分类：oracle

重建控制文件时resetlogs与noresetlogs的使用情况

控制文件中记录着数据库的数据文件,日志文件,备份数据等信息,更为重要的,控制文件中还记录了数据库的检查点
和scn信息,这些信息在数据恢复的过程中将起到关键性作用.

一个正常运行的数据库,通常控制文件都存在多份镜像,这些镜像的内容是完全相同的,oracle缺省就创建多份控制
文件更说明了控制文件的重要:

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/product/11.2.0/oradata/jingyong/control01.ctl
/u01/app/oracle/product/11.2.0/oradata/jingyong/control02.ctl

可以通过如下一条命令将控制文件的创建语句备份到跟踪文件中:

SQL> alter database backup controlfile to trace;

Database altered.

SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/jingyong/jingyong/trace/jingyong_ora_18818.trc

SQL> host sz /u01/app/oracle/diag/rdbms/jingyong/jingyong/trace/jingyong_ora_18818.trc
rz
Starting zmodem transfer.  Press Ctrl+C to cancel.
  100%       8 KB    8 KB/s 00:00:01       0 Errors

此跟踪文件中会记录控制文件的创建脚本,脚本包含两个主要的段落,其中一段如下所示:

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "JINGYONG" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/app/oracle/product/11.2.0/oradata/jingyong/redo01.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/u01/app/oracle/product/11.2.0/oradata/jingyong/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/u01/app/oracle/product/11.2.0/oradata/jingyong/redo03.log'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/u01/app/oracle/product/11.2.0/oradata/jingyong/system01.dbf',
  '/u01/app/oracle/product/11.2.0/oradata/jingyong/sysaux01.dbf',
  '/u01/app/oracle/product/11.2.0/oradata/jingyong/undotbs01.dbf',
  '/u01/app/oracle/product/11.2.0/oradata/jingyong/users01.dbf',
  '/u01/app/oracle/product/11.2.0/oradata/jingyong/example01.dbf',
  '/u01/app/oracle/product/11.2.0/oradata/jingyong/jy01.dbf'
CHARACTER SET ZHS16GBK
;

当数据库处于nomount状态下时,可以通过运行这段脚本创建控制文件,控制文件会自动创建到参数文件中
记录控制文件的位置(原来的控制文件在创建过程会被覆盖).这里需要理解的一个主要选项是:
noresetlogs/resetlogs.在跟踪文件中包含如下注释,详细解释了这两个选项的含义:

-- Below are two sets of SQL statements, each of which creates a new
-- control file and uses it to open the database. The first set opens
-- the database with the NORESETLOGS option and should be used only if
-- the current versions of all online logs are available. The second
-- set opens the database with the RESETLOGS option and should be used
-- if online logs are unavailable.
-- The appropriate set of statements can be copied from the trace into
-- a script. file, edited as necessary, and executed when there is a
-- need to re-create the control file.

当数据库当前的redo log都可用时,可以通过noresetlogs参数重建控制文件,此时oracle能够从日志文件中
读取redo信息,记录到控制文件中,由于redo中记录的信息足以重演所有提交成功的事务,所以最终能够实现
完全恢复,成功打开数据库,这时的数据库就如同进行了一次断电之后的实例恢复,数据没有损失,重做日志
可以继续向前写入:

下面测试来看一下以noresetlogs重建控制文件进行数据库恢复的过程
先在数据库正常运行状态下对控制文件执行一次转储:

SQL> alter session set events 'immediate trace name controlf level 12';

Session altered.

SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/jingyong/jingyong/trace/jingyong_ora_19350.trc

这个转储文件中将包含数据库的检查点,redo thread信息,数据文件等信息,看一下
log file records内容:

***************************************************************************
LOG FILE RECORDS
***************************************************************************
 (size = 72, compat size = 72, section max = 16, section in-use = 3,
  last-recid= 3, old-recno = 0, last-recno = 0)
 (extent = 1, blkno = 10, numrecs = 16)
LOG FILE #1:
  name #3: /u01/app/oracle/product/11.2.0/oradata/jingyong/redo01.log
 Thread 1 redo log links: forward: 2 backward: 0
 siz: 0x19000 seq: 0x00000010 hws: 0x2 bsz: 512 nab: 0x1e flg: 0x1 dup: 1
 Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.000ea466
 Low scn: 0x0000.000ea474 05/02/2013 11:40:58
 Next scn: 0x0000.000ea4db 05/02/2013 11:44:07
LOG FILE #2:
  name #2: /u01/app/oracle/product/11.2.0/oradata/jingyong/redo02.log
 Thread 1 redo log links: forward: 3 backward: 1
 siz: 0x19000 seq: 0x00000011 hws: 0x1 bsz: 512 nab: 0xffffffff flg: 0x8 dup: 1
 Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.000ea474
 Low scn: 0x0000.000ea4db 05/02/2013 11:44:07
 Next scn: 0xffff.ffffffff 01/01/1988 00:00:00
LOG FILE #3:
  name #1: /u01/app/oracle/product/11.2.0/oradata/jingyong/redo03.log
 Thread 1 redo log links: forward: 0 backward: 2
 siz: 0x19000 seq: 0x0000000f hws: 0x2 bsz: 512 nab: 0x2 flg: 0x1 dup: 1
 Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.000e8ed8
 Low scn: 0x0000.000ea466 05/02/2013 11:40:52
 Next scn: 0x0000.000ea474 05/02/2013 11:40:58

从记录信息中我们可以看到redo02.log文件的next scn:0xffff.ffffffff,所以redo02.log文件是当前的
日志文件,我们可以从v$log视图中查看当前的重做日志组

SQL> select group#,status from v$log;

    GROUP# STATUS
---------- ----------------
         1 INACTIVE
         2 CURRENT
         3 INACTIVE

接下来通过shutdown abort模拟一次数据库故障:

SQL> shutdown abort;
ORACLE instance shut down.

启动数据库到nomount状态,再来使用noresetlogs参数来重建控制文件:

SQL> startup nomount;
ORACLE instance started.

Total System Global Area  238530560 bytes
Fixed Size                  1335724 bytes
Variable Size             150998612 bytes
Database Buffers           83886080 bytes
Redo Buffers                2310144 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "JINGYONG" NORESETLOGS  ARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 292
  7  LOGFILE
  8    GROUP 1 '/u01/app/oracle/product/11.2.0/oradata/jingyong/redo01.log'  SIZE 50M BLOCKSIZE 512,
  9    GROUP 2 '/u01/app/oracle/product/11.2.0/oradata/jingyong/redo02.log'  SIZE 50M BLOCKSIZE 512,
 10    GROUP 3 '/u01/app/oracle/product/11.2.0/oradata/jingyong/redo03.log'  SIZE 50M BLOCKSIZE 512
 11  -- STANDBY LOGFILE
 12  DATAFILE
 13    '/u01/app/oracle/product/11.2.0/oradata/jingyong/system01.dbf',
 14    '/u01/app/oracle/product/11.2.0/oradata/jingyong/sysaux01.dbf',
 15    '/u01/app/oracle/product/11.2.0/oradata/jingyong/undotbs01.dbf',
 16    '/u01/app/oracle/product/11.2.0/oradata/jingyong/users01.dbf',
 17    '/u01/app/oracle/product/11.2.0/oradata/jingyong/example01.dbf',
 18    '/u01/app/oracle/product/11.2.0/oradata/jingyong/jy01.dbf'
 19  CHARACTER SET ZHS16GBK
 20  ;

Control file created.

此时再来对控制文件进行一次转储,检查log file records部分:

SQL> alter session set events 'immediate trace name controlf level 12';

Session altered.

SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/jingyong/jingyong/trace/jingyong_ora_19438.trc

***************************************************************************
LOG FILE RECORDS
***************************************************************************
 (size = 72, compat size = 72, section max = 16, section in-use = 3,
  last-recid= 0, old-recno = 0, last-recno = 0)
 (extent = 1, blkno = 10, numrecs = 16)
LOG FILE #1:
  name #2: /u01/app/oracle/product/11.2.0/oradata/jingyong/redo01.log
 Thread 1 redo log links: forward: 2 backward: 0
 siz: 0x19000 seq: 0x00000010 hws: 0x2 bsz: 512 nab: 0x1e flg: 0x0 dup: 1
 Archive links: fwrd: 2 back: 3 Prev scn: 0x0000.000ea466
 Low scn: 0x0000.000ea474 05/02/2013 11:40:58
 Next scn: 0x0000.000ea4db 05/02/2013 11:44:07
LOG FILE #2:
  name #1: /u01/app/oracle/product/11.2.0/oradata/jingyong/redo02.log
 Thread 1 redo log links: forward: 3 backward: 1
 siz: 0x19000 seq: 0x00000011 hws: 0x1 bsz: 512 nab: 0xffffffff flg: 0xa dup: 1
 Archive links: fwrd: 0 back: 1 Prev scn: 0x0000.000ea474
 Low scn: 0x0000.000ea4db 05/02/2013 11:44:07
 Next scn: 0xffff.ffffffff 01/01/1988 00:00:00
LOG FILE #3:
  name #3: /u01/app/oracle/product/11.2.0/oradata/jingyong/redo03.log
 Thread 1 redo log links: forward: 0 backward: 2
 siz: 0x19000 seq: 0x0000000f hws: 0x2 bsz: 512 nab: 0x2 flg: 0x0 dup: 1
 Archive links: fwrd: 1 back: 0 Prev scn: 0x0000.00000000
 Low scn: 0x0000.000ea466 05/02/2013 11:40:52
 Next scn: 0x0000.000ea474 05/02/2013 11:40:58

从上面的记录我们可以看到重建的控文件能够从当前的日志文件获得正确的SCN及时间点等信息.同样地,控制
文件也能够从数据文件中获得详细的检查点信息:

***************************************************************************
DATA FILE RECORDS
***************************************************************************
 (size = 520, compat size = 520, section max = 100, section in-use = 6,
  last-recid= 0, old-recno = 0, last-recno = 0)
 (extent = 1, blkno = 11, numrecs = 100)
DATA FILE #1:
  name #9: /u01/app/oracle/product/11.2.0/oradata/jingyong/system01.dbf
