ORA-19808错误一例

2011/02/10 by Leave a Comment

一套Linux上的11.2.0.2 RAC系统，其中一个节点startup mount时出现ORA-19808错误，日志如下：


SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-01105: mount is incompatible with mounts by other instances
ORA-19808: recovery destination parameter mismatch


SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options


[oracle@rh3 ~]$ oerr ora 19808
19808, 00000, "recovery destination parameter mismatch"
// *Cause:  The value of parameters DB_RECOVERY_FILE_DEST and
//          DB_RECOVERY_FILE_DEST_SIZE must be same in all instances.
//          instance. All databases must have same recovery destination
//          parameters.
// *Action: Check DB_RECOVERY_FILE_DEST and DB_RECOVERY_FILE_DEST_SIZE
//          values in all instances.

SQL> col name for a30
SQL> col value for a30

SQL> select name ,value,inst_id from gv$system_parameter  where name='db_recovery_file_dest_size';

NAME                           VALUE                             INST_ID
------------------------------ ------------------------------ ----------
db_recovery_file_dest_size     0                                       1
db_recovery_file_dest_size     42278584320                             2

该错误是由于启动节点使用了pfile形式的参数文件，而该参数文件中的db_recovery_file_dest_size值与已经启动的另一个节点的db_recovery_file_dest_size不一致所造成。

如果使用共享的server parameter file则不可能出现上述情况，当然也可以通过在启动节点上修改db_recovery_file_dest_size来解决问题。

Filed Under: Oracle, Oracle 11g新特性 Tagged With: , ,

Oracle 11g中数据库能有多大？

2010/08/29 by Leave a Comment

根据11g release 2文档《Oracle® Database Reference 11g Release 2 (11.2)》，我们可以计算出11g中数据库大小的一个理论极限:

Physical Database Limits

Item Type of Limit Limit Value
Database Block Size Minimum 2048 bytes; must be a multiple of operating system physical block size
Database Block Size Maximum Operating system dependent; never more than 32 KB
Database Blocks Minimum in initial extent of a segment 2 blocks
Database Blocks Maximum per datafile Platform dependent; typically 222 – 1 blocks
Controlfiles Number of control files 1 minimum; 2 or more (on separate devices) strongly recommended
Controlfiles Size of a control file Dependent on operating system and database creation options; maximum of 20,000 x (database block size)
Database files Maximum per tablespace Operating system dependent; usually 1022
Database files Maximum per database 65533

May be less on some operating systems

Limited also by size of database blocks and by the DB_FILES initialization parameter for a particular instance
Database extents Maximum per dictionary managed tablespace 4 GB * physical block size (with K/M modifier); 4 GB (without K/M modifier)
Database extents Maximum per locally managed (uniform) tablespace 2 GB * physical block size (with K/M modifier); 2 GB (without K/M modifier)
Database file size Maximum Operating system dependent. Limited by maximum operating system file size; typically 222 or 4 MB blocks
MAXEXTENTS Default value Derived from tablespace default storage or DB_BLOCK_SIZE initialization parameter
MAXEXTENTS Maximum Unlimited
Redo Log Files Maximum number of logfiles Limited by value of MAXLOGFILES parameter in the CREATE DATABASE statementControl file can be resized to allow more entries; ultimately an operating system limit
Redo Log Files Maximum number of logfiles per group Unlimited
Redo Log File Size Minimum size 4 MB
Redo Log File Size Maximum Size Operating system limit; typically 2 GB
Tablespaces Maximum number per database 64 K

Number of tablespaces cannot exceed the number of database files because each tablespace must include at least one file
Bigfile Tablespaces Number of blocks A bigfile tablespace contains only one datafile or tempfile, which can contain up to approximately 4 billion ( 232 ) blocks. The maximum size of the single datafile or tempfile is 128 terabytes (TB) for a tablespace with 32 K blocks and 32 TB for a tablespace with 8 K blocks.
Smallfile (traditional) Tablespaces Number of blocks A smallfile tablespace is a traditional Oracle tablespace, which can contain 1022 datafiles or tempfiles, each of which can contain up to approximately 4 million (222) blocks.
External Tables file Maximum size Dependent on the operating system.An external table can be composed of multiple files.

