Oracle Data Guard Overview

[Former Member]

Oracle records database changes in its redo logs.

When Data Guard is activated, these changes are shipped from the primary to the standby and applied there.

This keeps the two databases synchronized with a time lag dependent on how often the changes are shipped and how long it takes to ship them.

ODG Standby Database Types

Physical Standby

• Identical to primary
• Updated via redo apply

Logical Standby

• Can be different structure to primary
• Updated via sql statements               

ODG – Available Modes

Mode

     =Maximum protection (LGWR, SYNC, AFFIRM)

               highest level of protection

               no data loss

               primary stops if redo data cannot be applied to the standby

     =Maximum availability (LGWR, SYNC, AFFIRM)

               high level of protection

               minimal data loss

               switches to maximum performance mode if standby unavailable

               automated catch-up of gaps if redo was not able to be written

     =Maximum performance (LGWR|ARCH, SYNC|ASYNC, AFFIRM|NOAFFIRM)

              medium level of protection

              some data loss

              primary not affected by standby unavailability

General objectives of DR system :

• Reduce recovery time in case of a disaster

                    Recovery Point Objective (RPO) = no more than X minutes of data loss

                    Recovery Time Objective (RTO) = Y hours from declaration of disaster

                    where X and Y are chosen by Project Build Team

• Reduce dependence on tapes

                    With tape based DR, tapes need to be shipped to DR site. Tapes can be lost or damaged with problems identified too late. ODG will know when a   redo log is missing or damaged at the point when it is to be applied to the standby not when the disaster has already occurred.

ODG Implementation Overview :

• Build Standby DB
• Setup network connection between primary and DR
• Refresh Standby with data from Primary DB (ship tapes or rsync over network)
• Synchronize databases (primary shutdown & apply redo logs or final rsync with primary in backup mode)
• Activate Oracle Data Guard
• Monitor (alert logs, network, primary db performance)
• Initial test - bring up standby to validate system

Configure required parameters on Primary and Secondary DB in init<SID>.ora or spfile as per Oracle online documentation - Data Guard

Concepts and Administration.

ODG Activation Overview

Once all ODG parameters are set, on the PRIMARY DB:

sqlplus / as sysdba

SQL> alter system set log_archive_dest_state_2=‘ENABLE';

SQL> show parameter log_archive_dest_state_2

Deactivate via:

sqlplus / as sysdba

SQL> alter system set log_archive_dest_state_2=‘DEFER';

SQL> show parameter log_archive_dest_state_2

ODG Troubleshooting

• Manual Monitoring options

          Database alert logs

          ST04N

• Automated Monitoring

          Alerts from OS level

          Solution Manger

          Oracle tools

Database alert logs

Oracle Alert Logs can be found in $SAPDATA_HOME/saptrace/background on both Primary database and Standby database.View the logs to check for messages / errors:

• more alert_<SID>.log | grep <ORA- / error / archival / GAP>
• tail –f alert_<SID>.log
• less alert_<SID>.log

Standby Database Backups

• Place the DB in read only mode
• Take a file system backup
• Place the DB back into managed recovery mode
• Clean up old redo logs after 2 successful backups

Disaster Recovery: High Level Overview

ODG Support: Common Problems

The following problems can occur :

• Missing logs
• Logs not being applied
• Cannot allocate new log
• Redo log storage filesystem full / filling

Problem: logs missing / not received

Problem: Redo logs not being applied

Problem: Can not allocate log

Problem: Redo storage full / filling