undo事务具体是如何回滚,这里提供了大概的异常undo事务回滚的一个过程(更加准确的说，这个过程是在以下几种情况中发生的过程：1.数据库非正常关闭后启动,2.事务未提交会话终止)，数据库先扫描所有回滚段，然后发现有事务未提交回滚段,然后根据这个回滚段定位到undo block,然后定位到data block，当一个undo block回滚完成之后，利用undo的链表规则完成下一个undo block的回滚操作，依次类此，从而实现数据库的回滚操作；回滚的过程是先回滚后操作的块(先进后出原则)
创建测试表

SQL> create table chf.t_xifenfei(a varchar2(4000));
Table created.
SQL> insert into chf.t_xifenfei values (lpad('www.xifenfei.com',4000,'a'));
1 row created.
SQL> insert into chf.t_xifenfei values (lpad('www.xifenfei.com',4000,'b'));
1 row created.
SQL> commit;
Commit complete.
SQL> update chf.t_xifenfei set a=lpad('www.xifenfei.com',4000,'F');
2 rows updated.
SQL> select
  2  dbms_rowid.rowid_relative_fno(rowid) rel_fno,
  3  dbms_rowid.rowid_block_number(rowid) block_no
  4  from chf.t_xifenfei;
   REL_FNO   BLOCK_NO
---------- ----------
         9        421
         9        422

确保表中有两条记录,存储在两个block中

查询测试表相关信息

SQL> select object_id,data_object_id from dba_objects where owner='CHF' AND OBJECT_NAME='T_XIFENFEI';
 OBJECT_ID DATA_OBJECT_ID
---------- --------------
     53917          53917
SQL> SELECT TO_CHAR(53917,'XXXXX') FROM DUAL;
TO_CHA
------
  D29D
SQL> SELECT XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBASQN,UBAREC FROM v$transaction;
    XIDUSN    XIDSLOT     XIDSQN     UBAFIL     UBABLK     UBASQN     UBAREC
---------- ---------- ---------- ---------- ---------- ---------- ----------
         2         28        513          2       3627        253          1
SQL> select to_char(28,'xxx') from dual;
TO_C
----
  1c
SQL> select to_char(513,'xxx') from dual;
TO_C
----
 201
------------------------
--xid=0002.01c.00000201
------------------------
SQL> variable dba varchar2(30)
SQL> exec :dba := dbms_utility.make_data_block_address(2,3627);
PL/SQL procedure successfully completed.
SQL> print dba
DBA
--------------------------------
8392235
SQL> select to_char(253,'xxx') from dual;
TO_C
----
  fd
---------------------
--uda=800e2a.00fd.01
---------------------

通过这些查询可以得知:
1)chf.t_xifenfei的object_id/data_object_id的值为53917/D29D
2)xid=0002.01c.00000201(Xid=usn.slot.wrap)
3)uda=800e2a.00fd.01(Uba=undo block dba.undo record number.undo block sequence number)

dump rollback header

SQL> select * from v$rollname where usn=2;
       USN NAME
---------- ------------------------------
         2 _SYSSMU2$
SQL>  alter system dump undo header "_SYSSMU2$";
System altered.
 index  state cflags  wrap#    uel         scn            dba            parent-xid    nub     stmt_num    cmt
  ------------------------------------------------------------------------------------------------
0x00    9    0x00  0x0201  0x0012  0x0b2c.c02c9b85  0x00800e27  0x0000.000.00000000  0x00000001   0x00000000  1358780575
   ………………
0x1b    9    0x00  0x0201  0x0028  0x0b2c.c02c9bf4  0x00800e27  0x0000.000.00000000  0x00000001   0x00000000  1358780575
0x1c   10    0x80  0x0201  0x0002  0x0b2c.c02ca2a8  0x00800e2b  0x0000.000.00000000  0x00000002   0x00000000  0
0x1d    9    0x00  0x0200  0x0004  0x0b2c.c02c9a8b  0x00800e26  0x0000.000.00000000  0x00000001   0x00000000  1358780444
   ………………
0x2f    9    0x00  0x0201  0x001a  0x0b2c.c02ca1da  0x00800e29  0x0000.000.00000000  0x00000001   0x00000000  1358784176
------------------------------------------------------------------------------
==> Transaction Table
==>  state      0 = IDLE
                1 = Collecting       2 = Prepared            3 = Committed
                4 = Forced Abort     5 = Forced Commit       6 = Forced Mixed
                7 = try again later
                9 = No TX (Committed)    10= 'a' = Active local TX
==>  cflags     1 = TX has started storing collecting information
                2 = TX has forced the collecting information
                4 = Prepared TX needs distributed recovery
                10= Rollback failed on this TX - mark SMON for recover
                20= TX has rolled back its updates
==>  wrap#      is incremented TX slot reuse.
==>  uel        ?
==>  scn        SCN for the TX prepare / commit
==>  dba        is DBA of HEAD of the REDO - Ie: The MOST RECENT CHANG
------------------------------------------------------------------------------
--通过上面的提示可以知道index为0x1c为未提交事务
SQL> select to_number('800e2b','xxxxxxx') from dual;
TO_NUMBER('800E2B','XXXXXXX')
-----------------------------
                      8392235
SQL> select dbms_utility.data_block_address_block(8392235) "block",
  2  dbms_utility.data_block_address_file(8392235)  "file" from dual;
     block       file
---------- ----------
      3627          2
--v$transaction表中查询出来的UBAFIL与UBABLK一致

dump undo block

SQL> alter system dump datafile 2 block 3627;
System altered.
UNDO BLK:
xid: 0x0002.01c.00000201  seq: 0xfd  cnt: 0x1   irb: 0x1   icl: 0x0   flg: 0x0000
 Rec Offset      Rec Offset      Rec Offset      Rec Offset      Rec Offset
---------------------------------------------------------------------------
0x01 0x0fdc
*-----------------------------
* Rec #0x1  slt: 0x1c  objn: 53917(0x0000d29d)  objd: 53917  tblspc: 9(0x00000009)
*       Layer:  11 (Row)   opc: 1   rci 0x00  <--- 表明指向下一个block
Undo type:  Regular undo   Last buffer split:  No
Temp Object:  No
Tablespace Undo:  No
rdba: 0x00800e2a   <--- 为下一个undo block rdba
*-----------------------------
KDO undo record:
KTB Redo
op: 0x03  ver: 0x01
op: Z
Array Update of 1 rows:
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 71
ncol: 1 nnew: 1 size: 0
KDO Op code:  21 row dependencies Disabled
  xtype: XAxtype KDO_KDOM2 flags: 0x00000080  bdba: 0x024001a6  hdba: 0x024001a3
