source database	duplicate database
IP: 192.168.1.20	IP: 192.168.1.21
hostname: practice1	hostname: clne
oracle sid:PRACTICE	oracle sid:PRACTICE
tnsnames: beijing	tnsnames: shanghai

參考資料：
Backup and Recovery Reference ->2-> duplicate
Backup and Recovery User's Guide->PartVII ->24 ,25

Backup and Recovery Reference、  Backup and Recovery User's Guide
檢視PartVII Transferring Data with RMAN
24 Duplicateing a database

此次實驗分為8個步驟
Creating a Backup-Based Duplicate Database
1. Create an Oracle password file for the auxiliary instance
2. Establish Oracle Net connectivity to the auxiliary instance
3. Create an initalization parameter file for the auxiliary instance
4. Start the auxiliary instance in NOMOUNT mode
~~5. Mount or open the target database~~
6. Ensure that backups and archived redo log files are available
7. Allocate auxiliary channels if needed
8. Execute the DUPLICATE command

1. Create an Oracle password file for the auxiliary instance
orapwd file=$ORACLE_HOME/dbs/orapwPRACTICE password=oracle entries=10 force=y

2. Establish Oracle Net connectivity to the auxiliary instance
在duplicate新增靜態註冊，確保RMAN可以連線。紫色字型為新增的靜態註冊 $ vi $ORACLE_HOME/network/admin/listener.ora

LISTENER =
(DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.21)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
)
SID_LIST_LISTENER=
(SID_LIST=
    (SID_DESC=
      (SID_NAME=PRACTICE)
      (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
      (GLOBAL_DBNAME=PRACTICE)))
ADR_BASE_LISTENER = /u01/app/oracle

建立TNS檔案，beijing代表target資料庫，shanghai代表auxiliary資料庫
該檔案可建立在windows主機上，也可以建立target主機上

$ vi $ORACLE_HOME/network/admin/tnsnames.ora
shanghai = (DESCRIPTION =     (ADDRESS_LIST =       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.21)(PORT = 1521))     )     (CONNECT_DATA =       (SERVER = DEDICATED)       (SERVICE_NAME = PRACTICE)
    ) )beijing = (DESCRIPTION =     (ADDRESS_LIST =       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.20)(PORT = 1521))     )     (CONNECT_DATA =       (SERVER = DEDICATED)       (SERVICE_NAME = PRACTICE)
    ) )

兩臺主機分別啟動監聽
[oracle@practice1 ~]$ lsnrctl start
[oracle@clne ~]$ lsnrctl start

3. Create an initalization parameter file for the auxiliary instance
只需要一個引數就可以執行
$ vi $ORACLE_HOME/dbs/initPRACTICE.ora
db_name='PRACTICE'

4. Start the auxiliary instance in NOMOUNT mode
備用庫啟動到nomount狀態下，這裡需要listener的靜態註冊
SQL> startup nomount pfile=$ORACLE_HOME/dbs/initPRACTICE.ora

關閉，確保duplate命令執行順利
SQL> quit

5. Mount or open the target database
sqlplus / as sysdba
startup

6. Ensure that backups and archived redo log files are available
在target資料庫上做一次rman備份
rman target sys/oracle@beijing
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/backup/ctl_%F';

backup database format '/backup/dup_db_%U' plus archivelog delete input format '/backup/dup_log_%U’;

執行完rman備份後，源資料庫就可以關閉了，節省些虛擬機器資源
RMAN> shutdown immediate;

複製備份檔案到axuxliary相應目錄下
ls -al /backup
total 1161532
drwxr-xr-x 5 oracle oinstall       4096 Aug 3 17:45 .
drwxr-xr-x 29 root   root           4096 Aug 3 11:43 ..
drwxr-xr-x 2 oracle oinstall       4096 Jul 31 14:53 closed_backup
-rw-r----- 1 oracle oinstall    7544320 Aug 3 17:43 dup_arch_1cpf1ne6_1_1
-rw-r----- 1 oracle oinstall      11776 Aug 3 17:44 dup_arch_1epf1nf0_1_1
-rw-r----- 1 oracle oinstall    9863168 Aug 3 17:44 dup_ctl_c-3045062435-20140803-0c
-rw-r----- 1 oracle oinstall 1170784256 Aug 3 17:43 dup_db_1dpf1ne7_1_1
drwxr-xr-x 2 oracle oinstall       4096 Aug 2 10:31 open_backup
drwxr-xr-x 2 oracle oinstall       4096 Aug 2 12:27 scripts

scp /backup/dup_* oracle@192.168.1.21:/backup/

7. Allocate auxiliary channels if needed

