Oracle Study之--Oracle 11gR2通過(guò)RMAN克隆數(shù)據(jù)庫(kù)

Purpose of Database Duplication

A duplicate database is useful for a variety of purposes, most of which involve testing. You can perform the following tasks in a duplicate database:

• Test backup and recovery procedures

• Test an upgrade to a new release of Oracle Database

• Test the effect of applications on database performance

• Create a standby database

• Generate reports

Techniques for Duplicating a Database

RMAN supports two basic types of duplication: active database duplication and backup-based duplication. RMAN can perform backup-based duplication with or without either of the following connections:

• Target

• Recovery catalog

A connection to both is required for active database duplication.

Figure 24-1?shows the decision tree for the two duplication techniques.

Active Database Duplication

In?active database duplication, RMAN connects as?TARGET?to the source database instance and as?AUXILIARY?to the auxiliary instance. RMAN copies the live source database over the network to the auxiliary instance, thereby creating the duplicate database. No backups of the source database are required.?Figure 24-2?illustrates active database duplication.

? ? ?Oracle 11g的RMAN duplicate?能夠通過(guò)Active database duplicate和Backup-based duplicate兩種方法實(shí)現(xiàn)。本案例使用的是Active database duplicate，對(duì)于Active database duplicate來(lái)說(shuō)，在克隆數(shù)據(jù)庫(kù)時(shí)不用對(duì)Source備份，這對(duì)于大數(shù)據(jù)特別是T級(jí)別的數(shù)據(jù)庫(kù)來(lái)說(shuō)長(zhǎng)處很明顯，復(fù)制前不須要進(jìn)行備份，降低了備份和傳送備份的時(shí)間，同一時(shí)候節(jié)省備份空間。

? ? 克隆數(shù)據(jù)庫(kù)通常是在不同的主機(jī)上來(lái)完畢，本案例是測(cè)試環(huán)境，所以在一臺(tái)主機(jī)上完畢。
1、Source 庫(kù)信息 [oracle@rh64 ~]$sqlplus '/as sysdba' SQL*Plus: Release 11.2.0.3.0 Production on Thu Mar 24 16:30:48 2016 Copyright (c) 1982, 2011, Oracle. All rights reserved. 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 16:30:48 SYS@ prod>select status from v$instance; STATUS ------------ OPEN16:31:12 SYS@ prod>select name from v$datafile; NAME ------------------------------------------------------------------------------------------------------------------------ /u01/app/oracle/oradata/prod/system01.dbf /u01/app/oracle/oradata/prod/sysaux01.dbf /u01/app/oracle/oradata/prod/undotbs01.dbf /u01/app/oracle/oradata/prod/users01.dbf /u01/app/oracle/oradata/prod/example01.dbf /u01/app/oracle/oradata/prod/test1.dbf 6 rows selected.16:37:41 SYS@ prod>select member from v$logfile; MEMBER ------------------------------------------------------------------------------------------------------------------------ /u01/app/oracle/oradata/prod/redo03.log /u01/app/oracle/oradata/prod/redo02.log /u01/app/oracle/oradata/prod/redo01.log16:38:43 SYS@ prod>show parameter name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_file_name_convert string db_name string prod db_unique_name string prod global_names boolean FALSE instance_name string prod lock_name_space string log_file_name_convert string processor_group_name string service_names string prod 2、構(gòu)建測(cè)試庫(kù)：

1）測(cè)試庫(kù)文件夾環(huán)境
[root@rh64 dsk3]# cd test1/
[root@rh64 test1]# ls
oradata ?logs admin

2）生成測(cè)試庫(kù)pfile
[oracle@rh64 dbs]$ cat inittest1.ora?
*.audit_file_dest='/dsk3/test1/admin/adump' *.audit_trail='db' *.compatible='11.2.0.0.0' *.control_files='/dsk3/test1/oradata/control01.ctl' *.db_block_size=8192 *.db_domain='' *.db_name='test1' *.diagnostic_dest='/u01/app/oracle' *.memory_target=319430400 *.open_cursors=300 *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' *.undo_tablespace='UNDOTBS1' *.db_file_name_convert=('/u01/app/oracle/oradata/prod/','/dsk3/test1/oradata/') 。假設(shè)源庫(kù)和目標(biāo)庫(kù)在同一臺(tái)主機(jī)，須要配置文件存儲(chǔ)路徑轉(zhuǎn)換參數(shù) *.log_file_name_convert=('/u01/app/oracle/oradata/prod/','/dsk3/test1/logs/') 3）生成口令文件
[oracle@rh64 dbs]$ cp orapwprod orapwtest1

