搭建 Oracle10g DataGuard

搭建 Oracle10g DataGuard

primary資料庫

os：winxp
ip：10.0.10.112
db：ora10.2.0.4
dbname：lixiang

stanby資料庫

os：winxp
ip：10.0.10.114
db：ora10.2.0.4
dbname：lixiangdg

[@more@]

物理standby 建立步驟：

一、primary資料庫相關操作(10.0.10.112)：

1、確認主庫處於歸檔模式
SQL> archive log list
資料庫日誌模式 存檔模式
自動存檔 啟用
存檔終點 G:oraclearchivelog
最早的聯機日誌序列 15
下一個存檔日誌序列 17
當前日誌序列 17

2、設定primary資料庫為FORCELOGGING模式
SQL> alter database force logging;
資料庫已更改。

3、建立standby資料庫控制檔案
SQL>alter database create standby controlfile as 'c:/lixiangdg.ctl';
資料庫已更改。

4、建立pfile修改DG用引數
SQL>create pfile='c:/pfile.ora' from spfile;
檔案已建立

5、修改c:/pfile.ora 新增DG用引數
#--------配置primary
DB_UNIQUE_NAME=lixiang
LOG_ARCHIVE_CONFIG='DG_CONFIG=(lixiang,lixiangdg)'
LOG_ARCHIVE_DEST_1='LOCATION=G:oraclearchivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=lixiang'
LOG_ARCHIVE_DEST_2='SERVICE=lixiangdg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=lixiangdg'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

#--------配置standby 角色的引數用於角色轉換
FAL_SERVER=lixiangdg
FAL_CLIENT=lixiang
DB_FILE_NAME_CONVERT='oradatalixiangdg','oradatalixiang'
LOG_FILE_NAME_CONVERT='oradatalixiangdg','oradatalixiang'
STANDBY_FILE_MANAGEMENT=AUTO

6、透過pfile 重建spfile
SQL> shutdown immediate
...
SQL> create spfile from pfile='c:/pfile.ora';
檔案已建立。

7、複製資料檔案、日誌檔案、pfile、PWDlixiang.ora、standby controlfile(lixiangdg.ctl) 至 stanby資料庫

8、配置tnsname.ora
LIXIANG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.10.112)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = lixiang)
)
)

LIXIANGDG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.10.114)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = lixiangdg)
)
)
9、tnsping 測試tnsname
C:Documents and SettingsAdministrator>tnsping lixiang

...

已使用 TNSNAMES 介面卡來解析別名
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.10.112)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME =

lixiang)))
OK (30 毫秒)

C:Documents and SettingsAdministrator>tnsping lixiangdg

...

已使用 TNSNAMES 介面卡來解析別名
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.10.114)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME =

lixiangdg)))
OK (30 毫秒)

二、standby資料庫相關操作(10.0.10.114)：

1、建立新的OracleService
C:Documents and SettingsAdministrator>oradim -new -sid lixiangdg
例項已建立

2、建立目錄
E:G:oracleproduct10.2.0admin>mkdir lixiangdg
E:G:oracleproduct10.2.0adminlixiangdg>mkdir adump
E:G:oracleproduct10.2.0adminlixiangdg>mkdir bdump
E:G:oracleproduct10.2.0adminlixiangdg>mkdir cdump
E:G:oracleproduct10.2.0adminlixiangdg>mkdir udump
E:G:oracleproduct10.2.0adminlixiangdg>mkdir pfile

3、將資料檔案、日誌檔案、PWDlixiang.ora，基於lixiangdg.ctl複製三份lixiangdg01.ctl,lixiangdg02.ctl,lixiangdg03.ctl放到相應位置，資料檔案等位置保持與主庫一致。

4、修改從主庫傳送的pfile.ora
#--------配置standy
db_unique_name=lixiangdg
LOG_ARCHIVE_CONFIG='DG_CONFIG=(lixiang,lixiangdg)'
DB_FILE_NAME_CONVERT='oradatalixiang','oradatalixiangdg'
LOG_FILE_NAME_CONVERT='oradatalixiang','oradatalixiangdg'
LOG_ARCHIVE_FORMAT=log%t_%s_%r.arc
LOG_ARCHIVE_DEST_1='LOCATION=E:ora10goradatalixiangdg
VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=lixiangdg'
LOG_ARCHIVE_DEST_STATE_1=ENABLE

#---下列引數用於角色切換
LOG_ARCHIVE_DEST_2='SERVICE=lixiang LGWR ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=lixiang'
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
FAL_SERVER=lixiang
FAL_CLIENT=lixiangdg
STANDBY_FILE_MANAGEMENT=AUTO
同時修改*_dest的路徑

5、透過pfile 重建spfile
SQL> create spfile from pfile='c:/pfile.ora';
檔案已建立。

6、配置tnsname.ora
LIXIANG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.10.112)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = lixiang)
)
)

LIXIANGDG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.10.114)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = lixiangdg)
)
)
7、tnsping 測試tnsname
C:Documents and SettingsAdministrator>tnsping lixiang

...

已使用 TNSNAMES 介面卡來解析別名
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.10.112)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME =

lixiang)))
OK (30 毫秒)

C:Documents and SettingsAdministrator>tnsping lixiangdg

...

已使用 TNSNAMES 介面卡來解析別名
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.10.114)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME =

lixiangdg)))
OK (30 毫秒)

8、啟動standby資料庫到mount
SQL> startup mount;
ORACLE 例程已經啟動。
Total System Global Area 167772160 bytesFixed Size 1289484 bytes
Variable Size 62915316 bytes
Database Buffers 96468992 bytes
Redo Buffers 7098368 bytes
資料庫裝載完畢。

9、啟動redo 應用
SQL> alter database recover managed standby database disconnect from session;
資料庫已更改。

10、檢視同步情況：

首先連線到primary 資料庫
SQL> show parameter instance_name;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
instance_name string lixiang

SQL> alter system switch logfile;
系統已更改。

SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
51

SQL> select d.NAME,d.DATABASE_ROLE,d.PROTECTION_MODE,d.PROTECTION_LEVEL from v$database d;

NAME DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL
--------- ---------------- -------------------- --------------------
LIXIANG PRIMARY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE

連線到standby 資料庫
SQL> show parameter instance_name;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
instance_name string lixiangdg

SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
51

至此物理standby DG搭建完成