使用bbed让rac中的sysaux数据文件online

一个朋友的11g rac库的sysaux表空间因某种原因缺少历史归档,导致无法正常online,是的数据库的很多功能受限.通过实现展示恢复过程.
模拟环境

SQL> select name ,file#,status from v$datafile;   NAME                                                      FILE# STATUS ---------------------------------------------------- ---------- ------- +XIFENFEI/xff/datafile/system.256.776961315                   1 SYSTEM +XIFENFEI/xff/datafile/sysaux.257.776961315                   2 ONLINE +XIFENFEI/xff/datafile/undotbs1.258.776961317                 3 ONLINE +XIFENFEI/xff/datafile/user_dd.dbf                            4 ONLINE +XIFENFEI/xff/datafile/undotbs2.264.776961693                 5 ONLINE +XIFENFEI/asm/datafile/xifenfei01.dbf.268.781967893           6 ONLINE   6 rows selected.   SQL> alter database datafile 2 offline;   Database altered.   SQL> archive log list; Database log mode              Archive Mode Automatic archival             Enabled Archive destination            USE_DB_RECOVERY_FILE_DEST Oldest online log sequence     14 Next log sequence to archive   15 Current log sequence           15 SQL> alter system switch logfile;   System altered.   SQL> /   System altered.   SQL> /   System altered.   SQL> /   System altered.   SQL> /   System altered.   SQL> archive log list; Database log mode              Archive Mode Automatic archival             Enabled Archive destination            USE_DB_RECOVERY_FILE_DEST Oldest online log sequence     19 Next log sequence to archive   19 Current log sequence           20   --删除部分归档日志 [grid@rac1 ~]$ asmcmd ASMCMD> ls DATA/ XIFENFEI/ ASMCMD> cd data ASMCMD> ls XFF/ rac-cluster/ ASMCMD> cd xff ASMCMD> ls ARCHIVELOG/ CONTROLFILE/ ONLINELOG/ ASMCMD> cd archivelog ASMCMD> ls 2012_03_03/ 2012_04_13/ 2012_04_30/ 2012_05_01/ 2012_05_24/ 2012_06_12/ ASMCMD> cd 2012_06_12 ASMCMD> ls thread_1_seq_15.280.785752747 thread_1_seq_16.281.785752845 thread_1_seq_17.282.785752929 thread_1_seq_18.283.785753043 thread_1_seq_19.284.785753115 ASMCMD> rm thread_1_seq_16.281.785752845 ASMCMD> rm thread_1_seq_15.280.785752747

尝试online 数据文件

SQL> alter database datafile 2 online; alter database datafile 2 online * ERROR at line 1: ORA-01113: file 2 needs media recovery ORA-01110: data file 2: '+XIFENFEI/xff/datafile/sysaux.257.776961315'     SQL> recover datafile 2; ORA-00279: change 1155352 generated at 06/12/2012 08:20:10 needed for thread 1 ORA-00289: suggestion : +DATA/xff/archivelog/2012_06_12/thread_1_seq_15.280.785752747 ORA-00280: change 1155352 for thread 1 is in sequence #15     Specify log: {<RET>=suggested | filename | AUTO | CANCEL} auto ORA-00308: cannot open archived log '+DATA/xff/archivelog/2012_06_12/thread_1_seq_15.280.785752747' ORA-17503: ksfdopn:2 Failed to open file +DATA/xff/archivelog/2012_06_12/thread_1_seq_15.280.785752747 ORA-15012: ASM file '+DATA/xff/archivelog/2012_06_12/thread_1_seq_15.280.785752747' does not exist     ORA-00308: cannot open archived log '+DATA/xff/archivelog/2012_06_12/thread_1_seq_15.280.785752747' ORA-17503: ksfdopn:2 Failed to open file +DATA/xff/archivelog/2012_06_12/thread_1_seq_15.280.785752747 ORA-15012: ASM file '+DATA/xff/archivelog/2012_06_12/thread_1_seq_15.280.785752747' does not exist

准备bbed修改数据文件
现在datafile 2不能恢复,我们需要修改的就是该datafile header 相关的scn等信息,另外拷贝一个数据文件出来做修改时候参考

