duplicate database

 工作很久了，也一直都沒有操作過duplicate database，因為平常的備份恢復，用rman普通方式就可以搞定了，但這個duplicate database是必須瞭解的，所以現在實驗一下，以免以後用到。
帶著疑問：duplicate database有什麼過人之處，導致我們非要使用它，到底和普通的備份恢復有啥區別？
       據說是用catalog的話，必須使用duplicate，如果用ctl的話，那基本和普通的備份恢復沒啥區別，所以這應該也是我這麼多年都沒有太瞭解duplicate的原因吧，因為我一直都使用ctl，沒有使用catalog，雖然說備份軟體裡的內建資料庫就類似於我們的catalog，但操作上還是有一定區別的。他最大的特點可能就是可以生產新的dbid吧。

定義和原理：
 我理解所謂duplicate database這個命令，其實就是一堆命令的封裝，當我們執行這個命令時，可以自動的完成很多工作。對於原理，還不是很清楚，所以還是需要通過實驗來了解原理。過了1h，實驗完畢。現在來簡單記錄一下原理：duplicate database可以修改dbid，所以我們要是異機恢復的話，不需要記錄dbid，在原資料庫mount狀態下就可以執行duplicate database，之所以必須要求原資料庫在mount下是因為duplicate database要使用原資料庫的控制檔案裡的備份資訊。所以我們使用duplicate database，更嚴格的說是可以複製資料庫。

 實驗：
 原庫：192.168.160.2 hostname：dongdong.com，已有資料庫和例項，狀態為open。
 duplicate database：192.168.160.128 hostname:maomao.com，已安裝資料庫軟體，沒有例項。
 備註:原庫和目標庫的所有路徑都一樣。
1. 原庫備份
RMAN> backup database plus archivelog;-----需要將歸檔也備份。
[oracle@baobao ~]$ scp -r 2013_12_01/ 192.168.160.128:/u01/app/oracle/flash_recovery_area/DONG/backupset/
[oracle@maomao 2013_12_01]$ pwd
/u01/app/oracle/flash_recovery_area/DONG/backupset/2013_12_01
[oracle@maomao 2013_12_01]$ ll
總用量 1281504
-rw-r-----. 1 oracle oinstall   12736512 12月  1 13:19 o1_mf_annnn_TAG20131201T131320_99okcjxn_.bkp
-rw-r-----. 1 oracle oinstall       3584 12月  1 13:16 o1_mf_annnn_TAG20131201T131450_99okgc5k_.bkp
-rw-r-----. 1 oracle oinstall   10158080 12月  1 13:16 o1_mf_ncsnf_TAG20131201T131322_99okg99z_.bkp
-rw-r-----. 1 oracle oinstall 1289355264 12月  1 13:18 o1_mf_nnndf_TAG20131201T131322_99okclrp_.bkp
2 duplicate資料庫建立pfile，並啟動例項
[oracle@maomao dbs]$ export ORACLE_SID=mm
[oracle@maomao dbs]$ cat initmm.ora
*.control_files='/u01/app/oracle/oradata/dong/control01.ctl'#Restore Controlfile
*.db_name='mm'
[oracle@maomao dbs]$ sqlplus / as sysdba
SQL> startup nomount
ORACLE instance started.
Total System Global Area  150667264 bytes
Fixed Size                  1335080 bytes
Variable Size              92274904 bytes
Database Buffers           50331648 bytes
Redo Buffers                6725632 bytes
3 配置tns監聽，讓兩邊都可以互相連線
4 配置duplicate database的口令檔案
5 連線，執行duplicate database命令
[oracle@baobao backupset]$ rman target / auxiliary sys/oracle@mm

Recovery Manager: Release 11.2.0.1.0 - Production on Sun Dec 1 13:24:47 2013

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

connected to target database: DONG (DBID=2075447482)
connected to auxiliary database: MM (not mounted)

RMAN> duplicate target database to mm nofilenamecheck;（備註：這裡使用nofilenamecheck是因為oracle以為你要將恢復到當前機器，會覆蓋dbf。其實我們是恢復到auxiliary庫上，但oracle目前還沒有這麼聰明，所以我們就需要寫啦）

Starting Duplicate Db at 01-DEC-13
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 "alter system set  db_name =
 ''DONG'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''MM'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   restore clone primary controlfile;
   alter clone database mount;
}
executing Memory Script

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

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

Oracle instance shut down

Oracle instance started

Total System Global Area     150667264 bytes

Fixed Size                     1335080 bytes
Variable Size                 92274904 bytes
Database Buffers              50331648 bytes
Redo Buffers                   6725632 bytes

Starting restore at 01-DEC-13
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=95 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/DONG/backupset/2013_12_01/o1_mf_ncsnf_TAG20131201T131322_99okg99z_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/DONG/backupset/2013_12_01/o1_mf_ncsnf_TAG20131201T131322_99okg99z_.bkp tag=TAG20131201T131322
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/dong/control01.ctl
Finished restore at 01-DEC-13

database mounted
Using previous duplicated file /u01/app/oracle/oradata/dong/system01.dbf for datafile 1 with checkpoint SCN of 1411720
Using previous duplicated file /u01/app/oracle/oradata/dong/sysaux01.dbf for datafile 2 with checkpoint SCN of 1411720
Using previous duplicated file /u01/app/oracle/oradata/dong/undotbs01.dbf for datafile 3 with checkpoint SCN of 1411720
Using previous duplicated file /u01/app/oracle/oradata/dong/users01.dbf for datafile 4 with checkpoint SCN of 1411720

