Oracle9i standby 資料庫筆記(zt)

.
簡單介紹
說明：Oracle9i 資料庫的 Data Guard 特性確保對資料進行完整的保護，是 Oracle 9i 的一個關鍵特性之一。Data Guard 可以建立物理的 Standby 資料庫，也可以建立邏輯的Standby資料庫,還可以混合使用，靈活性比較強．這個文件是Fenng練習時候記下來的東西，談不上是什麼心得。這不過是一份安裝過程中的筆記而已，大約寫於一年以前，如果對Standby機制的瞭解有更高的期望，或者想得到關於Oracle資料庫的data guard 和Standby的更多資訊，請參考官方的文件。

系統環境版本資訊描述：Windows 2000 專業版　SP3　512M 記憶體
現有的資料庫例項 (Primary)名字：Demo
預建立的Standby資料庫例項名字：Pstandby

資料庫版本資訊：

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 Mode
Automatic archival Enabled
Archive destination d:oracleoradatademoarchive
Oldest online log sequence 42
Next log sequence to archive 44
Current log sequence 44

如果不在歸檔模式下，調整資料庫。首先提交命令修改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:oracleoradataDEMOArchive' SCOPE=BOTH;

此操作直接生效

2.建立物理的Standby資料庫

2.1 標記出Primary資料庫的資料檔案
SQL> SELECT NAME FROM v$datafile;
NAME
-----------------------------------------------------
D:ORACLEORADATADEMOSYSTEM01.DBF
D:ORACLEORADATADEMOUNDOTBS01.DBF
D:ORACLEORADATADEMOCWMLITE01.DBF
D:ORACLEORADATADEMODRSYS01.DBF
D:ORACLEORADATADEMOEXAMPLE01.DBF
D:ORACLEORADATADEMOINDX01.DBF
D:ORACLEORADATADEMOODM01.DBF
D:ORACLEORADATADEMOTOOLS01.DBF
D:ORACLEORADATADEMOUSERS01.DBF
D:ORACLEORADATADEMOXDB01.DBF
D:ORACLEORADATADEMOOEM_REPOSITORY.DBF
11 rows selected.
SQL>

2.2 關閉Instance 複製資料檔案到既定目的地
SQL> SHUTDOWN IMMEDIATE;

2.3 為Standby 資料庫建立控制檔案
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE
AS 'C:DataGuardPstandbyDEMODEMOSTANDBY.CTL';

要注意這個控制檔案的名字不要和Primary的控制檔名字重複

2.4 為Standby資料庫準備初始化引數檔名字
SQL> CREATE PFILE='C:DataGuardPstandbyDEMOinitpstandby' FROM SPFILE;

2.5 設定初始化Physical Standby Database引數
*.aq_tm_processes=1
*.background_dump_dest='D:oracleadminDEMObdump'
*.compatible='9.2.0.0.0'
*.control_files='C:DataGuardPstandbyDEMODEMOSTANDBY.CTL'
*.core_dump_dest='D:oracleadminDEMOcdump'
*.db_block_size=8192
*.db_cache_size=19922944
*.db_domain=''
*.db_file_multiblock_read_count=32
*.db_name='DEMO'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=DEMOXDB)'
*.fast_start_mttr_target=300
*.hash_area_size=1048576
*.hash_join_enabled=TRUE
*.instance_name='pstandby'
*.java_pool_size=20971520
*.job_queue_processes=10
*.large_pool_size=7340032
*.log_archive_dest_1='location=d:oracleoradatademoarchive'
*.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='D:oracleadminDEMOudump'

lock_name_space=pstandby
standby_file_management=AUTO
remote_archive_enable=TRUE
standby_archive_dest='C:DataGuardPstandbyDEMOArchive'
db_file_name_convert=('D:oracleoradataDEMO', 'C:DataGuardPstandbyDEMO')
log_file_name_convert=('D:oracleoradataDEMO', 'C:DataGuardPstandbyDEMO')
log_archive_dest_1=('LOCATION=C:DataGuardPstandbyDEMOArchive')

整個操作的過程中，容易出現錯誤的地方几乎都集中在此處。必須認真仔細的對待這個檔案。標記為黑色的地方是需要進行修改的。

2.6 建立一個Windows服務
WINNT> oradim -NEW -SID Pstandby -STARTMODE manual

2.7 Create a Server Parameter File for the Standby Database
可參考執行如下操作：

C:>SET oracle_sid=pstandby
C:>SQLPLUS /nolog
SQL> CONNECT / as sysdba
SQL> CREATE SPFILE FROM PFILE='C:DataGuardPstandbyDEMOinitPstandby.ora';

2.8 啟動物理Standby資料庫
C:>set oracle_sid=pstandby
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=Pstandby' SCOPE=BOTH;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE SCOPE=BOTH;

2.11 啟動遠端歸檔
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

3.安裝完的的驗證

3.1在database,　查詢V$ARCHIVED_LOG
(其實也可以直接到相關目錄下檢視Log是否建立):

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME
2 FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# FIRST_TIME NEXT_TIME
---------- ---------- ----------
38 23-7? -02 23-7? -02
39 23-7? -02 23-7? -02
40 23-7? -02 23-7? -02
41 23-7? -02 23-7? -02
42 23-7? -02 23-7? -02

3.2 在Primary資料庫上，歸檔當前的Log
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

3.3 驗證是否收到
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME
2> FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# FIRST_TIME NEXT_TIME
---------- ---------- ----------
38 23-7? -02 23-7? -02
39 23-7? -02 23-7? -02
40 23-7? -02 23-7? -02
41 23-7? -02 23-7? -02
42 23-7? -02 23-7? -02
43 23-7? -02 23-7? -02

3.4 驗證是否新的歸檔Redo日誌已經被應用
SQL> SELECT sequence#, applied FROM v$archived_log ORDER BY sequence#;
SEQUENCE# APP
---------- ---
38 YES
39 YES
40 YES
41 YES
42 YES
43 YES

OK.表明我們還是成功的。暫時告一段落。

附加內容
primary資料庫的Pfile內容

*.aq_tm_processes=1
*.background_dump_dest='D:oracleadminDEMObdump'
*.compatible='9.2.0.0.0'
*.control_files='D:oracleoradataDEMOCONTROL01.CTL',
　　　'D:oracleoradataDEMOCONTROL02.CTL','D:oracleoradataDEMOCONTROL03.CTL'
*.core_dump_dest='D:oracleadminDEMOcdump'
*.db_block_size=8192
*.db_cache_size=19922944
*.db_domain=''
*.db_file_multiblock_read_count=32
*.db_name='DEMO'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=DEMOXDB)'
*.fast_start_mttr_target=300
*.hash_area_size=1048576
*.hash_join_enabled=TRUE
*.instance_name='DEMO'
*.java_pool_size=20971520
*.job_queue_processes=10
*.large_pool_size=7340032
*.log_archive_dest_1='location=d:oracleoradatademoarchive'
*.log_archive_dest_2='SERVICE=PSTANDBY'
*.log_archive_dest_state_2='ENABLE'
*.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='D:oracleadminDEMOudump'