一、建立物理standby資料庫
1.利用duplicate命令建立物理standby資料庫的Oracle官方的三點說法：
a.duplicate命令建立物理standby資料庫，是透過Primary資料庫的備份進行，因此不會對primary有影響
b.duplicate命令支援以OMF方式管理檔案以及路徑結構
c.duplicate命令支援在恢復過程中自動執行recover，儘可能保持standby資料庫與Primary資料庫的一致狀態。
2.配置Data Guard環境需要進行的準備工作，這塊包括：primary資料庫開啟歸檔模式、設定為force logging、配置相關初始化引數等等。
a.擁有至少一份有效地RMAN整庫備份。
b.建立輔助例項並啟動至NOMOUNT狀態。
c.配置網路服務名、監聽服務等。
3.建立步驟：Data Guard配置非常靈活，sstandby資料庫可以與primary在相同的伺服器或不同的伺服器上，並且兩端的檔案路徑可以相同，也可以不同。
a.相同目錄結構的建立:說明standby資料庫與primary資料庫肯定不在同一臺伺服器上，不需要各種方式轉換檔案路徑，次情況下用duplicate建立物理standby極為簡單。
#連線primary資料庫和輔助例項，如果使用了catalog模式，那麼還要連結到catalog資料庫
#分配通道
#建立standby控制檔案
(1)RMAN>backup current controlfile for standby;
(2)RMAN>copy controlfile for standby to 'f:\oracle\product\oradata\orclstdby01.ctl';
#執行DUPLICATE命令建立standby資料庫：
RMAN>duplicate target database for standby nofilenamecheck dorecover;
必須指定NOFILENAMECHECK引數，避免覆蓋primary資料庫的當前的資料檔案。
DORECOVER並不是必選的，如果不指定，則duplicate僅修復資料檔案到目標伺服器，不過並不會Recover資料檔案，最後將standby資料庫開啟到MOUNT狀態，此時新建立的物理standby有可能與primary相差較遠(因為備份後所有的redologs均未在standby資料庫上應用過)，指定DORECOVER引數後，DUPLICATE在修復資料檔案到目標路徑下後，就會自動對這些檔案執行RECOVER。
b.不同目錄結構的建立
#連線primary資料庫和輔助例項，如果使用了catalog模式，那麼還要連結到catalog資料庫
#分配通道
#建立standby控制檔案
#為資料檔案、日誌檔案指定新路徑
#執行DUPLICATE命令建立standby資料庫
要對資料檔案和日誌檔案的路徑重新定義方法很多
(1)DB_FILE_NAME_CONVERT和LOG_FILE_NAME_CONVERT
(2)SET NEWNAME命令重定義資料檔案 RUN{ SET NEWNAME FOR DATAFILE 1 TO 'F:\oracle\product\10.2.0\oradata\orcl\SYSTEM01.DBF';
duplicate target database for standby dorecover;}
(3)CONFIGURE AUXNAME命令重定義資料檔案
CONFIGURE AUXNAME FOR DATAFILE 1 TO 'F:\oracle\product\10.2.0\oradata\orcl\AUX_1.F';
DUPLICATE TARGET DATABASE FOR STANDBY;
務必注意：CONFIGURE AUXNAME命令執行後是一直生效的，因此應當在DUPLICATE命令執行完之後，清楚CONFIGURE AUXNAME，這樣就不會對未來的操作造成影響。
CONFIGURE AUXNAME FOR DATAFILE 1 CLEAR;
4.duplicate命令操作步驟：預設情況下，執行duplicate命令後，RMAN會按照下面的步驟操作：
a.恢復standby控制檔案到standby伺服器
b.恢復primary資料庫備份集中的相應的資料檔案到standby伺服器
c.將standby資料庫開啟到MOUNT狀態，不過不會自動開啟REDO應用，命令執行完畢。
NOTE：在預設情況duplicate命令並不會應用歸檔檔案對建立的standby資料庫資料檔案進行恢復，除非執行duplicate時指定了DORECOVER引數，那麼預設duplicate會應用
所有可應用的redologs，如果希望指定恢復的時間點，可以透過SET UNTIL SCN/TIME的方式執行不完全恢復，不過注意SET UNTIL和duplicate命令必須在同一個RUN塊中。
5.duplicate命令建立物理standby
primary資料庫：ORCL；IP:192.168.29.38; windows xp sp3
透過ORCL建立Standby資料庫，設定standby資料庫DB_UNIQUE_NAME為ORCLMAN，並重指定standby資料庫的檔案路徑。
a.配置standby輔助例項的初始化引數檔案
#建立相關的目錄：
F:\oracle\product\10.2.0\admin\orclman>mkdir adump bdump cdump pfile udump
F:\oracle\product\10.2.0\admin\orclman>cd F:\oracle\product\10.2.0\oradata
F:\oracle\product\10.2.0\oradata>mkdir orclman
F:\oracle\product\10.2.0\oradata>cd orclman
F:\oracle\product\10.2.0\oradata\orclman>
F:\oracle\product\10.2.0\oradata\orclman>mkdir archivelog
#建立初始化引數檔案init.ora
orcl.__db_cache_size=20971520
orcl.__java_pool_size=4194304
orcl.__large_pool_size=4194304
orcl.__shared_pool_size=251658240
orcl.__streams_pool_size=0
*._allow_resetlogs_corruption=FALSE
*.audit_file_dest='F:\oracle\product\10.2.0\admin\orclman\adump'
*.audit_sys_operations=TRUE
*.audit_trail='DB_EXTENDED'
*.background_dump_dest='F:\oracle\product\10.2.0\admin\orclman\bdump'
*.compatible='10.2.0.3.0'
*.control_files='F:\oracle\product\10.2.0\oradata\orclman\stdctl01.ctl','F:\oracle\product\10.2.0\oradata\orclman\stdctl02.ctl','F:\oracle\product\10.2.0\oradata\orclman\stdctl03.ctl'
*.core_dump_dest='F:\oracle\product\10.2.0\admin\orclman\cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.DB_FILE_NAME_CONVERT='F:\oracle\product\10.2.0\oradata\orcl','F:\oracle\product\10.2.0\oradata\orclman'
*.db_name='orcl'
*.db_recovery_file_dest_size=2147483648
*.db_unique_name='orclman'
*.job_queue_processes=10
*.log_archive_config='DG_CONFIG=(orcl,orclman)'
*.log_archive_dest_1='location=F:\oracle\product\10.2.0\oradata\orclman\archivelog\ valid_for=(all_logfiles,all_roles) db_unique_name=orclman'
*.log_archive_dest_state_1='ENABLE'
*.LOG_ARCHIVE_FORMAT='log%t_%s_%r.arc'
*.LOG_FILE_NAME_CONVERT='F:\oracle\product\10.2.0\oradata\orcl','F:\oracle\product\10.2.0\oradata\orclman'
*.open_cursors=300
*.open_links=10
*.pga_aggregate_target=95420416
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=170
*.sga_target=287309824
*.STANDBY_FILE_MANAGEMENT='AUTO'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='F:\oracle\product\10.2.0\admin\orclman\udump'
儲存為在init.ora，存放在pfile目錄下。
由於次standby資料庫不準備執行角色切換，因此沒有配置FAL_*及其他歸檔目錄等相關引數。
b.建立例項orclman
C:\>oradim -new -sid orclman -startmode manual
Instance created.
c.從pfile建立spfile
C:\>set oracle_sid=orclman
C:\>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.3.0 - Production on Sun Aug 12 16:12:05 2012