itli: 2  ispac: 0  maxfr: 4858
--bdba表示undo对应的data block,这里对应的是datafile 9 block 422
--hdba表示chf.t_xifenfei的extent 0的 first block(first mapbit)
SQL> select to_number('800e2a','xxxxxxx') from dual;
TO_NUMBER('800E2A','XXXXXXX')
-----------------------------
                      8392234
SQL> select dbms_utility.data_block_address_block(8392234) "block",
  2  dbms_utility.data_block_address_file(8392234)  "file" from dual;
     block       file
---------- ----------
      3626          2
SQL> alter system dump datafile 2 block 3626;
System altered.
UNDO BLK:
xid: 0x0002.01c.00000201  seq: 0xfd  cnt: 0x1   irb: 0x1   icl: 0x0   flg: 0x0000
 Rec Offset      Rec Offset      Rec Offset      Rec Offset      Rec Offset
---------------------------------------------------------------------------
0x01 0x0fac
*-----------------------------
* Rec #0x1  slt: 0x1c  objn: 53917(0x0000d29d)  objd: 53917  tblspc: 9(0x00000009)
*       Layer:  11 (Row)   opc: 1   rci 0x00   <--- 表明指向下一个block
Undo type:  Regular undo    Begin trans    Last buffer split:  No
Temp Object:  No
Tablespace Undo:  No
rdba: 0x00000000  <--- 下一个block为 0x00000000,表示事务到此为止
*-----------------------------
uba: 0x00800e29.00fd.24 ctl max scn: 0x0b2c.c02c99cc prv tx scn: 0x0b2c.c02c9a27
txn start scn: scn: 0x0b2c.c02ca0b3 logon user: 0
 prev brb: 8392230 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x03  ver: 0x01
op: Z
Array Update of 1 rows:
tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 71
ncol: 1 nnew: 1 size: 0
KDO Op code:  21 row dependencies Disabled
  xtype: XAxtype KDO_KDOM2 flags: 0x00000080  bdba: 0x024001a5  hdba: 0x024001a3
itli: 2  ispac: 0  maxfr: 4858
--bdba表示undo对应的data block,这里对应的是datafile 9 block 421

XID: this is the transaction ID, which matches the value made up previously from the query on V$TRANSACTION.
CNT: this is the number of undo records in this block.
IRB: this is the index if the first record to be considered in case of a rollback.
OBJN: this is the object number being altered by the undo record. This matches the value previously queried from DBA_OBJECTS.
RCI: this is the next undo record in the chain to be examined. When rolling back transactions, these undo records are applied one by one on the data blocks to undo the changes.
1. undo链表的意思为:从IRB表示undo 回滚的开始，RCI表示未IRB对应的下一条记录，依次类推。当RCI为0,而且rdba不为0的时候，表示undo 记录关联到下一个block；当RCI为0，而且rdba也为0表示undo结束
2. undo事务回滚是倒序的,这里可以看到事务先读取block 422，再读取block 421

dump data block

SQL> alter system dump datafile 9 block 421;
System altered.
Block header dump:  0x024001a5
 Object id on Block? Y
 seg/obj: 0xd29d  csc: 0xb2c.c02ca2bc  itc: 2  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x24001a1 ver: 0x01 opc: 0
     inc: 0  exflg: 0
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0009.01d.00000232  0x0083dbe0.02be.22  C---    0  scn 0x0b2c.c02ca2a8
0x02   0x0002.01c.00000201  0x00800e2a.00fd.01  ----    1  fsc 0x0000.00000000
SQL> alter system dump datafile 9 block 422;
System altered.
Block header dump:  0x024001a6
 Object id on Block? Y
 seg/obj: 0xd29d  csc: 0xb2c.c02ca2bd  itc: 2  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x24001a1 ver: 0x01 opc: 0
     inc: 0  exflg: 0
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0009.01d.00000232  0x0083dbe0.02be.23  C---    0  scn 0x0b2c.c02ca2a8
0x02   0x0002.01c.00000201  0x00800e2b.00fd.01  ----    1  fsc 0x0000.00000000

这里可以看到Xid,Uba和上面计算出来的一致，看出来事务未提交标记和上述一致.

在春节前写过table中各种类型block坏块是否能被跳过,本来准备节前写完它的姊妹篇关于table中各种blog如果出现坏块,对select/dml操作影响,因为回家一些事情给耽误了,今天补上该文章,这篇文章主要基于试验测试为主,没有从相关block原理上进行分析,如果有时间,后续文章从原理上来分析为什么这些select/dml操作不能执行
创建测试表

SQL> create table t_xifenfei as
  2  select * from dba_objects where rownum<10;
Table created.
SQL> select count(*) from chf.t_xifenfei;
  COUNT(*)
----------
         9
SQL> select
  2  dbms_rowid.rowid_relative_fno(rowid)rel_fno,
  3       max(dbms_rowid.rowid_block_number(rowid)) max_block,
  4       min(dbms_rowid.rowid_block_number(rowid)) min_block
  5       from chf.t_xifenfei
  6       group by dbms_rowid.rowid_relative_fno(rowid);
   REL_FNO  MAX_BLOCK  MIN_BLOCK
---------- ---------- ----------
         4        171        171
SQL> select EXTENT_ID,FILE_ID,BLOCK_ID,blocks from dba_extents where owner='CHF'
  2   AND SEGMENT_NAME='T_XIFENFEI';
 EXTENT_ID    FILE_ID   BLOCK_ID     BLOCKS
---------- ---------- ---------- ----------
         0          4        168          8
SQL> select SEGMENT_NAME,HEADER_FILE,HEADER_BLOCK,blocks,extents from DBA_SEGMENTS
  2  WHERE OWNER='CHF' AND SEGMENT_NAME='T_XIFENFEI';
