[转帖]Oracle9i Standby (Dataguard) 建立

1. 準備工作

• Primary Server IP:192.168.217.9
• Standby Server IP:192.168.217.23
• Primary Net Service Name: primary
• Standby Net Service Name: standby
• Primary ORACLE_SID=primary
• Standby ORACLE_SID=standby
• ORACLE_BASE=/opt/ora9
• ORACLE_HOME=/opt/ora9/product/9.2
• Primary 已經存在一個資料庫，Standby只要灌好Oracle軟體，不需要建立資料庫。
• Primary DataBase已經開啟於Archive Log模式
• Primary DB、Standby DB位於兩台不同的機器上
• Primary DB與Standby DB的目錄結構都相同

1. (primary)開啟Force Logging模式

   SQL> SQL*Plus: Release 9.2.0.8.0 - Production on 星期二 4月 1 17:51:14 2008 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. SQL> connect / as sysdba Connected. SQL> alter database force logging;

   
   
2. (primary)造出最新的pfile

   SQL> create pfile from spfile;

   
   
3. (primary)修改初始化參數($ORACLE_HOME\dbs\initprimary.ora)

   *.INSTANCE_NAME='primary' *.DB_NAME='primary' *.LOG_ARCHIVE_DEST_1='LOCATION=/opt/ora9/oradata/archive_log mandatory' *.LOG_ARCHIVE_DEST_2='SERVICE=standby OPTIONAL reopen=60 ARCH SYNC NOAFFIRM '--&gt(Maximum Performance 模式) *.LOG_ARCHIVE_DEST_STATE_1=ENABLE *.LOG_ARCHIVE_DEST_STATE_2=ENABLE *.LOG_ARCHIVE_FORMAT=%d_%t_%s.arc *.REMOTE_ARCHIVE_ENABLE=TRUE *.FAL_SERVER=standby *.FAL_CLIENT=primary *. STANDBY_ARCHIVE_DEST=/opt/ora9/oradata/archive_log *.STANDBY_FILE_MANAGEMENT=AUTO *._log_archive_callout='LOCAL_FIRST=TRUE'--&gt避免尖峰時間,switch log太快，系統hang住 *.archive_lag_target=1800

   
   
4. (primary)複製參數檔密碼檔至Standby DB

   $ scp $ORACLE_HOME/dbs/initprimary.ora    oracle@192.168.217.23:$ORACLE_HOME/dbs/initstandby.ora $ scp $ORACLE_HOME/dbs/orapwprimary    oracle@192.168.217.23:$ORACLE_HOME/dbs/orapwstandby

   
   
5. (Standby)修改 Standby DB 之參數檔($ORACLE_HOME\dbs\initstandby.ora)

   *.INSTANCE_NAME='standby' *.DB_NAME='primary' --&gt需要跟primary db相同 *.LOG_ARCHIVE_DEST_1='LOCATION=/opt/ora9/oradata/archive_log mandatory' *.LOG_ARCHIVE_DEST_2='SERVICE=primary OPTIONAL reopen=60 ARCH SYNC NOAFFIRM '--&gt(Maximum Performance 模式) *.LOG_ARCHIVE_DEST_STATE_1=ENABLE *.LOG_ARCHIVE_DEST_STATE_2=DEFER --&gt暫時關閉，等Switchover時才開啟。 *.LOG_ARCHIVE_FORMAT=%d_%t_%s.arc *.REMOTE_ARCHIVE_ENABLE=TRUE *.FAL_SERVER=primary *.FAL_CLIENT=standby *. STANDBY_ARCHIVE_DEST=/opt/ora9/oradata/archive_log *.STANDBY_FILE_MANAGEMENT=AUTO *._log_archive_callout='LOCAL_FIRST=TRUE' *.archive_lag_target=1800

   
   
6. (Standby)創建Standby DB上的相關目錄

   #su - oracle $ mkdir -p $ORACLE_BASE/backup --&gt用於 Rman備份 $ mkdir -p $ORACLE_BASE/oradata/primary --&gt資料庫目錄 $ mkdir -p $ORACLE_BASE/oradata/archive_log $ mkdir -p $ORACLE_BASE/oradata/standby_archive_log $ mkdir -p $ORACLE_BASE/admin/primary/bdump $ mkdir -p $ORACLE_BASE/admin/primary/cdump $ mkdir -p $ORACLE_BASE/admin/primary/create $ mkdir -p $ORACLE_BASE/admin/primary/pfile $ mkdir -p $ORACLE_BASE/admin/primary/udump

   
   