contents of Memory Script:
{
   catalog clone datafilecopy  "/u01/app/oracle/oradata/dong/system01.dbf",
 "/u01/app/oracle/oradata/dong/sysaux01.dbf",
 "/u01/app/oracle/oradata/dong/undotbs01.dbf",
 "/u01/app/oracle/oradata/dong/users01.dbf";
   switch clone datafile  1 to datafilecopy
 "/u01/app/oracle/oradata/dong/system01.dbf";
   switch clone datafile  2 to datafilecopy
 "/u01/app/oracle/oradata/dong/sysaux01.dbf";
   switch clone datafile  3 to datafilecopy
 "/u01/app/oracle/oradata/dong/undotbs01.dbf";
   switch clone datafile  4 to datafilecopy
 "/u01/app/oracle/oradata/dong/users01.dbf";
}
executing Memory Script

cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/dong/system01.dbf RECID=1 STAMP=833030747
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/dong/sysaux01.dbf RECID=2 STAMP=833030747
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/dong/undotbs01.dbf RECID=3 STAMP=833030747
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/dong/users01.dbf RECID=4 STAMP=833030747

datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=833030747 file name=/u01/app/oracle/oradata/dong/system01.dbf

datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=833030747 file name=/u01/app/oracle/oradata/dong/sysaux01.dbf

datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=833030747 file name=/u01/app/oracle/oradata/dong/undotbs01.dbf

datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=833030747 file name=/u01/app/oracle/oradata/dong/users01.dbf

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

executing command: SET until clause

Starting recover at 01-DEC-13
using channel ORA_AUX_DISK_1

starting media recovery

channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=3
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/DONG/backupset/2013_12_01/o1_mf_annnn_TAG20131201T131320_99okcjxn_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/DONG/backupset/2013_12_01/o1_mf_annnn_TAG20131201T131320_99okcjxn_.bkp tag=TAG20131201T131320
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_3_832784280.dbf thread=1 sequence=3
channel clone_default: deleting archived log(s)
archived log file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_3_832784280.dbf RECID=4 STAMP=833030748
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=4
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/DONG/backupset/2013_12_01/o1_mf_annnn_TAG20131201T131450_99okgc5k_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/DONG/backupset/2013_12_01/o1_mf_annnn_TAG20131201T131450_99okgc5k_.bkp tag=TAG20131201T131450
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_4_832784280.dbf thread=1 sequence=4
channel clone_default: deleting archived log(s)
archived log file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_4_832784280.dbf RECID=5 STAMP=833030751
media recovery complete, elapsed time: 00:00:01
Finished recover at 01-DEC-13

contents of Memory Script:
{
   shutdown clone immediate;
   startup clone nomount;
   sql clone "alter system set  db_name =
 ''MM'' 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

database dismounted
Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     150667264 bytes

Fixed Size                     1335080 bytes
Variable Size                 92274904 bytes
Database Buffers              50331648 bytes
Redo Buffers                   6725632 bytes

sql statement: alter system set  db_name =  ''MM'' 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     150667264 bytes

Fixed Size                     1335080 bytes
Variable Size                 92274904 bytes
Database Buffers              50331648 bytes
Redo Buffers                   6725632 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "MM" RESETLOGS ARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     8
  MAXLOGHISTORY      292
 LOGFILE
  GROUP  1 ( '/u01/app/oracle/oradata/dong/redo01.log' ) SIZE 50 M  REUSE,
  GROUP  2 ( '/u01/app/oracle/oradata/dong/redo02.log' ) SIZE 50 M  REUSE,
  GROUP  3 ( '/u01/app/oracle/oradata/dong/redo03.log' ) SIZE 50 M  REUSE
 DATAFILE
  '/u01/app/oracle/oradata/dong/system01.dbf'
 CHARACTER SET WE8MSWIN1252

contents of Memory Script:
{
   catalog clone datafilecopy  "/u01/app/oracle/oradata/dong/sysaux01.dbf",
 "/u01/app/oracle/oradata/dong/undotbs01.dbf",
 "/u01/app/oracle/oradata/dong/users01.dbf";
   switch clone datafile all;
   switch clone datafile  2 to datafilecopy
 "/u01/app/oracle/oradata/dong/sysaux01.dbf";
   switch clone datafile  3 to datafilecopy
 "/u01/app/oracle/oradata/dong/undotbs01.dbf";
   switch clone datafile  4 to datafilecopy
 "/u01/app/oracle/oradata/dong/users01.dbf";
}
executing Memory Script

cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/dong/sysaux01.dbf RECID=1 STAMP=833030769
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/dong/undotbs01.dbf RECID=2 STAMP=833030769
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/dong/users01.dbf RECID=3 STAMP=833030769

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=833030769 file name=/u01/app/oracle/oradata/dong/sysaux01.dbf

datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=833030769 file name=/u01/app/oracle/oradata/dong/undotbs01.dbf

datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=833030769 file name=/u01/app/oracle/oradata/dong/users01.dbf

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

database opened
Finished Duplicate Db at 01-DEC-13
[oracle@maomao ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sun Dec 1 13:34:03 2013

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select status from v$instance;

STATUS
------------
OPEN

SQL> show parameter name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert                 string
db_name                              string      MM
db_unique_name                       string      MM
global_names                         boolean     FALSE
instance_name                        string      mm
lock_name_space                      string
log_file_name_convert                string
service_names                        string      MM

如果dbf和control的路徑和原庫不一樣的話，那就set newname設定一下即可。

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/24500180/viewspace-1061781/，如需轉載，請註明出處，否則將追究法律責任。