creation size=0 block size=8192 status=0x12 head=9 tail=9 dup=1
 tablespace 0, index=1 krfil=1 prev_file=0
 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
 Checkpoint cnt:119 scn: 0x0000.000ea4db 05/02/2013 11:44:07
 Stop scn: 0xffff.ffffffff 05/02/2013 12:44:28
 Creation Checkpointed at scn:  0x0000.00000007 08/13/2009 23:00:53
 thread:0 rba:(0x0.0.0)
 .....
 DATA FILE #2:
  name #8: /u01/app/oracle/product/11.2.0/oradata/jingyong/sysaux01.dbf
creation size=0 block size=8192 status=0x12 head=8 tail=8 dup=1
 tablespace 1, index=2 krfil=2 prev_file=0
 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
 Checkpoint cnt:119 scn: 0x0000.000ea4db 05/02/2013 11:44:07
 Stop scn: 0xffff.ffffffff 05/02/2013 12:44:28
 Creation Checkpointed at scn:  0x0000.00000874 08/13/2009 23:00:57
 thread:0 rba:(0x0.0.0)
 .....
 DATA FILE #3:
  name #7: /u01/app/oracle/product/11.2.0/oradata/jingyong/undotbs01.dbf
creation size=0 block size=8192 status=0x12 head=7 tail=7 dup=1
 tablespace 2, index=3 krfil=3 prev_file=0
 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
 Checkpoint cnt:47 scn: 0x0000.000ea4db 05/02/2013 11:44:07
 Stop scn: 0xffff.ffffffff 05/02/2013 12:44:28
 Creation Checkpointed at scn:  0x0000.000b7982 08/13/2009 23:56:54
 thread:0 rba:(0x0.0.0)
 .....
 DATA FILE #4:
  name #6: /u01/app/oracle/product/11.2.0/oradata/jingyong/users01.dbf
creation size=0 block size=8192 status=0x12 head=6 tail=6 dup=1
 tablespace 4, index=4 krfil=4 prev_file=0
 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
 Checkpoint cnt:118 scn: 0x0000.000ea4db 05/02/2013 11:44:07
 Stop scn: 0xffff.ffffffff 05/02/2013 12:44:28
 Creation Checkpointed at scn:  0x0000.00004743 08/13/2009 23:01:06
 thread:0 rba:(0x0.0.0)
 ....
 DATA FILE #5:
  name #5: /u01/app/oracle/product/11.2.0/oradata/jingyong/example01.dbf
creation size=0 block size=8192 status=0x12 head=5 tail=5 dup=1
 tablespace 6, index=5 krfil=5 prev_file=0
 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
 Checkpoint cnt:43 scn: 0x0000.000ea4db 05/02/2013 11:44:07
 Stop scn: 0xffff.ffffffff 05/02/2013 12:44:28
 Creation Checkpointed at scn:  0x0000.000bf3fe 04/25/2013 14:05:52
 thread:0 rba:(0x0.0.0)
 ....
 DATA FILE #6:
  name #4: /u01/app/oracle/product/11.2.0/oradata/jingyong/jy01.dbf
creation size=0 block size=8192 status=0x12 head=4 tail=4 dup=1
 tablespace 7, index=6 krfil=6 prev_file=0
 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
 Checkpoint cnt:11 scn: 0x0000.000ea96d 05/02/2013 12:00:47
 Stop scn: 0xffff.ffffffff 05/02/2013 12:44:28
 Creation Checkpointed at scn:  0x0000.000e9b4f 05/02/2013 08:43:22
 thread:0 rba:(0x0.0.0)
 .....

从上面的信息可以知道由于数据库是异常关闭的,所以数据文件的Stop scn:为无穷大:
Stop scn: 0xffff.ffffffff,接下来对数据库执行恢复,当恢复完成后再对控制文件进行转储:

SQL> recover database;
Media recovery complete.

SQL> alter session set events 'immediate trace name controlf level 12';

Session altered.

SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/jingyong/jingyong/trace/jingyong_ora_19450.trc

来观察此跟踪文件中的数据文件信息:

***************************************************************************
DATA FILE RECORDS
***************************************************************************
 (size = 520, compat size = 520, section max = 100, section in-use = 6,
  last-recid= 0, old-recno = 0, last-recno = 0)
 (extent = 1, blkno = 11, numrecs = 100)
DATA FILE #1:
  name #9: /u01/app/oracle/product/11.2.0/oradata/jingyong/system01.dbf
creation size=0 block size=8192 status=0x2 head=9 tail=9 dup=1
 tablespace 0, index=1 krfil=1 prev_file=0
 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
 Checkpoint cnt:120 scn: 0x0000.000efd7d 05/02/2013 12:43:16
 Stop scn: 0x0000.000efd7d 05/02/2013 12:43:16
 Creation Checkpointed at scn:  0x0000.00000007 08/13/2009 23:00:53
 thread:0 rba:(0x0.0.0)
 ....
DATA FILE #2:
  name #8: /u01/app/oracle/product/11.2.0/oradata/jingyong/sysaux01.dbf
creation size=0 block size=8192 status=0x2 head=8 tail=8 dup=1
 tablespace 1, index=2 krfil=2 prev_file=0
 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
 Checkpoint cnt:120 scn: 0x0000.000efd7d 05/02/2013 12:43:16
 Stop scn: 0x0000.000efd7d 05/02/2013 12:43:16
 Creation Checkpointed at scn:  0x0000.00000874 08/13/2009 23:00:57
 thread:0 rba:(0x0.0.0)
....
DATA FILE #3:
  name #7: /u01/app/oracle/product/11.2.0/oradata/jingyong/undotbs01.dbf
creation size=0 block size=8192 status=0x2 head=7 tail=7 dup=1
 tablespace 2, index=3 krfil=3 prev_file=0
 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
 Checkpoint cnt:48 scn: 0x0000.000efd7d 05/02/2013 12:43:16
 Stop scn: 0x0000.000efd7d 05/02/2013 12:43:16
 Creation Checkpointed at scn:  0x0000.000b7982 08/13/2009 23:56:54
 thread:0 rba:(0x0.0.0)
 ....
DATA FILE #4:
  name #6: /u01/app/oracle/product/11.2.0/oradata/jingyong/users01.dbf
creation size=0 block size=8192 status=0x2 head=6 tail=6 dup=1
 tablespace 4, index=4 krfil=4 prev_file=0
 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
 Checkpoint cnt:119 scn: 0x0000.000efd7d 05/02/2013 12:43:16
 Stop scn: 0x0000.000efd7d 05/02/2013 12:43:16
 Creation Checkpointed at scn:  0x0000.00004743 08/13/2009 23:01:06
 thread:0 rba:(0x0.0.0)
 ....
DATA FILE #5:
  name #5: /u01/app/oracle/product/11.2.0/oradata/jingyong/example01.dbf
creation size=0 block size=8192 status=0x2 head=5 tail=5 dup=1
 tablespace 6, index=5 krfil=5 prev_file=0
 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
 Checkpoint cnt:44 scn: 0x0000.000efd7d 05/02/2013 12:43:16
 Stop scn: 0x0000.000efd7d 05/02/2013 12:43:16
 Creation Checkpointed at scn:  0x0000.000bf3fe 04/25/2013 14:05:52
 thread:0 rba:(0x0.0.0)
 ....
DATA FILE #6:
  name #4: /u01/app/oracle/product/11.2.0/oradata/jingyong/jy01.dbf
creation size=0 block size=8192 status=0x2 head=4 tail=4 dup=1
 tablespace 7, index=6 krfil=6 prev_file=0
 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
 Checkpoint cnt:12 scn: 0x0000.000efd7d 05/02/2013 12:43:16
 Stop scn: 0x0000.000efd7d 05/02/2013 12:43:16
 Creation Checkpointed at scn:  0x0000.000e9b4f 05/02/2013 08:43:22
 thread:0 rba:(0x0.0.0)
 ....
 

经过恢复之后,数据文件达到了一致状态,checkpoint scn(0x0000.000efd7d)和Stop scn(0x0000.000efd7d)
达到了一致,此时数据库就完成了恢复,数据库可以顺利启动:

SQL> ALTER SYSTEM ARCHIVE LOG ALL;

System altered.

SQL> ALTER DATABASE OPEN;

Database altered.

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/product/11.2.0/oradata/jingyong/temp01.dbf'
  2       SIZE 30M  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 40M;

Tablespace altered.

现在我们来实验使用resetlogs方式来重建控制文件:
模拟数据库故障

SQL> shutdown abort;
ORACLE instance shut down.

以resetlogs来重建控制文件
SQL> startup nomount
ORACLE instance started.

Total System Global Area  238530560 bytes
Fixed Size                  1335724 bytes
Variable Size             150998612 bytes
Database Buffers           83886080 bytes
Redo Buffers                2310144 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "JINGYONG" RESETLOGS  ARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 292
  7  LOGFILE
  8    GROUP 1 '/u01/app/oracle/product/11.2.0/oradata/jingyong/redo01.log'  SIZE 50M BLOCKSIZE 512,
  9    GROUP 2 '/u01/app/oracle/product/11.2.0/oradata/jingyong/redo02.log'  SIZE 50M BLOCKSIZE 512,
 10    GROUP 3 '/u01/app/oracle/product/11.2.0/oradata/jingyong/redo03.log'  SIZE 50M BLOCKSIZE 512
 11  -- STANDBY LOGFILE
 12  DATAFILE
 13    '/u01/app/oracle/product/11.2.0/oradata/jingyong/system01.dbf',
 14    '/u01/app/oracle/product/11.2.0/oradata/jingyong/sysaux01.dbf',
 15    '/u01/app/oracle/product/11.2.0/oradata/jingyong/undotbs01.dbf',
 16    '/u01/app/oracle/product/11.2.0/oradata/jingyong/users01.dbf',
 17    '/u01/app/oracle/product/11.2.0/oradata/jingyong/example01.dbf',
 18    '/u01/app/oracle/product/11.2.0/oradata/jingyong/jy01.dbf'
 19  CHARACTER SET ZHS16GBK
 20  ;

Control file created.

此时对控制文件进行一次转储

SQL> alter session set events 'immediate trace name controlf level 12';

Session altered.

SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/jingyong/jingyong/trace/jingyong_ora_19598.trc

观察转储的跟踪文件中的log file record的信息:

