在單機上創建物理的Oracle9i standby數據庫
2024-08-29 13:31:13
供稿：網友
說明；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 ystem01.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'
*.control_files='c:/dataguard/pstandby/demo/demostandby.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='pstandby'
*.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'
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')
整個操作的過程中，容易出現錯誤的地方幾乎都集中在此處。必須認真仔細的對待這個文件。標記為黑色的地方是需要進行修改的。
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'
創建過程中的可能的錯誤：待續
本文來源于網頁設計愛好者web開發社區http://www.html.org.cn收集整理，歡迎訪問。