SEGMENT_NAME                   HEADER_FILE HEADER_BLOCK     BLOCKS    EXTENTS
------------------------------ ----------- ------------ ---------- ----------
T_XIFENFEI                               4          170          8          1

通过alter system dump datafile 4 block n得出相关block数据块类型
168为FIRST LEVEL BITMAP BLOCK
169为SECOND LEVEL BITMAP BLOCK
170为PAGETABLE SEGMENT HEADER
171为trans data

处理block 168

--制造坏块
BBED> set block 168
        BLOCK#          168
BBED> set offset 8188
        OFFSET          8188
BBED> d
 File: /u01/oracle/oradata/ora11g/users01.dbf (0)
 Block: 168              Offsets: 8188 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 0320d14f
 <32 bytes per line>
BBED> m /x 0320d14e
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
 File: /u01/oracle/oradata/ora11g/users01.dbf (0)
 Block: 168              Offsets: 8188 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 0320d14e
 <32 bytes per line>
BBED> sum apply
Check value for File 0, Block 168:
current = 0xf60b, required = 0xf60b
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oracle/oradata/ora11g/users01.dbf
BLOCK = 168
Block 168 is corrupt
Corrupt block relative dba: 0x010000a8 (file 0, block 168)
Fractured block found during verification
Data in bad block:
 type: 32 format: 2 rdba: 0x010000a8
 last change scn: 0x0b8c.3bff4fd1 seq: 0x3 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x4ed12003
 check value in block header: 0xf60b
 computed block checksum: 0x0
DBVERIFY - Verification complete
Total Blocks Examined         : 1
Total Blocks Processed (Data) : 0
Total Blocks Failing   (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 1
Total Blocks Influx           : 2
Message 531 not found;  product=RDBMS; facility=BBED
--select操作
SQL> select count(*) from chf.t_xifenfei;
  COUNT(*)
----------
         9
--dml操作
SQL> delete from chf.t_xifenfei where rownum<3;
2 rows deleted.
----注意update操作
SQL> update chf.t_xifenfei set object_name='www.xifenfei.com';
7 rows updated.
SQL> insert into chf.t_xifenfei select * from dba_objects where rownum=1;
insert into chf.t_xifenfei select * from dba_objects where rownum=1
                *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 168)
ORA-01110: data file 4: '/u01/oracle/oradata/ora11g/users01.dbf'

这里证明对于FIRST LEVEL BITMAP BLOCK,在delete,select操作正常，insert操作异常，update操作待定(update操作不一定能够立马展示效果)

处理block 169

--标记坏块
BBED> set block 169
        BLOCK#          169
BBED> set offset 8188
        OFFSET          8188
BBED> d
 File: /u01/oracle/oradata/ora11g/users01.dbf (0)
 Block: 169              Offsets: 8188 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 0221ce4f
 <32 bytes per line>
BBED> m /x 0221ce4e
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
 File: /u01/oracle/oradata/ora11g/users01.dbf (0)
 Block: 169              Offsets: 8188 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 0221ce4e
 <32 bytes per line>
BBED> sum apply
Check value for File 0, Block 169:
current = 0x9d2f, required = 0x9d2f
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oracle/oradata/ora11g/users01.dbf
BLOCK = 169
Block 169 is corrupt
Corrupt block relative dba: 0x010000a9 (file 0, block 169)
Fractured block found during verification
Data in bad block:
 type: 33 format: 2 rdba: 0x010000a9
 last change scn: 0x0b8c.3bff4fce seq: 0x2 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x4ece2102
 check value in block header: 0x9d2f
 computed block checksum: 0x0
DBVERIFY - Verification complete
Total Blocks Examined         : 1
Total Blocks Processed (Data) : 0
Total Blocks Failing   (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 1
Total Blocks Influx           : 2
Message 531 not found;  product=RDBMS; facility=BBED
--select操作
SQL> select count(*) from chf.t_xifenfei;
  COUNT(*)
----------
         9
--dml操作
SQL> delete from chf.t_xifenfei where rownum<2;
1 row deleted.
----注意update操作
SQL> update chf.t_xifenfei set object_name='www.xifenfei.com';
9 rows updated.
SQL> alter table t_xifenfei modify EDITION_NAME varchar2(4000);
Table altered.
SQL> update t_xifenfei set EDITION_NAME=lpad('www.xifenfei.com', 4000, '0');
update t_xifenfei set EDITION_NAME=lpad('www.xifenfei.com', 4000, '0')
       *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 169)
ORA-01110: data file 4: '/u01/oracle/oradata/ora11g/users01.dbf'
SQL> insert into chf.t_xifenfei
  2  select * from dba_objects where rownum<2;
insert into chf.t_xifenfei
                *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 169)
ORA-01110: data file 4: '/u01/oracle/oradata/ora11g/users01.dbf'

SECOND LEVEL BITMAP BLOCK在delete,select操作正常，insert操作异常，update操作分情况(如果更新的列字符串交短，可能不报错，如果更新的字符串较长可能报错)

处理block 170

--标记坏块
BBED> SET BLOCK 170
        BLOCK#          170
BBED> set offset 8188
        OFFSET          8188
BBED> d
 File: /u01/oracle/oradata/ora11g/users01.dbf (0)
 Block: 170              Offsets: 8188 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 0223b91b
 <32 bytes per line>
BBED> m /x  0223b91a
 File: /u01/oracle/oradata/ora11g/users01.dbf (0)
 Block: 170              Offsets: 8188 to 8191           Dba:0x00000000
------------------------------------------------------------------------
 0223b91a
 <32 bytes per line>
BBED> sum apply
Check value for File 0, Block 170:
current = 0xb7d4, required = 0xb7d4
BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oracle/oradata/ora11g/users01.dbf
BLOCK = 170
Block 170 is corrupt
Corrupt block relative dba: 0x010000aa (file 0, block 170)
Fractured block found during verification
Data in bad block:
 type: 35 format: 2 rdba: 0x010000aa
 last change scn: 0x0b8c.3c001bb9 seq: 0x2 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x1ab92302
 check value in block header: 0xb7d4
 computed block checksum: 0x0
DBVERIFY - Verification complete
Total Blocks Examined         : 1
Total Blocks Processed (Data) : 0
Total Blocks Failing   (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 1
Total Blocks Influx           : 2
Message 531 not found;  product=RDBMS; facility=BBED
--select操作
SQL> select count(*) from chf.t_xifenfei;
select count(*) from chf.t_xifenfei
                         *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 170)
