Network-Enabled Database Duplication Without Backups
Prior to Oracle 11g, an RMAN database duplication to a different host required copies of
the relevant backups and archived redo logs to be manually copied to the remote server.
Oracle 11g allows active database duplication, so there is no need for the presence of pre-existing
database backups and manual copying of files.

Once the DUPLICATE command is initiated, RMAN automatically performs the following steps:

Copies the spfile to the destination server.
Starts the auxiliary instance with the spfile.
Copies the relevant database files and archived redo logs over the network to the destination server.
Recovers the database.
Opens the database with the RESETLOGS option.
This method is suitable for creating a duplicate database or physical standby database.

也就是說oracle 11g 用DUPLICATE命令建立standby database 或 duplicate database 不再需要主庫的備份資料檔案,
歸檔日誌檔案，引數檔案,控制檔案等
而是直接從主庫複製到standby庫，不需要做copy檔案手工操作
需要做手工的地方就是複製主庫的密碼檔案PWDprimary.ora 命名為PWDstandby.ora檔案
oracle 11g rman新特性 active database duplication
下面是在同一臺機利用active database duplication 搭建standby 的操作過程

作業系統環境windows 2003 server +oracle 11.1.0.6
資料庫名 primary
主庫 sid=primary
存放資料檔案路徑:d:\app\administrator\oradata\primary\
存放歸檔檔案路徑:d:\primary\archivelog
閃迴檔案路徑:d:\app\administrator\flash_recovery_area\primary
備庫
sid=primary
存放資料檔案路徑:d:\app\administrator\oradata\standby\
存放歸檔檔案路徑:d:\standby\archivelog
閃迴檔案路徑:d:\app\administrator\flash_recovery_area\standby
建好備庫的存放檔案相關目錄
d:\app\administrator\admin 目錄下建立standby 目錄
d:\app\administrator\admin\standby 目錄下建立以下三個資料夾
adump, dpdump, pfile

操作步驟如下:
1.在windows下新建standby 例項,用oradim工具
C:\Documents and Settings\Administrator>oradim -NEW -SID standby -STARTMODE auto -S
RVCSTART system
Instance created.
The Oracle standby VSS Writer Service service is starting.
The Oracle standby VSS Writer Service service was started successfully.

2.在f:目錄下新建個檔案init.ora檔案用於啟動standby 例項
編輯init.ora 輸入 db_name=primary 儲存退出

3.編輯listener.ora與tnsnames.ora檔案使能夠與備庫相同
listener.ora檔案如下:
# listener.ora Network Configuration File: D:\app\Administrator\product\11.1.0\db_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

LISTENER =
(DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = paul2003)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
)

SID_LIST_LISTENER =
(SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = D:\app\Administrator\product\11.1.0\db_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_NAME =primary)
      (ORACLE_HOME = D:\app\Administrator\product\11.1.0\db_1)
      (SID_NAME =primary)
    )
   (SID_DESC =
      (GLOBAL_NAME =primary)
      (ORACLE_HOME = D:\app\Administrator\product\11.1.0\db_1)
      (SID_NAME =standby)
    )
)
tnsnames.ora檔案如下:
primary =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.1.227)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = primary)
    )
)
standby =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.1.227)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME =standby)
    )
)
重新啟動監聽
lsnrctl stop
lsnrctl start

4.啟動standby例項到nomount狀態
C:\Documents and Settings\Administrator>set oracle_sid=standby

C:\Documents and Settings\Administrator>sqlplus / as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Thu Jul 31 11:16:20 2008

Connected to an idle instance.

SQL> startup nomount pfile='f:\init.ora';
ORACLE instance started.

Total System Global Area 146472960 bytes
Fixed Size                  1331740 bytes
Variable Size              92278244 bytes
Database Buffers           50331648 bytes
Redo Buffers                2531328 bytes
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - P
oduction
With the Partitioning, OLAP, Data Mining and Real Application Testing options

5.在執行DUPLICATE之前，首先設定PRIMARY資料庫為歸檔模式,FORCE LOGGING和DATA GUARD相關的初始化引數：
C:\Documents and Settings\Administrator>set oracle_sid=primary

C:\Documents and Settings\Administrator>sqlplus / as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Thu Jul 31 11:17:30 2008

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

SQL> select status from v$instance;

STATUS
------------
OPEN
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            d:\primary\archivelog
Oldest online log sequence     43
Next log sequence to archive   45
Current log sequence           45
--已經是歸檔模式如果是非歸檔模式則必須修改為歸檔模式

SQL> ALTER DATABASE FORCE LOGGING;

