一个朋友的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: {=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.
--然后重启节点
663
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