ORA-01110: data file 4: '/u01/oracle/oradata/ora11g/users01.dbf'
--dml操作
SQL> update chf.t_xifenfei where object_name='www.xifenfei.com';
update chf.t_xifenfei where object_name='www.xifenfei.com'
                      *
ERROR at line 1:
ORA-00971: missing SET keyword
SQL> update chf.t_xifenfei set object_name='www.xifenfei.com';
update chf.t_xifenfei set object_name='www.xifenfei.com'
           *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 170)
ORA-01110: data file 4: '/u01/oracle/oradata/ora11g/users01.dbf'
SQL> delete from chf.t_xifenfei where rownum<2;
delete from chf.t_xifenfei where rownum<2
                *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 170)
ORA-01110: data file 4: '/u01/oracle/oradata/ora11g/users01.dbf'

PAGETABLE SEGMENT HEADER异常的时候，数据库包括select,update,delete,insert操作都不能进行

结果汇总
1.BITMAP BLOCK异常的时候，select/delete操作可以正常进行，insert操作异常，update操作可能异常也可能正常
2.SEGMENT HEADER异常的时候，数据库包括select,update,delete,insert操作都不能进行
3.对于这些特殊的block出现坏块，如果有rman备份，从10g开始可以通过rman blockrecover来修复
4.如果没有rman备份,可以BITMAP BLOCK可以类似ctas重建,SEGMENT HEADER可以通过dul scan extent抽取数据
5.对于trans data太过于常见，而且event就可以跳过,在以前的文章中说过，不再讲述

在有些时候，我们需要通过systemstate或者hanganalyze来收集数据库hang信息，但是当我们的数据库不能登录的时候，无法通过常规手段来使用该方法来收集信息。ORACLE的开发者也考虑到了类似情况，让我们可以通过dbx或者gdb来收集数据库信息.这里我通过在linux平台上模拟一个新会话，然后通过gdb直接对该会话进行做systemstate,然后操作该会话(证明gdb操作后正常),来实现类似系统hang住，无法登陆的时候怎么做systemstate.
模拟会话
就是假设我们hang住的系统中的数据库进程(为了后面gdb和验证对会话影响而使用,不然可以直接使用oracle 后台进程来完成该操作)

