使用RMAN复制恢复开发库环境

最近为了不影响开发库的使用，打算复制创建一个备库，定时更新，防止开发库不能使用的情况下，可以临时使用备库，不影响进度。

环境：
11.2.0.4的单实例库。
库1是源库(target)，库2是备库(auxiliary)

操作过程：

1.库1执行全库备份

[oracle@testvm002 duplicate]$ rman target /Recovery Manager: Release 11.2.0.4.0 - Production on Mon Feb 1 16:57:06 2016Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.connected to target database: DEP (DBID=1501218369)RMAN> run{2> allocate channel dup type disk;3> backup format '/home/oracle/duplicate/df_t%t_s%s_p%p' database;4> sql 'alter system archive log current';5> backup format '/home/oracle/duplicate/al_t%t_s%s_p%p' archivelog all delete input;6> release channel dup;7> }using target database control file instead of recovery catalogallocated channel: dupchannel dup: SID=24 device type=DISKStarting backup at 01-FEB-16channel dup: starting full datafile backup setchannel dup: specifying datafile(s) in backup setinput datafile file number=00002 name=/u01/app/oracle/11.2.0.4/oradata/dep/sysaux01.dbfinput datafile file number=00005 name=/u01/app/oracle/11.2.0.4/oradata/dep/dep_tbs01.dbfinput datafile file number=00001 name=/u01/app/oracle/11.2.0.4/oradata/dep/system01.dbfinput datafile file number=00003 name=/u01/app/oracle/11.2.0.4/oradata/dep/undotbs01.dbfinput datafile file number=00004 name=/u01/app/oracle/11.2.0.4/oradata/dep/users01.dbfchannel dup: starting piece 1 at 01-FEB-16channel dup: finished piece 1 at 01-FEB-16piece handle=/home/oracle/duplicate/df_t902681834_s10_p1 tag=TAG20160201T165714 comment=NONEchannel dup: backup set complete, elapsed time: 00:00:36channel dup: starting full datafile backup setchannel dup: specifying datafile(s) in backup setincluding current control file in backup setincluding current SPFILE in backup setchannel dup: starting piece 1 at 01-FEB-16channel dup: finished piece 1 at 01-FEB-16piece handle=/home/oracle/duplicate/df_t902681870_s11_p1 tag=TAG20160201T165714 comment=NONEchannel dup: backup set complete, elapsed time: 00:00:01Finished backup at 01-FEB-16sql statement: alter system archive log currentStarting backup at 01-FEB-16current log archivedchannel dup: starting archived log backup setchannel dup: specifying archived log(s) in backup setinput archived log thread=1 sequence=743 RECID=13 STAMP=902681873input archived log thread=1 sequence=744 RECID=14 STAMP=902681874channel dup: starting piece 1 at 01-FEB-16channel dup: finished piece 1 at 01-FEB-16piece handle=/home/oracle/duplicate/al_t902681874_s12_p1 tag=TAG20160201T165754 comment=NONEchannel dup: backup set complete, elapsed time: 00:00:01channel dup: deleting archived log(s)archived log file name=/u01/app/oracle/11.2.0.4/fast_recovery_area/DEP/archivelog/2016_02_01/o1_mf_1_743_cby7jklq_.arc RECID=13 STAMP=902681873archived log file name=/u01/app/oracle/11.2.0.4/fast_recovery_area/DEP/archivelog/2016_02_01/o1_mf_1_744_cby7jl4s_.arc RECID=14 STAMP=902681874Finished backup at 01-FEB-16released channel: dup

2.库1备份控制文件

SQL> alter database backup controlfile to '/home/oracle/duplicate/control.ctl';Database altered.

3.将备份集(1)拷贝到库2相同路径下，即/home/oracle/duplicate，此时产生了三个文件：

al_t902681874_s12_p1df_t902681834_s10_p1df_t902681870_s11_p1

4.将库1的inittest.ora参数文件拷贝到库2，并按照其中配置的控制文件路径，将库1的控制文件(2)可以直接copy到库2对应路径下，包括文件名需要保持和参数文件总定义一致：