***************************************************************************
LOG FILE RECORDS
***************************************************************************
 (size = 72, compat size = 72, section max = 16, section in-use = 3,
  last-recid= 0, old-recno = 0, last-recno = 0)
 (extent = 1, blkno = 10, numrecs = 16)
LOG FILE #1:
  name #3: /u01/app/oracle/product/11.2.0/oradata/jingyong/redo01.log
 Thread 1 redo log links: forward: 2 backward: 0
 siz: 0x19000 seq: 0x00000000 hws: 0x0 bsz: 512 nab: 0x0 flg: 0x1 dup: 1
 Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.00000000
 Low scn: 0x0000.00000000 01/01/1988 00:00:00
 Next scn: 0x0000.00000000 01/01/1988 00:00:00
LOG FILE #2:
  name #2: /u01/app/oracle/product/11.2.0/oradata/jingyong/redo02.log
 Thread 1 redo log links: forward: 3 backward: 1
 siz: 0x19000 seq: 0x00000000 hws: 0x0 bsz: 512 nab: 0x0 flg: 0x1 dup: 1
 Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.00000000
 Low scn: 0x0000.00000000 01/01/1988 00:00:00
 Next scn: 0x0000.00000000 01/01/1988 00:00:00
LOG FILE #3:
  name #1: /u01/app/oracle/product/11.2.0/oradata/jingyong/redo03.log
 Thread 1 redo log links: forward: 0 backward: 2
 siz: 0x19000 seq: 0x00000000 hws: 0x0 bsz: 512 nab: 0x2 flg: 0xb dup: 1
 Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.00000000
 Low scn: 0x0000.00000000 01/01/1988 00:00:00
 Next scn: 0x0000.00000000 01/01/1988 00:00:00

从上面的信息可以看到此时控制文件中的日志信息都是空的,oracle认为resetlogs方式下,当前的日志文件
已经损坏,那么就意味着oracle可能会丢失提交成功的数据,恢复将是一次不完全的介质恢复.

此时的数据文件信息如下:

***************************************************************************
DATA FILE RECORDS
***************************************************************************
 (size = 520, compat size = 520, section max = 100, section in-use = 6,
  last-recid= 0, old-recno = 0, last-recno = 0)
 (extent = 1, blkno = 11, numrecs = 100)
DATA FILE #1:
  name #9: /u01/app/oracle/product/11.2.0/oradata/jingyong/system01.dbf
creation size=0 block size=8192 status=0x12 head=9 tail=9 dup=1
 tablespace 0, index=1 krfil=1 prev_file=0
 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
 Checkpoint cnt:123 scn: 0x0000.000efd80 05/02/2013 12:53:11
 Stop scn: 0xffff.ffffffff 05/02/2013 13:01:12
 Creation Checkpointed at scn:  0x0000.00000007 08/13/2009 23:00:53
 thread:0 rba:(0x0.0.0)
 ....
DATA FILE #2:
  name #8: /u01/app/oracle/product/11.2.0/oradata/jingyong/sysaux01.dbf
creation size=0 block size=8192 status=0x12 head=8 tail=8 dup=1
 tablespace 1, index=2 krfil=2 prev_file=0
 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
 Checkpoint cnt:123 scn: 0x0000.000efd80 05/02/2013 12:53:11
 Stop scn: 0xffff.ffffffff 05/02/2013 13:01:12
 Creation Checkpointed at scn:  0x0000.00000874 08/13/2009 23:00:57
 thread:0 rba:(0x0.0.0)
 ....
DATA FILE #3:
  name #7: /u01/app/oracle/product/11.2.0/oradata/jingyong/undotbs01.dbf
creation size=0 block size=8192 status=0x12 head=7 tail=7 dup=1
 tablespace 2, index=3 krfil=3 prev_file=0
 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
 Checkpoint cnt:51 scn: 0x0000.000efd80 05/02/2013 12:53:11
 Stop scn: 0xffff.ffffffff 05/02/2013 13:01:12
 Creation Checkpointed at scn:  0x0000.000b7982 08/13/2009 23:56:54
 thread:0 rba:(0x0.0.0)
 ....
DATA FILE #4:
  name #6: /u01/app/oracle/product/11.2.0/oradata/jingyong/users01.dbf
creation size=0 block size=8192 status=0x12 head=6 tail=6 dup=1
 tablespace 4, index=4 krfil=4 prev_file=0
 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
 Checkpoint cnt:122 scn: 0x0000.000efd80 05/02/2013 12:53:11
 Stop scn: 0xffff.ffffffff 05/02/2013 13:01:12
 Creation Checkpointed at scn:  0x0000.00004743 08/13/2009 23:01:06
 thread:0 rba:(0x0.0.0)
 ....
DATA FILE #5:
  name #5: /u01/app/oracle/product/11.2.0/oradata/jingyong/example01.dbf
creation size=0 block size=8192 status=0x12 head=5 tail=5 dup=1
 tablespace 6, index=5 krfil=5 prev_file=0
 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
 Checkpoint cnt:47 scn: 0x0000.000efd80 05/02/2013 12:53:11
 Stop scn: 0xffff.ffffffff 05/02/2013 13:01:12
 Creation Checkpointed at scn:  0x0000.000bf3fe 04/25/2013 14:05:52
 thread:0 rba:(0x0.0.0)
 ....
DATA FILE #6:
  name #4: /u01/app/oracle/product/11.2.0/oradata/jingyong/jy01.dbf
creation size=0 block size=8192 status=0x12 head=4 tail=4 dup=1
 tablespace 7, index=6 krfil=6 prev_file=0
 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
 Checkpoint cnt:15 scn: 0x0000.000efd80 05/02/2013 12:53:11
 Stop scn: 0xffff.ffffffff 05/02/2013 13:01:12
 Creation Checkpointed at scn:  0x0000.000e9b4f 05/02/2013 08:43:22
 thread:0 rba:(0x0.0.0)
 ....
 

从上面的信息可以知道由于数据库是异常关闭的,所以数据文件的Stop scn:为无穷大:
Stop scn: 0xffff.ffffffff

不完全恢复最终要求数据库通过resetlogs方式打开,resetlogs将会强制清空或重建联机重做日志文件.
此时执行恢复必须使用backup controlfile选项,否则将会报错:

SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

SQL> recover database using backup controlfile;
ORA-00279: change 982400 generated at 05/02/2013 12:53:11 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/product/11.2.0/flash_recovery_area/JINGYONG/archivelog/2013_05_0
2/o1_mf_1_18_%u_.arc
ORA-00280: change 982400 for thread 1 is in sequence #18


Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.

如果在线日志操坏,那么恢复到最后,oracle提示的最后一个归档日志将是不存在的(如果在线日志没有
损坏,则可以指定在线日志文件执行恢复),此时可以输入cancel取消恢复,然后可以强制打开数据库:

alter database open resetlogs;

SQL> recover database using backup controlfile;
ORA-00279: change 1003572 generated at 05/02/2013 13:20:06 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/product/11.2.0/flash_recovery_area/JINGYONG/archivelog/2013_05_0
2/o1_mf_1_19_%u_.arc
ORA-00280: change 1003572 for thread 1 is in sequence #19


Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/product/11.2.0/oradata/jingyong/redo01.log
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;

Database altered.

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/product/11.2.0/oradata/jingyong/temp01.dbf'
  2       SIZE 30M  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 40M;

Tablespace altered.

RAC数据库出现ORA-27090错误

/u01/app/oracle/admin/yyjm/bdump/yyjm2_p000_6379.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1
System name:Linux
Node name:node6
Release:2.6.18-92.el5
Version:#1 SMP Tue Apr 29 13:16:15 EDT 2008
Machine:x86_64
Instance name: yyjm2
Redo thread mounted by this instance: 2
Oracle process number: 42
Unix process pid: 6379, image: oracle@node6 (P000)

*** SERVICE NAME:(SYS$BACKGROUND) 2013-05-04 07:19:48.850
*** SESSION ID:(716.11196) 2013-05-04 07:19:48.850
*** 2013-05-04 07:19:48.850
Start recovery for domain 0, valid = 0, flags = 0x1
*** 2013-05-04 07:19:49.082
KCRP: blocks claimed = 10, eliminated = 0
----- Recovery Hash Table Statistics ---------
Hash table buckets = 4194304
Longest hash chain = 1
Average hash chain = 10/10 = 1.0
Max compares per lookup = 0
Avg compares per lookup = 0/10 = 0.0
----------------------------------------------
ORA-27090: Message 27090 not found;  product=RDBMS; facility=ORA
Linux-x86_64 Error: 4: Interrupted system call
Additional information: 3
Additional information: 128
Additional information: 65536
----- Recovery Hash Table Statistics ---------
Hash table buckets = 4194304
Longest hash chain = 1
Average hash chain = 10/10 = 1.0
Max compares per lookup = 1
Avg compares per lookup = 33/33 = 1.0
----------------------------------------------

该问题是发生在RAC一个节点崩溃后，所有的错误都发生在并行进程PNNN上。MOS上说这个问题是由于系统参数配置不当所致：ORA-27090: MESSAGE 27090 NOT FOUND; [ID 579108.1]。

内核参数的值：aio-max-nr设置太低，推荐设置为fs.aio-max-nr= 3145728。修改参数使用/sbin/sysctl -p重新加载参数后，重启数据库即可。
下面是OTN上对该问题的讨论
https://forums.oracle.com/forums/thread.jspa?threadID=859368

业务人员报告说不能登录系统，业务几乎停顿.

听到这个消息首先登录主机执行如下命令

[/@zzld03]#sar -u 1 10

 

HP-UX zzld03 B.11.23 U ia64   04/16/13

 

10:32:25    %usr    %sys    %wio   %idle

10:32:26      63       1      26      10

10:32:27      53       1      23      23

10:32:28      49       3      33      16

10:32:29      43       1      39      18

10:32:30      39       1      34      26

10:32:31      35       0      40      24

10:32:32      41       1      37      21

10:32:33      43       1      42      15

10:32:34      40       4      40      15

10:32:35      57      11      27       5

 

Average       46       2      34      17

发现cpu消耗很高

执行top命令检查

从上面的top命令的监控情况来看pid=9362的进程消耗了95.55%的cpu

select s.sid,s.username,s.event,s.wait_time,s.state,s.seconds_in_wait,p.PROGRAM,s.MACHINE

from v$session s,v$process p

