--測試環境：
OS：Redhat linux(64)
Primary：
IP：192.168.94.198
SID：dgdb1
Hostname：dg1
DB_UNIQUE_NAME：dgdb1
Database：10.2.0.1(64)

Standby：
IP：192.168.94.199
SID：dgdb1
Hostname：dg2
DB_UNIQUE_NAME：dgdb1_s
Database：10.2.0.1(64)

--實施DG前的準備工作
--開啟資料庫logging及資料庫archivelog
--開啟資料庫logging
SQL> alter database force logging;
--檢查資料庫是否開啟archivelog
SQL> archive log list;
--如果資料庫未開啟archivelog，則要開啟archivelog
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database archivelog;
--檢查資料庫是否開啟archivelog
SQL> archive log list;

--建立相應目錄（根據具體情況，primary和standby端要一致）
--Standby：
mkdir –p /export/home/oracle/product/10.2.0/oradata/dgdb1
mkdir –p /export/home/oracle/product/10.2.0/admin/dgdb1/adump
mkdir –p /export/home/oracle/product/10.2.0/admin/dgdb1/bdump
mkdir –p /export/home/oracle/product/10.2.0/admin/dgdb1/cdump
mkdir –p /export/home/oracle/product/10.2.0/admin/dgdb1/udump
mkdir –p /export/home/oracle/product/10.2.0/admin/dgdb1/dpdump
mkdir –p /export/home/oracle/product/10.2.0/admin/dgdb1/pfile
mkdir –p /export/home/oracle/archive
mkdir -p /export/home/oracle/bak
--primary：
mkdir –p /export/home/oracle/archive
mkdir -p /export/home/oracle/bak

--修改或新增listener.ora 和tnsnames.ora（或者用GUI工具配置）
--注意：listener.ora 中新增的部分在括號內而非括號外
--primary端：
--listener.ora：
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /export/home/oracle/product/10.2.0)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = dgdb1)
(ORACLE_HOME = /export/home/oracle/product/10.2.0)
(SID_NAME = dgdb1)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dg1)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)

--tnsnames.ora
dgdb1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.94.198)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dgdb1)
)
)

dgdb1_s =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.94.199)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dgdb1)
)
)

--standby端：
--listener.ora:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /export/home/oracle/product/10.2.0)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = dgdb1)
(ORACLE_HOME = /export/home/oracle/product/10.2.0)
(SID_NAME = dgdb1)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dg2)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
--tnsnames.ora
dgdb1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.94.198)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dgdb1)
)
)
dgdb1_s =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.94.199)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dgdb1)
)
)

--具體實施DG的步驟：
--在primary上生成pfile，並修改新增相應引數，生成standby需要的pfile
--primary端：
oracle$>sqlplus / as sysdba
SQL> CREATE PFILE='/export/home/oracle/standby.ora' FROM SPFILE;
--編輯生成的pfile檔案('/export/home/oracle/standby.ora')，新增如下引數：
*.db_unique_name='dgdb_s'
*.fal_server='dgdb1'
*.fal_client='dgdb_s'
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(dgdb1,dgdb_s)'
*.log_archive_dest_1='LOCATION=/export/home/oracle/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=dgdb_s'
*.LOG_ARCHIVE_DEST_2='SERVICE=dgdb1 LGWR ASYNC=40960 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dgdb1'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.standby_archive_dest='/export/home/oracle/archive'
*.standby_file_management='AUTO'
--修改完後複製到standby端
Oracle$Scp /export/home/oracle/standby.ora oracle@192.168.94.199:/export/home/oracle/
--在standby端使用pfile啟動例項
--在primary端用命令建立資料庫密碼檔案，並接複製至standby端相同路徑下（如已存在，可直接複製）
--primary端手工建立資料庫密碼檔案
Oracle$orapwd file=... password=...
--primary端複製資料庫密碼檔案至standby端
Oracle$Scp /export/home/oracle/product/10.2.0/database/PWDdgdb1.ora oracle@192.168.94.199:/export/home/oracle/product/10.2.0/database
--standby端：
Oracle$set oracle_sid=dgdb1
Oracle$sqlplus / as sysdba
SQL> startup nomount pfile=’/export/home/oracle/standby.ora’
SQL> CREATE SPFILE FROM PFILE='/export/home/oracle/standby.ora';
--在primary端修改相應引數
--primary端：
Oracle$sqlplus / as sysdba
SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT = AUTO scope=both;
SQL> ALTER SYSTEM SET fal_server='dgdb1_s' scope=both;
SQL> ALTER SYSTEM SET fal_client='dgdb1' scope=both;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(dgdb1_s,dgdb1)' scope=both;
SQL> ALTER SYSTEM SET log_archive_dest_1='LOCATION=/export/home/oracle/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=dgdb1' scope=both;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=dgdb1_s LGWR ASYNC=40960 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dgdb1_s' scope=both;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1='ENABLE' scope=both;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2='ENABLE' scope=both;
SQL> ALTER SYSTEM SET standby_archive_dest='/export/home/oracle/archive' scope=both;

--在primary端開始使用rman備份資料庫
--primary端：
Oracle$rman target /
RMAN>backup full format='/u01/app/oracle/bak/ora10g_%d_%T_%s' database include current controlfile for standby plus archivelog format='/u01/app/oracle/bak/arch_%d_%T_%s';

--備份完畢後，把相應備份檔案複製到叢庫的相應目錄（目錄必須一致）
Oracle$Scp /export/home/oracle/bak/* oracle@192.168.94.199:/export/home/oracle/bak/

--使用duplicate 還原standby資料庫
--primary端：
Oracle$rman target / auxiliary sys/system@dgdb1_s
RMAN> duplicate target database for standby nofilenamecheck dorecover;

--收尾工作及開啟dg
--standby端建立standby log（至少比redo多一組）：
Oracle$sqlplus / as sysdba
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('/export/home/oracle/product/10.2.0/oradata/dgdb1/redo04.log') size 50M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('/export/home/oracle/product/10.2.0/oradata/dgdb1/redo05.log') size 50M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('/export/home/oracle/product/10.2.0/oradata/dgdb1/redo06.log') size 50M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ('/export/home/oracle/product/10.2.0/oradata/dgdb1/redo07.log') size 50M;
--開啟服務（standby端）：
SQL> alter database recover managed standby database disconnect from session;

--測試服務是否正常
--standby端（看歸檔日誌號）：
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
--primary端（強制一個日誌切換）：
SQL> alter system switch logfile;
--standby端（看歸檔日誌號）：
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
--standby端（看應用歸檔日誌的號）：
SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

--實現primary、standby的切換：
--primary端：
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
--primary端（上一步的結果必須是”TO STANDBY”才可以）
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;
--OR（上一步結果為”SESSIONS ACTIVE”,且解決不掉）
SQL>ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;
--Primary端：
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
--standby端：
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
--standby端（上一步的結果必須是”TO STANDBY”才可以）：
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
--OR（上一步結果為”SESSIONS ACTIVE”，且解決不掉）
SQL>ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
--開啟新的primary：
SQL>ALTER DATABASE OPEN;
--OR（最後一次啟動後以read only模式開啟過）
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;

--在新standby上重新啟動log apply services（如果必要）：
SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;
--OR（後臺模式）
SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
--OR（實時應用redo）
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE;
--新primary端（開始傳送redo 資料到新standby端）：
SQL> ALTER SYSTEM SWITCH LOGFILE;

Oracle10g物理DG詳細配置方法及步驟

相關文章