7. (Primary)修改$ORACLE_HOME/network/admin/listener.ora

   LISTENER = (DESCRIPTION_LIST = 　(DESCRIPTION = 　　(ADDRESS_LIST = 　　　(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC)) 　　) 　　(ADDRESS_LIST = 　　　　(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.217.9)(PORT = 1521)) 　　) 　) ) SID_LIST_LISTENER = (　　SID_LIST = 　　　(SID_DESC = 　　　　(SID_NAME = PLSExtProc) 　　　　(ORACLE_HOME = /opt/ora9/product/9.2) 　　　　(PROGRAM = extproc) 　　　) 　　　(SID_DESC = 　　　　(GLOBAL_DBNAME = primary) 　　　　(ORACLE_HOME = /opt/ora9/product/9.2) 　　　　(SID_NAME = primary) 　　　) )

   
   
8. (Standby)修改$ORACLE_HOME/network/admin/listener.ora

   LISTENER = (DESCRIPTION_LIST = 　(DESCRIPTION = 　　(ADDRESS_LIST = 　　　(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC)) 　　) 　　(ADDRESS_LIST = 　　　　(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.217.23)(PORT = 1521)) 　　) 　) ) SID_LIST_LISTENER = (　　SID_LIST = 　　　(SID_DESC = 　　　　(SID_NAME = PLSExtProc) 　　　　(ORACLE_HOME = /opt/ora9/product/9.2) 　　　　(PROGRAM = extproc) 　　　) 　　　(SID_DESC = 　　　　(GLOBAL_DBNAME = standby) 　　　　(ORACLE_HOME = /opt/ora9/product/9.2) 　　　　(SID_NAME = standby) 　　　) )

   
   
9. (Standby&Primary)修改$ORACLE_HOME/network/admin/sqlnet.ora

   NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME) SQLNET.EXPIRE_TIME = 2

   
   
10. (Primary)修改$ORACLE_HOME/network/admin/tnsnames.ora

    MASTER = (DESCRIPTION = 　 (ADDRESS_LIST = 　　　　(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.217.9)(PORT = 1521)) 　　) 　(CONNECT_DATA = 　　　　(SERVER = DEDICATED) 　　　　(SERVICE_NAME = master) 　) ) STANDBY = (DESCRIPTION = 　(ADDRESS_LIST = 　　(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.217.23)(PORT = 1521)) 　) 　(CONNECT_DATA = 　　(SERVER = DEDICATED) 　　(SERVICE_NAME = standby) 　) )

    
    
11. (Standby)修改$ORACLE_HOME/network/admin/tnsnames.ora

    MASTER = (DESCRIPTION = 　 (ADDRESS_LIST = 　　　　(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.217.9)(PORT = 1521)) 　　) 　(CONNECT_DATA = 　　　　(SERVER = DEDICATED) 　　　　(SERVICE_NAME = master) 　) ) STANDBY = (DESCRIPTION = 　(ADDRESS_LIST = 　　(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.217.23)(PORT = 1521)) 　) 　(CONNECT_DATA = 　　(SERVER = DEDICATED) 　　(SERVICE_NAME = standby) 　) )

    
    
12. (Primary)(Standby)若有修改listner.ora　需重新啟動listener

    #su - oracle $ lsnrctl stop $ lsnrctl start $ lsnrctl status

    
    
13. (Standby)由pfile創出spfile，並且開啟Standby DB於nomount模式

    [oracle@standby dbs]$ sqlplus /nolog SQL*Plus: Release 9.2.0.8.0 - Production on 星期二 4月 1 23:41:33 2008 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. SQL> connect / as sysdba Connected to an idle instance. SQL> create spfile from pfile; File created. SQL> startup nomount; ORACLE instance started. Total System Global Area 235999648 bytes Fixed Size 450976 bytes Variable Size 201326592 bytes Database Buffers 33554432 bytes Redo Buffers 667648 bytes SQL> exit

    
    
14. (Primary)使用Rman備份Primary資料庫,準備Duplicate至Standby DB

    [oracle@primary ora9]$ rman target / nocatalog Recovery Manager: Release 9.2.0.8.0 - Production Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved. connected to target database: MASTER (DBID=2710930473) using target database controlfile instead of recovery catalog RMAN> backup filesperset 5 database format '/opt/ora9/backup/%d_t%t_s%s_p%p' include current controlfile for standby; Starting backup at 02-4月 -08 allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=18 devtype=DISK channel ORA_DISK_1: starting full datafile backupset channel ORA_DISK_1: specifying datafile(s) in backupset input datafile fno=00013 name=/opt/ora9/oradata/primary/DBS3.dbf input datafile fno=00006 name=/opt/ora9/oradata/primary/indx01.dbf input datafile fno=00009 name=/opt/ora9/oradata/primary/users01.dbf input datafile fno=00004 name=/opt/ora9/oradata/primary/drsys01.dbf input datafile fno=00008 name=/opt/ora9/oradata/primary/tools01.dbf ...... including current SPFILE in backupset including standby controlfile in backupset ...... RMAN> sql 'alter system archive log current'; sql statement: alter system archive log current RMAN> backup archivelog all ; Starting backup at 02-4月 -08 current log archived using channel ORA_DISK_1 .....

    
    