*.control_files='/u01/app/oracle/11.2.0.4/oradata/test/control01.ctl','/u01/app/oracle/11.2.0.4/fast_recovery_area/test/control02.ctl'

创建文件夹：

/u01/app/oracle/11.2.0.4/oradata/test/u01/app/oracle/11.2.0.4/fast_recovery_area/test/u01/app/oracle/11.2.0.4/admin/adump(/bdump/cdump/dpdump/udump)

5.库2启动nomount状态

SQL> startup nomount pfile='/u01/app/oracle/11.2.0.4/dbhome_1/dbs/initdep.ora';ORACLE instance started.Total System Global Area 1620115456 bytesFixed Size                  2253704 bytesVariable Size             922750072 bytesDatabase Buffers          687865856 bytesRedo Buffers                7245824 bytes

6.库2执行恢复

[oracle@testvm001 dbs]$ rman target /Recovery Manager: Release 11.2.0.4.0 - Production on Mon Feb 1 17:10:21 2016Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.connected to target database: DEP (not mounted)RMAN> restore database;Starting restore at 01-FEB-16using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=10 device type=DISKRMAN-00571: ==================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ======RMAN-00571: ==================================================RMAN-03002: failure of restore command at 02/01/2016 17:10:28ORA-01507: database not mounted

报错提示数据库需要置为mount状态。

7.库2置为mount状态

SQL> alter database mount;Database altered.

8.库2执行恢复

RMAN> restore database;Starting restore at 01-FEB-16released channel: ORA_DISK_1Starting implicit crosscheck backup at 01-FEB-16allocated channel: ORA_DISK_1channel ORA_DISK_1: SID=10 device type=DISKCrosschecked 6 objectsFinished implicit crosscheck backup at 01-FEB-16Starting implicit crosscheck copy at 01-FEB-16using channel ORA_DISK_1Crosschecked 6 objectsFinished implicit crosscheck copy at 01-FEB-16searching for all files in the recovery areacataloging files...no files catalogedusing channel ORA_DISK_1channel ORA_DISK_1: starting datafile backup set restorechannel ORA_DISK_1: specifying datafile(s) to restore from backup setchannel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/11.2.0.4/oradata/dep/system01.dbfchannel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/11.2.0.4/oradata/dep/sysaux01.dbfchannel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/11.2.0.4/oradata/dep/undotbs01.dbfchannel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/11.2.0.4/oradata/dep/users01.dbfchannel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/11.2.0.4/oradata/dep/dep_tbs01.dbfchannel ORA_DISK_1: reading from backup piece /home/oracle/duplicate/df_t902681834_s10_p1channel ORA_DISK_1: piece handle=/home/oracle/duplicate/df_t902681834_s10_p1 tag=TAG20160201T165714channel ORA_DISK_1: restored backup piece 1channel ORA_DISK_1: restore complete, elapsed time: 00:00:35Finished restore at 01-FEB-16

9.库2执行打开

SQL> alter database open;alter database open*ERROR at line 1:ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

报错提示需要使用RESETLOGS或NORESETLOGS。

10.库2执行打开

SQL> alter database open resetlogs;alter database open resetlogs*ERROR at line 1:ORA-01152: file 1 was not restored from a sufficiently old backupORA-01110: data file 1: '/u01/app/oracle/11.2.0.4/oradata/dep/system01.dbf'

依旧报错，提示：
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: ‘/u01/app/oracle/11.2.0.4/oradata/dep/system01.dbf’

查看这两个报错：

[oracle@testvm001 dbs]$ oerr ora 1152 01152, 00000, “file %s was
not restored from a sufficiently old backup ”
*Cause: An incomplete recovery session was started, but an
insufficient number of logs were applied to make the database
consistent. This file is still in the future of the last log applied.
The most likely cause of this error is forgetting to restore the file
from a backup before doing incomplete recovery.
*Action: Either apply more logs until the database is consistent or > restore the database file from an older backup and repeat
recovery.

