使用NBU进行某些数据文件的恢复

最新推荐文章于 2024-03-23 11:20:39 发布

civudgl81761

最新推荐文章于 2024-03-23 11:20:39 发布

阅读量539

点赞数

因为数据库的数据文件所在的磁盘发生了故障(突然间不可访问和不可见),重新启动操作系统时发现scsi阵列卡已经无法识别数据磁盘,数据磁盘的状态为foreign.
在BIOS中进行操作和设置,调整了数据磁盘的状态;然后进入操作系统可以正常识别数据磁盘,数据库也可以启动到open状态.
数据库启动后,发现有部分数据文件的状态为recover,需要进行数据库的恢复.
下面是数据库恢复的步骤:

1.恢复前状态:

SQL>select file#,name,status,checkpoint_change# from v$datafile

FILE# NAME STATUS CHECKPOINT_CHANGE#
---------- --------------------------------------------- --------------- ------------------
......
18 /oracle/oradata/gcwzdb/mm_basic10.dbf ONLINE 144032510
19 /oradata/gcwzdb/mm_basic11.dbf RECOVER 143638963
20 /oradata/gcwzdb/mm_basic12.dbf RECOVER 143663968
21 /oradata/gcwzdb/mm_basic13.dbf RECOVER 143663968
22 /oradata/gcwzdb/test03.dbf RECOVER 143663968

FILE# NAME STATUS CHECKPOINT_CHANGE#
---------- --------------------------------------------- --------------- ------------------
23 /oradata/gcwzdb/test04.dbf RECOVER 143663968
24 /oradata/gcwzdb/test05.dbf RECOVER 143663968
25 /oradata/gcwzdb/test06.dbf RECOVER 143663968
......
28 rows selected.

NOTE: 28个数据文件中有7个数据文件都处于RECOVER状态

2.关闭数据库,然后启动到mount状态,进行数据库恢复.
SQL>shutdown immediate;
SQL>start mount;

SQL>recover datafile 19;
ORA-00279: change 143638963 generated at 11/11/2011 18:40:54 needed for thread
1
ORA-00289: suggestion : /oradata/archivelog/1_8674_684702199.dbf
ORA-00280: change 143638963 for thread 1 is in sequence #8674
Specify log: {=suggested | filename | AUTO | CANCEL}

3.使用netbackup,从磁带库中恢复从1_8674_*.dbf开始的归档日志
run{
ALLOCATE CHANNEL ch00 TYPE 'SBT_TAPE';
restore archivelog from sequence 8673;
release channel ch00;}

allocated channel: ch00
channel ch00: sid=535 devtype=SBT_TAPE
channel ch00: Veritas NetBackup for Oracle - Release 6.5 (2007072323)

Starting restore at 18-NOV-11

channel ch00: starting archive log restore to default destination
channel ch00: restoring archive log
archive log thread=1 sequence=8673
channel ch00: restoring archive log
archive log thread=1 sequence=8674
channel ch00: restoring archive log
archive log thread=1 sequence=8675
channel ch00: restoring archive log
.....
channel ch00: starting archive log restore to default destination
channel ch00: restoring archive log
archive log thread=1 sequence=8701
channel ch00: restoring archive log
archive log thread=1 sequence=8702
channel ch00: reading from backup piece al_6963_1_767528670
channel ch00: restored backup piece 1
piece handle=al_6963_1_767528670 tag=TAG20111118T102430
channel ch00: restore complete, elapsed time: 00:00:45
Finished restore at 18-NOV-11

released channel: ch00

4.因为是多个文件需要recover,所以想通过整个库的recover来完成多个文件的恢复,结果失败.
SQL> recover database until cancel;
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01245: offline file 19 will be lost if RESETLOGS is done
ORA-01110: data file 19: '/oradata/gcwzdb/mm_basic11.dbf'

5.逐个恢复状态为recovery的数据文件.
SQL> recover datafile 19;
ORA-00279: change 143638963 generated at 11/11/2011 18:40:54 needed for thread
1
ORA-00289: suggestion : /oradata/archivelog/1_8674_684702199.dbf
ORA-00280: change 143638963 for thread 1 is in sequence #8674

Specify log: {=suggested | filename | AUTO | CANCEL}
AUTO
Log applied.
Media recovery complete.

单个文件recover完成后,状态由recover变成offline,而且此时的checkpoint_change#号没有变化(只有等到数据库open以后才会修改)
SQL>select file#,name,status,checkpoint_change# from v$datafile;

FILE# NAME STATUS CHECKPOINT_CHANGE#
---------- --------------------------------------------- --------------- ------------------
1 /oracle/oradata/gcwzdb/system01.dbf SYSTEM 144032510
......
17 /oracle/oradata/gcwzdb/mm_basic09.dbf ONLINE 144032510
18 /oracle/oradata/gcwzdb/mm_basic10.dbf ONLINE 144032510
19 /oradata/gcwzdb/mm_basic11.dbf OFFLINE 143660438
20 /oradata/gcwzdb/mm_basic12.dbf RECOVER 143663968
21 /oradata/gcwzdb/mm_basic13.dbf RECOVER 143663968
22 /oradata/gcwzdb/test03.dbf RECOVER 143663968