Database altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG = 'DG_CONFIG=(primary,standby)';

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1 = 'LOCATION=d:\primary\archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primary' SCOPE = SPFILE;

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2 = 'SERVICE=standby ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby' scope=spfile;

System altered.

SQL> ALTER SYSTEM SET DB_FILE_NAME_CONVERT='PRIMARY','STANDBY';

System altered.

SQL> ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='PRIMARY','STANDBY'; 用於搭建過程中主庫和備庫目錄轉換
如果是兩臺機器相同目錄則不需要設定這兩個引數
System altered.

6.登入主庫執行duplicate命令

C:\Documents and Settings\Administrator>set oracle_sid=primary

C:\Documents and Settings\Administrator>rman target / auxiliary
dby log=c:\rman.log
Recovery Manager: Release 11.1.0.6.0 - Production on Thu Jul 31 11:17:54 2008

connected to target database: PRIMARY (DBID=1498839992)
connected to auxiliary database: PRIMARY (not mounted)

RMAN>RMAN> duplicate target database for standby
2> dorecover from active database nofilenamecheck
3> spfile set DB_UNIQUE_NAME 'standby'
4> set FAL_CLIENT 'standby'
5> set FAL_SERVER 'primary'
6> set log_archive_dest_1
7> 'LOCATION=d:\standby\archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
8> '
9> set CONTROL_FILES 'd:\app\administrator\oradata\standby\control01.ctl', --注意這是存放備庫控制檔案的地方
10> 'd:\app\administrator\oradata\standby\control02.ctl', --一定要設定
11> 'd:\app\administrator\oradata\standby\control03.ctl'
12> ;
Starting Duplicate Db at 31-JUL-08
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=98 device type=DISK

contents of Memory Script.:
{
   backup as copy reuse
   file 'D:\app\Administrator\product\11.1.0\db_1\DATABASE\PWDprimary.ORA' auxiliary format
'D:\app\Administrator\product\11.1.0\db_1\DATABASE\PWDstandby.ORA'   file
'D:\APP\ADMINISTRATOR\PRODUCT\11.1.0\DB_1\DATABASE\SPFILEPRIMARY.ORA' auxiliary format
'D:\APP\ADMINISTRATOR\PRODUCT\11.1.0\DB_1\DATABASE\SPFILESTANDBY.ORA'   ;
   sql clone "alter system set spfile= ''D:\APP\ADMINISTRATOR\PRODUCT\11.1.0\DB_1\DATABASE\SPFILESTANDBY.ORA''";
}
executing Memory Script

Starting backup at 31-JUL-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=139 device type=DISK
Finished backup at 31-JUL-08

sql statement: alter system set spfile= ''D:\APP\ADMINISTRATOR\PRODUCT\11.1.0\DB_1\DATABASE\SPFILESTANDBY.ORA''

contents of Memory Script.:
{
   sql clone "alter system set db_unique_name =
''standby'' comment=
'''' scope=spfile";
   sql clone "alter system set FAL_CLIENT =
''standby'' comment=
'''' scope=spfile";
   sql clone "alter system set FAL_SERVER =
''primary'' comment=
'''' scope=spfile";
   sql clone "alter system set log_archive_dest_1 =
''LOCATION=d:\standby\archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES)'' comment=
'''' scope=spfile";
   sql clone "alter system set CONTROL_FILES =
''d:\app\administrator\oradata\standby\control01.ctl'', ''d:\app\administrator\oradata\standby\control02.ctl'', ''d:\app\administrator\oradata\standby\control03.ctl'' comment=
'''' scope=spfile";
   shutdown clone immediate;
   startup clone nomount ;
}
executing Memory Script

sql statement: alter system set db_unique_name = ''standby'' comment= '''' scope=spfile

sql statement: alter system set FAL_CLIENT = ''standby'' comment= '''' scope=spfile

sql statement: alter system set FAL_SERVER = ''primary'' comment= '''' scope=spfile

sql statement: alter system set log_archive_dest_1 = ''LOCATION=d:\standby\archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES)'' comment= '''' scope=spfile

sql statement: alter system set CONTROL_FILES = ''d:\app\administrator\oradata\standby\control01.ctl'', ''d:\app\administrator\oradata\standby\control02.ctl'', ''d:\app\administrator\oradata\standby\control03.ctl'' comment= '''' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 431038464 bytes

Fixed Size                     1333676 bytes
Variable Size                255854164 bytes
Database Buffers             167772160 bytes
Redo Buffers                   6078464 bytes

