配置Oracle physical DataGuard

Primary SID:orcl   ORACLE_HOME= D:\oracle\product\10.2.0\oradata\orcl
Standby SID:standby  ORACLE_HOME= D:\oracle\product\10.2.0\oradata\standby
1. 準備primary database
2. 備份primary database
3. 拷貝相關primary database檔案到standby system
4. 設定physical standby的引數檔案
5. 啟動standby
6. 配置listener和tnsnames
7. 配置primary system的引數檔案
8. 傳輸歸檔日誌檔案

1. 準備primary database
1) 設定database為force logging
   SQL>alter database force logging;
2) 建立口令檔案
C:\>orapwd file=d:\PWDorcl.ora password=sys
3) 開啟歸檔模式
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;
2. 備份primary database
1) 冷備份
SQL>shutdown immediate;
拷貝D:\oracle\product\10.2.0\oradata\orcl到D:\oracle\product\10.2.0\oradata\standby
2) RMAN備份（採用壓縮方式）
RMAN> backup as compressed backupset database;
3) 確認備份完成後，建立standby controlfile
SQL> alter database create standby controlfile as ‘d:\standby.ctl’;
3. 拷貝相關primary database檔案到standby system
1) 拷貝備份檔案至相應位置
2) 拷貝口令檔案到standby system相應位置
Windows:%ORACLE_HOME%\database\
Unix&linux:$ORACLE_HOME\dbs\
(測試時primary和standby在同一主機，口令檔案不用複製)
3) 拷貝standby.ctl到D:\oracle\product\10.2.0\oradata\standby\
4. 設定physical standby的引數檔案
1) Primary上建立pfile檔案
SQL>create pfile=’d:\init.ora’ from spfile;
2) 編輯init.ora，增加如下項：
DB_NAME=orcl
DB_UNIQUE_NAME=standby
LOG_ARCHIVE_CONFIG=’DG_CONFIG=(orcl,standby)’
CONTROL_FILES=’D:\oracle\product\10.2.0\oradata\standby\standby.ctl’
LOG_ARCHIVE_DEST_1=’location= D:\oracle\product\10.2.0\oradata\standby\arch’
LOG_ARCHIVE_DEST_2=’service=standby optional’
DB_FILE_NAME_CONVERT=‘D:\oracle\product\10.2.0\oradata\orcl’,’D:\oracle\product\10.2.0\oradata\standby’
LOG_FILE_NAME_CONVERT=’D:\oracle\product\10.2.0\oradata\orcl’,’D:\oracle\product\10.2.0\oradata\standby’
STANDBY_FILE_MANAGEMEN=’auto’
AUDIT_FILE_DEST=’D:\oracle\product\10.2.0\admin\standby\adump’
BACKGROUND_DUMP_DEST=’D:\oracle\product\10.2.0\admin\standby\bdump’
CORE_DUMP_DEST=’D:\oracle\product\10.2.0\admin\standby\cdump’
USER_DUMP_DEST=’D:\oracle\product\10.2.0\admin\standby\udump’
5. 啟動standby
1) Windows平臺下，使用oradim建立standby
C:\>oradim –NEW –SID standby
2) 使用init.ora引數檔案啟動standby
C:\>set oracle_sid=standby
C:\>sqlplus / as sysdba
SQL>startup nomount pfile=d:\init.ora
SQL>alter database mount standby database;
啟動過程如果報ORA-00205錯，請檢查controlfile的放置路徑是否與init.ora中指定的一致。
6. 配置listener和tnsnames
1) 配置listener
primary和standby在同一主機，可使用同一個監聽，增加黑體部分即可：
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orcl)
      (ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
      (SID_NAME = orcl)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = standby)
      (ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
      (SID_NAME = standby)
    ) 
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
    )
  )
2) 配置tnsnames
增加standby黑體部分,ORCL為原有指向primary部分
standby =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = standby)
    )
  )

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )
7. 配置primary system的引數檔案
1) 建立primary的pfile檔案
 SQL>create pfile=’d:\initorcl.ora’ from spfile;
2) 編輯initorcl.ora檔案，增加如下內容：
DB_NAME=orcl
DB_UNIQUE_NAME=orcl
LOG_ARCHIVE_CONFIG=’DG_CONFIG=(orcl,standby)’
LOG_ARCHIVE_DEST_1=’location= D:\oracle\product\10.2.0\oradata\orcl\arch’
LOG_ARCHIVE_DEST_2=’service=standby optional’
STANDBY_FILE_MANAGEMEN=’auto’
8. 傳輸歸檔日誌檔案
1) 啟動primary至open狀態
SQL>startup
SQL>alter system switch logfile;
檢視log_archive_dest_1與log_archive_dest_2的狀態
SQL>select dest_id,status,error from v$arhive_dest;
如status不正常，error會列出問題所在

檢視歸檔日誌是否傳輸到standby指定的LOG_ARCHIVE_DEST_1目錄下，如沒有，且在alert日誌中出現“ORA-01031: 許可權不足”，則說明口令檔案存在問題，檢查口令檔案路徑及命名是否正確。
口令檔案預設路徑及命名：
Windows:%ORACLE_HOME%\database\PWDsid.ora (PWDorcl.ora)
Unix&linux:$ORACLE_HOME\dbs\orapwSID (orapwORCL)

手動傳輸歸檔日誌並應用：
例如傳輸primary的ARC00046_0683553865.001歸檔檔案到standby的d:\下，
SQL> alter database register physical logfile 'd:\ ARC00046_0683553865.001';
SQL> select sequence#,applied from v$archived_log;
 SEQUENCE# APP
---------- ---
        46 NO
SQL> alter database recover managed standby database disconnect from session;
資料庫已更改。
SQL> select sequence#,applied from v$archived_log;
 SEQUENCE# APP
---------- ---
        46 YES
至此，歸檔46已應用到了standby中。

2) 啟動standby至mount狀態
SQL>startup nomount pfile=d:\init.ora
SQL>alter database mount standby database;
3) 應用歸檔日誌
如歸檔日誌已傳至standby相應目錄下：
在後臺應用歸檔日誌
SQL>alter database recover managed standby database disconnect from session;
在前臺應用歸檔日誌
SQL>alter database recover managed standby database;
停止應用歸檔
SQL>alter database recover managed standby database cancel;
4) Read only open database
停止應用歸檔後：
SQL>alter database open read only;
此時即可檢查primary與standby的資料一致性。