基于归档的冷备份恢复

基于归档的冷备份恢复--脱机恢复，可以是完全恢复也可能是不完全恢复。

完全恢复的场景适用于数据文件和控制文件丢失或损坏，但是联机重做日志文件均完好，这时可以利用冷备份+重建控制文件+归档+联机重做日志文件进行数据库的完全恢复。

需要做归档模式下的完全恢复：

完全恢复的条件：

有最近的一次数据文件备份

控制文件和联机日志文件没有损坏

从备份时间开始到数据文件损坏时的所有归档日志文件完好

 

不完全恢复的场景适用于当前的控制文件和联机重做日志均已损坏或丢失，必须要应用到归档日志的备份来进行不完全恢复，和基于非归档的冷备份恢复同样只能恢复到指定的时间点或SCN。

       下面是基于归档的冷备份完全恢复进行实验。不完全恢复会有单独的实验。

1.        初始场景为用户timmie下的表t1中有一条记录，在这个基础上数据库做了冷备份操作，冷备份的方法不再解释

SYS@PROD>conn timmie/timmie Connected. TIMMIE@PROD>truncate table t1;   Table truncated.   TIMMIE@PROD>insert into t1 values(1);   1 row created.   TIMMIE@PROD>commit;   Commit complete.   TIMMIE@PROD>select * from t1;            A ----------          1   TIMMIE@PROD>conn / as sysdba Connected. SYS@PROD>shutdown immediate Database closed. Database dismounted. ORACLE instance shut down.   cp /u01/app/oracle/oradata/PROD/disk1/system001.dbf /home/oracle/coldbackup/20140125-1 cp /u01/app/oracle/oradata/PROD/disk1/undotbs01.dbf /home/oracle/coldbackup/20140125-1 cp /u01/app/oracle/oradata/PROD/disk1/sysaux01.dbf /home/oracle/coldbackup/20140125-1 cp /u01/app/oracle/oradata/PROD/disk1/example1.dbf /home/oracle/coldbackup/20140125-1 cp /u01/app/oracle/oradata/PROD/disk1/indx1.dbf /home/oracle/coldbackup/20140125-1 cp /u01/app/oracle/oradata/PROD/disk1/tools1.dbf /home/oracle/coldbackup/20140125-1 cp /u01/app/oracle/oradata/PROD/disk1/users1.dbf /home/oracle/coldbackup/20140125-1 cp /u01/app/oracle/oradata/PROD/disk1/oltp1.dbf /home/oracle/coldbackup/20140125-1 cp /u01/app/oracle/oradata/PROD/disk1/tbs_tommie01.dbf /home/oracle/coldbackup/20140125-1 cp /u01/app/oracle/oradata/PROD/disk1/redo101.log /home/oracle/coldbackup/20140125-1 cp /u01/app/oracle/oradata/PROD/disk2/redo102.log /home/oracle/coldbackup/20140125-1 cp /u01/app/oracle/oradata/PROD/disk3/redo103.log /home/oracle/coldbackup/20140125-1 cp /u01/app/oracle/oradata/PROD/disk1/redo201.log /home/oracle/coldbackup/20140125-1 cp /u01/app/oracle/oradata/PROD/disk2/redo202.log /home/oracle/coldbackup/20140125-1 cp /u01/app/oracle/oradata/PROD/disk3/redo203.log /home/oracle/coldbackup/20140125-1 cp /u01/app/oracle/oradata/PROD/disk1/redo301.log /home/oracle/coldbackup/20140125-1 cp /u01/app/oracle/oradata/PROD/disk2/redo302.log /home/oracle/coldbackup/20140125-1 cp /u01/app/oracle/oradata/PROD/disk3/redo303.log /home/oracle/coldbackup/20140125-1 cp /u01/app/oracle/oradata/PROD/disk4/redo104.log /home/oracle/coldbackup/20140125-1 cp /u01/app/oracle/oradata/PROD/disk4/redo204.log /home/oracle/coldbackup/20140125-1 cp /u01/app/oracle/oradata/PROD/disk1/control01.ctl /home/oracle/coldbackup/20140125-1 cp /u01/app/oracle/oradata/PROD/disk2/control02.ctl /home/oracle/coldbackup/20140125-1 cp /u01/app/oracle/product/10.2.0/db_1/dbs/spfilePROD.ora /home/oracle/coldbackup/20140125-1

2.        冷备份后启动数据库，数据库的正常工作内容包括插入一条新数据后执行归档，然后再插入第三条记录，该记录留在联机日志文件中