contents of Memory Script.:
{
   backup as copy current controlfile for standby auxiliary format 'D:\APP\ADMINISTRATOR\ORADATA\STANDBY\CONTROL01.CTL';
   restore clone controlfile to 'D:\APP\ADMINISTRATOR\ORADATA\STANDBY\CONTROL02.CTL' from
'D:\APP\ADMINISTRATOR\ORADATA\STANDBY\CONTROL01.CTL';
   restore clone controlfile to 'D:\APP\ADMINISTRATOR\ORADATA\STANDBY\CONTROL03.CTL' from
'D:\APP\ADMINISTRATOR\ORADATA\STANDBY\CONTROL01.CTL';
   sql clone 'alter database mount standby database';
}
executing Memory Script

Starting backup at 31-JUL-08
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=D:\APP\ADMINISTRATOR\PRODUCT\11.1.0\DB_1\DATABASE\SNCFPRIMARY.ORA tag=TAG20080731T111834 RECID=14 STAMP=661519116
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 31-JUL-08

Starting restore at 31-JUL-08
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=153 device type=DISK

channel clone_default: skipped, AUTOBACKUP already found
channel ORA_AUX_DISK_1: skipped, AUTOBACKUP already found
channel ORA_DISK_1: copied control file copy
Finished restore at 31-JUL-08

Starting restore at 31-JUL-08
using channel ORA_AUX_DISK_1

channel clone_default: skipped, AUTOBACKUP already found
channel ORA_AUX_DISK_1: skipped, AUTOBACKUP already found
channel ORA_DISK_1: copied control file copy
Finished restore at 31-JUL-08

sql statement: alter database mount standby database

