在單機上創建物理的Oracle9i standby數據庫
2024-08-29 13:46:11
供稿：網友
活躍在itpub,csdn,cnoug等論壇上上聞名的fenng的大作，非常佩服！
注一下：一下說明是fenng本人寫的，可不是我寫的，呵呵！
說明；Oracle9i數據庫的Data Guard 特性確保對數據進行完整的保護,是Oracle 9i的一個要害特性之一．Data Guard可以創建物理的Standby數據庫，也可以創建邏輯的Standby數據庫,還可以混合使用，靈活性比較強．這個文檔是Fenng練習時候記下來的東西，談不上是什么心得．*這不過是一份安裝過程中的筆記而已* 假如對這個有更高的期望，或者想得到關于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:/oracle/oradata/demo/archive
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
備份數據庫
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/oradata/DEMO/Archive' SCOPE=BOTH;
此操作直接生效
2.創建物理的Standby數據庫
2.1 標記出Primary數據庫的數據文件
SQL> select name from v$datafile;
NAME
-----------------------------------------------------
D:/ORACLE/ORADATA/DEMO/SYSTEM01.DBF
D:/ORACLE/ORADATA/DEMO/UNDOTBS01.DBF
D:/ORACLE/ORADATA/DEMO/CWMLITE01.DBF
D:/ORACLE/ORADATA/DEMO/DRSYS01.DBF
D:/ORACLE/ORADATA/DEMO/EXAMPLE01.DBF
D:/ORACLE/ORADATA/DEMO/INDX01.DBF
D:/ORACLE/ORADATA/DEMO/ODM01.DBF
D:/ORACLE/ORADATA/DEMO/TOOLS01.DBF
D:/ORACLE/ORADATA/DEMO/USERS01.DBF
D:/ORACLE/ORADATA/DEMO/XDB01.DBF
D:/ORACLE/ORADATA/DEMO/OEM_REPOSITORY.DBF
11 rows selected.
SQL>
2.2 關閉Instance 拷貝數據文件到既定目的地
SQL> SHUTDOWN IMMEDIATE;
2.3 為Standby 數據庫創建控制文件
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE
AS 'C:/DataGuard/Pstandby/DEMO/DEMOSTANDBY.CTL';
要注重這個控制文件的名字不要和Primary的控制文件名字重復
2.4 為Standby數據庫預備初始化參數文件名字：
SQL> CREATE PFILE='C:/DataGuard/Pstandby/DEMO/initpstandby' FROM SPFILE;
2.5 設定初始化Physical Standby Database參數
*.aq_tm_processes=1
*.background_dump_dest='D:/oracle/admin/DEMO/bdump'
*.compatible='9.2.0.0.0'
[b:9d9b27cba6]*.control_files='C:/DataGuard/Pstandby/DEMO/DEMOSTANDBY.CTL'[/b:9d9b27cba6]
*.core_dump_dest='D:/oracle/admin/DEMO/cdump'
*.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
[b:9d9b27cba6]*.instance_name='pstandby'[/b:9d9b27cba6]
*.java_pool_size=20971520
*.job_queue_processes=10
*.large_pool_size=7340032
*.log_archive_dest_1='location=d:/oracle/oradata/demo/archive'
*.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:/oracle/admin/DEMO/udump'
[b:9d9b27cba6]lock_name_space=pstandby
standby_file_management=AUTO
remote_archive_enable=TRUE
standby_archive_dest='C:/DataGuard/Pstandby/DEMO/Archive'
db_file_name_convert=('D:/oracle/oradata/DEMO', 'C:/DataGuard/Pstandby/DEMO/')
log_file_name_convert=('D:/oracle/oradata/DEMO', 'C:/DataGuard/Pstandby/DEMO/')
log_archive_dest_1=('LOCATION=C:/DataGuard/Pstandby/DEMO/Archive')[/b:9d9b27cba6]
整個操作的過程中，輕易出現錯誤的地方幾乎都集中在此處。必須認真仔細的對待這個文件。標記為黑色的地方是需要進行修改的。
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:/DataGuard/Pstandby/DEMO/initPstandby.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
2 order by sequence#;
SEQUENCE# APP
---------- ---
38 YES
39 YES
40 YES
41 YES
42 YES
43 YES
OK.表明我們還是成功的。暫時告一段落。
參考文檔
Oracle Data Guard Concepts and Administration
Release 2 (9.2)
Part Number A96653-02
附加內容：
primary數據庫的Pfile內容：
*.aq_tm_processes=1
*.background_dump_dest='D:/oracle/admin/DEMO/bdump'
*.compatible='9.2.0.0.0'
*.control_files='D:/oracle/oradata/DEMO/CONTROL01.CTL',
'D:/oracle/oradata/DEMO/CONTROL02.CTL','D:/oracle/oradata/DEMO/CONTROL03.CTL'
*.core_dump_dest='D:/oracle/admin/DEMO/cdump'
*.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:/oracle/oradata/demo/archive'
*.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:/oracle/admin/DEMO/udump'
創建過程中的可能的錯誤：待續