15. (Primary)copy 剛剛的備份至Standby DB

    $ scp $ORACLE_BASE/backup/* oracle@192.168.217.23:/$ORACLE_BASE/backup

    
    
16. (Primary)Duplicate 剛剛的備份至Standby DB

    RMAN> connect auxiliary sys@standby; auxiliary database Password: connected to auxiliary database: master (not mounted) RMAN> duplicate target database for standby nofilenamecheck dorecover; Starting Duplicate Db at 02-4月 -08 using channel ORA_AUX_DISK_1 printing stored script. Memory Script { restore clone standby controlfile to clone_cf; --&gt恢復Standby control file replicate clone controlfile from clone_cf; sql clone 'alter database mount standby database'; --&gt原本Standby 開啟於nomount模式'，Rman將其轉變成mount 模式 } executing script. Memory Script Starting restore at 02-4月 -08 using channel ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: starting datafile backupset restore channel ORA_AUX_DISK_1: restoring controlfile output filename=/opt/ora9/oradata/master/control_sdby01.ctl channel ORA_AUX_DISK_1: restored backup piece 1 .....

    
    
17. (Primary)重新啟動Primary，使用剛剛修改過的initprimary.ora

    [oracle@baksrv dbs]$ sqlplus /nolog SQL*Plus: Release 9.2.0.8.0 - Production on 星期三 4月 2 11:41:05 2008 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. SQL> connect / as sysdba Connected. SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> create spfile from pfile; --&gt做出新的spfile File created. SQL> startup; ORACLE instance started. Total System Global Area 235999648 bytes Fixed Size 450976 bytes Variable Size 201326592 bytes Database Buffers 33554432 bytes Redo Buffers 667648 bytes Database mounted. Database opened. SQL> alter system switch logfile;--&gtswitch log一次 System altered. SQL> select * from v$log;　--&gt目前的log sequence#　為148,最新的archived log為147 GROUP# 　THREAD# 　SEQUENCE#　 BYTES 　MEMBERS　 ARC　 STATUS 　FIRST_CHANGE# 　　FIRST_TIME ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------- 1　　　　 1 　　　　　146　　　 　　104857600 　1 　　　YES 　　INACTIVE　 2178807 02-4月 -08 2 　　　　1 　　　　　147 　　　　　104857600 　1 　　　YES 　　ACTIVE 　　2178808 02-4月 -08 3 　　　　1 　　　　　148 　　　　　104857600 　1 　　　NO 　　CURRENT 　2179548 02-4月 -08 8 　　　　1 　　　　　145 　　　　　104857600 　1 　　　YES 　　INACTIVE　 2176247 02-4月 -08

    
    
18. (Standby)檢查Standby　是否有接收到Archived Log

    SQL> select sequence#,applied from v$archived_log; SEQUENCE# APP ---------- --- 146 NO 145 NO 147 NO --&gt已經接收到147...Ｄata Guard已經大致上完成了^_^ SQL> alter database recover managed standby database disconnect from session; --&gt開始做自動 standby recover Database altered. SQL> select sequence#,applied from v$archived_log; SEQUENCE# APP ---------- --- 146 YES 145 YES 147 YES --&gt原本NO的狀態，已經變成YES

    
    
19. (Standby)加入Standby Redo Log..為了Switchover..建議Primary也加入

    SQL> alter database recover managed standby database cancel; SQL> Alter database add standby logfile '/opt/ora9/oradata/primary/stdby_redo01.log' size 100M; SQL> Alter database add standby logfile '/opt/ora9/oradata/primary/stdby_redo02.log' size 100M; SQL> Alter database add standby logfile '/opt/ora9/oradata/primary/stdby_redo03.log' size 100M; SQL> alter database recover managed standby database disconnect from session;

    
    
20. (Standby)檢查Standby

    SQL> select process,status,client_process,sequence#,block# from v$managed_standby; SQL> select * from v$standby_log; --&gt檢查Standby　Redo log是否啟用。目前log sequence為150 GROUP#　　 THREAD# 　　　SEQUENCE#　　 BYTES 　USED　 ARC 　STATUS FIRST_CHANGE# 　FIRST_TIME LAST_CHANGE# LAST_TIME ---------- ---------- ---------- ---------- ---------- --- ---------- ------------- ---------- ------------ ---------- 4 　　　1 　　　150 　　　　104857600　　 0 　　　　　YES 　　　ACTIVE　　　　 2180477 02-4月 -08 0 5　　　 0　　　 0 　　　　　104857600 　　1024 　　　　YES　　　 UNASSIGNED　 0 0 6　　　 0　　　 0 　　　　　104857600　　 1024　　　　 YES 　　　UNASSIGNED　 0 0 SQL>

    
    

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/35489/viewspace-561364/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/35489/viewspace-561364/