做RMAN恢复测试时遇到的一个问题:
1、RMAN全备份数据库（数据库为ARCHIVE LOG模式）
2、在数据库上创建新的表空间
3、备份所有归档日志
4、关闭数据库并将数据库里面的控制文件、数据文件全部删除
5、用RMAN进行恢复
RMAN>set dbid ...
RMAN>startup nomount
RMAN>restore controlfile from autobackup;
RMAN>alter database mount;
RMAN>restore database-------这一步出错，提示无法找到新创建表空间的数据文件的备份
这是否说明上面第二步中创建表空间后应使用RMAN备份该表空间？如果没有备份的话，数据库真的不能恢复吗？应该还是能够恢复的吧，但具体应该怎么做呢？
1. ------设置归档模式------------
SQL> alter system set log_archive_dest_1='location=C:\SOFTWARE\ORACLE\oradata\zhs16gbk\archive';
System altered.
SQL> alter system set log_archive_start=true scope=spfile;
System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 101784276 bytes
Fixed Size 453332 bytes
Variable Size 75497472 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination C:\SOFTWARE\ORACLE\oradata\zhs16gbk\archive
Oldest online log sequence 41
Next log sequence to archive 42
Current log sequence 42
SQL> alter database open;
Database altered.
SQL>
2.------备份数据库---------------------
C:\Documents and Settings\jason>rman target sys/oracle@zhs16gbk catalog rman/rman@rmandb
Recovery Manager: Release 9.2.0.1.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database: ZHS16GBK (DBID=3761021478)
connected to recovery catalog database
RMAN> run {
2> allocate channel c1 type disk format 'd:\dbbak\bk_%s_%p_%t';
3> backup database ;
4> backup format 'd:\dbbak\ck_%s_%p_%t' current controlfile;
5> sql 'alter system archive log current';
6> backup format 'd:\dbbak\al_%s_%p_%t' archivelog all delete input;
7> release channel c1;
8> }
allocated channel: c1
channel c1: sid=15 devtype=DISK
Starting backup at 23-MAY-07
channel c1: starting full datafile backupset
channel c1: specifying datafile(s) in backupset
including current SPFILE in backupset
including current controlfile in backupset
input datafile fno=00001 name=C:\SOFTWARE\ORACLE\ORADATA\ZHS16GBK\SYSTEM01.DBF
input datafile fno=00002 name=C:\SOFTWARE\ORACLE\ORADATA\ZHS16GBK\UNDOTBS01.DBF
input datafile fno=00003 name=C:\SOFTWARE\ORACLE\ORADATA\ZHS16GBK\INDX01.DBF
input datafile fno=00005 name=C:\SOFTWARE\ORACLE\ORADATA\ZHS16GBK\USERS01.DBF
input datafile fno=00004 name=C:\SOFTWARE\ORACLE\ORADATA\ZHS16GBK\TOOLS01.DBF
channel c1: starting piece 1 at 23-MAY-07
channel c1: finished piece 1 at 23-MAY-07
piece handle=D:\DBBAK\BK_1_1_623326719 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:26
Finished backup at 23-MAY-07
Starting backup at 23-MAY-07
channel c1: starting full datafile backupset
channel c1: specifying datafile(s) in backupset
including current controlfile in backupset
channel c1: starting piece 1 at 23-MAY-07
channel c1: finished piece 1 at 23-MAY-07
piece handle=D:\DBBAK\CK_2_1_623326746 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 23-MAY-07
sql statement: alter system archive log current
Starting backup at 23-MAY-07
current log archived
channel c1: starting archive log backupset
channel c1: specifying archive log(s) in backup set
input archive log thread=1 sequence=42 recid=1 stamp=623326748
input archive log thread=1 sequence=43 recid=2 stamp=623326755
channel c1: starting piece 1 at 23-MAY-07
channel c1: finished piece 1 at 23-MAY-07
piece handle=D:\DBBAK\AL_3_1_623326755 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:02
channel c1: deleting archive log(s)
archive log filename=C:\SOFTWARE\ORACLE\ORADATA\ZHS16GBK\ARCHIVE\ARC00042.001 recid=1 stamp=623326748
archive log filename=C:\SOFTWARE\ORACLE\ORADATA\ZHS16GBK\ARCHIVE\ARC00043.001 recid=2 stamp=623326755
Finished backup at 23-MAY-07
released channel: c1
RMAN>
3.---------创建新的表空间--------------
SQL> create tablespace test_tablespace datafile 'c:\software\oracle\oradata\zhs16gbk\test01.dbf' size 10M;
Tablespace created.
SQL>
4.---------备份日志-------------------
RMAN> run {
2> sql 'alter system archive log current';
3> backup format 'd:\dbbak\al_%s_%p_%t' archivelog all delete input;
4> }
starting full resync of recovery catalog
full resync complete
sql statement: alter system archive log current
Starting backup at 23-MAY-07
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=15 devtype=DISK
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=44 recid=3 stamp=623326979
input archive log thread=1 sequence=45 recid=4 stamp=623326982
channel ORA_DISK_1: starting piece 1 at 23-MAY-07
channel ORA_DISK_1: finished piece 1 at 23-MAY-07
piece handle=D:\DBBAK\AL_4_1_623326982 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
channel ORA_DISK_1: deleting archive log(s)
archive log filename=C:\SOFTWARE\ORACLE\ORADATA\ZHS16GBK\ARCHIVE\ARC00044.001 recid=3 stamp=623326979
archive log filename=C:\SOFTWARE\ORACLE\ORADATA\ZHS16GBK\ARCHIVE\ARC00045.001 recid=4 stamp=623326982
Finished backup at 23-MAY-07
RMAN>
5.---------关闭数据库,删除控制文件和数据文件------------------
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
移动数据文件\控制文件
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 101784276 bytes
Fixed Size 453332 bytes
Variable Size 75497472 bytes
Database Buffers 25165824 bytes
Redo Buffers 667648 bytes
SQL>
6.----------RMAN恢复尝试---------------
C:\Documents and Settings\jason>rman target sys/oracle@zhs16gbk catalog rman/rman@rmandb
Recovery Manager: Release 9.2.0.1.0 - Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database: zhs16gbk (not mounted)
connected to recovery catalog database
RMAN> restore controlfile from autobackup;
Starting restore at 23-MAY-07
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=14 devtype=DISK
channel ORA_DISK_1: looking for autobackup on day: 20070523
channel ORA_DISK_1: looking for autobackup on day: 20070522
channel ORA_DISK_1: looking for autobackup on day: 20070521
channel ORA_DISK_1: looking for autobackup on day: 20070520
channel ORA_DISK_1: looking for autobackup on day: 20070519
channel ORA_DISK_1: looking for autobackup on day: 20070518
channel ORA_DISK_1: looking for autobackup on day: 20070517
channel ORA_DISK_1: no autobackup in 7 days found
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 05/23/2007 10:26:33
RMAN-06172: no autobackup found or specified handle is not a valid copy or piece
这时发现不能恢复控制文件, 需要从指定的备份中恢复控制文件
RMAN> restore controlfile from 'd:\dbbak\CK_2_1_623326746';
Starting restore at 23-MAY-07
using channel ORA_DISK_1
channel ORA_DISK_1: restoring controlfile
channel ORA_DISK_1: restore complete
replicating controlfile
input filename=C:\SOFTWARE\ORACLE\ORADATA\ZHS16GBK\CONTROL01.CTL
output filename=C:\SOFTWARE\ORACLE\ORADATA\ZHS16GBK\CONTROL02.CTL
Finished restore at 23-MAY-07
RMAN>
=============开始尝试解决===================================
启动到mount状态:
SQL> alter database mount;
Database altered.
SQL>
RMAN> restore database ;
Starting restore at 23-MAY-07
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=12 devtype=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 05/23/2007 10:29:54
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 6 found to restore
RMAN>
不行, 先查看备份
================================================================
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1605 Full 198M DISK 00:00:24 23-MAY-07
BP Key: 1606 Status: AVAILABLE Tag: TAG20070523T101839
Piece Name: D:\DBBAK\BK_1_1_623326719
SPFILE Included: Modification time: 23-MAY-07
List of Datafiles in backup set 1605
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 879808 23-MAY-07 C:\SOFTWARE\ORACLE\ORADATA\ZHS16GBK\SYSTEM01.DBF
2 Full 879808 23-MAY-07 C:\SOFTWARE\ORACLE\ORADATA\ZHS16GBK\UNDOTBS01.DBF
3 Full 879808 23-MAY-07 C:\SOFTWARE\ORACLE\ORADATA\ZHS16GBK\INDX01.DBF
4 Full 879808 23-MAY-07 C:\SOFTWARE\ORACLE\ORADATA\ZHS16GBK\TOOLS01.DBF
5 Full 879808 23-MAY-07 C:\SOFTWARE\ORACLE\ORADATA\ZHS16GBK\USERS01.DBF
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1615 Full 1M DISK 00:00:00 23-MAY-07
BP Key: 1616 Status: AVAILABLE Tag: TAG20070523T101906
Piece Name: D:\DBBAK\CK_2_1_623326746
Controlfile Included: Ckp SCN: 879817 Ckp time: 23-MAY-07
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
1625 557K DISK 00:00:01 23-MAY-07
BP Key: 1626 Status: AVAILABLE Tag: TAG20070523T101915
Piece Name: D:\DBBAK\AL_3_1_623326755
List of Archived Logs in backup set 1625
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 42 877727 17-MAY-07 879821 23-MAY-07
1 43 879821 23-MAY-07 879842 23-MAY-07
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
1637 60K DISK 00:00:01 23-MAY-07
BP Key: 1638 Status: AVAILABLE Tag: TAG20070523T102302
Piece Name: D:\DBBAK\AL_4_1_623326982
List of Archived Logs in backup set 1637
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 44 879842 23-MAY-07 880088 23-MAY-07
1 45 880088 23-MAY-07 880093 23-MAY-07
RMAN>
利用其他数据库(须为OPEN状态),从备份中恢复数据文件
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=>1,toname=>'C:\SOFTWARE\ORACLE\ORADATA\ZHS16GBK\SYSTEM01.DBF');
8 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>2,toname=>'C:\SOFTWARE\ORACLE\ORADATA\ZHS16GBK\UNDOTBS01.DBF');
9 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>3,toname=>'C:\SOFTWARE\ORACLE\ORADATA\ZHS16GBK\INDX01.DBF');
10 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>4,toname=>'C:\SOFTWARE\ORACLE\ORADATA\ZHS16GBK\TOOLS01.DBF');
11 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>5,toname=>'C:\SOFTWARE\ORACLE\ORADATA\ZHS16GBK\USERS01.DBF');
12 sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'d:\dbbak\BK_1_1_623326719', params=>null);
13 sys.dbms_backup_restore.deviceDeallocate;
14 END;
15 /
PL/SQL procedure successfully completed.
SQL>
恢复日志文件:
SQL> show parameter log_archive_format;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_format string ARC%S.%T
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('c:\software\oracle\oradata\zhs16gbk\archive');
7 sys.dbms_backup_restore.restoreArchivedLog(thread=>1,sequence=>42);
8 sys.dbms_backup_restore.restoreArchivedLog(thread=>1,sequence=>43);
9 sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'d:\dbbak\AL_3_1_623326755',params=>null);
10 sys.dbms_backup_restore.deviceDeallocate;
11 end;
12 /
PL/SQL procedure successfully completed.
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('c:\software\oracle\oradata\zhs16gbk\archive');
7 sys.dbms_backup_restore.restoreArchivedLog(thread=>1,sequence=>44);
8 sys.dbms_backup_restore.restoreArchivedLog(thread=>1,sequence=>45);
9 sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'d:\dbbak\AL_4_1_623326982',params=>null);
10 sys.dbms_backup_restore.deviceDeallocate;
11 end;
12 /
PL/SQL procedure successfully completed.
SQL>
还原数据:
SQL> recover database using backup controlfile;
ORA-00279: change 879808 generated at 05/23/2007 10:18:40 needed for thread 1
ORA-00289: suggestion : C:\SOFTWARE\ORACLE\ORADATA\ZHS16GBK\ARCHIVE\ARC00042.001
ORA-00280: change 879808 for thread 1 is in sequence #42
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 879821 generated at 05/23/2007 10:19:07 needed for thread 1
ORA-00289: suggestion : C:\SOFTWARE\ORACLE\ORADATA\ZHS16GBK\ARCHIVE\ARC00043.001
ORA-00280: change 879821 for thread 1 is in sequence #43
ORA-00278: log file 'C:\SOFTWARE\ORACLE\ORADATA\ZHS16GBK\ARCHIVE\ARC00042.001' no longer needed for
this recovery
ORA-00279: change 879842 generated at 05/23/2007 10:19:14 needed for thread 1
ORA-00289: suggestion : C:\SOFTWARE\ORACLE\ORADATA\ZHS16GBK\ARCHIVE\ARC00044.001
ORA-00280: change 879842 for thread 1 is in sequence #44
ORA-00278: log file 'C:\SOFTWARE\ORACLE\ORADATA\ZHS16GBK\ARCHIVE\ARC00043.001' no longer needed for
this recovery
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to controlfile by media recovery
ORA-01110: data file 6: 'C:\SOFTWARE\ORACLE\ORADATA\ZHS16GBK\TEST01.DBF'
ORA-01112: media recovery not started
SQL> recover database using backup controlfile;
ORA-00283: recovery session canceled due to errors
ORA-01111: name for data file 6 is unknown - rename to correct file
ORA-01110: data file 6: 'C:\SOFTWARE\ORACLE\ORA92\DATABASE\UNNAMED00006'
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01111: name for data file 6 is unknown - rename to correct file
ORA-01110: data file 6: 'C:\SOFTWARE\ORACLE\ORA92\DATABASE\UNNAMED00006'
SQL>
SQL> select * from v$tablespace;
TS# NAME INC
---------- ------------------------------ ---
0 SYSTEM YES
1 UNDOTBS1 YES
2 TEMP YES
3 INDX YES
4 TOOLS YES
5 USERS YES
6 TEST_TABLESPACE YES
7 rows selected.
SQL> select * from v$datafile;
FILE# CREATION_CHANGE# CREATION_ TS# RFILE# STATUS ENABLED CHECKPOINT_CHANGE#
---------- ---------------- --------- ---------- ---------- ------- ---------- ------------------
CHECKPOIN UNRECOVERABLE_CHANGE# UNRECOVER LAST_CHANGE# LAST_TIME OFFLINE_CHANGE# ONLINE_CHANGE#
--------- --------------------- --------- ------------ --------- --------------- --------------
ONLINE_TI BYTES BLOCKS CREATE_BYTES BLOCK_SIZE
--------- ---------- ---------- ------------ ----------
NAME
----------------------------------------------------------------------------------------------------
PLUGGED_IN BLOCK1_OFFSET
---------- -------------
AUX_NAME
----------------------------------------------------------------------------------------------------
1 5 08-MAY-07 0 1 SYSTEM READ WRITE 880041
23-MAY-07 0 0 0
262144000 32000 262144000 8192
C:\SOFTWARE\ORACLE\ORADATA\ZHS16GBK\SYSTEM01.DBF
0 8192
NONE
2 4921 08-MAY-07 1 2 ONLINE READ WRITE 880041
23-MAY-07 0 0 0
209715200 25600 209715200 8192
C:\SOFTWARE\ORACLE\ORADATA\ZHS16GBK\UNDOTBS01.DBF
0 8192
NONE
3 6383 08-MAY-07 3 3 ONLINE READ WRITE 880041
23-MAY-07 0 0 0
26214400 3200 26214400 8192
C:\SOFTWARE\ORACLE\ORADATA\ZHS16GBK\INDX01.DBF
0 8192
NONE
4 6402 08-MAY-07 4 4 ONLINE READ WRITE 880041
23-MAY-07 0 0 0
10485760 1280 10485760 8192
C:\SOFTWARE\ORACLE\ORADATA\ZHS16GBK\TOOLS01.DBF
0 8192
NONE
5 6421 08-MAY-07 5 5 ONLINE READ WRITE 880041
23-MAY-07 0 0 0
26214400 3200 26214400 8192
C:\SOFTWARE\ORACLE\ORADATA\ZHS16GBK\USERS01.DBF
0 8192
NONE
6 880039 23-MAY-07 6 6 RECOVER READ WRITE 880039
23-MAY-07 0 0 0
0 0 10485760 8192
C:\SOFTWARE\ORACLE\ORA92\DATABASE\UNNAMED00006
0 4294967295
UNKNOWN
6 rows selected.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: 'C:\SOFTWARE\ORACLE\ORADATA\ZHS16GBK\SYSTEM01.DBF'
SQL>
数据文件6 恢复不成功,尝试再建
SQL> alter database create datafile 6 as 'c:\software\oracle\oradata\zhs16gbk\test01.dbf';
Database altered.
再还原:
SQL> recover database using backup controlfile;
ORA-00279: change 880039 generated at 05/23/2007 10:22:25 needed for thread 1
ORA-00289: suggestion : C:\SOFTWARE\ORACLE\ORADATA\ZHS16GBK\ARCHIVE\ARC00044.001
ORA-00280: change 880039 for thread 1 is in sequence #44
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change 880088 generated at 05/23/2007 10:22:58 needed for thread 1
ORA-00289: suggestion : C:\SOFTWARE\ORACLE\ORADATA\ZHS16GBK\ARCHIVE\ARC00045.001
ORA-00280: change 880088 for thread 1 is in sequence #45
ORA-00278: log file 'C:\SOFTWARE\ORACLE\ORADATA\ZHS16GBK\ARCHIVE\ARC00044.001' no longer needed for
this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change 880093 generated at 05/23/2007 10:23:01 needed for thread 1
ORA-00289: suggestion : C:\SOFTWARE\ORACLE\ORADATA\ZHS16GBK\ARCHIVE\ARC00046.001
ORA-00280: change 880093 for thread 1 is in sequence #46
ORA-00278: log file 'C:\SOFTWARE\ORACLE\ORADATA\ZHS16GBK\ARCHIVE\ARC00045.001' no longer needed for
this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00308: cannot open archived log 'C:\SOFTWARE\ORACLE\ORADATA\ZHS16GBK\ARCHIVE\ARC00046.001'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) 靠靠靠靠靠
SQL> recover database using backup controlfile;
ORA-00279: change 880093 generated at 05/23/2007 10:23:01 needed for thread 1
ORA-00289: suggestion : C:\SOFTWARE\ORACLE\ORADATA\ZHS16GBK\ARCHIVE\ARC00046.001
ORA-00280: change 880093 for thread 1 is in sequence #46
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL>
尝试打开数据库:
SQL> alter database datafile 6 offline;
Database altered.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01245: offline file 6 will be lost if RESETLOGS is done
ORA-01110: data file 6: 'C:\SOFTWARE\ORACLE\ORADATA\ZHS16GBK\TEST01.DBF'
SQL> alter database datafile 6 online;
Database altered.
SQL> alter database datafile 6 offline drop;
Database altered.
SQL> alter database open resetlogs;
Database altered.
SQL>
SQL> drop tablespace test_tablespace;
Tablespace dropped.
SQL>
现在数据库已打开, 但新建的表空间已DROP