Oracle11gR2使用RMAN duplicate復制數據庫——active database duplicate

11gR2的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 db：

ip 192.168.56.10

oracle_sid=mydb

oracle_version=11.2.0.3

auxiliary db：

ip 192.168.56.150

oracle_sid=oradu

oracle_version=11.2.0.3

1、在新庫創建參數文件并啟動實例到nomount狀態 

--auxiliary db上執行

[oracle@localhost ~]$ cat initoradu.ora

db_name=oradu

db_block_size=8192

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/')

--由于這里使用的是不同實例，所以必須添加db_file_name_convert和log_file_name_convert，否則在復制的時候會報錯無法創建數據文件，如果是同實例名復制，且兩數據目錄完全一樣的情況下，這兩個參數可省略。

--在auxiliary db 上創建新庫的數據文件在存放的目錄

mkdir -p /u01/app/oracle/oradata/oradu/

[oracle@localhost ~]$ export ORACLE_SID=oradu

[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Sun Mar 20 12:56:36 2016

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount pfile=/home/oracle/initoradu.ora

ORACLE instance started.

Total System Global Area  238034944 bytes

Fixed Size                  2227136 bytes

Variable Size             180356160 bytes

Database Buffers           50331648 bytes

Redo Buffers                5120000 bytes

SQL> 

2、創建密碼文件

--必須保持target DB和auxiliary DB的密碼一致。這里我直接把target db的密碼文件復制到auxiliary db對應的目錄下并重命名 

--target db上執行

[oracle@localhost ~]$ scp /u01/app/oracle/product/11.2.0/db/dbs/orapwmydb oracle@192.168.56.150:/u01/app/oracle/product/11.2.0/db/dbs/orapworadu

The authenticity of host '192.168.56.150 (192.168.56.150)' can't be established.

RSA key fingerprint is 58:71:ed:0c:e0:2a:57:68:3e:fe:79:52:8b:72:2e:00.

Are you sure you want to continue connecting (yes/no)? yes

Warning: Permanently added '192.168.56.150' (RSA) to the list of known hosts.

oracle@192.168.56.150's password: 

orapwmydb                                     100% 1536     1.5KB/s   00:00    

3、配置target db 和auxiliary db的監聽

--auxiliary db必須使用靜態監聽，否則報錯RMAN-04006: error from auxiliary database: ORA-12528: TNS:listener: all appropriate instances are blocking new connections

--auxiliary db

vi /u01/app/oracle/product/11.2.0/db/network/admin/listener.ora 

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db)

      (SID_NAME=oradu)

    )

  )

  vi /u01/app/oracle/product/11.2.0/db/network/admin/tnsnames.ora

  mydb =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.10)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = mydb)

      (SERVER = DEDICATED)

    )

  )

 --target db

vi /u01/app/oracle/product/11.2.0/db/network/admin/listener.ora 

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db)

      (ORACLE_SID = mydb)

    )

  )

  vi /u01/app/oracle/product/11.2.0/db/network/admin/tnsnames.ora

  oradu =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.150)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = oradu)

      (SERVER = DEDICATED)

    )

  )

  --重啟兩臺機器的監聽

  lsnrctl stop

  lsnrctl start

4、開始復制

--在target db上執行

[oracle@localhost ~]$ rman target / auxiliary sys/123456@oradu

Recovery Manager: Release 11.2.0.3.0 - Production on Sun Mar 20 14:09:39 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: MYDB (DBID=2820637901)

connected to auxiliary database: ORADU (not mounted)

RMAN> duplicate target database to oradu from active database;

Starting Duplicate Db at 20-MAR-16

using target database control file instead of recovery catalog

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=20 device type=DISK

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 bytes

Variable Size                180356160 bytes

Database Buffers              50331648 bytes

Redo Buffers                   5120000 bytes

contents 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

   backup as copy current controlfile auxiliary format  '/u01/app/oracle/product/11.2.0/db/dbs/cntrloradu.dbf';

   alter clone database mount;

}

executing Memory Script

sql statement: alter system set  db_name =  ''MYDB'' 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 bytes

Variable Size                180356160 bytes

Database Buffers              50331648 bytes

Redo Buffers                   5120000 bytes

Starting backup at 20-MAR-16

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=34 device type=DISK

channel ORA_DISK_1: starting datafile copy

copying current control file

output file name=/u01/app/oracle/product/11.2.0/db/dbs/snapcf_mydb.f tag=TAG20160320T140956 RECID=31 STAMP=906991797

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03

