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

来源：互联网发布：vb.net oracle 编辑：程序博客网时间：2024/06/05 09:53

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

SQL>selectname,file#,statusfromv$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
 
6rowsselected.
 
SQL>alterdatabase datafile 2 offline;
 
Databasealtered.
 
SQL> archive log list;
Databaselog mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence    14
Nextlog sequenceto archive   15
Currentlog sequence          15
SQL>altersystem switch logfile;
 
System altered.
 
SQL> /
 
System altered.
 
SQL> /
 
System altered.
 
SQL> /
 
System altered.
 
SQL> /
 
System altered.
 
SQL> archive log list;
Databaselog mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence    19
Nextlog sequenceto archive   19
Currentlog 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>alterdatabase datafile 2 online;
alterdatabase datafile 2 online
*
ERRORatline 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 at06/12/2012 08:20:10 needed forthread 1
ORA-00289: suggestion :
+DATA/xff/archivelog/2012_06_12/thread_1_seq_15.280.785752747
ORA-00280: change 1155352 forthread 1 isin sequence #15
 
 
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot openarchived log
'+DATA/xff/archivelog/2012_06_12/thread_1_seq_15.280.785752747'
ORA-17503: ksfdopn:2 Failed toopen 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 notexist
 
 
ORA-00308: cannot openarchived log
'+DATA/xff/archivelog/2012_06_12/thread_1_seq_15.280.785752747'
ORA-17503: ksfdopn:2 Failed toopen 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 notexist

准备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 filenumber=00002 name=+XIFENFEI/xff/datafile/sysaux.257.776961315
outputfilename=/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 filenumber=00004 name=+XIFENFEI/xff/datafile/user_dd.dbf
outputfilename=/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/orits 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>setfile 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>setfile 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>setcount 16
        COUNT           16
 
BBED> d file2 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 /x87a71100 file1 block 1 offset 484
BBED-00209: invalid number (87a71100)
 
 
BBED> m /x87a7 file1 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 file2 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 /xcda9d52e file1 block 1 offset 492
BBED-00209: invalid number (cda9d52e)
 
 
BBED> d file1 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 /xcda9 file1 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 file1 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 file2 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 /x86000000 file1 block 1 offset 140
BBED-00209: invalid number (86000000)
 
 
BBED> m /x8600 file1 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 file2 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 /x8500 file1 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>setfile 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>sumapply
Check value forFile 1, Block 1:
current = 0x48c4, required = 0x48c4

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

SQL>alterdatabase rename file '+XIFENFEI/xff/datafile/sysaux.257.776961315'to '/tmp/auxsys.dbf_rman';
 
Databasealtered.
 
SQL> recover databasedatafile 2 ;
ORA-00274: illegal recovery optionDATAFILE
 
 
SQL> recover databasedatafile 2;
ORA-00274: illegal recovery optionDATAFILE
 
 
SQL> recover datafile 2;
ORA-00283: recovery session canceled due toerrors
ORA-01122:databasefile 2 failed verification check
ORA-01110: data file 2: '/tmp/auxsys.dbf_rman'
ORA-01207: file ismore recent than control file - old control file

尝试重建控制文件

SQL>alterdatabase backup controlfile totrace as'/tmp/xifenfei.ctl';
 
Databasealtered.
 
SQL> shutdown immediate
Databaseclosed.
Databasedismounted.
ORACLE instance shut down.
SQL> STARTUP NOMOUNT
ORACLE instance started.
 
Total System GlobalArea  535662592 bytes
FixedSize                 1346140 bytes
VariableSize            411043236 bytes
DatabaseBuffers          117440512 bytes
Redo Buffers                5832704 bytes
SQL> @xifenfei_ctl
 
CREATECONTROLFILE REUSE DATABASE"XFF" NORESETLOGS  ARCHIVELOG
*
ERRORatline 1:
ORA-01503:CREATECONTROLFILE failed
ORA-12720: operation requires databaseis in EXCLUSIVE mode
 
--在rac中重建控制文件需要设置cluster_database=FALSE
 
SQL>altersystem set cluster_database=FALSEscope=spfile;
 
System altered.
 
SQL> shutdown immediate;
ORA-01507:databasenot mounted
 
 
ORACLE instance shut down.
SQL> STARTUP NOMOUNT
ORACLE instance started.
 
Total System GlobalArea  535662592 bytes
FixedSize                 1346140 bytes
VariableSize            411043236 bytes
DatabaseBuffers          117440512 bytes
Redo Buffers                5832704 bytes
SQL> @xifenfei_ctl
 
Control file created.

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

SQL> recover database;
Media recovery complete.
SQL>alterdatabase open;
 
Databasealtered.
 
SQL> col namefor a52
SQL>selectname,file#,statusfromv$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
 
6rowsselected.

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

SQL>alterdatabase datafile 2 offline;
 
Databasealtered.
 
RMAN> copy datafile 2 to'+XIFENFEI';
 
Starting backup at2012-06-12 10:55:42
using target databasecontrol file insteadof 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
outputfile 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 at2012-06-12 11:15:05
 
 
RMAN> switch datafile 2 tocopy;
 
datafile 2 switched todatafile copy "+XIFENFEI/xff/datafile/sysaux.257.785761227"
 
RMAN> recover datafile 2;
 
Starting recover at2012-06-12 11:30:32
using channel ORA_DISK_1
 
starting media recovery
media recovery complete, elapsed time: 00:01:30
 
Finished recover at2012-06-12 11:34:11
 
RMAN> sql 'alter database datafile 2 online';
 
sql statement: alterdatabase datafile 2 online

验证和收尾工作

SQL>selectname,file#,statusfromv$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>altersystem set cluster_database=truescope=spfile;
 
System altered.
 
--然后重启节点

0 0