8. Execute the DUPLICATE command
rman
connect auxiliary sys/oracle@shanghai
run{
allocate auxiliary channel dup1 type disk;
duplicate database to PRACTICE spfile backup location '/backup/' nofilenamecheck logfile <<==複製target的spfile
group 1 ('/oradata/PRACTICE/redo01_1.rdo') size 50m,
group 2 ('/oradata/PRACTICE/redo02_1.rdo') size 50m,
group 3 ('/oradata/PRACTICE/redo03_1.rdo') size 50m;
}

紫色小字部分是執行duplicate命令過程中的螢幕輸出
allocated channel: dup1
channel dup1: SID=10 device type=DISK

Starting Duplicate Db at 2014/08/03 20:52:55

contents of Memory Script:
{
   restore clone spfile to '/u01/app/oracle/product/11.2.0/db_1/dbs/spfilePRACTICE.ora' from
'/backup/dup_ctl_c-3045062435-20140803-0c';
   sql clone "alter system set spfile= ''/u01/app/oracle/product/11.2.0/db_1/dbs/spfilePRACTICE.ora''";
}
executing Memory Script

Starting restore at 2014/08/03 20:52:55

channel dup1: restoring spfile from AUTOBACKUP /backup/dup_ctl_c-3045062435-20140803-0c
channel dup1: SPFILE restore from AUTOBACKUP complete
Finished restore at 2014/08/03 20:52:56

sql statement: alter system set spfile= ''/u01/app/oracle/product/11.2.0/db_1/dbs/spfilePRACTICE.ora''