11g中当采用32 K块时单个表空间最大可以扩展到128 TB，而一个数据库最多拥有64 K个表空间，则可以得出单个数据库的理论最大值为128TB *64 K= 8192 PB= 8EB，该数据与Oracle 10g 数据库的容量持平，换而言之11g中数据库的容量并未增加。

Youtube视频网站一年产生的信息量大约在800TB，世界上1000强的公司所有数据库的总数据量约合1EB(平均一个公司1PB)，而64位可寻址内存的最大容量为16EB，理论上我们可以把所有这些少于8EB的信息存储到您的笔记本上安装的Oracle数据库中，而且可以将它们完全缓存到内存中以供读取。

在几年前没有人相信TB级别的数据库会在不久后普及，但是我们今天可以很自信地说随着TB级别的数据库不断涌现，无论从硬件角度或者软件角度支撑该数量级的数据库的solution都已经十分成熟了。

在11g未来的日子里，我们将注目于EB级数据库的出现！

Filed Under: Oracle Tagged With: , , , , ,

发一个Oracle Dba招聘启事

2010/08/28 by Leave a Comment

公司招聘初中级Oracle Dba。主要向客户提供oracle数据库技术支持服务。
工作地点在上海，具体薪资视乎应聘者的技术水平。

具体要求：
1.熟悉Oracle基础理论和知识，对Oracle技术有持久的兴趣
2.熟悉数据库常见错误和简单性能问题的诊断
3.不对经验作强制要求，当然有经验更佳
4.熟悉unix/solaris操作系统/存储等硬件环境者优先
5.拥有ocp证书者优先

如有意向，请将简历发至 maclean.liu@parnassusdata.com
该职位长期招聘中！

Filed Under: Oracle Tagged With: , ,

Database Force open example

2010/08/27 by 3 Comments

帮网友强制打开了一个没有备份的测试库，这个库没有备份也没有打开归档，因为之前也出现过active日志文件损毁，一直使用隐式参数才能正常打开:

                 _allow_resetlogs_corruption= TRUE

 

如果自己搞不定可以找诗檀软件专业ORACLE数据库修复团队成员帮您恢复!

诗檀软件专业数据库修复团队

服务热线 ： 13764045638   QQ号:47079569    邮箱：service@parnassusdata.com

 

 

这次一开始这个库报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

这个case告诉我们，测试库并不一定就不重要，测试库也是需要备份的。

Filed Under: Oracle Tagged With: , , , , , , ,

SGA_MAX_SIZE,SGA_TARGET以及PRE_PAGE_SGA参数

2010/08/26 by 4 Comments

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使用时，这的确可能造成一种浪费 ! * /

10g中Oracle已经强烈不建议使用pre_page_sga参数了，在ASMM下它带来的问题可不小!

Filed Under: Oracle Tagged With: , , , ,

undo backup optimization does not work on 11.2.0.1?

2010/08/25 by 2 Comments

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!
Filed Under: Oracle Tagged With: , , , , , ,

关于DataPump的external_table模式

2010/08/24 by 4 Comments

在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类型列，则此列只能是最后一列
使用Expdp执行导出任务时没有为相关表指定QUERY, SAMPLE, or REMAP_DATA等参数

需要导出的表或分区相对较少(多达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!
Filed Under: Oracle Tagged With: , , , , ,

Pending Problem

2010/08/23 by 3 Comments

这个问题发生在今年的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
Description

Sometimes 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着，是个悬案。

Filed Under: Oracle Tagged With: , , , , ,

ORA-07445: [__lwp_kill()+8] [SIGIOT]错误一例

2010/08/19 by 6 Comments

这是一套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，仍在观察期。
录以记之！

Filed Under: Oracle Tagged With: , , , , , ,

利用FORCE_MATCHING_SIGNATURE捕获非绑定变量SQL

2010/08/19 by 12 Comments

做为一个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
Filed Under: Oracle Tagged With: , , , , , ,

沪ICP备14014813号-2

沪公网安备 31010802001379号