OS：win2000 server DB:92010
现状：从RMAN全备份完成的那个时刻起到resetlogs之前这个时间段的归档全部丢失。所有数据文件丢失。
目的：将数据库恢复到RMAN全备份完成的那个时刻。
步骤：
1.从rman无法restore数据文件。虽然list backup可以显示备份集，但是已经没有办法再进行恢复了，错误为RMAN-06026
RMAN> restore database;
启动 restore 于 23-12月-08
使用通道 ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 12/23/2008 15:40:55
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 5 found to restore
RMAN-06023: no backup or copy of datafile 4 found to restore
RMAN-06023: no backup or copy of datafile 3 found to restore
RMAN-06023: no backup or copy of datafile 2 found to restore
RMAN-06023: no backup or copy of datafile 1 found to restore
2.用dbms_backup_restore提取备份集的数据文件。
SQL> DECLARE
2 devtype varchar2(256);
3 done boolean;
4 BEGIN
5 devtype:=sys.dbms_backup_restore.deviceAllocate (type=>'',ident=>'t1');
6 sys.dbms_backup_restore.restoreSetDatafile;
7 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>01,toname=>'E:\ORACLE\ORADATA\SJHTEST\SYSTEM01.DBF');
8 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>02,toname=>'E:\ORACLE\ORADATA\SJHTEST\UNDOTBS01.DBF');
9 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>03,toname=>'E:\ORACLE\ORADATA\SJHTEST\INDX01.DBF');
10 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>04,toname=>'E:\ORACLE\ORADATA\SJHTEST\TOOLS01.DBF');
11 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>05,toname=>'E:\ORACLE\ORADATA\SJHTEST\USERS01.DBF');
12 sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'E:\oracle\ora92\dbs\0ggmiabq_1_1',params=>null);
13 sys.dbms_backup_restore.deviceDeallocate;
14 END;
15 /
DECLARE
*
ERROR 位于第 1 行:
ORA-19624: ????????????
ORA-19505: ??????"E:\oracle\ora92\dbs\0ggmiabq_1_1" --备份集名称指错了。
ORA-27041: ??????
OSD-04002: N^7(4r?*ND<~
O/S-Error: (OS 2) O5M3UR2;5=V86(5DND<~!#
ORA-06512: ?"SYS.X$DBMS_BACKUP_RESTORE", line 1371
ORA-06512: ?line 12
SQL> DECLARE
2 devtype varchar2(256);
3 done boolean;
4 BEGIN
5 devtype:=sys.dbms_backup_restore.deviceAllocate (type=>'',ident=>'t1');
6 sys.dbms_backup_restore.restoreSetDatafile;
7 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>01,toname=>'E:\ORACLE\ORADATA\SJHTEST\SYSTEM01.DBF');
8 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>02,toname=>'E:\ORACLE\ORADATA\SJHTEST\UNDOTBS01.DBF');
9 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>03,toname=>'E:\ORACLE\ORADATA\SJHTEST\INDX01.DBF');
10 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>04,toname=>'E:\ORACLE\ORADATA\SJHTEST\TOOLS01.DBF');
11 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>05,toname=>'E:\ORACLE\ORADATA\SJHTEST\USERS01.DBF');
12 sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'E:\ORACLE\RMAN_BAKCUP\FULL03K2INAV_3_1',params=>null);
13 sys.dbms_backup_restore.deviceDeallocate;
14 END;
15 /
DECLARE
*
ERROR 位于第 1 行:
ORA-19568: ?????????
ORA-06512: ?"SYS.X$DBMS_BACKUP_RESTORE", line 140
ORA-06512: ?"SYS.X$DBMS_BACKUP_RESTORE", line 114
ORA-06512: ?line 5
--需要重启实例
SQL> shutdown abort
ORACLE 例程已经关闭。
SQL> startup nomount;
ORACLE 例程已经启动。
Total System Global Area 147921840 bytes
Fixed Size 453552 bytes
Variable Size 121634816 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
SQL> DECLARE
2 devtype varchar2(256);
3 done boolean;
4 BEGIN
5 devtype:=sys.dbms_backup_restore.deviceAllocate (type=>'',ident=>'t1');
6 sys.dbms_backup_restore.restoreSetDatafile;
7 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>01,toname=>'E:\ORACLE\ORADATA\SJHTEST\SYSTEM01.DBF');
8 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>02,toname=>'E:\ORACLE\ORADATA\SJHTEST\UNDOTBS01.DBF');
9 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>03,toname=>'E:\ORACLE\ORADATA\SJHTEST\INDX01.DBF');
10 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>04,toname=>'E:\ORACLE\ORADATA\SJHTEST\TOOLS01.DBF');
11 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>05,toname=>'E:\ORACLE\ORADATA\SJHTEST\USERS01.DBF');
12 sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'E:\ORACLE\RMAN_BAKCUP\FULL03K2INAV_3_1',params=>null);
13 sys.dbms_backup_restore.deviceDeallocate;
14 END;
15 /
PL/SQL 过程已成功完成。
SQL>
--数据文件从备份集提取成功。
3.实施不完全恢复失败
SQL> alter database mount;
数据库已更改。
SQL> recover database until cancel;
ORA-00283: ??????????
ORA-01190: ?????????1??????? RESETLOGS ??
ORA-01110: ???? 1: 'E:\ORACLE\ORADATA\SJHTEST\SYSTEM01.DBF'
SQL> recover database using backup controlfile until cancel;
ORA-00283: ??????????
ORA-01190: ?????????1??????? RESETLOGS ??
ORA-01110: ???? 1: 'E:\ORACLE\ORADATA\SJHTEST\SYSTEM01.DBF'
SQL> recover database until cancel using backup controlfile;
ORA-00283: ??????????
ORA-01190: ?????????1??????? RESETLOGS ??
ORA-01110: ???? 1: 'E:\ORACLE\ORADATA\SJHTEST\SYSTEM01.DBF'
--始终报错！现在的控制文件是resetlog之后的，没有损坏。
4.重建控制文件
利用以前trc重建控制文件。重建脚本recv_ct.sql如下：
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "SJHTEST" RESETLOGS ARCHIVELOG
-- SET STANDBY TO MAXIMIZE PERFORMANCE
MAXLOGFILES 5
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 226
LOGFILE
GROUP 1 'E:\ORACLE\ORADATA\SJHTEST\REDO01.LOG' SIZE 10M,
GROUP 2 'E:\ORACLE\ORADATA\SJHTEST\REDO02.LOG' SIZE 10M,
GROUP 3 'E:\ORACLE\ORADATA\SJHTEST\REDO03.LOG' SIZE 10M
-- STANDBY LOGFILE
DATAFILE
'E:\ORACLE\ORADATA\SJHTEST\SYSTEM01.DBF',
'E:\ORACLE\ORADATA\SJHTEST\UNDOTBS01.DBF',
'E:\ORACLE\ORADATA\SJHTEST\INDX01.DBF',
'E:\ORACLE\ORADATA\SJHTEST\TOOLS01.DBF',
'E:\ORACLE\ORADATA\SJHTEST\USERS01.DBF'
CHARACTER SET ZHS16GBK
;
--Recovery is required if any of the datafiles are restored backups,
RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE
--Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;
SQL> conn as sysdba
已连接到空闲例程。
SQL> @recv_ct.sql
ORACLE 例程已经启动。
Total System Global Area 147921840 bytes
Fixed Size 453552 bytes
Variable Size 121634816 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
控制文件已创建
ORA-00279: ?? 3746638 (? 11/27/2008 13:37:21 ??) ???? 1 ????
ORA-00289: ??: E:\ORACLE\ORADATA\SJHTEST\ARCHIVE\1_29.DBF
ORA-00280: ?? 3746638 ???? 1 ???? # 29 ???
ORA-00308: ???????? '--Database'
ORA-27041: ??????
OSD-04002: N^7(4r?*ND<~
O/S-Error: (OS 2) O5M3UR2;5=V86(5DND<~!#
ORA-00308: ???????? 'ALTER'
ORA-27041: ??????
OSD-04002: N^7(4r?*ND<~
O/S-Error: (OS 2) O5M3UR2;5=V86(5DND<~!#
指定日志: {=suggested | filename | AUTO | CANCEL}
CANCEL --手工输入auto，cancel都没用
ORA-01547: ??: RECOVER ??? OPEN RESETLOGS ???????
ORA-01152: ?? 1 ????????????
ORA-01110: ???? 1: 'E:\ORACLE\ORADATA\SJHTEST\SYSTEM01.DBF'
ORA-01112: ???????
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR 位于第 1 行:
ORA-01152: ?? 1 ????????????
ORA-01110: ???? 1: 'E:\ORACLE\ORADATA\SJHTEST\SYSTEM01.DBF'
--提示1_29.DBF这个归档需要恢复。看来数据文件之间也存在不一致了。否则resetlog是可以打开数据库的。在RMAN全备的时候用了plus archivelog，那么最后一个归档应该还在备份集里。有一点需要注意就是数据文件在恢复的时候SCN不能往回走（DB2好像可以）,所以在rman全备期间的那个archivelog很重要。来看看alert到底发生了什么事情：
Tue Dec 23 18:07:44 2008
Successful mount of redo thread 1, with mount id 1510436591.
Tue Dec 23 18:07:44 2008
Completed: CREATE CONTROLFILE REUSE DATABASE "SJHTEST" RESETL
Tue Dec 23 18:07:44 2008
ALTER DATABASE RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE
Media Recovery Start
Starting datafile 1 recovery in thread 1 sequence 32
Datafile 1: 'E:\ORACLE\ORADATA\SJHTEST\SYSTEM01.DBF'
Starting datafile 2 recovery in thread 1 sequence 32
Datafile 2: 'E:\ORACLE\ORADATA\SJHTEST\UNDOTBS01.DBF'
Starting datafile 3 recovery in thread 1 sequence 29
Datafile 3: 'E:\ORACLE\ORADATA\SJHTEST\INDX01.DBF'
Starting datafile 4 recovery in thread 1 sequence 32
Datafile 4: 'E:\ORACLE\ORADATA\SJHTEST\TOOLS01.DBF'
Starting datafile 5 recovery in thread 1 sequence 32
Datafile 5: 'E:\ORACLE\ORADATA\SJHTEST\USERS01.DBF'
Media Recovery Log
ORA-279 signalled during: ALTER DATABASE RECOVER DATABASE UNTIL CANCEL USIN...
Tue Dec 23 18:07:44 2008
ALTER DATABASE RECOVER LOGFILE '--Database'
Media Recovery Log --Database
Errors with log --Database.
ORA-308 signalled during: ALTER DATABASE RECOVER LOGFILE '--Database' ...
Tue Dec 23 18:07:44 2008
ALTER DATABASE RECOVER LOGFILE 'ALTER'
Media Recovery Log ALTER
Errors with log ALTER.
ORA-308 signalled during: ALTER DATABASE RECOVER LOGFILE 'ALTER' ...
从日志中看到INDX01.DBF这个数据文件需要从sequence 29这个日志开始恢复，大家一起恢复到32就可以了。32是备份期间的日志，在备份集里面。
5.从备份集提取所需要的归档日志
SQL> shutdown abort
ORACLE 例程已经关闭。
SQL> startup nomount
ORACLE 例程已经启动。
Total System Global Area 147921840 bytes
Fixed Size 453552 bytes
Variable Size 121634816 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
SQL> DECLARE
2 devtype varchar2(256);
3 done boolean;
4 BEGIN
5 devtype:=sys.dbms_backup_restore.deviceAllocate (type=>'',ident=>'t1');
6 sys.dbms_backup_restore.restoresetarchivedlog;
7 sys.dbms_backup_restore.restorearchivedlog(thread=>1,sequence=>29);
8 sys.dbms_backup_restore.restorearchivedlog(thread=>1,sequence=>30);
9 sys.dbms_backup_restore.restorearchivedlog(thread=>1,sequence=>31);
10 sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'E:\ORACLE\RMAN_BAKCUP\ARCHIVE_SJHTEST_20081218_2',params=>null);
11 sys.dbms_backup_restore.deviceDeallocate;
12 END;
13 /
PL/SQL 过程已成功完成。
SQL> DECLARE
2 devtype varchar2(256);
3 done boolean;
4 BEGIN
5 devtype:=sys.dbms_backup_restore.deviceAllocate (type=>'',ident=>'t1');
6 sys.dbms_backup_restore.restoresetarchivedlog;
7 sys.dbms_backup_restore.restorearchivedlog(thread=>1,sequence=>32);
8 sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'E:\ORACLE\RMAN_BAKCUP\ARCHIVE_SJHTEST_20081218_4',params=>null);
9 sys.dbms_backup_restore.deviceDeallocate;
10 END;
11 /
PL/SQL 过程已成功完成。
6.开始不完全恢复
SQL> recover database using backup controlfile until cancel;
ORA-01507: ??????
SQL> recover database until cancel;
ORA-01507: ??????
7.再次重建控制文件并恢复
SQL> shutdown abort
ORACLE 例程已经关闭。
SQL> @recv_ct.sql
ORACLE 例程已经启动。
Total System Global Area 147921840 bytes
Fixed Size 453552 bytes
Variable Size 121634816 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
控制文件已创建
ORA-00279: ?? 3746638 (? 11/27/2008 13:37:21 ??) ???? 1 ????
ORA-00289: ??: E:\ORACLE\ORADATA\SJHTEST\ARCHIVE\1_29.DBF
ORA-00280: ?? 3746638 ???? 1 ???? # 29 ???
ORA-00308: ???????? '--Database'
ORA-27041: ??????
OSD-04002: N^7(4r?*ND<~
O/S-Error: (OS 2) O5M3UR2;5=V86(5DND<~!#
ORA-00308: ???????? 'ALTER'
ORA-27041: ??????
OSD-04002: N^7(4r?*ND<~
O/S-Error: (OS 2) O5M3UR2;5=V86(5DND<~!#
指定日志: {=suggested | filename | AUTO | CANCEL}
auto --应用归档日志
ORA-00279: ?? 3969203 (? 12/17/2008 15:33:11 ??) ???? 1 ????
ORA-00289: ??: E:\ORACLE\ORADATA\SJHTEST\ARCHIVE\1_30.DBF
ORA-00280: ?? 3969203 ???? 1 ???? # 30 ???
ORA-00278: ??????????? 'E:\ORACLE\ORADATA\SJHTEST\ARCHIVE\1_29.DBF'
ORA-00279: ?? 3996093 (? 12/18/2008 14:23:40 ??) ???? 1 ????
ORA-00289: ??: E:\ORACLE\ORADATA\SJHTEST\ARCHIVE\1_31.DBF
ORA-00280: ?? 3996093 ???? 1 ???? # 31 ???
ORA-00278: ??????????? 'E:\ORACLE\ORADATA\SJHTEST\ARCHIVE\1_30.DBF'
ORA-00279: ?? 3996254 (? 12/18/2008 14:31:45 ??) ???? 1 ????
ORA-00289: ??: E:\ORACLE\ORADATA\SJHTEST\ARCHIVE\1_32.DBF
ORA-00280: ?? 3996254 ???? 1 ???? # 32 ???
ORA-00278: ??????????? 'E:\ORACLE\ORADATA\SJHTEST\ARCHIVE\1_31.DBF'
ORA-00279: ?? 3996281 (? 12/18/2008 14:32:46 ??) ???? 1 ????
ORA-00289: ??: E:\ORACLE\ORADATA\SJHTEST\ARCHIVE\1_33.DBF
ORA-00280: ?? 3996281 ???? 1 ???? # 33 ???
ORA-00278: ??????????? 'E:\ORACLE\ORADATA\SJHTEST\ARCHIVE\1_32.DBF'
ORA-00308: ???????? 'E:\ORACLE\ORADATA\SJHTEST\ARCHIVE\1_33.DBF'
ORA-27041: ??????
OSD-04002: N^7(4r?*ND<~
O/S-Error: (OS 2) O5M3UR2;5=V86(5DND<~!#
SQL> alter database open resetlogs;
数据库已更改。
SQL> select * from sjh.test1;
ID
----------
1
--到此数据不完全恢复完成。
