在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
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 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
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.
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/