where p.spid=9362 and s.PADDR=p.ADDR

根据进程9362找到的语句如下:

select tt.indi_id as indi_id,
       tt.folk_code as folk_code,
       tt.urban_type as urban_type,
       e.mt_pers_type as pers_type,
       tt.pers_type as pers_type_detail,
       tt.culture_code as culture_code,
       tt.housereg_type as housereg_type,
       tt.job_sta_code as job_sta_code,
       tt.city_code as city_code,
       tt.occu_grade_code as occu_grade_code,
       tt.indi_sta as indi_sta,
       tt.kindred_code as kindred_code,
       tt.insr_code as insr_code,
       tt.name as name,
       decode(tt.sex, '1', '男', '0', '女', '未知') as sex,
       to_char(tt.birthday, 'yyyy-mm-dd') as birthday,
       tt.idcard as idcard,
       tt.native as native,
       to_char(tt.job_date, 'yyyy-mm-dd') as job_date,
       tt.retire_date as retire_date,
       tt.telephone as telephone,
       tt.address as address,
       tt.post_code as post_code,
       tt.marri_sta as marri_sta,
       tt.pre_job_years as pre_job_years,
       tt.all_job_years as all_job_years,
       tt.host_indi_id as host_indi_id,
       tt.nothing_flag as nothing_flag,
       tt.speical_pers_flag as speical_pers_flag,
       tt.remark as remark,
       d.folk_name as folk_name,
       e.pers_name as pers_name,
       f.culture_name as culture_name,
       g.housereg_name as housereg_name,
       h.job_sta_name as job_sta_name,
       i.city_name as city_name,
       i.city_class as city_class,
       j.occu_grade_name as occu_grade_name,
       k.indi_sta_name as indi_sta_name,
       l.kindred_name as kindred_name,
       tt.insr_detail_code as insr_detail_code,
       tt.corp_id as corp_id,
       to_char(tt.begin_date, 'yyyy-mm-dd') as begin_date,
       to_char(tt.end_date, 'yyyy-mm-dd') as end_date,
       tt.indi_join_sta as indi_join_sta,
       tt.occu_code as occu_code,
       tt.freeze_sta as freeze_sta,
       tt.posi_code as posi_code,
       tt.hire_type as hire_type,
       tt.work_type as work_type,
       nvl(tt.official_code, '00') as official_code,
       tt.special_code as special_code,
       tt.indi_ins_no as indi_ins_no,
       tt.total_salary as total_salary,
       tt.indi_join_flag as indi_join_flag,
       m.occupation_name as occupation_name,
       n.position_name as position_name,
       o.hire_name as hire_name,
       p.work_type_name as work_type_name,
       q.special_name as special_name,
       tt.corp_name as corp_name,
       tt.corp_code as corp_code,
       tt.corp_type_code as corp_type_code,
       tt.corp_type_name as corp_type_name,
       tt.center_id as center_id,
       tt.veteran_benefit_name as veteran_benefit,
       decode(tt.official_code,
              '0',
              '一般干部',
              '1',
              '副厅以上',
              '2',
              '副厅以下',
              t.official_name) as official_name,
       u.center_name as center_name,
       nvl(v.last_balance, 0) as last_balance
  from (select /*+rule*/
         a.indi_id,
         a.folk_code,
         a.urban_type,
         a.pers_type,
         a.culture_code,
         a.housereg_type,
         a.job_sta_code,
         a.city_code,
         a.occu_grade_code,
         a.indi_sta,
         a.kindred_code,
         a.insr_code,
         a.name,
         a.sex,
         a.birthday,
         a.idcard,
         a.native,
         a.job_date,
         a.retire_date,
         a.telephone,
         a.address,
         a.post_code,
         a.marri_sta,
         a.pre_job_years,
         a.all_job_years,
         a.host_indi_id,
         a.nothing_flag,
         a.speical_pers_flag,
         decode(a.city_code,
                null,
                a.remark,
                (select city.city_name
                   From bs_city city
                  where city.city_code = a.city_code)) remark,
         (select bct.corp_type_name
            From bs_corp_type bct
           where bct.corp_type_code = s.corp_type_code
             and bct.center_id = s.center_id) corp_type_name,
         b.insr_detail_code,
         b.corp_id,
         b.begin_date,
         b.end_date,
         b.indi_join_sta,
         c.occu_code,
         decode(decode(nvl(p.freeze_sta, '0'),
                       '0',
                       nvl(b.freeze_sta, '0'),
                       '1',
                       '9'),
                '0',
                '基金未冻结',
                '1',
                '基金已冻结',
                '9',
                '单位已冻结') as freeze_sta,
         c.posi_code,
         c.hire_type,
         c.work_type,
         nvl(x.veteran_benefit_name, '非优抚对象') as veteran_benefit_name,
         decode(a.pers_type, '3', nvl(c.office_grade, '0'), c.official_code) as official_code,
         c.special_code,
         c.indi_ins_no,
         c.total_salary,
         c.indi_join_flag,
         s.corp_name,
         s.corp_code,
         s.corp_type_code,
         s.center_id
          from bs_corp            s,
               bs_pres_insur      b,
               bs_corp_insure     p,
               bs_corp_pres       c,
               bs_veteran_benefit x,
               bs_insured         a
         where (a.idcard = '430204850922611' or
               a.idcard = '430204198509226110')
           and a.indi_id = b.indi_id
           and a.veteran_benefit_type = x.veteran_benefit_type(+)
           and decode(b.insr_detail_code,
                      '12',
                      '2',
                      '21',
                      '2',
                      '17',
                      '2',
                      b.insr_detail_code) =
               DECODE(a.Sex || '~' || a.Pers_Type || '~' || C.INDI_JOIN_FLAG || '~' || '2',
                      '0~1~1~7',
                      '2',
                      '0~2~1~7',
                      '2',
                      '2')
           and decode(p.insr_detail_code,
                      '12',
                      '2',
                      '21',
                      '2',
                      '17',
                      '2',
                      p.insr_detail_code) =
               DECODE(a.Sex || '~' || a.Pers_Type || '~' || C.INDI_JOIN_FLAG || '~' || '2',
                      '0~1~1~7',
                      '2',
                      '0~2~1~7',
                      '2',
                      '2')
           and s.corp_id = c.corp_id
           and s.corp_id = p.corp_id
           and b.insr_detail_code = p.insr_detail_code
           and b.indi_id = c.indi_id
           and b.corp_id = s.corp_id) tt,
       bs_folk d,
       bs_person_type e,
       bs_culture_stac f,
       bs_housereg_type g,
       bs_job_stac h,
       bs_city i,
       bs_occupation_grade j,
       bs_pers_status k,
       bs_kindred l,
       bs_occupation m,
       bs_position n,
       bs_hired_type o,
       bs_work_type p,
       bs_special q,
       bs_official t,
       bs_center u,
       bs_mdi_indi_acc v
 where tt.folk_code = d.folk_code(+)
   and tt.pers_type = e.pers_type(+)
   and tt.center_id = e.center_id(+)
   and tt.culture_code = f.culture_code(+)
   and tt.housereg_type = g.housereg_type(+)
   and tt.job_sta_code = h.job_sta_code(+)
   and tt.city_code = i.city_code(+)
   and tt.occu_grade_code = j.occu_grade_code(+)
   and tt.indi_sta = k.indi_sta(+)
   and tt.kindred_code = l.kindred_code(+)
   and tt.occu_code = m.occu_code(+)
   and tt.posi_code = n.posi_code(+)
   and tt.hire_type = o.hire_type(+)
   and tt.work_type = p.work_type(+)
   and tt.special_code = q.special_code(+)
   and tt.official_code = t.official_code(+)
   and tt.indi_id = v.indi_id(+)
   and 1 = v.acco_sta(+)
   and tt.center_id = u.center_id
   and tt.center_id in ('430300')

后通过查看该语句的执行计划,发现没有选择合适的索引造成的,而没有选择合适的索引是因为在查询中使用了/*+rule */提示,将该提示去掉后,语句执行正常cpu的利用率也恢复正常,应用也恢复正常.

这就是典刑的一条sql语句拖垮整个系统的案例.

开发人员执行以下语句时报ora-01801错误

在oracle 11.2.0.1.0 在sql语句中使用with table as 写法时触发了ora-03113错误