RMAN> copy datafile 2 to  '/tmp/auxsys.dbf_rman' ;   Starting backup at 2012-06-12 08:59:07 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=20 instance=XFF1 device type =DISK channel ORA_DISK_1: starting datafile copy input datafile file number=00002 name=+XIFENFEI /xff/datafile/sysaux .257.776961315 output file name= /tmp/auxsys .dbf_rman tag=TAG20120612T090029 RECID=1 STAMP=785754322 channel ORA_DISK_1: datafile copy complete, elapsed time : 00:03:50 Finished backup at 2012-06-12 09:05:36   RMAN>  copy datafile 4 to '/tmp/user.dbf_rman' ;   Starting backup at 2012-06-12 09:09:28 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile copy input datafile file number=00004 name=+XIFENFEI /xff/datafile/user_dd .dbf output file name= /tmp/user .dbf_rman tag=TAG20120612T090932 RECID=2 STAMP=785754582 channel ORA_DISK_1: datafile copy complete, elapsed time : 00:00:15 Finished backup at 2012-06-12 09:09:48

bbed修改datafile header

[oracle@rac1 tmp]$ bbed password=blockedit listfile= /tmp/o_bbed  mode=edit   BBED: Release 2.0.0.0.0 - Limited Production on Tue Jun 12 09:37:30 2012   Copyright (c) 1982, 2011, Oracle and /or its affiliates.  All rights reserved.   ************* !!! For Oracle Internal Use only !!! ***************   BBED> info   File #  Name                                                        Size(blks)   -----  ----                                                        ----------       1  /tmp/auxsys .dbf_rman                                                 0       2  /tmp/user .dbf_rman                                                   0   BBED> set file 2 block 1          FILE #           2          BLOCK #          1     BBED> p kcvfhckp struct kcvfhckp, 36 bytes                   @484         struct kcvcpscn, 8 bytes                 @484            ub4 kscnbas                           @484      0x0011a787        ub2 kscnwrp                           @488      0x0000     ub4 kcvcptim                             @492      0x2ed5a9cd     ub2 kcvcpthr                             @496      0x0001     union u, 12 bytes                        @500            struct kcvcprba, 12 bytes             @500               ub4 kcrbaseq                       @500      0x00000014           ub4 kcrbabno                       @504      0x000000c5           ub2 kcrbabof                       @508      0x0010     ub1 kcvcpetb[0]                          @512      0x02     ub1 kcvcpetb[1]                          @513      0x00     ub1 kcvcpetb[2]                          @514      0x00     ub1 kcvcpetb[3]                          @515      0x00     ub1 kcvcpetb[4]                          @516      0x00     ub1 kcvcpetb[5]                          @517      0x00     ub1 kcvcpetb[6]                          @518      0x00     ub1 kcvcpetb[7]                          @519      0x00   BBED> p kcvfhcpc                             ub4 kcvfhcpc                                @140      0x00000086   BBED> p kcvfhccc                             ub4 kcvfhccc                                @148      0x00000085   BBED> set file 1 block 1          FILE #           1          BLOCK #          1   BBED> p kcvfhckp struct kcvfhckp, 36 bytes                   @484         struct kcvcpscn, 8 bytes                 @484            ub4 kscnbas                           @484      0x0011a118        ub2 kscnwrp                           @488      0x0000     ub4 kcvcptim                             @492      0x2ed59e3a     ub2 kcvcpthr                             @496      0x0001     union u, 12 bytes                        @500            struct kcvcprba, 12 bytes             @500               ub4 kcrbaseq                       @500      0x0000000f           ub4 kcrbabno                       @504      0x0000c4ed           ub2 kcrbabof                       @508      0x0010     ub1 kcvcpetb[0]                          @512      0x02     ub1 kcvcpetb[1]                          @513      0x00     ub1 kcvcpetb[2]                          @514      0x00     ub1 kcvcpetb[3]                          @515      0x00     ub1 kcvcpetb[4]                          @516      0x00     ub1 kcvcpetb[5]                          @517      0x00     ub1 kcvcpetb[6]                          @518      0x00     ub1 kcvcpetb[7]                          @519      0x00   BBED> p kcvfhcpc ub4 kcvfhcpc                                @140      0x00000079   BBED>  p kcvfhccc ub4 kcvfhccc                                @148      0x00000078   /* 确定需要修改项kscnbas /kcvcptim/kcvfhcpc/kcvfhccc 的相关信息 */   BBED> set count 16          COUNT           16   BBED> d file 2 block 1 offset 484                              File: /tmp/user .dbf_rman (2)   Block: 1                Offsets:  484 to  499           Dba:0x00800001 ------------------------------------------------------------------------   87a71100 00001000 cda9d52e 01000000     <32 bytes per line>   BBED> m /x 87a71100 file 1 block 1 offset 484 BBED-00209: invalid number (87a71100)     BBED> m /x 87a7 file 1 block 1 offset 484   File: /tmp/auxsys .dbf_rman (1)   Block: 1                Offsets:  484 to  499           Dba:0x00400001 ------------------------------------------------------------------------   87a71100 00000000 3a9ed52e 01000000     <32 bytes per line>   BBED> d file 2 block 1 offset 492   File: /tmp/user .dbf_rman (2)   Block: 1                Offsets:  492 to  507           Dba:0x00800001 ------------------------------------------------------------------------   cda9d52e 01000000 14000000 c5000000     <32 bytes per line>   BBED> m /x cda9d52e file 1 block 1 offset 492 BBED-00209: invalid number (cda9d52e)     BBED> d file 1 block 1 offset 492   File: /tmp/auxsys .dbf_rman (1)   Block: 1                Offsets:  492 to  507           Dba:0x00400001 ------------------------------------------------------------------------   3a9ed52e 01000000 0f000000 edc40000     <32 bytes per line>   BBED> m /x cda9 file 1 block 1 offset 492   File: /tmp/auxsys .dbf_rman (1)   Block: 1                Offsets:  492 to  507           Dba:0x00400001 ------------------------------------------------------------------------   cda9d52e 01000000 0f000000 edc40000     <32 bytes per line>   BBED> d file 1 block 1 offset 140   File: /tmp/auxsys .dbf_rman (1)   Block: 1                Offsets:  140 to  155           Dba:0x00400001 ------------------------------------------------------------------------   79000000 2970bc2e 78000000 00000000     <32 bytes per line>   BBED> d file 2 block 1 offset 140   File: /tmp/user .dbf_rman (2)   Block: 1                Offsets:  140 to  155           Dba:0x00800001 ------------------------------------------------------------------------   86000000 2970bc2e 85000000 00000000     <32 bytes per line>   BBED> m /x 86000000 file 1 block 1 offset 140 BBED-00209: invalid number (86000000)     BBED> m /x 8600 file 1 block 1 offset 140   File: /tmp/auxsys .dbf_rman (1)   Block: 1                Offsets:  140 to  155           Dba:0x00400001 ------------------------------------------------------------------------   86000000 2970bc2e 78000000 00000000     <32 bytes per line>   BBED> d file 2 block 1 offset 148   File: /tmp/user .dbf_rman (2)   Block: 1                Offsets:  148 to  163           Dba:0x00800001 ------------------------------------------------------------------------   85000000 00000000 00000000 00000000     <32 bytes per line>   BBED> m /x 8500 file 1 block 1 offset 148   File: /tmp/auxsys .dbf_rman (1)   Block: 1                Offsets:  148 to  163           Dba:0x00400001 ------------------------------------------------------------------------   85000000 00000000 00000000 00000000     <32 bytes per line>   BBED> set file 1 block 1          FILE #           1          BLOCK #          1   BBED> p kcvfhckp struct kcvfhckp, 36 bytes                   @484         struct kcvcpscn, 8 bytes                 @484            ub4 kscnbas                           @484      0x0011a787        ub2 kscnwrp                           @488      0x0000     ub4 kcvcptim                             @492      0x2ed5a9cd     ub2 kcvcpthr                             @496      0x0001     union u, 12 bytes                        @500            struct kcvcprba, 12 bytes             @500               ub4 kcrbaseq                       @500      0x0000000f           ub4 kcrbabno                       @504      0x0000c4ed           ub2 kcrbabof                       @508      0x0010     ub1 kcvcpetb[0]                          @512      0x02     ub1 kcvcpetb[1]                          @513      0x00     ub1 kcvcpetb[2]                          @514      0x00     ub1 kcvcpetb[3]                          @515      0x00     ub1 kcvcpetb[4]                          @516      0x00     ub1 kcvcpetb[5]                          @517      0x00     ub1 kcvcpetb[6]                          @518      0x00     ub1 kcvcpetb[7]                          @519      0x00   BBED> p kcvfhcpc ub4 kcvfhcpc                                @140      0x00000086   BBED>  p kcvfhccc ub4 kcvfhccc                                @148      0x00000085   BBED> sum apply Check value for File 1, Block 1: current = 0x48c4, required = 0x48c4

