通過RMAN duplicate遷移數據庫（單機到單機）

客戶要求搭建一套測試庫，現在環境是window下oracle RAC，使用能使用導入導出最好，但是目前是歸檔模式，使用導入導出風險太大，使用RMAN備份服務器上沒有足夠的空間。好吧，最好的方法就是RMAN duplicate的方法了。先在單機環境測試一下！

Oracle 11g的RMAN duplicate 可以通過Activedatabase duplicate和Backup-based duplicate兩種方法實現。本案例使用的是Active database duplicate，對于Active databaseduplicate來說，在克隆數據庫時不用對Source備份，這對于大數據特別是T級別的數據庫來說優點非常明顯，復制前不需要進行備份，減少了備份和傳送備份的時間，同時節省備份空間。

本次測試將PROD2數據庫復制到另外一臺服務器上，名稱為PROD5

查看源庫信息

源庫必須為歸檔模式，并啟用快速恢復區；確認數據文件和日志文件路徑！

 [oracle@orar2p1 ~]$ sqlplus / as sysdba

SQL*Plus:Release 11.2.0.3.0 Production on Tue Sep 5 14:56:01 2017

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

Connected to:

Oracle Database11g Enterprise Edition Release 11.2.0.3.0 - Production

With thePartitioning, OLAP, Data Mining and Real Application Testing options

SYS@PROD2>selectstatus from v$instance;

STATUS

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

OPEN

SYS@PROD2>selectname from v$datafile;

NAME

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

/u01/app/oracle/oradata/PROD2/system01.dbf

/u01/app/oracle/oradata/PROD2/sysaux01.dbf

/u01/app/oracle/oradata/PROD2/undotbs01.dbf

/u01/app/oracle/oradata/PROD2/users01.dbf

/u01/app/oracle/oradata/PROD2/example01.dbf

SYS@PROD2>selectmember from v$logfile;

MEMBER

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

/u01/app/oracle/oradata/PROD2/redo03.log

/u01/app/oracle/oradata/PROD2/redo02.log

/u01/app/oracle/oradata/PROD2/redo01.log

SYS@PROD2>showparameter name

NAME                                 TYPE        VALUE

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

db_file_name_convert                 string

db_name                              string      PROD2

db_unique_name                       string      PROD2

global_names                         boolean     FALSE

instance_name                        string      PROD2

lock_name_space                      string

log_file_name_convert                string

processor_group_name                 string

service_names                        string      PROD2.us.oracle.com

構建測試庫PROD5：

1）生成測試庫pfile

可以將源庫的pfile拷貝過來，將名稱替換，也可以重新生成pfile，下面是重新生成的！

[oracle@orar2p2dbs]$ cat init.ora | grep -v ^#| grep -v ^$ >initPROD5.ora

[oracle@orar2p2dbs]$ vi initPROD5.ora

db_name='PROD5'

memory_target=1G

processes = 150

audit_file_dest='$ORACLE_BASE/admin/PROD5/adump'

audit_trail='db'

db_block_size=8192

db_domain='us.oracle.com'

db_recovery_file_dest='$ORACLE_BASE/fast_recovery_area'

db_recovery_file_dest_size=2G

diagnostic_dest='$ORACLE_BASE'

dispatchers='(PROTOCOL=TCP)(SERVICE=PROD5XDB)'

open_cursors=300

remote_login_passwordfile='EXCLUSIVE'

undo_tablespace='UNDOTBS1'    此處必須和源庫名稱一樣！

control_files ='/u01/app/oracle/oradata/PROD5/ora_control01.ctl','/u01/app/oracle/fast_recovery_area/PROD5/ora_control02.ctl'

compatible='11.2.0'

db_file_name_convert='/u01/app/oracle/oradata/PROD2/','/u01/app/oracle/oradata/PROD5/'

log_file_name_convert='/u01/app/oracle/oradata/PROD2/','/u01/app/oracle/oradata/PROD5/','/u01/app/oracle/fast_recovery_area/PROD2/onlinelog/','/u01/app/oracle/fast_recovery_area/PROD5/'（日志文件的對應關系必須確認好，否則會報錯）

2）創建測試庫相關目錄（根據pfile信息創建相關目錄）

[oracle@orar2p2oradata]$ mkdir PROD5

[oracle@orar2p2oradata]$ cd ../admin

