在不同機器之間使用rman複製資料庫例項，從非asm到asm

下面演示在不同機器之間使用rman複製資料庫例項，從非asm到asm的過程。

參考：metalink node.382669.1

1、環境說明：

源資料庫：

主機：host_a ip：192.168.0.200 os：CentOS 5 x86 oracle版本：10.2.0.1 sid：cnhtm 使用檔案系統

目標資料庫,要透過複製（Duplicate）生成的資料庫：

主機：host_b ip：192.168.0.100 os：CentOS 5 x86 oracle版本：10.2.0.1 sid：aux 使用asm（資料庫例項不存在，需要Duplicate後生成）

[@more@]

2、操作過程

2.1、host_a上使用rman備份資料庫

ora_test@oracle[/home/oracle]> export ORACLE_SID=cnhtm ora_test@oracle[/home/oracle]> rman target / Recovery Manager: Release 10.2.0.1.0 - Production on Fri Jan 22 09:12:58 2010 Copyright (c) 1982, 2005, Oracle. All rights reserved. connected to target database: CNHTM (DBID=1436452454) RMAN> run { 2> allocate channel d1 type disk; 3> backup format '/oradata/backups/df_t%t_s%s_p%p' database; 4> sql 'alter system archive log current'; 5> backup format '/oradata/backups/al_t%t_s%s_p%p' archivelog all; 6> backup format '/oradata/backups/cf_t%t_s%s_p%p' current controlfile; 7> release channel d1; 8> } using target database control file instead of recovery catalog allocated channel: d1 channel d1: sid=139 devtype=DISK Starting backup at 22-JAN-10 channel d1: starting full datafile backupset channel d1: specifying datafile(s) in backupset input datafile fno=00001 name=/oradata/cnhtm/system01.dbf input datafile fno=00003 name=/oradata/cnhtm/sysaux01.dbf input datafile fno=00005 name=/oradata/cnhtm/example01.dbf input datafile fno=00002 name=/oradata/cnhtm/undotbs01.dbf input datafile fno=00004 name=/oradata/cnhtm/users01.dbf channel d1: starting piece 1 at 22-JAN-10 channel d1: finished piece 1 at 22-JAN-10 piece handle=/oradata/backups/df_t708945230_s5_p1 tag=TAG20100122T091349 comment=NONE channel d1: backup set complete, elapsed time: 00:01:56 channel d1: starting full datafile backupset channel d1: specifying datafile(s) in backupset including current control file in backupset including current SPFILE in backupset channel d1: starting piece 1 at 22-JAN-10 channel d1: finished piece 1 at 22-JAN-10 piece handle=/oradata/backups/df_t708945347_s6_p1 tag=TAG20100122T091349 comment=NONE channel d1: backup set complete, elapsed time: 00:00:04 Finished backup at 22-JAN-10 sql statement: alter system archive log current Starting backup at 22-JAN-10 current log archived channel d1: starting archive log backupset channel d1: specifying archive log(s) in backup set input archive log thread=1 sequence=2 recid=1 stamp=708945368 input archive log thread=1 sequence=3 recid=2 stamp=708945370 channel d1: starting piece 1 at 22-JAN-10 channel d1: finished piece 1 at 22-JAN-10 piece handle=/oradata/backups/al_t708945373_s7_p1 tag=TAG20100122T091610 comment=NONE channel d1: backup set complete, elapsed time: 00:00:16 Finished backup at 22-JAN-10 Starting backup at 22-JAN-10 channel d1: starting full datafile backupset channel d1: specifying datafile(s) in backupset including current control file in backupset channel d1: starting piece 1 at 22-JAN-10 channel d1: finished piece 1 at 22-JAN-10 piece handle=/oradata/backups/cf_t708945393_s8_p1 tag=TAG20100122T091633 comment=NONE channel d1: backup set complete, elapsed time: 00:00:03 Finished backup at 22-JAN-10 released channel: d1 RMAN> exit Recovery Manager complete.

2.2、在host_a上，ftp剛才的rman備份到host_b的相同目錄