使用修改后数据文件尝试online

SQL> alter database rename file '+XIFENFEI/xff/datafile/sysaux.257.776961315' to '/tmp/auxsys.dbf_rman' ;   Database altered.   SQL> recover database datafile 2 ; ORA-00274: illegal recovery option DATAFILE     SQL> recover database datafile 2; ORA-00274: illegal recovery option DATAFILE     SQL> recover datafile 2; ORA-00283: recovery session canceled due to errors ORA-01122: database file 2 failed verification check ORA-01110: data file 2: '/tmp/auxsys.dbf_rman' ORA-01207: file is more recent than control file - old control file

尝试重建控制文件

SQL> alter database backup controlfile to trace as '/tmp/xifenfei.ctl' ;   Database altered.   SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> STARTUP NOMOUNT ORACLE instance started.   Total System Global Area  535662592 bytes Fixed Size                  1346140 bytes Variable Size             411043236 bytes Database Buffers          117440512 bytes Redo Buffers                5832704 bytes SQL> @xifenfei_ctl   CREATE CONTROLFILE REUSE DATABASE "XFF" NORESETLOGS  ARCHIVELOG * ERROR at line 1: ORA-01503: CREATE CONTROLFILE failed ORA-12720: operation requires database is in EXCLUSIVE mode   --在rac中重建控制文件需要设置cluster_database=FALSE   SQL> alter system set  cluster_database= FALSE scope=spfile;   System altered.   SQL> shutdown immediate; ORA-01507: database not mounted     ORACLE instance shut down. SQL> STARTUP NOMOUNT ORACLE instance started.   Total System Global Area  535662592 bytes Fixed Size                  1346140 bytes Variable Size             411043236 bytes Database Buffers          117440512 bytes Redo Buffers                5832704 bytes SQL> @xifenfei_ctl   Control file created.