Finished backup at 20-MAR-16

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";

   set newname for datafile  5 to 

 "/u01/app/oracle/oradata/oradu/test.dbf";

   set newname for datafile  6 to 

 "/u01/app/oracle/oradata/oradu/store_01.dbf";

   set newname for datafile  7 to 

 "/u01/app/oracle/oradata/oradu/store_02.dbf";

   set newname for datafile  8 to 

 "/u01/app/oracle/oradata/oradu/pitr01.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"   datafile 

 5 auxiliary format 

 "/u01/app/oracle/oradata/oradu/test.dbf"   datafile 

 6 auxiliary format 

 "/u01/app/oracle/oradata/oradu/store_01.dbf"   datafile 

 7 auxiliary format 

 "/u01/app/oracle/oradata/oradu/store_02.dbf"   datafile 

 8 auxiliary format 

 "/u01/app/oracle/oradata/oradu/pitr01.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

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 20-MAR-16

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

input datafile file number=00001 name=/u01/app/oracle/oradata/mydb/system01.dbf

output file name=/u01/app/oracle/oradata/oradu/system01.dbf tag=TAG20160320T141004

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:05

channel ORA_DISK_1: starting datafile copy

input datafile file number=00002 name=/u01/app/oracle/oradata/mydb/sysaux01.dbf

output file name=/u01/app/oracle/oradata/oradu/sysaux01.dbf tag=TAG20160320T141004

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45

channel ORA_DISK_1: starting datafile copy

input datafile file number=00003 name=/u01/app/oracle/oradata/mydb/undotbs01.dbf

output file name=/u01/app/oracle/oradata/oradu/undotbs01.dbf tag=TAG20160320T141004

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25

channel ORA_DISK_1: starting datafile copy

input datafile file number=00004 name=/u01/app/oracle/oradata/mydb/users01.dbf

output file name=/u01/app/oracle/oradata/oradu/users01.dbf tag=TAG20160320T141004

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15

channel ORA_DISK_1: starting datafile copy

input datafile file number=00007 name=/u01/app/oracle/oradata/mydb/store_02.dbf

output file name=/u01/app/oracle/oradata/oradu/store_02.dbf tag=TAG20160320T141004

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03

channel ORA_DISK_1: starting datafile copy

input datafile file number=00005 name=/u01/app/oracle/oradata/mydb/test.dbf

output file name=/u01/app/oracle/oradata/oradu/test.dbf tag=TAG20160320T141004

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03

channel ORA_DISK_1: starting datafile copy

input datafile file number=00006 name=/u01/app/oracle/oradata/mydb/store_01.dbf

output file name=/u01/app/oracle/oradata/oradu/store_01.dbf tag=TAG20160320T141004

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07

channel ORA_DISK_1: starting datafile copy

input datafile file number=00008 name=/u01/app/oracle/oradata/mydb/pitr01.dbf

output file name=/u01/app/oracle/oradata/oradu/pitr01.dbf tag=TAG20160320T141004

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03

Finished backup at 20-MAR-16

sql statement: alter system archive log current

contents of Memory Script:

{

   backup as copy reuse

   archivelog like  "/u01/app/oracle/product/11.2.0/db/dbs/arch/1_22_906314379.dbf" auxiliary format 

 "/u01/app/oracle/product/11.2.0/db/dbs/arch2_22_906314379.dbf"   ;

   catalog clone archivelog  "/u01/app/oracle/product/11.2.0/db/dbs/arch2_22_906314379.dbf";

   switch clone datafile all;

}

executing Memory Script

Starting backup at 20-MAR-16

using channel ORA_DISK_1

channel ORA_DISK_1: starting archived log copy

input archived log thread=1 sequence=22 RECID=44 STAMP=906991972

output file name=/u01/app/oracle/product/11.2.0/db/dbs/arch2_22_906314379.dbf RECID=0 STAMP=0

channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01

Finished backup at 20-MAR-16

cataloged archived log

archived log file name=/u01/app/oracle/product/11.2.0/db/dbs/arch2_22_906314379.dbf RECID=44 STAMP=906989788

datafile 1 switched to datafile copy

input datafile copy RECID=31 STAMP=906989788 file name=/u01/app/oracle/oradata/oradu/system01.dbf

datafile 2 switched to datafile copy

input datafile copy RECID=32 STAMP=906989788 file name=/u01/app/oracle/oradata/oradu/sysaux01.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=33 STAMP=906989788 file name=/u01/app/oracle/oradata/oradu/undotbs01.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=34 STAMP=906989788 file name=/u01/app/oracle/oradata/oradu/users01.dbf