Connected to an idle instance.
SQL> show user;
USER is "SYS"
SQL> create spfile from pfile='F:\oracle\product\10.2.0\admin\orclman\pfile\init.ora'；

File created.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area  289406976 bytes
Fixed Size                  1290184 bytes
Variable Size              96469048 bytes
Database Buffers          184549376 bytes
Redo Buffers                7098368 bytes
SQL>
d.建立輔助例項金鑰檔案
F:\>orapwd file=F:\oracle\product\10.2.0\db_1\database\PWDorclman.ora password=l
yn entries=30
e.配置standby伺服器的監聽服務
在listener.ora檔案中新增如下內容：
(SID_DESC =
     (GLOBAL_DBNAME = orclman)
     (ORACLE_HOME = F:\oracle\product\10.2.0\db_1)
     (SID_NAME = orclman)
)
f.在primary資料庫端配置網路服務名
修改tnsnames.ora檔案，新增如下內容：
ORCLMAN =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.29.38)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SID = ORCLMAN)
    )
)
g.在primary資料庫檢查狀態
SQL> show parameter instance_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------------
instance_name                        string      orcl
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            C:\DataGuard\Demo\orcl\archivelog
Oldest online log sequence     271
Next log sequence to archive   273
Current log sequence           273
SQL> select force_logging from v$DATABASE;
FOR
---
YES
h.primary資料庫端檢查備份並複製到目標伺服器相同目錄下
首先在primary端檢查是否擁有合適的備份集
C:\>rman target  catalog

