export data from 11g and import into 10G R2 ORA-01455
EXP-00008: ORACLE error 1455 encountered
ORA-01455: converting column overflows integer datatype
EXP-00000: Export terminated unsuccessfully
Dynamic SQL Supports Statements Length Characters Limit (execute immediate sql长度限制)
有时需要在plsql 写一些动态sql,用execute immediate 或dbms_sql 调 用，但是execute immediate 后动态sql 文本的长度限制是多少呢？
To hold the latch manually, DB hang demo(手动持用latch使数据库hang)
It is widely known that the Oracle server uses kslgetl – Kernel Service Lock Management Get Latch function to acquire the latch,oradebug call kslgetl/kslfre can be used to acquire the latch manually. This is very useful to simulate latch related hangs and contention.
Delete the data on the table very slow(删除数据慢)
今天开发的同事问有个表上的数据为什么删不掉？我看了一下，也就不到100000条数据，表上有外键，等了5分钟hang在那里…
Know more about Oracle ASH
《Know More about Oracle AWR》 姐妹篇，oracle 10g引入，ASH 是性能诊断工具，不像sql trace总是默认打开， 保存历史上发生了什么， 提供比AWR更细粒度的数据，即使当系统负载很高时，它也工作的很好，因为使用类似memcopy的数据收集，不会有太大的额外负担，没用任何类型的数据库lock。 Active(‘Active’ == Non-idle sessions) Session History Licensed as part of the Diagnostic pack,
Know More about Oracle AWR
一段时间以来，Oracle在此领域的解决方案一直是其内置工具Statspack。Oracle数据库10g进行了重大改进：自动工作量存储库（AWR）。AWR与数据库一起安装，不仅捕获统计信息，还捕获捕获的指标。
Oracle 12c new feature:OFFSET n FETCH n row-limit
在分页查询或top n中在oracle 之间的版本使用rownum,row_number..，从12c 提供了offset fetch 的语法，其实这个语法不是什么新鲜玩意，在DB2,MSSQL,MYSQL,PostgreSQL之前也都提供的语法。
oracle 12c new feature: moving online datafiles(在线移动数据文件)
在12c以前如果想移动表空间的数据文件首先要offline.再OS mv命令移动离线的数据文件到新路径，Recover datafile[ or tablespace or database] 再Online.从12c 开始可以移动Online 的数据文件了。
案例：latch: cache buffers chains event tuning
前两天对oracle数据库(single instance)进行了迁移升级从10.2.0.4 升到11.2.0.3，有一个项目迁完后第二天，cpu负载升到了130更高（16cpus）. 向用户询问后使用上没有改变，平时就几个人使用，而该用户活动会话就有100多个。最在等待CBC latch. 怀疑是执行计划发生了改变。
oracle 12c Database Architecture (CDB、PDB)
Database Architecture consists of
1) Container Database (CDB)
2) Pluggable Database (PDB)
Script: RMAN backup shell on linux
# make direcory for backset file and scripts file,in my case /backup/db_bak cd /backup/db_bak mkdir scripts logs rman # the following is contents of rman_backup_sh vi /backup/db_bak/scripts/rman_backup_sh #!/bin/bash # author: anbob.com # desc: rman backup database level 0 # contact: weejar@gmail.com export ORACLE_HOME=/oracle/product/11.2.0/db_1 export ORACLE_SID=pora40 RMAN_LOG_FILE=/backup/db_bak/logs/rman_database_backup.`date +%y%m%d%H%M`.out # —————————————————————– # Initialize the log file. # … Read more
Expdp Exclude schemas or tables while full=y(排除指定schema的全备份)
当做EXPDP 逻辑备份时常用到FULL=Y 的全库备份,但是有些schema不想每次都备份如一些自带的schemas(如APEX_030200),这时可以使用exclude很方便的排除…
About redo log wait events
log file sync wait 默认是发生在前台进程发进commits时比如用户commit,DDL,递归操作发生在dictionary table 上的DML,同时rollback 也会导致…
How to hanganalyze and systemstate dumps for Oracle database？
Oracle support request hang analysis and system state dumps when rasing SR. One 10.1 or higher versions login as sqlplus -prelim / as sysdba To do a hanganalyze oradebug setmypid; oradebug unlimit; oradebug hanganalyze 3; Wait 60 – 90 seconds and run the last command again to identify the process state changes. To get a … Read more
How to Encrypt Your Bash Shell Script on Linux Using SHC?(加密shell script)
当需要写shell连接数据库取一些数据或备份等操作时，shell中可能包含数据库的用户密码等敏感信息，被一些不怀好意的人发现是你的灾难，出于安全的考虑需要对shell明文加密，使用shc 就可以把原shell编译为可执行程序(二进制)，这样就无法再明文打开shell。
Manually create Oracle 11g physical standby DG ,Switchover, Convert to Logical standby(Scripts and Short Descriptions)
Manually create physical standby with rman;
Primary role and Primary standby role switchover;
Converting Physical Standby to Logical Standby ;
CTAS will loss default value define on columns
The CTAS operation although copies column constraints such as NULL, NOT NULL from the normal table to the partitioned table during the partitioned table creation, it does not copy DEFAULT VALUEs of the columns.
script:purge ADR file
[oracle@db231 ~]$ ./adr_purge
INFO: Purge started at Thu May 16 07:35:32 CST 2013
INFO: adrci purge started at Thu May 16 07:35:32 CST 2013
INFO: adrci purging diagnostic destination diag/tnslsnr/db231/listener
INFO: purging ALERT older than 90 days …
INFO: purging INCIDENT older than 30 days …
ORA-12034: materialized view log on xx.xx younger than last refresh when a mview fast refresh after Impdp
ORA-12012: error on auto execute of job 88
ORA-12034: materialized view log on “TOPBOX”.”TOPBOX_COURSESTUDY” younger than last refresh
ORA-06512: at “SYS.DBMS_SNAPSHOT”, line 2256
Tuning “Backup:sbtbackup” Event
A few days ago a friend ask me to see a awr issue. In the TOP 5 Times Events, as you see, Obviously,database has relatively high sbtbackup wait event, Backup:sbtbackup falls under the administrative category also realted to the RMAN. These wait events will occur when there is RMAN backup running on the server. Backup: … Read more