select pay_bill_no,
       hospital_id,
       hospital_name,
       aaa027,
       insur_name as center_name,
       pers_name,
       aka035,
       indi_count as rc,
       count(pers_name) as ds,
       sum(bac004) as bac004,
       0 as bili,
       sum(total_pay) as total_money,
       sum(fund_pay) as fund_money,
       0 as fund_fee,
       0 as qa_fee,
       0 as offi_fee,
       sum(total_pay_tekun) as total_money_tekun,
       sum(fund_pay_tekun) as fund_money_tekun,
       (select nvl(max(akb070), 0) from kf21 where aaz272 = pay_bill_no) +
       (select nvl(sum(AKB069), 0) from kcb5 where aaz272 = pay_bill_no) akb070,
       (select nvl(max(bkb070), 0) from kf21 where aaz272 = pay_bill_no) bkb070
  from (with abcd as (select decode(a.bac004, '2', 1, 0) as bac004,
                             n.akb079,
                             k.aaa027,
                             n.aaz272 as pay_bill_no,
                             m.aaz107 as hospital_id,
                             b.aaz217 as serial_no,
                             z.aab069 as corp_name,
                             z.aab001 as corp_code,
                             k.aaa129 as insur_name,
                             a.aac003 as name,
                             a.aac002 as idcard,
                             d.aka121 as disease,
                             decode(b.aka042, 'E', '是', '否') as twice_inhosp_flag,
                             '否' as backbur_flag,
                             t.aaa103 as pers_name,
                             sum(c.aae019) as total_pay,
                             decode(a.bac004, '2', sum(c.aae019), 0) as total_pay_tekun,
                             sum(decode(c.aka002,
                                        311901,
                                        c.aae019,
                                        311902,
                                        c.aae019,
                                        311903,
                                        c.aae019,
                                        510900,
                                        c.aae019,
                                        0)) as fund_pay,
                             decode(a.bac004,
                                    '2',
                                    sum(decode(c.aka002,
                                               311901,
                                               c.aae019,
                                               311902,
                                               c.aae019,
                                               311903,
                                               c.aae019,
                                               510900,
                                               c.aae019,
                                               0)),
                                    0) as fund_pay_tekun,
                             a.aac001 as indi_id,
                             e.aab069 as hospital_name,
                             b.aae030 as begin_date,
                             b.aae031 as end_date,
                             to_char(n.aae015, 'yyyy-mm-dd') as check_date,
                             decode(b.aka042,
                                    '1',
                                    '普通住院',
                                    '2',
                                    '急诊或抢救住院',
                                    '3',
                                    '转院住院',
                                    'Q',
                                    '更正报销业务',
                                    'R',
                                    '连续住院',
                                    'Z',
                                    '转科住院',
                                    'B',
                                    '欠费补缴',
                                    'E',
                                    '二次返院',
                                    '') as aka042,
                             decode(y.aka035a,
                                    'A',
                                    '普通病种',
                                    'C',
                                    '特殊病种',
                                    'C1',
                                    '欠费补缴',
                                    'D',
                                    '综合病种',
                                    'E',
                                    '生育病种',
                                    d.aka121) as aka035,
                             y.bkb007,
                             y.bkb008,
                             y.bkb009,
                             y.bkb010,
                             y.bkb030,
                             y.bkb035,
                             y.aka035a,
                             decode(ceil(trunc(to_date(h.aae031, 'yyyymmdd')) -
                                         trunc(to_date(h.aae030, 'yyyymmdd'))),
                                    0,
                                    1,
                                    ceil(trunc(to_date(h.aae031, 'yyyymmdd')) -
                                         trunc(to_date(h.aae030, 'yyyymmdd')))) as days
                        from Ac01 a,
                             Kc21 b,
                             Kc27 c,
                             Ka06 d,
                             Ae10 e,
                             Kb01 f,
                             Kc19 h,
                             Kc25 m,
                             Kf20 n,
                             aa13 k,
                             Kcb4 z,
                             (select aaa102, aaa103
                                from aa23
                               where aae140 = '310'
                                 and aaa027 = 440999) t,
                             (select decode(y.aka120, '', y.aka035, y.aka120) as aka035a,
                                     sum(bkb007) as bkb007,
                                     sum(bkb008) as bkb008,
                                     sum(bkb009) as bkb009,
                                     sum(y.bkb010) as bkb010,
                                     sum(y.bkb030) as bkb030,
                                     sum(y.bkb035) as bkb035
                                from kcb5 y
                               where aaz272 = 8180
                               group by decode(y.aka120,
                                               '',
                                               y.aka035,
                                               y.aka120)) y
                       where b.aac001 = a.aac001
                         and b.aaz217 = c.aaz217
                         and d.aka120 = b.akc196
                         and f.aaz269 = e.Aaz001
                         and b.aaz217 = h.aaz217
                         and b.aaz217 = m.aaz217
                         and f.akb020 = n.akb020
                         and n.aaz272 = m.aaz272
                         and z.aaz217 = b.aaz217
                         and z.baa027 = k.aaa027
                         and b.aaa027 = f.aaa027
                         and n.aka028 = 2
                         and b.aae100 = 1
                         and c.aae100 = 1
                         and h.aae100 = 1
                         and m.aae100 = 1
                         and decode(z.aac066, '1', '311', z.aac066) =
                             t.aaa102(+)
                         and nvl(m.aae167, 0) >= 0
                         and (m.aka035 = y.aka035a or y.aka035a = d.aka120)
                         and m.aaz272 = 8180
                         and n.aaa027 in
                             ('440999', '440901', '440902', '440903')
                       group by n.aaz272,
                                b.aaz217,
                                a.aac003,
                                a.aac002,
                                d.aka121,
                                b.aka042,
                                t.aaa103,
                                e.aab069,
                                b.aae030,
                                b.aae031,
                                m.aaz107,
                                n.aae015,
                                z.aab001,
                                z.aab069,
                                k.aaa129,
                                n.akb079,
                                a.aac001,
                                y.aka035a,
                                y.bkb007,
                                y.bkb008,
                                y.bkb009,
                                y.bkb010,
                                y.bkb030,
                                y.bkb035,
                                k.aaa027,
                                m.aka035,
                                h.aae031,
                                a.bac004,
                                h.aae030
                      union all
                      select decode(a.bac004, '2', 1, 0) as bac004,
                             f.akb079,
                             k.aaa027,
                             d.aaz272 as pay_bill_no,
                             d.aaz107 as hospital_id,
                             0 as serial_no,
                             t1.aab069 as corp_name,
                             e.aaz001 as corp_code,
                             k.aaa129 as insur_name,
                             a.aac003 as name,
                             a.aac002 as idcard,
                             b.aka121 as disease,
                             decode(d.aka042, 'E', '是', '否') as twice_inhosp_flag,
                             '是' as backbur_flag,
                             t.aaa103 as pers_name,
                             d.aae198 as total_pay,
                             decode(a.bac004, '2', d.aae198, 0) as total_pay_tekun,
                             0 as fund_pay,
                             0 as fund_pay_tekun,
                             a.aac001 as indi_id,
                             e.aab069 as hospital_name,
                             d.aae041 as begin_date,
                             d.aae042 as end_date,
                             to_char(f.aae015, 'yyyy-mm-dd') as check_date,
                             decode(d.aka042,
                                    '1',
                                    '普通住院',
                                    '2',
                                    '急诊或抢救住院',
                                    '3',
                                    '转院住院',
                                    'Q',
                                    '更正报销业务',
                                    'R',
                                    '连续住院',
                                    'Z',
                                    '转科住院',
                                    'B',
                                    '欠费补缴',
                                    'E',
                                    '二次返院',
                                    '') as aka042,
                             decode(y.aka035a,
                                    'A',
                                    '普通病种',
                                    'C',
                                    '特殊病种',
                                    'C1',
                                    '欠费补缴',
                                    'D',
                                    '综合病种',
                                    'E',
                                    '生育病种',
                                    b.aka121) as aka035,
                             y.bkb007,
                             y.bkb008,
                             y.bkb009,
                             y.bkb010,
                             y.bkb030,
                             y.bkb035,
                             y.aka035a,
                             decode(ceil(trunc(to_date(d.aae042, 'yyyymmdd')) -
                                         trunc(to_date(d.aae041, 'yyyymmdd'))),
                                    0,
                                    1,
                                    ceil(trunc(to_date(d.aae042, 'yyyymmdd')) -
                                         trunc(to_date(d.aae041, 'yyyymmdd')))) as days
                        from Ac01 a,
                             Ka06 b,
                             Ae10 e,
                             Kb01 c,
                             Kcb1 d,
                             Kf20 f,
                             aa13 k,
                             (select aaa102, aaa103
                                from aa23
                               where aae140 = '310'
                                 and aaa027 = 440999) t,
                             (select decode(y.aka120, '', y.aka035, y.aka120) as aka035a,
                                     sum(bkb007) as bkb007,
                                     sum(bkb008) as bkb008,
                                     sum(bkb009) as bkb009,
                                     sum(y.bkb010) as bkb010,
                                     sum(y.bkb030) as bkb030,
                                     sum(y.bkb035) as bkb035
                                from kcb5 y
                               where aaz272 = 8180
                               group by decode(y.aka120,
                                               '',
                                               y.aka035,
                                               y.aka120)) y,
                             (select ac20.aac001, ae10.aab069
                                from ae10, ac20
                               where ae10.aaz001 = ac20.aab001
                                 and ac20.aae140 = '310') t1
                       where c.aaz269 = e.Aaz001
                         and f.aaz272 = d.aaz272
                         and d.aac001 = a.aac001
                         and b.aka120 = d.akc196
                         and d.aae100 = 1
                         and d.aaz107 = c.aaz107
                         and k.aaa027 = d.aaa027
                         and c.aaa027 = d.aaa027
                         and d.aac001 = t1.aac001
                         and f.aka028 = 2
                         and nvl(d.aae167, 0) >= 0
                         and d.aac066 = t.aaa102(+)
                         and (d.aka035 = y.aka035a or b.aka120 = y.aka035a)
                         and d.aaz272 = 8180), abc as (select count(distinct
                                                                    indi_id) indi_count,
                                                              insur_name,
                                                              pers_name,
                                                              aka035,
                                                              aaa027
                                                         from abcd
                                                        group by insur_name,
                                                                 pers_name,
                                                                 aka035,
                                                                 aaa027)
         select abc.indi_count,
                abc.insur_name,
                abc.pers_name,
                abc.aka035,
                abc.aaa027,
                abcd.hospital_id,
                abcd.hospital_name,
                abcd.bac004,
                abcd.pay_bill_no,
                abcd.total_pay,
                abcd.fund_pay,
                abcd.total_pay_tekun,
                abcd.fund_pay_tekun
           from abc, abcd
          where abc.insur_name = abcd.insur_name
            and abc.pers_name = abcd.pers_name
            and abc.aka035 = abcd.aka035
            and abc.aaa027 = abcd.aaa027)
          group by indi_count,
                   insur_name,
                   pers_name,
                   aka035,
                   aaa027,
                   hospital_id,
                   hospital_name,
                   pay_bill_no
          order by aaa027, pers_name desc;