contents of Memory Script.:
{
   set newname for tempfile 1 to
"D:\APP\ADMINISTRATOR\ORADATA\STANDBY\TEMP01.DBF";
   switch clone tempfile all;
   set newname for datafile 1 to
"D:\APP\ADMINISTRATOR\ORADATA\STANDBY\SYSTEM01.DBF";
   set newname for datafile 2 to
"D:\APP\ADMINISTRATOR\ORADATA\STANDBY\SYSAUX01.DBF";
   set newname for datafile 3 to
"D:\APP\ADMINISTRATOR\ORADATA\STANDBY\UNDOTBS01.DBF";
   set newname for datafile 4 to
"D:\APP\ADMINISTRATOR\ORADATA\STANDBY\USERS01.DBF";
   set newname for datafile 5 to
"D:\APP\ADMINISTRATOR\ORADATA\STANDBY\TEST01_NEW.DBF";
   backup as copy reuse
   datafile 1 auxiliary format
"D:\APP\ADMINISTRATOR\ORADATA\STANDBY\SYSTEM01.DBF"   datafile
2 auxiliary format
"D:\APP\ADMINISTRATOR\ORADATA\STANDBY\SYSAUX01.DBF"   datafile
3 auxiliary format
"D:\APP\ADMINISTRATOR\ORADATA\STANDBY\UNDOTBS01.DBF"   datafile
4 auxiliary format
"D:\APP\ADMINISTRATOR\ORADATA\STANDBY\USERS01.DBF"   datafile
5 auxiliary format
"D:\APP\ADMINISTRATOR\ORADATA\STANDBY\TEST01_NEW.DBF"   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to D:\APP\ADMINISTRATOR\ORADATA\STANDBY\TEMP01.DBF in control file

executing command: SET NEWNAME

Starting backup at 31-JUL-08
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=D:\APP\ADMINISTRATOR\ORADATA\PRIMARY\SYSTEM01.DBF
output file name=D:\APP\ADMINISTRATOR\ORADATA\STANDBY\SYSTEM01.DBF tag=TAG20080731T111901 RECID=0 STAMP=0
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=D:\APP\ADMINISTRATOR\ORADATA\PRIMARY\UNDOTBS01.DBF
output file name=D:\APP\ADMINISTRATOR\ORADATA\STANDBY\UNDOTBS01.DBF tag=TAG20080731T111901 RECID=0 STAMP=0
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=D:\APP\ADMINISTRATOR\ORADATA\PRIMARY\SYSAUX01.DBF
output file name=D:\APP\ADMINISTRATOR\ORADATA\STANDBY\SYSAUX01.DBF tag=TAG20080731T111901 RECID=0 STAMP=0
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=D:\APP\ADMINISTRATOR\ORADATA\PRIMARY\TEST01_NEW.DBF
output file name=D:\APP\ADMINISTRATOR\ORADATA\STANDBY\TEST01_NEW.DBF tag=TAG20080731T111901 RECID=0 STAMP=0
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=D:\APP\ADMINISTRATOR\ORADATA\PRIMARY\USERS01.DBF
output file name=D:\APP\ADMINISTRATOR\ORADATA\STANDBY\USERS01.DBF tag=TAG20080731T111901 RECID=0 STAMP=0
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 31-JUL-08

sql statement: alter system archive log current

contents of Memory Script.:
{
   backup as copy reuse
   archivelog like "D:\PRIMARY\ARCHIVELOG\ARC00042_0661357304.001" auxiliary format
"D:\STANDBY\ARCHIVELOG\ARC00042_0661357304.001"   ;
   catalog clone archivelog "D:\STANDBY\ARCHIVELOG\ARC00042_0661357304.001";
   switch clone datafile all;
}
executing Memory Script

Starting backup at 31-JUL-08
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=42 RECID=58 STAMP=661519209
output file name=D:\STANDBY\ARCHIVELOG\ARC00042_0661357304.001 RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
Finished backup at 31-JUL-08

cataloged archived log
archived log file name=D:\STANDBY\ARCHIVELOG\ARC00042_0661357304.001 RECID=1 STAMP=661519212

datafile 1 switched to datafile copy
input datafile copy RECID=14 STAMP=661519212 file name=D:\APP\ADMINISTRATOR\ORADATA\STANDBY\SYSTEM01.DBF
datafile 2 switched to datafile copy
input datafile copy RECID=15 STAMP=661519212 file name=D:\APP\ADMINISTRATOR\ORADATA\STANDBY\SYSAUX01.DBF
datafile 3 switched to datafile copy
input datafile copy RECID=16 STAMP=661519212 file name=D:\APP\ADMINISTRATOR\ORADATA\STANDBY\UNDOTBS01.DBF
datafile 4 switched to datafile copy
input datafile copy RECID=17 STAMP=661519212 file name=D:\APP\ADMINISTRATOR\ORADATA\STANDBY\USERS01.DBF
datafile 5 switched to datafile copy
input datafile copy RECID=18 STAMP=661519212 file name=D:\APP\ADMINISTRATOR\ORADATA\STANDBY\TEST01_RMAN>

Recovery Manager complete.

{
   set until scn 282312;
   recover
   standby
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 31-JUL-08
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 42 is already on disk as file D:\STANDBY\ARCHIVELOG\ARC00042_0661357304.001
archived log file name=D:\STANDBY\ARCHIVELOG\ARC00042_0661357304.001 thread=1 sequence=42
media recovery complete, elapsed time: 00:00:01
Finished recover at 31-JUL-08
Finished Duplicate Db at 31-JUL-08

7.登陸STANDBY資料庫,進入到應用日誌模式,並檢查主庫歸檔是否正常傳到備庫：

C:\Documents and Settings\Administrator>set oracle_sid=standby

C:\Documents and Settings\Administrator>sqlplus / as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Thu Jul 31 11:23:33 2008

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

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> select sequence#,applied from v$archived_log;

SEQUENCE# APP
---------- ---
42 YES
43 YES

主庫進行alter system switch logfile;
備庫馬上可以查詢到
SQL> select sequence#,applied from v$archived_log;

SEQUENCE# APP
---------- ---
        42 YES
        43 YES
        44 YES

SQL>

搭建成功

注意地方：
1.要是沒複製密碼檔案
在執行duplicate命令時會報
Starting backup at 31-JUL-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=137 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 07/31/2008 12:28:23
RMAN-03015: error occurred in stored script. Memory Script
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 07/31/2008 12:28:
23
ORA-17629: Cannot connect to the remote database server
ORA-17627: ORA-01031: insufficient privileges
ORA-17629: Cannot connect to the remote database server

2.要是執行duplicate 命令時沒指定存放standby 引數控制檔案存放位置會報以下錯誤:
contents of Memory Script.:
{
   backup as copy current controlfile for standby auxiliary format 'D:\APP\ADMI
NISTRATOR\ORADATA\PRIMARY\CONTROL01.CTL';
   restore clone controlfile to 'D:\APP\ADMINISTRATOR\ORADATA\PRIMARY\CONTROL02
.CTL' from
'D:\APP\ADMINISTRATOR\ORADATA\PRIMARY\CONTROL01.CTL';
   restore clone controlfile to 'D:\APP\ADMINISTRATOR\ORADATA\PRIMARY\CONTROL03
.CTL' from
'D:\APP\ADMINISTRATOR\ORADATA\PRIMARY\CONTROL01.CTL';
   sql clone 'alter database mount standby database';
}
executing Memory Script

Starting backup at 31-JUL-08
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 07/31/2008 12:35:22
RMAN-03015: error occurred in stored script. Memory Script
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 07/31/2008 12:35:
22
ORA-17628: Oracle error 19505 returned by remote Oracle server

11g RMAN新特性 active database duplication createing standby

相關文章