分类目录归档：Oracle备份恢复

bbed 恢复 GLOBAL_NAME 为空故障

发表于 2012 年 8 月 13 日 由 惜分飞

看到dbsnake关于UPDATE GLOBAL_NAME为空之后的恢复写的不是很完整,自己通过试验和对bbed的研究,完善他的blog内容(泄露一点内部的东西)
模拟错误

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

SQL> select * from global_name ;

GLOBAL_NAME
--------------------------------------------------------------------
ORA11G

SQL> update global_name set global_name='';

1 row updated.

SQL> COMMIT;

Commit complete.

SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP
ORACLE instance started.

Total System Global Area  313860096 bytes
Fixed Size                  1344652 bytes
Variable Size             260049780 bytes
Database Buffers           46137344 bytes
Redo Buffers                6328320 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [18062], [], [], [], [], [], [], [],
[], [], [], []
Process ID: 28306
Session ID: 125 Serial number: 5

alert日志

Errors in file /u01/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_28306.trc  (incident=20556):
ORA-00600: internal error code, arguments: [18062], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/oracle/diag/rdbms/ora11g/ora11g/incident/incdir_20556/ora11g_ora_28306_i20556.trc
Wed Aug 08 23:21:48 2012
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file /u01/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_28306.trc:
ORA-00600: internal error code, arguments: [18062], [], [], [], [], [], [], [], [], [], [], []
Errors in file /u01/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_28306.trc:
ORA-00600: internal error code, arguments: [18062], [], [], [], [], [], [], [], [], [], [], []
Error 600 happened during db open, shutting down database
USER (ospid: 28306): terminating the instance due to error 600
Instance terminated by USER, pid = 28306
ORA-1092 signalled during: ALTER DATABASE OPEN...
opiodr aborting process unknown ospid (28306) as a result of ORA-1092
Wed Aug 08 23:21:48 2012
ORA-1092 : opitsk aborting process

找出global_name相关信息(另外库中)

SQL> SET LONG 1000
SQL> select dbms_metadata.get_ddl('VIEW','GLOBAL_NAME','SYS') from dual;

DBMS_METADATA.GET_DDL('VIEW','GLOBAL_NAME','SYS')
--------------------------------------------------------------------------------

  CREATE OR REPLACE FORCE VIEW "SYS"."GLOBAL_NAME" ("GLOBAL_NAME") AS
  select value$ from sys.props$ where name = 'GLOBAL_DB_NAME'

SQL> select * from props$ where value$='XIFENFEI';

NAME                           VALUE$
------------------------------ ----------------------------------------
COMMENT$
--------------------------------------------------------------------------
GLOBAL_DB_NAME                 XIFENFEI
Global database name

SQL> select dump('GLOBAL_DB_NAME','16') from dual;

DUMP('GLOBAL_DB_NAME','16')
--------------------------------------------------------
Typ=96 Len=14: 47,4c,4f,42,41,4c,5f,44,42,5f,4e,41,4d,45
--得出GLOBAL_DB_NAME存储字16进制字符串为:0e474c4f42414c5f44425f4e414d45

bbed操作

--通过第三方工具定位props$表中的GLOBAL_DB_NAME列在数据块0x00400321的31行上

[oracle@xifenfei ~]$ bbed listfile=listfile mode=edit password=blockedit

BBED: Release 2.0.0.0.0 - Limited Production on Thu Aug 9 00:26:12 2012

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

************* !!! For Oracle Internal Use only !!! ***************

BBED> info
 File#  Name                                                        Size(blks)
 -----  ----                                                        ----------
     1  /u01/oracle/oradata/ora11g/system01.dbf                              0
     2  /u01/oracle/oradata/ora11g/sysaux01.dbf                              0
     3  /u01/oracle/oradata/ora11g/undotbs01.dbf                             0
     4  /u01/oracle/oradata/ora11g/users01.dbf                               0

BBED> set block 801
        BLOCK#          801

BBED> map
 File: /u01/oracle/oradata/ora11g/system01.dbf (1)
 Block: 801                                   Dba:0x00400321
------------------------------------------------------------
 KTB Data Block (Table/Cluster)

 struct kcbh, 20 bytes                      @0       

 struct ktbbh, 72 bytes                     @20      

 struct kdbh, 14 bytes                      @92      

 struct kdbt[1], 4 bytes                    @106     

 sb2 kdbr[37]                               @110     

 ub1 freespace[5771]                        @184     

 ub1 rowdata[2233]                          @5955    

 ub4 tailchk                                @8188    

