本文共 20344 字，大约阅读时间需要 67 分钟。
使用RMAN duplicate database from file System to ASM
测试环境如下: 1.使用了恢复目录数据库jy 2.目标数据库test在主机oracle11g上，并且数据文件是存储在文件系统中 3.将目标数据库test复制到远程主机jingyong1上生成数据库dupdb。 4.主机jingyong1上有ASM磁盘组+dg1 5.将数据库dupdb的数据文件存储在磁盘组+dg1中 6.在磁盘组+dg1中存储两份控制文件 7.在主机oracle11g上创建的备份与归档重做日志必须能被主机jingyong1访问执行步骤如下:
1.创建辅助实例的密码文件(这里辅助实例名为dupdb)[oracle@jingyong1 dbs]$ orapwd file=/u01/app/oracle/product/10.2.0/db/dbs/orapwdupdb password=oracle entries=10[oracle@jingyong1 dbs]$ ls -lrt orapwdupdb-rw-r----- 1 oracle oinstall 2560 Mar 25 20:49 orapwdupdb
2.创建辅助实例网络连接,修改监听文件，使用静态监听来监听辅助实例远程主机jingyong1
[oracle@jingyong1 admin]$ vi listener.oraSID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = dupdb) (ORACLE_HOME =/u01/app/oracle/product/10.2.0/db) ) )LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.11)(PORT = 1521)) ) )
给辅助实例增加网络服务名
[oracle@jingyong1 admin]$ vi tnsnames.oradupdb = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.56.11)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME =dupdb) (UR=A) ) )
在目标主机上给辅助实例增加网络服务名
[oracle@oracle11g admin]$ vi tnsnames.oradupdb = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.56.11)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME =dupdb) (UR=A) ) )
测试网络连接
远程主机jingyong1[oracle@jingyong1 admin]$ export ORACLE_SID=dupdb[oracle@jingyong1 admin]$ sqlplus /nologSQL*Plus: Release 10.2.0.5.0 - Production on Wed Mar 25 20:53:26 2015Copyright (c) 1982, 2010, Oracle. All Rights Reserved.SQL> conn sys/oracle@dupdb as sysdbaConnected to an idle instance.
目标主机oracle11g
[oracle@oracle11g admin]$ sqlplus /nologSQL*Plus: Release 10.2.0.5.0 - Production on Wed Mar 25 21:00:13 2015Copyright (c) 1982, 2010, Oracle. All Rights Reserved.SQL> conn sys/oracle@dupdb as sysdbaConnected to an idle instance.
3.创建辅助实例的参数文件
[oracle@jingyong1 dbs]$ vi initdupdb.oradb_name=dupdbdb_unique_name=dupdbcontrol_files=('+DG1/control01.ctl','+DG1/control02.ctl')db_file_name_convert=('/u01/app/oracle/oradata/test/','+DG1/')log_file_name_convert=('/u01/app/oracle/oradata/test/','+DG1/')remote_login_passwordfile=exclusivecompatible = 10.2.0.5.0db_block_size=8192sga_target=160Msga_max_size=160Mpga_aggregate_target=16M[oracle@jingyong1 dbs]$ export ORACLE_SID=dupdb[oracle@jingyong1 dbs]$ sqlplus / as sysdbaSQL*Plus: Release 10.2.0.5.0 - Production on Wed Mar 25 21:08:14 2015Copyright (c) 1982, 2010, Oracle. All Rights Reserved.Connected to an idle instance.SQL> create spfile from pfile='/u01/app/oracle/product/10.2.0/db/dbs/initdupdb.ora';File created. 4.启动辅助实例
[oracle@jingyong1 dbs]$ export ORACLE_SID=dupdb[oracle@jingyong1 dbs]$ sqlplus / as sysdbaSQL*Plus: Release 10.2.0.5.0 - Production on Wed Mar 25 21:10:09 2015Copyright (c) 1982, 2010, Oracle. All Rights Reserved.Connected to an idle instance.SQL> startup nomountORACLE instance started.Total System Global Area 167772160 bytesFixed Size 1272624 bytesVariable Size 58721488 bytesDatabase Buffers 104857600 bytesRedo Buffers 2920448 bytesSQL> set long 300SQL> set linesize 300SQL> show parameter spfileNAME TYPE VALUE------------------------------------ ---------------------- ------------------------------spfile string /u01/app/oracle/product/10.2.0 /db/dbs/spfiledupdb.ora
断开连接
SQL> exitDisconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options
这里一定要断开启动辅助实例的会话否则在执行复制操作时会出现如下错误 ：
executing Memory ScriptRMAN-00571: ===========================================================RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-03002: failure of Duplicate Db command at 03/24/2015 18:28:32RMAN-03015: error occurred in stored script Memory ScriptRMAN-06136: ORACLE error from auxiliary database: ORA-01013: user requested cancel of current operation
5.加载或打开目标数据库
[oracle@oracle11g ~]$ sqlplus /nologSQL*Plus: Release 10.2.0.5.0 - Production on Tue Mar 24 21:13:44 2015Copyright (c) 1982, 2010, Oracle. All Rights Reserved.SQL> conn / as sysdbaConnected to an idle instance.SQL> startupORACLE instance started.Total System Global Area 167772160 bytesFixed Size 1272600 bytesVariable Size 92275944 bytesDatabase Buffers 71303168 bytesRedo Buffers 2920448 bytesDatabase mounted.Database opened.
6.确保有需要的备份和归档重做日志,对目标数据库（被复制的数据库）进行备份（包含数据文件和归档重做日志）
[oracle@oracle11g admin]$ export NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss'[oracle@oracle11g admin]$ rman target sys/zzh_2046@test catalog rman/rman@jyRecovery Manager: Release 10.2.0.5.0 - Production on Tue Mar 24 15:15:52 2015Copyright (c) 1982, 2007, Oracle. All rights reserved.connected to target database: TEST (DBID=2168949517)connected to recovery catalog databaseRMAN> backup as backupset database plus archivelog delete all input;Starting backup at 25-MAR-15current log archivedusing channel ORA_DISK_1channel ORA_DISK_1: starting archive log backupsetchannel ORA_DISK_1: specifying archive log(s) in backup setinput archive log thread=1 sequence=65 recid=72 stamp=875262973input archive log thread=1 sequence=66 recid=73 stamp=875266238channel ORA_DISK_1: starting piece 1 at 25-MAR-15channel ORA_DISK_1: finished piece 1 at 25-MAR-15piece handle=/u02/ora_test875266246_751 tag=TAG20150325T093040 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:04channel ORA_DISK_1: deleting archive log(s)archive log filename=/u02/1_65_870806981.dbf recid=72 stamp=875262973archive log filename=/u02/1_66_870806981.dbf recid=73 stamp=875266238Finished backup at 25-MAR-15Starting backup at 25-MAR-15using channel ORA_DISK_1channel ORA_DISK_1: starting full datafile backupsetchannel ORA_DISK_1: specifying datafile(s) in backupsetinput datafile fno=00001 name=/u01/app/oracle/oradata/test/system01.dbfinput datafile fno=00003 name=/u01/app/oracle/oradata/test/sysaux01.dbfinput datafile fno=00005 name=/u01/app/oracle/oradata/test/example01.dbfinput datafile fno=00006 name=/u01/app/oracle/oradata/test/tspitr01.dbfinput datafile fno=00002 name=/u01/app/oracle/oradata/test/undotbs01.dbfinput datafile fno=00004 name=/u01/app/oracle/oradata/test/users01.dbfchannel ORA_DISK_1: starting piece 1 at 25-MAR-15channel ORA_DISK_1: finished piece 1 at 25-MAR-15piece handle=/u02/ora_test875266253_761 tag=TAG20150325T093052 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:02:46Finished backup at 25-MAR-15Starting backup at 25-MAR-15current log archivedusing channel ORA_DISK_1channel ORA_DISK_1: starting archive log backupsetchannel ORA_DISK_1: specifying archive log(s) in backup setinput archive log thread=1 sequence=67 recid=74 stamp=875266422channel ORA_DISK_1: starting piece 1 at 25-MAR-15channel ORA_DISK_1: finished piece 1 at 25-MAR-15piece handle=/u02/ora_test875266425_771 tag=TAG20150325T093344 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:02channel ORA_DISK_1: deleting archive log(s)archive log filename=/u02/1_67_870806981.dbf recid=74 stamp=875266422Finished backup at 25-MAR-15Starting Control File and SPFILE Autobackup at 25-MAR-15piece handle=/u01/app/oracle/flash_recovery_area/TEST/autobackup/2015_03_25/o1_mf_s_875266428_bk4441pd_.bkp comment=NONEFinished Control File and SPFILE Autobackup at 25-MAR-15RMAN> list backup;List of Backup Sets===================BS Key Size Device Type Elapsed Time Completion Time------- ---------- ----------- ------------ ---------------4782 5.72M DISK 00:00:04 25-MAR-15 BP Key: 4783 Status: AVAILABLE Compressed: NO Tag: TAG20150325T093040 Piece Name: /u02/ora_test875266246_751 List of Archived Logs in backup set 4782 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------- 1 65 1009334 24-MAR-15 1038362 25-MAR-15 1 66 1038362 25-MAR-15 1040219 25-MAR-15BS Key Type LV Size Device Type Elapsed Time Completion Time------- ---- -- ---------- ----------- ------------ ---------------4794 Full 624.76M DISK 00:02:40 25-MAR-15 BP Key: 4797 Status: AVAILABLE Compressed: NO Tag: TAG20150325T093052 Piece Name: /u02/ora_test875266253_761 List of Datafiles in backup set 4794 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- 1 Full 1040242 25-MAR-15 /u01/app/oracle/oradata/test/system01.dbf 2 Full 1040242 25-MAR-15 /u01/app/oracle/oradata/test/undotbs01.dbf 3 Full 1040242 25-MAR-15 /u01/app/oracle/oradata/test/sysaux01.dbf 4 Full 1040242 25-MAR-15 /u01/app/oracle/oradata/test/users01.dbf 5 Full 1040242 25-MAR-15 /u01/app/oracle/oradata/test/example01.dbf 6 Full 1040242 25-MAR-15 /u01/app/oracle/oradata/test/tspitr01.dbfBS Key Size Device Type Elapsed Time Completion Time------- ---------- ----------- ------------ ---------------4811 12.50K DISK 00:00:01 25-MAR-15 BP Key: 4818 Status: AVAILABLE Compressed: NO Tag: TAG20150325T093344 Piece Name: /u02/ora_test875266425_771 List of Archived Logs in backup set 4811 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------- 1 67 1040219 25-MAR-15 1040304 25-MAR-15BS Key Type LV Size Device Type Elapsed Time Completion Time------- ---- -- ---------- ----------- ------------ ---------------4827 Full 6.89M DISK 00:00:06 25-MAR-15 BP Key: 4829 Status: AVAILABLE Compressed: NO Tag: TAG20150325T093348 Piece Name: /u01/app/oracle/flash_recovery_area/TEST/autobackup/2015_03_25/o1_mf_s_875266428_bk4441pd_.bkp Control File Included: Ckp SCN: 1040326 Ckp time: 25-MAR-15 SPFILE Included: Modification time: 25-MAR-15
将上面的备份传输到远程主机的相同目录中：
[oracle@jingyong1 dup]$ scp -r oracle@192.168.56.2:/u02/ora_test875266246_751 /u02oracle@192.168.56.2's password:ora_test875266246_751 100% 5857KB 5.7MB/s 00:00[oracle@jingyong1 dup]$ scp -r oracle@192.168.56.2:/u02/ora_test875266253_761 /u02oracle@192.168.56.2's password:ora_test875266253_761 100% 625MB 8.1MB/s 01:17[oracle@jingyong1 dup]$ scp -r oracle@192.168.56.2:/u02/ora_test875266425_771 /u02oracle@192.168.56.2's password:ora_test875266425_771 100% 13KB 13.0KB/s 00:00[oracle@jingyong1 dup]$ scp -r oracle@192.168.56.2:/u01/app/oracle/flash_recovery_area/TEST/autobackup/2015_03_25/o1_mf_s_875266428_bk4441pd_.bkp /u01/app/oracle/flash_recovery_area/TEST/autobackup/2015-03-25oracle@192.168.56.2's password:o1_mf_s_875266428_bk4441pd_.bkp
检查磁盘组+DG1的空间使用情况,要有足够的空间存储复制数据库的所有文件
ASMCMD> lsdgState Type Rebal Unbal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks NameMOUNTED EXTERN N N 512 4096 1048576 4096 4045 0 4045 0 DG1/
7.执行duplicate命令，如果没有配置自动通道，那么至少手动分配一个辅助实例如果是使用PFILE参数文件启动辅助实例需要指定pfile参数文件，且pfile参数文件必须存储在运行RMAN执行复制的主机上。这里辅助实例使用SPFILE参数文件来启动，并使用自动通道:
[oracle@oracle11g admin]$ export NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss'[oracle@oracle11g admin]$ rman target sys/zzh_2046@test auxiliary sys/oracle@dupdb catalog rman/rman@jyRecovery Manager: Release 10.2.0.5.0 - Production on Wed Mar 25 21:26:07 2015Copyright (c) 1982, 2007, Oracle. All rights reserved.connected to target database: TEST (DBID=2168949517)connected to recovery catalog databaseconnected to auxiliary database: DUPDB (not mounted)RMAN> duplicate target database to dupdb;Starting Duplicate Db at 2015-03-25 21:26:22allocated channel: ORA_AUX_DISK_1channel ORA_AUX_DISK_1: sid=35 devtype=DISKcontents of Memory Script:{ set until scn 1093434; set newname for datafile 1 to "+DG1/system01.dbf"; set newname for datafile 2 to "+DG1/undotbs01.dbf"; set newname for datafile 3 to "+DG1/sysaux01.dbf"; set newname for datafile 4 to "+DG1/users01.dbf"; set newname for datafile 5 to "+DG1/example01.dbf"; set newname for datafile 6 to "+DG1/tspitr01.dbf"; restore check readonly clone database ;}executing Memory Scriptexecuting command: SET until clauseexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEexecuting command: SET NEWNAMEStarting restore at 2015-03-25 21:26:23using channel ORA_AUX_DISK_1channel ORA_AUX_DISK_1: starting datafile backupset restorechannel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup setrestoring datafile 00001 to +DG1/system01.dbfrestoring datafile 00002 to +DG1/undotbs01.dbfrestoring datafile 00003 to +DG1/sysaux01.dbfrestoring datafile 00004 to +DG1/users01.dbfrestoring datafile 00005 to +DG1/example01.dbfrestoring datafile 00006 to +DG1/tspitr01.dbfchannel ORA_AUX_DISK_1: reading from backup piece /u02/ora_test875266253_761channel ORA_AUX_DISK_1: restored backup piece 1piece handle=/u02/ora_test875266253_761 tag=TAG20150325T093052channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:05Finished restore at 2015-03-25 21:27:32sql statement: CREATE CONTROLFILE REUSE SET DATABASE "DUPDB" RESETLOGS ARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 ( '+DG1/redo01.log' ) SIZE 50 M REUSE, GROUP 2 ( '+DG1/redo02.log' ) SIZE 50 M REUSE, GROUP 3 ( '+DG1/redo03.log' ) SIZE 50 M REUSE DATAFILE '+DG1/system01.dbf' CHARACTER SET ZHS16GBKcontents of Memory Script:{ switch clone datafile all;}executing Memory Scriptdatafile 2 switched to datafile copyinput datafile copy recid=1 stamp=875309253 filename=+DG1/undotbs01.dbfdatafile 3 switched to datafile copyinput datafile copy recid=2 stamp=875309253 filename=+DG1/sysaux01.dbfdatafile 4 switched to datafile copyinput datafile copy recid=3 stamp=875309253 filename=+DG1/users01.dbfdatafile 5 switched to datafile copyinput datafile copy recid=4 stamp=875309253 filename=+DG1/example01.dbfdatafile 6 switched to datafile copyinput datafile copy recid=5 stamp=875309253 filename=+DG1/tspitr01.dbfcontents of Memory Script:{ set until scn 1093434; recover clone database delete archivelog ;}executing Memory Scriptexecuting command: SET until clauseStarting recover at 2015-03-25 21:27:37using channel ORA_AUX_DISK_1starting media recoverychannel ORA_AUX_DISK_1: starting archive log restore to default destinationchannel ORA_AUX_DISK_1: restoring archive logarchive log thread=1 sequence=67channel ORA_AUX_DISK_1: reading from backup piece /u02/ora_test875266425_771channel ORA_AUX_DISK_1: restored backup piece 1piece handle=/u02/ora_test875266425_771 tag=TAG20150325T093344channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02archive log filename=/u01/app/oracle/product/10.2.0/db/dbs/arch1_67_870806981.dbf thread=1 sequence=67channel clone_default: deleting archive log(s)archive log filename=/u01/app/oracle/product/10.2.0/db/dbs/arch1_67_870806981.dbf recid=1 stamp=875309258channel ORA_AUX_DISK_1: starting archive log restore to default destinationchannel ORA_AUX_DISK_1: restoring archive logarchive log thread=1 sequence=68channel ORA_AUX_DISK_1: restoring archive logarchive log thread=1 sequence=69channel ORA_AUX_DISK_1: reading from backup piece /u02/ora_test875292006_791channel ORA_AUX_DISK_1: restored backup piece 1piece handle=/u02/ora_test875292006_791 tag=TAG20150325T164005channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03archive log filename=/u01/app/oracle/product/10.2.0/db/dbs/arch1_68_870806981.dbf thread=1 sequence=68channel clone_default: deleting archive log(s)archive log filename=/u01/app/oracle/product/10.2.0/db/dbs/arch1_68_870806981.dbf recid=3 stamp=875309261archive log filename=/u01/app/oracle/product/10.2.0/db/dbs/arch1_69_870806981.dbf thread=1 sequence=69channel clone_default: deleting archive log(s)archive log filename=/u01/app/oracle/product/10.2.0/db/dbs/arch1_69_870806981.dbf recid=2 stamp=875309260channel ORA_AUX_DISK_1: starting archive log restore to default destinationchannel ORA_AUX_DISK_1: restoring archive logarchive log thread=1 sequence=70channel ORA_AUX_DISK_1: restoring archive logarchive log thread=1 sequence=71channel ORA_AUX_DISK_1: reading from backup piece /u02/ora_test875308493_811channel ORA_AUX_DISK_1: restored backup piece 1piece handle=/u02/ora_test875308493_811 tag=TAG20150325T211453channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:04archive log filename=/u01/app/oracle/product/10.2.0/db/dbs/arch1_70_870806981.dbf thread=1 sequence=70channel clone_default: deleting archive log(s)archive log filename=/u01/app/oracle/product/10.2.0/db/dbs/arch1_70_870806981.dbf recid=4 stamp=875309268archive log filename=/u01/app/oracle/product/10.2.0/db/dbs/arch1_71_870806981.dbf thread=1 sequence=71channel clone_default: deleting archive log(s)archive log filename=/u01/app/oracle/product/10.2.0/db/dbs/arch1_71_870806981.dbf recid=5 stamp=875309268media recovery complete, elapsed time: 00:00:04Finished recover at 2015-03-25 21:27:57contents of Memory Script:{ shutdown clone; startup clone nomount ;}executing Memory Scriptdatabase dismountedOracle instance shut downconnected to auxiliary database (not started)Oracle instance startedTotal System Global Area 167772160 bytesFixed Size 1272624 bytesVariable Size 58721488 bytesDatabase Buffers 104857600 bytesRedo Buffers 2920448 bytessql statement: CREATE CONTROLFILE REUSE SET DATABASE "DUPDB" RESETLOGS ARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 ( '+DG1/redo01.log' ) SIZE 50 M REUSE, GROUP 2 ( '+DG1/redo02.log' ) SIZE 50 M REUSE, GROUP 3 ( '+DG1/redo03.log' ) SIZE 50 M REUSE DATAFILE '+DG1/system01.dbf' CHARACTER SET ZHS16GBKcontents of Memory Script:{ set newname for tempfile 1 to "+DG1/temp01.dbf"; switch clone tempfile all; catalog clone datafilecopy "+DG1/undotbs01.dbf"; catalog clone datafilecopy "+DG1/sysaux01.dbf"; catalog clone datafilecopy "+DG1/users01.dbf"; catalog clone datafilecopy "+DG1/example01.dbf"; catalog clone datafilecopy "+DG1/tspitr01.dbf"; switch clone datafile all;}executing Memory Scriptexecuting command: SET NEWNAMErenamed temporary file 1 to +DG1/temp01.dbf in control filecataloged datafile copydatafile copy filename=+DG1/undotbs01.dbf recid=1 stamp=875309291cataloged datafile copydatafile copy filename=+DG1/sysaux01.dbf recid=2 stamp=875309291cataloged datafile copydatafile copy filename=+DG1/users01.dbf recid=3 stamp=875309291cataloged datafile copydatafile copy filename=+DG1/example01.dbf recid=4 stamp=875309292cataloged datafile copydatafile copy filename=+DG1/tspitr01.dbf recid=5 stamp=875309292datafile 2 switched to datafile copyinput datafile copy recid=1 stamp=875309291 filename=+DG1/undotbs01.dbfdatafile 3 switched to datafile copyinput datafile copy recid=2 stamp=875309291 filename=+DG1/sysaux01.dbfdatafile 4 switched to datafile copyinput datafile copy recid=3 stamp=875309291 filename=+DG1/users01.dbfdatafile 5 switched to datafile copyinput datafile copy recid=4 stamp=875309292 filename=+DG1/example01.dbfdatafile 6 switched to datafile copyinput datafile copy recid=5 stamp=875309292 filename=+DG1/tspitr01.dbfcontents of Memory Script:{ Alter clone database open resetlogs;}executing Memory Scriptdatabase openedFinished Duplicate Db at 2015-03-25 21:28:33 来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26015009/viewspace-1475226/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/26015009/viewspace-1475226/