Oracle 11gR2利用RMAN複製整庫記錄

Oracle 11gR2利用RMAN複製整庫記錄

Oracle 11gR2利用RMAN複製整庫記錄

源端和目標端配置好TNS和監聽（11g動態監聽sys無法在nomount狀態登入，需配置靜態監聽）

[oracle@154-Oracle_Archive admin]$ vi tnsnames.ora

 TESTHIS.154 =
  (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.234.154)(PORT = 1521))
      )
      (CONNECT_DATA =
        (SERVER = DEDICATED)
        (SID = TESTHIS)
        )
    ) 

 TESTHIS.11 =
  (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.234.11)(PORT = 1521))
      )
      (CONNECT_DATA =
        (SERVER = DEDICATED)
        (SID = testhisnew)
        )
    )

[oracle@154-Oracle_Archive admin]$ vi listener.ora

 # listener.ora Network Configuration File: /opt/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
 # Generated by Oracle configuration tools.

 LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 154-Oracle_Archive.hubs1.pu)(PORT = 1521))
    )
  )

 SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = testhis
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = testhis)
    )
  )

源庫建立pfile

SQL> create pfile='/home/oracle/pfile.ora' from spfile;

File created.

將源庫的pfile、listener、tnsname、orapw檔案傳輸到目標庫的指定位置並加以修改

[oracle@154-Oracle_Archive admin]$ scp *.ora root@192.168.234.11:/u01/app/oracle/product/11.2.0/dbhome_1/network/admin
root@192.168.234.11's password:
 listener.ora                                                                      100%  619    0.6KB/s  00:00   
 tnsnames.ora                                                                      100%  448    0.4KB/s  00:00   

[oracle@154-Oracle_Archive ~]$ scp pfile.ora root@192.168.234.11:/home/oracle
root@192.168.234.11's password:
 pfile.ora                                                                        100% 1018    1.0KB/s  00:00

[oracle@154-Oracle_Archive dbs]$ scp orapwtesthis root@192.168.234.11:/home/oracle
root@192.168.234.11's password:
 Permission denied, please try again.
root@192.168.234.11's password:
 orapwtesthis                                                                      100% 1536    1.5KB/s  00:00 

[oracle@Oracle_archive ~]$ cp orapwtesthis /u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwtesthisnew

修改pfile檔案

[oracle@Oracle_archive ~]$ vi pfile.ora
 testhisnew.__db_cache_size=922746880
 testhisnew.__java_pool_size=33554432
 testhisnew.__large_pool_size=16777216
 testhisnew.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
 testhisnew.__pga_aggregate_target=1358954496
 testhisnew.__sga_target=2533359616
 testhisnew.__shared_io_pool_size=0
 testhisnew.__shared_pool_size=1493172224
 testhisnew.__streams_pool_size=33554432
 *.audit_file_dest='/u01/app/oracle/admin/testhisnew/adump'
 *.audit_trail='db'
 *.compatible='11.2.0.0.0'
 *.control_files='/u01/app/oracle/oradata/testhisnew/control01.ctl','/u01/app/oracle/fast_recovery_area/testhisnew/control02.ctl'
 *.db_block_size=8192
 *.db_domain=''
 *.db_name='testhisne'  //最長8個字元
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
 *.db_recovery_file_dest_size=10737418240
 *.diagnostic_dest='/u01/app/oracle'
 *.dispatchers='(PROTOCOL=TCP) (SERVICE=testhisnewXDB)'
 *.log_archive_dest_1='location=/u01/app/oracle/test_arch'
 *.memory_target=3879731200
 *.open_cursors=300
 *.processes=1000
 *.remote_login_passwordfile='EXCLUSIVE'
 *.sessions=1105
 *.undo_tablespace='UNDOTBS1'

db_file_name_convert=('/u01/app/oracle/oradata/testhis/','/u01/app/oracle/oradata/testhisnew/')

log_file_name_convert=('/u01/app/oracle/oradata/testhis/','/u01/app/oracle/oradata/testhisnew/'）

建立相關目錄

[oracle@Oracle_archive ~]$ mkdir -p /u01/app/oracle/admin/testhis_n/adump
 [oracle@Oracle_archive ~]$ mkdir -p /u01/app/oracle/oradata/testhis_n/
 [oracle@Oracle_archive ~]$ mkdir -p /u01/app/oracle/fast_recovery_area/testhis_n/

登入資料庫建立spfile並啟動執行個體

[oracle@Oracle_archive ~]$ echo $ORACLE_SID
 testhisnew
 [oracle@Oracle_archive ~]$ sqlplus / as sysdba

 SQL*Plus: Release 11.2.0.2.0 Production on Tue Jul 28 16:14:04 2015

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

 Connected to an idle instance.

 SQL> create spfile from pfile='/home/oracle/pfile.ora';

 File created.

SQL> startup nomount;

ORACLE instance started.

Total System Global Area 3874926592 bytes

Fixed Size                  2232368 bytes

Variable Size            2936016848 bytes

Database Buffers          922746880 bytes

Redo Buffers              13930496 bytes

源端登入兩資料庫，執行複製

[oracle@154-Oracle_Archive admin]$ rman target sys/******@testhis.154 auxiliary sys/******@testhis.11

 Recovery Manager: Release 11.2.0.2.0 - Production on Tue Jul 28 17:46:30 2015

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

 connected to target database: TESTHIS (DBID=1362777844)
 connected to auxiliary database: TESTHISNE (not mounted)

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

.

.

.

datafile 32 switched to datafile copy

input datafile copy RECID=31 STAMP=886273519 file name=/u01/app/oracle/oradata/testhisnew/test27.dbf

datafile 33 switched to datafile copy

input datafile copy RECID=32 STAMP=886273519 file name=/u01/app/oracle/oradata/testhisnew/testdeltable01.dbf

contents of Memory Script:

{

  Alter clone database open resetlogs;

}

executing Memory Script

database opened

Finished Duplicate Db at 28-JUL-15

.

複製完成後啟動關閉目標庫，驗證是否複製成功，並觀察alter日誌是否有報錯日誌，確認無誤後及時做全庫備份。

--------------------------------------推薦閱讀 --------------------------------------

RMAN備份時遭遇ORA-19571 

RMAN 配置歸檔日誌刪除策略

Oracle基礎教程之通過RMAN複製資料庫

RMAN備份策略制定參考內容

RMAN備份學習筆記

OracleDatabase Backup加密 RMAN加密

--------------------------------------分割線 --------------------------------------