上面是报ora-03113故障的语句使用了with table as 写法
经过修改语句去掉with table as 写法后正常执行
select pay_bill_no,
       hospital_id,
       hospital_name,
       aaa027,
       insur_name as center_name,
       pers_name,
       aka035,
       indi_count as rc,
       count(pers_name) as ds,
       sum(bac004) as bac004,
       0 as bili,
       sum(total_pay) as total_money,
       sum(fund_pay) as fund_money,
       0 as fund_fee,
       0 as qa_fee,
       0 as offi_fee,
       sum(total_pay_tekun) as total_money_tekun,
       sum(fund_pay_tekun) as fund_money_tekun,
       (select nvl(max(akb070), 0) from kf21 where aaz272 = pay_bill_no) +
       (select nvl(sum(AKB069), 0) from kcb5 where aaz272 = pay_bill_no) akb070,
       (select nvl(max(bkb070), 0) from kf21 where aaz272 = pay_bill_no) bkb070
  from (
         select abc.indi_count,
                abc.insur_name,
                abc.pers_name,
                abc.aka035,
                abc.aaa027,
                abcd.hospital_id,
                abcd.hospital_name,
                abcd.bac004,
                abcd.pay_bill_no,
                abcd.total_pay,
                abcd.fund_pay,
                abcd.total_pay_tekun,
                abcd.fund_pay_tekun
           from
           (
           select count(distinct
                                                                    indi_id) indi_count,
                                                              insur_name,
                                                              pers_name,
                                                              aka035,
                                                              aaa027
                                                         from
                                                         (
                                                         select decode(a.bac004, '2', 1, 0) as bac004,
                             n.akb079,
                             k.aaa027,
                             n.aaz272 as pay_bill_no,
                             m.aaz107 as hospital_id,
                             b.aaz217 as serial_no,
                             z.aab069 as corp_name,
                             z.aab001 as corp_code,
                             k.aaa129 as insur_name,
                             a.aac003 as name,
                             a.aac002 as idcard,
                             d.aka121 as disease,
                             decode(b.aka042, 'E', '是', '否') as twice_inhosp_flag,
                             '否' as backbur_flag,
                             t.aaa103 as pers_name,
                             sum(c.aae019) as total_pay,
                             decode(a.bac004, '2', sum(c.aae019), 0) as total_pay_tekun,
                             sum(decode(c.aka002,
                                        311901,
                                        c.aae019,
                                        311902,
                                        c.aae019,
                                        311903,
                                        c.aae019,
                                        510900,
                                        c.aae019,
                                        0)) as fund_pay,
                             decode(a.bac004,
                                    '2',
                                    sum(decode(c.aka002,
                                               311901,
                                               c.aae019,
                                               311902,
                                               c.aae019,
                                               311903,
                                               c.aae019,
                                               510900,
                                               c.aae019,
                                               0)),
                                    0) as fund_pay_tekun,
                             a.aac001 as indi_id,
                             e.aab069 as hospital_name,
                             b.aae030 as begin_date,
                             b.aae031 as end_date,
                             to_char(n.aae015, 'yyyy-mm-dd') as check_date,
                             decode(b.aka042,
                                    '1',
                                    '普通住院',
                                    '2',
                                    '急诊或抢救住院',
                                    '3',
                                    '转院住院',
                                    'Q',
                                    '更正报销业务',
                                    'R',
                                    '连续住院',
                                    'Z',
                                    '转科住院',
                                    'B',
                                    '欠费补缴',
                                    'E',
                                    '二次返院',
                                    '') as aka042,
                             decode(y.aka035a,
                                    'A',
                                    '普通病种',
                                    'C',
                                    '特殊病种',
                                    'C1',
                                    '欠费补缴',
                                    'D',
                                    '综合病种',
                                    'E',
                                    '生育病种',
                                    d.aka121) as aka035,
                             y.bkb007,
                             y.bkb008,
                             y.bkb009,
                             y.bkb010,
                             y.bkb030,
                             y.bkb035,
                             y.aka035a,
                             decode(ceil(trunc(to_date(h.aae031, 'yyyymmdd')) -
                                         trunc(to_date(h.aae030, 'yyyymmdd'))),
                                    0,
                                    1,
                                    ceil(trunc(to_date(h.aae031, 'yyyymmdd')) -
                                         trunc(to_date(h.aae030, 'yyyymmdd')))) as days
                        from Ac01 a,
                             Kc21 b,
                             Kc27 c,
                             Ka06 d,
                             Ae10 e,
                             Kb01 f,
                             Kc19 h,
                             Kc25 m,
                             Kf20 n,
                             aa13 k,
                             Kcb4 z,
                             (select aaa102, aaa103
                                from aa23
                               where aae140 = '310'
                                 and aaa027 = 440999) t,
                             (select decode(y.aka120, '', y.aka035, y.aka120) as aka035a,
                                     sum(bkb007) as bkb007,
                                     sum(bkb008) as bkb008,
                                     sum(bkb009) as bkb009,
                                     sum(y.bkb010) as bkb010,
                                     sum(y.bkb030) as bkb030,
                                     sum(y.bkb035) as bkb035
                                from kcb5 y
                               where aaz272 = 8180
                               group by decode(y.aka120,
                                               '',
                                               y.aka035,
                                               y.aka120)) y
                       where b.aac001 = a.aac001
                         and b.aaz217 = c.aaz217
                         and d.aka120 = b.akc196
                         and f.aaz269 = e.Aaz001
                         and b.aaz217 = h.aaz217
                         and b.aaz217 = m.aaz217
                         and f.akb020 = n.akb020
                         and n.aaz272 = m.aaz272
                         and z.aaz217 = b.aaz217
                         and z.baa027 = k.aaa027
                         and b.aaa027 = f.aaa027
                         and n.aka028 = 2
                         and b.aae100 = 1
                         and c.aae100 = 1
                         and h.aae100 = 1
                         and m.aae100 = 1
                         and decode(z.aac066, '1', '311', z.aac066) =
                             t.aaa102(+)
                         and nvl(m.aae167, 0) >= 0
                         and (m.aka035 = y.aka035a or y.aka035a = d.aka120)
                         and m.aaz272 = 8180
                         and n.aaa027 in
                             ('440999', '440901', '440902', '440903')
                       group by n.aaz272,
                                b.aaz217,
                                a.aac003,
                                a.aac002,
                                d.aka121,
                                b.aka042,
                                t.aaa103,
                                e.aab069,
                                b.aae030,
                                b.aae031,
                                m.aaz107,
                                n.aae015,
                                z.aab001,
                                z.aab069,
                                k.aaa129,
                                n.akb079,
                                a.aac001,
                                y.aka035a,
                                y.bkb007,
                                y.bkb008,
                                y.bkb009,
                                y.bkb010,
                                y.bkb030,
                                y.bkb035,
                                k.aaa027,
                                m.aka035,
                                h.aae031,
                                a.bac004,
                                h.aae030
                      union all
                      select decode(a.bac004, '2', 1, 0) as bac004,
                             f.akb079,
                             k.aaa027,
                             d.aaz272 as pay_bill_no,
                             d.aaz107 as hospital_id,
                             0 as serial_no,
                             t1.aab069 as corp_name,
                             e.aaz001 as corp_code,
                             k.aaa129 as insur_name,
                             a.aac003 as name,
                             a.aac002 as idcard,
                             b.aka121 as disease,
                             decode(d.aka042, 'E', '是', '否') as twice_inhosp_flag,
                             '是' as backbur_flag,
                             t.aaa103 as pers_name,
                             d.aae198 as total_pay,
                             decode(a.bac004, '2', d.aae198, 0) as total_pay_tekun,
                             0 as fund_pay,
                             0 as fund_pay_tekun,
                             a.aac001 as indi_id,
                             e.aab069 as hospital_name,
                             d.aae041 as begin_date,
                             d.aae042 as end_date,
                             to_char(f.aae015, 'yyyy-mm-dd') as check_date,
                             decode(d.aka042,
                                    '1',
                                    '普通住院',
                                    '2',
                                    '急诊或抢救住院',
                                    '3',
                                    '转院住院',
                                    'Q',
                                    '更正报销业务',
                                    'R',
                                    '连续住院',
                                    'Z',
                                    '转科住院',
                                    'B',
                                    '欠费补缴',
                                    'E',
                                    '二次返院',
                                    '') as aka042,
                             decode(y.aka035a,
                                    'A',
                                    '普通病种',
                                    'C',
                                    '特殊病种',
                                    'C1',
                                    '欠费补缴',
                                    'D',
                                    '综合病种',
                                    'E',
                                    '生育病种',
                                    b.aka121) as aka035,
                             y.bkb007,
                             y.bkb008,
                             y.bkb009,
                             y.bkb010,
                             y.bkb030,
                             y.bkb035,
                             y.aka035a,
                             decode(ceil(trunc(to_date(d.aae042, 'yyyymmdd')) -
                                         trunc(to_date(d.aae041, 'yyyymmdd'))),
                                    0,
                                    1,
                                    ceil(trunc(to_date(d.aae042, 'yyyymmdd')) -
                                         trunc(to_date(d.aae041, 'yyyymmdd')))) as days
                        from Ac01 a,
                             Ka06 b,
                             Ae10 e,
                             Kb01 c,
                             Kcb1 d,
                             Kf20 f,
                             aa13 k,
                             (select aaa102, aaa103
                                from aa23
                               where aae140 = '310'
                                 and aaa027 = 440999) t,
                             (select decode(y.aka120, '', y.aka035, y.aka120) as aka035a,
                                     sum(bkb007) as bkb007,
                                     sum(bkb008) as bkb008,
                                     sum(bkb009) as bkb009,
                                     sum(y.bkb010) as bkb010,
                                     sum(y.bkb030) as bkb030,
                                     sum(y.bkb035) as bkb035
                                from kcb5 y
                               where aaz272 = 8180
                               group by decode(y.aka120,
                                               '',
                                               y.aka035,
                                               y.aka120)) y,
                             (select ac20.aac001, ae10.aab069
                                from ae10, ac20
                               where ae10.aaz001 = ac20.aab001
                                 and ac20.aae140 = '310') t1
                       where c.aaz269 = e.Aaz001
                         and f.aaz272 = d.aaz272
                         and d.aac001 = a.aac001
                         and b.aka120 = d.akc196
                         and d.aae100 = 1
                         and d.aaz107 = c.aaz107
                         and k.aaa027 = d.aaa027
                         and c.aaa027 = d.aaa027
                         and d.aac001 = t1.aac001
                         and f.aka028 = 2
                         and nvl(d.aae167, 0) >= 0
                         and d.aac066 = t.aaa102(+)
                         and (d.aka035 = y.aka035a or b.aka120 = y.aka035a)
                         and d.aaz272 = 8180)
                                                        group by insur_name,
                                                                 pers_name,
                                                                 aka035,
                                                                 aaa027)
           abc,
           (select decode(a.bac004, '2', 1, 0) as bac004,
                             n.akb079,
                             k.aaa027,
                             n.aaz272 as pay_bill_no,
                             m.aaz107 as hospital_id,
                             b.aaz217 as serial_no,
                             z.aab069 as corp_name,
                             z.aab001 as corp_code,
                             k.aaa129 as insur_name,
                             a.aac003 as name,
                             a.aac002 as idcard,
                             d.aka121 as disease,
                             decode(b.aka042, 'E', '是', '否') as twice_inhosp_flag,
                             '否' as backbur_flag,
                             t.aaa103 as pers_name,
                             sum(c.aae019) as total_pay,
                             decode(a.bac004, '2', sum(c.aae019), 0) as total_pay_tekun,
                             sum(decode(c.aka002,
                                        311901,
                                        c.aae019,
                                        311902,
                                        c.aae019,
                                        311903,
                                        c.aae019,
                                        510900,
                                        c.aae019,
                                        0)) as fund_pay,
                             decode(a.bac004,
                                    '2',
                                    sum(decode(c.aka002,
                                               311901,
                                               c.aae019,
                                               311902,
                                               c.aae019,
                                               311903,
                                               c.aae019,
                                               510900,
                                               c.aae019,
                                               0)),
                                    0) as fund_pay_tekun,
                             a.aac001 as indi_id,
                             e.aab069 as hospital_name,
                             b.aae030 as begin_date,
                             b.aae031 as end_date,
                             to_char(n.aae015, 'yyyy-mm-dd') as check_date,
                             decode(b.aka042,
                                    '1',
                                    '普通住院',
                                    '2',
                                    '急诊或抢救住院',
                                    '3',
                                    '转院住院',
                                    'Q',
                                    '更正报销业务',
                                    'R',
                                    '连续住院',
                                    'Z',
                                    '转科住院',
                                    'B',
                                    '欠费补缴',
                                    'E',
                                    '二次返院',
                                    '') as aka042,
                             decode(y.aka035a,
                                    'A',
                                    '普通病种',
                                    'C',
                                    '特殊病种',
                                    'C1',
                                    '欠费补缴',
                                    'D',
                                    '综合病种',
                                    'E',
                                    '生育病种',
                                    d.aka121) as aka035,
                             y.bkb007,
                             y.bkb008,
                             y.bkb009,
                             y.bkb010,
                             y.bkb030,
                             y.bkb035,
                             y.aka035a,
                             decode(ceil(trunc(to_date(h.aae031, 'yyyymmdd')) -
                                         trunc(to_date(h.aae030, 'yyyymmdd'))),
                                    0,
                                    1,
                                    ceil(trunc(to_date(h.aae031, 'yyyymmdd')) -
                                         trunc(to_date(h.aae030, 'yyyymmdd')))) as days
                        from Ac01 a,
                             Kc21 b,
                             Kc27 c,
                             Ka06 d,
                             Ae10 e,
                             Kb01 f,
                             Kc19 h,
                             Kc25 m,
                             Kf20 n,
                             aa13 k,
                             Kcb4 z,
                             (select aaa102, aaa103
                                from aa23
                               where aae140 = '310'
                                 and aaa027 = 440999) t,
                             (select decode(y.aka120, '', y.aka035, y.aka120) as aka035a,
                                     sum(bkb007) as bkb007,
                                     sum(bkb008) as bkb008,
                                     sum(bkb009) as bkb009,
                                     sum(y.bkb010) as bkb010,
                                     sum(y.bkb030) as bkb030,
                                     sum(y.bkb035) as bkb035
                                from kcb5 y
                               where aaz272 = 8180
                               group by decode(y.aka120,
                                               '',
                                               y.aka035,
                                               y.aka120)) y
                       where b.aac001 = a.aac001
                         and b.aaz217 = c.aaz217
                         and d.aka120 = b.akc196
                         and f.aaz269 = e.Aaz001
                         and b.aaz217 = h.aaz217
                         and b.aaz217 = m.aaz217
                         and f.akb020 = n.akb020
                         and n.aaz272 = m.aaz272
                         and z.aaz217 = b.aaz217
                         and z.baa027 = k.aaa027
                         and b.aaa027 = f.aaa027
                         and n.aka028 = 2
                         and b.aae100 = 1
                         and c.aae100 = 1
                         and h.aae100 = 1
                         and m.aae100 = 1
                         and decode(z.aac066, '1', '311', z.aac066) =
                             t.aaa102(+)
                         and nvl(m.aae167, 0) >= 0
                         and (m.aka035 = y.aka035a or y.aka035a = d.aka120)
                         and m.aaz272 = 8180
                         and n.aaa027 in
                             ('440999', '440901', '440902', '440903')
                       group by n.aaz272,
                                b.aaz217,
                                a.aac003,
                                a.aac002,
                                d.aka121,
                                b.aka042,
                                t.aaa103,
                                e.aab069,
                                b.aae030,
                                b.aae031,
                                m.aaz107,
                                n.aae015,
                                z.aab001,
                                z.aab069,
                                k.aaa129,
                                n.akb079,
                                a.aac001,
                                y.aka035a,
                                y.bkb007,
                                y.bkb008,
                                y.bkb009,
                                y.bkb010,
                                y.bkb030,
                                y.bkb035,
                                k.aaa027,
                                m.aka035,
                                h.aae031,
                                a.bac004,
                                h.aae030
                      union all
                      select decode(a.bac004, '2', 1, 0) as bac004,
                             f.akb079,
                             k.aaa027,
                             d.aaz272 as pay_bill_no,
                             d.aaz107 as hospital_id,
                             0 as serial_no,
                             t1.aab069 as corp_name,
                             e.aaz001 as corp_code,
                             k.aaa129 as insur_name,
                             a.aac003 as name,
                             a.aac002 as idcard,
                             b.aka121 as disease,
                             decode(d.aka042, 'E', '是', '否') as twice_inhosp_flag,
                             '是' as backbur_flag,
                             t.aaa103 as pers_name,
                             d.aae198 as total_pay,
                             decode(a.bac004, '2', d.aae198, 0) as total_pay_tekun,
                             0 as fund_pay,
                             0 as fund_pay_tekun,
                             a.aac001 as indi_id,
                             e.aab069 as hospital_name,
                             d.aae041 as begin_date,
                             d.aae042 as end_date,
                             to_char(f.aae015, 'yyyy-mm-dd') as check_date,
                             decode(d.aka042,
                                    '1',
                                    '普通住院',
                                    '2',
                                    '急诊或抢救住院',
                                    '3',
                                    '转院住院',
                                    'Q',
                                    '更正报销业务',
                                    'R',
                                    '连续住院',
                                    'Z',
                                    '转科住院',
                                    'B',
                                    '欠费补缴',
                                    'E',
                                    '二次返院',
                                    '') as aka042,
                             decode(y.aka035a,
                                    'A',
                                    '普通病种',
                                    'C',
                                    '特殊病种',
                                    'C1',
                                    '欠费补缴',
                                    'D',
                                    '综合病种',
                                    'E',
                                    '生育病种',
                                    b.aka121) as aka035,
                             y.bkb007,
                             y.bkb008,
                             y.bkb009,
                             y.bkb010,
                             y.bkb030,
                             y.bkb035,
                             y.aka035a,
                             decode(ceil(trunc(to_date(d.aae042, 'yyyymmdd')) -
                                         trunc(to_date(d.aae041, 'yyyymmdd'))),
                                    0,
                                    1,
                                    ceil(trunc(to_date(d.aae042, 'yyyymmdd')) -
                                         trunc(to_date(d.aae041, 'yyyymmdd')))) as days
                        from Ac01 a,
                             Ka06 b,
                             Ae10 e,
                             Kb01 c,
                             Kcb1 d,
                             Kf20 f,
                             aa13 k,
                             (select aaa102, aaa103
                                from aa23
                               where aae140 = '310'
                                 and aaa027 = 440999) t,
                             (select decode(y.aka120, '', y.aka035, y.aka120) as aka035a,
                                     sum(bkb007) as bkb007,
                                     sum(bkb008) as bkb008,
                                     sum(bkb009) as bkb009,
                                     sum(y.bkb010) as bkb010,
                                     sum(y.bkb030) as bkb030,
                                     sum(y.bkb035) as bkb035
                                from kcb5 y
                               where aaz272 = 8180
                               group by decode(y.aka120,
                                               '',
                                               y.aka035,
                                               y.aka120)) y,
                             (select ac20.aac001, ae10.aab069
                                from ae10, ac20
                               where ae10.aaz001 = ac20.aab001
                                 and ac20.aae140 = '310') t1
                       where c.aaz269 = e.Aaz001
                         and f.aaz272 = d.aaz272
                         and d.aac001 = a.aac001
                         and b.aka120 = d.akc196
                         and d.aae100 = 1
                         and d.aaz107 = c.aaz107
                         and k.aaa027 = d.aaa027
                         and c.aaa027 = d.aaa027
                         and d.aac001 = t1.aac001
                         and f.aka028 = 2
                         and nvl(d.aae167, 0) >= 0
                         and d.aac066 = t.aaa102(+)
                         and (d.aka035 = y.aka035a or b.aka120 = y.aka035a)
                         and d.aaz272 = 8180)
           abcd
          where abc.insur_name = abcd.insur_name
            and abc.pers_name = abcd.pers_name
            and abc.aka035 = abcd.aka035
            and abc.aaa027 = abcd.aaa027)
          group by indi_count,
                   insur_name,
                   pers_name,
                   aka035,
                   aaa027,
                   hospital_id,
                   hospital_name,
                   pay_bill_no
          order by aaa027, pers_name desc;
          

