Top 42 Oracle Data Guard Interview Questions You Must Prepare 19.Mar.2024

A snapshot standby database is a fully updatable standby database that is created by converting a physical standby database into a snapshot standby database.

Like a physical or logical standby database, a snapshot standby database receives and archives redo data from a primary database. Unlike a physical or logical standby database, a snapshot standby database does not apply the redo data that it receives. The redo data received by a snapshot standby database is not applied until the snapshot standby is converted back into a physical standby database, after first discarding any local updates made to the snapshot standby database.

Here is some data guard category and there enhancement

  1. Data Protection Advanced Compression Lost-write protection Fast-Start Failover
  2. Increase ROI Active Data Guard Snapshot Standby
  3. High Availability Faster Redo Apply Faster failover & switchover Automatic Failover using ASYNC
  4. Manageability Mixed Windows/Linux

FAL_SERVER

specifies the FAL (fetch archive log) server for a standby database. The value is an Oracle Net service name, which is assumed to be configured properly on the standby database system to point to the desired FAL server.

FAL_CLIENT

specifies the FAL (fetch archive log) client name that is used by the FAL service, configured through the

FAL_SERVER initialization parameter, to refer to the FAL client.

The value is an Oracle Net service name, which is assumed to be configured properly on the FAL server system to point to the FAL client (standby database).

  1. Take a full hot backup of Primary database
  2. Create standby control file
  3. Trfer full backup, init.ora, standby control file to standby node.
  4. Modify init.ora file on standby node.
  5. Restore database
  6. Recover Standby database(Alternatively, RMAN DUPLICATE DATABASE FOR STANDBY DO RECOVERY can be also used)
  7. Setup FAL_CLIENT and FAL_SERVER parameters on both sides
  8. Put Standby database in Managed Recover mode

select round((sysdate – a.NEXT_TIME)*24*60) as “Backlog”,m.SEQUENCE#-1 “Seq Applied”,m.process, m.status

from v$archived_log a, (select process,SEQUENCE#, status from v$managed_standby where process like ‘%MRP%’)m where a.SEQUENCE#=(m.SEQUENCE#-1);

DB_FILE_NAME_CONVERT parameter is used in Oracle Data Guard setup that to in standby databases. DB_FILE_NAME_CONVERT parameter are used to update the location of data files in standby database. These parameter are used when you are using different directory structure in standby database compare to primary database data files location.

Switchover – This is done when both primary and standby databases are available. It is pre-planned.

Failover – This is done when the primary database is NO longer available (ie in a Disaster). It is not pre-planned.

Till Oracle 10g, 9 standby databases are supported.

From Oracle 11g R2, we can create 30 standby databases..

11g Snapshot Standby Database

Oracle 11g introduces the Snapshot Standby database which essentially is an updateable standby database which has been created from a physical standby database.

We can convert a physical standby database to a snapshot standby database, do some kind of testing on a database which is a read write copy of the current primary or production database and then finally revert it to  it’s earlier state as a physical standby database.

While the snapshot standby database is open in read-write mode, redo is being received from the primary database, but is not applied.

After converting it back to a physical standby database, it is resynchronized with the primary by applying the accumalated redo data which was earlier shipped from the primary database but not applied.

Using a snapshot standby, we are able to do real time application testing using near real time production data. Very often we are required to do production clones for the purpose of testing. But using snapshot standby databases we can meet the same requirement sparing the effort,time,resources and disk space.

LOG_FILE_NAME_CONVERT parameter is used in Oracle Data Guard setup that to in standby databases. LOG_FILE_NAME_CONVERT parameter are used to update the location of redo log files in standby database. These parameter are used when you are using different directory structure in standby database compare to primary database redo log file location.

Step for Physical Standby

These are the steps to follow:

  • Enable forced logging
  • Create a password file
  • Configure a standby redo log
  • Enable archiving
  • Set up the primary database initialization parameters
  • Configure the listener and tnsnames to support the database on both nodes
  • col name format a20
  • col thread# format 999
  • col sequence# format 999
  • col first_change# format 999999
  • col next_change# format 999999

SELECT thread#, sequence# AS “SEQ#”, name, first_change# AS “FIRSTSCN”,

next_change# AS “NEXTSCN”,archived, deleted,completion_time AS “TIME”

FROM v$archived_log

V$ log_history

Oracle Data Guard classified in to two types based on way of creation and method used for Redo Apply.

They are as follows:

  • Physical standby (Redo Apply technology)
  • Logical standby (SQL Apply Technology)

11g Active Data Guard

Oracle Active Data Guard enables read-only access to a physical standby database for queries, sorting, reporting, web-based access, etc., while continuously applying changes received from the production database.

Oracle Active Data Guard also enables the use of fast incremental backups when offloading backups to a standby database, and can provide additional benefits of high availability and disaster protection against planned or unplanned outages at the production site.

Set Primary Database Initialization Parameters

———————————————-

On the primary database, you define initialization parameters that control redo trport services while the database is in the primary role. There are additional parameters you need to add that control the receipt of the redo data and log apply services when the primary database is tritioned to the standby role.

 DB_NAME=chicago

DB_UNIQUE_NAME=chicago

LOG_ARCHIVE_CONFIG=’DG_CONFIG=(chicago,boston)’

CONTROL_FILES=’/arch1/chicago/control1.ctl’, ‘/arch2/chicago/control2.ctl’

LOG_ARCHIVE_DEST_1=

‘LOCATION=/arch1/chicago/

VALID_FOR=(ALL_LOGFILES,ALL_ROLES)

DB_UNIQUE_NAME=chicago’

LOG_ARCHIVE_DEST_2=

‘SERVICE=boston LGWR ASYNC

VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)