contents of Memory Script:
{
   sql clone "alter system set db_name =
''PRACTICE'' comment=
''duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

sql statement: alter system set db_name = ''PRACTICE'' comment= ''duplicate'' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     584568832 bytes

Fixed Size                     2230552 bytes
Variable Size                381683432 bytes
Database Buffers             192937984 bytes
Redo Buffers                   7716864 bytes
allocated channel: dup1
channel dup1: SID=133 device type=DISK

contents of Memory Script:
{
   sql clone "alter system set db_name =
''PRACTICE'' comment=
''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set db_unique_name =
''PRACTICE'' comment=
''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   restore clone primary controlfile from '/backup/dup_ctl_c-3045062435-20140803-0c';
   alter clone database mount;
}
executing Memory Script

sql statement: alter system set db_name = ''PRACTICE'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set db_unique_name = ''PRACTICE'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area     584568832 bytes

Fixed Size                     2230552 bytes
Variable Size                381683432 bytes
Database Buffers             192937984 bytes
Redo Buffers                   7716864 bytes
allocated channel: dup1
channel dup1: SID=133 device type=DISK

Starting restore at 2014/08/03 20:53:28

channel dup1: restoring control file
channel dup1: restore complete, elapsed time: 00:00:01
output file name=/oradata/PRACTICE/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/PRACTICE/control02.ctl
Finished restore at 2014/08/03 20:53:29

database mounted
RMAN-05538: WARNING: implicitly using DB_FILE_NAME_CONVERT

contents of Memory Script:
{
   set until scn 1356901;
   set newname for datafile 1 to
"/oradata/PRACTICE/system01.dbf";
   set newname for datafile 2 to
"/oradata/PRACTICE/sysaux01.dbf";
   set newname for datafile 3 to
"/oradata/PRACTICE/undotbs01.dbf";
   set newname for datafile 4 to
"/oradata/PRACTICE/users01.dbf";
   set newname for datafile 5 to
"/oradata/PRACTICE/example01.dbf";
   set newname for datafile 6 to
"/oradata/PRACTICE/tools01.dbf";
   set newname for datafile 7 to
"/oradata/PRACTICE/indx.dbf";
   set newname for datafile 8 to
"/oradata/PRACTICE/users02.dbf";
   restore
   clone database
   ;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

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 restore at 2014/08/03 20:53:33

channel dup1: starting datafile backup set restore
channel dup1: specifying datafile(s) to restore from backup set
channel dup1: restoring datafile 00001 to /oradata/PRACTICE/system01.dbf
channel dup1: restoring datafile 00002 to /oradata/PRACTICE/sysaux01.dbf
channel dup1: restoring datafile 00003 to /oradata/PRACTICE/undotbs01.dbf
channel dup1: restoring datafile 00004 to /oradata/PRACTICE/users01.dbf
channel dup1: restoring datafile 00005 to /oradata/PRACTICE/example01.dbf
channel dup1: restoring datafile 00006 to /oradata/PRACTICE/tools01.dbf
channel dup1: restoring datafile 00007 to /oradata/PRACTICE/indx.dbf
channel dup1: restoring datafile 00008 to /oradata/PRACTICE/users02.dbf
channel dup1: reading from backup piece /backup/dup_db_1dpf1ne7_1_1
channel dup1: piece handle=/backup/dup_db_1dpf1ne7_1_1 tag=TAG20140803T174335
channel dup1: restored backup piece 1
channel dup1: restore complete, elapsed time: 00:00:28
Finished restore at 2014/08/03 20:53:58

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=854643719 file name=/oradata/PRACTICE/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=854643719 file name=/oradata/PRACTICE/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=854643719 file name=/oradata/PRACTICE/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=854643719 file name=/oradata/PRACTICE/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=5 STAMP=854643719 file name=/oradata/PRACTICE/example01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=6 STAMP=854643719 file name=/oradata/PRACTICE/tools01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=7 STAMP=854643719 file name=/oradata/PRACTICE/indx.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=8 STAMP=854643719 file name=/oradata/PRACTICE/users02.dbf

contents of Memory Script:
{
   set until scn 1356901;
   recover
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 2014/08/03 20:53:58

starting media recovery

channel dup1: starting archived log restore to default destination
channel dup1: restoring archived log
archived log thread=1 sequence=20
channel dup1: reading from backup piece /backup/dup_arch_1epf1nf0_1_1
channel dup1: piece handle=/backup/dup_arch_1epf1nf0_1_1 tag=TAG20140803T174400
channel dup1: restored backup piece 1
channel dup1: restore complete, elapsed time: 00:00:01
archived log file name=/archive/1_20_854536121.arc thread=1 sequence=20
channel clone_default: deleting archived log(s)
archived log file name=/archive/1_20_854536121.arc RECID=1 STAMP=854643720
media recovery complete, elapsed time: 00:00:00
Finished recover at 2014/08/03 20:54:00
Oracle instance started

Total System Global Area     584568832 bytes

Fixed Size                     2230552 bytes
Variable Size                381683432 bytes
Database Buffers             192937984 bytes
Redo Buffers                   7716864 bytes

contents of Memory Script:
{
   sql clone "alter system set db_name =
''PRACTICE'' 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 = ''PRACTICE'' 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     584568832 bytes

Fixed Size                     2230552 bytes
Variable Size                381683432 bytes
Database Buffers             192937984 bytes
Redo Buffers                   7716864 bytes
allocated channel: dup1
channel dup1: SID=133 device type=DISK
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "PRACTICE" RESETLOGS ARCHIVELOG
MAXLOGFILES     16
MAXLOGMEMBERS      3
MAXDATAFILES      100
MAXINSTANCES     8
MAXLOGHISTORY      292
LOGFILE
GROUP   1 ( '/oradata/PRACTICE/redo01_1.rdo' ) SIZE 50 M ,
GROUP   2 ( '/oradata/PRACTICE/redo02_1.rdo' ) SIZE 50 M ,
GROUP   3 ( '/oradata/PRACTICE/redo03_1.rdo' ) SIZE 50 M
DATAFILE
'/oradata/PRACTICE/system01.dbf'
CHARACTER SET ZHS16GBK

contents of Memory Script:
{
   set newname for tempfile 1 to
"/oradata/PRACTICE/temp01.dbf";
   switch clone tempfile all;
   catalog clone datafilecopy "/oradata/PRACTICE/sysaux01.dbf",
"/oradata/PRACTICE/undotbs01.dbf",
"/oradata/PRACTICE/users01.dbf",
"/oradata/PRACTICE/example01.dbf",
"/oradata/PRACTICE/tools01.dbf",
"/oradata/PRACTICE/indx.dbf",
"/oradata/PRACTICE/users02.dbf";
   switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /oradata/PRACTICE/temp01.dbf in control file

cataloged datafile copy
datafile copy file name=/oradata/PRACTICE/sysaux01.dbf RECID=1 STAMP=854643732
cataloged datafile copy
datafile copy file name=/oradata/PRACTICE/undotbs01.dbf RECID=2 STAMP=854643732
cataloged datafile copy
datafile copy file name=/oradata/PRACTICE/users01.dbf RECID=3 STAMP=854643732
cataloged datafile copy
datafile copy file name=/oradata/PRACTICE/example01.dbf RECID=4 STAMP=854643732
cataloged datafile copy
datafile copy file name=/oradata/PRACTICE/tools01.dbf RECID=5 STAMP=854643732
cataloged datafile copy
datafile copy file name=/oradata/PRACTICE/indx.dbf RECID=6 STAMP=854643732
cataloged datafile copy
datafile copy file name=/oradata/PRACTICE/users02.dbf RECID=7 STAMP=854643732

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=854643732 file name=/oradata/PRACTICE/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=854643732 file name=/oradata/PRACTICE/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=854643732 file name=/oradata/PRACTICE/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=854643732 file name=/oradata/PRACTICE/example01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=5 STAMP=854643732 file name=/oradata/PRACTICE/tools01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=6 STAMP=854643732 file name=/oradata/PRACTICE/indx.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=7 STAMP=854643732 file name=/oradata/PRACTICE/users02.dbf

contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script

database opened
Finished Duplicate Db at 2014/08/03 20:54:18
released channel: dup1

Oracle備份與恢復系列（四）續 RMAN Duplicate複製資料庫

相關文章