Data Guard Interview Questions and Answers

1.What is meant by Data Guard ?
The software which Oracle Corporation markets as Oracle Data Guard forms an extension to the Oracle relational database management system (RDBMS). It aids in establishing and maintaining secondary standby databases as alternative/supplementary repositories to production primary databases.

Oracle provides both graphical user interface (GUI) and command-line (CLI) tools for managing Data Guard configurations.

Data Guard supports both physical standby and logical standby sites. Oracle Corporation makes Data Guard available only as a bundled feature included within its “Enterprise Edition” of the Oracle RDBMS.^[1]

With appropriately set-up Data Guard operations, DBAs can facilitate failovers or switchovers to alternative hosts in the same or alternative locations.

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

1. Physical standby(Redo Apply technology)
2. Logical standby(SQL Apply Technology)
3. Snapshot standby(not applied until the snapshot standby is Converted)

3. What is the use of standby redolog ?
The Advantage of having Standby Redo Logs is that every records written into the Online RedoLogs of the Primary Database
is transferred to the Standby database and written into the Standby Redo Logs at the same time.
therefore, you it minimizes the probability of Data Loss on the Standby Database.

For real time apply, it is mandatory to have redolog.

Without Standby Redo Logs, an Archived Redo Log is created by the RFS process and when it has completed,
this Archived Redo Log is applied to the Standby Database by the MRP process . An incomplete ArchiveLog file cannot be applied on the Standby Database and will not be used in a Failover situation. This causes a certain data loss.

If you have Standby Redo Logs, the RFS process will write into the Standby Redo Log ,when there record entered in online redolog and when a log switch occurs, the Archiver Process of the Standby Database will archive this Standby Redo Log to an Archived Redo Log, while the MRP process applies the information to the Standby Database. In a Failover situation, you will also have access to the information already written in the Standby Redo Logs, so the information will not be lost.

4. What are the advantages in using Oracle Data Guard?
Following are the different benefits in using Oracle Data Guard feature in your environment.

1. High Availability.
2. Data Protection.
3. Off loading Backup operation to standby database.
4. Automatic Gap detection and Resolution in standby database.
5. Automatic Role Transition using Data Guard Broker.

5. What is active dataguard. Does it needs additional licensing?
Active dataguard means, the standby database is open with read only mode, when redo logs are getting applied in real time.

Below are the benefit of using active dataguard.

• Reporting queries can be offloaded to standby database.
• Physical block corruptions are repaired automatically either at primary or physical standby database.
• RMAN backups can be initiated from standby , instead  of primary which will reduce cpu load from primary.

NOTE – To use active dataguard, you need additional license from oracle

6. What are the different services available in Oracle Data Guard?
Following are the different Services available in Oracle Data Guard of Oracle database.

1. Redo Transport Services.
2. Log Apply Services.
3. Role Transitions.

• Redo Transport Services

Control the automated transfer of redo data from the production database to one or more archival destinations.

• Apply Services

Apply redo data on the standby database to maintain transactional synchronization with the primary database. Redo data can be applied either from archived redo log files, or, if real-time apply is enabled, directly from the standby redo log files as they are being filled, without requiring the redo data to be archived first at the standby database.

• Role Transitions

Change the role of a database from a standby database to a primary database, or from a primary database to a standby database using either a switchover or a failover operation.

7. What are the different types of redo transport services in dataguard.
SYNC and ASYNC.

SYNC(SYNCHRONOUS):

This mode is used for maximum protection and maximum availability protection mode. The synchronous redo transport mode transmits redo data synchronously with respect to transaction commitment. A transaction cannot commit until all redo generated by that transaction has been successfully sent to every standby destinations.

ASYNC(ASYNCHRONOUS):

This mode is used for maximum performance mode. A transaction can commit without waiting for the redo generated by that transaction to be successfully sent to any redo transport destination that uses the asynchronous redo transport mode.

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

1. Maximum Protection
2. Maximum Availability
3. Maximum Performance (Default Mode)

9. What are the new feature for DG in 11g?
Active database duplicate

snapshot stand by

Active data guard

Redo compression

32 standby database  configuration support (earlier only 9)

FAL_CLIENT not require

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

SQL> select protection_mode from v$database;

PROTECTION_MODE
——————————–

MAXIMUM PERFORMANCE

11. What is difference between Real time apply and redo apply?
IN real time apply, LOG APPLY Service will not wait for the current SRL to archive .SO the redo data is apply AS SOON AS it receive from primary.
But in normal apply mode : LOG APPLY service will wait for SRL to archive.

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

13. Which process is responsible for Redo log information  transmitting?
LGWR or ARCH

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

15. Which parameter user for GAP Resolution

FAL_SERVER and FAL_CLIENT

When MRP finds that an archive log is missing during media recovery, it sends the fal_client information to the server identified by fal_server and requests fal_server to resend the file again.  The fal_client and fal_server init.ora (spfile) parameters are set on the standby instance

16. What is physical standby database in Oracle Data Guard?
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.

As of Oracle Database 11g release 1 (11.1), a physical standby database can receive and apply redo while it is open for read-only access. A physical standby database can therefore be used concurrently for data protection and reporting.

17.What is difference between Physical stand by and snapshot standby.
Snapshot stdb will receive redo but not apply .Normal standby will receive and apply redo.

18. What is Logical standby database in Oracle Data Guard?
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 transforms the data in the redo received from the primary database into SQL statements and then executes the SQL statements on the standby database.

A logical standby database can be used for other business purposes in addition to disaster recovery requirements. This allows users to access a logical standby database for queries and reporting purposes at any time. Also, using a logical standby database, you can upgrade Oracle Database software and patch sets with almost no downtime. Thus, a logical standby database can be used concurrently for data protection, reporting, and database upgrades.

19. What is active dataguard  duplicate?
Starting from 11g we can duplicate database by two way 1) Active DB duplicate 2)Backup-based duplicate.
Active DB duplicate copies the live TARGET DB over the network to the AUXILLARY destination and then create the duplicate database.In an active duplication process, target database online image copies and archived redo log files were copied through the auxiliary instance service name. So there is no need of target db backup.

20. What is Snapshot standby database in Oracle Data Guard?
A snapshot standby database is a fully updatable 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.

A snapshot standby database is best used in scenarios that require a temporary, updatable snapshot of a physical standby database. Note that because redo data received by a snapshot standby database is not applied until it is converted back into a physical standby, the time needed to recover from a primary database failure is directly proportional to the amount of redo data that needs to be applied.