FILE# NAME STATUS CHECKPOINT_CHANGE#
---------- --------------------------------------------- --------------- ------------------
23 /oradata/gcwzdb/test04.dbf RECOVER 143663968
......

28 rows selected.

SQL> recover datafile 20;
ORA-00279: change 143638963 generated at 11/11/2011 18:40:54 needed for thread 1
ORA-00289: suggestion : /oradata/archivelog/1_8674_684702199.dbf
ORA-00280: change 143638963 for thread 1 is in sequence #8674

Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 143663968 generated at 11/11/2011 23:05:07 needed for thread 1
ORA-00289: suggestion : /oradata/archivelog/1_8675_684702199.dbf
ORA-00280: change 143663968 for thread 1 is in sequence #8675
ORA-00278: log file '/oradata/archivelog/1_8674_684702199.dbf' no longer needed for this recovery

Log applied.
Media recovery complete.
SQL> recover datafile 21;
ORA-00279: change 143638963 generated at 11/11/2011 18:40:54 needed for thread 1
ORA-00289: suggestion : /oradata/archivelog/1_8674_684702199.dbf
ORA-00280: change 143638963 for thread 1 is in sequence #8674

Log applied.
Media recovery complete.
......
SQL> recover datafile 25;
ORA-00279: change 143638963 generated at 11/11/2011 18:40:54 needed for thread 1
ORA-00289: suggestion : /oradata/archivelog/1_8674_684702199.dbf
ORA-00280: change 143638963 for thread 1 is in sequence #8674

Log applied.
Media recovery complete.

6. online所有状态为offline的数据文件
SQL> select file#,name,status,checkpoint_change# from v$datafile;

......
20 /oradata/gcwzdb/mm_basic12.dbf OFFLINE 143667621
21 /oradata/gcwzdb/mm_basic13.dbf OFFLINE 143667626
22 /oradata/gcwzdb/test03.dbf OFFLINE 143667629

FILE# NAME STATUS CHECKPOINT_CHANGE#
---------- --------------------------------------------- --------------- ------------------
23 /oradata/gcwzdb/test04.dbf OFFLINE 143667632
24 /oradata/gcwzdb/test05.dbf OFFLINE 143667635
25 /oradata/gcwzdb/test06.dbf OFFLINE 143667638
......
28 rows selected.

SQL> alter database datafile 19 online;
Database altered.

SQL> alter database datafile 20 online;

Database altered.

SQL> alter database datafile 21 online;

Database altered.

SQL> alter database datafile 22 online;

Database altered.

SQL> alter database datafile 23 online;

Database altered.

SQL> alter database datafile 24 online;

Database altered.

SQL> alter database datafile 25 online;

Database altered.

这些数据文件虽然online,但因为数据库处于mount状态,所以没有做checkpoint,还是自己恢复的时间点
SQL> select file#,name,status,checkpoint_change# from v$datafile;
......
18 /oracle/oradata/gcwzdb/mm_basic10.dbf ONLINE 144032510
19 /oradata/gcwzdb/mm_basic11.dbf ONLINE 143660438
20 /oradata/gcwzdb/mm_basic12.dbf ONLINE 143667621
21 /oradata/gcwzdb/mm_basic13.dbf ONLINE 143667626
22 /oradata/gcwzdb/test03.dbf ONLINE 143667629

FILE# NAME STATUS CHECKPOINT_CHANGE#
---------- --------------------------------------------- --------------- ------------------
23 /oradata/gcwzdb/test04.dbf ONLINE 143667632
24 /oradata/gcwzdb/test05.dbf ONLINE 143667635
25 /oradata/gcwzdb/test06.dbf ONLINE 143667638
26 /oradata/gcwzdb/mm_basic14.dbf ONLINE 144032510
......
28 rows selected.

7. 打开数据库(open db)
SQL> alter database open;
Database altered.

SQL> select file#,name,status,checkpoint_change# from v$datafile;

FILE# NAME STATUS CHECKPOINT_CHANGE#
---------- --------------------------------------------- --------------- ------------------
......
18 /oracle/oradata/gcwzdb/mm_basic10.dbf ONLINE 144032511
19 /oradata/gcwzdb/mm_basic11.dbf ONLINE 144032511
20 /oradata/gcwzdb/mm_basic12.dbf ONLINE 144032511
21 /oradata/gcwzdb/mm_basic13.dbf ONLINE 144032511
22 /oradata/gcwzdb/test03.dbf ONLINE 144032511

FILE# NAME STATUS CHECKPOINT_CHANGE#
---------- --------------------------------------------- --------------- ------------------
23 /oradata/gcwzdb/test04.dbf ONLINE 144032511
24 /oradata/gcwzdb/test05.dbf ONLINE 144032511
25 /oradata/gcwzdb/test06.dbf ONLINE 144032511
......

数据库open以后,所有数据文件的CHECKPOINT_CHANG

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23862439/viewspace-1058032/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23862439/viewspace-1058032/

civudgl81761

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
使用NBU进行某些数据文件的恢复

因为数据库的数据文件所在的磁盘发生了故障(突然间不可访问和不可见),重新启动操作系统时发现scsi阵列卡已经无法识别数据磁盘,数据磁盘的状态为foreign.在BIOS中进行操作和设置,调整了数据磁盘的状态;然后进入操作系统可以...
复制链接

扫一扫