最近在給客戶做一個專案的資料庫遷移，雖然是一個比較簡單的操作，但由於考慮到停機時間和資料庫安全以及其他因素，專案部搞了很多方案比如匯入匯出方案，客戶都覺得不是很滿意。所以考慮使用直接把原資料庫進行復制，但現象是兩資料庫版本不同，不知道有沒有影響，以下是在同版本資料庫的操作，供參考學習！

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 DB

ip：192.168.1.1

hostname：oracledba

oraclesid：orcl

auxiliary DB

ip：192.168.1.1

hostname：oracledba

oraclesid：oradu

2、建立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/ -pv

mkdir: created directory `/u01/app/oracle/oradata/oradu/'

[root@oracledba dbs]# mkdir /u01/app/oracle/fast_recovery_area/oradu/ -pv

mkdir: 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 bytes

Fixed Size 2227136 bytes

Variable Size 180356160 bytes

Database Buffers 50331648 bytes

Redo Buffers 5120000 bytes

3、建立密碼檔案，必須保持target DB和auxiliary DB的密碼一致。這裡我直接對target DB密碼檔案重新命名使用

[root@oracledba dbs]# cp orapworcl orapworadu

4、配置網路，原因不解釋，配置完成後如下

[root@oracledba dbs]# cat ../network/admin/listener.ora

SID_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.ora

orcl =

(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)

))

補充：如果是10g的oracle，此時需要在target庫上進行rman備份，再將備份集複製到auxiliary上相同的路徑下，（補充的ORACLE_SID與試驗不一樣，注重思路即可）

[oracle@gc1 admin]$ rman target /
RMAN> backup database;
RMAN> backup current controlfile;
RMAN> backup archivelog all;

scp gc1:/home/oracle/backup/* /home/oracle/backup/ --複製到auxiliary相同路徑下

[oracle@gc1 admin]$ rman target sys/oracle@prod auxiliary sys/oracle@prod2

RMAN> duplicate target database to PROD2;

5、開始複製,複製時需要注意是否使用nofilenamecheck引數，如果兩個例項相關資料目錄結構完全相同，則需要指定，否則會報錯。這裡不需要使用。複製日誌太多，有興趣的可以看看，或者跳過直接看結果!

[oracle@oracledba ~]$ export ORACLE_SID=oradu --------異機操作可省略此步操作

[oracle@oracledba ~]$rman target sys/oracle@orcl auxiliary sys/oracle@oradu

Recovery Manager: Release 11.2.0.3.0 - Production on Sat May 11 23:40:39 2013

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-13

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

Variable Size 180356160 bytes

Database Buffers 50331648 bytes

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

Variable Size 180356160 bytes

Database Buffers 50331648 bytes

Redo Buffers 5120000 bytes

Starting backup at 11-MAY-13

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=40 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_1/dbs/snapcf_orcl.f tag=TAG20130511T224515 RECID=2 STAMP=815179515

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

Finished backup at 11-MAY-13

Starting restore at 11-MAY-13

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=18 device type=DISK

channel ORA_AUX_DISK_1: copied control file copy

Finished 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

Starting backup at 11-MAY-13

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

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

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

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

channel ORA_DISK_1: starting datafile copy

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

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

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

channel ORA_DISK_1: starting datafile copy

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

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

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

channel ORA_DISK_1: starting datafile copy

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

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

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

Finished 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-13

using channel ORA_DISK_1

channel ORA_DISK_1: starting archived log copy

input archived log thread=1 sequence=14 RECID=10 STAMP=815179605

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

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

Finished backup at 11-MAY-13

cataloged archived log

archived 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 copy

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

datafile 2 switched to datafile copy

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

datafile 3 switched to datafile copy

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

datafile 4 switched to datafile copy

input 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-13

using 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.dbf

archived log file name=/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_14_815146340.dbf thread=1 sequence=14

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

Finished recover at 11-MAY-13

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

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

cataloged datafile copy

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

cataloged datafile copy

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

datafile 2 switched to datafile copy

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

datafile 3 switched to datafile copy

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

datafile 4 switched to datafile copy

input 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 opened

Finished 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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With 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 ORADU

db_unique_name string ORADU

global_names boolean FALSE

instance_name string oradu

lock_name_space string

log_file_name_convert string /u01/app/oracle/oradata/orcl/,

/u01/app/oracle/oradata/oradu/

NAME TYPE VALUE

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

processor_group_name string

service_names string ORADU

SQL> show parameter pfile

NAME TYPE VALUE

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

spfile string /u01/app/oracle/product/11.2.0

/db_1/dbs/spfileoradu.ora

資料庫已經open，各項引數正常，且已建立spfile並使用！整個複製過程完成！

Oracle11gR2使用RMAN duplicate複製資料庫

相關文章