联系：手机/微信(+86 17813235971) QQ(107644445)
标题：使用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. --然后重启节点
使用Oracle Recovery Tools一键式解决给问题
加我QQ
