【賽迪網－IT技術報道】說明：Oracle9i 數據庫的 Data Guard 特性確保對數據進行完整的保護，是 Oracle 9i 的一個關鍵特性之一。Data Guard 可以創建物理的 Standby 數據庫，也可以創建邏輯的Standby數據庫,還可以混合使用，靈活性比較強．如果對Standby機制的了解有更高的期望，或者想得到關於Oracle數據庫的data guard 和Standby的更多資訊，請參考官方的文檔。
現有的數據庫實例 (Primary)名字：orcl1
預創建的Standby數據庫實例名字：orcl2
數據庫版本資訊：
SQL> SELECT * FROM v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE 9.2.0.1.0 Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production
準備工作:
首先確認Primary數據庫是否在歸檔模式下
SQL> SHOW user
USER is "SYS"
SQL>
SQL> ARCHIVE log list
Database log mode Archive ModeAutomatic archival EnabledArchive destination D:\oracle\arcOldest online log sequence 28Next log sequence to archive 30Current log sequence 30如果不在歸檔模式下，調整數據庫。首先提交命令修改SPfile：
SQL>ALTER SYSTEM SET log_archive_start=TRUE SCOPE=SPFILE;
然後關閉數據庫實例
SQL>SHUTDOWN immediate;
備份數據庫
SQL>STARTUP MOUNT
SQL>ALTER DATABASE ARCHIVELOG;
SQL>ALTER DATABASE OPEN;
SQL>SHUTDOWN IMMEDIATE
備份
1. Primary Database 需要做的準備工作
1.1 激活 Forced Logging
SQL> ALTER DATABASE FORCE LOGGING;
1.2 設置本地歸檔目標
SQL> ALTER SYSTEM SET log_archive_dest_1='LOCATION=D:\oracle\arc' SCOPE=BOTH;
此操作直接生效
2.創建物理的Standby數據庫
2.1 標記出Primary數據庫的資料檔案
SQL> SELECT NAME FROM v$datafile;
NAME
-----------------------------------------------------
D:\ORACLE\ORADATA\ORCL1\SYSTEM01.DBFD:\ORACLE\ORADATA\ORCL1\UNDOTBS01.DBFD:\ORACLE\ORADATA\ORCL1\DRSYS01.DBFD:\ORACLE\ORADATA\ORCL1\INDX01.DBFD:\ORACLE\ORADATA\ORCL1\TOOLS01.DBFD:\ORACLE\ORADATA\ORCL1\USERS01.DBFD:\ORACLE\ORADATA\ORCL1\XDB01.DBF
2.2 關閉Instance 拷貝資料檔案到既定目的地
SQL> SHUTDOWN IMMEDIATE;
2.3 為Standby 數據庫創建控制文件
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE
AS 'E:\oracle\oradata\orcl2\STANDBY.CTL';
要注意這個控制文件的名字不要和Primary的控制文件名字重復
2.4 為Standby數據庫準備初始化參數文件名字
SQL> CREATE PFILE='E:\oracle\admin\orcl2\pfile\initorcl2.ora' FROM SPFILE;
2.5 設定初始化Physical Standby Database參數
*.aq_tm_processes=1
*.background_dump_dest='E:\oracle\admin\orcl2\bdump'
*.compatible='9.2.0.0.0'
*.control_files='E:\oracle\oradata\orcl2\STANDBY.CTL'
*.core_dump_dest='E:\oracle\admin\orcl2\cdump'
*.db_block_size=8192
*.db_cache_size=19922944
*.db_domain=''
*.db_file_multiblock_read_count=32
*.db_name='orcl1'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=DEMOXDB)'
*.fast_start_mttr_target=300
*.hash_area_size=1048576
*.hash_join_enabled=TRUE
*.instance_name='orcl2'
*.java_pool_size=20971520
*.job_queue_processes=10
*.large_pool_size=7340032
*.log_archive_dest_1='location=E:\oracle\arc'
*.log_archive_start=TRUE
*.open_cursors=300
*.optimizer_mode='FIRST_ROWS'
*.pga_aggregate_target=17825792
*.processes=150
*.query_rewrite_enabled='TRUE'
*.remote_login_passwordfile='EXCLUSIVE'
*.shared_pool_size=33554432
*.sort_area_size=1048576
*.star_transformation_enabled='TRUE'
*.timed_statistics=TRUE
*.undo_management='AUTO'
*.undo_retention=10800
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='E:\oracle\admin\orcl2\udump'
lock_name_space=orcl2
standby_file_management=AUTO
remote_archive_enable=TRUE
standby_archive_dest='E:\oracle\standbyarc'
db_file_name_convert=('D:\oracle\oradata\orcl1', 'E:\oracle\oradata\orcl2')
log_file_name_convert=('D:\oracle\oradata\orcl1', 'E:\oracle\oradata\orcl2')
log_archive_dest_1=('LOCATION=E:\oracle\standbyarc')
整個操作的過程中，容易出現錯誤的地方幾乎都集中在此處。必須認真仔細的對待這個文件。標記為黑色的地方是需要進行修改的。
2.6 創建一個Windows服務
WINNT> oradim -NEW -SID orcl2 -STARTMODE manual
2.7 Create a Server Parameter File for the Standby Database
可參考執行如下操作：
C:\>SET oracle_sid=orcl2
C:\>SQLPLUS /nolog
SQL> CONNECT / as sysdba
SQL> CREATE SPFILE FROM PFILE='E:\oracle\admin\orcl2\pfile\initorcl2.ora';
2.8 啟動物理Standby數據庫
C:\>set oracle_sid=orcl2
C:\>sqlplus /nolog
SQL> connect / as sysdba
SQL> STARTUP NOMOUNT;
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;
2.9 在Standby數據庫上，初始化Log Apply 服務
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
2.10 激活到物理Standby數據庫的歸檔
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=orcl2' SCOPE=BOTH;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE SCOPE=BOTH;
2.11 啟動遠程歸檔
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT; -在primary database上執行.
3.安裝完的的驗證
3.1 在Primary database上
alter system set standby_archive_dest='E:\oracle\standbyarc' scope=both;
3.2 在database,查詢V$ARCHIVED_LOG
(其實也可以直接到相關目錄下查看Log是否創建):
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME
FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# FIRST_TIM NEXT_TIME---------- --------- --------- 30 09-JAN-07 17-JAN-07 31 17-JAN-07 17-JAN-073.3 在Primary數據庫上，歸檔當前的Log
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
3.4 驗證是否收到
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME
FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# FIRST_TIM NEXT_TIME---------- --------- --------- 30 09-JAN-07 17-JAN-07 31 17-JAN-07 17-JAN-07 32 17-JAN-07 17-JAN-07
3.5 驗證是否新的歸檔Redo日誌已經被應用
SQL> SELECT sequence#, applied FROM v$archived_log ORDER BY sequence#;
SEQUENCE# APP---------- --- 30 YES 31 YES 32 YESOK.表明我們還是成功的.
附加內容:
primary數據庫的Pfile內容
*.aq_tm_processes=1
*.background_dump_dest='D:\oracle\admin\orcl1\bdump'
*.compatible='9.2.0.0.0'
*.control_files='D:\oracle\oradata\orcl1\control01.ctl','D:\oracle\oradata\orcl1\control02.ctl','D:\oracle\oradata\orcl1\control03.ctl'
*.core_dump_dest='D:\oracle\admin\orcl1\cdump'
*.db_block_size=8192
*.db_cache_size=25165824
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='orcl1'
*.dispatchers='(PROTOCOL=TCP)'
*.fast_start_mttr_target=300
*.hash_join_enabled=TRUE
*.instance_name='orcl1'
*.java_pool_size=33554432
*.job_queue_processes=10
*.large_pool_size=8388608
*.log_archive_dest_1='LOCATION=D:\oracle\arc'
*.log_archive_start=TRUE
*.open_cursors=300
*.pga_aggregate_target=25165824
*.processes=150
*.query_rewrite_enabled='FALSE'
*.remote_login_passwordfile='EXCLUSIVE'
*.shared_pool_size=50331648
*.sort_area_size=524288
*.star_transformation_enabled='FALSE'
*.timed_statistics=TRUE
*.undo_management='AUTO'
*.undo_retention=10800
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='D:\oracle\admin\orcl1\udump'
*.standby_archive_dest='E:\oracle\standbyarc'
*.remote_archive_enable=true
（責任編輯：盧兆林）