DB_UNIQUE_NAME=boston’

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

Primary Database: Standby Role Initialization Parameters

FAL_SERVER=boston

FAL_CLIENT=chicago

DB_FILE_NAME_CONVERT=’boston’,’chicago’

LOG_FILE_NAME_CONVERT=  ‘/arch1/boston/’,’/arch1/chicago/’,’/arch2/boston/’,’/arch2/chicago/’

STANDBY_FILE_MANAGEMENT=AUTO

Prepare an Initialization Parameter File for the Standby Database

—————————————————————–

Create a text initialization parameter file (PFILE) from the server parameter file (SPFILE) used by the primary database; a text initialization parameter file can be copied to the standby location and modified. For example:

CREATE PFILE=’/tmp/initboston.ora’ FROM SPFILE;

Modifying Initialization Parameters for a Physical Standby Database.

DB_NAME=chicago

DB_UNIQUE_NAME=boston

LOG_ARCHIVE_CONFIG=’DG_CONFIG=(chicago,boston)’

CONTROL_FILES=’/arch1/boston/control1.ctl’, ‘/arch2/boston/control2.ctl’

DB_FILE_NAME_CONVERT=’chicago’,’boston’

LOG_FILE_NAME_CONVERT= ‘/arch1/chicago/’,’/arch1/boston/’,’/arch2/chicago/’,’/arch2/boston/’

LOG_ARCHIVE_FORMAT=log%t_%s_%r.arc

LOG_ARCHIVE_DEST_1=  ‘LOCATION=/arch1/boston/

VALID_FOR=(ALL_LOGFILES,ALL_ROLES)

DB_UNIQUE_NAME=boston’

LOG_ARCHIVE_DEST_2=  ‘SERVICE=chicago LGWR ASYNC  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)   DB_UNIQUE_NAME=chicago’

LOG_ARCHIVE_DEST_STATE_1=ENABLE

LOG_ARCHIVE_DEST_STATE_2=ENABLE

REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

STANDBY_FILE_MANAGEMENT=AUTO

FAL_SERVER=chicago

FAL_CLIENT=boston

Oracle Standby database are divided into physical standby database or logical standby database based on standby database creation and redo log apply method. Physical standby database are created as exact copy i.e block by block copy of primary database. In physical standby database tractions happen in primary database are synchronized in standby database by using Redo Apply method by continuously applying redo data on standby database received from primary database.

Physical standby database can offload the backup activity and reporting activity from Primary database. Physical standby database can be opened for read-only tractions but redo apply won’t happen during that time. But from 11g onwards using Active Data Guard option (extra purchase) you can simultaneously open the physical standby database for read-only access and apply redo logs received from primary database.

Maximum performance:

This is the default protection mode.

It provides the highest level of data protection that is possible without affecting the performance of a primary database.

This is accomplished by allowing tractions to commit as soon as all redo data generated by those tractions has been written to the online log.

Maximum protection:

This protection mode ensures that no data loss will occur if the primary database fails.

To provide this level of protection, the redo data needed to recover a traction must be written to both the online redo log and to at least one standby database before the traction commits.

To ensure that data loss cannot occur, the primary database will shut down, rather than continue processing tractions.

Maximum availability:

