環境設定

プライマリDB: 192.168.21.142
スタンバイDB: 192.168.21.141
両DBともインスタンス名: adg, DB名: adg

1. ネットワーク設定

両サーバの/etc/hostsに以下を追加:

192.168.2.31 primary
192.168.2.101 standby

2. リスナー設定

プライマリDBのlistener.ora:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = adg)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/)
      (SID_NAME = adg)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = primary)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

スタンバイDBのlistener.ora:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = std)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/)
      (SID_NAME = adg)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = standby)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

3. パスワードファイルの設定

orapwd file=/u01/app/oracle/product/11.2.0/dbs/orapwadg password=syspass entries=10
scp /u01/app/oracle/product/11.2.0/dbs/orapwadg standby:/u01/app/oracle/product/11.2.0/dbs/

データベース設定

1. 強制ロギングの有効化

SQL> ALTER DATABASE FORCE LOGGING;

2. アーカイブログモードの設定

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;
SQL> ALTER SYSTEM SET log_archive_dest_1='location=/u01/app/arch/dg';

3. スタンバイREDOログの追加

SQL> ALTER DATABASE ADD STANDBY LOGFILE 
     GROUP 11 ('/u01/app/oracle/oradata/dg/redo11.log') SIZE 50M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE 
     GROUP 12 ('/u01/app/oracle/oradata/dg/redo12.log') SIZE 50M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE 
     GROUP 13 ('/u01/app/oracle/oradata/dg/redo13.log') SIZE 50M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE 
     GROUP 14 ('/u01/app/oracle/oradata/dg/redo14.log') SIZE 50M;

4. スタンバイ制御ファイルの作成

SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/standby.ctl';
scp /tmp/standby.ctl standby:/u01/app/oracle/oradata/dg/control01.ctl

パラメータファイル設定

1. プライマリDBのパラメータ追加

*.db_unique_name='pri'
*.log_archive_config='DG_CONFIG=(pri,std)'
*.log_archive_dest_1='LOCATION=/u01/app/arch/dg VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=pri'
*.log_archive_dest_2='SERVICE=std LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=std'
*.fal_server='std'
*.fal_client='pri'
*.standby_file_management='AUTO'

2. スタンバイDBのパラメータ追加

*.db_unique_name='std'
*.log_archive_config='DG_CONFIG=(pri,std)'
*.log_archive_dest_1='LOCATION=/u01/app/arch/dg VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=std'
*.log_archive_dest_2='SERVICE=pri LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=pri'
*.fal_server='pri'
*.fal_client='std'
*.standby_file_management='AUTO'

スタンバイDBの起動

SQL> STARTUP NOMOUNT PFILE='/home/oracle/spfile/initdg.ora';
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

同期状態の確認

SQL> SELECT open_mode,database_role FROM v$database;
SQL> SELECT process,status,sequence# FROM v$managed_standby;
SQL> SELECT thread#,sequence#,applied FROM v$archived_log;