[oracle@testvm001 dbs]$ oerr ora 1110 01110, 00000, “data file %s: ‘%s’”
*Cause: Reporting file name for details of another error.
The reported name can be of the old file if a data file
move operation is in progress.
*Action: See associated error message.

11.库2执行recover

RMAN> recover database;Starting recover at 01-FEB-16using channel ORA_DISK_1starting media recoverychannel ORA_DISK_1: starting archived log restore to default destinationchannel ORA_DISK_1: restoring archived logarchived log thread=1 sequence=743channel ORA_DISK_1: restoring archived logarchived log thread=1 sequence=744channel ORA_DISK_1: reading from backup piece /home/oracle/duplicate/al_t902681874_s12_p1channel ORA_DISK_1: piece handle=/home/oracle/duplicate/al_t902681874_s12_p1 tag=TAG20160201T165754channel ORA_DISK_1: restored backup piece 1channel ORA_DISK_1: restore complete, elapsed time: 00:00:01archived log file name=/u01/app/oracle/11.2.0.4/fast_recovery_area/DEP/archivelog/2016_02_01/o1_mf_1_743_cby8pbbf_.arc thread=1 sequence=743channel default: deleting archived log(s)archived log file name=/u01/app/oracle/11.2.0.4/fast_recovery_area/DEP/archivelog/2016_02_01/o1_mf_1_743_cby8pbbf_.arc RECID=16 STAMP=902683082archived log file name=/u01/app/oracle/11.2.0.4/fast_recovery_area/DEP/archivelog/2016_02_01/o1_mf_1_744_cby8pbc5_.arc thread=1 sequence=744channel default: deleting archived log(s)archived log file name=/u01/app/oracle/11.2.0.4/fast_recovery_area/DEP/archivelog/2016_02_01/o1_mf_1_744_cby8pbc5_.arc RECID=15 STAMP=902683082unable to find archived logarchived log thread=1 sequence=745RMAN-00571: ===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-03002: failure of recover command at 02/01/2016 17:18:03RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 745 and starting SCN of 14019269

提示需要sequence=745号的archivelog

12.查看备份集中归档文件al_t902681874_s12_p1的sequence是12。

13.再次执行恢复

RMAN> run{2>  allocate channel ch00 type disk;3>  set until sequence 13 thread 1;4>  recover database;5>  release channel ch00;6> }released channel: ORA_DISK_1allocated channel: ch00channel ch00: SID=10 device type=DISKexecuting command: SET until clauseStarting recover at 01-FEB-16starting media recoverymedia recovery complete, elapsed time: 00:00:00Finished recover at 01-FEB-16released channel: ch00

对应alert日志：

ARC3: Archival startedARC0: STARTING ARCH PROCESSES COMPLETEStarting background process QMNCMon Feb 01 17:22:33 2016QMNC started with pid=27, OS id=4154LOGSTDBY: Validating controlfile with logical metadataLOGSTDBY: Validation complete

14.开启库2

SQL> alter database open resetlogs;Database altered.

对应alert日志：

Completed: alter database open resetlogsMon Feb 01 17:22:35 2016db_recovery_file_dest_size of 4977 MB is 0.00% used. This is auser-specified limit on the amount of space that will be used by thisdatabase for recovery-related files, and does not reflect the amount ofspace available in the underlying filesystem or ASM diskgroup.Mon Feb 01 17:22:35 2016Starting background process CJQ0Mon Feb 01 17:22:35 2016CJQ0 started with pid=28, OS id=4168

15.登录库2，用户、对象等信息和库1完全一致。

总结：
​1.RMAN执行restore需要数据库置于mount状态。
2.可以先将target的control控制文件拷贝到auxiliary库相同路径下，需要注意参数文件中定义的控制文件路径以及其他文件夹均需要提前在auxiliary库中创建，如果提前将控制文件拷贝到auxiliary库对应路径下，则无需restore control，相当于已经做了这不操作。
​3.resetlogs提示ORA1152错误，需要进行相应归档日志的恢复，根据拷贝过来的归档日志文件名推算seq号。