Recovery Manager: Release 10.2.0.3.0 - Production on Sun Aug 12 16:38:53 2012

connected to target database: ORCL (DBID=1304585157)
connected to recovery catalog database
RMAN> list backup;
............................many backupsets............
如果沒有standby控制檔案，可以透過RMAN來建立
RMAN> backup current controlfile for standby;

Starting backup at 12-AUG-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=127 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including standby control file in backupset
channel ORA_DISK_1: starting piece 1 at 12-AUG-12
channel ORA_DISK_1: finished piece 1 at 12-AUG-12
piece handle=F:\BACKUP_4JNIFK61_1_1 tag=TAG20120812T164137 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 12-AUG-12

Starting Control File and SPFILE Autobackup at 12-AUG-12
piece handle=F:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\AUTOBACKUP\2012_0
8_12\O1_MF_S_791138502_82GV27VC_.BKP comment=NONE
Finished Control File and SPFILE Autobackup at 12-AUG-12
i.執行建立操作
RMAN連線目標資料庫和輔助例項，連線standby的時候前面要制定AUXILIARY；
C:\>set oracle_sid=orcl

C:\>rman target / auxiliary

Recovery Manager: Release 10.2.0.3.0 - Production on Sun Aug 12 17:11:55 2012

connected to target database: ORCL (DBID=1304585157)
connected to auxiliary database: ORCL (not mounted)

RMAN> duplicate target database for standby;

Starting Duplicate Db at 12-AUG-12
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=156 devtype=DISK

contents of Memory Script:
{
restore clone standby controlfile;
sql clone 'alter database mount standby database';
}
executing Memory Script

Starting restore at 12-AUG-12
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece F:\BACKUP_4JNIFK61_1_1
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=F:\BACKUP_4JNIFK61_1_1 tag=TAG20120812T164137
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:04
output filename=F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLMAN\STDCTL01.CTL
output filename=F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLMAN\STDCTL02.CTL
output filename=F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLMAN\STDCTL03.CTL
Finished restore at 12-AUG-12

sql statement: alter database mount standby database
released channel: ORA_AUX_DISK_1