ora_test@oracle[/home/oracle]> cd /oradata/backups ora_test@oracle[/oradata/backups]> ls -l total 670400 -rw-r----- 1 oracle oinstall 48597504 Jan 22 09:16 al_t708945373_s7_p1 -rw-r----- 1 oracle oinstall 7110656 Jan 22 09:16 cf_t708945393_s8_p1 -rw-r----- 1 oracle oinstall 622944256 Jan 22 09:15 df_t708945230_s5_p1 -rw-r----- 1 oracle oinstall 7143424 Jan 22 09:15 df_t708945347_s6_p1 ora_test@oracle[/oradata/backups]> sftp 192.168.0.100 Connecting to 192.168.0.100... The authenticity of host '192.168.0.100 (192.168.0.100)' can't be established. RSA key fingerprint is d3:ab:26:1f:ea:f5:c5:6e:8e:f4:2e:00:3e:b3:3e:65. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added '192.168.0.100' (RSA) to the list of known hosts. oracle@192.168.0.100's password: sftp> cd /oradata/backups sftp> mput * Uploading al_t708945373_s7_p1 to /oradata/backups/al_t708945373_s7_p1 al_t708945373_s7_p1 100% 46MB 708.3KB/s 01:07 Uploading cf_t708945393_s8_p1 to /oradata/backups/cf_t708945393_s8_p1 cf_t708945393_s8_p1 100% 6944KB 1.7MB/s 00:04 Uploading df_t708945230_s5_p1 to /oradata/backups/df_t708945230_s5_p1 df_t708945230_s5_p1 100% 594MB 1.9MB/s 05:16 Uploading df_t708945347_s6_p1 to /oradata/backups/df_t708945347_s6_p1 df_t708945347_s6_p1 100% 6976KB 6.8MB/s 00:01 sftp> exit

2.3、在host_a上，ftp備份過程中生成的歸檔日誌(archive log)到host_b的相同目錄

ora_test@oracle[/oradata/backups]> cd $ORACLE_BASE/flash_recovery_area/CNHTM/archivelog/2010_01_22 ora_test@oracle[/oracle/flash_recovery_area/CNHTM/archivelog/2010_01_22]> ls -l total 47516 -rw-r----- 1 oracle oinstall 48595456 Jan 22 09:16 o1_mf_1_2_5okz2d4w_.arc -rw-r----- 1 oracle oinstall 1024 Jan 22 09:16 o1_mf_1_3_5okz2t33_.arc ora_test@oracle[/oracle/flash_recovery_area/CNHTM/archivelog/2010_01_22]> sftp 192.168.0.100 Connecting to 192.168.0.100... oracle@192.168.0.100's password: sftp> cd /oracle/flash_recovery_area/CNHTM/archivelog/2010_01_22 sftp> mput * Uploading o1_mf_1_2_5okz2d4w_.arc to /oracle/flash_recovery_area/CNHTM/archivelog/2010_01_22/o1_mf_1_2_5okz2d4w_.arc o1_mf_1_2_5okz2d4w_.arc 100% 46MB 6.6MB/s 00:07 Uploading o1_mf_1_3_5okz2t33_.arc to /oracle/flash_recovery_area/CNHTM/archivelog/2010_01_22/o1_mf_1_3_5okz2t33_.arc o1_mf_1_3_5okz2t33_.arc 100% 1024 1.0KB/s 00:00 sftp> exit

2.4、在host_b上，準備trace目錄

ora_test@oracle[/home/oracle]> cd $ORACLE_BASE/admin/ ora_test@oracle[/oracle/admin]> mkdir aux ora_test@oracle[/oracle/admin]> cd aux ora_test@oracle[/oracle/admin/aux]> mkdir bdump udump adump cdump

2.5、在host_b上，建立aux庫的pfile檔案

ora_test@oracle[/oracle/admin/aux]> cd $ORACLE_HOME/dbs/ ora_test@oracle[/oracle/app/10.1/dbs]> cat initaux.ora #各種trac檔案的目錄，目錄需要提前準備好 audit_file_dest =/oracle/admin/aux/adump background_dump_dest =/oracle/admin/aux/bdump core_dump_dest =/oracle/admin/aux/cdump user_dump_dest =/oracle/admin/aux/udump #資料庫名和例項名 db_name ="aux" instance_name =aux #複製資料庫的控制檔名，檔案現在不存在，會在複製過程中生成 control_files =/oradata/aux/control01.ctl #檔名轉換規則： #源資料庫使用檔案系統，資料檔案在/oradata/cnhtm目錄 #目標資料庫計劃使用asm，diskgroup名稱為DATA db_file_name_convert =("/oradata/cnhtm", "+DATA/cnhtm") log_file_name_convert =("/oradata/cnhtm", "+DATA/cnhtm") #下面這幾個引數與源資料庫相同 undo_management =AUTO undo_retention =10800 undo_tablespace =UNDOTBS1 db_block_size = 8192 compatible = 10.2.0.1.0 shared_pool_size=62914560 ora_test@oracle[/oracle/app/10.1/dbs]>

2.6、在host_b上啟動aux資料庫到nomount狀態

ora_test@oracle[/oradata/aux]> export ORACLE_SID=aux ora_test@oracle[/oradata/aux]> sqlplus / as sysdba SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jan 22 09:48:26 2010 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to an idle instance. idle> startup nomount ORACLE instance started. Total System Global Area 117440512 bytes Fixed Size 1218004 bytes Variable Size 58722860 bytes Database Buffers 50331648 bytes Redo Buffers 7168000 bytes idle> exit Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options