[oracle@xifenfei ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Jan 17 18:28:30 2013
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>

查找数据库进程
在另外一个shell窗口找出数据库进程

[root@xifenfei trace]# ps -ef|grep LOCAL
oracle    7476  7473  0 18:28 ?        00:00:00 oracleora11g (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
root      7487  7326  0 18:28 pts/2    00:00:00 grep LOCAL

gdb做systemstate

[oracle@xifenfei trace]$ gdb $ORACLE_HOME/bin/oracle 7476
GNU gdb (GDB) Red Hat Enterprise Linux (7.0.1-37.el5)
Copyright (C) 2009 Free Software Foundation, Inc.
License GPLv3+: GNU GPL version 3 or later <http://gnu.org/licenses/gpl.html>
This is free software: you are free to change and redistribute it.
There is NO WARRANTY, to the extent permitted by law.  Type "show copying"
and "show warranty" for details.
This GDB was configured as "i386-redhat-linux-gnu".
…………
Reading symbols from /u01/oracle/oracle/product/11.2.0/db_1/lib/libnque11.so...
Loaded symbols for /u01/oracle/oracle/product/11.2.0/db_1/lib/libnque11.so
0xb789c424 in __kernel_vsyscall ()
(gdb)  print ksudss(10)  <--输入print ksudss(10)
$1 = 0
(gdb) quit
A debugging session is active.
        Inferior 1 [process 7476] will be detached.
Quit anyway? (y or n) y
Detaching from program: /u01/oracle/oracle/product/11.2.0/db_1/bin/oracle, process 7476

查看trace文件

[oracle@xifenfei trace]$ ls -l *7476*.trc
-rw-r----- 1 oracle oinstall 742438 Jan 17 18:28 ora11g_ora_7476.trc
[oracle@xifenfei trace]$ more ora11g_ora_7476.trc
Trace file /u01/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_7476.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/oracle/oracle/product/11.2.0/db_1
System name:    Linux
Node name:      xifenfei
Release:        2.6.32-200.13.1.el5uek
Version:        #1 SMP Wed Jul 27 20:21:26 EDT 2011
Machine:        i686
VM name:        VMWare Version: 6
Instance name: ora11g
Redo thread mounted by this instance: 1
Oracle process number: 21
Unix process pid: 7476, image: oracle@xifenfei (TNS V1-V3)
*** 2013-01-17 18:28:59.225
*** SESSION ID:(143.23) 2013-01-17 18:28:59.225
*** CLIENT ID:() 2013-01-17 18:28:59.225
*** SERVICE NAME:(SYS$USERS) 2013-01-17 18:28:59.225
*** MODULE NAME:(sqlplus@xifenfei (TNS V1-V3)) 2013-01-17 18:28:59.225
*** ACTION NAME:() 2013-01-17 18:28:59.225
===================================================
SYSTEM STATE (level=10)  <---systemstate levle 10
------------
System global information:
     processes: base 0x32bc5b38, size 150, cleanup 0x32bd5990
     allocation: free sessions 0x32085b84, free calls (nil)
     control alloc errors: 0 (process), 0 (session), 0 (call)
     PMON latch cleanup depth: 0
     seconds since PMON's last scan for dead processes: 693
     system statistics:

这里证明使用gdb–>print ksudss(10)对数据库做的是systemstate level 10

验证做gdb的进程

SQL> select * from dual;
D
-
X
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

证明被gdb处理过的进程还是正常(未收到额外破坏),所以我们可以确定在系统hang住，而且新会话不能正常建立连接的时候，我们可以尝试着使用ksudss(10)来对系统做systemstate然后分析

自动诊断存储库(Automatic Diagnostic Repository) 是oracle database 11g的一个新特性,它提供了一个中心化存储trace files, dumps, health monitor reports, alert logs等. 不同的产品和实例都有统一的目录结构, 把各自的诊断数据存储在自己的ADR_HOME里.从11gr1–>11gr2–>12cr1 adrci的功能逐渐增强,也确实需要关注下其最基本的功能,管理alert日志和trace文件.
adrci启动

[oracle@xifenfei ~]$ adrci
ADRCI: Release 12.1.0.0.2        on Sun Dec 16 15:27:51 2012
Copyright (c) 1982, 2012, Oracle and/or its affiliates.  All rights reserved.
ADR base = "/u01/app/oracle"

adrci帮助

adrci> help
 HELP [topic]
   Available Topics:
        CREATE REPORT
        ECHO
        EXIT
        HELP
        HOST
        IPS
        PURGE
        RUN
        SET BASE
        SET BROWSER
        SET CONTROL
        SET ECHO
        SET EDITOR
        SET HOMES | HOME | HOMEPATH
        SET TERMOUT
        SHOW ALERT
        SHOW BASE
        SHOW CONTROL
        SHOW HM_RUN
        SHOW HOMES | HOME | HOMEPATH
        SHOW INCDIR
        SHOW INCIDENT
        SHOW LOG
        SHOW PROBLEM
        SHOW REPORT
        SHOW TRACEFILE
        SPOOL
 There are other commands intended to be used directly by Oracle, type
 "HELP EXTENDED" to see the list

adrci设置home
当adrci中含有了多个home目录的时候,需要手工设置home值才能够很好的运行该工具

adrci> show home
ADR Homes:
diag/tnslsnr/xifenfei/listener
diag/tnslsnr/xifenfei/listener_a
diag/clients/user_oracle/host_2460950761_80
diag/rdbms/yxhe_pitr_xifenfei/yxhE
diag/rdbms/aief_pitr_xifenfei/aief
diag/rdbms/xifenfei/xff
diag/rdbms/xifenfei/xifenfei
diag/rdbms/iwcn_pitr_xifenfei/iwcn
diag/rdbms/abrn_pitr_xifenfei/aBrn
diag/rdbms/efqn_pitr_xifenfei/efqn
diag/rdbms/gwix_pitr_xifenfei/gwix
adrci> show alert -tail 1
DIA-48449: Tail alert can only apply to single ADR home
adrci> set home diag/rdbms/xifenfei/xff
adrci> show home
ADR Homes:
diag/rdbms/xifenfei/xff

adrci help命令具体使用

adrci> help show alert
  Usage: SHOW ALERT [-p <predicate_string>]  [-term]
                    [ [-tail [num] [-f]] | [-file <alert_file_name>] ]
  Purpose: Show alert messages.
  Options:
    [-p <predicate_string>]: The predicate string must be double-quoted.
    The fields in the predicate are the fields:
        ORIGINATING_TIMESTAMP         timestamp
        NORMALIZED_TIMESTAMP          timestamp
        ORGANIZATION_ID               text(65)
        COMPONENT_ID                  text(65)
        HOST_ID                       text(65)
        HOST_ADDRESS                  text(17)
        MESSAGE_TYPE                  number
        MESSAGE_LEVEL                 number
        MESSAGE_ID                    text(65)
        MESSAGE_GROUP                 text(65)
        CLIENT_ID                     text(65)
        MODULE_ID                     text(65)
        PROCESS_ID                    text(33)
        THREAD_ID                     text(65)
        USER_ID                       text(65)
        INSTANCE_ID                   text(65)
        DETAILED_LOCATION             text(161)
        UPSTREAM_COMP_ID              text(101)
        DOWNSTREAM_COMP_ID            text(101)
        EXECUTION_CONTEXT_ID          text(101)
        EXECUTION_CONTEXT_SEQUENCE    number
        ERROR_INSTANCE_ID             number
        ERROR_INSTANCE_SEQUENCE       number
        MESSAGE_TEXT                  text(2049)
        MESSAGE_ARGUMENTS             text(129)
        SUPPLEMENTAL_ATTRIBUTES       text(129)
        SUPPLEMENTAL_DETAILS          text(4000)
        PROBLEM_KEY                   text(65)
    [-tail [num] [-f]]: Output last part of the alert messages and
    output latest messages as the alert log grows. If num is not specified,
    the last 10 messages are displayed. If "-f" is specified, new data
    will append at the end as new alert messages are generated.
    [-term]: Direct results to terminal. If this option is not specified,
    the results will be open in an editor.
    By default, it will open in emacs, but "set editor" can be used
    to set other editors.
    [-file <alert_file_name>]: Allow users to specify an alert file which
    may not be in ADR. <alert_file_name> must be specified with full path.
    Note that this option cannot be used with the -tail option
  Examples:
    show alert
    show alert -p "message_text like '%incident%'"
    show alert -tail 20

adrci查看alert日志
1.使用alert命令为例子
2.这里的tail n并非传统的tail 命令指定的行数结果

--显示tail 1日志
adrci> show alert -tail 1
2012-12-15 23:41:35.571000 +08:00
System state dump requested by (instance=1, osid=3633 (PSP0)), summary=[abnormal instance termination].
2012-12-15 23:41:40.454000 +08:00
Instance terminated by PSP0, pid = 3633
--显示含PSP0行日志
adrci> show alert -p "message_text like '%PSP0%'"
ADR Home = /u01/app/oracle/diag/rdbms/xifenfei/xff:
*************************************************************************
Output the results to file: /tmp/alert_26813_1400_xff_1.ado
2012-12-06 22:47:43.072000 +08:00
Starting background process PSP0
PSP0 started with pid=3, OS id=23605
2012-12-06 23:29:19.362000 +08:00
Starting background process PSP0
PSP0 started with pid=3, OS id=25006
2012-12-08 20:38:43.602000 +08:00
Starting background process PSP0
PSP0 started with pid=3, OS id=3942
2012-12-12 21:24:19.874000 +08:00
Starting background process PSP0
PSP0 started with pid=3, OS id=3673
2012-12-12 21:34:19.647000 +08:00
Starting background process PSP0
PSP0 started with pid=3, OS id=4254
2012-12-15 23:41:35.571000 +08:00
System state dump requested by (instance=1, osid=3633 (PSP0)), summary=[abnormal instance termination].
2012-12-15 23:41:40.454000 +08:00
Instance terminated by PSP0, pid = 3633
--含PSP0 tail 1行记录
adrci> show alert -p "message_text like '%PSP0%'" -tail 1
2012-12-15 23:41:35.571000 +08:00
System state dump requested by (instance=1, osid=3633 (PSP0)), summary=[abnormal instance termination].
2012-12-15 23:41:40.454000 +08:00
Instance terminated by PSP0, pid = 3633

补充其他常用命令

--清理60分钟前alert
purge -age 60 -type  ALERT
--清理120分钟钱trace文件
purge -age 120 -type TRACE
--监控alert日志
SHOW ALERT -tail 1 -f
--查看trace文件
SHOW TRACEFILE -tr/t
--查看特定进程trace文件并排序
show tracefile %log% -tr
--查看特定spid进程
show tracefile %27476%

补充说明:对于adr的进一步使用,可以通过使用help命令来逐步查询并且尝试操作,adrci中的help是非常强大工具.

在日常的数据库维护中，经常出现因为数据库登录审计的功能启动,导致system表空间被用满.从而出现异常,一般建议把aud$相关对象迁移到其他表空间,从而避免system被用完的风险.
人工move aud$相关对象

alter table AUDIT$ move tablespace users;
alter table AUDIT_ACTIONS move tablespace users;
alter table AUD$ move tablespace users;
alter table AUD$ move lob(SQLBIND) store as SYS_IL0000000384C00041$$ (tablespace users);
alter table AUD$ move lob(SQLTEXT) store as SYS_IL0000000384C00041$$ (tablespace users);
alter index I_AUDIT rebuild online tablespace users;
alter index I_AUDIT_ACTIONS rebuild online tablespace users;
--可能修改值(index和lob index)
SQL> select COLUMN_NAME,index_name from dba_lobs where owner='SYS' and table_name='AUD$';
COLUMN_NAME                              INDEX_NAME
---------------------------------------- ------------------------------
SQLBIND                                  SYS_IL0000000384C00040$$
SQLTEXT                                  SYS_IL0000000384C00041$$
SQL> SELECT INDEX_NAME FROM DBA_INDEXES WHERE TABLE_NAME='AUD$';
INDEX_NAME
------------------------------
SYS_IL0000000384C00040$$
SYS_IL0000000384C00041$$
SQL> SELECT INDEX_NAME FROM DBA_INDEXES WHERE TABLE_NAME='AUDIT$';
INDEX_NAME
------------------------------
I_AUDIT
SQL>  SELECT INDEX_NAME FROM DBA_INDEXES WHERE TABLE_NAME='AUDIT_ACTIONS';
INDEX_NAME
------------------------------
I_AUDIT_ACTIONS

DBMS_AUDIT_MGMT实现迁移

conn / as sysdba
BEGIN
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD,
audit_trail_location_value => 'USERS');
END;
/

验证DBMS_AUDIT_MGMT效果

SQL> select segment_name,tablespace_name from dba_segments where
  2  segment_name in('AUD$','SYS_IL0000000384C00040$$','SYS_IL0000000384C00041$$',
  3  'AUDIT$','I_AUDIT','AUDIT_ACTIONS','I_AUDIT_ACTIONS');
SEGMENT_NAME                                                                      TABLESPACE_NAME
--------------------------------------------------------------------------------- ------------------------------
AUDIT_ACTIONS                                                                     SYSTEM
AUDIT$                                                                            SYSTEM
AUD$                                                                              SYSTEM
SYS_IL0000000384C00041$$                                                          SYSTEM
SYS_IL0000000384C00040$$                                                          SYSTEM
I_AUDIT_ACTIONS                                                                   SYSTEM
I_AUDIT                                                                           SYSTEM
SQL> BEGIN
  2  DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD,
  3  audit_trail_location_value => 'USERS');
  4  END;
  5  /
