Oracle Data Guard Interview Questions & Answers

What are the types of Oracle Data Guard?

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)

What are the advantages in using Oracle Data Guard?

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 Transition using Data Guard Broker.

What are the different services available in Oracle Data Guard?

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

Redo Transport Services.

Log Apply Services.

Role Transitions.

What are the different Protection modes available in Oracle Data Guard?

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:
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 transaction must be written to both the online redo log and to at least one standby database before the transaction commits. To ensure that data loss cannot occur, the primary database will shut down, rather than continue processing transactions.

Maximum availability:
This protection mode provides the highest level of data protection that is possible without compromising the availability of a primary database. Transactions do not commit until all redo data needed to recover those transactions has been written to the online redo log and to at least one standby 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 transactions to commit as soon as all redo data generated by those transactions has been written to the online log.

How to check what protection mode of primary database in your Oracle Data Guard?

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

How to change protection mode in Oracle Data Guard setup?

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;

What are the advantages of using Physical standby database in Oracle Data Guard?

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

·         High Availability.

·         Load balancing (Backup and Reporting).

·         Data Protection.

·         Disaster Recovery.

What is physical standby database in Oracle Data Guard?

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 transactions 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 transactions 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.

What is Logical standby database in Oracle Data Guard?

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 synchronise with primary database are available for read-only operations like reporting, select queries and adding index on those tables and creating materialised 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.

What are the advantages of Logical standby database in Oracle Data Guard?

·         Better usage of resource

·         Data Protection

·         High Availability

·         Disaster Recovery

What is the usage of LOG_FILE_NAME_CONVERT parameter in Oracle Data Guard setup?

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

Tell me about parameter which is used for standby database?

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 dg2. 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.

How many standby databases we can create (in 10g/11g)?
Till Oracle 10g, 9 standby databases are supported.
From Oracle 11g R2, we can create 30 standby databases.

What are differences between physical, logical, snapshot standby and ADG (or) what are different types of standby databases?
Physical standby – in mount state, MRP (Managed recovery process) will apply archives
ADG (Active Data Guard) – in READ ONLY state, MRP will apply archives
Logical standby – in READ ONLY state, LSP (Logical Standby process) 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.

How to find out backlog of standby?
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);

If you didn't have access to the standby database and you wanted to find out what error has occurred in a data guard configuration, what view would you check in the primary database to check the error message?
You can check the v$dataguard_status view. 
select message from v$dataguard_status;

How can u recover standby which far behind from primary (or) without archive logs how can we make standby sync?
By using RMAN incremental backup.

What is snapshot standby (or) How can we give a physical standby to user in READ WRITE mode and let him do updates and revert back to standby?
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.

.  What are the services required on the primary and standby data-base?

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 transmit 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.

Log network server (LNS): LNS is used on the primary to initiate a connection with the standby database.

The services required on the standby database are:

Fetch Archive Log (FAL) Client – Pulls archived redo log files from the primary site. Initiates transfer 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.

What is RTS (Redo Transport Services) in Data-guard?

It controls the automated transfer of redo data from the production database to one or more archival destinations. The redo transport services perform the following tasks:

·         Transmit redo data from the primary system to the standby systems in the configuration.

·         Manage the process of resolving any gaps in the archived redo log files due to a network failure.

·         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.

·         Control the automated transfer of redo data from a database destination to one or more destinations. Redo transport services also manage the process of resolving any gaps in the archived redo log files due to a network failure.

How to delay the application of logs to a physical standby?

·         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.