BBED>  p *kdbr[31]
rowdata[0]
----------
ub1 rowdata[0]                              @5955     0x2c

BBED> x /rccc
rowdata[0]                                  @5955    
----------
flag@5955: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@5956: 0x02
cols@5957:    3

col   0[14] @5958: GLOBAL_DB_NAME
col    1[0] @5973: *NULL*
col   2[20] @5974: Global database name

BBED> set count 64
        COUNT           64

BBED> f /x 0e474c4f
 File: /u01/oracle/oradata/ora11g/system01.dbf (1)
 Block: 801              Offsets: 5958 to 6021           Dba:0x00400321
------------------------------------------------------------------------
 0e474c4f 42414c5f 44425f4e 414d45ff 14476c6f 62616c20 64617461 62617365 
 206e616d 652c0003 0e474c4f 42414c5f 44425f4e 414d4506 4f524131 31471447 

 <32 bytes per line>

BBED> f
 File: /u01/oracle/oradata/ora11g/system01.dbf (1)
 Block: 801              Offsets: 5998 to 6061           Dba:0x00400321
------------------------------------------------------------------------
 0e474c4f 42414c5f 44425f4e 414d4506 4f524131 31471447 6c6f6261 6c206461 
 74616261 7365206e 616d652c 00030a44 4254494d 455a4f4e 45053030 3a30300c 

 <32 bytes per line>

BBED> f
 File: /u01/oracle/oradata/ora11g/system01.dbf (1)
 Block: 801              Offsets: 6460 to 6523           Dba:0x00400321
------------------------------------------------------------------------
 0e474c4f 42414c5f 44425f4e 414d4508 53454544 44415441 14476c6f 62616c20 
 64617461 62617365 206e616d 652c0003 114e4c53 5f524442 4d535f56 45525349 

 <32 bytes per line>

BBED> f
BBED-00212: search string not found

BBED> d /v offset 5958
 File: /u01/oracle/oradata/ora11g/system01.dbf (1)
 Block: 801     Offsets: 5958 to 6021  Dba:0x00400321
-------------------------------------------------------
 0e474c4f 42414c5f 44425f4e 414d45ff l .GLOBAL_DB_NAME.
 14476c6f 62616c20 64617461 62617365 l .Global database
 206e616d 652c0003 0e474c4f 42414c5f l  name,...GLOBAL_
 44425f4e 414d4506 4f524131 31471447 l DB_NAME.ORA11G.G

 <16 bytes per line>

BBED> d /v offset 5998
 File: /u01/oracle/oradata/ora11g/system01.dbf (1)
 Block: 801     Offsets: 5998 to 6061  Dba:0x00400321
-------------------------------------------------------
 0e474c4f 42414c5f 44425f4e 414d4506 l .GLOBAL_DB_NAME.
 4f524131 31471447 6c6f6261 6c206461 l ORA11G.Global da
 74616261 7365206e 616d652c 00030a44 l tabase name,...D
 4254494d 455a4f4e 45053030 3a30300c l BTIMEZONE.00:00.

 <16 bytes per line>

BBED> d /v offset 6460
 File: /u01/oracle/oradata/ora11g/system01.dbf (1)
 Block: 801     Offsets: 6460 to 6523  Dba:0x00400321
-------------------------------------------------------
 0e474c4f 42414c5f 44425f4e 414d4508 l .GLOBAL_DB_NAME.
 53454544 44415441 14476c6f 62616c20 l SEEDDATA.Global 
 64617461 62617365 206e616d 652c0003 l database name,..
 114e4c53 5f524442 4d535f56 45525349 l .NLS_RDBMS_VERSI

 <16 bytes per line>
--通过时上面的查找可以知道数据库对GLOBAL_DB_NAME有进行两次update操作
--GLOBAL_DB_NAME初始化值SEEDDATA,第一次更新为ORA11G,第二次更新为null

BBED> p  kdbr[31]
sb2 kdbr[31]                                @172      5863
--这里可以发现我们看到offset 5995开始有值,但是row directory却指向了5863

BBED> d offset 5863 count 128
 File: /u01/oracle/oradata/ora11g/system01.dbf (1)
 Block: 801              Offsets: 5863 to 5990           Dba:0x00400321
------------------------------------------------------------------------
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 2c02030e 
 474c4f42 414c5f44 425f4e41 4d45ff14 476c6f62 616c2064 61746162 61736520 

 <32 bytes per line>
--通过dump看到row directory指向的值和实际的数据相差(5995-5863),都是0

