1.Data Guard Operational Prerequisites
-------------------------------------------------
1.1 Oracle Data Guard is available only as a feature of Oracle Database Enterprise Edition.
1.2 COMPATIBLE initialization parameter must be set to the same value between primary and standby.
1.3 The primary database must run in ARCHIVELOG mode.
1.4 turn on FORCE LOGGING at the primary database before performing datafile backups for standby creation.
-------------------------------------------------
2.Preparing the Primary Database for Standby Database Creation.
-----------------------------------------------------------------
2.1 Enable Forced Logging
SQL> ALTER DATABASE FORCE LOGGING;
2.2 These redo transport sessions are authenticated usinga remote login password file.
NAME
TYPE
VALUE
------------------------------------ -------------------------------- ------------------------------
remote_login_passwordfile
string
EXCLUSIVE/SHARED
2.2.1 set remote_login_passwordfile to EXCLUSIVE/SHARED.
2.2.2 copy the password file to the standby server.
2.3 Configure the Primary Database to Receive Redo Data
2.3.1 Creating and Managing a Standby Redo Log.
for non-rac:
SQL> ALTER DATABASE ADD STANDBY LOGFILE ('/oracle/dbs/slog1.rdo') SIZE 500M;
for rac:
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 SIZE 500M;
2.3.2 Configuring Standby Redo Log Archival.
2.3.2.1 Enable Archiving
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;
2.3.2.2 Standby Redo Log Archival to a fast recovery area
LOG_ARCHIVE_DEST_2 = 'LOCATION = /disk2/archive VALID_FOR=(STANDBY_LOGFILE,STANDBY_ROLE)'
LOG_ARCHIVE_DEST_STATE_2=ENABLE
2.4 Set Primary Database Initialization Parameters
2.4.1 Primary Database:
CONTROL_FILES='/arch1/chicago/control1.ctl', '/arch2/chicago/control2.ctl'
DB_NAME=chicago
DB_UNIQUE_NAME=chicago
LOG_ARCHIVE_CONFIG='DG_CONFIG=(chicago,boston)'
LOG_ARCHIVE_DEST_1='LOCATION=/arch1/chicago/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=chicago'
log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST valid_for=(ALL_LOGFILES,ALL_ROLES)' --for db_recovery_file_dest
LOG_ARCHIVE_DEST_2='SERVICE=boston LGWR SYNC AFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name=boston net_timeout=30, valid_for=(all_logfiles,primary_role)'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
FAL_SERVER=boston
DB_FILE_NAME_CONVERT='boston','chicago'
LOG_FILE_NAME_CONVERT=
'/arch1/chicago/','/arch1/boston/','/arch2/chicago/','/arch2/boston/'
STANDBY_FILE_MANAGEMENT=AUTO
dg_broker_start=true
dg_broker_config_file1=?
dg_broker_config_file2=?
---------------------------------------------------------------------------------
3.Creating a Physical Standby Database
---------------------------------------------------------------------------------
3.1 Create a Control File for the Standby Database on primary.
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/boston.ctl';
SQL> ALTER DATABASE OPEN;
3.2 Create a Parameter File for the Standby Database on primary.
SQL> CREATE PFILE='/tmp/initboston.ora' FROM SPFILE;
CONTROL_FILES='/arch1/chicago/control1.ctl', '/arch2/chicago/control2.ctl'
DB_NAME=chicago
DB_UNIQUE_NAME=boston
LOG_ARCHIVE_CONFIG='DG_CONFIG=(chicago,boston)'
LOG_ARCHIVE_DEST_1='LOCATION=/arch1/boston/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=boston'
log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST valid_for=(ALL_LOGFILES,ALL_ROLES)' --for db_recovery_file_dest
LOG_ARCHIVE_DEST_2='SERVICE=chicago LGWR SYNC AFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name=chicago net_timeout=30, valid_for=(all_logfiles,primary_role)'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
FAL_SERVER=boston
DB_FILE_NAME_CONVERT='boston','chicago'
LOG_FILE_NAME_CONVERT=
'/arch1/boston/','/arch1/chicago/','/arch2/boston/','/arch2/boston/'
STANDBY_FILE_MANAGEMENT=AUTO
dg_broker_start=true
dg_broker_config_file1=?
dg_broker_config_file2=?
-----------------------------------------------------------------------------------
4.Copy Files from the Primary System to the Standby System
-----------------------------------------------------------------------------------
5.Set Up the Environment to Support the Standby Database.
-----------------------------------------------------------------------------------
5.1 set up listener.
on primary:
LISTENER =
(ADDRESS_LIST =
(ADDRESS= (PROTOCOL= TCP)(Host= xxxxxxxxxxxxxx)(Port= 1521))
(ADDRESS= (PROTOCOL= IPC)(KEY= chicago))
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = chicago)
(SID_NAME = chicago)
(ORACLE_HOME = /data/oracle/chicago/product/11.2.0.3)
)
(SID_DESC =
(GLOBAL_DBNAME = chicago_DGMGRL)
(SID_NAME = chicago)
(ORACLE_HOME = /data/oracle/chicago/product/11.2.0.3)
)
)
on standby:
LISTENER =
(ADDRESS_LIST =
(ADDRESS= (PROTOCOL= TCP)(Host= xxxxxxxxxxxxxx)(Port= 1521))
(ADDRESS= (PROTOCOL= IPC)(KEY= chicago))
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = boston)
(SID_NAME = chicago)
(ORACLE_HOME = /data/oracle/boston/product/11.2.0.3)
)
(SID_DESC =
(GLOBAL_DBNAME = boston_DGMGRL)
(SID_NAME = chicago)
(ORACLE_HOME = /data/oracle/boston/product/11.2.0.3)
)
)
5.2 set up tnsnames.ora
chicago =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = xxxxxxxxxxxxxx)(PORT = 1722))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = chicago)
)
)
-----------------------------------------------------------------------------
6.Start Redo Apply.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
7.Set the data protection mode.
SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE {AVAILABILITY | PERFORMANCE | PROTECTION};
SQL> SELECT PROTECTION_MODE FROM V$DATABASE; |