This protection mode provides the highest level of data protection that is possible without compromising the availability of a primary database.

Tractions do not commit until all redo data needed to recover those tractions has been written to the online redo log and to at least one standby database.

Data Guard provides a comprehensive set of services that create, maintain, manage, and monitor one or more standby databases to enable production Oracle databases to survive disasters and data corruptions. Data Guard maintains these standby databases as copies of the production database. Data Guard can be used with traditional backup, restoration, and cluster techniques to provide a high level of data protection and data availability.

By using following query you can check protection mode of primary database in your Oracle Data Guard setup. SELECT PROTECTION_MODE FROM V$DATABASE;

For Example: SQL> select protection_mode from v$database;

PROTECTION_MODE

——————————–

MAXIMUM PERFORMANCE

Data Guard : Dataguard is mechanism/tool to maintain standby database. The dataguard is set up between primary and standby instance . Data Guard is only available on Enterprise Edition.

Standby Database : 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. Standby capability is available on Standard Edition.

Specifies that network I/O is to be done synchronously (SYNC) or asynchronously (ASYNC) when archival is performed using the log writer process (LGWR).

Specifies whether redo trport services use archiver processes (ARCn) or the log writer process (LGWR) to collect traction redo data and trmit it to standby destinations. If neither the ARCH or LGWR attributes are specified, the default is ARCH.

Controls whether redo trport services use synchronous or asynchronous I/O to write redo data to disk

AFFIRM—specifies that all disk I/O to archived redo log files and standby redo log files is performed synchronously and completes successfully before the log writer process continues.

NOAFFIRM—specifies that all disk I/O to archived redo log files and standby redo log files is performed asynchronously; the log writer process on the primary database does not wait until the disk I/O completes before continuing.

The services required on the primary database are:

  • Log Writer Process (LGWR) – Collects redo information and updates the online redo logs. It can also      create local archived redo logs and trmit online redo to standby databases.
  • Archiver Process (ARCn) – One or more archiver processes make copies of online redo logs either locally or remotely for standby databases.
  • Fetch Archive Log (FAL) Server – Services requests for archive redo logs from FAL clients running on multiple standby databases. Multiple FAL servers can be run on a primary database, one for each FAL request. .
  • The services required on the standby database are:
  • Fetch Archive Log (FAL) Client – Pulls archived redo log files from the primary site. Initiates trfer of archived redo logs when it detects a gap sequence.
  • Remote File Server (RFS) – Receives archived and/or standby redo logs from the primary database.
  • Archiver (ARCn) Processes – Archives the standby redo logs applied by the managed recovery process (MRP).
  • Managed Recovery Process (MRP) – Applies archive redo log information to the standby database

Physical standby – in mount state, MRP will apply archives

ADG – in READ ONLY state, MRP will apply archives

Logical standby – in READ ONLY state, LSP will run

Snapshot standby databases – Physical standby database can be converted to snapshot standby database, which will be in READ WRITE mode, can do any kind of testing, then we can convert back snapshot standby database to physical standby database and start MRP which will apply all pending archives.

Oracle Standby database are divided into physical standby database or logical standby database based on standby database creation and redo log apply method. Logical standby database can be created similar to Physical standby database and later you can alter the structure of logical standby database. Logical standby database uses SQL Apply method to synchronize logical standby database with primary database.

This SQL apply technology converts the received redo logs to SQL statements and continuously apply those SQL statements on logical standby database to make standby database consistent with primary database. Main advantage of Logical standby database compare to physical standby database is you can use Logical standby database for reporting purpose during SQL apply i.e Logical standby database must be open during SQL apply.

Even though Logical standby database are opened for read/write mode, tables which are in synchronize with primary database are available for read-only operations like reporting, select queries and adding index on those tables and creating materialized views on those tables. Though Logical standby database has advantage on Physical standby database it has some restriction on data-types, types of DDL, types of DML and types of tables.

Till Oralce 10g, create guaranteed restore point, open in read write, let him do updates, flashback to restore point, start MRP.

From Oracle 11g, convert physical standby to snapshot standby, let him do updates, convert to physical standby, start MRP.

11gr2 new database property, StaticConnectIdentifier, which allows the user to specify a static connect identifier that the DGMGRL client will use to start database instances.

A standby database automatically applies redo logs when they arrive from the primary database. But in some cases, we want to create a time lag between the archiving of a redo log at the primary site, and the application of the log at the standby site.