BBED> d /v offset 5900
 File: /u01/oracle/oradata/ora11g/system01.dbf (1)
 Block: 801     Offsets: 5900 to 6027  Dba:0x00400321
-------------------------------------------------------
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 00000000 00000000 00000000 l ................
 00000000 0000002c 02030e47 4c4f4241 l .......,...GLOBA
 4c5f4442 5f4e414d 45ff1447 6c6f6261 l L_DB_NAME..Globa
 6c206461 74616261 7365206e 616d652c l l database name,
 00030e47 4c4f4241 4c5f4442 5f4e414d l ...GLOBAL_DB_NAM
 45064f52 41313147 14476c6f 62616c20 l E.ORA11G.Global 

 <16 bytes per line>
--需要指定的新值前面也存在同样的0,所以模仿的处理方法,让row directory同样向前偏移92

BBED> m /x 0f17 offset 172
 File: /u01/oracle/oradata/ora11g/system01.dbf (1)
 Block: 801              Offsets:  172 to  299           Dba:0x00400321
------------------------------------------------------------------------
 0f176018 16189a17 5e173d17 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 

 <32 bytes per line>

BBED> p kdbr[31]
sb2 kdbr[31]                                @172      5903

BBED> p *kdbr[31]
rowdata[40]
-----------
ub1 rowdata[40]                             @5995     0x2c

BBED> x /rccc
rowdata[40]                                 @5995    
-----------
flag@5995: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@5996: 0x00
cols@5997:    3

col   0[14] @5998: GLOBAL_DB_NAME
col    1[6] @6013: ORA11G
col   2[20] @6020: Global database name

修改lock信息
BBED> m /x 02 offset 5996
 File: /u01/oracle/oradata/ora11g/system01.dbf (1)
 Block: 801              Offsets: 5996 to 6123           Dba:0x00400321
------------------------------------------------------------------------
 02030e47 4c4f4241 4c5f4442 5f4e414d 45064f52 41313147 14476c6f 62616c20 
 64617461 62617365 206e616d 652c0003 0a444254 494d455a 4f4e4505 30303a30 
 300c4442 2074696d 65207a6f 6e652c00 02174e4f 5f555345 5249445f 56455249 
 46494552 5f53414c 54203633 39364335 38414231 37414530 30374539 41373238 

 <32 bytes per line>

BBED> d offset 5955
 File: /u01/oracle/oradata/ora11g/system01.dbf (1)
 Block: 801              Offsets: 5955 to 6082           Dba:0x00400321
------------------------------------------------------------------------
 2c02030e 474c4f42 414c5f44 425f4e41 4d45ff14 476c6f62 616c2064 61746162 
 61736520 6e616d65 2c02030e 474c4f42 414c5f44 425f4e41 4d45064f 52413131 
 4714476c 6f62616c 20646174 61626173 65206e61 6d652c00 030a4442 54494d45 
 5a4f4e45 0530303a 30300c44 42207469 6d65207a 6f6e652c 0002174e 4f5f5553 

 <32 bytes per line>

BBED> m /x 2c00  
 File: /u01/oracle/oradata/ora11g/system01.dbf (1)
 Block: 801              Offsets: 5955 to 6082           Dba:0x00400321
------------------------------------------------------------------------
 2c00030e 474c4f42 414c5f44 425f4e41 4d45ff14 476c6f62 616c2064 61746162 
 61736520 6e616d65 2c02030e 474c4f42 414c5f44 425f4e41 4d45064f 52413131 
 4714476c 6f62616c 20646174 61626173 65206e61 6d652c00 030a4442 54494d45 
 5a4f4e45 0530303a 30300c44 42207469 6d65207a 6f6e652c 0002174e 4f5f5553 

 <32 bytes per line>

--验证块
BBED> sum apply
Check value for File 1, Block 801:
current = 0xe836, required = 0xe836

BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oracle/oradata/ora11g/system01.dbf
BLOCK = 801

Block Checking: DBA = 4195105, Block Type = KTB-managed data block
data header at 0xb677b25c
kdbchk: the amount of space used is not equal to block size
        used=2056 fsc=6 avsp=6040 dtl=8096
Block 801 failed with check code 6110

DBVERIFY - Verification complete

Total Blocks Examined         : 1
Total Blocks Processed (Data) : 1
Total Blocks Failing   (Data) : 1
Total Blocks Processed (Index): 0
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 0
Total Blocks Influx           : 0