PL/SQL procedure successfully completed.
SQL> select segment_name,tablespace_name from dba_segments where
  2   segment_name in('AUD$','SYS_IL0000000384C00040$$','SYS_IL0000000384C00041$$',
  3   'AUDIT$','I_AUDIT','AUDIT_ACTIONS','I_AUDIT_ACTIONS');
SEGMENT_NAME                                                                      TABLESPACE_NAME
--------------------------------------------------------------------------------- ------------------------------
AUDIT_ACTIONS                                                                     SYSTEM
AUDIT$                                                                            SYSTEM
AUD$                                                                              USERS
SYS_IL0000000384C00041$$                                                          USERS
SYS_IL0000000384C00040$$                                                          USERS
I_AUDIT_ACTIONS                                                                   SYSTEM
I_AUDIT                                                                           SYSTEM

通过试验证明DBMS_AUDIT_MGMT就是迁移了AUD$表中相关对象,对于和审计相关的其他几个对象并未迁移到其他表空间

aud$相关说明
1.DBMS_AUDIT_MGMT版本支持情况

It is still not supported (but it works) to use it on 10.2.0.4 and 11.1.0.7 for non-Audit Vault Environment.
Using RDBMS with Audit Vault, it is supported  for 10.2.0.4.0 and 11.1.0.7.0
as the DBMS_AUDIT_MGMT Package is provided to be used with an Audit Vault Environment.

2.该包可以实现在线迁移，特别是在高业务的系统中,可以实现在线迁移,而人工的move操作不能实现在线处理
3.对于AUD$对象，如果登录审计数据不是非常重要,可以通过truncate来解决一时的问题，在业务高的系统，可能truncate不能马上操作成功，可以尝试使用11gr2的新特性alter session set ddl_lock_timeout = 10;来实现自动ddl尝试
4.如果确定不需要登录审计功能，可以通过设置audit_trail=none来关闭(需要重启实例)

今天网友问到了v$sgainfo中的Free SGA Memory Available的一些情况,开始我也比较迷糊,为什么会出现Free SGA Memory Available的值不为0,通过查询一些试验和测试,对这个问题进行了有力的说明
数据库版本10G

SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
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

当前SGA各组件信息

SQL> select name,bytes/1024/1024 from v$sgainfo;
NAME                             BYTES/1024/1024
-------------------------------- ---------------
Fixed SGA Size                        1.20853043
Redo Buffers                           6.7890625
Buffer Cache Size                            192
Shared Pool Size                              88
Large Pool Size                                4
Java Pool Size                                 4
Streams Pool Size                              8
Granule Size                                   4
Maximum SGA Size                             304
Startup overhead in Shared Pool               36
Free SGA Memory Available                      0
11 rows selected.