Modify the LOG_ARCHIVE_DEST_n initialization parameter on the primary database to set a delay for the standby database.

Example: For 60min Delay:

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2=’SERVICE=stdby_srvc DELAY=60′;

The DELAY attribute is expressed in minutes.

The archived redo logs are still automatically copied from the primary site to the standby site, but the logs are not immediately applied to the standby database. The logs are applied when the specified time interval expires.

Data Guard Configurations:

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. There are no restrictions on where the databases are located, provided they can communicate with each other.

 Dataguard Architecture

The Oracle 9i Data Guard architecture incorporates the following items:

  • Primary Database – A production database that is used to create standby databases. The archive logs from the primary database are trfered and applied to standby databases. Each standby can only be associated with a single primary database, but a single primary database can be associated with multiple standby databases.
  • Standby Database – A replica of the primary database.
  • Log Trport Services – Control the automatic trfer of archive redo log files from the primary database to one or more standby destinations.
  • Network Configuration – The primary database is connected to one or more standby databases using      Oracle Net.
  • Log Apply Services – Apply the archived redo logs to the standby database. The Managed Recovery      Process (MRP) actually does the work of maintaining and applying the archived redo logs.
  • Role Management Services – Control the changing of database roles from primary to standby. The services include switchover, switchback and failover.
  • Data Guard Broker – Controls the creation and monitoring of Data Guard. It comes with a GUI and command line interface.

Primary Database:

A Data Guard configuration contains one production database, also referred to as the primary database, that functions in the primary role. This is the database that is accessed by most of your applications.

Standby Database:

A standby database is a tractionally consistent copy of the primary database. Using a backup copy of the primary database, you can create up to nine standby databases and incorporate them in a Data Guard configuration. Once created, Data Guard automatically maintains each standby database by trmitting redo data from the primary database and then applying the redo to the standby database.

The types of standby databases are as follows:

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. The database schema, including indexes, are the same. A physical standby database is kept synchronized with the primary database, through Redo Apply, which recovers the redo data received from the primary database and applies the redo to the physical standby database.

Logical standby database:

Contains the same logical information as the production database, although the physical organization and structure of the data can be different. The logical standby database is kept synchronized with the primary database through SQL Apply, which trforms the data in the redo received from the primary database into SQL statements and then executes the SQL statements on the standby database.

  • Better usage of resource
  • Data Protection
  • High Availability
  • Disaster Recovery

Advantages of using Physical standby database in Oracle Data Guard are as follows:

  • High Availability.
  • Load balancing (Backup and Reporting).
  • Data Protection.
  • Disaster Recovery.

Following are the different Services available in Oracle Data Guard of Oracle database.

  • Redo Trport Services.
  • Log Apply Services.
  • Role Tritions.

Following are the different protection modes available in Data Guard of Oracle database you can use any one based on your application requirement.

  • Maximum Protection
  • Maximum Availability
  • Maximum Performance.

By using following query your can change the protection mode in your primary database after setting up required value in corresponding LOG_ARCHIVE_DEST_n parameter in primary database for corresponding standby database. ALTER DATABASE SET STANDBY DATABASE TO MAXIMUM [PROTECTION|PERFORMANCE|AVAILABILITY];

Example: alter database set standby database to MAXIMUM PROTECTION;

  • Log_Archive_Dest_n
  • Log_Archive_Dest_State_n
  • Log_Archive_Config
  • Log_File_Name_Convert
  • Standby_File_Managment
  • DB_File_Name_Convert
  • DB_Unique_Name
  • Control_Files
  • Fat_Client
  • Fat_Server

The LOG_ARCHIVE_CONFIG parameter enables or disables the sending of redo streams to the standby sites. The DB_UNIQUE_NAME of the primary database is dg1 and the DB_UNIQUE_NAME of the standby database is dg@The primary database is configured to ship redo log stream to the standby database. In this example, the standby database service is dg2.

Next, STANDBY_FILE_MANAGEMENT is set to AUTO so that when Oracle files are added or dropped from the primary database, these changes are made to the standby databases automatically. The STANDBY_FILE_MANAGEMENT is only applicable to the physical standby databases.

Setting the STANDBY_FILE_MANAGEMENT parameter to AUTO is is recommended when using Oracle Managed Files (OMF) on the primary database. Next, the primary database must be running in ARCHIVELOG mode.

DG Broker “it is the management and monitoring tool”.

Oracle dataguard broker is a distributed management framework that automates and centralizes the creation , maintenance and monitoring of DG configuration.