[oracle@orar2p2admin]$ mkdir -p PROD5/adump

3）生成口令文件  可以直接將源庫的密碼文件拷貝過來，或者重新生成，但是兩邊的密碼必須一致！

oracle@orar2p2dbs]$ orapwd file=orapwPROD5 password=oracle entries=30

傳輸參數文件scp initPROD2.ora oracle@192.0.2.12:/u01/app/oracle/product/11.2.0/db_1/dbs

傳輸密碼文件scp orapwPROD2 oracle@192.0.2.12:/u01/app/oracle/product/11.2.0/db_1/dbs/orapwPROD5

測試庫啟動到nomout狀態

[oracle@orar2p2admin]$ export ORACLE_SID=PROD5

[oracle@orar2p2admin]$ sqlplus / as sysdba

SQL*Plus:Release 11.2.0.3.0 Production on Tue Sep 5 08:35:48 2017

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

Connected to anidle instance.

SYS@PROD5>startupnomount

配置監聽

兩端都要配置源庫和目標庫的監聽和tnsname.ora

源庫靜態監聽信息

SID_LIST_LISTENER=

  (SID_LIST=

    (SID_DESC=

      (GLOBAL_DBNAME=PROD2.us.oracle.com)

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

      (SID_NAME=PROD2))

   )

源庫tnsnames.ora配置信息

PROD2 =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = orar2p1.example.com)(PORT= 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = PROD2.us.oracle.com)

    )

  )

PROD5 =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = orar2p2.example.com)(PORT= 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = PROD5.us.oracle.com)

    )

  )

目標庫靜態監聽信息

SID_LIST_LISTENER=

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME =PROD5.us.oracle.com)

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

      (SID_NAME =PROD5)

    )

  )

目標庫tnsnames.ora配置信息

PROD2 =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = orar2p1.example.com)(PORT= 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = PROD2.us.oracle.com)

    )

  )

PROD5 =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = orar2p2.example.com)(PORT= 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = PROD5.us.oracle.com)

    )

  )

在源庫PROD2啟動rman，復制數據庫

開始數據庫復制

[oracle@orar2p2admin]$ rman target sys/oracle@prod2 auxiliary sys/oracle@prod5

RecoveryManager: Release 11.2.0.3.0 - Production on Tue Sep 5 08:26:17 2017

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

connected totarget database: PROD2 (DBID=1512727797)

connected toauxiliary database: PROD5 (not mounted)

RMAN> duplicate target database to prod5 from active database nofilenamecheck;

duplicate targetdatabase to prod from active database nofilenamecheck;

--如果主備庫文件路徑不變，要加nofilenamecheck（否則會報錯）

驗證克隆

[oracle@orar2p2admin]$ sql

SQL*Plus:Release 11.2.0.3.0 Production on Tue Sep 5 08:41:35 2017

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

Connected to:

Oracle Database11g Enterprise Edition Release 11.2.0.3.0 - Production

With thePartitioning, OLAP, Data Mining and Real Application Testing options

SYS@PROD5>selectstatus from v$instance;

STATUS

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

OPEN

SYS@PROD5>selectname from v$datafile;

NAME

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

/u01/app/oracle/oradata/PROD5/system01.dbf

/u01/app/oracle/oradata/PROD5/sysaux01.dbf

/u01/app/oracle/oradata/PROD5/undotbs01.dbf

/u01/app/oracle/oradata/PROD5/users01.dbf

/u01/app/oracle/oradata/PROD5/example01.dbf

SYS@PROD5>selectmember from v$logfile;

MEMBER

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

/u01/app/oracle/oradata/PROD5/redo03.log

/u01/app/oracle/oradata/PROD5/redo02.log

/u01/app/oracle/oradata/PROD5/redo01.log

SYS@PROD5>showparameter control

NAME                                 TYPE        VALUE

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

_optimizer_extended_stats_usage_continteger     192

rol

_optimizer_join_order_control        integer     3

control_file_record_keep_time        integer     7

control_files                        string      /u01/app/oracle/oradata/PROD5/

                                                 ora_control01.ctl,/u01/app/or

                                                acle/fast_recovery_area/PROD5/

                                                ora_control02.ctl

control_management_pack_access       string      DIAGNOSTIC+TUNING

SYS@PROD5>