Oracle11gR2使用RMAN duplicate復制數據庫
2024-08-29 13:54:44
供稿：網友
最近在給客戶做一個項目的數據庫遷移，雖然是一個比較簡單的操作，但由于考慮到停機時間和數據庫安全以及其他因素，項目部搞了很多方案比如導入導出方案，客戶都覺得不是很滿意。所以考慮使用直接把原數據庫進行復制，但現象是兩數據庫版本不同，不知道有沒有影響，以下是在同版本數據庫的操作，供參考學習！11g的RMAN duplicate 個人感覺比10g的先進了很多，10G需要在rman備份的基礎上進行復制，使用 RMAN duplicate 創建一個數據完全相同但DBID不同的數據庫。而11g的RMAN duplicate 可以通過Active database duplicate和Backup-based duplicate兩種方法實現。這里的測試使用的是Active database duplicate，因為Active database duplicate 功能強大，不需要先把目標數據庫進行rman備份，只要目標數據庫處于歸檔模式下即可直接通過網絡對數據庫進行copy，且copy完成后自動open數據庫。這對于大數據特別是T級別的數據庫來說優點非常明顯，復制前不需要進行備份，減少了備份和傳送備份的時間，同時節省備份空間。下面來進行具體的duplicate操作。 1、環境信息。由于是測試，且個人硬件限制，以下測試在同一機器不同數據庫實例操作，和在異機操作沒什么區別。target DBip：192.168.1.1hostname：Oracledbaoraclesid：orcl auxiliary DBip：192.168.1.1hostname：oracledbaoraclesid：oradu2、創建auxiliary DB參數文件，啟動實例到nomount狀態，我們可以使用target DB的pfile進行修改使用，內容不需要太多，有相關目錄能把實例啟動到nomount即可，創建完成后pfile如下：[root@oracledba dbs]# cat initoradu.ora.audit_file_dest='/u01/app/oracle/admin/oradu/adump'*.audit_trail='db'*.compatible='11.2.0.0.0'*.control_files='/u01/app/oracle/oradata/oradu/control01.ctl','/u01/app/oracle/fast_recovery_area/oradu/control02.ctl'*.db_block_size=8192*.db_name='oradu'db_file_name_convert=('/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/oradu/')log_file_name_convert=('/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/oradu/') 由于這里使用的是同一機器上的不同實例，所以必須添加db_file_name_convert和log_file_name_convert，否則在復制的時候會報錯無法創建數據文件，如果是異機復制，且兩數據目錄完全一樣的情況下，這兩個參數可省略，由于測試在同一環境，所以db_name不能一樣，如果是異機復制，db_name完全可以一樣。 創建參數文件中的相關目錄并修改所屬：[root@oracledba dbs]# mkdir /u01/app/oracle/oradata/oradu/ -pvmkdir: created directory `/u01/app/oracle/oradata/oradu/'[root@oracledba dbs]# mkdir /u01/app/oracle/fast_recovery_area/oradu/ -pvmkdir: created directory `/u01/app/oracle/fast_recovery_area/oradu/'chown oracle:oinstall /u01 -R 啟動實例到nomount狀態SQL> startup nomount pfile='/u01/app/oracle/PRoduct/11.2.0/db_1/dbs/initoradu.ora';ORACLE instance started. Total System Global Area 238034944 bytesFixed Size 2227136 bytesVariable Size 180356160 bytesDatabase Buffers 50331648 bytesRedo Buffers 5120000 bytes 3、創建密碼文件，必須保持target DB和auxiliary DB的密碼一致。這里我直接對target DB密碼文件重命名使用[root@oracledba dbs]# cp orapworcl orapworadu 4、配置網絡，原因不解釋，配置完成后如下[root@oracledba dbs]# cat ../network/admin/listener.oraSID_LIST_LISTENER = (SID_LIST = (SID_DESC = (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1) (SID_NAME =orcl) ) (SID_DESC = (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1) (SID_NAME =oradu) ) ) LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oracledba)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) ) ADR_BASE_LISTENER = /u01/app/oracle [root@oracledba dbs]# cat ../network/admin/tnsnames.oraorcl = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oracledba)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl) )) oradu = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oracledba)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = oradu) )) 5、開始復制,復制時需要注意是否使用nofilenamecheck參數，如果兩個實例相關數據目錄結構完全相同，則需要指定，否則會報錯。這里不需要使用。復制日志太多，有興趣的可以看看，或者跳過直接看結果![oracle@oracledba ~]$ export ORACLE_SID=oradu --------異機操作可省略此步操作[oracle@oracledba ~]$rman target sys/oracle@orcl auxiliary sys/oracle@oraduRecovery Manager: Release 11.2.0.3.0 - Production on Sat May 11 23:40:39 2013 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: ORCL (DBID=1342927075)connected to auxiliary database: ORADU (not mount) RMAN>duplicate target database to oradu from active database nofilenamecheck;----------這里應該使用下面命令復制，可以進行對比一下差別。RMAN> duplicate target database to oradu from active database; Starting Duplicate Db at 11-MAY-13using channel ORA_AUX_DISK_1 contents of Memory Script:{ sql clone "create spfile from memory";}executing Memory Script sql statement: create spfile from memory contents of Memory Script:{ shutdown clone immediate; startup clone nomount;}executing Memory Script Oracle instance shut down connected to auxiliary database (not started)Oracle instance started Total System Global Area 238034944 bytes Fixed Size 2227136 bytesVariable Size 180356160 bytesDatabase Buffers 50331648 bytesRedo Buffers 5120000 bytes contents of Memory Script:{ sql clone "alter system set db_name = ''ORCL'' 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 backup as copy current controlfile auxiliary format '/u01/app/oracle/oradata/oradu/control01.ctl'; restore clone controlfile to '/u01/app/oracle/fast_recovery_area/oradu/control02.ctl' from '/u01/app/oracle/oradata/oradu/control01.ctl'; alter clone database mount;}executing Memory Script sql statement: alter system set db_name = ''ORCL'' comment= ''Modified by RMAN duplicate'' scope=spfile sql statement: alter system set db_unique_name = ''ORADU'' comment= ''Modified by RMAN duplicate'' scope=spfile Oracle instance shut down Oracle instance started Total System Global Area 238034944 bytes Fixed Size 2227136 bytesVariable Size 180356160 bytesDatabase Buffers 50331648 bytesRedo Buffers 5120000 bytes Starting backup at 11-MAY-13allocated channel: ORA_DISK_1channel ORA_DISK_1: SID=40 device type=DISKchannel ORA_DISK_1: starting datafile copycopying current control fileoutput file name=/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_orcl.f tag=TAG20130511T224515 RECID=2 STAMP=815179515channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01Finished backup at 11-MAY-13 Starting restore at 11-MAY-13allocated channel: ORA_AUX_DISK_1channel ORA_AUX_DISK_1: SID=18 device type=DISK channel ORA_AUX_DISK_1: copied control file copyFinished restore at 11-MAY-13 database mounted contents of Memory Script:{ 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"; backup as copy reuse datafile 1 auxiliary format "/u01/app/oracle/oradata/oradu/system01.dbf" datafile 2 auxiliary format "/u01/app/oracle/oradata/oradu/sysaux01.dbf" datafile 3 auxiliary format "/u01/app/oracle/oradata/oradu/undotbs01.dbf" datafile 4 auxiliary format "/u01/app/oracle/oradata/oradu/users01.dbf" ; sql 'alter system archive log current';}executing Memory Script executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME Starting backup at 11-MAY-13using channel ORA_DISK_1channel ORA_DISK_1: starting datafile copyinput datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbfoutput file name=/u01/app/oracle/oradata/oradu/system01.dbf tag=TAG20130511T224522channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:37channel ORA_DISK_1: starting datafile copyinput datafile file number=00002 name=/u01/app/oracle/oradata/orcl/sysaux01.dbfoutput file name=/u01/app/oracle/oradata/oradu/sysaux01.dbf tag=TAG20130511T224522channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:37channel ORA_DISK_1: starting datafile copyinput datafile file number=00003 name=/u01/app/oracle/oradata/orcl/undotbs01.dbfoutput file name=/u01/app/oracle/oradata/oradu/undotbs01.dbf tag=TAG20130511T224522channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:08channel ORA_DISK_1: starting datafile copyinput datafile file number=00004 name=/u01/app/oracle/oradata/orcl/users01.dbfoutput file name=/u01/app/oracle/oradata/oradu/users01.dbf tag=TAG20130511T224522channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01Finished backup at 11-MAY-13 sql statement: alter system archive log current contents of Memory Script:{ backup as copy reuse archivelog like "/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2013_05_11/o1_mf_1_14_8rwpgonf_.arc" auxiliary format "/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_14_815146340.dbf" ; catalog clone archivelog "/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_14_815146340.dbf"; switch clone datafile all;}executing Memory Script Starting backup at 11-MAY-13using channel ORA_DISK_1channel ORA_DISK_1: starting archived log copyinput archived log thread=1 sequence=14 RECID=10 STAMP=815179605output file name=/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_14_815146340.dbf RECID=0 STAMP=0channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01Finished backup at 11-MAY-13 cataloged archived logarchived log file name=/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_14_815146340.dbf RECID=10 STAMP=815179607 datafile 1 switched to datafile copyinput datafile copy RECID=2 STAMP=815179607 file name=/u01/app/oracle/oradata/oradu/system01.dbfdatafile 2 switched to datafile copyinput datafile copy RECID=3 STAMP=815179607 file name=/u01/app/oracle/oradata/oradu/sysaux01.dbfdatafile 3 switched to datafile copyinput datafile copy RECID=4 STAMP=815179607 file name=/u01/app/oracle/oradata/oradu/undotbs01.dbfdatafile 4 switched to datafile copyinput datafile copy RECID=5 STAMP=815179607 file name=/u01/app/oracle/oradata/oradu/users01.dbf contents of Memory Script:{ set until scn 1063454; recover clone database delete archivelog ;}executing Memory Script executing command: SET until clause Starting recover at 11-MAY-13using channel ORA_AUX_DISK_1 starting media recovery archived log for thread 1 with sequence 14 is already on disk as file /u01/app/oracle/product/11.2.0/db_1/dbs/arch1_14_815146340.dbfarchived log file name=/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_14_815146340.dbf thread=1 sequence=14media recovery complete, elapsed time: 00:00:00Finished recover at 11-MAY-13Oracle instance started Total System Global Area 238034944 bytes Fixed Size 2227136 bytesVariable Size 180356160 bytesDatabase Buffers 50331648 bytesRedo Buffers 5120000 bytes contents 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 Script sql statement: alter system set db_name = ''ORADU'' comment= ''Reset to original value by RMAN'' scope=spfile sql statement: alter system reset db_unique_name scope=spfile Oracle instance shut down connected to auxiliary database (not started)Oracle instance started Total System Global Area 238034944 bytes Fixed 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' ) SIZE 50 M REUSE, GROUP 2 ( '/u01/app/oracle/oradata/oradu/redo02.log' ) SIZE 50 M REUSE, GROUP 3 ( '/u01/app/oracle/oradata/oradu/redo03.log' ) SIZE 50 M REUSE DATAFILE '/u01/app/oracle/oradata/oradu/system01.dbf' CHARACTER SET AL32UTF8 contents 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 Script executing command: SET NEWNAME renamed tempfile 1 to /u01/app/oracle/oradata/oradu/temp01.dbf in control file cataloged datafile copydatafile copy file name=/u01/app/oracle/oradata/oradu/sysaux01.dbf RECID=1 STAMP=815179616cataloged datafile copydatafile copy file name=/u01/app/oracle/oradata/oradu/undotbs01.dbf RECID=2 STAMP=815179616cataloged datafile copydatafile copy file name=/u01/app/oracle/oradata/oradu/users01.dbf RECID=3 STAMP=815179616 datafile 2 switched to datafile copyinput datafile copy RECID=1 STAMP=815179616 file name=/u01/app/oracle/oradata/oradu/sysaux01.dbfdatafile 3 switched to datafile copyinput datafile copy RECID=2 STAMP=815179616 file name=/u01/app/oracle/oradata/oradu/undotbs01.dbfdatafile 4 switched to datafile copyinput datafile copy RECID=3 STAMP=815179616 file name=/u01/app/oracle/oradata/oradu/users01.dbf contents of Memory Script:{ Alter clone database open resetlogs;}executing Memory Script database openedFinished Duplicate Db at 11-MAY-13數據庫復制完成。 6、檢查結果RMAN> exit Recovery Manager complete.[oracle@oracledba ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Sat May 11 22:48:08 2013 Copyright (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 options SQL> select name from v$datafile; NAME--------------------------------------------------------------------------------/u01/app/oracle/oradata/oradu/system01.dbf/u01/app/oracle/oradata/oradu/sysaux01.dbf/u01/app/oracle/oradata/oradu/undotbs01.dbf/u01/app/oracle/oradata/oradu/users01.dbf SQL> select status from v$instance; STATUS------------OPEN SQL> show parameter name NAME TYPE VALUE------------------------------------ ----------- ------------------------------db_file_name_convert string /u01/app/oracle/oradata/orcl/, /u01/app/oracle/oradata/oradu/ db_name string ORADUdb_unique_name string ORADUglobal_names boolean FALSEinstance_name string oradulock_name_space stringlog_file_name_convert string /u01/app/oracle/oradata/orcl/, /u01/app/oracle/oradata/oradu/ NAME TYPE VALUE------------------------------------ ----------- ------------------------------processor_group_name stringservice_names string ORADUSQL> show parameter pfile NAME TYPE VALUE------------------------------------ ----------- ------------------------------spfile string /u01/app/oracle/product/11.2.0 /db_1/dbs/spfileoradu.ora數據庫已經open，各項參數正常，且已創建spfile并使用！整個復制過程完成！
