Data Guard

Data Guard

Oracle Data Guard ensures high availability, data protection, and disaster recovery for enterprise data. A Data Guard configuration consists of one production database and one or more standby databases. The databases in a Data Guard configuration are connected by Oracle Net and may be dispersed geographically.

Physical standby database: Provides a physically identical copy of the primary database, with on disk database structures that are identical to the primary database on a block-for-block basis. Redo Apply.

Logical standby database: Same logical information as the production database, physical organization and structure of the data can be different, SQL Apply redo received from the primary database into SQL statements and then executing the SQL statements on the standby database.

Data Guard Protection Modes

Maximum protection:  Must be written to both the local online redo log and to the standby redo log on at least one standby database before the transaction commits or else primary shutdown

Maximum availability: Transaction will not commit until it written to the one standby it operates in maximum protection, the primary database does not shut down but primary database operates in maximum performance mode until the fault is corrected.

Maximum performance: Highest level of data protection, the primary database's redo data stream is also written to at least one standby database, but that redo stream is written asynchronously with respect to the transactions that create the redo data.

 DataGuard Configuration

Enable Forced Logging

Even in the event that a 'no logging' operation is done, force logging takes precedence and all operations are logged into the redo logs.

SQL> ALTER DATABASE FORCE LOGGING;

Configure a Standby Redo Log

Ensure log file sizes are identical on the primary and standby databases.

Create the Password file.

cd $ORACLE_HOME/dbs

[oracle] > orapwd file=orapwsainath password=oracle force=y

Configure a Standby Redo Log

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 SIZE 50M;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 SIZE 50M;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 SIZE 50M;

Database altered.

Verifying the standby redo logs

SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;

If archiving is not enabled, Enable Archiving

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP MOUNT;

SQL> ALTER DATABASE ARCHIVELOG;

SQL> ALTER DATABASE OPEN;

DB_NAME=hyderabad

DB_UNIQUE_NAME=hyderabad

LOG_ARCHIVE_CONFIG='DG_CONFIG=(hyderabad,bangalore)'

CONTROL_FILES='/arch1/hyderabad/control1.ctl', '/arch2/hyderabad/control2.ctl'

LOG_ARCHIVE_DEST_1=

 'LOCATION=/arch1/hyderabad/

  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)

  DB_UNIQUE_NAME=hyderabad'

LOG_ARCHIVE_DEST_2=

 'SERVICE=bangalore LGWR ASYNC

  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)

  DB_UNIQUE_NAME=bangalore'

LOG_ARCHIVE_DEST_STATE_1=ENABLE

LOG_ARCHIVE_DEST_STATE_2=ENABLE

REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

LOG_ARCHIVE_FORMAT=%t_%s_%r.arc

LOG_ARCHIVE_MAX_PROCESSES=30

DB_UNIQUE_NAME

Unique name for each database even if the primary and standby databases reverse roles.

LOG_ARCHIVE_CONFIG

Specify the DG_CONFIG attribute on this parameter to list the DB_UNIQUE_NAME of the primary and standby databases in the Data Guard configuration

LOG_ARCHIVE_DEST_n

LOG_ARCHIVE_DEST_2 is valid only for the primary role. This destination transmits redo data to the remote physical standby destination bangalore.

LOG_ARCHIVE_DEST_STATE_n

Specify ENABLE to allow redo transport services to transmit redo data to the specified destination.

REMOTE_LOGIN_PASSWORDFILE

Set the same password for hyderabad both the primary and standby databases. The recommended setting is either EXCLUSIVE or SHARED.

FAL_SERVER

When the Chicago database is running in the standby role, it uses the Boston database as the FAL server from which to fetch (request) missing archived redo log files if Boston is unable to automatically send the missing log files.

FAL_CLIENT

Specify the Oracle Net service name of the Chicago database. The FAL server (Boston) copies missing archived redo log files to the Chicago standby database.

DB_FILE_NAME_CONVERT

This parameter converts the path names of the primary database datafiles to the standby datafile path names

LOG_FILE_NAME_CONVERT

This parameter converts the path names of the primary database log files to the path names on the standby database.

STANDBY_FILE_MANAGEMENT

Set to AUTO so when datafiles are added to or dropped from the primary database, corresponding changes are made automatically to the standby database.