Oracle Data Guard Interview Questions

ORL or Oracle Local Repository comprises information that enables the cluster programs to initiate with the OCR in the ASM storage. The ASM file is unavailable until the grid process starts. So, a local copy of the OCR data is stored in the OLR.

In this article, we will cover the following:

• Oracle Data Guard Interview Questions
• Oracle Data Guard 11g Interview Questions
• Oracle Data Guard 12c Interview Questions

Most frequently asked Oracle Data Guard Interview Questions

What are the advantages of using Oracle Data Guard? What are the different Protection modes available in Oracle Data Guard? How to change the protection mode in the Oracle Data Guard setup? What is RTS (Redo Transport Services) in Data-guard? How many standby databases we can create (in 10g/11g)? What are the parameters we’ve to set in primary/standby for Data Guard? How to find out the backlog of standby? What is an active data guard? Does it need additional licensing? What are the differences between switchover and failover?

Oracle Data Guard Interview Questions

Q1) What are the advantages of using Oracle Data Guard?

Ans: The following are the different benefits of using the Oracle Data Guard feature in your environment.

• High Availability.
• Data Protection.
• The off-loading Backup operation to standby database.
• Automatic Gap detection and Resolution in standby database.
• Automatic Role Transition using Data Guard Broker.

Q2) What are the different services available in Oracle Data Guard?

Ans: The following are the different Services available in the Oracle Data Guard of the Oracle database.

• Redo Transport Services.
• Log Apply Services.
• Role -Transitions.

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

Ans: Below are the protection modes available in DG

1. Maximum Protection
2. Maximum Availability
3. 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.

Q4) How to check what protection mode of the primary database in your Oracle Data Guard is?

Ans:

SELECT PROTECTION_MODE FROM V$DATABASE;

Q5) How to change the protection mode in the Oracle Data Guard setup?

Ans:

ALTER DATABASE SET STANDBY DATABASE TO MAXIMUM [PROTECTION | PERFORMANCE | AVAILABILITY];

Do you want to Master Oracle Data Guard? Then enrol in "Oracle Data Guard Training" This course will help you to master Oracle Data Guard

Q6) What are the advantages of using a physical standby database in Oracle Data Guard?

Ans:

1. High Availability.
2. Load balancing (Backup and Reporting).
3. Data Protection.
4. Disaster Recovery.

Q7) What is the usage of the DB_FILE_NAME_CONVERT parameter in the Oracle Data Guard setup?

Ans: DB_FILE_NAME_CONVERT This parameter is used when you are using different directory structures in the standby database compared to the primary database data files location & also when we duplicate the database this parameter can be used to generate files in a different location.

Q8) What are the services required on the primary and standby databases?

Ans: The services required on the primary database are:

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

2. Archiver Process (ARCn):

One or more archiver processes make copies of online redo logs either locally or remotely for standby databases.

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

4. 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:

5. Fetch Archive Log (FAL) Client:

Pulls archived redo log files from the primary site. Initiates the transfer of archived redo logs when it detects a gap sequence.

6. Remote File Server (RFS):

Receives archived and/or standby redo logs from the primary database.

7. Archiver (ARCn) Processes:

Archives the standby redo logs applied by the managed recovery process (MRP).

8. Managed Recovery Process (MRP):

applies archive redo log information to the standby database. It controls the automated transfer of redo data from the production database to one or more archival destinations. Redo transport services perform the following tasks:

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

Ans: 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.

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

Ans: A standby database automatically applies to 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.

Checkout Blog on Oracle BI Publisher Tutorial

Oracle Data Guard 11g Interview Questions

Q11) How many standby databases we can create (in 10g/11g)?

Ans: Till Oracle 10g, 9 standby databases are supported.

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

Q12) What are the differences between physical, logical, snapshot standby, and ADG (or) what are the different types of standby databases?

Ans: Physical standby – in MOUNT STATE, MRP proves will apply the archives

ADG – in READ ONLY state, MRP will apply the archives

