使用绑定变量降低了分析SQL语句所需分配的共享内存量，因为绑定变量对于相同（或类似）SQL语句的多次执行重用单个游标。因为绑定变量减少了分析时间和内存使用，从而提高了数据库的性能和可伸缩性。特别是在具有大量并发用户时更是如此。初始化参数cursor_sharing确定哪些SQL语句可共享相同的游标。设置cursor_sharing参数为exact规定只有相同的SQL语句才能共享一个游标。设置它为FORCE表示某些字面值不同的语句可以共享一个游标。除非不同的字面值影响到执行计划的优化度，否则设置此参数为SIMILAR与设置它为FORCE的行为相同。
不过，游标共享与SQL优化具有固有的矛盾，因为指定字面值而不是绑定值给优化程序提供了更丰富的信息，使得能生成更好的计划。由于这个原因，强制游标共享极可能导致次优执行假话。在某些情形下，一条SQL语句的某些用户可能会到的很优
帮网友强制打开了一个没有备份的测试库，这个库没有备份也没有打开归档，因为之前也出现过active日志文件损毁，一直使用隐式参数才能正常打开:
_allow_resetlogs_corruption= TRUE
这次一开始这个库报ORA-600[2662]错误:
Mon Aug 23 09:37:00 2010 Errors in file /oracle/QAS/saptrace/usertrace/qas_ora_852096.trc: ORA-00600: internal error code, arguments: [2662], [0], [130131504], [0], [130254136], [4264285], [], [] Mon Aug 23 09:37:02 2010 Errors in file /oracle/QAS/saptrace/usertrace/qas_ora_852096.trc: ORA-00600: internal error code, arguments: [2662], [0], [130131506], [0], [130254136], [4264285], [], []
ORA-600 [2662] “Block SCN is ahead of Current SCN”错误是当数据块中的SCN领先于current SCN，由于后台进程或服务进程都会比对UGA中的dependent SCN和数据库当前的SCN，如果数据库当前SCN小于dependent SCN，那么该进程就会报ORA-600 [2662]错误，如果遭遇该错误的是服务进程，那么服务进程一般会异常终止；如果遭遇该错误的是后台进程譬如SMON,则会导致实例CRASH。
ORA-600 [2662]错误可以能由以下几种情况引起:
1.启用隐含参数_ALLOW_RESETLOGS_CORRUPTION后，以resetlogs形式打开数据库；这种情况下发生2662错误，根本原因是没有完全前滚导致控制文件中的SCN滞后于数据块中的SCN。
2.硬件故障导致数据库没法写控制文件和联机日志文件
3.错误的部分恢复数据库
4.恢复了控制文件，但是没有使用recover database using backup controlfile进行恢复
5.数据库crash后设置了_DISABLE_LOGGING隐含参数
6.在并行服务器环境中DLM存在问题
该错误的5个参数的具体含义如下:
ARGUMENTS:
Arg [a] Current SCN WRAP
Arg [b] Current SCN BASE
Arg [c] dependent SCN WRAP
Arg [d] dependent SCN BASE
Arg [e] Where present this is the DBA where the dependent SCN came from.
我们的case当中dependent SCN为130254136，而当前SCN为130131506，其差值为122630；从以上告警日志中可以看到数据库的当前SCN是在不断缓慢增长的，当我们遭遇到2662错误时，很滑稽的一点是只要不断重启数据库保持current SCN的增长，一段时间后2662错误会不药而愈。当然我们也可以不用这种笨办法，10015事件可以帮助我们调整数据库当前SCN:
/* 当数据库处于mount状态，可以使用10015事件来调整scn */
alter session set events '10015 trace name adjust_scn level 1';
/* 这里可以设置level 2..10等 (level 1是在每次打开数据库时scn增加1000k)*/
/* 需要注意的是10g某些版本不同于9i，需要设置隐式参数_allow_error_simulation，才能真正增进scn */
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
SQL> col current_scn format 999,999,999,999
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
1141408
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1653518336 bytes
Fixed Size 2213896 bytes
Variable Size 989857784 bytes
Database Buffers 654311424 bytes
Redo Buffers 7135232 bytes
Database mounted.
SQL> alter session set events '10015 trace name adjust_scn level 1';
Session altered.
SQL> alter database open;
Database altered.
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
1142031
/* 可以看到current_scn并未大量增加，10.2.0.4上默认10015 adjust_scn不被触发 */
SQL> alter system set "_allow_error_simulation"=true scope=spfile;
System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1653518336 bytes
Fixed Size 2213896 bytes
Variable Size 989857784 bytes
Database Buffers 654311424 bytes
Redo Buffers 7135232 bytes
Database mounted.
SQL> alter session set events '10015 trace name adjust_scn level 1';
Session altered.
SQL> alter database open;
Database altered.
SQL>select current_scn from v$database;
CURRENT_SCN
----------------
1,073,741,980
在接手之前该网友已经通过反复重启数据库将数据库的当前SCN提高到dependent SCN的127037138；原以为这样就可以打开数据库了，谁知道又出现了一下错误:
Wed Aug 25 07:43:53 2010 Errors in file /oracle/QAS/saptrace/usertrace/qas_ora_929958.trc: ORA-00600: internal error code, arguments: [4000], [8], [], [], [], [], [], [] Wed Aug 25 07:43:53 2010 Errors in file /oracle/QAS/saptrace/usertrace/qas_ora_929958.trc: ORA-00704: bootstrap process failure ORA-00704: bootstrap process failure ORA-00600: internal error code, arguments: [4000], [8], [], [], [], [], [], [] Wed Aug 25 07:43:53 2010 Error 704 happened during db open, shutting down database
bootstrap自举过程中遭遇了ORA-600 [4000]错误，该错误一般当Oracle尝试读取数据字典(主要是undo$基表)中记录的USN对应的回滚段失败引起.，通过设置隐式参数_corrupted_rollback_segments可以一定程度上规避该错误，强制打开数据库,其Argument[a]代表造成读取失败的USN(undo segment number)，但实际上有问题的回滚段可能不止这一个:
/* 通过strings工具从system表空间上找回各回滚段的名字 */ $strings system.dbf |grep _SYSSMU|less _SYSSMU1$ _SYSSMU2$ _SYSSMU3$ _SYSSMU4$ _SYSSMU5$ _SYSSMU6$ _SYSSMU7$ _SYSSMU8$ _SYSSMU9$ ......... alter system set "_corrupted_rollback_segments"='(_SYSSMU1$, _SYSSMU2$, _SYSSMU3$, _SYSSMU4$, _SYSSMU5$, _SYSSMU6$, _SYSSMU7$, _SYSSMU8$, _SYSSMU9$, _SYSSMU10$, _SYSSMU11$, _SYSSMU12$)' scope=spfile; System altered. /* 即便设置了_corrupted_rollback_segments隐式参数，也还有一定概率遭遇4000错误，尝试加上10513事件，并多次重启数据库 */ SQL> alter system set event='10513 trace name context forever,level 2' scope=spfile; System altered. /* 再次出现4000 错误 */ Errors in file /oracle/QAS/saptrace/usertrace/qas_ora_1016014.trc: ORA-00600: internal error code, arguments: [4000], [8], [], [], [], [], [], [] Thu Aug 26 09:43:39 2010 Errors in file /oracle/QAS/saptrace/usertrace/qas_ora_1016014.trc: ORA-00704: bootstrap process failure ORA-00704: bootstrap process failure ORA-00600: internal error code, arguments: [4000], [8], [], [], [], [], [], [] Thu Aug 26 09:43:39 2010 Error 704 happened during db open, shutting down database /* 再次重启后发现4000错误不再出现 * /
再次重启发现不再出现ORA-600[4000]错误，但在字典检查阶段Oracle认为数据文件227不匹配于当前的incarnation:
Thu Aug 26 11:13:22 2010 Dictionary check beginning Thu Aug 26 09:46:00 2010 Errors in file /oracle/QAS/saptrace/usertrace/qas_ora_897162.trc: ORA-01177: data file does not match dictionary - probably old incarnation ORA-01110: data file 227: '/oracle/QAS/sapdata2/qas_192/qas.data196' Error 1177 happened during db open, shutting down database USER: terminating instance due to error 1177 Instance terminated by USER, pid = 897162
初步判断出现ORA-01177可能为2种可能性:
1.数据字典出现讹误，227号文件对应的incarnation信息不正确
2.在之前的某次resetlogs open过程中，227号文件头由于某些原因没有正确更新incarnation信息
针对这样的情况如果一定要找回该数据文件上的数据的话只能通过手动修改数据字典或文件头，当然也可以尝试使用一些直接从数据文件上抽取数据的工具。
因为这是一次友情协助，就没有继续深入下去，通过重建控制文件并跳过该数据文件解决了:
CREATE CONTROLFILE REUSE DATABASE "QAS" RESETLOGS NOARCHIVELOG
-- SET STANDBY TO MAXIMIZE PERFORMANCE
MAXLOGFILES 255
MAXLOGMEMBERS 3
MAXDATAFILES 254
MAXINSTANCES 50
MAXLOGHISTORY 36302
LOGFILE
GROUP 1 (
'/oracle/QAS/redolog/redolog11A.dbf',
'/oracle/QAS/redolog/redolog11B.dbf'
) SIZE 500M,
GROUP 2 (
'/oracle/QAS/redolog/redolog12A.dbf',
'/oracle/QAS/redolog/redolog12B.dbf'
) SIZE 500M
-- STANDBY LOGFILE
DATAFILE
'/oracle/QAS/sapdata1/system_1/system.data1',
........
'/oracle/QAS/sapdata2/qas_192/qas.data195'
CHARACTER SET WE8DEC
Thu Aug 26 14:04:50 2010
Successful mount of redo thread 1, with mount id 2117500093
Thu Aug 26 14:04:50 2010
Completed: CREATE CONTROLFILE REUSE DATABASE "QAS" RESETLOGS
Thu Aug 26 14:05:05 2010
alter database mount
Thu Aug 26 14:05:05 2010
ORA-1100 signalled during: alter database mount...
Thu Aug 26 14:05:15 2010
alter database open resetlogs
RESETLOGS is being done without consistancy checks. This may result
in a corrupted database. The database should be recreated.
RESETLOGS after incomplete recovery UNTIL CHANGE 1125281471596
Resetting resetlogs activation ID 0 (0x0)
Online log 1 of thread 1 was previously cleared
Thu Aug 26 14:05:36 2010
Assigning activation ID 2117500093 (0x7e367cbd)
Thread 1 opened at log sequence 1
Current log# 2 seq# 1 mem# 0: /oracle/QAS/redolog/redolog12A.dbf
Current log# 2 seq# 1 mem# 1: /oracle/QAS/redolog/redolog12B.dbf
Successful open of redo thread 1
Thu Aug 26 14:05:36 2010
SMON: enabling cache recovery
Thu Aug 26 14:05:36 2010
Dictionary check beginning
Tablespace 'PSAPTEMP' #2 found in data dictionary,
but not in the controlfile. Adding to controlfile.
File #227 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING00227' in the controlfile.
This file can no longer be recovered so it must be dropped.
File #228 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING00228' in the controlfile.
This file can no longer be recovered so it must be dropped.
File #229 found in data dictionary but not in controlfile.
Creating OFFLINE file 'MISSING00229' in the controlfile.
This file can no longer be recovered so it must be dropped.
Dictionary check complete
Thu Aug 26 14:05:38 2010
SMON: enabling tx recovery
Thu Aug 26 14:05:38 2010
Database Characterset is WE8DEC
replication_dependency_tracking turned off (no async multimaster replication found)
Completed: alter database open resetlogs
10g引入ASMM后SGA_TARGET取代shared_pool_size,db_cache_size等参数，成为DBA关注的主要SGA内存管理参数；有不少情况下SGA_TARGET参数会设置为一个小于SGA_MAX_SIZE的值(这样做在多实例情况下更具灵活性)。但不少人会问，这样岂不是要浪费一部分物理内存吗？Oracle会为实例分配SGA_MAX_SIZE大小的内存的，SGA_TARGET要设得和SGA_MAX_SIZE一样大才合理啊！
让我们来看看实际的情况:
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
/* linux上的10.2.0.4 */
SQL> show parameter sga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 3600M
sga_target big integer 368M
SQL> col component for a25;
SQL> select component, current_size / 1024 / 1024, last_oper_type
2 from v$sga_dynamic_components
3 where current_size != 0;
COMPONENT CURRENT_SIZE/1024/1024 LAST_OPER_TYP
------------------------- ---------------------- -------------
shared pool 96 STATIC
large pool 16 STATIC
java pool 16 STATIC
DEFAULT buffer cache 224 INITIALIZING
/* 此时的SGA_TARGET为368M，而SGA_MAX_SIZE为3600M */
/* 我们来看一下实际的物理内存使用情况 */
/* 以root用户登录，因为我们需要用到清理文件系统缓存的命令 */
[maclean@rh2 ~]$ su - root
Password:
[root@rh2 ~]# sync
[root@rh2 ~]# sync
/* sync 命令用以写出文件系统脏缓存，类似于Oracle的checkpoint手动检查点 */
[root@rh2 ~]# echo 3 > /proc/sys/vm/drop_caches
[root@rh2 ~]# echo 3 > /proc/sys/vm/drop_caches
/* 将proc文件系统下的drop_caches文件置为3，清理文件系统缓存，以免其干扰我们的实验 */
[root@rh2 ~]# free
total used free shared buffers cached
Mem: 4046352 429328 3617024 0 296 176100
-/+ buffers/cache: 252932 3793420
Swap: 10241428 0 10241428
/* 4g内存中仅有419M处于使用状态，其他均为free。可见Oracle没有为实例分配大小等于SGA_MAX_SIZE的内存空间，而接近于SGA_TARGET_SIZE的大小 */
[root@rh2 ~]# ps -ef|grep pmon|grep -v grep
maclean 6361 1 0 18:35 ? 00:00:00 ora_pmon_YOUYUS
[root@rh2 ~]# pmap -x 6361
6361: ora_pmon_YOUYUS
Address Kbytes RSS Dirty Mode Mapping
0000000000400000 100412 7300 0 r-x-- oracle
000000000680f000 544 180 64 rwx-- oracle
0000000006897000 148 104 104 rwx-- [ anon ]
000000001e9d0000 672 532 532 rwx-- [ anon ]
0000000060000000 3688448 1044 388 rwxs- [ shmid=0x390005 ]
/* 利用pmap工具探测Oracle后台进程的内存地址空间，可以看到这里虚拟共享内存段(也就是SGA)的大小为3602M */
[root@rh2 ~]# ipcs -m
------ Shared Memory Segments --------
key shmid owner perms bytes nattch status
0x74018366 3112960 root 600 4 0
0x00000000 3473409 root 644 80 2
0x74018365 3080194 root 600 4 0
0x00000000 3506179 root 644 16384 2
0x00000000 3538948 root 644 280 2
0x1cdd16e8 3735557 maclean 640 3776970752 15
/* 使用ipcs 命令也可以观察到这个大小为3602M的共享内存段 */
/* 可以猜测Oracle在这里使用malloc函数或类似的方法实现虚拟内存的分配，没有像calloc函数那样对空间中的每一位都初始为0,保证了不浪费物理内存 */
/* 我们动态修改SGA_TARGET参数来看看* /
SQL> alter system set sga_target=3000M;
System altered.
SQL> select component, current_size / 1024 / 1024, last_oper_type
2 from v$sga_dynamic_components
3 where current_size != 0;
COMPONENT CURRENT_SIZE/1024/1024 LAST_OPER_TYP
------------------------- ---------------------- -------------
shared pool 96 STATIC
large pool 16 STATIC
java pool 16 STATIC
DEFAULT buffer cache 2864 INITIALIZING
/* BUFFER_CACHE 的空间大幅增加 * /
SQL> !free
total used free shared buffers cached
Mem: 4046352 3187784 858568 0 536 2889920
-/+ buffers/cache: 297328 3749024
Swap: 10241428 0 10241428
/* used mem部分上升到3113M，随着SGA_TARGET的动态增长而增长了 */
/* 如果我们改小SGA_TARGET呢？ */
SQL> alter system set sga_target=368M;
System altered.
SQL> select component, current_size / 1024 / 1024, last_oper_type
2 from v$sga_dynamic_components
3 where current_size != 0;
COMPONENT CURRENT_SIZE/1024/1024 LAST_OPER_TYP
------------------------- ---------------------- -------------
shared pool 96 STATIC
large pool 16 STATIC
java pool 16 STATIC
DEFAULT buffer cache 224 SHRINK
/* 可以看到BUFFER CACHE最近执行了SHRINK收缩操作，SIZE下降到224M */
QL> !free
total used free shared buffers cached
Mem: 4046352 3204608 841744 0 1596 2912888
-/+ buffers/cache: 290124 3756228
Swap: 10241428 0 10241428
/* 此时OS层仍认为used memory部分为3130M;但可以放心，它们是可被其他进程复用的 * /
官方对pre_page_sga参数的定义是”PRE_PAGE_SGA determines whether Oracle reads the entire SGA into memory at instance startup. Operating system page table entries are then prebuilt for each page of the SGA. This setting can increase the amount of time necessary for instance startup, but it is likely to decrease the amount of time necessary for Oracle to reach its full performance capacity after startup.” 它决定Oracle是否在启动阶段将整个SGA读入内存，实际上在启动阶段SGA的内容是十分稀疏的，当pre_page_sga为TRUE时Oracle所要做的是向OS所要SGA_MAX_SIZE大小的实际物理页。
我们来看一下这个参数的实际效果:
SQL> alter system set sga_max_size=2500M scope=spfile;
System altered.
SQL> alter system set pre_page_sga=true scope=spfile;
System altered.
SQL> startup force ;
ORACLE instance started.
Total System Global Area 2634022912 bytes
Fixed Size 2086288 bytes
Variable Size 2382367344 bytes
Database Buffers 234881024 bytes
Redo Buffers 14688256 bytes
Database mounted.
Database opened.
[root@rh2 ~]# echo 3 > /proc/sys/vm/drop_caches
SQL> show parameter sga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean TRUE
sga_max_size big integer 2512M
sga_target big integer 368M
SQL> !free
total used free shared buffers cached
Mem: 4046352 2895256 1151096 0 648 2623692
-/+ buffers/cache: 270916 3775436
Swap: 10241428 104936 10136492
/* 设置pre_page_sga参数为true后，Oracle实际分配的物理内存不再局限于SGA_TARGET，而是在实例生命周期内几乎恒等于SGA_MAX_SIZE指定的值了 * /
/* 当SGA_TARGET配合pre_page_sga使用时，这的确可能造成一种浪费 ! * /
Backup Undo Optimization是11g的新特性之一，RMAN将避免备份撤销表空间上那些已提交事务的撤销数据。且该特性无法被禁用(You can enable and disable backup optimization, but backup undo optimization is built-in behavior.)。
我们在11.2.0.1版本上具体测试一下这个新特性:
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
/* 为了避免undo自动调优干扰我们的测试，修改_undo_autotune参数禁用该特性 */
SQL> alter system set "_undo_autotune"=false;
System altered.
/* 创建一个新的undo表空间，清理现场 */
SQL> create undo tablespace UNDOTEST datafile size 500M autoextend on next 50M maxsize unlimited;
Tablespace created.
SQL> alter system set undo_tablespace=UNDOTEST;
System altered.
/* 列出相关的undo参数，可以看到这里undo_retention参数设为极短的10s */
SQL> show parameter undo;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_undo_autotune boolean FALSE
undo_management string AUTO
undo_retention integer 10
undo_tablespace string UNDOTEST
RMAN> list backup;
specification does not match any backup in the repository
RMAN> backup tablespace UNDOTEST;
RMAN> list backup of tablespace UNDOTEST;
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4 Full 1.90M DISK 00:00:00 25-AUG-10
/* undo表空间初始的备份大小为1.9M */
SQL> conn maclean/maclean
Connected.
SQL> select count(*) from YOUYUS;
COUNT(*)
----------
579808
/* YOUYUS表有大约60万条数据，我们批量删除这些数据，将产生大量的undo*/
SQL> delete YOUYUS;
579808 rows deleted.
/* 此时再次执行备份undo表空间操作 */
RMAN> backup tablespace UNDOTEST;
RMAN> list backup of tablespace UNDOTEST;
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
5 Full 134.43M DISK 00:00:03 25-AUG-10
/* 在存在大量active undo数据的情况下，备份文件增大到134M */
SQL> commit;
Commit complete.
SQL> exec dbms_lock.sleep(20);
PL/SQL procedure successfully completed.
SQL> select status,sum(blocks) from dba_undo_extents group by status;
STATUS SUM(BLOCKS)
--------- -----------
UNEXPIRED 2696
EXPIRED 32936
/* commit后等待20s,确定没有active的撤销段 */
RMAN> backup tablespace UNDOTEST;
RMAN> list backup of tablespace UNDOTEST;
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
6 Full 134.49M DISK 00:00:02 25-AUG-10
/* 备份文件还要大于commit前，undo backup optimization居然没有起作用？ */
/* 这个会是BUG吗？ */
根据以上情况我提交了SR，ORACLE GCS给出的回复:
Bug 6399468: UNDO OPTIMIZATION
====> Undo optimization was changed from ‘all undo not needed’ to ‘undo not in use older than 1 hour’
In unpublished bug 6399468 DEV has confirmed the Undo optimization was changed from ‘all undo not needed’ to ‘undo not in use older than 1 hour’. For your last test case please wait for 1 hour and try backup again.
居然又是一个unpublished的BUG，Oracle DEV部门确认了backup undo optimization所避免备份的是1个小时以上未被尝试使用的undo，而非所有不再需要的undo。这是典型的开发部门和文档撰写部门间没有充分交流造成的问题！
/* 尝试等待3600s */ SQL> exec dbms_lock.sleep(3600); PL/SQL procedure successfully completed. /* 3600s还真漫长....... */ RMAN> backup tablespace UNDOTEST; RMAN> list backup of tablespace UNDOTEST; BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 10 Full 15.01M DISK 00:00:00 25-AUG-10 /* 备份集缩小到15m,undo backup optimization起到了作用！*/ That's great!
在pre10g的很长时间内，Oracle仅提供exp/imp导入导出工具，虽然这2个实用程序十分有效(现在也是如此)，但因为它们受限于client/server模式工具自身的限制，以普通用户程序的身份来运转数以TB计的数据，其才不堪大用！DataPump是10g以后主推的数据抽取/导入工具，不同于exp/imp工具，DataPump是一个服务器端的实用程序，因为运行在服务器上故而DataPump进程可以直接访问数据文件与SGA(无需借shadow进程之手)，与exp/imp工具相比使用DataPump可以获得显著的性能改善。DataPump可以通过直接路径或外部表路径这两种方法导出数据；其中直接路径避开了数据库高速缓存。当使用直接路径模式抽取数据时，DataPump从磁盘直接读取数据文件，抽取和格式化文件内容，最后将内容写出到转储文件。该种模式和SGA交互等待少，其导入导出速度直接取决于数据库所在磁盘速度和cpu;因此，直接路径极为快速。
外部表路径模式将使用到数据库的高速缓存buffer cache，通过外部表路径方法导出数据时，DataPump使用普通的SELECT操作将数据块从数据文件中读入buffer cache，为了写出转储文件，数据会在缓存中被格式化。通过外部表路径导入数据时，DataPump根据转储文件的内容构造标准的插入语句，并且通过将数据块从数据文件读至缓存来执行这些语句，插入操作按照标准的样式在缓存中完成；如同任何普通DML操作一样，外部表路径也会同时产生撤销和重做。
DataPump自身会根据对象的复杂性作出使用直接路径还是外部表路径的选择；对于较复杂的对象(后文将列出)而言，为了分解复杂性而必须同SGA进行交互，此情况下Data Pump只能采用外部表模式。我们还可以通过使用access_method参数来控制其行为，当然这仅在我们确认Data Pump作出了错误选择时才有必要。
| 若满足右列条件EXPDP将采用direct_path即直接路径模式 | 表结构允许使用直接路径模式,举例而言：
表上没有启用针对SELECT操作的fine-grained access control 非队列表(queue table) 表上没有BFILE和opaque类型的列，或包含有opaque列的对象类型 表上没有加密列 表上没有被废弃的旧类型列 若表上存在LONG或LONG RAW类型列，则此列只能是最后一列 需要导出的表或分区相对较少(多达250M)，亦或者表或分区其实很大，但导出任务无法工作在并行模式(未指定parallel参数，或parallel参数设置为1) |
| 若满足右列条件EXPDP将采用external_table即外部表模式 | 数据结构不满足在直接路径模式下抽取的条件，举例而言:
表上启用了针对SELECT操作的精细粒度控制 队列表 表上包含了BFILE或opaque类型列，或者包含有opaque列的对象类型 表上存在加密列 表上存在被废弃的旧类型列 表上存在LONG或LONG RAW类型列，且不是最后列 数据结构满足使用直接路径模式的条件，但执行导出任务时相关表上指定了QUERY, SAMPLE, or REMAP_DATA等参数 数据结构满足使用直接路径模式的条件，但相关的表或分区相对较大(大于250M)，此时并行SQL可以用来加速数据抽取 |
| 若满足右列条件IMPDP将采用direct_path即直接路径模式 | 数据结构满足使用直接路径模式的条件，举例而言:
当导入某单一表分区时该分区表上没有建立全局索引，这一点也包括分区的对象表 没有基于LOB列建立的域索引(domain index) 非cluster表 表上没有BFILE列或opaque类型列 表上没有嵌入了opaque类型的VARRAY列 表上没有加密列 没有启用补全日志(Supplemental logging)且表上没有LOB类型列 若导入表已预先建立了表建构，则需满足以下条件: 表上没有激活的触发器 并且 若是分区表则应有索引 并且 表上上没有启用针对INSERT操作的精细粒度控制 并且 表上除去check类型约束不存在其他类型约束 并且 表上没有unique的索引 执行导入任务时没有为相关表指定QUERY, REMAP_DATA等参数
需要导入的表或分区相对较小(少于250M)，或者表或分区其实很大，但无法以并行模式导入(未指定parallel参数或指定其为1) |
| 若满足右列条件IMPDP将采用external_table即外部表路径 | 当数据结构不满足在直接路径模式下导入的条件，举例而言:
当导入某单一表分区时该分区表上建有全局索引，这一点也包括分区的对象表 表上有基于LOB列建立的域索引(domain index) cluster表 表上有BFILE列或opaque类型列 表上有嵌入了opaque类型的VARRAY列 表上有加密列 启用了补全日志且表上有至少一个LOB列 若导入表已预先建立了表建构，且满足以下条件: 表上有激活的触发器 或者 是分区表且没有任何索引 或者 表上启用了针对INSERT操作的精细粒度控制 或者 表上除去check类型约束还还有其他类型约束 或者 表上有unique的索引 执行导入任务时有为相关表指定QUERY, REMAP_DATA等参数
数据结构满足使用直接路径模式的条件，但相关的表或分区相对较大(大于250M)，此时并行SQL可以用来加速数据导入 |
我们有必要深入了解一下外部表路径究竟是如何工作的:
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
SQL> exec dbms_workload_repository.create_snapshot;
PL/SQL procedure successfully completed.
/* 执行expdp导出任务，TCACHE表大小为465M，这里显示指定了外部表路径 */
[maclean@rh2 ~]$ expdp maclean/maclean directory=dumps tables=TCACHE dumpfile=tcache1.dmp access_method=external_table
Export: Release 10.2.0.4.0 - 64bit Production on Tuesday, 24 August, 2010 20:31:48
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "MACLEAN"."SYS_EXPORT_TABLE_07": maclean/******** directory=dumps tables=TCACHE dumpfile=tcache1.dmp access_method=external_table
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 544 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "MACLEAN"."TCACHE" 465.6 MB 4999999 rows
Master table "MACLEAN"."SYS_EXPORT_TABLE_07" successfully loaded/unloaded
******************************************************************************
Dump file set for MACLEAN.SYS_EXPORT_TABLE_07 is:
/s01/dump/tcache1.dmp
Job "MACLEAN"."SYS_EXPORT_TABLE_07" successfully completed at 20:32:18
/* 这次我们指定了并行度为4 */
[maclean@rh2 ~]$ expdp maclean/maclean directory=dumps tables=TCACHE dumpfile=tcache2.dmp,tcache3.dmp,tcache4.dmp,tcache5.dmp access_method=external_table parallel=4
Export: Release 10.2.0.4.0 - 64bit Production on Tuesday, 24 August, 2010 20:32:49
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "MACLEAN"."SYS_EXPORT_TABLE_07": maclean/******** directory=dumps tables=TCACHE dumpfile=tcache2.dmp,tcache3.dmp,tcache4.dmp,tcache5.dmp access_method=external_table parallel=4
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 544 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "MACLEAN"."TCACHE" 465.6 MB 4999999 rows
Master table "MACLEAN"."SYS_EXPORT_TABLE_07" successfully loaded/unloaded
******************************************************************************
Dump file set for MACLEAN.SYS_EXPORT_TABLE_07 is:
/s01/dump/tcache2.dmp
/s01/dump/tcache3.dmp
/s01/dump/tcache4.dmp
Job "MACLEAN"."SYS_EXPORT_TABLE_07" successfully completed at 20:33:04
SQL> exec dbms_workload_repository.create_snapshot;
PL/SQL procedure successfully completed.
SQL> @?/rdbms/admin/awrrpt
接着我们分析该时段内的awr报告!
从AWR报告中，我们不难找到以下语句:
SQL ID 3qwsywpvtdyjp:
CREATE TABLE "ET$088200010001" ( "OWNER", "OBJECT_NAME", "SUBOBJECT_NAME", "OBJECT_ID", "DATA_OBJECT_ID", "OBJECT_TYPE", "CREATED", "LAST_DDL_TIME", "TIMESTAMP", "STATUS", "TEMPORARY", "GENERATED", "SECONDARY" ) ORGANIZATION EXTERNAL ( TYPE ORACLE_DATAPUMP DEFAULT DIRECTORY DUMPS ACCESS PARAMETERS (DEBUG =0 DATAPUMP INTERNAL TABLE "MACLEAN"."TCACHE" JOB ( "MACLEAN", "SYS_EXPORT_TABLE_07", 1) WORKERID 1 PARALLEL 1 VERSION COMPATIBLE ENCRYPTPASSWORDISNULL ) LOCATION ('bogus.dat') ) PARALLEL 1 REJECT LIMIT UNLIMITED AS SELECT /*+ PARALLEL(KU$, 1) */ "OWNER", "OBJECT_NAME", "SUBOBJECT_NAME", "OBJECT_ID", "DATA_OBJECT_ID", "OBJECT_TYPE", "CREATED", "LAST_DDL_TIME", "TIMESTAMP", "STATUS", "TEMPORARY", "GENERATED", "SECONDARY" FROM RELATIONAL("MACLEAN"."TCACHE" ) KU$
SQL ID 1rxax27p7anr3:
CREATE TABLE "ET$088000020001" ( "OWNER", "OBJECT_NAME", "SUBOBJECT_NAME", "OBJECT_ID", "DATA_OBJECT_ID", "OBJECT_TYPE", "CREATED", "LAST_DDL_TIME", "TIMESTAMP", "STATUS", "TEMPORARY", "GENERATED", "SECONDARY" ) ORGANIZATION EXTERNAL ( TYPE ORACLE_DATAPUMP DEFAULT DIRECTORY DUMPS ACCESS PARAMETERS (DEBUG =0 DATAPUMP INTERNAL TABLE "MACLEAN"."TCACHE" JOB ( "MACLEAN", "SYS_EXPORT_TABLE_07", 1) WORKERID 2 PARALLEL 2 VERSION COMPATIBLE ENCRYPTPASSWORDISNULL ) LOCATION ('bogus.dat') ) PARALLEL 2 REJECT LIMIT UNLIMITED AS SELECT /*+ PARALLEL(KU$, 2) */ "OWNER", "OBJECT_NAME", "SUBOBJECT_NAME", "OBJECT_ID", "DATA_OBJECT_ID", "OBJECT_TYPE", "CREATED", "LAST_DDL_TIME", "TIMESTAMP", "STATUS", "TEMPORARY", "GENERATED", "SECONDARY" FROM RELATIONAL("MACLEAN"."TCACHE" ) KU$
以上就是Expdp在导出数据时使用的创建组织类型为ORACLE_DATAPUMP的外部表的SQL语句；后者语句中指定的并行度为2，而非我所指定的4，因该是Oracle考虑到所需导出表的具体大小，设置过高的并行度反而会适得其反，所以对设定值做了修正。
启用了并行的SQL 1rxax27p7anr3执行时间为22.63s，反而要比不使用并行慢；可见在TCACHE表这个数量级，完全没有必要使用并行导出。
接着我们来探究一下外部表路径的导入:
SQL> truncate table tcache;
Table truncated.
SQL> set pagesize 1400;
SQL> set linesize 140;
/* 执行外部表路径的数据导入，并行度1 */
[maclean@rh2 admin]$ impdp maclean/maclean directory=dumps tables=TCACHE content=data_only dumpfile=tcache1.dmp access_method=external_table
/* 在以上SQL执行期间查询V$SQL动态视图的相关语句*/
select sql_text,
sql_id,
cpu_time,
elapsed_time,
disk_reads,
buffer_gets,
rows_processed
from v$sql
where sql_text like '%TCACHE%'
and sql_text not like '%like%';
SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID CPU_TIME ELAPSED_TIME DISK_READS BUFFER_GETS ROWS_PROCESSED
------------- ---------- ------------ ---------- ----------- --------------
INSERT /*+ SYS_DL_CURSOR */ INTO RELATIONAL("MACLEAN"."TCACHE") ("OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID","DATA_OBJECT_ID","OBJECT
_TYPE","CREATED","LAST_DDL_TIME","TIMESTAMP","STATUS","TEMPORARY","GENERATED","SECONDARY") VALUES (NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,N
ULL,NULL,NULL,NULL,NULL)
6tn47a220d34q 408 408 0 0 0
INSERT /*+APPEND+*/ /*+PARALLEL("TCACHE",1)+*/ INTO RELATIONAL("MACLEAN"."TCACHE" ) ("OWNER", "OBJECT_NAME", "SUBOBJECT_NAME", "OBJECT_ID",
"DATA_OBJECT_ID", "OBJECT_TYPE", "CREATED", "LAST_DDL_TIME", "TIMESTAMP", "STATUS", "TEMPORARY", "GENERATED", "SECONDARY") SELECT "OWNE
R", "OBJECT_NAME", "SUBOBJECT_NAME", "OBJECT_ID", "DATA_OBJECT_ID", "OBJECT_TYPE", "CREATED", "LAST_DDL_TIME", "TIMESTAMP", "STATUS", "TEMPO
RARY", "GENERATED", "SECONDARY" FROM "ET$087B00060001" KU$
66q4w8c4ak0wf 31576 31576 0 608 0
/* 当导入job完成后再次查询 */
select sql_text,
sql_id,
cpu_time,
elapsed_time,
disk_reads,
buffer_gets,
rows_processed
from v$sql
where sql_text like '%TCACHE%'
and sql_text not like '%like%';
SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID CPU_TIME ELAPSED_TIME DISK_READS BUFFER_GETS ROWS_PROCESSED
------------- ---------- ------------ ---------- ----------- --------------
INSERT /*+ SYS_DL_CURSOR */ INTO RELATIONAL("MACLEAN"."TCACHE") ("OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID","DATA_OBJECT_ID","OBJECT
_TYPE","CREATED","LAST_DDL_TIME","TIMESTAMP","STATUS","TEMPORARY","GENERATED","SECONDARY") VALUES (NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,N
ULL,NULL,NULL,NULL,NULL)
6tn47a220d34q 408 408 0 0 0
/* 执行完成后一句SQL立马消失了！？ Oracle好像很不情愿我们看到外部表路径使用的INSERT语句 :) */
INSERT /*+APPEND+*/ /*+PARALLEL("TCACHE",1)+*/
INTO RELATIONAL
("MACLEAN"."TCACHE")
("OWNER",
"OBJECT_NAME",
"SUBOBJECT_NAME",
"OBJECT_ID",
"DATA_OBJECT_ID",
"OBJECT_TYPE",
"CREATED",
"LAST_DDL_TIME",
"TIMESTAMP",
"STATUS",
"TEMPORARY",
"GENERATED",
"SECONDARY")
SELECT "OWNER",
"OBJECT_NAME",
"SUBOBJECT_NAME",
"OBJECT_ID",
"DATA_OBJECT_ID",
"OBJECT_TYPE",
"CREATED",
"LAST_DDL_TIME",
"TIMESTAMP",
"STATUS",
"TEMPORARY",
"GENERATED",
"SECONDARY"
FROM "ET$087F00230001" KU$
/* 这里的KU$代表的就是以ORACLE_DATAPUMP转储文件形式存放在数据库外的外部表 */
/* 可以看到这里尝试使用了APPEND插入模式，以减少undo和redo的产生*/
SQL> truncate table tcache;
Table truncated.
/* 若启用并行导入呢？ */
[maclean@rh2 admin]$ impdp maclean/maclean directory=dumps tables=TCACHE content=data_only dumpfile=tcache2.dmp,tcache3.dmp,tcache4.dmp access_method=external_table parallel=3
........
select sql_text,
sql_id,
cpu_time,
elapsed_time,
disk_reads,
buffer_gets,
rows_processed
from v$sql
where sql_text like '%TCACHE%'
and sql_text not like '%like%';
SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID CPU_TIME ELAPSED_TIME DISK_READS BUFFER_GETS ROWS_PROCESSED
------------- ---------- ------------ ---------- ----------- --------------
INSERT /*+APPEND+*/ /*+PARALLEL("TCACHE",1)+*/ INTO RELATIONAL("MACLEAN"."TCACHE" ) ("OWNER", "OBJECT_NAME", "SUBOBJECT_NAME", "OBJECT_ID",
"DATA_OBJECT_ID", "OBJECT_TYPE", "CREATED", "LAST_DDL_TIME", "TIMESTAMP", "STATUS", "TEMPORARY", "GENERATED", "SECONDARY") SELECT "OWNE
R", "OBJECT_NAME", "SUBOBJECT_NAME", "OBJECT_ID", "DATA_OBJECT_ID", "OBJECT_TYPE", "CREATED", "LAST_DDL_TIME", "TIMESTAMP", "STATUS", "TEMPO
RARY", "GENERATED", "SECONDARY" FROM "ET$087A00040001" KU$
2mvs15623ssvy 5910289 5910289 0 73578 0
INSERT /*+ SYS_DL_CURSOR */ INTO RELATIONAL("MACLEAN"."TCACHE") ("OWNER","OBJECT_NAME","SUBOBJECT_NAME","OBJECT_ID","DATA_OBJECT_ID","OBJECT
_TYPE","CREATED","LAST_DDL_TIME","TIMESTAMP","STATUS","TEMPORARY","GENERATED","SECONDARY") VALUES (NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,N
ULL,NULL,NULL,NULL,NULL)
6tn47a220d34q 1167 1167 0 0 0
/* 似乎因为沿用了APPEND插入模式，插入语句的并行度并未上升 */
/* 因为Oracle不想同我们分享它的小秘密，所以awr和ash报告中都不记录外部表路径插入SQL的相关信息 */
/* 好吧，它有权保持沉默！ */
That's great!
这个问题发生在今年的1月，用户以操作系统认证形式登陆RAC中的主用实例时发现登陆挂起，但不出现错误。之后应用人员陆续手动杀死服务进程，杀死进程后发现实例可以登录了，应用人员在没有做任何信息转储的情况下重启了数据库，这就造成了我们后期诊断时缺乏必要的信息，也是这个case变成悬案的主要原因。
在实例hang住的一个半小时中告警日志没有任何信息；仅有的有用信息是该实例中diag，pmon，lmd后台进程的trace文件。以下为trace文件:
lmd0 trace:
*** 2010-01-16 11:02:58.106
DUMP LOCAL BLOCKER/HOLDER: block level 5 res [0x10b0005][0xeb5],[TX]
----------resource 0x70000044a76e1a0----------------------
resname : [0x10b0005][0xeb5],[TX]
Local node : 1
dir_node : 1
master_node : 1
hv idx : 25
hv last r.inc : 0
current inc : 20
hv status : 0
hv master : 0
open options : dd
grant_bits : KJUSERNL KJUSEREX
grant mode : KJUSERNL KJUSERCR KJUSERCW KJUSERPR KJUSERPW KJUSEREX
count : 1 0 0 0 0 1
val_state : KJUSERVS_NOVALUE
valblk : 0x00000000000000000000000000000000 .
access_node : 1
vbreq_state : 0
state : x0
resp : 70000044a76e1a0
On Scan_q? : N
Total accesses: 15061
Imm. accesses: 12545
Granted_locks : 1
Cvting_locks : 1
value_block: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
GRANTED_Q :
lp 700000488546990 gl KJUSEREX rp 70000044a76e1a0 [0x10b0005][0xeb5],[TX]
master 1 gl owner 70000048dd08220 possible pid 2928930 xid 2033-0333-0026D22C bast 0 rseq 215 mseq 0 history 0x14951495
open opt KJUSERDEADLOCK
CONVERT_Q:
lp 700000488546ae0 gl KJUSERNL rl KJUSEREX rp 70000044a76e1a0 [0x10b0005][0xeb5],[TX]
master 1 gl owner 70000048ee0ed48 possible pid 2040272 xid 2006-0066-0006F888 bast 0 rseq 215 mseq 0 history 0x1495149a
convert opt KJUSERGETVALUE KJUSERTRCCANCEL
----------enqueue 0x700000488546990------------------------
lock version : 2242641
Owner node : 1
grant_level : KJUSEREX
req_level : KJUSEREX
bast_level : KJUSERNL
notify_func : 0
resp : 70000044a76e1a0
procp : 70000048928b8f0
pid : 2040272
proc version : 40135
oprocp : 0
opid : 0
group lock owner : 70000048dd08220
possible pid : 2928930
xid : 2033-0333-0026D22C
dd_time : 0.0 secs
dd_count : 0
timeout : 0.0 secs
On_timer_q? : N
On_dd_q? : N
lock_state : GRANTED
Open Options : KJUSERDEADLOCK
Convert options : KJUSERNOQUEUE
History : 0x14951495
Msg_Seq : 0x0
res_seq : 215
valblk : 0x00000000000000000000000000000000 .
DUMP LOCAL BLOCKER: initiate state dump for TIMEOUT
possible owner[819.2928930] on resource TX-010B0005-00000EB5
Submitting asynchronized dump request [28]
pmon trace:
*** SESSION ID:(3384.1) 2010-01-16 11:00:11.349
[claim lock for dead process][lp 0x7000004870f7078][p 0x7000004891a79e0.2928930][hist x49514951]
[claim lock for dead process][lp 0x700000488546990][p 0x70000048928b8f0.0][hist x49514951] <<<< note:this message@2010-01-16 12:35
pmon trace:
*** 2010-01-16 11:02:58.244
Dump requested by process [orapid=6]
REQUEST:custom dump [2] with parameters [6][819][2][2]
. process info of pid[819.2928930] requested by pid[6.3621092]
Dumping process 819.2928930 info:
*** 2010-01-16 11:02:58.244
Dumping diagnostic information for ospid 2928930:
OS pid = 2928930
loadavg : 10.83 10.96 10.91
swap info: free_mem = 21646.73M rsv = 128.00M
alloc = 235.52M avail = 32768.00M swap_free = 32532.48M
F S UID PID PPID C PRI NI ADDR SZ WCHAN STIME TTY TIME CMD
240001 A orauser 2928930 1 120 120 20 6da7bd510 174068 10:35:28 - 1:50 oracleCRMDB22 (LOCAL=NO)
open: The file access permissions do not allow the specified action.
procstack: write(/proc/2928930/ctl): The requested resource is busy.
2928930: oracleCRMDB22 (LOCAL=NO)
lmd0进程的trace文件显示该进程发现了本地存在阻塞超时的问题，[0x10b0005][0xeb5],[TX]资源的拥有者是2928930进程，而pmon进程认为2928930进程可能出于僵死状态，故希望RAC中的diag进程能对该进程做进一步的诊断，diag进程受到pmon的邀请”Dump requested by process [orapid=6]”,尝试”Dumping process 819.2928930 info:”，并利用AIX上的procstack调试工具分析此进程当时的调用栈，但出现了”procstack: write(/proc/2928930/ctl): The requested resource is busy”的错误，在网上搜索该记录的相关信息发现可能是AIX上procstack工具的一个Bug，而跟实例挂起问题关系不大:
Problem summary
****************************************************************
* USERS AFFECTED:
* Users of the procstac command on the 5300-09 Technology Level
* with the bos.perf.proctools fileset at the 5.3.9.0 and 5.3.9.1
* levels.
****************************************************************
* PROBLEM DESCRIPTION:
* The procstac command fails with an error similar to:
*
* open: Permission denied
* procstack: write(/proc/2068724/ctl): Device busy
****************************************************************
* RECOMMENDATION:
* Install APAR IZ47903.
****************************************************************
Problem conclusion
*Modified the code to fix this problelm.
diag进程的跟踪文件还显示2928930进程持有redo copy latch；这个case发生后的一个礼拜恰好有Oracle原厂工程师到该客户单位做巡检，原厂工程师查看了实例挂起期间的ASH报告，发现该时段内实例中redo相关的等待频繁发生，基本可以认定是僵死进程持有”redo copy latch”造成了RAC中单实例hang住。
原厂工程师的结论令客户比较满意，这个case基本可以结束了。但我对这个结论并不完全认同，2928930进程持有的redo copy latch是一个子栓，该类子栓在实例上的总数取决于CPU总数，一般来说等于CPU * 2；该实例所在服务器为IBM的p595配有32个cpu，redo copy latch共有64个，单单一个子栓被hold住会造成整个实例都hang住，且登录挂起吗？
SQL> show parameter cpu_count
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cpu_count integer 32
SQL> select count(*) from v$latch_children where name='redo copy';
COUNT(*)
----------
64
/*我们在非生产环境测试一下*/
select addr "十六进制地址",
to_number(addr, 'xxxxxxxxxxxx') "十进制地址",
gets,
misses,
immediate_gets,
immediate_misses
from v$latch_children
where name = 'redo copy';
十六进制 十进制地址 GETS MISSES IMMEDIATE_GETS IMMEDIATE_MISSES
-------- ---------- ---------- ---------- -------------- ----------------
2DAB5898 766204056 7 0 0 0
2DAB5818 766203928 5 0 0 0
2DAB5798 766203800 5 0 0 0
2DAB5718 766203672 5 0 0 0
2DAB5698 766203544 5 0 0 0
2DAB5618 766203416 5 0 0 0
2DAB5598 766203288 5 0 511 0
2DAB5518 766203160 5 0 297024 347
8 rows selected.
/*正式测试前的redo copy child latch统计信息，我们还要用到这里的十进制latch地址*/
/*我们尝试手动hold住一个redo copy child latch，并在其他会话中执行一些产生redo的操作*/
session A:
SQL> oradebug setmypid;
Statement processed.
SQL> oradebug call kslgetl 766203160 1;
Function returned 1
/* kslgetl是Oracle内部用get latch的函数，oradebug 可以调用该函数*/
session B:
SQL> select * From v$latchholder;
PID SID LADDR NAME GETS
---------- ---------- -------- ---------------------------------------------------------------- ----------
22 136 2DAB5518 redo copy 297443
另外开三个session,执行一下批量插入数据的匿名块，测试redo是否能够正常产生：
begin
for i in 1 .. 90000 loop
insert into tv values (i, i, i);
commit;
end loop;
end;
/
PL/SQL procedure successfully completed.
session A:
SQL> oradebug call kslfre 766203160 1;
Function returned 2FD548E0
/*手动释放766203160对应的redo copy 子闩*/
select addr "十六进制地址",
to_number(addr, 'xxxxxxxxxxxx') "十进制地址",
gets,
misses,
immediate_gets,
immediate_misses
from v$latch_children
where name = 'redo copy';
十六进制 十进制地址 GETS MISSES IMMEDIATE_GETS IMMEDIATE_MISSES
-------- ---------- ---------- ---------- -------------- ----------------
2DAB5898 766204056 7 0 0 0
2DAB5818 766203928 5 0 0 0
2DAB5798 766203800 5 0 0 0
2DAB5718 766203672 5 0 745 0
2DAB5698 766203544 5 0 122370 5
2DAB5618 766203416 5 0 176712 5
2DAB5598 766203288 5 0 144441 6
2DAB5518 766203160 6 0 297443 373
/*可以看到虽然8个子闩中一个被手动hold住，但对实例的影响远不足以到达hang所需要的条件*/
虽然能证明单个redo copy latch被僵死进程长期持有和实例hang住没有必然的因果联系，但因为缺少相关的systemstate和hanganalyze转储信息，我们也无法进一步做出推断。
这个case发生后的几个月，我无意中在MOS上发现了以下Bug note:
Diagnostic collection may hang or crash the instance
DescriptionSometimes while the DIAG process is collecting diagnostics
the database may hang or crash.
This is more likely to occur in a RAC or ASM environment .You may be seeing this problem if the database crashes or hangs just
after you see the following text in a trace file:
“Dumping diagnostic information for X”
where X is the process that hung or terminated.This fix replaces invokations of a port specific OS debugger
(like gdb) with Oracle ORADEBUG calls to gather the required
diagnostics.Note:
For HP Itanium fixes for this issue in 10.2.0.3/10.2.0.4 use bug 8767023
instead of this bug#.For AIX fixes for this issue in 10.2.0.3/10.2.0.4 use bug 8929701
instead of this bug#.Workaround
>=10.2.0.3 only: Set init.ora parameter “_ksb_disable_diagpid” = TRUE
but this will disable automatic diagnostics collection.
当diag诊断进程调用系统debugger工具调试问题进程时可能出现实例hang或crash的，且该问题多发于RAC或ASM环境中，workaroud的方法是动态设置_ksb_disable_diagpid为TRUE，可以避免diag诊断进程对问题进程的调试，但这些调试信息往往对我们又是有用的，这倒有些《种树郭橐驼说》中”既然已，勿动勿虑，去不复顾”的味道。
好了这个问题，仍旧pending着，是个悬案。
这是一套SunOS 5.10上的10.2.0.3的RAC系统，8月初告警日志中陆续出现以下记录:
Tue Aug 3 15:17:04 2010 Errors in file /u01/app/oracle/admin/prsi061/udump/prsi061a_ora_27774.trc: ORA-07445: exception encountered: core dump [__lwp_kill()+8] [SIGIOT] [unknown code] [0x6C7E00000000] [] []
SIGIOT信号伴随7445错误出现并不多见，因为该信号一般是用来实现相关的硬件异常的。
我们可以欣赏一下这个trace文件。
trace文件中的堆栈信息如下:
ksedmp()+744 CALL ksedst() 000000840 ? 1066C60CC ?
000000000 ? 1066C2BC0 ?
1066C1928 ? 1066C2328 ?
ssexhd()+1240 CALL ksedmp() 000106400 ? 106530764 ?
106530000 ? 000106530 ?
000106400 ? 106530764 ?
__sighndlr()+12 PTR_CALL 0000000000000000 10652D000 ? 1066C9EF0 ?
10652A72C ? 00010652D ?
000000006 ? 000000067 ?
call_user_handler() CALL __sighndlr() 000000006 ? 1066C9EF0 ?
+992 1066C9C10 ? 10033B1C0 ?
000000000 ? 000000005 ?
sigacthandler()+84 CALL call_user_handler() FFFFFFFF7D500200 ?
FFFFFFFF7D500200 ?
1066C9C10 ? 000000009 ?
000000000 ? 000000000 ?
__lwp_kill()+8 PTR_CALL 0000000000000000 000000000 ? 1066C9EF0 ?
1066C9C10 ?
FFFFFFFF7D500200 ?
000000000 ?
FFFFFFFF7C73C000 ?
raise()+16 FRM_LESS _pthread_kill() 000000000 ? 000000006 ?
FFFFFFFF7F60AC48 ?
FFFFFFFF7C54B048 ?
000000005 ?
FFFFFFFF7C74CB50 ?
abort()+208 CALL raise() 000000006 ? 000000006 ?
000000005 ?
FFFFFFFF7C748500 ?
FFFFFFFF7D500200 ?
000000005 ?
vcsipc_poll()+1724 CALL FFFFFFFF7F5477E0 000001DA0 ?
FFFFFFFF7F550D00 ?
FFFFFFFF7F4205A8 ?
0001F107C ? 000000001 ?
000000000 ?
skgxpwait()+5604 CALL vcsipc_poll() FFFFFFFF7FFECE90 ?
106747378 ? 000001FD0 ?
FFFFFFFF7FFE78C8 ?
000001C00 ? 000200000 ?
ksxpwait()+1804 CALL 0000000106524980 FFFFFFFF7F54FC28 ?
106747378 ? 000000000 ?
FFFFFFFF7FFECF68 ?
0000004E2 ?
FFFFFFFF7FFECE90 ?
ksliwat()+2952 CALL ksxpwait() 000000000 ? 000101000 ?
000000000 ? 10652DB98 ?
000001000 ? 106533FC8 ?
kslwaitns_timed()+4 CALL ksliwat() 000000000 ? 000000002 ?
8 00000007D ? 5798B6C18 ?
5798B6BA0 ? 000032033 ?
kskthbwt()+232 CALL kslwaitns_timed() 00000007D ? 000000001 ?
00000007C ? 000000000 ?
FFFFFFFF7FFED3B8 ?
000000001 ?
kslwait()+116 CALL kskthbwt() 00000007D ? 00000007C ?
000000000 ? 000000007 ?
000032033 ? 000000001 ?
ksxprcv()+916 CALL kslwait() 0925A2B0A ? 000000000 ?
00000000A ? 00000000A ?
000032033 ? 000000001 ?
kclwcrs()+960 CALL ksxprcv() 0001056DE ? 10652B118 ?
00000007D ? 1056DE598 ?
00010652A ? 1056DE000 ?
kclgclk()+10052 CALL kclwcrs() 3800143A8 ? 000000000 ?
000000000 ? 519F716A0 ?
000000007 ? 000106535 ?
kcbzib()+19288 CALL kclgclk() 000106400 ? 00000000C ?
FFFFFFFF7FFF5EB8 ?
000000000 ? 000000000 ?
000105400 ?
kcbgtcr()+10528 CALL kcbzib() 5665FB520 ?
FFFFFFFF7C058170 ?
000105C00 ? 000000000 ?
000000006 ?
FFFFFFFF7FFF44A0 ?
ktrget()+260 CALL kcbgtcr() FFFFFFFF7FFF5028 ?
FFFFFFFF7FFF502C ?
5665FB520 ? 000000000 ?
000000000 ? 57FF6DA18 ?
kdst_fetch()+872 CALL ktrget() FFFFFFFF7C058160 ?
FFFFFFFF7C0580E0 ?
00000023F ? 000000000 ?
FFFFFFFF7C058170 ?
3800172B8 ?
kdstf0100101km()+50 CALL kdst_fetch() FFFFFFFF7C058158 ?
4 000000000 ?
FFFFFFFF7FFF5688 ?
000106528 ? 00000023F ?
00000FC00 ?
kdsttgr()+27872 CALL kdstf0100101km() FFFFFFFF7C058158 ?
4E6AB809E ? 000000001 ?
000000000 ? 54C540BB8 ?
FFFFFFFF7C058038 ?
qertbFetch()+720 CALL kdsttgr() 000000000 ? 000000000 ?
FFFFFFFF7C054EA8 ?
FFFFFFFF7C058158 ?
000000004 ? 1032109C0 ?
qerflFetch()+172 PTR_CALL 0000000000000000 000000001 ? 000000001 ?
1056DE068 ?
FFFFFFFF7FFF6348 ?
10652B298 ? 000000002 ?
opifch2()+8204 PTR_CALL 0000000000000000 FFFFFFFF7C058898 ?
102527EE0 ?
FFFFFFFF7FFF69D0 ?
000000001 ? 103210000 ?
10320CA00 ?
opifch()+52 CALL opifch2() FFFFFFFF7FFF6878 ?
000000090 ? 000000000 ?
000000001 ? 000000000 ?
105A2C000 ?
opipls()+3532 CALL opifch() 000000005 ? 000000002 ?
FFFFFFFF7FFF6F20 ?
000000002 ? 000000000 ?
000000001 ?
opiodr()+1548 PTR_CALL 0000000000000000 000106400 ? 10653A000 ?
000105800 ? 000000010 ?
00010653A ?
FFFFFFFF7B6392D8 ?
rpidrus()+196 CALL opiodr() 10576DC08 ? 000000066 ?
10652B000 ? 000000001 ?
FFFFFFFF7C03A830 ?
00010652D ?
skgmstack()+168 PTR_CALL 0000000000000000 FFFFFFFF7FFF8350 ?
000000006 ?
FFFFFFFF7FFF8100 ?
10652A000 ? 000000066 ?
1056DE000 ?
rpidru()+172 CALL skgmstack() 10034D1E0 ?
FFFFFFFF7FFF8350 ?
00000F618 ? 10034D1E0 ?
FFFFFFFF7FFF8350 ?
FFFFFFFF7FFF8328 ?
rpiswu2()+500 PTR_CALL 0000000000000000 FFFFFFFF7FFF8B18 ?
1056C3000 ? 1056C2B90 ?
1056C0F50 ? 000000C10 ?
000000182 ?
rpidrv()+1696 CALL rpiswu2() 000000000 ? 10652B298 ?
000000000 ?
FFFFFFFF7FFF84E8 ?
1056DE000 ? 00010652A ?
psddr0()+516 CALL rpidrv() FFFFFFFF7FFF8EC0 ?
000105C00 ?
FFFFFFFF7FFF89C4 ?
000000002 ?
FFFFFFFF7B615F60 ?
00010652D ?
psdnal()+512 CALL psddr0() 106541CE0 ? 10652B298 ?
000000066 ? 1056DE068 ?
000000008 ? 00000000A ?
pevm_BFTCHC()+308 PTR_CALL 0000000000000000 FFFFFFFF7FFF9CA8 ?
00000000A ? 000000000 ?
FFFFFFFF7B6396F8 ?
106537000 ? 10652B000 ?
pfrinstr_FTCHC()+18 CALL pevm_BFTCHC() 000000000 ? 105AE7600 ?
0 555E62580 ?
FFFFFFFF7C069EE8 ?
FFFFFFFF7B6396F8 ?
000000000 ?
pfrrun_no_tool()+72 PTR_CALL 0000000000000000 000000000 ? 000000000 ?
FFFFFFFF7C069F50 ?
FFFFFFFF7C069EE8 ?
0000001EE ? 555E62892 ?
pfrrun()+832 CALL pfrrun_no_tool() FFFFFFFF7C069EE8 ?
555E6288E ?
FFFFFFFF7C069F50 ?
105B1BF50 ? 000002001 ?
000002001 ?
plsql_run()+696 CALL pfrrun() FFFFFFFF7C035420 ?
FFFFFFFF7C069EE8 ?
000002001 ? 000200000 ?
FFFFFFFF7C069EE8 ?
0001056DE ?
peicnt()+260 CALL plsql_run() 000000006 ? 000000000 ?
FFFFFFFF7B63BBF8 ?
FFFFFFFF7FFF9888 ?
000000180 ? 000000007 ?
kkxexe()+616 CALL peicnt() FFFFFFFF7FFFA808 ?
10652B298 ? 106541CE0 ?
106762258 ? 10652B000 ?
10652B000 ?
opiexe()+12736 CALL kkxexe() FFFFFFFF7B63F4B8 ?
106537000 ? 000106537 ?
FFFFFFFF7FFF9CA8 ?
000000000 ? 54C0616F0 ?
kpoal8()+1912 CALL opiexe() 000106400 ?
FFFFFFFF7C056EC0 ?
000000000 ? 000000000 ?
000000000 ? 57FDB9250 ?
opiodr()+1548 PTR_CALL 0000000000000000 0BFFFFC00 ? 000040008 ?
000000000 ? 000000820 ?
000105800 ? 106538260 ?
ttcpip()+1284 PTR_CALL 0000000000000000 10576DC08 ? 00000005E ?
10652B000 ? 000000001 ?
FFFFFFFF7C03A830 ?
00010652D ?
opitsk()+1432 CALL ttcpip() 000000017 ?
FFFFFFFF7FFFCFB0 ?
1056C3F6C ? 1056C1750 ?
000000000 ? 10652B118 ?
opiino()+1128 CALL opitsk() 106538268 ? 000000001 ?
000000000 ? 106538260 ?
1058884D0 ? 0FFFFFFFD ?
opiodr()+1548 PTR_CALL 0000000000000000 000106400 ? 10652DB98 ?
000106400 ? 10652D000 ?
000106400 ? 106538260 ?
opidrv()+896 CALL opiodr() 1065373D8 ? 00000003C ?
000106400 ? 1065381E0 ?
000106538 ? 00010652D ?
sou2o()+80 CALL opidrv() 10653A960 ? 000000000 ?
00000003C ? 106537698 ?
00000003C ? 000000000 ?
opimai_real()+124 CALL sou2o() FFFFFFFF7FFFF708 ?
00000003C ? 000000004 ?
FFFFFFFF7FFFF730 ?
105E12000 ? 000105E12 ?
main()+152 CALL opimai_real() 000000002 ?
FFFFFFFF7FFFF808 ?
104054D6C ? 1064D3220 ?
00247E3B4 ? 000014800 ?
_start()+380 CALL main() 000000002 ? 000000008 ?
000000000 ?
FFFFFFFF7FFFF818 ?
FFFFFFFF7FFFF928 ?
FFFFFFFF7D500200 ?
经过和MOS确认，认为是apply了Patch 5165885后引起的新问题:
I have checked our internal bug database and issue seems to be occuring due to fix for Bug.5165885.
Action plan:
=============Apply patch for 6678154
https://updates.oracle.com/download/6678154.html
Workaround:
————–Remove the patch for 5165885 .
Yes, Symptoms are pointing finger towards this bug. I would recommend to apply the patch rather than going for workarounds.
这个case目前实施了补丁6678154，仍在观察期。
录以记之！
做为一个DBA，你大概习惯了定期要抓取数据库中的非绑定变量SQL，这些SQL经常扮演着一箱苹果中蛀虫的角色。
看到下列SQL你必定觉得眼熟:
SELECT substr(sql_text, 1, 80), count(1) FROM v$sql GROUP BY substr(sql_text, 1, 80) HAVING count(1) > 10 ORDER BY 2
是的，以上这段抓取literal sql的脚本大约从8i时代就开始流行了，在那时它很popular也很休闲，使用它或许还会给你的雇主留下一丝神秘感。不过今天我要告诉你的是，它彻底过时了，落伍了，已经不是fashion master了。
10g以后v$SQL动态性能视图增加了FORCE_MATCHING_SIGNATURE列，其官方定义为”The signature used when the CURSOR_SHARING parameter is set to FORCE”，也就是Oracle通过将原SQL_TEXT转换为可能的FORCE模式后计算得到的一个SIGNATURE值。这么说可能不太形象，我们来具体看一下:
SQL> create table YOUYUS (t1 int); Table created. SQL> alter system flush shared_pool; System altered. SQL>select /*test_matching_a*/ * from YOUYUS where t1=1; no rows selected SQL>select /*test_matching_a*/ * from YOUYUS where t1=2; no rows selected SQL>select /*test_matching_a*/ * from YOUYUS where t1=3; no rows selected SQL> col sql_text format a55; SQL> select sql_text, FORCE_MATCHING_SIGNATURE, EXACT_MATCHING_SIGNATURE 2 FROM V$SQL 3 WHERE sql_text like '%test_matching_a%' 4 and sql_text not like '%like%'; SQL_TEXT FORCE_MATCHING_SIGNATURE EXACT_MATCHING_SIGNATURE ------------------------------------------------------- ------------------------ ------------------------ select /*test_matching_a*/ * from YOUYUS where t1=2 4.59124694481197E18 1.00267830752731E19 select /*test_matching_a*/ * from YOUYUS where t1=3 4.59124694481197E18 1.61270448861426E19 select /*test_matching_a*/ * from YOUYUS where t1=1 4.59124694481197E18 1.36782048270058E18 /*以上将变量硬编码至SQL中的游标，FORCE_MATCHING_SIGNATURE值完全相同，而EXACT_MATCHING_SIGNATURE值各有不同。FORCE_MATCHING_SIGNATURE值相同说明在游标共享FORCE模式下，这些游标满足CURSOR SHARING的条件 */ SQL> alter system flush shared_pool; System altered. SQL> alter session set cursor_sharing=FORCE; Session altered. SQL>select /*test_matching_a*/ * from YOUYUS where t1=1; no rows selected SQL>select /*test_matching_a*/ * from YOUYUS where t1=2; no rows selected SQL>select /*test_matching_a*/ * from YOUYUS where t1=3; no rows selected SQL> col sql_text for a70 SQL> select sql_text, FORCE_MATCHING_SIGNATURE, EXACT_MATCHING_SIGNATURE 2 FROM V$SQL 3 WHERE sql_text like '%test_matching_a%' 4 and sql_text not like '%like%'; SQL_TEXT FORCE_MATCHING_SIGNATURE EXACT_MATCHING_SIGNATURE ---------------------------------------------------------------------- ------------------------ ------------------------ select /*test_matching_a*/ * from YOUYUS where t1=:"SYS_B_0" 4.59124694481197E18 4.59124694481197E18 /*FORCE模式下将SQL文本中的变量值转换成了:SYS_B形式，EXACT_MATCHING_SIGNATURE也随之等同于FORCE_MATCHING_SIGNATURE了*/
以上演示说明了FORCE_MATCHING_SIGNATURE列可以帮助我们找出那些潜在可以共享的游标(也包括了因非绑定问题造成的游标无法共享)，现在我们利用它来完善捕获非绑定变量SQL的脚本：
SQL> alter system flush shared_pool;
System altered.
SQL> select /*test_matching_b*/ * from YOUYUS where t1=1;
no rows selected
SQL> select /*test_matching_b*/ * from YOUYUS where t1='1'; //我有引号，我与众不同！
no rows selected
SQL> col sql_text for a70
SQL> select sql_text, FORCE_MATCHING_SIGNATURE, EXACT_MATCHING_SIGNATURE
2 FROM V$SQL
3 WHERE sql_text like '%test_matching_b%'
4 and sql_text not like '%like%';
SQL_TEXT FORCE_MATCHING_SIGNATURE EXACT_MATCHING_SIGNATURE
---------------------------------------------------------------------- ------------------------ ------------------------
select /*test_matching_b*/ * from YOUYUS where t1='1' 1.43666633406896E19 1.83327833675856E19
select /*test_matching_b*/ * from YOUYUS where t1=1 1.43666633406896E19 8.05526057286178E18
/*多余的引号也会导致游标无法共享，此时的FORCE_MATCHING_SIGNATURE 也会是一致的*/
select FORCE_MATCHING_SIGNATURE, count(1)
from v$sql
where FORCE_MATCHING_SIGNATURE > 0
and FORCE_MATCHING_SIGNATURE != EXACT_MATCHING_SIGNATURE
group by FORCE_MATCHING_SIGNATURE
having count(1) > &a
order by 2;
Enter value for a: 10
old 6: having count(1) > &a
new 6: having count(1) > 10
FORCE_MATCHING_SIGNATURE COUNT(1)
------------------------ ----------
8.81463386552502E18 12
So We find it!
在这里再推荐一种来自MOS，find Literal SQL的方法:
How to Find Literal SQL in Shared Pool
Applies to:
PL/SQL – Version: 8.1.7 to 10.2
Information in this document applies to any platform.Goal
There is no direct way to query the dictionary for literal SQL only.
However the following example will try to exclude all SQL statements in the
shared pool that do use bind variables.There still might be situations, with statements using subqueries, where the
example still will show SQL statements using bind variables.Solution
Create the following PL/SQL block:
[maclean@rh2 bin]$ cat find_literal.sql
set serveroutput on
set linesize 120
--
-- This anonymous PL/SQL block must be executed as INTERNAL or SYS
-- Execute from : SQL*PLUS
-- CAUTION:
-- This sample program has been tested on Oracle Server - Enterprise Edition
-- However, there is no guarantee of effectiveness because of the possibility
-- of error in transmitting or implementing it. It is meant to be used as a
-- template, and it may require modification.
--
declare
b_myadr VARCHAR2(20);
b_myadr1 VARCHAR2(20);
qstring VARCHAR2(100);
b_anybind NUMBER;
cursor my_statement is
select address from v$sql
group by address;
cursor getsqlcode is
select substr(sql_text,1,60)
from v$sql
where address = b_myadr;
cursor kglcur is
select kglhdadr from x$kglcursor
where kglhdpar = b_myadr
and kglhdpar != kglhdadr
and kglobt09 = 0;
cursor isthisliteral is
select kkscbndt
from x$kksbv
where kglhdadr = b_myadr1;
begin
dbms_output.enable(10000000);
open my_statement;
loop
Fetch my_statement into b_myadr;
open kglcur;
fetch kglcur into b_myadr1;
if kglcur%FOUND Then
open isthisliteral;
fetch isthisliteral into b_anybind;
if isthisliteral%NOTFOUND Then
open getsqlcode;
fetch getsqlcode into qstring;
dbms_output.put_line('Literal:'||qstring||' address: '||b_myadr);
close getsqlcode;
end if;
close isthisliteral;
end if;
close kglcur;
Exit When my_statement%NOTFOUND;
End loop;
close my_statement;
end;
/
/*尝试执行*/
SQL> @find_literal
Literal:select inst_id, java_size, round(java_size / basejava_size, address: 00000000BC6E94E8
Literal:select reason_id, object_id, subobject_id, internal_instance address: 00000000BC5F1D60
Literal:select DBID, NAME, CREATED, RESETLOGS_CHANGE#, RESETLOGS_TI address: 00000000BC6000B0
Literal:select di.inst_id,di.didbi,di.didbn,to_date(di.dicts,'MM/DD/ address: 00000000BC530DA8
Literal: declare vsn varchar2(20); begin address: 00000000BC85A9F8
Literal:SELECT INCARNATION#, RESETLOGS_CHANGE#, RESETLOGS_TIME, PRIO address: 00000000BC829978
Literal:select pos#,intcol#,col#,spare1,bo#,spare2 from icol$ where address: 00000000BCA84D00
Literal:select SYS_CONTEXT('USERENV', 'SERVER_HOST'), SYS_CONTEXT('U address: 00000000BC771BF0
Literal: select sql_text, FORCE_MATCHING_SIGNATURE, EXACT_MATCHING_S address: 00000000BC4673A8
Literal:select streams_pool_size_for_estimate s, streams_p address: 00000000BCA58848
Literal: select open_mode from v$database address: 00000000BC5DF2D0
Literal:select FORCE_MATCHING_SIGNATURE, count(1) from v$sql wher address: 00000000BCA91628
Literal:select inst_id, tablespace_name, segment_file, segment_block address: 00000000BC66EF38
Literal:select sum(used_blocks), ts.ts# from GV$SORT_SEGMENT gv, t address: 00000000BCAA01B0
Literal:BEGIN DBMS_OUTPUT.ENABLE(NULL); END; address: 00000000BC61D2D8
Literal:select value$ from props$ where name = 'GLOBAL_DB_NAME' address: 00000000BC570500
Literal:select count(*) from sys.job$ where (next_date > sysdate) an address: 00000000BC6C53F8
Literal:select java_pool_size_for_estimate s, java_pool_si address: 00000000BCA65070
Literal:select local_tran_id, global_tran_fmt, global_oracle_id, glo address: 00000000BC5900B8
Literal:select inst_id,kglnaobj,kglfnobj,kglobt03, kglobhs0+kglobhs1 address: 00000000BC921538
Literal:select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname, address: 00000000BCA83E90
Literal:SELECT * FROM V$SQL address: 00000000BCA58BC0
Literal:SELECT ADDRESS FROM V$SQL GROUP BY ADDRESS address: 00000000BC565BE8
Literal: begin dbms_rcvman.resetAll; end; address: 00000000BC759858
Literal:declare b_myadr VARCHAR2(20); b_myadr1 VARCHAR2(20); qstring address: 00000000BC928FF8
Literal:select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, address: 00000000BC898BF8
Literal:select CONF#, NAME, VALUE from GV$RMAN_CONFIGURATION where i address: 00000000BC8CB7F8
Literal:select f.file#, f.block#, f.ts#, f.length from fet$ f, ts$ t address: 00000000BC8CDFE8
Literal:select u.name, o.name, trigger$.sys_evts, trigger$.type# fr address: 00000000BCA877B8
Literal:select id, name, block_size, advice_status, address: 00000000BC636B38
Literal:select incarnation#, resetlogs_change#, resetlogs_time, address: 00000000BCA94250
Literal:select INSTANCE_NUMBER , INSTANCE_NAME , HOST_NAME , VERSIO address: 00000000BC62A678
Literal:select ks.inst_id,ksuxsins,ksuxssid,ksuxshst,ksuxsver,ksuxst address: 00000000BC8E5440
Literal:select timestamp, flags from fixed_obj$ where obj#=:1 address: 00000000BC916C78
Literal:select size_for_estimate, size_factor * address: 00000000BCA5F830
Literal:select shared_pool_size_for_estimate s, shared_pool address: 00000000BCA5A350
Literal:select SQL_TEXT , SQL_FULLTEXT , SQL_ID, SHARABLE_MEM , PE address: 00000000BC76B3A0
Literal:lock table sys.col_usage$ in exclusive mode nowait address: 00000000BCA05978
Literal:select 'x' from dual address: 00000000BC583818
Literal: select name, resetlogs_time, resetlogs_ch address: 00000000BCA9D430
Literal:select inst_id, sp_size, round(sp_size / basesp_size, 4), k address: 00000000BC65A9F0
Literal:select userenv('Instance'), icrid, to_number(icrls), address: 00000000BC692260
Literal:select shared_pool_size_for_estimate, shared_pool_size_facto address: 00000000BCAE0750
Literal:select INST_ID, RMRNO, RMNAM, RMVAL from X$KCCRM where RMNAM address: 00000000BC8CD778
Literal:select metadata from kopm$ where name='DB_FDO' address: 00000000BC9EBB98
Literal:select java_pool_size_for_estimate, java_pool_size_factor, address: 00000000BC5B27D0
Literal:SELECT INCARNATION#, INCARNATION#, RESETLOGS_CHANGE#, RESETL address: 00000000BC829C48
Literal:select file# from file$ where ts#=:1 address: 00000000BC87CF18
Literal:select A.inst_id, A.bpid, B.bp_name, A.blksz, address: 00000000BC802248
Literal:lock table sys.mon_mods$ in exclusive mode nowait address: 00000000BC5CBE68
Literal:lock table sys.mon_mods$ in exclusive mode nowait address: 00000000BC5CBE68
ddl是否会产生undo?
这可能是每一个初学Oracle的人都会有的疑问；ddl操作又不能rollback回滚，要什么undo数据呢？
事实是几乎每个ddl操作都会产生undo,我们来探究一下:
SQL> select vs.name, ms.value
2 from v$mystat ms, v$sysstat vs
3 where ms.statistic# = vs.statistic#
4 and name = 'undo change vector size';
NAME VALUE
---------------------------------------------------------------- ----------
undo change vector size 0
SQL> create table YOUYUS (t1 int);
Table created.
SQL> select vs.name, ms.value
2 from v$mystat ms, v$sysstat vs
3 where ms.statistic# = vs.statistic#
4 and name = 'undo change vector size';
NAME VALUE
---------------------------------------------------------------- ----------
undo change vector size 1992
/*create table的ddl语句产生了大约1992 bytes的撤销变化向量*/
SQL> drop table YOUYUS;
Table dropped.
SQL> select vs.name, ms.value
2 from v$mystat ms, v$sysstat vs
3 where ms.statistic# = vs.statistic#
4 and name = 'undo change vector size';
NAME VALUE
---------------------------------------------------------------- ----------
undo change vector size 4528
/* drop table语句产生2563 bytes的undo数据，多于create table；我们可以猜测create table时Oracle需要向基表中insert数据，而drop table时则需要delete/update数据，显然后者产生更多的undo*/
/*我们尝试创建一个由254个列组成的表*/
SQL> select vs.name, ms.value
2 from v$mystat ms, v$sysstat vs
3 where ms.statistic# = vs.statistic#
4 and name = 'undo change vector size';
NAME VALUE
---------------------------------------------------------------- ----------
undo change vector size 0
create table YOUYUS (
t1 int,
t2 char(4) default 'oooo',
t3 char(4) default 'oooo',
t4 char(4) default 'oooo',
t5 char(4) default 'oooo',
t6 char(4) default 'oooo',
t7 char(4) default 'oooo',
t8 char(4) default 'oooo',
t9 char(4) default 'oooo',
............................
t248 char(4) default 'oooo',
t249 char(4) default 'oooo',
t250 char(4) default 'oooo',
t251 char(4) default 'oooo',
t252 char(4) default 'oooo',
t253 char(4) default 'oooo',
t254 char(4) default 'oooo'
);
SQL> select vs.name, ms.value
2 from v$mystat ms, v$sysstat vs
3 where ms.statistic# = vs.statistic#
4 and name = 'undo change vector size';
NAME VALUE
---------------------------------------------------------------- ----------
undo change vector size 85832
/*产生了83k的undo，ddl所产生的undo量视乎其所要维护数据字典的操作类型和操作量*/
SQL> oradebug setmypid;
Statement processed.
SQL> oradebug event 10046 trace name context forever,level 1;
Statement processed.
SQL> drop table YOUYUS;
Table dropped.
SQL> select vs.name, ms.value
2 from v$mystat ms, v$sysstat vs
3 where ms.statistic# = vs.statistic#
4 and name = 'undo change vector size';
NAME VALUE
---------------------------------------------------------------- ----------
undo change vector size 214020
/*drop 产生了125k的undo*/
SQL> oradebug tracefile_name;
/home/maclean/app/maclean/diag/rdbms/prod/PROD/trace/PROD_ora_5433.trc
/* 我们来看看drop table 到底做了哪些递归操作? */
[maclean@rh2 ~]$ cat PROD_ora_5433.trc|egrep "delete|update"
'Need use delete_topo_geometry_layer() to deregister table '
select decode(u.type#, 2, u.ext_username, u.name), o.name, t.update$, t.insert$, t.delete$, t.enabled, decode(bitand(t.property, 8192),8192, 1, 0), decode(bitand(t.property, 65536), 65536, 1, 0), decode(bitand(t.property, 131072), 131072, 1, 0), (select o.name from obj$ o where o.obj# = u.spare2 and o.type# =57) from sys.obj$ o, sys.user$ u, sys.trigger$ t, sys.obj$ bo where t.baseobject=bo.obj# and bo.name = :1 and bo.spare3 = :2 and bo.namespace = 1 and t.obj#=o.obj# and o.owner#=u.user# and o.type# = 12 and bitand(property,16)=0 and bitand(property,8)=0 order by o.obj#
delete from object_usage where obj# in (select a.obj# from object_usage a, ind$ b where a.obj# = b.obj# and b.bo# = :1)
delete from sys.cache_stats_1$ where dataobj# = :1
delete com$ where obj#=:1
delete from hist_head$ where obj# = :1
delete from dependency$ where d_obj#=:1
delete from source$ where obj#=:1
delete from compression$ where obj#=:1
m_stmt:='delete from sdo_geor_ddl__table$$ where id=2';
m_stmt:='delete from sdo_geor_ddl__table$$';
delete from sdo_geor_ddl__table$$ where id=2
delete from col$ where obj#=:1
delete from icol$ where bo#=:1
delete from icoldep$ where obj# in (select obj# from ind$ where bo#=:1)
delete from jijoin$ where obj# in ( select obj# from jijoin$ where tab1obj# = :1 or tab2obj# = :1)
delete from jirefreshsql$ where iobj# in ( select iobj# from jirefreshsql$ where tobj# = :1)
delete from ccol$ where obj#=:1
delete from ind$ where bo#=:1
delete from cdef$ where obj#=:1
delete ecol$ where tabobj# = :1
delete from tab$ where obj#=:1
delete from idl_ub1$ where obj#=:1 and part=:2
delete from idl_char$ where obj#=:1 and part=:2
delete from idl_ub2$ where obj#=:1 and part=:2
delete from idl_sb4$ where obj#=:1 and part=:2
delete from ncomp_dll$ where obj#=:1 returning dllname into :2
delete from idl_ub1$ where obj#=:1 and part=:2
delete from idl_char$ where obj#=:1 and part=:2
delete from idl_ub2$ where obj#=:1 and part=:2
delete from idl_sb4$ where obj#=:1 and part=:2
delete from ncomp_dll$ where obj#=:1 returning dllname into :2
delete from idl_ub1$ where obj#=:1 and part=:2
delete from idl_char$ where obj#=:1 and part=:2
delete from idl_ub2$ where obj#=:1 and part=:2
delete from idl_sb4$ where obj#=:1 and part=:2
delete from ncomp_dll$ where obj#=:1 returning dllname into :2
delete from col$ where obj#=:1
delete coltype$ where obj#=:1
delete from subcoltype$ where obj#=:1
delete ntab$ where obj#=:1
delete lob$ where obj#=:1
delete refcon$ where obj#=:1
delete from opqtype$ where obj#=:1
delete from cdef$ where obj#=:1
delete from objauth$ where obj#=:1
delete from obj$ where obj# = :1
update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,extsize=:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535, NULL, :13),groups=decode(:14, 65535, NULL, :14), cachehint=:15, hwmincr=:16, spare1=DECODE(:17,0,NULL,:17),scanhint=:18, bitmapranges=:19 where ts#=:1 and file#=:2 and block#=:3
update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,extsize=:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535, NULL, :13),groups=decode(:14, 65535, NULL, :14), cachehint=:15, hwmincr=:16, spare1=DECODE(:17,0,NULL,:17),scanhint=:18, bitmapranges=:19 where ts#=:1 and file#=:2 and block#=:3
delete from seg$ where ts#=:1 and file#=:2 and block#=:3
/*如果ddl操作执行失败又会如何呢？*/
SQL> oradebug setmypid;
Statement processed.
SQL> oradebug event 10046 trace name context forever,level 1;
Statement processed.
SQL> select vs.name, ms.value
2 from v$mystat ms, v$sysstat vs
3 where ms.statistic# = vs.statistic#
4 and name = 'undo change vector size';
NAME VALUE
---------------------------------------------------------------- ----------
undo change vector size 0
SQL> drop table YOUYUS;
drop table YOUYUS
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select vs.name, ms.value
2 from v$mystat ms, v$sysstat vs
3 where ms.statistic# = vs.statistic#
4 and name = 'undo change vector size';
NAME VALUE
---------------------------------------------------------------- ----------
undo change vector size 264
/*同样产生了undo,量较少*/
SQL> oradebug tracefile_name;
/home/maclean/app/maclean/diag/rdbms/prod/PROD/trace/PROD_ora_5494.trc
[maclean@rh2 trace]$ cat PROD_ora_5494.trc|egrep "update|insert|delete"
'Need use delete_topo_geometry_layer() to deregister table '
m_stmt:='insert into sdo_geor_ddl__table$$ values (1)';
m_stmt:='insert into sdo_geor_ddl__table$$ values (2)';
insert into sdo_geor_ddl__table$$ values (2)
m_stmt:='delete from sdo_geor_ddl__table$$';
delete from sdo_geor_ddl__table$$
/*执行少量递归操作后，Oracle发现所要drop的对象并不存在，将会rollback之前的"部分"递归dml操作*/
其实我们可以把ddl操作分解为以下步骤:
begin
commit;
--编译ddl
begin
--实现ddl,包括一系列递归的数据字典维护操作及其他操作
commit;
exception
when others then
rollback;
end;
end;
ddl操作无需也不允许手动commit或rollback参与，但这并不代表ddl操作不产生undo。
Oracle Certified Master(OCM) 大师认证资质是Oracle认证的最高级别。此认证是对技术、知识和操作技能的最高级别的认可。Oracle认证大师是解决最困难的技术难题和最复杂的系 统故障的最佳Oracle专家人选。资深专家级Oracle 技能考试，通过后将成为企业内的资深专家和顾问。OCM 不但有能力处理关键业务数据库系统和应用，还能帮助客户解决所有的Oracle 技术困难。要想获得OCM 证书，必须先通过OCA、OCP考试，再学习两门高级技术课程，然后在Oracle 实验室通过场景实验考试。场景实验考试的目的是测试您的实际问题分析和故障解决能力。
作为一个过来人，我十分愿意分享我对备考OCM的几点建议:
| 这对于你来说既是一次考试，也是一次系统复习的好机会；OCM考试的面非常广，部分环节可能是你从未实践过的。 |
| 准备时间最好不要少于半年，但也千万不要超过半年。 |
| 准备阶段使用考纲指定的操作系统,DB,OMS,Clusterware版本。这里推荐在Redhat或Oracle Enterprise Linux 4.7平台上练习，切勿使用windows或windows远程登录练习。 |
| 2天的考试是对体力的考验，所以要保证充足的睡眠。 |
| 试卷分英语和日语两版；题目文字量较多，需认真阅读；如果对题意并不明了，可以让监考老师进一步解释，一般不会推辞。 |
| 很多考试中要求建立的对象可以通过OMS获得SQL语句，确认后输入到SQLPLUS中执行。 |
| 部分环节需要十分耐心，如果你不想在屏幕前焦急等待，可以去上个厕所或者泡杯咖啡，总之请放轻松。 |
| 如果你不熟悉Grid Control(OMS)界面，那么可以尝试读一下《Oracle.Enterprise.Manager.10g.Grid.Control.Implementation.Guide.2009》，并勤加练习。 |
| 考试期间可以阅读10g的官方文档，也可以查询metalink(MOS)，前提是在你有足够时间的情况下。 |
| 安装配置Grid Control(OMS)是考试的一个高危阶段，这往往决定于你的安装配置经验以及人品，但是一旦出现了问题而你毫无troubleshooting Grid Control的能力，那么就显得事倍功半了。 |
| 考试后的hand on是一个”复杂的”过程，请致电OU的800电话。 |
to be continued …………