在Oracle 11.2.0.3.0中执行awrrpt.sql生成awr报告报ora-06502错误

ERROR:ORA-06502:PL/SQL:numeric or value error:character string buffer
too small ORA-06502:at "YSY.DBMS_WORKLOAD_REPOSITORY", line 919
ORA-06502:at line 1

这是个bug再,也给出解决方法：

Bug 13527323 - ORA-6502 generating HTML AWR report using awrrpt.sql in Multibyte characterset database [ID 13527323.8]

解决方法：

update WRH$_SQLTEXT set sql_text = SUBSTR(sql_text, 1, 1000);
commit;

数据库是11.2.0.3.0 的rac

在使用impdp导入数据时导入进程终止了.
后检查告警日志发现如下信息

Time drift detected. Please check VKTM trace file for more details.
Thu Mar 28 05:45:49 2013
Time drift detected. Please check VKTM trace file for more details.
Thu Mar 28 07:28:47 2013
Time drift detected. Please check VKTM trace file for more details.
Thu Mar 28 09:06:15 2013
Time drift detected. Please check VKTM trace file for more details.
Thu Mar 28 09:44:25 2013
NOTE: ASMB terminating
Errors in file /oracle/diag/rdbms/xtybdata/xtybdata1/trace/xtybdata1_asmb_3802094.trc:
ORA-15064: communication failure with ASM instance
ORA-03113: end-of-file on communication channel