SYS@PROD>startup ORACLE instance started.   Total System Global Area  314572800 bytes Fixed Size                  1219184 bytes Variable Size              75498896 bytes Database Buffers          234881024 bytes Redo Buffers                2973696 bytes Database mounted. Database opened. SYS@PROD>conn timmie/timmie Connected. TIMMIE@PROD>select * from t1;            A ----------          1   TIMMIE@PROD>insert into t1 values(2);   1 row created.   TIMMIE@PROD>commit;   Commit complete.   TIMMIE@PROD>conn / as sysdba Connected. SYS@PROD>alter system switch logfile;   System altered.   [oracle@odd-oelr4u8 arc_dest_1]$ ll 总用量 304 -rw-r-----  1 oracle oinstall 307200  1月 22 13:24 1_33_836833769.dbf [oracle@odd-oelr4u8 arc_dest_1]$ pwd /u01/app/oracle/oradata/PROD/arc_dest_1   SYS@PROD>conn timmie/timmie Connected. TIMMIE@PROD>insert into t1 values(3);   1 row created.   TIMMIE@PROD>commit;   Commit complete.   TIMMIE@PROD>select * from t1;            A ----------          1          2              3

此时t1有三条记录，第一条记录在冷备份中，第二条记录在归档中，第三条记录在联机日志中

3.        之后数据库出现问题，某个数据文件损坏或丢失，控制文件也全部丢失或损坏，但是联机日志文件都完好，需要执行冷备份+重建控制文件+归档+联机日志的完全恢复

4.        实验恢复前，再做一个关机冷备份，跟恢复无关，只是做测试时的一种保障手段，备份到另一个冷备份目录里

5.        下面开始执行完全恢复

                        i.              第一步：以下只恢复数据文件，不恢复联机日志，必须用到当前的联机日志才能做完全恢复

TIMMIE@PROD>conn / as sysdba Connected. SYS@PROD>shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. cd /u01/app/oracle/oradata/PROD/disk1/ rm -f /u01/app/oracle/oradata/PROD/disk1/system001.dbf rm -f /u01/app/oracle/oradata/PROD/disk1/undotbs01.dbf rm -f /u01/app/oracle/oradata/PROD/disk1/sysaux01.dbf rm -f /u01/app/oracle/oradata/PROD/disk1/example1.dbf rm -f /u01/app/oracle/oradata/PROD/disk1/indx1.dbf rm -f /u01/app/oracle/oradata/PROD/disk1/tools1.dbf rm -f /u01/app/oracle/oradata/PROD/disk1/users1.dbf rm -f /u01/app/oracle/oradata/PROD/disk1/oltp1.dbf rm -f /u01/app/oracle/oradata/PROD/disk1/tbs_tommie01.dbf   cp /home/oracle/coldbackup/20140125-1/system001.dbf    /u01/app/oracle/oradata/PROD/disk1/system001.dbf cp /home/oracle/coldbackup/20140125-1/undotbs01.dbf    /u01/app/oracle/oradata/PROD/disk1/undotbs01.dbf cp /home/oracle/coldbackup/20140125-1/sysaux01.dbf     /u01/app/oracle/oradata/PROD/disk1/sysaux01.dbf cp /home/oracle/coldbackup/20140125-1/example1.dbf     /u01/app/oracle/oradata/PROD/disk1/example1.dbf cp /home/oracle/coldbackup/20140125-1/indx1.dbf        /u01/app/oracle/oradata/PROD/disk1/indx1.dbf cp /home/oracle/coldbackup/20140125-1/tools1.dbf       /u01/app/oracle/oradata/PROD/disk1/tools1.dbf cp /home/oracle/coldbackup/20140125-1/users1.dbf       /u01/app/oracle/oradata/PROD/disk1/users1.dbf cp /home/oracle/coldbackup/20140125-1/oltp1.dbf        /u01/app/oracle/oradata/PROD/disk1/oltp1.dbf cp /home/oracle/coldbackup/20140125-1/tbs_tommie01.dbf /u01/app/oracle/oradata/PROD/disk1/tbs_tommie01.dbf

                      ii.              第二步：尝试noresetlogs方式创建控制文件。-- 经过测试，如果原controlfile没有问题，可以不需要执行该步骤，直接执行第三步的recover database

控制文件中各个数据文件的checkpoint cnt来自于online的redolog。重建控制文件的内容来自trace文件，alter database backup controlfile to trace; --在user_dump_dest目录下。

--重建控制文件有一个主要选项需要理解：NORESETLOGS/RESETLOGS 在跟踪文件中包含以下注释，详细解释了这两个选项和含义：

--Below are two sets of SQL statements, each of which creates a new

--control file and uses it to open the database. The first set opens

--the database with the NORESETLOGS option and should be used only if

--the current versions of all online logs are available. The second

--set opens the database with the RESETLOGS option and should be used

--if online logs are unavailable.

