Oracle 11gR2使用RMAN duplicate复制数据库——Backup-based database duplicate

本实例测试Oracle 11g使用rman复制数据库。11g的RMAN duplicate 可以通过Active database duplicate和Backup-based duplicate两种方法实现。这里的测试使用的是Backup-based duplicate。应用场景。基于Active database duplicate参考 http://www.linuxidc.com/Linux/2017-02/140532.htm1、旧库或网络不可用，但是有备份实验环境：target db：ip 192.168.56.10oracle_sid=mydboracle_version=11.2.0.3auxiliary db：ip 192.168.56.150oracle_sid=oraduoracle_version=11.2.0.31、备份target dbRMAN> backup database include current controlfile plus archivelog delete all input; Starting backup at 20-MAR-16current log archivedusing channel ORA_DISK_1channel ORA_DISK_1: starting archived log backup setchannel ORA_DISK_1: specifying archived log（s） in backup setinput archived log thread=1 sequence=25 RECID=47 STAMP=906998693input archived log thread=1 sequence=26 RECID=48 STAMP=906999061channel ORA_DISK_1: starting piece 1 at 20-MAR-16channel ORA_DISK_1: finished piece 1 at 20-MAR-16piece handle=/u01/app/oracle/product/11.2.0/db/dbs/34r0vd8l_1_1 tag=TAG20160320T161101 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:01channel ORA_DISK_1: deleting archived log（s）archived log file name=/u01/app/oracle/product/11.2.0/db/dbs/arch/1_25_906314379.dbf RECID=47 STAMP=906998693archived log file name=/u01/app/oracle/product/11.2.0/db/dbs/arch/1_26_906314379.dbf RECID=48 STAMP=906999061Finished backup at 20-MAR-16Starting backup at 20-MAR-16using channel ORA_DISK_1channel ORA_DISK_1: starting full datafile backup setchannel ORA_DISK_1: specifying datafile（s） in backup setinput datafile file number=00001 name=/u01/app/oracle/oradata/mydb/system01.dbfinput datafile file number=00002 name=/u01/app/oracle/oradata/mydb/sysaux01.dbfinput datafile file number=00003 name=/u01/app/oracle/oradata/mydb/undotbs01.dbfinput datafile file number=00004 name=/u01/app/oracle/oradata/mydb/users01.dbfchannel ORA_DISK_1: starting piece 1 at 20-MAR-16channel ORA_DISK_1: finished piece 1 at 20-MAR-16piece handle=/u01/app/oracle/product/11.2.0/db/dbs/35r0vd8m_1_1 tag=TAG20160320T161102 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:55channel ORA_DISK_1: starting full datafile backup setchannel ORA_DISK_1: specifying datafile（s） in backup setincluding current control file in backup setchannel ORA_DISK_1: starting piece 1 at 20-MAR-16channel ORA_DISK_1: finished piece 1 at 20-MAR-16piece handle=/u01/app/oracle/product/11.2.0/db/dbs/36r0vdad_1_1 tag=TAG20160320T161102 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:01Finished backup at 20-MAR-16Starting backup at 20-MAR-16current log archivedusing channel ORA_DISK_1channel ORA_DISK_1: starting archived log backup setchannel ORA_DISK_1: specifying archived log（s） in backup setinput archived log thread=1 sequence=27 RECID=49 STAMP=906999120channel ORA_DISK_1: starting piece 1 at 20-MAR-16channel ORA_DISK_1: finished piece 1 at 20-MAR-16piece handle=/u01/app/oracle/product/11.2.0/db/dbs/37r0vdag_1_1 tag=TAG20160320T161200 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:01channel ORA_DISK_1: deleting archived log（s）archived log file name=/u01/app/oracle/product/11.2.0/db/dbs/arch/1_27_906314379.dbf RECID=49 STAMP=906999120Finished backup at 20-MAR-16Starting Control File and SPFILE Autobackup at 20-MAR-16piece handle=/u01/app/oracle/product/11.2.0/db/dbs/c-2820637901-20160320-05 comment=NONEFinished Control File and SPFILE Autobackup at 20-MAR-162、复制参数文件和创建密码文件--必须保持target DB和auxiliary DB的密码一致。这里我直接把target db的密码文件复制到auxiliary db对应的目录下--target db上执行[oracle@localhost ~]$ cd $ORACLE_HOME/dbs[oracle@localhost dbs]$ scp orapwmydb oracle@192.168.56.150:/home/oracleoracle@192.168.56.150"s password:orapwmydb 100% 1536 1.5KB/s 00:00 [oracle@localhost dbs]$ scp initmydb.ora oracle@192.168.56.150:/home/oracleoracle@192.168.56.150"s password:initmydb.ora 100% 193 0.2KB/s 00:00 3、把旧机器的备份复制到新机器上的对应目录[oracle@localhost dbs]$ scp /u01/app/oracle/product/11.2.0/db/dbs/34r0vd8l_1_1 oracle@192.168.56.150:/home/oracle/bakoracle@192.168.56.150"s password:34r0vd8l_1_1 100% 5457KB 5.3MB/s 00:00 [oracle@localhost dbs]$ scp /u01/app/oracle/product/11.2.0/db/dbs/35r0vd8m_1_1 oracle@192.168.56.150:/home/oracle/bakoracle@192.168.56.150"s password:35r0vd8m_1_1 100% 1049MB 15.0MB/s 01:10 [oracle@localhost dbs]$ scp /u01/app/oracle/product/11.2.0/db/dbs/36r0vdad_1_1 oracle@192.168.56.150:/home/oracle/bakoracle@192.168.56.150"s password:36r0vdad_1_1 100% 9952KB 9.7MB/s 00:00 [oracle@localhost dbs]$ scp /u01/app/oracle/product/11.2.0/db/dbs/37r0vdag_1_1 oracle@192.168.56.150:/home/oracle/bakoracle@192.168.56.150"s password:37r0vdag_1_1 100% 10KB 10.0KB/s 00:00 4、更改密码文件、参数文件的文件名和参数文件中的对应的实例名（实例名不变时不需要执行）,把目录转换参数加入参数文件中（实例名、目录不变时不需要）本实验中更新实例名为oradu[oracle@localhost ~]$ mv initmydb.ora initoradu.ora[oracle@localhost ~]$ mv orapwmydb orapworaduvi initoradu.ora把所有的旧实例名mydb改为oradu，并加入转换参数db_file_name_convert=（"/u01/app/oracle/oradata/mydb/","/u01/app/oracle/oradata/oradu/"）log_file_name_convert=（"/u01/app/oracle/oradata/mydb/","/u01/app/oracle/oradata/oradu/"）新建目录mkdir -p /u01/app/oracle/oradata/oradu/不改变实例名只需要新建目录mkdir -p /u01/app/oracle/oradata/mydb/移动参数文件和密码文件到默认目录中/u01/app/oracle/product/11.2.0/db/dbs/[oracle@localhost ~]$ mv initoradu.ora /u01/app/oracle/product/11.2.0/db/dbs/[oracle@localhost ~]$ mv orapworadu /u01/app/oracle/product/11.2.0/db/dbs/5、启动到nomount[oracle@localhost ~]$ export ORACLE_SID=oradu[oracle@localhost ~]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.3.0 Production on Sun Mar 20 16:31:43 2016Copyright （c） 1982, 2011, Oracle. All rights reserved.Connected to an idle instance.SQL> create spfile from pfile;File created.SQL> startup nomount;ORACLE instance started.Total System Global Area 238034944 bytesFixed Size 2227136 bytesVariable Size 180356160 bytesDatabase Buffers 50331648 bytesRedo Buffers 5120000 bytesSQL>6、使用RMAN开始复制[oracle@localhost ~]$ rman auxiliary /Recovery Manager: Release 11.2.0.3.0 - Production on Sun Mar 20 16:33:11 2016Copyright （c） 1982, 2011, Oracle and/or its affiliates. All rights reserved.connected to auxiliary database: ORADU （not mounted）RMAN> duplicate database to oradu backup location "/home/oracle/bak";Starting Duplicate Db at 20-MAR-16contents of Memory Script:{ sql clone "alter system set db_name = ""MYDB"" comment= ""Modified by RMAN duplicate"" scope=spfile"; sql clone "alter system set db_unique_name = ""ORADU"" comment= ""Modified by RMAN duplicate"" scope=spfile"; shutdown clone immediate; startup clone force nomount restore clone primary controlfile from "/home/oracle/bak/36r0vdad_1_1"; alter clone database mount;}executing Memory Scriptsql statement: alter system set db_name = ""MYDB"" comment= ""Modified by RMAN duplicate"" scope=spfilesql statement: alter system set db_unique_name = ""ORADU"" comment= ""Modified by RMAN duplicate"" scope=spfileOracle instance shut downOracle instance startedTotal System Global Area 238034944 bytesFixed Size 2227136 bytesVariable Size 180356160 bytesDatabase Buffers 50331648 bytesRedo Buffers 5120000 bytesStarting restore at 20-MAR-16allocated channel: ORA_AUX_DISK_1channel ORA_AUX_DISK_1: SID=18 device type=DISKchannel ORA_AUX_DISK_1: restoring control filechannel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02output file name=/u01/app/oracle/oradata/oradu/control01.ctloutput file name=/u01/app/oracle/oradata/oradu/control02.ctlFinished restore at 20-MAR-16database mountedreleased channel: ORA_AUX_DISK_1allocated channel: ORA_AUX_DISK_1channel ORA_AUX_DISK_1: SID=18 device type=DISKcontents of Memory Script:{ set until scn 2815228; set newname for datafile 1 to "/u01/app/oracle/oradata/oradu/system01.dbf"; set newname for datafile 2 to "/u01/app/oracle/oradata/oradu/sysaux01.dbf"; set newname for datafile 3 to "/u01/app/oracle/oradata/oradu/undotbs01.dbf"; set newname for datafile 4 to "/u01/app/oracle/oradata/oradu/users01.dbf"; restore clone database ;}executing Memory Scriptexecuting command: SET until clauseexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEStarting restore at 20-MAR-16using channel ORA_AUX_DISK_1channel ORA_AUX_DISK_1: starting datafile backup set restorechannel ORA_AUX_DISK_1: specifying datafile（s） to restore from backup setchannel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/oradu/system01.dbfchannel ORA_AUX_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/oradu/sysaux01.dbfchannel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/oradu/undotbs01.dbfchannel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/oradu/users01.dbfchannel ORA_AUX_DISK_1: reading from backup piece /home/oracle/bak/35r0vd8m_1_1channel ORA_AUX_DISK_1: piece handle=/home/oracle/bak/35r0vd8m_1_1 tag=TAG20160320T161102channel ORA_AUX_DISK_1: restored backup piece 1channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:05Finished restore at 20-MAR-16contents of Memory Script:{ switch clone datafile all;}executing Memory Scriptdatafile 1 switched to datafile copyinput datafile copy RECID=5 STAMP=907000504 file name=/u01/app/oracle/oradata/oradu/system01.dbfdatafile 2 switched to datafile copyinput datafile copy RECID=6 STAMP=907000504 file name=/u01/app/oracle/oradata/oradu/sysaux01.dbfdatafile 3 switched to datafile copyinput datafile copy RECID=7 STAMP=907000504 file name=/u01/app/oracle/oradata/oradu/undotbs01.dbfdatafile 4 switched to datafile copyinput datafile copy RECID=8 STAMP=907000504 file name=/u01/app/oracle/oradata/oradu/users01.dbfcontents of Memory Script:{ set until scn 2815228; recover clone database delete archivelog ;}executing Memory Scriptexecuting command: SET until clauseStarting recover at 20-MAR-16using channel ORA_AUX_DISK_1starting media recoverychannel ORA_AUX_DISK_1: starting archived log restore to default destinationchannel ORA_AUX_DISK_1: restoring archived logarchived log thread=1 sequence=27channel ORA_AUX_DISK_1: reading from backup piece /home/oracle/bak/37r0vdag_1_1channel ORA_AUX_DISK_1: piece handle=/home/oracle/bak/37r0vdag_1_1 tag=TAG20160320T161200channel ORA_AUX_DISK_1: restored backup piece 1channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01archived log file name=/u01/app/oracle/product/11.2.0/db/dbs/arch1_27_906314379.dbf thread=1 sequence=27channel clone_default: deleting archived log（s）archived log file name=/u01/app/oracle/product/11.2.0/db/dbs/arch1_27_906314379.dbf RECID=1 STAMP=907000505media recovery complete, elapsed time: 00:00:01Finished recover at 20-MAR-16Oracle instance startedTotal System Global Area 238034944 bytesFixed Size 2227136 bytesVariable Size 180356160 bytesDatabase Buffers 50331648 bytesRedo Buffers 5120000 bytescontents of Memory Script:{ sql clone "alter system set db_name = ""ORADU"" comment= ""Reset to original value by RMAN"" scope=spfile"; sql clone "alter system reset db_unique_name scope=spfile"; shutdown clone immediate; startup clone nomount;}executing Memory Scriptsql statement: alter system set db_name = ""ORADU"" comment= ""Reset to original value by RMAN"" scope=spfilesql statement: alter system reset db_unique_name scope=spfileOracle instance shut downconnected to auxiliary database （not started）Oracle instance startedTotal System Global Area 238034944 bytesFixed Size 2227136 bytesVariable Size 180356160 bytesDatabase Buffers 50331648 bytesRedo Buffers 5120000 bytessql statement: CREATE CONTROLFILE REUSE SET DATABASE "ORADU" RESETLOGS ARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 （ "/u01/app/oracle/oradata/oradu/redo01.log", "/u01/app/oracle/oradata/oradu/redo01_01.log" ） SIZE 100 M REUSE, GROUP 2 （ "/u01/app/oracle/oradata/oradu/redo02.log", "/u01/app/oracle/oradata/oradu/redo02_01.log" ） SIZE 100 M REUSE, GROUP 3 （ "/u01/app/oracle/oradata/oradu/redo03.log", "/u01/app/oracle/oradata/oradu/redo03_01.log" ） SIZE 100 M REUSE, GROUP 4 （ "/u01/app/oracle/oradata/oradu/redo04.log", "/u01/app/oracle/oradata/oradu/redo04_01.log" ） SIZE 100 M REUSE DATAFILE "/u01/app/oracle/oradata/oradu/system01.dbf" CHARACTER SET ZHS16GBKcontents of Memory Script:{ set newname for tempfile 1 to "/u01/app/oracle/oradata/oradu/temp01.dbf"; switch clone tempfile all; catalog clone datafilecopy "/u01/app/oracle/oradata/oradu/sysaux01.dbf", "/u01/app/oracle/oradata/oradu/undotbs01.dbf", "/u01/app/oracle/oradata/oradu/users01.dbf"; switch clone datafile all;}executing Memory Scriptexecuting command: SET NEWNAMErenamed tempfile 1 to /u01/app/oracle/oradata/oradu/temp01.dbf in control filecataloged datafile copydatafile copy file name=/u01/app/oracle/oradata/oradu/sysaux01.dbf RECID=1 STAMP=907000517cataloged datafile copydatafile copy file name=/u01/app/oracle/oradata/oradu/undotbs01.dbf RECID=2 STAMP=907000517cataloged datafile copydatafile copy file name=/u01/app/oracle/oradata/oradu/users01.dbf RECID=3 STAMP=907000517datafile 2 switched to datafile copyinput datafile copy RECID=1 STAMP=907000517 file name=/u01/app/oracle/oradata/oradu/sysaux01.dbfdatafile 3 switched to datafile copyinput datafile copy RECID=2 STAMP=907000517 file name=/u01/app/oracle/oradata/oradu/undotbs01.dbfdatafile 4 switched to datafile copyinput datafile copy RECID=3 STAMP=907000517 file name=/u01/app/oracle/oradata/oradu/users01.dbfReenabling controlfile options for auxiliary databaseExecuting: alter database add supplemental log datacontents of Memory Script:{ Alter clone database open resetlogs;}executing Memory Scriptdatabase openedFinished Duplicate Db at 20-MAR-16RMAN>7、验证是否可用[oracle@localhost ~]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.3.0 Production on Sun Mar 20 16:36:52 2016Copyright （c） 1982, 2011, Oracle. All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> select name,open_mode from v$database;NAME OPEN_MODE--------- --------------------ORADU READ WRITE更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址