Logical standby – in READ ONLY state, LSP will run

Snapshot standby databases – Physical standby database can be converted to a 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 the physical standby database and start MRP which will apply all pending archives.

Q13) How many standby databases we can create (in 10g/11g)?

Ans: Till Oracle 10g, 9 standby databases are supported.

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

Q14) What are the parameters we’ve to set in primary/standby for Data Guard?

Ans:

  DB_UNIQUE_NAME

LOG_ARCHIVE_CONFIG

LOG_ARCHIVE_MAX_PROCESSES

DB_CREATE_FILE_DEST

DB_FILE_NAME_CONVERT

LOG_FILE_NAME_CONVERT

LOG_ARCHIVE_DEST_n

LOGARCHIVE_DEST_STATE_n

FAL_SERVER

FAL_CLIENT

STANDBY_FILE_MANAGEMENT

Q15) What is the use of fal_server & fal_client, is it mandatory to set these?

Ans: 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).

Q16) How to find out the backlog of standby?

Ans:

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);

Oracle Data Guard 12c Interview Questions

Q17) 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?

Ans: You can check the v$dataguard_status view.

select a message from v$dataguard_status;

Q18) How can u recover standby which is far behind from primary (or) without archive logs how can we make standby sync?

Ans: By using RMAN incremental backup.

Q19) What is snapshot standby (or) How can we give a physical standby to the user in READ WRITE mode and let him do updates and revert back to standby?

Ans: Till Oracle 10g, create a guaranteed restore point, open in read-write, let him do updates, flashback to restore point, and start MRP.

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

Q20) What is an active data guard? Does it need additional licensing?

Ans: Active data guard means, the standby database is open with read-only mode when redo logs are getting applied in real-time. Below is the benefit of using an active data guard.

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

What is an active data guard duplicate? Starting from 11g we can duplicate the database in two ways

1) Active DB duplicate

2) Backup-based duplicate.

Active DB duplicate copies of 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 to target DB backup.

Q21) What are the different data guard protection modes?

Ans: There are three modes of protection in data guard: 

1. Maximum protection: This mode guarantees zero data loss. 

2. Maximum availability: This mode provides the highest level of data protection without affecting the availability of the primary database. 

3. Maximum performance: This mode is the default protection. 

Q22) What are the differences between switchover and failover? 

Ans:  A switchover is when the roles are switched between the primary database and the standby database. The primary database is changed to a standby role, and the standby database is changed to the primary role. 

A failover is when the primary database fails, and one of the standby databases takes over the primary role. There is no possibility of recovering the primary database.

Q23) What are the steps to apply a PSU patch in the Oracle data guard setup? 

Ans:  The steps are: 

• Make sure there is zero lag between primary and standby.
• Cancel the recovery (MRP) on standby 
• Shutdown the standby database and listener 
• Apply the patch to binary using opatch apply command 
• When the patch is applied, startup the listener and standby in the mount stage or OPEN 
• Start the MRP recovery process on standby. 

Q24) What are the primary and standby databases of Oracle Data Guard configurations? 

Ans: The Oracle Data Guard configuration contains: 

Primary Database: Also called the production database, has the primary role functions. This database is accessed by most applications. 

Standby Databases: This is a consistent copy of the primary database. There are three types: 

Physical standby database: As of Oracle Database 11g Release 1 (11.1), a physical standby database receives and applies redo when it is open for read-only access. 

Logical standby database: The flexibility of this standby database allows the upgrade of Oracle Database software and performs other database maintenance. 

Snapshot Standby Database: This standby database is used in situations that require a temporary, updatable snapshot of a physical standby database. 

Q25) What is Oracle Data Guard Far Sync Instances? 

Ans:  It is a remote Oracle Data Guard destination. It accepts redo from the primary database and then transports it to other parts of the Oracle Data Guard configuration. In Oracle Data Guard 12c, far sync instances are part of the Oracle Active Data Guard Far Sync feature.