是asm实例出了故障
检查asm的告警日志

*** 2013-03-28 09:44:25.446
NOTE: ASMB terminating
ORA-15064: communication failure with ASM instance
ORA-03113: end-of-file on communication channel
Process ID:
Session ID: 961 Serial number: 3
error 15064 detected in background process
ORA-15064: communication failure with ASM instance
ORA-03113: end-of-file on communication channel
Process ID:
Session ID: 961 Serial number: 3
kjzduptcctx: Notifying DIAG for crash event
----- Abridged Call Stack Trace -----
ksedsts()+360< -kjzdssdmp()+240<-kjzduptcctx()+228<-kjzdicrshnfy()+100<-ksuitm()+5124<-ksbrdp()+4508<-opirip()+1624<-opidrv()+608<-sou2o()+136<-opimai_real()+188<-ssthrdmain()+268<-main()+204<-__start()+112
----- End of Abridged Call Stack Trace -----

*** 2013-03-28 09:44:25.473
ASMB (ospid: 3802094): terminating the instance due to error 15064

*** 2013-03-28 09:44:29.630
ksuitm: waiting up to [5] seconds before killing DIAG(4063788)

这也没有指出特定的原因,重启该节点,数据库起来了,但是问题的根本原因还是没找到

在linux系统下,将另一台aix机器上的存储mount到linux下通过nfs,然后在linux下使用expdp导出数据存储在mount到linux下的磁盘上报错

ORA-27054: NFS file system where the file is created or resides is not mounted with correct options
Additional information: 6

解决访问

Alter system set events '10298 trace name context forever,level 32'

执行这个命令，不需要重新启动,但在下次数据库重新启动后就失效了，需要再次手工执行。

如果想数据库重新启动后，自动执行这个命令，则执行下面的命令：

SQL>alter system set event='10298 trace name context forever,level 32' scope=spfile;

C:\Users\dell>exp ufgov/ufgov@orcl file=d:\hddatabackup\ufgov_20130228.dmp log=d:\hddatabackup\ufgov_20130228.log buffer=40960000


Export: Release 10.2.0.1.0 - Production on 星期四 2月 28 10:13:48 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


EXP-00056:
EXP-00000: Export terminated unsuccessfully

这是一个很普通的错误，通常是由于EXP程序在执行某条命令错误所造成的，可以通过自己手动执行如下脚本解决，

@D:\Program_Files\oracle\product\10.2.0\db_1\RDBMS\ADMIN\catmeta.sql
@D:\Program_Files\oracle\product\10.2.0\db_1\RDBMS\ADMIN\catexp.sql

C:\Users\dell>exp ufgov/ufgov@orcl file=d:\hddatabackup\ufgov_20130228.dmp log=d:\hddatabackup\ufgov_20130228.log buffer=40960000

EXP-00056: ORACLE error 932 encountered
ORA-00932: inconsistent datatypes: expected BLOB, CLOB got CHAR
EXP-00000: Export terminated unsuccessfully

如果是测试库，可以尝试：

sqlplus /nolog

SQL> connect / as sysdba
SQL> @D:\Program_Files\oracle\product\10.2.0\db_1\RDBMS\ADMIN\catmetx.sql
SQL> @D:\Program_Files\oracle\product\10.2.0\db_1\RDBMS\ADMIN\utlrp.sql
SQL> exit

如果是生产库，先做备份，可以考虑升级，不行再尝试。

C:\Users\dell>exp ufgov/ufgov@orcl file=d:\hddatabackup\ufgov_20130228.dmp log=d:\hddatabackup\ufgov_20130228.log buffer=40960000

连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集

即将导出指定的用户...
. 正在导出 pre-schema 过程对象和操作
. 正在导出用户 UFGOV 的外部函数库名
. 导出 PUBLIC 类型同义词
. 正在导出专用类型同义词
. 正在导出用户 UFGOV 的对象类型定义
即将导出 UFGOV 的对象...
. 正在导出数据库链接
. 正在导出序号
. 正在导出簇定义
. 即将导出 UFGOV 的表通过常规路径...
. . 正在导出表                ADJUST_FZYE_TEMP导出了           0 行
. . 正在导出表                ADJUST_KMYE_TEMP导出了           0 行
. . 正在导出表                       BBSRCYWLX导出了           0 行
. . 正在导出表                        BBYWLXDM导出了           0 行
. . 正在导出表                           BG_BB导出了           0 行
...............

生成sql trace可以有以下几种方式：

1、参数设置：非常传统的方法。
系统级别：
参数文件中指定: sql_trace=true
或

SQL> alter system set sql_trace=true;

注意：系统级别启用sql_trace，会产生大量trace文件，很容易耗尽磁盘空间，因此一般设置会话级别，并且及时关闭。
会话级别：

SQL> alter session set sql_trace=true;
SQL> 执行sql
SQL> alter session set sql_trace=false;

启用跟踪后，跟踪文件保存在user_dump_dest下
可以使用下面的查询来找到生成的跟踪文件

SQL> select
  2  d.value||'/'||lower(rtrim(i.instance,
  3  chr(0)))||'_ora_'||p.spid||'.trc' trace_file_name
  4  from ( select p.spid
  5  from v$mystat m,
  6  v$session s,v$process p
  7  where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p,
  8  ( select t.instance from v$thread  t,v$parameter v
  9  where v.name = 'thread' and
 10  (v.value = 0 or t.thread# = to_number(v.value))) i,
 11  ( select value from v$parameter
 12  where name = 'user_dump_dest') d
 13  /


TRACE_FILE_NAME
--------------------------------------------------------------------------------
/oracle/admin/RLZY/udump/rlzy_ora_721532.trc

也可以给要生成的跟踪文件指定标识符来让你更容易的找到跟文件

SQL> alter session set tracefile_identifier='jingyong';

2、使用10046事件：
10046事件级别：
Lv0 – 禁用sql_trace，等价于sql_trace=false
Lv1 – 启用标准的sql_trace功能,等价于sql_trace=true
Lv4 – Level 1 + 绑定变量值(bind values)
Lv8 – Level 1 + 等待事件跟踪(waits)
Lv12 – Level 1 + Level 4 + Level 8

全局设定：
参数文件中指定: event=”10046 trace name context forever,level 12″
或者

SQL> alter system set events '10046 trace name context forever, level 12';
SQL> alter system set events '10046 trace name context off';

注意：系统级别启用sql_trace，会产生大量trace文件，很容易耗尽磁盘空间，因此一般设置会话级别，并且及时关闭。

当前session设定：

SQL> alter session set events '10046 trace name context forever, level 12';
SQL> 执行sql
SQL> alter session set events '10046 trace name context off';

3、dbms_session包：只能跟踪当前会话，不能指定会话。
跟踪当前会话：

SQL> exec dbms_session.set_sql_trace(true);
SQL> 执行sql
SQL> exec dbms_session.set_sql_trace(false);

dbms_session.set_sql_trace相当于alter session set sql_trace，从生成的trace文件可以明确地看
alter session set sql_trace语句。
使用dbms_session.session_trace_enable过程，不仅可以看到等待事件信息还可以看到绑定变量信息，
相当于alter session set events ‘10046 trace name context forever, level 12’;语句从生成的trace文件可以确认。

SQL> exec dbms_session.session_trace_enable(waits=>true,binds=>true);
SQL> 执行sql
SQL> exec dbms_session.session_trace_enable();

4、dbms_support包：不应该使用这种方法，非官方支持。
系统默认没有安装这个包，可以手动执行$ORACLE_HOME/rdbms/admin/bmssupp.sql脚本来创建该包
跟踪当前会话：

SQL> exec dbms_support.start_trace
SQL> 执行sql
SQL> exec dbms_support.stop_trace

跟踪其他会话：等待事件+绑定变量，相当于level 12的10046事件。

SQL> select sid,serial#,username from v$session where ...;
SQL> exec dbms_support.start_trace_in_session(sid=>sid,serial=>serial#,waits=>true,binds=>true);
SQL> exec dbms_support.stop_trace_in_session(sid=>sid,serial=>serial#);

5、dbms_system包：
跟踪其他会话：
使用dbms_system.set_ev设置10046事件

SQL> select sid,serial#,username from v$session where ...;
SQL> exec dbms_system.set_ev(sid,serial#,10046,12,'');
SQL> exec dbms_system.set_ev(sid,serial#,10046,0,'');

但经过测试在10g中使用级别为8,12的跟踪并没有在跟踪文件中生产等待事件信息

6、dbms_monitor包：10g提供，功能非常强大。可在模块级别、动作级别、客户端级别、数据库级别、会话级别进行跟踪。oracle官方支持。
跟踪当前会话：

SQL> exec dbms_monitor.session_trace_enable;
SQL> 执行sql
SQL> exec dbms_monitor.session_trace_disable;

跟踪其他会话：

SQL> exec dbms_monitor.session_trace_enable(session_id=>sid,serial_num=>serial#,waits=>true,binds=>true);
SQL> exec dbms_monitor.session_trace_disable(session_id=>sid,serial_num=>serial#);

7、oradebug
这是sqlplus的工具，需要提供OSPID或者oracle PID。
跟踪当前会话：

SQL> oradebug setmypid;
Statement processed.
SQL> oradebug unlimit;
Statement processed.
SQL> oradebug event 10046 trace name context forever,level 12;
Statement processed.
SQL> 执行sql
SQL> oradebug tracefile_name
SQL> oradebug event 10046 trace name context off;
Statement processed.

跟踪其他会话：

SQL> select spid,pid2  from v$process
  2  where addr in (select paddr from v$session where sid=(select distinct sid from v$mystat));
SPID                PID
------------ ----------
1457                 313
SQL> oradebug setospid 1457;
Statement processed.
或者
SQL> oradebug setorapid 313;
Statement processed.
SQL> oradebug unlimit;
Statement processed.
SQL> oradebug event 10046 trace name context forever,level 12;
Statement processed.
SQL> oradebug tracefile_name
SQL> oradebug event 10046 trace name context off;
Statement processed.