online数据文件
重建控制文件恢复数据库之后 datafile 2自动online成功,省去了手工处理麻烦,如果没有自动online,请手工处理

SQL> recover database ; Media recovery complete. SQL> alter database open ;   Database altered.   SQL> col name for a52 SQL> select name ,file#,status from v$datafile;   NAME                                                      FILE# STATUS ---------------------------------------------------- ---------- ------- +XIFENFEI/xff/datafile/system.256.776961315                   1 SYSTEM /tmp/auxsys.dbf_rman                                          2 ONLINE +XIFENFEI/xff/datafile/undotbs1.258.776961317                 3 ONLINE +XIFENFEI/xff/datafile/user_dd.dbf                            4 ONLINE +XIFENFEI/xff/datafile/undotbs2.264.776961693                 5 ONLINE +XIFENFEI/asm/datafile/xifenfei01.dbf.268.781967893           6 ONLINE   6 rows selected.

文件系统中的datafile 2 恢复到asm中

SQL> alter database datafile 2 offline;   Database altered.   RMAN> copy datafile 2 to '+XIFENFEI' ;   Starting backup at 2012-06-12 10:55:42 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=21 device type=DISK channel ORA_DISK_1: starting datafile copy input datafile file number=00002 name =/tmp/auxsys.dbf_rman output file name =+XIFENFEI/xff/datafile/sysaux.257.785761227 tag=TAG20120612T105800 RECID=1 STAMP=785762097 channel ORA_DISK_1: datafile copy complete, elapsed time : 00:16:24 Finished backup at 2012-06-12 11:15:05     RMAN> switch datafile 2 to copy;   datafile 2 switched to datafile copy "+XIFENFEI/xff/datafile/sysaux.257.785761227"   RMAN> recover datafile 2;   Starting recover at 2012-06-12 11:30:32 using channel ORA_DISK_1   starting media recovery media recovery complete, elapsed time : 00:01:30   Finished recover at 2012-06-12 11:34:11   RMAN> sql 'alter database datafile 2 online' ;   sql statement: alter database datafile 2 online

验证和收尾工作

SQL> select name ,file#,status from v$datafile;   NAME                                                      FILE# STATUS ---------------------------------------------------- ---------- ------- +XIFENFEI/xff/datafile/system.256.776961315                   1 SYSTEM +XIFENFEI/xff/datafile/sysaux.257.785761227                   2 ONLINE +XIFENFEI/xff/datafile/undotbs1.258.776961317                 3 ONLINE +XIFENFEI/xff/datafile/user_dd.dbf                            4 ONLINE +XIFENFEI/xff/datafile/undotbs2.264.776961693                 5 ONLINE +XIFENFEI/asm/datafile/xifenfei01.dbf.268.781967893           6 ONLINE     SQL> alter system set  cluster_database= true scope=spfile;   System altered.   --然后重启节点