2.7、在host_b上配置tnsnames

編輯tnsnames檔案，配置兩個服務名：
cnhtm為連線到源資料庫例項的服務名
aux為連結到目標資料庫的服務名

ora_test@oracle[/oradata/aux]> cd $ORACLE_HOME/network/admin ora_test@oracle[/oracle/app/10.1/network/admin]> cat tnsnames.ora # tnsnames.ora Network Configuration File: /oracle/app/10.1/network/admin/tnsnames.ora # Generated by Oracle configuration tools. EXTPROC_CONNECTION_DATA = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0)) ) (CONNECT_DATA = (SID = PLSExtProc) (PRESENTATION = RO) ) ) #連線到源資料庫的服務名 cnhtm = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.200)(PORT = 1521)) ) (CONNECT_DATA = (sid = cnhtm) ) ) #連線到目標資料庫的服務名 aux = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.100)(PORT = 1521)) ) (CONNECT_DATA = (sid = aux) ) ) ora_test@oracle[/oracle/app/10.1/network/admin]>

2.8、在host_b上測試配置的服務名

確保host_a和host_b上的監聽處於啟動狀態，然後使用如下命令測試

ora_test@oracle[/oracle/app/10.1/network/admin]> tnsping cnhtm TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 22-JAN-2010 09:56:20 Copyright (c) 1997, 2005, Oracle. All rights reserved. Used parameter files: Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.200)(PORT = 1521))) (CONNECT_DATA = (sid = cnhtm))) OK (20 msec)

2.9、在host_b上準備複製指令碼

ora_test@oracle[/oracle/app/10.1/dbs]> cd

建立cr_duplicate.rcv檔案，其內容如下

ora_test@oracle[/home/oracle]> cat cr_duplicate.rcv run { allocate auxiliary channel C1 device type disk; duplicate target database to aux; }

2.10、在host_b上啟動rman，複製資料庫