contents of Memory Script:
{
   set newname for tempfile  2 to
"F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLMAN\TEMP01.DBF";
   switch clone tempfile all;
   set newname for datafile  1 to
"F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLMAN\SYSTEM01.DBF";
   set newname for datafile  2 to
"F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLMAN\UNDOTBS2.DBF";
   set newname for datafile  3 to
"F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLMAN\SYSAUX01.DBF";
   set newname for datafile  4 to
"F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLMAN\USERS01.DBF";
   set newname for datafile  5 to
"F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLMAN\EXAMPLE01.DBF";
   set newname for datafile  6 to
"F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLMAN\ORADATA1.DBF";
   set newname for datafile  7 to
"F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLMAN\ORADATA2.DBF";
   set newname for datafile  8 to
"F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLMAN\SYSTEM02.DBF";
   set newname for datafile  9 to
"F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLMAN\MYIDX01.DBF";
   set newname for datafile  10 to
"F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLMAN\UNDOTBS02.DBF";
   set newname for datafile  11 to
"F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLMAN\ASM_LMT.DBF";
   set newname for datafile  12 to
"F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLMAN\PERFSTAT.DBF";
   set newname for datafile  13 to
"F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLMAN\ORADATA3.DBF";
   set newname for datafile  14 to
"F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLMAN\STREAMS_TBS.DBF";
   set newname for datafile  15 to
"F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLMAN\ORCL\DATAFILE\O1_MF_USER123_81MTG1GN_.DBF";
   set newname for datafile  16 to
"F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLMAN\GGTBS.DBF";
   restore
   check readonly
   clone database
   ;
}
executing Memory Script

executing command: SET NEWNAME

renamed temporary file 2 to F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLMAN\TEMP01.DBF
in control file

executing command: SET NEWNAME

Starting restore at 12-AUG-12
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=155 devtype=DISK

channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLMAN\SYSTEM01.DBF
restoring datafile 00002 to F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLMAN\UNDOTBS2.DBF
restoring datafile 00003 to F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLMAN\SYSAUX01.DBF
restoring datafile 00004 to F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLMAN\USERS01.DBF

restoring datafile 00005 to F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLMAN\EXAMPLE01.DBF
restoring datafile 00006 to F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLMAN\ORADATA1.DBF
restoring datafile 00007 to F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLMAN\ORADATA2.DBF
restoring datafile 00008 to F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLMAN\SYSTEM02.DBF
restoring datafile 00009 to F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLMAN\MYIDX01.DBF

restoring datafile 00010 to F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLMAN\UNDOTBS02.DBF
restoring datafile 00011 to F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLMAN\ASM_LMT.DBF

restoring datafile 00012 to F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLMAN\PERFSTAT.DBF
restoring datafile 00013 to F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLMAN\ORADATA3.DBF
restoring datafile 00014 to F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLMAN\STREAMS_TBS.DBF
restoring datafile 00015 to F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLMAN\ORCL\DATAFI
LE\O1_MF_USER123_81MTG1GN_.DBF
restoring datafile 00016 to F:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCLMAN\GGTBS.DBF
channel ORA_AUX_DISK_1: reading from backup piece F:\BACKUP_4MNIFKR1_1_1
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=F:\BACKUP_4MNIFKR1_1_1 tag=TAG20120812T165249
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:08:51
Finished restore at 12-AUG-12

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