--修改_ktbitfsc信息，让其通过块验证
BBED> p ktbbhitl
struct ktbbhitl[0], 24 bytes                @44      
   struct ktbitxid, 8 bytes                 @44      
      ub2 kxidusn                           @44       0x0004
      ub2 kxidslt                           @46       0x000e
      ub4 kxidsqn                           @48       0x000001d4
   struct ktbituba, 8 bytes                 @52      
      ub4 kubadba                           @52       0x00c00a93
      ub2 kubaseq                           @56       0x0083
      ub1 kubarec                           @58       0x33
   ub2 ktbitflg                             @60       0x8000 (KTBFCOM)
   union _ktbitun, 2 bytes                  @62      
      b2 _ktbitfsc                          @62       0
      ub2 _ktbitwrp                         @62       0x0000
   ub4 ktbitbas                             @64       0x000c78fe
struct ktbbhitl[1], 24 bytes                @68      
   struct ktbitxid, 8 bytes                 @68      
      ub2 kxidusn                           @68       0x000a
      ub2 kxidslt                           @70       0x0016
      ub4 kxidsqn                           @72       0x000001eb
   struct ktbituba, 8 bytes                 @76      
      ub4 kubadba                           @76       0x00c0015b
      ub2 kubaseq                           @80       0x008f
      ub1 kubarec                           @82       0x0d
   ub2 ktbitflg                             @84       0x0001 (NONE)
   union _ktbitun, 2 bytes                  @86      
      b2 _ktbitfsc                          @86       6
      ub2 _ktbitwrp                         @86       0x0006
   ub4 ktbitbas                             @88       0x00000000

BBED> m /x 00 offset 86
 File: /u01/oracle/oradata/ora11g/system01.dbf (1)
 Block: 801              Offsets:   86 to  213           Dba:0x00400321
------------------------------------------------------------------------
 00000000 00000001 25000600 5c00e716 98179e17 00002500 701f571e 92189c1e 
 101efb1d ffff981d 551d0f1d d91cb71c 941c731c 4a1c1a1c ef1bc51b 9c1b701b 
 471b191b d61a931a 3c1a101a ee19bc19 86194e19 0d190f17 60181618 9a175e17 
 3d170000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 

 <32 bytes per line>

BBED> sum apply
Check value for File 1, Block 801:
current = 0xe830, required = 0xe830

BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oracle/oradata/ora11g/system01.dbf
BLOCK = 801

Block Checking: DBA = 4195105, Block Type = KTB-managed data block
data header at 0xb677b25c
kdbchk: space available on commit is incorrect
        tosp=6046 fsc=0 stb=0 avsp=6040
Block 801 failed with check code 6111

DBVERIFY - Verification complete

Total Blocks Examined         : 1
Total Blocks Processed (Data) : 1
Total Blocks Failing   (Data) : 1
Total Blocks Processed (Index): 0
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 0
Total Blocks Influx           : 0

--修改kdbhtosp信息
BBED> p kdbh
struct kdbh, 14 bytes                       @92      
   ub1 kdbhflag                             @92       0x00 (NONE)
   b1 kdbhntab                              @93       1
   b2 kdbhnrow                              @94       37
   sb2 kdbhfrre                             @96       6
   sb2 kdbhfsbo                             @98       92
   sb2 kdbhfseo                             @100      5863
   b2 kdbhavsp                              @102      6040
   b2 kdbhtosp                              @104      6046

BBED> d offset 102
 File: /u01/oracle/oradata/ora11g/system01.dbf (1)
 Block: 801              Offsets:  102 to  229           Dba:0x00400321
------------------------------------------------------------------------
 98179e17 00002500 701f571e 92189c1e 101efb1d ffff981d 551d0f1d d91cb71c 
 941c731c 4a1c1a1c ef1bc51b 9c1b701b 471b191b d61a931a 3c1a101a ee19bc19 
 86194e19 0d190f17 60181618 9a175e17 3d170000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 

 <32 bytes per line>

BBED> d offset 104
 File: /u01/oracle/oradata/ora11g/system01.dbf (1)
 Block: 801              Offsets:  104 to  231           Dba:0x00400321
------------------------------------------------------------------------
 9e170000 2500701f 571e9218 9c1e101e fb1dffff 981d551d 0f1dd91c b71c941c 
 731c4a1c 1a1cef1b c51b9c1b 701b471b 191bd61a 931a3c1a 101aee19 bc198619 
 4e190d19 0f176018 16189a17 5e173d17 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 

 <32 bytes per line>

BBED> m /x 98
 File: /u01/oracle/oradata/ora11g/system01.dbf (1)
 Block: 801              Offsets:  104 to  231           Dba:0x00400321