ora_test@oracle[/home/oracle]> export ORACLE_SID=aux ora_test@oracle[/home/oracle]> rman target sys/oracle@cnhtm nocatalog auxiliary / Recovery Manager: Release 10.2.0.1.0 - Production on Fri Jan 22 14:16:45 2010 Copyright (c) 1982, 2005, Oracle. All rights reserved. connected to target database: CNHTM (DBID=1436452454) using target database control file instead of recovery catalog connected to auxiliary database: AUX (not mounted) RMAN> @cr_duplicate.rcv RMAN> run { 2> allocate auxiliary channel C1 device type disk; 3> duplicate target database to aux; 4> } allocated channel: C1 channel C1: sid=36 devtype=DISK Starting Duplicate Db at 22-JAN-10 contents of Memory Script: { set until scn 528447; set newname for datafile 1 to "+DATA/cnhtm/system01.dbf"; set newname for datafile 2 to "+DATA/cnhtm/undotbs01.dbf"; set newname for datafile 3 to "+DATA/cnhtm/sysaux01.dbf"; set newname for datafile 4 to "+DATA/cnhtm/users01.dbf"; set newname for datafile 5 to "+DATA/cnhtm/example01.dbf"; restore check readonly clone database ; } executing Memory Script executing command: SET until clause executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME Starting restore at 22-JAN-10 channel C1: starting datafile backupset restore channel C1: specifying datafile(s) to restore from backup set restoring datafile 00001 to +DATA/cnhtm/system01.dbf restoring datafile 00002 to +DATA/cnhtm/undotbs01.dbf restoring datafile 00003 to +DATA/cnhtm/sysaux01.dbf restoring datafile 00004 to +DATA/cnhtm/users01.dbf restoring datafile 00005 to +DATA/cnhtm/example01.dbf channel C1: reading from backup piece /oradata/backups/df_t708945230_s5_p1 channel C1: restored backup piece 1 piece handle=/oradata/backups/df_t708945230_s5_p1 tag=TAG20100122T091349 channel C1: restore complete, elapsed time: 00:01:16 Finished restore at 22-JAN-10 sql statement: CREATE CONTROLFILE REUSE SET DATABASE "AUX" RESETLOGS ARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 ( '+DATA/cnhtm/redo01.log' ) SIZE 100 M REUSE, GROUP 2 ( '+DATA/cnhtm/redo02.log' ) SIZE 100 M REUSE, GROUP 3 ( '+DATA/cnhtm/redo03.log' ) SIZE 100 M REUSE DATAFILE '+DATA/cnhtm/system01.dbf' CHARACTER SET ZHS16GBK contents of Memory Script: { switch clone datafile all; } executing Memory Script datafile 2 switched to datafile copy input datafile copy recid=1 stamp=708963523 filename=+DATA/cnhtm/undotbs01.dbf datafile 3 switched to datafile copy input datafile copy recid=2 stamp=708963523 filename=+DATA/cnhtm/sysaux01.dbf datafile 4 switched to datafile copy input datafile copy recid=3 stamp=708963523 filename=+DATA/cnhtm/users01.dbf datafile 5 switched to datafile copy input datafile copy recid=4 stamp=708963523 filename=+DATA/cnhtm/example01.dbf contents of Memory Script: { set until scn 528447; recover clone database delete archivelog ; } executing Memory Script executing command: SET until clause Starting recover at 22-JAN-10 starting media recovery archive log thread 1 sequence 2 is already on disk as file /oracle/flash_recovery_area/CNHTM/archivelog/2010_01_22/o1_mf_1_2_5okz2d4w_.arc archive log thread 1 sequence 3 is already on disk as file /oracle/flash_recovery_area/CNHTM/archivelog/2010_01_22/o1_mf_1_3_5okz2t33_.arc archive log filename=/oracle/flash_recovery_area/CNHTM/archivelog/2010_01_22/o1_mf_1_2_5okz2d4w_.arc thread=1 sequence=2 archive log filename=/oracle/flash_recovery_area/CNHTM/archivelog/2010_01_22/o1_mf_1_3_5okz2t33_.arc thread=1 sequence=3 media recovery complete, elapsed time: 00:00:01 Finished recover at 22-JAN-10 contents of Memory Script: { shutdown clone; startup clone nomount ; } executing Memory Script database dismounted Oracle instance shut down connected to auxiliary database (not started) Oracle instance started Total System Global Area 146800640 bytes Fixed Size 1218172 bytes Variable Size 88082820 bytes Database Buffers 50331648 bytes Redo Buffers 7168000 bytes sql statement: CREATE CONTROLFILE REUSE SET DATABASE "AUX" RESETLOGS ARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 ( '+DATA/cnhtm/redo01.log' ) SIZE 100 M REUSE, GROUP 2 ( '+DATA/cnhtm/redo02.log' ) SIZE 100 M REUSE, GROUP 3 ( '+DATA/cnhtm/redo03.log' ) SIZE 100 M REUSE DATAFILE '+DATA/cnhtm/system01.dbf' CHARACTER SET ZHS16GBK contents of Memory Script: { set newname for tempfile 1 to "+DATA/cnhtm/temp01.dbf"; switch clone tempfile all; catalog clone datafilecopy "+DATA/cnhtm/undotbs01.dbf"; catalog clone datafilecopy "+DATA/cnhtm/sysaux01.dbf"; catalog clone datafilecopy "+DATA/cnhtm/users01.dbf"; catalog clone datafilecopy "+DATA/cnhtm/example01.dbf"; switch clone datafile all; } executing Memory Script executing command: SET NEWNAME renamed temporary file 1 to +DATA/cnhtm/temp01.dbf in control file cataloged datafile copy datafile copy filename=+DATA/cnhtm/undotbs01.dbf recid=1 stamp=708963567 cataloged datafile copy datafile copy filename=+DATA/cnhtm/sysaux01.dbf recid=2 stamp=708963567 cataloged datafile copy datafile copy filename=+DATA/cnhtm/users01.dbf recid=3 stamp=708963568 cataloged datafile copy datafile copy filename=+DATA/cnhtm/example01.dbf recid=4 stamp=708963568 datafile 2 switched to datafile copy input datafile copy recid=1 stamp=708963567 filename=+DATA/cnhtm/undotbs01.dbf datafile 3 switched to datafile copy input datafile copy recid=2 stamp=708963567 filename=+DATA/cnhtm/sysaux01.dbf datafile 4 switched to datafile copy input datafile copy recid=3 stamp=708963568 filename=+DATA/cnhtm/users01.dbf datafile 5 switched to datafile copy input datafile copy recid=4 stamp=708963568 filename=+DATA/cnhtm/example01.dbf contents of Memory Script: { Alter clone database open resetlogs; } executing Memory Script database opened Finished Duplicate Db at 22-JAN-10 RMAN> RMAN> **end-of-file** RMAN> exit Recovery Manager complete.

2.11、複製成功，檢查資料庫狀態

ora_test@oracle[/home/oracle]> sqlplus / as sysdba SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jan 22 14:21:19 2010 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options sys@AUX> select open_mode from v$database; OPEN_MODE ---------- READ WRITE sys@AUX> sys@AUX> select name from v$datafile; NAME ---------------------------------------------------------------------------------------------------- +DATA/cnhtm/system01.dbf +DATA/cnhtm/undotbs01.dbf +DATA/cnhtm/sysaux01.dbf +DATA/cnhtm/users01.dbf +DATA/cnhtm/example01.dbf sys@AUX>

--end--