4）啟動(dòng)Auxiliary Instance到no mount
16:52:01 SYS@ test1>startup nomount; ORACLE instance started. Total System Global Area 521936896 bytes Fixed Size 2229944 bytes Variable Size 314575176 bytes Database Buffers 201326592 bytes Redo Buffers 3805184 bytes16:52:13 SYS@ test1>show parameter name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_file_name_convert string /u01/app/oracle/oradata/prod/,/dsk3/test1/oradata/ db_name string test1 db_unique_name string test1 global_names boolean FALSE instance_name string test1 lock_name_space string log_file_name_convert string /u01/app/oracle/oradata/prod/,/dsk3/test1/logs/ processor_group_name string service_names string test1 三、配置網(wǎng)絡(luò)Listener 和 tnsname 因?yàn)锳uxiliary Instance僅僅能啟動(dòng)到no mount狀態(tài)，所以須要在listener里配置Auxiliary Instance為靜態(tài)注冊(cè)。以便RMAN進(jìn)行連接。 1）配置監(jiān)聽(tīng)器
[oracle@rh64 admin]$ cat listener.ora?
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora # Generated by Oracle configuration tools.LISTENER =(DESCRIPTION_LIST =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = rh64)(PORT = 1521))(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))))ADR_BASE_LISTENER = /u01/app/oracle # 靜態(tài)注冊(cè)Auxiliary Instance：test1
SID_LIST_LISTENER =
? (SID_LIST =
? ? (SID_DESC =
? ? ? (GLOBAL_DBNAME = test1)
? ? ? (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
? ? ? (SID_NAME = test1)
? ? )
? )
2）配置tnsnames
[oracle@rh64 admin]$ cat tnsnames.ora?
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora # Generated by Oracle configuration tools.PROD =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = rh64)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = prod)))TEST1 =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = rh64)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = test1))) 3）查看listener信息
[oracle@rh64 admin]$ lsnrctl status LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 24-MAR-2016 17:44:04 Copyright (c) 1991, 2011, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rh64)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production Start Date 24-MAR-2016 17:43:17 Uptime 0 days 0 hr. 0 min. 46 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/rh64/listener/alert/log.xml Listening Endpoints Summary...(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rh64.cuug.cn)(PORT=1521)))(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Services Summary... Service "prod" has 1 instance(s).Instance "prod", status READY, has 1 handler(s) for this service... Service "prodXDB" has 1 instance(s).Instance "prod", status READY, has 1 handler(s) for this service... Service "test1" has 1 instance(s).Instance "test1", status UNKNOWN, has 1 handler(s) for this service... ；；Auxiliary Instance靜態(tài)注冊(cè)后，狀態(tài)為“UNKNOWN" The command completed successfully四、配置RMAN克隆
[oracle@rh64 admin]$ rman target sys/oracle@prod auxiliary sys/oracle@test1
Recovery Manager: Release 11.2.0.3.0 - Production on Thu Mar 24 17:07:38 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. ?All rights reserved.
connected to target database: PROD (DBID=305104131)
connected to auxiliary database: TEST1 (not mounted)

RMAN> duplicate database to test1 from active database nofilenamecheck;
Starting Duplicate Db at 24-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=DISKcontents 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 321507328 bytes Fixed Size 2228024 bytes Variable Size 230686920 bytes Database Buffers 83886080 bytes Redo Buffers 4706304 bytes contents of Memory Script: {sql clone "alter system set db_name = ''PROD'' comment=''Modified by RMAN duplicate'' scope=spfile";sql clone "alter system set db_unique_name = ''TEST1'' comment=''Modified by RMAN duplicate'' scope=spfile";shutdown clone immediate;startup clone force nomountbackup as copy current controlfile auxiliary format '/dsk3/test1/oradata/control01.ctl';alter clone database mount; } executing Memory Script sql statement: alter system set db_name = ''PROD'' comment= ''Modified by RMAN duplicate'' scope=spfile sql statement: alter system set db_unique_name = ''TEST1'' comment= ''Modified by RMAN duplicate'' scope=spfile Oracle instance shut down Oracle instance started Total System Global Area 321507328 bytes Fixed Size 2228024 bytes Variable Size 230686920 bytes Database Buffers 83886080 bytes Redo Buffers 4706304 bytes Starting backup at 24-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_1/dbs/snapcf_prod.f tag=TAG20160324T175437 RECID=3 STAMP=907350882 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07 Finished backup at 24-MAR-16 database mounted contents of Memory Script: {set newname for datafile 1 to "/dsk3/test1/data/system01.dbf";set newname for datafile 2 to "/dsk3/test1/data/sysaux01.dbf";set newname for datafile 3 to "/dsk3/test1/data/undotbs01.dbf";set newname for datafile 4 to "/dsk3/test1/data/users01.dbf";set newname for datafile 5 to "/dsk3/test1/data/example01.dbf";set newname for datafile 6 to "/dsk3/test1/data/test1.dbf";backup as copy reusedatafile 1 auxiliary format "/dsk3/test1/data/system01.dbf" datafile 2 auxiliary format "/dsk3/test1/data/sysaux01.dbf" datafile 3 auxiliary format "/dsk3/test1/data/undotbs01.dbf" datafile 4 auxiliary format "/dsk3/test1/data/users01.dbf" datafile 5 auxiliary format "/dsk3/test1/data/example01.dbf" datafile 6 auxiliary format "/dsk3/test1/data/test1.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 Starting backup at 24-MAR-16 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile copy input datafile file number=00001 name=/u01/app/oracle/oradata/prod/system01.dbf output file name=/dsk3/test1/oradata/system01.dbf tag=TAG20160324T180122 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:45 channel ORA_DISK_1: starting datafile copy input datafile file number=00002 name=/u01/app/oracle/oradata/prod/sysaux01.dbf output file name=/dsk3/test1/oradata/sysaux01.dbf tag=TAG20160324T180122 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:35 channel ORA_DISK_1: starting datafile copy input datafile file number=00005 name=/u01/app/oracle/oradata/prod/example01.dbf output file name=/dsk3/test1/oradata/example01.dbf tag=TAG20160324T180122 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:55 channel ORA_DISK_1: starting datafile copy input datafile file number=00003 name=/u01/app/oracle/oradata/prod/undotbs01.dbf output file name=/dsk3/test1/oradata/undotbs01.dbf tag=TAG20160324T180122 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15 channel ORA_DISK_1: starting datafile copy input datafile file number=00006 name=/u01/app/oracle/oradata/prod/test1.dbf output file name=/dsk3/test1/oradata/test1.dbf tag=TAG20160324T180122 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/prod/users01.dbf output file name=/dsk3/test1/oradata/users01.dbf tag=TAG20160324T180122 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03 Finished backup at 24-MAR-16 sql statement: alter system archive log current contents of Memory Script: {backup as copy reusearchivelog like "/dsk4/arch_prod/arch_1_32_893265808.log" auxiliary format "/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_32_893265808.dbf" ;catalog clone archivelog "/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_32_893265808.dbf";switch clone datafile all; } executing Memory Script Starting backup at 24-MAR-16 using channel ORA_DISK_1 channel ORA_DISK_1: starting archived log copy input archived log thread=1 sequence=32 RECID=26 STAMP=907351568 output file name=/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_32_893265808.dbf RECID=0 STAMP=0 channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01 Finished backup at 24-MAR-16 cataloged archived log archived log file name=/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_32_893265808.dbf RECID=26 STAMP=907351572 datafile 1 switched to datafile copy input datafile copy RECID=4 STAMP=907351573 file name=/dsk3/test1/oradata/system01.dbf datafile 2 switched to datafile copy input datafile copy RECID=5 STAMP=907351573 file name=/dsk3/test1/oradata/sysaux01.dbf datafile 3 switched to datafile copy input datafile copy RECID=6 STAMP=907351574 file name=/dsk3/test1/oradata/undotbs01.dbf datafile 4 switched to datafile copy input datafile copy RECID=7 STAMP=907351574 file name=/dsk3/test1/oradata/users01.dbf datafile 5 switched to datafile copy input datafile copy RECID=8 STAMP=907351574 file name=/dsk3/test1/oradata/example01.dbf datafile 6 switched to datafile copy input datafile copy RECID=9 STAMP=907351574 file name=/dsk3/test1/oradata/test1.dbf contents of Memory Script: {set until scn 1471172;recoverclone databasedelete archivelog; } executing Memory Script executing command: SET until clause Starting recover at 24-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 32 is already on disk as file /u01/app/oracle/product/11.2.0/db_1/dbs/arch1_32_893265808.dbf archived log file name=/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_32_893265808.dbf thread=1 sequence=32 media recovery complete, elapsed time: 00:00:05 Finished recover at 24-MAR-16 Oracle instance started Total System Global Area 321507328 bytes Fixed Size 2228024 bytes Variable Size 234881224 bytes Database Buffers 79691776 bytes Redo Buffers 4706304 bytes contents of Memory Script: {sql clone "alter system set db_name = ''TEST1'' 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 = ''TEST1'' 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 321507328 bytes Fixed Size 2228024 bytes Variable Size 234881224 bytes Database Buffers 79691776 bytes Redo Buffers 4706304 bytes sql statement: CREATE CONTROLFILE REUSE SET DATABASE "TEST1" RESETLOGS ARCHIVELOG MAXLOGFILES 16MAXLOGMEMBERS 3MAXDATAFILES 100MAXINSTANCES 8MAXLOGHISTORY 292LOGFILEGROUP 1 ( '/dsk3/test1/logs/''redo01.log' ) SIZE 50 M REUSE,GROUP 2 ( '/dsk3/test1/logs/''redo02.log' ) SIZE 50 M REUSE,GROUP 3 ( '/dsk3/test1/logs/''redo03.log' ) SIZE 50 M REUSEDATAFILE'/dsk3/test1/oradata/system01.dbf'CHARACTER SET ZHS16GBKcontents of Memory Script: {set newname for tempfile 1 to "/dsk3/test1/oradata/temp01.dbf";switch clone tempfile all;catalog clone datafilecopy "/dsk3/test1/oradata/sysaux01.dbf", "/dsk3/test1/oradata/undotbs01.dbf", "/dsk3/test1/oradata/users01.dbf", "/dsk3/test1/oradata/example01.dbf", "/dsk3/test1/oradata/test1.dbf";switch clone datafile all; } executing Memory Script executing command: SET NEWNAME renamed tempfile 1 to /dsk3/test1/oradata/temp01.dbf in control file cataloged datafile copy datafile copy file name=/dsk3/test1/oradata/sysaux01.dbf RECID=1 STAMP=907351727 cataloged datafile copy datafile copy file name=/dsk3/test1/oradata/undotbs01.dbf RECID=2 STAMP=907351728 cataloged datafile copy datafile copy file name=/dsk3/test1/oradata/users01.dbf RECID=3 STAMP=907351728 cataloged datafile copy datafile copy file name=/dsk3/test1/oradata/example01.dbf RECID=4 STAMP=907351728 cataloged datafile copy datafile copy file name=/dsk3/test1/oradata/test1.dbf RECID=5 STAMP=907351729 datafile 2 switched to datafile copy input datafile copy RECID=1 STAMP=907351727 file name=/dsk3/test1/oradata/sysaux01.dbf datafile 3 switched to datafile copy input datafile copy RECID=2 STAMP=907351728 file name=/dsk3/test1/oradata/undotbs01.dbf datafile 4 switched to datafile copy input datafile copy RECID=3 STAMP=907351728 file name=/dsk3/test1/oradata/users01.dbf datafile 5 switched to datafile copy input datafile copy RECID=4 STAMP=907351728 file name=/dsk3/test1/oradata/example01.dbf datafile 6 switched to datafile copy input datafile copy RECID=5 STAMP=907351729 file name=/dsk3/test1/oradata/test1.dbf Reenabling controlfile options for auxiliary database Executing: alter database enable block change tracking using file '/dsk4/backup/block.trc' ORACLE error from auxiliary database: ORA-19751: could not create the change tracking file ORA-19750: change tracking file: '/dsk4/backup/block.trc' ORA-27038: created file already exists Additional information: 1 Ignoring error, reattempt command after duplicate finishes contents of Memory Script: {Alter clone database open resetlogs; } executing Memory Script database opened Finished Duplicate Db at 24-MAR-16
五、驗(yàn)證克隆
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 options18:28:57 SYS@ test1>select status from v$instance; STATUS ------------ OPEN18:29:06 SYS@ test1>select name from v$datafile; NAME ------------------------------------------------------------------------------------------------------------------------ /dsk3/test1/oradata/system01.dbf /dsk3/test1/oradata/sysaux01.dbf /dsk3/test1/oradata/undotbs01.dbf /dsk3/test1/oradata/users01.dbf /dsk3/test1/oradata/example01.dbf /dsk3/test1/oradata/test1.dbf 6 rows selected.18:29:13 SYS@ test1>select member from v$logfile; MEMBER ------------------------------------------------------------------------------------------------------------------------ /dsk3/test1/logs/redo03.log /dsk3/test1/logs/redo02.log /dsk3/test1/logs/redo01.log18:30:19 SYS@ test1>show parameter controlNAME TYPE VALUE ------------------------------------ ----------- ------------------------------ control_files string /dsk3/test1/oradata/control01.ctl <span style="font-family: 宋體, Arial; background-color: rgb(255, 255, 255);">? ? ? ?</span>
-------- 至此。數(shù)據(jù)庫(kù)克隆成功 。 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ??

轉(zhuǎn)載于:https://www.cnblogs.com/lytwajue/p/7123206.html

總結(jié)

以上是生活随笔為你收集整理的Oracle Study之--Oracle 11gR2通过RMAN克隆数据库的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。