datafile 5 switched to datafile copy

input datafile copy RECID=35 STAMP=906989788 file name=/u01/app/oracle/oradata/oradu/test.dbf

datafile 6 switched to datafile copy

input datafile copy RECID=36 STAMP=906989788 file name=/u01/app/oracle/oradata/oradu/store_01.dbf

datafile 7 switched to datafile copy

input datafile copy RECID=37 STAMP=906989788 file name=/u01/app/oracle/oradata/oradu/store_02.dbf

datafile 8 switched to datafile copy

input datafile copy RECID=38 STAMP=906989788 file name=/u01/app/oracle/oradata/oradu/pitr01.dbf

contents of Memory Script:

{

   set until scn  2809336;

   recover

   clone database

    delete archivelog

   ;

}

executing Memory Script

executing command: SET until clause

Starting recover at 20-MAR-16

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=18 device type=DISK

starting media recovery

archived log for thread 1 with sequence 22 is already on disk as file /u01/app/oracle/product/11.2.0/db/dbs/arch2_22_906314379.dbf

archived log file name=/u01/app/oracle/product/11.2.0/db/dbs/arch2_22_906314379.dbf thread=1 sequence=22

media recovery complete, elapsed time: 00:00:01

Finished recover at 20-MAR-16

Oracle instance started

Total System Global Area     238034944 bytes

Fixed Size                     2227136 bytes

Variable Size                180356160 bytes

Database Buffers              50331648 bytes

Redo 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 bytes

Variable Size                180356160 bytes

Database Buffers              50331648 bytes

Redo Buffers                   5120000 bytes

sql 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 ZHS16GBK

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", 

 "/u01/app/oracle/oradata/oradu/test.dbf", 

 "/u01/app/oracle/oradata/oradu/store_01.dbf", 

 "/u01/app/oracle/oradata/oradu/store_02.dbf", 

 "/u01/app/oracle/oradata/oradu/pitr01.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 copy

datafile copy file name=/u01/app/oracle/oradata/oradu/sysaux01.dbf RECID=1 STAMP=906989800

cataloged datafile copy

datafile copy file name=/u01/app/oracle/oradata/oradu/undotbs01.dbf RECID=2 STAMP=906989800

cataloged datafile copy

datafile copy file name=/u01/app/oracle/oradata/oradu/users01.dbf RECID=3 STAMP=906989800

cataloged datafile copy

datafile copy file name=/u01/app/oracle/oradata/oradu/test.dbf RECID=4 STAMP=906989800

cataloged datafile copy

datafile copy file name=/u01/app/oracle/oradata/oradu/store_01.dbf RECID=5 STAMP=906989800

cataloged datafile copy

datafile copy file name=/u01/app/oracle/oradata/oradu/store_02.dbf RECID=6 STAMP=906989800

cataloged datafile copy

datafile copy file name=/u01/app/oracle/oradata/oradu/pitr01.dbf RECID=7 STAMP=906989800

datafile 2 switched to datafile copy

input datafile copy RECID=1 STAMP=906989800 file name=/u01/app/oracle/oradata/oradu/sysaux01.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=2 STAMP=906989800 file name=/u01/app/oracle/oradata/oradu/undotbs01.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=3 STAMP=906989800 file name=/u01/app/oracle/oradata/oradu/users01.dbf

datafile 5 switched to datafile copy

input datafile copy RECID=4 STAMP=906989800 file name=/u01/app/oracle/oradata/oradu/test.dbf

datafile 6 switched to datafile copy

input datafile copy RECID=5 STAMP=906989800 file name=/u01/app/oracle/oradata/oradu/store_01.dbf

datafile 7 switched to datafile copy

input datafile copy RECID=6 STAMP=906989800 file name=/u01/app/oracle/oradata/oradu/store_02.dbf

datafile 8 switched to datafile copy

input datafile copy RECID=7 STAMP=906989800 file name=/u01/app/oracle/oradata/oradu/pitr01.dbf

Reenabling controlfile options for auxiliary database

Executing: alter database add supplemental log data

contents of Memory Script:

{

   Alter clone database open resetlogs;

}

executing Memory Script

database opened

Finished Duplicate Db at 20-MAR-16

RMAN> 

5、驗證是否遷移成功

--在auxiliary db 執行

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE

--------- --------------------

ORADU     READ WRITE