All management operations can be performed either through OEM, which uses the broker (or) broker specified command-line tool interface “DGMGRL”.

Specify the DG_CONFIG attribute to identify the DB_UNIQUE_NAME for the primary database and each standby database in the Data Guard configuration.

The default value of this parameter enables the primary database to send redo data to remote destinations and enables standby databases to receive redo data.

A standby redo log is required for the maximum protection and maximum availability modes and the LGWR ASYNC trport mode is recommended for all databases. Data Guard can recover and apply more redo data from a standby redo log than from archived redo log files alone.

If the real-time apply feature is enabled, log apply services can apply redo data as it is received, without waiting for the current standby redo log file to be archived.

This results in faster switchover and failover times because the standby redo log files have been applied already to the standby database by the time the failover or switchover begins.

Physical standby DB: As the name, it is physically (datafiles, schema, other physical identity) same copy of the primary database. It synchronized with the primary database with Apply Redo to the standby DB.

Logical Standby DB: As the name logical information is the same as the production database, it may be physical structure can be different.

It synchronized with primary database though SQL Apply, Redo received from the primary database into SQL statements and then executing these SQL statements on the standby DB.

We can open “physical stand by DB to “read only” and make it available to the applications users (Only select is allowed during this period). we can not apply redo logs received from primary database at this time.

We do not see such issues with logical standby database. We can open the database in normal mode and make it available to the users. At the same time, we can apply archived logs received from primary database.

For OLTP large traction database it is better to choose logical standby database.

real-time apply where before log shipping the LGWR process writes to a standbylog file simultaneously along with the online redolog file.

This standby logfile is written to standby log file on standby server. There is no loss of any committed traction whatsoever in Real-Time Apply scenario.

•In Real Time Apply, once a traction is committed on the Primary, the committed changes will be available on the Standby in Real Time even without switching the log at the Primary

MRP – Managed recovery process – For Data Guard, the background process that applies archived redo log to the standby database.

Data Guard Protection Modes

This section describes the Data Guard protection modes.

In these descriptions, a synchronized standby database is meant to be one that meets the minimum requirements of the configured data protection mode and that does not have a redo gap. Redo gaps are discussed in Section 6.3.3.

Maximum Availability

This protectionmode provides the highest level of data protection that is possible without compromising the availability of a primary database. Tractions do not commit until all redo data needed to recover those tractions has been written to the online redo log and to at least one synchronized standby database. If the primary database cannot write its redo stream to at least one synchronized standby database, it operates as if it were in maximum performance mode to preserve primary database availability until it is again able to write its redo stream to a synchronized standby database.

This mode ensures that no data loss will occur if the primary database fails, but only if a second fault does not prevent a complete set of redo data from being sent from the primary database to at least one standby database.

Maximum Performance

This protectionmode provides the highest level of data protection that is possible without affecting the performance of a primary database. This is accomplished by allowing tractions to commit as soon as all redo data generated by those tractions has been written to the online log. Redo data is also written to one or more standby databases, but this is done asynchronously with respect to traction commitment, so primary database performance is unaffected by delays in writing redo data to the standby database(s).

This protection mode offers slightly less data protection than maximum availability mode and has minimal impact on primary database performance.

This is the default protection mode.

Maximum Protection

This protection mode ensures that zero data loss occurs if a primary database fails. To provide this level of protection, the redo data needed to recover a traction must be written to both the online redo log and to at least one synchronized standby database before the traction commits. To ensure that data loss cannot occur, the primary database will shut down, rather than continue processing tractions, if it cannot write its redo stream to at least one synchronized standby database.

Because this data protection mode prioritizes data protection over primary database availability, Oracle recommends that a minimum of two standby databases be used to protect a primary database that runs in maximum protection mode to prevent a single standby database failure from causing the primary database to shut down.

It controls the automated trfer of redo data from the production database to one or more archival destinations.

The redo trport services perform the following tasks:

  1. Trmit redo data from the primary system to the standby systems in the configuration.
  2. Manage the process of resolving any gaps in the archived redo log files due to a network failure.
  3. Automatically detect missing or corrupted archived redo log files on a standby system and automatically retrieve replacement archived redo log files from the primary database or another standby database.

Following are the different benefits in using Oracle Data Guard feature in your environment:

  • High Availability.
  • Data Protection.
  • Off loading Backup operation to standby database.
  • Automatic Gap detection and Resolution in standby database.
  • Automatic Role Trition using Data Guard Broker.