Oracle Active database duplication
001 理論基礎
active database duplication ：A duplicate database that is created over a network without restoring backups of the target database. This technique is an alternative to backup-based duplication.
意即：duplicate資料庫不需要透過還原目標資料庫的備份，而是直接透過網路而建立。這項技術是基於備份的duplication的替代選擇。
002 實驗步驟
1.建立輔助例項的Oracle口令檔案
1.建立輔助例項的Oracle口令檔案
2.建立到輔助例項的Oracle Net連線
3.建立輔助例項的初始化引數檔案
4.在nomount模式下啟動輔助例項
5.裝載或開啟目標資料庫
6.登入rman，執行duplicate命令
003 建立輔助例項的Oracle口令檔案
第一步是在輔助例項所在的主機建立輔助例項的口令檔案。建立口令檔案有兩種方式：
1、手動建立口令檔案
對於執行duplicate ... from active database有一些額外的要求。你必須使用sys使用者的ID，並且密碼必須和source database的密碼一致。
orapwd file=$ORACLE_HOME/dbs/orapwPROD2 password=oracle entries=30
或者將source database的口令檔案複製過來（scp）然後改名。
004 建立到輔助例項的Oracle Net連線
在duplicate過程中會開/關auxiliary例項，因此需要對auxiliary例項配置靜態監聽。同時需要在source database端訪問auxiliary例項，因此還需要在source database端配置tnsnames.ora。
source database端的tnsnames.ora:
PROD1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = huangrui.hhu)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = PROD1)
)
)
PROD2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = huan.hhu)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = PROD2)
)
)
在duplicate端tnsnames.ora:
PROD1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST =huangrui.hhu )(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PROD1)
)
)
PROD2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = huan.hhu)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PROD2)
)
)
在duplicate端listener.ora:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = PROD2)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = PROD2)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = huan.hhu)(PORT = 1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
005 建立輔助例項的引數檔案
將source database端的引數檔案複製過來，因為source database端只有spfile，所以在source database端生成一個initPROD1.ora檔案。
SYS@PROD1> create pfile from spfile;
File created.
然後將initPROD1.ora傳輸到duplicate端：
scp initPROD1 @huan.hhu:$ORACLE_HOME/dbs
在duplicate端編輯initPROD1檔案，首先將其改名為initPROD2.ora：
mv initPROD1.ora initPROD2.ora
開始編輯initPROD2.ora,將檔案裡的所有PROD1改為PROD2:
%s@PROD1@PROD2@gi
在檔案尾部加上兩行：
db_file_name_convert='/u01/app/oracle/oradata/PROD1','/u01/app/oracle/oradata/PROD2'
log_file_name_convert='/u01/app/oracle/oradata/PROD1','/u01/app/oracle/oradata/PROD2'
最終的引數檔案樣子：
PROD2.__db_cache_size=469762048
PROD2.__java_pool_size=16777216
PROD2.__large_pool_size=33554432
PROD2.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
PROD2.__pga_aggregate_target=520093696
PROD2.__sga_target=754974720
PROD2.__shared_io_pool_size=0
PROD2.__shared_pool_size=218103808
PROD2.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/PROD2/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u01/app/oracle/oradata/PROD2/control01.ctl','/u01/app/oracle/fast_recovery_area/PROD2/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='PROD2'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4385144832
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=PROD2XDB)'
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=1261436928
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
db_file_name_convert='/u01/app/oracle/oradata/PROD1','/u01/app/oracle/oradata/PROD2'
log_file_name_convert='/u01/app/oracle/oradata/PROD1','/u01/app/oracle/oradata/PROD2'
還要將引數檔案中提及的幾個目錄建立好：
mkdir -p /u01/app/oracle/admin/PROD2/adump
mkdir -p /u01/app/oracle/oradata/PROD2
mkdir -p /u01/app/oracle/fast_recovery_area/PROD2
006 在nomount模式下啟動輔助例項
當然，是在duplicate端：
SQL>startup nomount pfile='/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initPROD2.ora'；
007 裝載或開啟目標資料庫
將source database啟動到mount或open狀態即可。
008 登入rman，執行duplicate命令
當然，是在source database端登入。
然後就是第一條也是最後一條duplicate命令：duplicate target database to PROD2 from active database;
009 下面是執行完第8步之後出現的整個duplicate流程：
RMAN> duplicate target database to PROD2 from active database;
Starting Duplicate Db at 11-JAN-16
using target database control file instead of recovery catalog #使用目標資料庫控制檔案而不是恢復目錄
allocated channel: ORA_AUX_DISK_1 #分配通道
channel ORA_AUX_DISK_1: SID=10 device type=DISK
contents of Memory Script: #記憶體指令碼內容
{
sql clone "create spfile from memory"; #建立spfile
}
executing Memory Script
sql statement: create spfile from memory
contents of Memory Script: #關閉然後開啟克隆例項到nomount狀態
{
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 1269366784 bytes
Fixed Size 2252864 bytes
Variable Size 822087616 bytes
Database Buffers 436207616 bytes
Redo Buffers 8818688 bytes
contents of Memory Script:
{
sql clone "alter system set db_name =
''PROD1'' comment=
''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set db_unique_name =
''PROD2'' 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/PROD2/control01.ctl'; #複製控制檔案
restore clone controlfile to '/u01/app/oracle/fast_recovery_area/PROD2/control02.ctl' from
'/u01/app/oracle/oradata/PROD2/control01.ctl';
alter clone database mount;
}
executing Memory Script
sql statement: alter system set db_name = ''PROD1'' comment= ''Modified by RMAN duplicate'' scope=spfile
sql statement: alter system set db_unique_name = ''PROD2'' comment= ''Modified by RMAN duplicate'' scope=spfile
Oracle instance shut down
Oracle instance started
Total System Global Area 1269366784 bytes
Fixed Size 2252864 bytes
Variable Size 822087616 bytes
Database Buffers 436207616 bytes
Redo Buffers 8818688 bytes
Starting backup at 11-JAN-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=144 device type=DISK
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_PROD1.f tag=TAG20160111T160715 RECID=4 STAMP=900864435
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 11-JAN-16
Starting restore at 11-JAN-16
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=133 device type=DISK
channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 11-JAN-16
database mounted
contents of Memory Script: #改變資料檔案路徑
{
set newname for datafile 1 to
"/u01/app/oracle/oradata/PROD2/system01.dbf";
set newname for datafile 2 to
"/u01/app/oracle/oradata/PROD2/sysaux01.dbf";
set newname for datafile 3 to
"/u01/app/oracle/oradata/PROD2/undotbs01.dbf";
set newname for datafile 4 to
"/u01/app/oracle/oradata/PROD2/users01.dbf";
set newname for datafile 5 to
"/u01/app/oracle/oradata/PROD2/example01.dbf";
backup as copy reuse
datafile 1 auxiliary format
"/u01/app/oracle/oradata/PROD2/system01.dbf" datafile
2 auxiliary format
"/u01/app/oracle/oradata/PROD2/sysaux01.dbf" datafile
3 auxiliary format
"/u01/app/oracle/oradata/PROD2/undotbs01.dbf" datafile
4 auxiliary format
"/u01/app/oracle/oradata/PROD2/users01.dbf" datafile
5 auxiliary format
"/u01/app/oracle/oradata/PROD2/example01.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
Starting backup at 11-JAN-16 #複製資料檔案
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/PROD1/system01.dbf
output file name=/u01/app/oracle/oradata/PROD2/system01.dbf tag=TAG20160111T160722
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/oradata/PROD1/sysaux01.dbf
output file name=/u01/app/oracle/oradata/PROD2/sysaux01.dbf tag=TAG20160111T160722
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/u01/app/oracle/oradata/PROD1/example01.dbf
output file name=/u01/app/oracle/oradata/PROD2/example01.dbf tag=TAG20160111T160722
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/PROD1/undotbs01.dbf
output file name=/u01/app/oracle/oradata/PROD2/undotbs01.dbf tag=TAG20160111T160722
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/PROD1/users01.dbf
output file name=/u01/app/oracle/oradata/PROD2/users01.dbf tag=TAG20160111T160722
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 11-JAN-16
sql statement: alter system archive log current
contents of Memory Script: #將source database端的歸檔日誌複製過來
{
backup as copy reuse
archivelog like "/u01/app/oracle/fast_recovery_area/PROD1/archivelog/2016_01_11/o1_mf_1_7_c96rsdhz_.arc" auxiliary format
"/u01/app/oracle/fast_recovery_area/PROD2/archivelog/2016_01_11/o1_mf_1_7_%u_.arc" ;
catalog clone recovery area;
switch clone datafile all;
}
executing Memory Script
Starting backup at 11-JAN-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=7 RECID=3 STAMP=900864558
output file name=/u01/app/oracle/fast_recovery_area/PROD2/archivelog/2016_01_11/o1_mf_1_7_09qr46hf_.arc RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:03
Finished backup at 11-JAN-16
searching for all files in the recovery area
List of Files Unknown to the Database
=====================================
File Name: /u01/app/oracle/fast_recovery_area/PROD2/archivelog/2016_01_11/o1_mf_1_7_09qr46hf_.arc
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u01/app/oracle/fast_recovery_area/PROD2/archivelog/2016_01_11/o1_mf_1_7_09qr46hf_.arc
List of files in Recovery Area not managed by the database
==========================================================
File Name: /u01/app/oracle/fast_recovery_area/PROD2/control02.ctl
RMAN-07526: Reason: File is not an Oracle Managed File
number of files not managed by recovery area is 1, totaling 9.28MB
datafile 1 switched to datafile copy
input datafile copy RECID=4 STAMP=900864562 file name=/u01/app/oracle/oradata/PROD2/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=5 STAMP=900864562 file name=/u01/app/oracle/oradata/PROD2/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=6 STAMP=900864562 file name=/u01/app/oracle/oradata/PROD2/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=7 STAMP=900864562 file name=/u01/app/oracle/oradata/PROD2/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=8 STAMP=900864562 file name=/u01/app/oracle/oradata/PROD2/example01.dbf
contents of Memory Script: #恢復資料庫，不完全恢復
{
set until scn 1053451;
recover
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 11-JAN-16
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 7 is already on disk as file /u01/app/oracle/fast_recovery_area/PROD2/archivelog/2016_01_11/o1_mf_1_7_09qr46hf_.arc
archived log file name=/u01/app/oracle/fast_recovery_area/PROD2/archivelog/2016_01_11/o1_mf_1_7_09qr46hf_.arc thread=1 sequence=7
media recovery complete, elapsed time: 00:00:00
Finished recover at 11-JAN-16
Oracle instance started
Total System Global Area 1269366784 bytes
Fixed Size 2252864 bytes
Variable Size 822087616 bytes
Database Buffers 436207616 bytes
Redo Buffers 8818688 bytes
contents of Memory Script:
{
sql clone "alter system set db_name =
''PROD2'' 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 = ''PROD2'' 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 1269366784 bytes
Fixed Size 2252864 bytes
Variable Size 822087616 bytes
Database Buffers 436207616 bytes
Redo Buffers 8818688 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "PROD2" RESETLOGS ARCHIVELOG #重建控制檔案
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( '/u01/app/oracle/oradata/PROD2/redo01.log' ) SIZE 50 M REUSE,
GROUP 2 ( '/u01/app/oracle/oradata/PROD2/redo02.log' ) SIZE 50 M REUSE,
GROUP 3 ( '/u01/app/oracle/oradata/PROD2/redo03.log' ) SIZE 50 M REUSE
DATAFILE
'/u01/app/oracle/oradata/PROD2/system01.dbf'
CHARACTER SET AL32UTF8
contents of Memory Script:
{
set newname for tempfile 1 to
"/u01/app/oracle/oradata/PROD2/temp01.dbf";
switch clone tempfile all;
catalog clone datafilecopy "/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";
switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /u01/app/oracle/oradata/PROD2/temp01.dbf in control file
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/PROD2/sysaux01.dbf RECID=1 STAMP=900864572
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/PROD2/undotbs01.dbf RECID=2 STAMP=900864572
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/PROD2/users01.dbf RECID=3 STAMP=900864572
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/PROD2/example01.dbf RECID=4 STAMP=900864572
datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=900864572 file name=/u01/app/oracle/oradata/PROD2/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=900864572 file name=/u01/app/oracle/oradata/PROD2/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=900864572 file name=/u01/app/oracle/oradata/PROD2/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=900864572 file name=/u01/app/oracle/oradata/PROD2/example01.dbf
contents of Memory Script:
{
Alter clone database open resetlogs; #開啟資料庫
}
executing Memory Script
database opened
Finished Duplicate Db at 11-JAN-16
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29067253/viewspace-1978621/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 使用RMAN複製資料庫 active database資料庫Database
- Oracle clone databaseOracleDatabase
- ORACLE database vaultOracleDatabase
- Oracle Database Cloud - Database as a Service Quick StartOracleDatabaseCloudUI
- Oracle Physical Database LimitsOracleDatabaseMIT
- Oracle Database Scheduler整理OracleDatabase
- Oracle 12.2 使用Database Link優化Standby Database WorkloadOracleDatabase優化
- Oracle 19c Concepts(01)：Introduction to Oracle DatabaseOracleDatabase
- Oracle 19c Concepts(13)：Oracle Database InstanceOracleDatabase
- Oracle DG Standby Database型別OracleDatabase型別
- Oracle DG建立Physical Standby DatabaseOracleDatabase
- Oracle DG建立Logical Standby DatabaseOracleDatabase
- oracle 10g flashback databaseOracle 10gDatabase
- Oracle OCP(35)：Database 安裝OracleDatabase
- Oracle 12C Database File Mapping for Oracle ASM FilesOracleDatabaseAPPASM
- [翻譯]-Detect And Repair Corruption in an Oracle DatabaseAIOracleDatabase
- Oracle 19c Database Management ToolsOracleDatabase
- Oracle OCP(38)：Database 物理結構OracleDatabase
- 關於Oracle Database Vault介紹OracleDatabase
- Scheduler in Oracle Database 10g(轉)OracleDatabase
- Oracle 12.2 How to Generate AWRs in Active Data Guard Standby DatabasesOracleDatabase
- Oracle例項關閉：SHUTDOWN: waiting for active calls to completeOracleAI
- G008-ORACLE-DG ORACLE 19C Active Data Guard DML RedirectionOracle
- Oracle 19c Concepts(00)：Changes in This Release for Oracle Database ConceptsOracleDatabase
- 【Oracle】Windows安裝oracle11gR1 database 11.1.0.6OracleWindowsDatabase
- Sqlcl 連線Oracle DataBase 19cSQLOracleDatabase
- 1 Oracle Database 19c 新特性OracleDatabase
- Oracle OCP(37)：Database 體系結構OracleDatabase
- Converting Oracle Database from Linux to Windows using RMANOracleDatabaseLinuxWindows
- Oracle database 升級（文件）to 10.2.0.4 from 10.2.0.1OracleDatabase
- Migration Of An Oracle Database Across OS Platforms [ID 733205.1]OracleDatabaseROSPlatform
- ORACLE資料庫檢視ACQ（ACTIVE CHECKPOINT QUEUE）資訊Oracle資料庫
- Oracle Database 19c安裝Sample SchemasOracleDatabase
- G009-ORACLE-ASK Using In-Database ArchivingOracleDatabase
- 1 Oracle Database Release 20c New FeaturesOracleDatabase
- 【PDB】Oracle 建立pdb說明(create pluggable database)OracleDatabase
- Oracle 19c Concepts(18)：Concepts for Database AdministratorsOracleDatabase
- Oracle 19c Concepts(19)：Concepts for Database DevelopersOracleDatabaseDeveloper
- Oracle OCP(39)：Database 記憶體結構OracleDatabase記憶體