sga配置

SQL> show parameter sga;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 304M
sga_target                           big integer 304M

在10g中，当采用asmm管理时,如果sga_max_size=sga_target,则Free SGA Memory Available为0

修改sga_target

SQL> alter system set sga_target=290M;
System altered.

再次查询v$sgainfo

SQL> select name,bytes/1024/1024 from v$sgainfo;
NAME                             BYTES/1024/1024
-------------------------------- ---------------
Fixed SGA Size                        1.20853043
Redo Buffers                           6.7890625
Buffer Cache Size                            180
Shared Pool Size                              88
Large Pool Size                                4
Java Pool Size                                 4
Streams Pool Size                              8
Granule Size                                   4
Maximum SGA Size                             304
Startup overhead in Shared Pool               36
Free SGA Memory Available                     12
11 rows selected.

再次查看sga_target值

SQL> show parameter sga;
NAME                                 TYPE        VALUE
------------------------------------ ----------- --------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 304M
sga_target                           big integer 292M

在10g中，当采用asmm管理时,如果sga_max_size>sga_target,则Free SGA Memory Available为sga_max_size-sga_target

找出sga_target修改为292M而不是290M原因

SQL> select component, granule_size from v$sga_dynamic_components;
COMPONENT                                                        GRANULE_SIZE
---------------------------------------------------------------- ------------
shared pool                                                           4194304
large pool                                                            4194304
java pool                                                             4194304
streams pool                                                          4194304
DEFAULT buffer cache                                                  4194304
KEEP buffer cache                                                     4194304
RECYCLE buffer cache                                                  4194304
DEFAULT 2K buffer cache                                               4194304
DEFAULT 4K buffer cache                                               4194304
DEFAULT 8K buffer cache                                               4194304
DEFAULT 16K buffer cache                                              4194304
DEFAULT 32K buffer cache                                              4194304
ASM Buffer Cache                                                      4194304
13 rows selected.
SQL> select 4194304/1024/1024 from dual;
4194304/1024/1024
-----------------
                4
SQL> select 292/4 from dual;
     292/4
----------
        73

因为sga的内存分配是按照GRANULE为单位进行的,而在该库中sga对应组件的GRANULE为4M,所以我们修改的290M的最近的GRANULE整数倍为292M

sga_target为0的情况

SQL> show parameter sga_target;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------
sga_target                           big integer 0
SQL> select name,bytes/1024/1024 from v$sgainfo;
NAME                             BYTES/1024/1024
-------------------------------- ---------------
Fixed SGA Size                        1.20846176
Redo Buffers                           6.7890625
Buffer Cache Size                            180
Shared Pool Size                              88
Large Pool Size                                4
Java Pool Size                                 4
Streams Pool Size                              8
Granule Size                                   4
Maximum SGA Size                             292
Startup overhead in Shared Pool               36
Free SGA Memory Available                      0
11 rows selected.

在10g中，如果不采用asmm内存管理模式,Free SGA Memory Available为0

11g数据库版本

SQL> select * from v$version;
BANNER
-----------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

查询v$sgainfo信息

SQL> select name,bytes/1024/1024 from v$sgainfo;
NAME                             BYTES/1024/1024
-------------------------------- ---------------
Fixed SGA Size                        1.28236008
Redo Buffers                          6.03515625
Buffer Cache Size                             20
Shared Pool Size                             116
Large Pool Size                                4
Java Pool Size                                 4
Streams Pool Size                              8
Shared IO Pool Size                            0
Granule Size                                   4
Maximum SGA Size                      299.320313
Startup overhead in Shared Pool       52.2684898
Free SGA Memory Available                    140

oracle内存相关参数设置

SQL> show parameter memory;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------
hi_shared_memory_address             integer     0
memory_max_target                    big integer 300M
memory_target                        big integer 300M
shared_memory_address                integer     0
SQL> show parameter pga;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------
pga_aggregate_target                 big integer 0

数据库动态组件内存分配

SQL> select COMPONENT,CURRENT_SIZE/1024/1024 CURRENT_SIZE from V$MEMORY_DYNAMIC_COMPONENTS;
COMPONENT                                                        CURRENT_SIZE
---------------------------------------------------------------- ------------
shared pool                                                               116
large pool                                                                  4
java pool                                                                   4
streams pool                                                                8
SGA Target                                                                160
DEFAULT buffer cache                                                       20
KEEP buffer cache                                                           0
RECYCLE buffer cache                                                        0
DEFAULT 2K buffer cache                                                     0
DEFAULT 4K buffer cache                                                     0
DEFAULT 8K buffer cache                                                     0
DEFAULT 16K buffer cache                                                    0
DEFAULT 32K buffer cache                                                    0
Shared IO Pool                                                              0
PGA Target                                                                140
ASM Buffer Cache                                                            0
16 rows selected.

初步怀疑:在11g的amm内存管理模式下Free SGA Memory Available和PGA Target相等

尝试修改pga_aggregate_target值

SQL> alter system set pga_aggregate_target=150M;
System altered.

再次查询v$sgainfo

SQL>  select name,bytes/1024/1024 from v$sgainfo;
NAME                             BYTES/1024/1024
-------------------------------- ---------------
Fixed SGA Size                        1.28236008
Redo Buffers                          6.03515625
Buffer Cache Size                              8
Shared Pool Size                             116
Large Pool Size                                4
Java Pool Size                                 4
Streams Pool Size                              8
Shared IO Pool Size                            0
Granule Size                                   4
Maximum SGA Size                      299.320313
Startup overhead in Shared Pool       52.2684898
Free SGA Memory Available                    152

查询动态组件内存分布

SQL> select COMPONENT,CURRENT_SIZE/1024/1024 CURRENT_SIZE from V$MEMORY_DYNAMIC_COMPONENTS;
COMPONENT                                                        CURRENT_SIZE
---------------------------------------------------------------- ------------
shared pool                                                               116
large pool                                                                  4
java pool                                                                   4
streams pool                                                                8
SGA Target                                                                148
DEFAULT buffer cache                                                        8
KEEP buffer cache                                                           0
RECYCLE buffer cache                                                        0
DEFAULT 2K buffer cache                                                     0
DEFAULT 4K buffer cache                                                     0
DEFAULT 8K buffer cache                                                     0
DEFAULT 16K buffer cache                                                    0
DEFAULT 32K buffer cache                                                    0
Shared IO Pool                                                              0
PGA Target                                                                152
ASM Buffer Cache                                                            0
16 rows selected.

