Oracle 10g物理DG详细配置方法及步骤

--测试环境：
    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  [email protected]:/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 [email protected]:/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;