操作環境：Windows 2003 企業版 + Serveice pack 2資料庫：Oracle 9.2.0.1主庫SID:dbguard IP: 192.168.159.133從庫SID:dbguard IP: 192.168.159.131

其實網路上有很多關於data guard的安裝配置資料，不過真正做起來還是會遇到很多問題的；在小楊的幫忙下，總算搞定了。

oracle安裝採用OMF結構

1. 主從庫均Install Oracle 9i，且只選擇安裝軟體，不建立資料庫

2. 在主庫上使用DBCA，建立dbguard例項

3. 建立測試環境,建立test表空間和test使用者以及test表和簡單的幾條記錄

SQL> create table test

2 (ID integer,

3 Name varchar2(20)

4 );

表已建立。

SQL> insert into test values(1,'a');

已建立 1 行。

SQL> insert into test values(2,'b');

已建立 1 行。

SQL> commit;

提交完成。

SQL> select * from test;

ID NAME

---------- --------------------

1 a

2 b

4. 修改資料庫為歸檔方式

SQL*Plus: Release 9.2.0.1.0 - Production on 星期二 10月 9 20:02:24 2007

SQL> connect sys/wbq as sysdba;

SQL> startup mount;

SQL> alter database archivelog;

SQL> alter system set log_archive_dest_1='E:ORACLEora92databasearchive' scope=both;

SQL> alter system set log_archive_dest_2='service=standby' scope=both;

SQL> alter system set log_archive_start=true scope=spfile;

SQL> alter database open;

SQL> archive log list;

資料庫日誌模式存檔模式

自動存檔啟用

存檔終點 e:oracleoradataarchive

最早的概要日誌序列 1

下一個存檔日誌序列 2

當前日誌序列 2

SQL> show parameter archive

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

archive_lag_target integer 0

log_archive_dest string

log_archive_dest_1 string location=e:oracleoradataarchive

log_archive_dest_2 string service=standby

log_archive_duplex_dest string

log_archive_format string ARC%S.%T

log_archive_max_processes integer 2

log_archive_min_succeed_dest integer 1

log_archive_start boolean TRUE

log_archive_trace integer 0

remote_archive_enable string true

standby_archive_dest string %ORACLE_HOME%RDBMS

SQL> shutdown immediate;

5. 建立standby 控制檔案和便於修改的pfile

SQL> alter database force logging;

資料庫已更改。

SQL> select name,force_logging from v$database;

NAME FOR

--------- ---

DBGUARD YES

SQL> create pfile=' E:oracleadmindbguardpfilepfile.ora' from spfile;

檔案已建立。

SQL> alter database create standby controlfile as 'e:oraclecontrol01.ctl';

6. 確定需要複製相應的資料檔案、日誌檔案等，並關閉資料庫

SQL> select file_name from dba_data_files;

FILE_NAME

--------------------------------------------------------------------------------

E:ORACLEORADATADBGUARDSYSTEM01.DBF

E:ORACLEORADATADBGUARDUNDOTBS01.DBF

E:ORACLEORADATADBGUARDCWMLITE01.DBF

E:ORACLEORADATADBGUARDDRSYS01.DBF

E:ORACLEORADATADBGUARDEXAMPLE01.DBF

E:ORACLEORADATADBGUARDINDX01.DBF

E:ORACLEORADATADBGUARDODM01.DBF

E:ORACLEORADATADBGUARDTOOLS01.DBF

E:ORACLEORADATADBGUARDUSERS01.DBF

E:ORACLEORADATADBGUARDXDB01.DBF

E:ORACLEORADATADBGUARDTEST.ORA

已選擇11行。

SQL> select member from v$logfile;

MEMBER

--------------------------------------------------------------------------------

E:ORACLEORADATADBGUARDREDO03.LOG

E:ORACLEORADATADBGUARDREDO02.LOG

E:ORACLEORADATADBGUARDREDO01.LOG

SQL>shutdown immediate

7. 建立standby伺服器相應的Oracle目錄，並把主庫檔案複製到standby機器的指定目錄下

Mkdir E:oracleadmindbguardbdump

Mkdir E:oracleadmindbguardcdump

Mkdir E:oracleadmindbguardcreate

Mkdir E:oracleadmindbguardpfile

Mkdir E:oracleadmindbguardudump

Mkdir D:oracleoradatadbguard

Mkdir D:oracleoradatadbguardarchive

8. 建立dbguard的window服務

Oradim –NEW –SID dbguard –STARDMODE manual

複製透過主庫建立的standby控制檔案，並分別複製為control02.ctl,control03.ctl,並複製到相應的目錄下

複製透過主庫建立的引數檔案並加以修改，新增以下資訊

*.standby_archive_dest='E:oracleoradataarchive'

*.fal_server='primary'

*.fal_client='satndby'

*.standby_file_management=auto

*.lock_name_space='dbguard'

9. 建立密碼檔案

C:>orapwd file=E:oracleora92DATABASEPWDdbguard.ORA password=test

10. 配置主從伺服器的listener.ora

--standby從庫為以下資訊；主庫修改為192.168.159.133

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.159.131)(PORT = 1521))

)

11. 分別配置主從伺服器的tnsname.ora保持一致

STANDBY =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.159.131)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = dbguard)

)

PRIMARY =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.159.133)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = dbguard)

)

12. 重新啟動lsn偵聽，並進行偵聽檢視主從機是否能夠監聽

Lsnrctl stop

Lsnrctl start

Tnsping standby

Tnsping primary

13. .啟動物理Standby資料庫

SQL> conn sys/test@dbguard as sysdba

已連線到空閒例程。

SQL> startup nomount;

ORACLE 例程已經啟動。

Total System Global Area 101784276 bytes

Fixed Size 453332 bytes

Variable Size 75497472 bytes

Database Buffers 25165824 bytes

Redo Buffers 667648 bytes

SQL> create spfile from pfile;

檔案已建立。

SQL> ALTER DATABASE MOUNT STANDBY DATABASE;

資料庫已更改。

14. 在Standby資料庫上，初始化Log Apply 服務

SQL> alter database recover managed standby database disconnect from session;

資料庫已更改

15. 安裝完的的驗證

在primary上

SQL> select sequence#,first_time,next_time from v$archived_log;

SEQUENCE# FIRST_TIME NEXT_TIME

---------- ---------- ----------

3 xxxx-xx-xx xxxx-xx-xx

在standby上

SQL> select sequence#,first_time,next_time from v$archived_log;

未選定行

在primary上

SQL> alter system archive log current;

系統已更改。

SQL> select sequence#,first_time,next_time from v$archived_log;

SEQUENCE# FIRST_TIME NEXT_TIME

---------- ---------- ----------

xxxx-xx-xx xxxx-xx-xx

在standby上

SQL> select sequence#,first_time,next_time from v$archived_log;

SEQUENCE# FIRST_TIME NEXT_TIME

---------- ---------- ----------

xxxx-xx-xx xxxx-xx-xx xx

SQL> select sequence#,applied from v$archived_log;

SEQUENCE# APP

---------- ---

5 YES

--Yes即OK

在standby上

SQL> select process,status from v$managed_standby;

PROCESS STATUS

------- ------------

ARCH CONNECTED

MRP0 WAIT_FOR_LOG

RFS RECEIVING

Windows環境下的Oracle Data Guard安裝和配置

相關文章