进一步证明:在11g的amm内存管理模式下Free SGA Memory Available和PGA Target相等

设置pga_aggregate_target为150M,PGA Target为152M原因分析

SQL> select COMPONENT,GRANULE_SIZE from V$MEMORY_DYNAMIC_COMPONENTS;
COMPONENT                                                        GRANULE_SIZE
---------------------------------------------------------------- ------------
shared pool                                                           4194304
large pool                                                            4194304
java pool                                                             4194304
streams pool                                                          4194304
SGA Target                                                            4194304
DEFAULT buffer cache                                                  4194304
KEEP buffer cache                                                     4194304
RECYCLE buffer cache                                                  4194304
DEFAULT 2K buffer cache                                               4194304
DEFAULT 4K buffer cache                                               4194304
DEFAULT 8K buffer cache                                               4194304
DEFAULT 16K buffer cache                                              4194304
DEFAULT 32K buffer cache                                              4194304
Shared IO Pool                                                        4194304
PGA Target                                                            4194304
ASM Buffer Cache                                                      4194304
16 rows selected.

原理同上述10g中的sga_target分析，不再重复,主要就是:150M不能被4M整除，所以取最近的152M

整体总结
1.如果不采用asmm和amm,Free SGA Memory Available为0
2.如果采用asmm,当sga_max_size=sga_target,则Free SGA Memory Available为0
3.如果采用asmm,当sga_max_size>sga_target,则Free SGA Memory Available为sga_max_size-sga_target
4.如果采用amm,Free SGA Memory Available和PGA Target相等

ORA-06502/ORA-06512错误

SQL> @?/rdbms/admin/awrload.sql
~~~~~~~~~~
AWR LOAD
~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~  This script will load the AWR data from a dump file. The   ~
~  script will prompt users for the following information:    ~
~     (1) name of directory object                            ~
~     (2) name of dump file                                   ~
~     (3) staging schema name to load AWR data into           ~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Specify the Directory Name
~~~~~~~~~~~~~~~~~~~~~~~~~~
Directory Name                 Directory Path
------------------------------ -------------------------------------------------
DATA_PUMP_DIR                  /u01/oracle/admin/ora11g/dpdump/
ORACLE_OCM_CONFIG_DIR          /u01/oracle/oracle/product/11.2.0/db_1/ccr/state
XMLDIR                         /u01/oracle/oracle/product/11.2.0/db_1/rdbms/xml
Choose a Directory Name from the list above (case-sensitive).
Enter value for directory_name: DATA_PUMP_DIR
Using the dump directory: DATA_PUMP_DIR
Specify the Name of the Dump File to Load
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Please specify the prefix of the dump file (.dmp) to load:
Enter value for file_name: awrdat_70441_70885-vpos-primary
Loading from the file name: awrdat_70441_70885-vpos-primary.dmp
begin
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 2

因为导出来的awr数据库的编码是AL32UTF8,而现在的编码是ZHS16GBK,所以解决方法是设置NLS_LANG为合适编码

[oracle@xifenfei ~]$ env|grep NLS_LANG
NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
[oracle@xifenfei ~]$ export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

ORA-31640

SQL> @?/rdbms/admin/awrload.sql
~~~~~~~~~~
AWR LOAD
~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~  This script will load the AWR data from a dump file. The   ~
~  script will prompt users for the following information:    ~
~     (1) name of directory object                            ~
~     (2) name of dump file                                   ~
~     (3) staging schema name to load AWR data into           ~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Specify the Directory Name
~~~~~~~~~~~~~~~~~~~~~~~~~~
Directory Name                 Directory Path
------------------------------ -------------------------------------------------
DATA_PUMP_DIR                  /u01/oracle/admin/ora11g/dpdump/
ORACLE_OCM_CONFIG_DIR          /u01/oracle/oracle/product/11.2.0/db_1/ccr/state
XMLDIR                         /u01/oracle/oracle/product/11.2.0/db_1/rdbms/xml
Choose a Directory Name from the list above (case-sensitive).
Enter value for directory_name: DATA_PUMP_DIR
Using the dump directory: DATA_PUMP_DIR
Specify the Name of the Dump File to Load
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Please specify the prefix of the dump file (.dmp) to load:
Enter value for file_name: awrdat_70441_70885-vpos-primary  <--注意输入
Loading from the file name: awrdat_70441_70885-vpos-primary.dmp
…………
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|  Loading the AWR data from the following
|  directory/file:
|   /u01/oracle/admin/ora11g/dpdump/
|   awrdat_70441_70885-vpos-primar.dmp    <--提示的文件名
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|
|  *** AWR Load Started ...
|
|  This operation will take a few moments. The
|  progress of the AWR load operation can be
|  monitored in the following directory/file:
|   /u01/oracle/admin/ora11g/dpdump/
|   awrdat_70441_70885-vpos-primar.log
|
DBMS_DATAPUMP.ADD_FILE(dump file)
ORA-39001: invalid argument value
Exception encountered in AWR_LOAD
begin
*
ERROR at line 1:
ORA-20115: datapump import encountered error:
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31640: unable to open dump file
"/u01/oracle/admin/ora11g/dpdump/awrdat_70441_70885-vpos-primar.dmp" for read
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-06512: at "SYS.DBMS_SWRF_INTERNAL", line 1717
ORA-06512: at line 3
begin
*
ERROR at line 1:
ORA-20106: AWR tables do not exist for the 'AWR_STAGE' user
ORA-06512: at "SYS.DBMS_SWRF_INTERNAL", line 2920
ORA-00942: table or view does not exist
ORA-06512: at line 3
... Dropping AWR_STAGE user
End of AWR Load

这里我们可以看到,dmp文件名为31个字符，而在提示文件名的时候是30个字符，从而出现了dmp文件不存在而导致的相关错误，解决方案重命名dmp文件，名称不超过30个字符