SYS@PROD>STARTUP NOMOUNT SYS@PROD>CREATE CONTROLFILE REUSE DATABASE "PROD" NORESETLOGS  ARCHIVELOG     MAXLOGFILES 150     MAXLOGMEMBERS 5     MAXDATAFILES 200     MAXINSTANCES 2     MAXLOGHISTORY 292 LOGFILE   GROUP 1 (     '/u01/app/oracle/oradata/PROD/disk1/redo101.log',     '/u01/app/oracle/oradata/PROD/disk2/redo102.log',     '/u01/app/oracle/oradata/PROD/disk3/redo103.log',     '/u01/app/oracle/oradata/PROD/disk4/redo104.log'   ) SIZE 100M,   GROUP 2 (     '/u01/app/oracle/oradata/PROD/disk1/redo201.log',     '/u01/app/oracle/oradata/PROD/disk2/redo202.log',     '/u01/app/oracle/oradata/PROD/disk3/redo203.log',     '/u01/app/oracle/oradata/PROD/disk4/redo204.log'   ) SIZE 100M,   GROUP 3 (     '/u01/app/oracle/oradata/PROD/disk1/redo301.log',     '/u01/app/oracle/oradata/PROD/disk2/redo302.log',     '/u01/app/oracle/oradata/PROD/disk3/redo303.log',     '/u01/app/oracle/oradata/PROD/disk4/redo304.log'   ) SIZE 100M -- STANDBY LOGFILE DATAFILE   '/u01/app/oracle/oradata/PROD/disk1/system001.dbf',   '/u01/app/oracle/oradata/PROD/disk1/undotbs01.dbf',   '/u01/app/oracle/oradata/PROD/disk1/sysaux01.dbf',   '/u01/app/oracle/oradata/PROD/disk1/example1.dbf',   '/u01/app/oracle/oradata/PROD/disk1/indx1.dbf',   '/u01/app/oracle/oradata/PROD/disk1/tools1.dbf',   '/u01/app/oracle/oradata/PROD/disk1/users1.dbf',   '/u01/app/oracle/oradata/PROD/disk1/oltp1.dbf',   '/u01/app/oracle/oradata/PROD/disk1/tbs_tommie01.dbf' CHARACTER SET AL32UTF8 ;   Control file created. SYS@PROD>VARIABLE RECNO NUMBER; SYS@PROD>EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CHANNEL','DEVICE TYPE DISK FORMAT   ''/home/oracle/backup/PROD_%U''');   PL/SQL procedure successfully completed.   SYS@PROD>VARIABLE RECNO NUMBER; SYS@PROD>EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','ON');   PL/SQL procedure successfully completed.   SYS@PROD>VARIABLE RECNO NUMBER; SYS@PROD>EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE','DISK TO ''/home/oracle/backup/control/controlfile_%F''');   PL/SQL procedure successfully completed.   SYS@PROD>VARIABLE RECNO NUMBER; SYS@PROD>EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DEVICE TYPE','DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET');   PL/SQL procedure successfully completed.   SYS@PROD>VARIABLE RECNO NUMBER; SYS@PROD>EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('BACKUP OPTIMIZATION','ON');   PL/SQL procedure successfully completed.   SYS@PROD>VARIABLE RECNO NUMBER; SYS@PROD>EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('RETENTION POLICY','TO RECOVERY WINDOW OF 30 DAYS');   PL/SQL procedure successfully completed.

                    iii.              第三步：RECOVER DATABASE

SYS@PROD>select open_mode from v$database; OPEN_MODE -------------------- MOUNTED SYS@PROD>recover database – 重建控制文件后，数据库自动到了mount状态下，如果不重建控制文件，则需要先startup mount然后再执行recover database Media recovery complete. SYS@PROD>ALTER SYSTEM ARCHIVE LOG ALL;   System altered.   SYS@PROD>ALTER DATABASE OPEN;   Database altered.   SYS@PROD>ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/PROD/disk1/temp01.dbf' REUSE; ALTER TABLESPACE TEMP1 ADD TEMPFILE '/u01/app/oracle/oradata/PROD/disk1/temp11.dbf' REUSE; ALTER TABLESPACE TEMP2 ADD TEMPFILE '/u01/app/oracle/oradata/PROD/disk1/temp22.dbf' REUSE;   Tablespace altered.   SYS@PROD> Tablespace altered.   SYS@PROD> Tablespace altered.

                     iv.              第四步：测试

SYS@PROD>conn timmie/timmie Connected. TIMMIE@PROD>select * from t1;            A ----------          1          2          3

至此，三条记录全部恢复，完全恢复成功。

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

转载于:http://blog.itpub.net/26521853/viewspace-1083930/