datafile 1 switched to datafile copy
input datafile copy recid=45 stamp=791140890 filename=F:\ORACLE\PRODUCT\10.2.0\O
RADATA\ORCLMAN\SYSTEM01.DBF
datafile 2 switched to datafile copy
input datafile copy recid=46 stamp=791140891 filename=F:\ORACLE\PRODUCT\10.2.0\O
RADATA\ORCLMAN\UNDOTBS2.DBF
datafile 3 switched to datafile copy
input datafile copy recid=47 stamp=791140892 filename=F:\ORACLE\PRODUCT\10.2.0\O
RADATA\ORCLMAN\SYSAUX01.DBF
datafile 4 switched to datafile copy
input datafile copy recid=48 stamp=791140892 filename=F:\ORACLE\PRODUCT\10.2.0\O
RADATA\ORCLMAN\USERS01.DBF
datafile 5 switched to datafile copy
input datafile copy recid=49 stamp=791140892 filename=F:\ORACLE\PRODUCT\10.2.0\O
RADATA\ORCLMAN\EXAMPLE01.DBF
datafile 6 switched to datafile copy
input datafile copy recid=50 stamp=791140892 filename=F:\ORACLE\PRODUCT\10.2.0\O
RADATA\ORCLMAN\ORADATA1.DBF
datafile 7 switched to datafile copy
input datafile copy recid=51 stamp=791140893 filename=F:\ORACLE\PRODUCT\10.2.0\O
RADATA\ORCLMAN\ORADATA2.DBF
datafile 8 switched to datafile copy
input datafile copy recid=52 stamp=791140893 filename=F:\ORACLE\PRODUCT\10.2.0\O
RADATA\ORCLMAN\SYSTEM02.DBF
datafile 9 switched to datafile copy
input datafile copy recid=53 stamp=791140893 filename=F:\ORACLE\PRODUCT\10.2.0\O
RADATA\ORCLMAN\MYIDX01.DBF
datafile 10 switched to datafile copy
input datafile copy recid=54 stamp=791140893 filename=F:\ORACLE\PRODUCT\10.2.0\O
RADATA\ORCLMAN\UNDOTBS02.DBF
datafile 11 switched to datafile copy
input datafile copy recid=55 stamp=791140894 filename=F:\ORACLE\PRODUCT\10.2.0\O
RADATA\ORCLMAN\ASM_LMT.DBF
datafile 12 switched to datafile copy
input datafile copy recid=56 stamp=791140894 filename=F:\ORACLE\PRODUCT\10.2.0\O
RADATA\ORCLMAN\PERFSTAT.DBF
datafile 13 switched to datafile copy
input datafile copy recid=57 stamp=791140894 filename=F:\ORACLE\PRODUCT\10.2.0\O
RADATA\ORCLMAN\ORADATA3.DBF
datafile 14 switched to datafile copy
input datafile copy recid=58 stamp=791140894 filename=F:\ORACLE\PRODUCT\10.2.0\O
RADATA\ORCLMAN\STREAMS_TBS.DBF
datafile 15 switched to datafile copy
input datafile copy recid=59 stamp=791140894 filename=F:\ORACLE\PRODUCT\10.2.0\O
RADATA\ORCLMAN\ORCL\DATAFILE\O1_MF_USER123_81MTG1GN_.DBF
datafile 16 switched to datafile copy
input datafile copy recid=60 stamp=791140895 filename=F:\ORACLE\PRODUCT\10.2.0\O
RADATA\ORCLMAN\GGTBS.DBF
Finished Duplicate Db at 12-AUG-12
至此，DUPLICATE命令執行完畢！
6.完善的工作
在建立standby資料庫的過程中，雖然屬於duplicate命令已經全部完成，不過data guard環境配置不僅僅只有建立standby資料庫，一個Data Guard環境至少包括一主一備，而且主備之間有互動才算是完整的Data Guard環境。
(1)修改primary資料庫的相關引數，主要設定LOG_ARCHIVE_*引數實現傳送歸檔到standby端。
ORCL> alter system set log_archive_config='dg_config=(orcl,orclman)';
ORCL> alter system set log_archive_dest_2='SERVICE=orclman lgwr async valid_for=(
online_logfiles,primary_role) db_unique_name=orclman';
(2)啟動standby資料庫到MOUNT狀態，並開啟REDO應用
ORCLMAN>alter database recover managed standby database disconnect from session;
Database altered.
(3)在Primary上切換日誌，驗證同步效果
ORCL>select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
274
ORCL>alter system switch logfile;

System altered.

ORCL>select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
275
(4)在standby端檢視是否接受到primary端傳來的歸檔日誌
ORCLMAN>select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
275
Data Guard配置成功！O(∩_∩)O哈哈~！透過RMAN的DUPLICATE命令建立物理standby，實際步驟要簡單一些，
基本上只需要記住DUPLICATE命令的語法就好，其他工作RMAN都幫你做好了。DUPLICATE建立物理standby簡單吧！其實按照官方文件create physical standby database也不是很複雜。
----------------------------------------------------------&gt>
轉載於:http://blog.sina.com.cn/s/blog_a32eff28010120je.html

ORACLE DUPLICATE建立物理standby資料庫

相關文章