------------------------------------------------------------------------
 98170000 2500701f 571e9218 9c1e101e fb1dffff 981d551d 0f1dd91c b71c941c 
 731c4a1c 1a1cef1b c51b9c1b 701b471b 191bd61a 931a3c1a 101aee19 bc198619 
 4e190d19 0f176018 16189a17 5e173d17 00000000 00000000 00000000 00000000 
 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 

 <32 bytes per line>

--至此修改row directory指针完成
BBED> sum apply
Check value for File 1, Block 801:
current = 0xe836, required = 0xe836

BBED> verify
DBVERIFY - Verification starting
FILE = /u01/oracle/oradata/ora11g/system01.dbf
BLOCK = 801


DBVERIFY - Verification complete

Total Blocks Examined         : 1
Total Blocks Processed (Data) : 1
Total Blocks Failing   (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 0
Total Blocks Influx           : 0

重新启动数据库

SQL> startup 
ORACLE instance started.

Total System Global Area  313860096 bytes
Fixed Size                  1344652 bytes
Variable Size             260049780 bytes
Database Buffers           46137344 bytes
Redo Buffers                6328320 bytes
Database mounted.
Database opened.
SQL> select * from global_name ;

GLOBAL_NAME
--------------------------------------------------------
ORA11G

SQL>
发表在 非常规恢复 | 标签为 , | 5 条评论

使用oradebug修改数据库scn

发表于 2012 年 8 月 11 日 由 惜分飞

闲着无事看到几篇文章介绍了使用oradebug修改数据库scn的案例,这里也做了两个测试,发现该功能确实很巧妙,通过修改内存中的scn值,然后写入控制文件和数据文件,实现修改scn的方法,不过同样该方法的危害性极大,这里仅供测试使用,生产环境切不可乱使用,可能引起很严重后果
数据库版本信息

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

SQL> select '惜分飞' XIFENFEI FROM DUAL;

XIFENF
------
惜分飞

在open库中修改scn

SQL> oradebug setmypid
Statement processed.

--查看当前scn
SQL>  oradebug DUMPvar SGA kcsgscn_
kcslf kcsgscn_ [20009228, 20009248) = 00000000 0007A09F 00000019 00000000 00000000 00000000 00000000 20009034
SQL>  select CHECKPOINT_CHANGE# a from v$datafile;

         A
----------
    499314
    499314
    499314
    499314

SQL> select dbms_flashback.get_system_change_number a from dual;

         A
----------
    499877

SQL> select to_number('7A09F','xxxxxxxxx') from dual;

TO_NUMBER('7A09F','XXXXXXXXX')
------------------------------
                        499871

--修改内存中scn值(十进制)
SQL>  oradebug poke 0x20009228 4 8
BEFORE: [20009228, 2000922C) = 00000000
AFTER:  [20009228, 2000922C) = 00000008
SQL> oradebug DUMPvar SGA kcsgscn_
kcslf kcsgscn_ [20009228, 20009248) = 00000008 0007A0D8 00000052 00000000 00000000 00000000 00000000 20009034

SQL> col a for 999999999999999
SQL> select dbms_flashback.get_system_change_number a from dual;

         A
------------
34360238301

SQL> select to_number('8','xx')*4294967296+to_number('0007A0D8','xxxxxxxx') a from dual;

               A
----------------
     34360238296

--做一个checkpoint为了内存中的scn值写入控制文件和数据文件
SQL>  alter system checkpoint;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  318767104 bytes
Fixed Size                  1267236 bytes
Variable Size              96471516 bytes
Database Buffers          213909504 bytes
Redo Buffers                7118848 bytes
Database mounted.
Database opened.

SQL> col a for 999999999999999
SQL> select CHECKPOINT_CHANGE# a from v$datafile;

               A
----------------
     34360238496
     34360238496
     34360238496
     34360238496

SQL> select CHECKPOINT_CHANGE# a from v$datafile_header;

               A
----------------
     34360238496
     34360238496
     34360238496
     34360238496

在mount库中修改scn

SQL> startup mount
ORACLE instance started.

Total System Global Area  318767104 bytes
Fixed Size                  1267236 bytes
Variable Size              96471516 bytes
Database Buffers          213909504 bytes
Redo Buffers                7118848 bytes
Database mounted.
SQL> oradebug setmypid
Statement processed.
--因为数据库是mount状态不能看到scn值
SQL>  oradebug DUMPvar SGA kcsgscn_
kcslf kcsgscn_ [20009228, 20009248) = 00000000 00000000 00000000 00000000 00000000 00000000 00000000 20009034
SQL>  col a for 999999999999999
SQL> select CHECKPOINT_CHANGE# a from v$datafile_header;

               A
----------------
     34360240739
     34360240739
     34360240739
     34360240739

--求出WRAP SCN值
SQL> select 34360240739/4294967296 from dual;

34360240739/4294967296
----------------------
            8.00011697

--修改内存中scn值(十六进制)
SQL> oradebug poke 0x20009228 4 0x0000000a
BEFORE: [20009228, 2000922C) = 00000000
AFTER:  [20009228, 2000922C) = 0000000A
SQL> oradebug DUMPvar SGA kcsgscn_
kcslf kcsgscn_ [20009228, 20009248) = 0000000A 00000000 00000000 00000000 00000000 00000000 00000000 20009034

SQL> alter database open;

Database altered.

SQL> select dbms_flashback.get_system_change_number a from dual
  2  ;

               A
----------------
     42949673074

--注意:使用此种方法修改BASE SCN如果不指定,会从0开始计数
SQL>  oradebug DUMPvar SGA kcsgscn_
kcslf kcsgscn_ [20009228, 20009248) = 0000000A 00000077 0000001C 00000000 00000000 00000000 00000000 20009034
SQL> select to_number('A','xx')*4294967296+to_number('00000077','xxxxxxxx') a from dual;

               A
----------------
     42949673079

SQL> alter system checkpoint;

System altered.

SQL> select CHECKPOINT_CHANGE# a from v$datafile_header;

               A
----------------
     42949673095
     42949673095
     42949673095
     42949673095

SQL> select CHECKPOINT_CHANGE# a from v$datafile;

               A
----------------
     42949673095
     42949673095
     42949673095
     42949673095

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  318767104 bytes
Fixed Size                  1267236 bytes
Variable Size              96471516 bytes
Database Buffers          213909504 bytes
Redo Buffers                7118848 bytes
Database mounted.
Database opened.
SQL> select CHECKPOINT_CHANGE# a from v$datafile_header;

               A
----------------
     42949673231
     42949673231
     42949673231
     42949673231

在oradebug推进scn的过程中，需要注意不同平台，不同位数的ORACLE数据库可能推进方式有一定的区别，操作前最好在系统平台位数上进行测试，否则有可能导致恢复后果更加麻烦

发表在 非常规恢复 | 标签为 , | 3 条评论

处理smon清理临时段导致数据库异常案例

发表于 2012 年 8 月 9 日 由 惜分飞

一个朋友的数据库在经过自己的千辛万苦终于open成功,但是几分钟就down掉,使得他想导出数据重建库的目标不能实现.让我帮忙处理
alert日志报ORA-00600[kafspa:columnBuffer1]

Wed Aug  8 10:55:31 2012
Completed: ALTER DATABASE OPEN
Wed Aug  8 10:55:41 2012
Errors in file /oracle/ora10/admin/ora10g/udump/ora10g_ora_12160.trc:
ORA-00600: internal error code, arguments: [kafspa:columnBuffer1], [28], [22], [], [], [], [], []
Wed Aug  8 10:55:47 2012
Non-fatal internal error happenned while SMON was doing temporary segment drop.
SMON encountered 2 out of maximum 100 non-fatal internal errors.
Wed Aug  8 10:55:47 2012
Errors in file /oracle/ora10/admin/ora10g/bdump/ora10g_smon_8941.trc:
ORA-00600: internal error code, arguments: [kafspa:columnBuffer1], [28], [22], [], [], [], [], []
Wed Aug  8 10:55:58 2012
Non-fatal internal error happenned while SMON was doing temporary segment drop.
SMON encountered 3 out of maximum 100 non-fatal internal errors.
Wed Aug  8 10:55:59 2012
Errors in file /oracle/ora10/admin/ora10g/bdump/ora10g_smon_8941.trc:
ORA-00600: internal error code, arguments: [kafspa:columnBuffer1], [28], [22], [], [], [], [], []
Wed Aug  8 10:56:10 2012
Non-fatal internal error happenned while SMON was doing temporary segment drop.
SMON encountered 4 out of maximum 100 non-fatal internal errors.
Wed Aug  8 10:56:11 2012
Errors in file /oracle/ora10/admin/ora10g/bdump/ora10g_smon_8941.trc:
ORA-00600: internal error code, arguments: [kafspa:columnBuffer1], [28], [22], [], [], [], [], []
Wed Aug  8 10:56:22 2012
Non-fatal internal error happenned while SMON was doing temporary segment drop.
SMON encountered 5 out of maximum 100 non-fatal internal errors.
Wed Aug  8 10:56:32 2012
Errors in file /oracle/ora10/admin/ora10g/bdump/ora10g_smon_8941.trc:
ORA-00600: internal error code, arguments: [kafspa:columnBuffer1], [28], [22], [], [], [], [], []
Wed Aug  8 10:56:43 2012
Non-fatal internal error happenned while SMON was doing temporary segment drop.
SMON encountered 6 out of maximum 100 non-fatal internal errors.
Wed Aug  8 10:56:53 2012
Errors in file /oracle/ora10/admin/ora10g/bdump/ora10g_smon_8941.trc:
ORA-00600: internal error code, arguments: [kafspa:columnBuffer1], [28], [22], [], [], [], [], []
Wed Aug  8 10:57:04 2012
Non-fatal internal error happenned while SMON was doing temporary segment drop.
SMON encountered 7 out of maximum 100 non-fatal internal errors.
Wed Aug  8 10:57:14 2012
Errors in file /oracle/ora10/admin/ora10g/bdump/ora10g_smon_8941.trc:
ORA-00600: internal error code, arguments: [kafspa:columnBuffer1], [28], [22], [], [], [], [], []
Wed Aug  8 10:57:25 2012
Non-fatal internal error happenned while SMON was doing temporary segment drop.
SMON encountered 8 out of maximum 100 non-fatal internal errors.
Wed Aug  8 10:57:35 2012
Errors in file /oracle/ora10/admin/ora10g/bdump/ora10g_smon_8941.trc:
ORA-00600: internal error code, arguments: [kafspa:columnBuffer1], [28], [22], [], [], [], [], []
Wed Aug  8 10:57:38 2012
Errors in file /oracle/ora10/admin/ora10g/bdump/ora10g_pmon_8856.trc:
ORA-00474: SMON process terminated with error
Wed Aug  8 10:57:38 2012
PMON: terminating instance due to error 474
Instance terminated by PMON, pid = 8856

这里出现ORA-00600[kafspa:columnBuffer1],一个未知的错误,但是根据相关的提示,可以大概猜出来是什么原因导致数据库异常

Non-fatal internal error happenned while SMON was doing temporary segment drop.

出现这个错误,使得我们想到一个smon的功能,清理临时段.该数据库down掉很可能和smon清理临时段的过程发生失败有关系

SMON encountered 8 out of maximum 100 non-fatal internal errors.

这个错误提示是因为smon内部最多允许发生100次错误,记录错误发生了8次,当然这次数据库down掉是smon还没有达到100次就直接abort掉

SQL> col name for a32
SQL> col value for a24
SQL> col description for a70
SQL> set linesize 150
SQL> select a.ksppinm name,b.ksppstvl value,a.ksppdesc description
  2    from x$ksppi a,x$ksppcv b
  3   where a.inst_id = USERENV ('Instance')
  4     and b.inst_id = USERENV ('Instance')
  5     and a.indx = b.indx
  6     and upper(a.ksppinm) LIKE upper('%&param%')
  7  order by name
  8  
SQL> /
Enter value for param: smon_internal_errlimit
old   6:    and upper(a.ksppinm) LIKE upper('%&param%')
new   6:    and upper(a.ksppinm) LIKE upper('%smon_internal_errlimit%')

NAME                             VALUE                    DESCRIPTION
-------------------------------- ------------------------ -----------------------------------
_smon_internal_errlimit          100                      limit of SMON internal errors

分析trace文件

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /oracle/ora10/product
System name:	Linux
Node name:	DBN-HLD-155-2
Release:	2.6.18-92.el5PAE
Version:	#1 SMP Tue Apr 29 13:31:02 EDT 2008
Machine:	i686
Instance name: ora10g
Redo thread mounted by this instance: 1
Oracle process number: 8
Unix process pid: 8941, image: oracle@DBN-HLD-155-2 (SMON)

*** SERVICE NAME:() 2012-08-08 10:55:20.208
*** SESSION ID:(274.1) 2012-08-08 10:55:20.208
*** 2012-08-08 10:55:20.208
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [25027], [0], [0], [], [], [], [], []
Current SQL statement for this session:
select file#, block#, ts# from seg$ where type# = 3

果然是smon在查询type#=3的时候发现异常,出现ORA-00600[25027]错误.通过对seg$相关视图分析,可以知道type#=3表示临时段,也就是说数据库smon在查询哪些segment是临时段的时候发生意外,而结合alert日志,完整的错误应该就是:数据库启动后,smon进程为了清理临时段,需要通过select file#, block#, ts# from seg$ where type# = 3查询临时段,但是在查询的时候发生错误.而这个错误累积几次导致数据库异常关闭.

解决问题

--修改两个参数
event='10061 trace name context forever, level 10'
_smon_internal_errlimit=1000000

--启动数据库
SQL> startup pfile='/tmp/pfile'
ORACLE instance started.

Total System Global Area 1577058304 bytes
Fixed Size                  1267716 bytes
Variable Size             385878012 bytes
Database Buffers         1174405120 bytes
Redo Buffers               15507456 bytes
Database mounted.
Database opened.

因为屏蔽了smon回收临时段,数据库未出现开始时错误，观察数据库几个小时,运行正常,到此基本上解决了此次异常,通过exp可以顺利导出数据然后导入到新库中.

导致该异常sql分析

SQL> select file#, block#, ts# from seg$ where type# = 3;
select file#, block#, ts# from seg$ where type# = 3
                               *
ERROR at line 1:
ORA-00600: internal error code, arguments: [25027], [0], [0], [], [], [], [],
[]


SQL> set autot trace exp
SQL> select file#, block#, ts# from seg$ where type# = 3;

Execution Plan
----------------------------------------------------------
Plan hash value: 1605285479

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    10 |   150 |   389   (1)| 00:00:05 |
|*  1 |  TABLE ACCESS FULL| SEG$ |    10 |   150 |   389   (1)| 00:00:05 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("TYPE#"=3)

SQL> select count(rowid) from seg$;

Execution Plan
----------------------------------------------------------
Plan hash value: 763549841

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    12 |   389   (1)| 00:00:05 |
|   1 |  SORT AGGREGATE    |      |     1 |    12 |            |          |
|   2 |   TABLE ACCESS FULL| SEG$ | 45682 |   535K|   389   (1)| 00:00:05 |
---------------------------------------------------------------------------

SQL> select /*+ full(t) */ count(*) from seg$;

Execution Plan
----------------------------------------------------------
Plan hash value: 763549841

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    29   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| SEG$ |  3223 |    29   (0)| 00:00:01 |
-------------------------------------------------------------------

SQL> select ts# from seg$;

Execution Plan
----------------------------------------------------------
Plan hash value: 1605285479

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  3223 |  9669 |    29   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| SEG$ |  3223 |  9669 |    29   (0)| 00:00:01 |
--------------------------------------------------------------------------

SQL> set autot off
SQL> select count(rowid) from seg$;                                                                                 
SQL> /

COUNT(ROWID)
------------
       45727

SQL> select /*+ full(t) */ count(*) from seg$;

  COUNT(*)
----------
     45727

SQL> select  ts# from seg$;
0
0
……
7
7

ORA-00600: internal error code, arguments: [25027], [0], [0], [], [], [], [],
[]

SQL> !dbv file='/oracle/ora10/oradata/ora10g/system01.dbf'

DBVERIFY: Release 10.2.0.4.0 - Production on Thu Aug 9 14:05:09 2012

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

DBVERIFY - Verification starting : FILE = /oracle/ora10/oradata/ora10g/system01.dbf
Block Checking: DBA = 4225673, Block Type = KTB-managed data block
data header at 0xb6d8225c
kdbchk: bad row offset slot 6 offs 3030 fseo 3752 dtl 8168 bhs 72
Page 31369 failed with check code 6135
Block Checking: DBA = 4236289, Block Type = KTB-managed data block
data header at 0xb6d7225c
kdbchk: tosp bad (-13399)
Page 41985 failed with check code 6127


DBVERIFY - Verification complete

Total Pages Examined         : 192000
Total Pages Processed (Data) : 47588
Total Pages Failing   (Data) : 2
Total Pages Processed (Index): 40929
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 1784
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 101699
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Highest block SCN            : 1775671440 (0.1775671440)

SQL> ANALYZE TABLE sys.SEG$ VALIDATE STRUCTURE CASCADE;
ANALYZE TABLE sys.SEG$ VALIDATE STRUCTURE CASCADE
*
ERROR at line 1:
ORA-01498: block check failure - see trace file

通过这里的分析大概可以确定是由于两块KTB-managed data block数据块异常,导致直接对seg$进行TABLE ACCESS FULL操作的时候发生异常.因为这个库已经破坏了数据一致性,先导出来数据,至于出现该错误的原因,后续继续关注分析

发表在 Oracle备份恢复 | 标签为 , | 一条评论