Oracle Data Guard Interview Questions and Answers

Want to switch your career to Oracle Data Guard? Then we will provide you with all the necessary details for you to clear the interview on Oracle Data Guard jobs. With our jobs portal you will find the number of jobs related to you along with the Oracle Data Guard Interview Questions and Answers. There are various leading companies that offer jobs in various roles like Oracle Applications DBA, Oracle Developer, Oracle Financials / Manufacturing Specialists, Oracle Developer, Senior Oracle Database Administrator with 12C and many other roles too. To save the time in searching for all the interview questions and answers on different site we have provided you all type of interview question and answers at one place.

1.What Is a Data Guard?

Ans:

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.

2.What Is a Dg Broker?

Ans:

DG Broker “it is the management and monitoring tool”.
Oracle data guard 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”.

3.What Is The Difference Between Data Guard And Standby?

Ans:

Data Guard : Data Guard is a mechanism/tool to maintain a standby database. The data guard is set up between primary and standby instances . 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.

4.What Are The Differences Between Physical/Logical Standby Databases? How Would You Decide Which One Is Best Suited For Your Environment?

Ans:

Physical standby DB: As the name implies, it is physically (datafiles, schema, other physical identity) the 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, its physical structure can be different.
It synchronized with the primary database through SQL Apply, Redo received from the primary database into SQL statements and then executed these SQL statements on the standby DB.
We can open “physical standby 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 the primary database at this time.
We do not see such issues with logical standby databases. 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 the primary database.
For OLTP large transaction databases it is better to choose a logical standby database.

5.Explain Active Data Guard?

Ans:

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.

6.What Are Differences Between Physical, Logical, Snapshot Standby And Adg (or) What Are Different Types Of Standby Databases?

Ans:

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 to all pending archives.

7.How To Find A Backlog Of Standby?

Ans:

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

9.How Can U Recover Standby Which Far Behind From Primary (or) Without Archive Logs How Can We Make Standby Sync?

Ans:

By using RMAN incremental backup.

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

Ans:

Till Oracle 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.

11.What Are New Features In 11g Data Guard?

Ans:

Here is some data guard category and there enhancement

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

12.What Are The Uses Of Standby Redo Log Files?

Ans:

A standby redo log is required for the maximum protection and maximum availability modes and the LGWR ASYNC transport 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.

13.What Is Dg_config ?

Ans:

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.

14.What Is Rta (Real Time Apply) Mode Mrp?

Ans:

real-time apply where before log shipping the LGWR process writes to a standby log file simultaneously along with the online redo log file.
This standby logfile is written to the standby logfile on the standby server. There is no loss of any committed transaction whatsoever in the Real-Time Apply scenario.

In Real Time Apply, once a transaction 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.

15.What Is The Difference Between Sync/async, Lgwr/arch, And Affirm/noaffirm ?

Ans:

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 transport services use archiver processes (ARCn) or the log writer process (LGWR) to collect transaction redo data and transmit it to standby destinations. If neither the ARCH or LGWR attributes are specified, the default is ARCH.
Controls whether redo transport 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.

16.What Is Staticconnectidentifier Property Used For?

Ans:

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.

17.What Is Failover/switchover (or) What Is The Difference Between Failover And Switchover?

Ans:

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.

18.What Are The Background Processes Involved In Data Guard?

Ans:

MRP, LSP

19.What Are Different Types Of Modes In Data Guard And Which Is Default?

Ans:

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

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

21.What Are The Parameters We’ve To Set In Primary/Standby For Data Guard ?

Ans:

22.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).

23.What Is A Snapshot Standby Database?

Ans:

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  its 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 accumulated 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.

24.Snapshot Standby Database (updatable Snapshot For Testing)?

Ans:

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.

25.Data Guard Architecture?

Ans:

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.
Data Guard 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 transferred 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 Transport Services – Control the automatic transfer 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 transactionally 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 transmitting 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 transforms the data in the redo received from the primary database into SQL statements and then executes the SQL statements on the standby database.

26.What Are The Services Required On The Primary And Standby Database ?
Ans:
The services required on the primary database are:

Ans:

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

27.What Is Rts (redo Transport Services) In Data Guard?

Ans:

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.

28.What Are The Protection Modes In Dataguard?

Ans:

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 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 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 protection mode 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. Redo data is also written to one or more standby databases, but this is done asynchronously with respect to transaction 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 Protectio
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 transaction must be written to both the online redo log and to at least one synchronized standby database before the transaction commits. To ensure that data loss cannot occur, the primary database will shut down, rather than continue processing transactions, 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.

29.How To Delay The Application Of Logs To A Physical Standby?

Ans:

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:

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.

30.Steps To Create Physical Standby Database?

Ans:

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

31.What Are The Data Guard Parameters In Oracle?

Ans:

Set Primary Database Initialization Parameters
———————————————-
On the primary database, you define initialization parameters that control redo transport 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 transitioned to the standby role.

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:

32.What Are The Types Of Oracle Data Guard?

Ans:

Oracle Data Guard classified into 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)

33.What Are The Advantages In Using Oracle Data Guard?

Ans:

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.

34.What Are The Different Services Available In Oracle Data Guard?

Ans:

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

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

35.What Are The Different Protection Modes Available In Oracle Data Guard?

Ans:

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

• Maximum Protection
• Maximum Availability
• Maximum Performance.

36.How To Check What Protection Mode Of Primary Database In Your Oracle Data Guard?

Ans:

By using the following query you can check the protection mode of the primary database in your Oracle Data Guard setup.

For Example:

37.What Are The Advantages Of Using A Physical Standby Database In Oracle Data Guard?

Ans:

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

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

38.What Is Physical Standby Database In Oracle Data Guard?

Ans:

Oracle Standby databases are divided into a physical standby database or logical standby database based on standby database creation and redo log apply method. Physical standby databases 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 databases can offload the backup activity and reporting activity from the Primary database. Physical standby databases can be opened for read-only transactions but redo apply won’t happen during that time. But from 11g onwards using the Active Data Guard option (extra purchase) you can simultaneously open the physical standby database for read-only access and apply redo logs received from the primary database.

39.What Is a Logical Standby Database In Oracle Data Guard?

Ans:

Oracle Standby databases are divided into a 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 a Physical standby database and later you can alter the structure of the 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 applies those SQL statements on logical standby databases to make standby databases consistent with primary databases. Main advantage of a Logical standby database compared to a physical standby database is you can use a Logical standby database for reporting purposes during SQL apply i.e Logical standby database must be open during SQL apply.
Even though Logical standby databases are opened for read/write mode, tables which are in synchronization with primary databases are available for read-only operations like reporting, select queries and adding indexes on those tables and creating materialized views on those tables. Though the Logical standby database has advantages on the Physical standby database it has some restrictions on data-types, types of DDL, types of DML and types of tables.

40.What Are The Advantages Of Logical Standby Database In Oracle Data Guard?

Ans:

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

41.What Is The Usage Of Db_file_name_convert Parameter In Oracle Data Guard Setup?

Ans:

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

42.What Is The Usage Of Log_file_name_convert Parameter In Oracle Data Guard Setup?

Ans:

LOG_FILE_NAME_CONVERT parameter is used in Oracle Data Guard setup in standby databases. LOG_FILE_NAME_CONVERT parameters are used to update the location of redo log files in the standby database. These parameters are used when you are using different directory structure in standby databases compared 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

43.Tell Me About The Parameter Which Is Used For Standby Database?

Ans:

• 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

44.What are the advantages of using Oracle Data Guard?

Ans:

The following are the different benefits in 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.

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

Ans:

46.How to change protection mode in Oracle Data Guard setup?

Ans:

Learn Oracle Data Guard by Tekslate – Fastest growing sector in the industry. Explore “Oracle Data Guard Training “Videos and course is aligned with industry needs & developed by industry veterans. Tekslate will turn you into an Oracle Data Guard Expert.

47.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 primary database data files location & also when we duplicate database this parameter can be used to generate files in a different location.

48.What are the services required on the primary and standby database?

Ans:

The services required on the primary database are:

1. 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. 2.Archiver Process (ARCn): One or more archiver processes make copies of online redo logs either locally or remotely for standby databases.
3. 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. 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:

1. 1.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.
2. 2.Remote File Server (RFS): Receives archived and/or standby redo logs from the primary database.
3. 3.Archiver (ARCn) Processes: Archives the standby redo logs applied by the managed recovery process (MRP).
4. 4.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:

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

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

Ans:

Till Oracle 10g, 9 standby databases are supported.

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

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

Ans:

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

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 to all pending archives.

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

Ans:

53.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).

54.How to find out the backlog of standby?

Ans:

55.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, start MRP.

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

56.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 primary or physical standby databases.
• RMAN backups can be initiated from standby, instead of the primary which will reduce CPU load from the primary.

57.What is meant by Data Guard?

Ans:

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.

58.What is the use of standby redo log?

Ans:

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 minimize the probability of Data Loss on the Standby Database.

For real time apply, it is mandatory to have redo log.

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 redo log 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.

59.What is active data guard. Does it needs 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 are the benefit of using active data guard:

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

NOTE: To use an active data guard, you need an additional license from oracle.

60.What are the different types of redo transport services in dataguard?

Ans:

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

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.

61.What are the new features for DG in 11g?

Ans:

• Active database duplicate
• snapshot standby
• Active data guard
• Redo compression
• 32 standby database  configuration support (earlier only 9)
• FAL_CLIENT not require

62.What is the difference between Real time apply and redo apply?

Ans:

In real time apply, LOG APPLY Service will not wait for the current SRL to archive .SO the redo data is applied AS SOON AS it is received from primary.

But in normal apply mode : LOG APPLY service will wait for SRL to archive.

63.Which process is responsible for Redo log information  transmitting?

Ans:

LGWR or ARCH.

64.Which parameter user for GAP Resolution?

Ans:

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

65.What is the physical standby database in Oracle Data Guard?

Ans:

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.

66.What is the difference between Physical standby and snapshot standby?

Ans:

Snapshot stdb will receive redo but not apply .Normal standby will receive and apply redo.

67.What is the Logical standby database in Oracle Data Guard?

Ans:

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.

68.What is active data guard  duplicate?

Ans:

Starting from 11g we can duplicate database by two way:

1. 1.Active DB duplicate.
2. 2.Backup-based duplicate.

Active DB duplicate copies the live TARGET DB over the network to the AUXILIARY destination and then creates 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 for target db backup.

69.What is Snapshot standby database in Oracle Data Guard?

Ans:

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

70.What is a data guard in simple language?

Ans:

Your primary database is running and you want to reduce downtime because of unplanned outages. You create a replica of this primary database (termed as standby database).

You regularly ship redo generated in the primary database to the standby database and apply it there. So that is our ‘Data Guard’ standby database and it is in a continuous state of recovery, validating and applying redo to remain in sync with the primary database.

71.Your standby database was out of reach because of network issues. How will you synchronize it with the primary database again?

Ans:

Data Guard automatically resynchronizes the standby following network or standby outages using redo data that has been archived at the primary.

72.What is Redo Transport Services (RTS)?

Ans:

This process takes care of the transmission of redo from a primary database to the standby database.

Below is how Redo Transport Services (RTS) works:

1) Log Network Server (LNS) reads redo information from the redo buffer in SGA of PRIMARY Database

2) Log Network Server (LNS) passes redo to Oracle Net Services for transmission to the STANDBY database

3) Remote File Server (RFS) records the redo information transmitted by the LNS at the STANDBY database

4) Remote File Server (RFS) writes it to a sequential file called a standby redo log file (SRL) at the STANDBY database

** For multi-standby configuration, the primary database has a separate LNS process for each standby database.

** Two redo transport methods are supported with the LNS process: synchronous (SYNC) or asynchronous (ASYNC).

73.What is the difference between SYNC and ASYNC redo transport method?

Ans:

Synchronous transport (SYNC)

Also known as a “zero data loss” redo transport method.

Below is how it works:

1) Log Network Server (LNS) reads redo information from the redo buffer in SGA of PRIMARY Database

2) Log Network Server (LNS) passes redo to Oracle Net Services for transmission to the STANDBY database

3) Remote File Server (RFS) records the redo information transmitted by the LNS at the STANDBY database

4) Remote File Server (RFS) writes it to a sequential file called a standby redo log file (SRL) at the STANDBY database

5) Remote File Server (RFS) transmits an acknowledgement back to the LNS process on the primary database

6) Log Network Server (LNS) notifies the LGWR that transmission is complete on the primary database.

7) Log Writer (LGWR) acknowledges the commitment to the user.

Asynchronous transport (ASYNC)

Unlike SYNC, Asynchronous transport (ASYNC) eliminates the requirement that the LGWR wait for acknowledgement from the LNS. This removes the performance impact on the

primary database irrespective of the distance between primary and standby locations.

So if the LNS is unable to keep pace and the log buffer is recycled before the redo can be transmitted to the standby, the LNS automatically transitions to reading and sending from the Online Redo logs. Once the LNS is caught up, it automatically transitions back to reading & sending directly from the log buffer.

Below is how it works:

1) Log Network Server (LNS) reads redo information from the redo buffer in SGA of PRIMARY Database

2) Log Network Server (LNS) passes redo to Oracle Net Services for transmission to the STANDBY database

3) Remote File Server (RFS) records the redo information transmitted by the LNS at the STANDBY database

4) Remote File Server (RFS) writes it to a sequential file called a standby redo log file (SRL) at the STANDBY database

so step 5, 6 & 7 as discussed above for SYNC are not applicable here.

The only drawback of ASYNC is the increased potential for data loss. Say a failure destroyed the primary database before any transport lag was reduced to zero, this means any committed transactions that were a part of the transport lag will be lost. So it is highly advisable to have enough network bandwidth to handle peak redo

generation rates when using ASYNC method.

74.How Synchronous transport (SYNC) can impact the primary database performance?

Ans:

SYNC guarantees protection for every transaction that the database acknowledges as having been committed but at the same time LGWR must wait for confirmation that data is protected at the standby before it can proceed with the next transaction. It can impact primary database performance and it depends on factors like

• the amount of redo information to be written
• available network bandwidth
• round-trip network latency (RTT)
• standby I/O performance writing to the SRL.
• distance between primary and standby databases as network RTT increases with distance.

75.What is Data Guard’s Automatic Gap Resolution?

Ans:

Your database is using ASYNC transport method and the instance load is at the peak. The LNS is unable to keep pace and the log buffer is recycled before the redo can be transmitted to the standby, the LNS automatically transitions to reading and sending from the Online Redo logs. Once the LNS is caught up, it automatically transitions back to reading & sending directly from the log buffer.

Now in some cases there can be two or more log switches before the LNS has completed sending the redo information from online redo log files and in meantime if any such required online redo log files were archived then those redo information will be transmitted via Data Guard’s gap resolution process “Automatic Gap Resolution”.

OR

In some other cases when your network or the standby database is down and your primary system is one busy system, so before the connection between the primary and standby is restored, a large log file gap will be formed.

Automatic Gap Resolution will take care of such scenarios by following below action plan:

1) ARCH process on the primary database continuously ping the standby database during the outage to determine its status.

2) As soon as the standby is restored, the ARCH ping process queries the standby control file (via its RFS process) to determine the last complete log file that the standby received from the primary database.

3) Data Guard determines which log files are required to resynchronize the standby database and immediately begins transmitting them using additional ARCH processes.

4) LNS process at primary database will also attempt and succeed in making a connection to the standby database and will begin transmitting current redo. So first all the ARCH files are applied and then current redo log.

The Data Guard architecture enables gaps to be resolved quickly using multiple background ARCH processes

76.What is the difference between Physical standby and Logical standby database?

Ans:

Data Guard Apply process in standby database can apply redo information directly and in that case it will be called physical standby.

OR It can apply SQL and in that case it will be called Logical standby.

Physical Standby:

In this case standby database is an exact, block-by-block, physical replica of the primary database.

The change vectors received by the RFS process are directly applied to the standby database by using media recovery.so here the apply process reads data blocks, assemble redo changes from mappings, and then apply redo changes to data blocks directly.

Physical Standby is the best choice for disaster recovery (DR) based upon their simplicity, transparency, high performance, and good data protection.

Logical Standby:

In this case standby database uses SQL Apply method to “mine” the redo by converting it to logical change records, and then building SQL

transactions and applying SQL to the standby database.

As this process of replaying the workload is more complex than the Physical Standby’s process, it requires more memory, CPU, and I/O.

One good advantage here is that a logical standby database can be opened read-write while SQL Apply is active which means you can update (create/insert/delete etc) local tables and schemas in the logical standby database.

77.How is the Data Guard Apply process works if the primary and secondary database involves Oracle RAC?

Ans:

If Primary database is RAC but standby is Non-RAC:

Each primary Oracle RAC instance ships its own thread of redo that is merged by the Data Guard apply process at the standby and applied in SCN order to the standby database.

If both Primary and standby databases are RAC:

If the standby is also an Oracle RAC database, only one instance (the apply instance) will merge and apply changes to the standby database. If the apply instance fails for any reason, the apply process will automatically failover to a surviving instance in the Oracle RAC standby database when using the Data Guard broker.

78.What is Active Data Guard Option (Oracle Database 11g Enterprise Edition)?

Ans:

For physical standby databases, prior to 11g, the database would have to be in the mount state when media recovery was active which means you were not able to query the standby database during the media recovery stage as there was no read-consistent view.

Active Data Guard 11g features solves the read consistency problem by use of a “query” SCN. The media recovery process on the standby database will advance the query SCN after all the changes in a transaction have been applied . The query SCN will appear to the user as the CURRENT_SCN column in the V$DATABASE view on the standby database. So Read-only users will only be able to see data up to the query SCN, and hence guaranteeing the same read consistency as the primary database.

This enables a physical standby database to be open as read-only while media recovery is active, making it useful for doing read-only workloads.

Also, if you need read-write access to the standby database, you can use SQL Apply method of data guard.

79.What are the important database parameters related to Data Guard corruption prevention?

Ans:

a) DB_ULTRA_SAFE

Values can be DATA_AND_INDEX or DATA_ONLY. Setting DB_ULTRA_SAFE at the primary will also automatically set DB_ LOST_WRITE_PROTECT=TYPICAL on the primary database.

In Oracle Database 11g Release 2 (11.2), the primary database automatically attempts to repair the corrupted block in real time by fetching a good version of the same block from a physical standby database.

On the standby database:

a) DB_BLOCK_CHECKSUM=FULL

DB_BLOCK_CHECKSUM detects redo and data block corruptions and detects corruptions on the primary database and protects the standby database. This parameter requires minimal CPU resources.

b) DB_LOST_WRITE_ PROTECT=TYPICAL

A lost write can occur when an I/O subsystem acknowledges the completion of a write, while in fact the write did not occur in persistent storage.

This will create a stale version of the data block. When the DB_LOST_WRITE_PROTECT initialization parameter is set, the database records buffer cache block reads in the redo log, and this information is used to detect lost writes.

You set DB_LOST_WRITE_PROTECT to TYPICAL in both primary and standby databases.

80.What are different Data Guard protection modes?

Ans:

Data Guard protection modes implement rules that control how the configuration will respond to failures, enabling you to achieve specific objectives for data protection, availability, and performance.

a) Maximum Performance

• emphasis is on primary database performance over data protection.
• requires ASYNC (the default method) redo transport so that the LGWR process never waits for acknowledgment from the standby database.
• network connection between primary and standby OR the availability of the standby database DO NOT IMPACT the primary database performance

b) Maximum Availability

• first emphasis is on availability and second priority is zero data loss protection.
• requires SYNC redo transport so primary database performance may be impacted in waiting for acknowledgement from the standby (it doesn’t mean indefinite wait in case standby database fails, maximum wait will be equal to parameter NET_TIMEOUT seconds).

c) Maximum Protection

• utmost priority is on data protection.
• also requires SYNC redo transport.
• unlike ‘Maximum Availability’ it does not consider the NET_TIMEOUT parameter, which means If the primary does not receive acknowledgment from a SYNC standby database, it will stall primary and eventually abort it, preventing any unprotected commits from occurring.
• highly recommended to use a minimum of two SYNC standby databases at different locations if using ‘Maximum Protection’ to have high availability of primary databases.

81.What is a Switchover event?

Ans:

Switchover is useful for minimizing downtime during planned maintenance. It is a planned event in which Data Guard reverses the roles of the primary and a standby database.

The primary database runs unaffected while we are making the required changes on our standby database (e.g. patchset upgrades, full Oracle version upgrades, etc).

Once changes are complete, production is switched over to the standby site running at the new release.

This means regardless of how much time is required to perform planned maintenance, the only production database downtime is the time required to execute a switchover, which can be less than 60 seconds

Below operations happens when switchover command is executed:

1. 1. the primary database is notified that a switchover is about to occur.
2. 2. all users are disconnected from the primary.
3. 3. a special redo record is generated that signals the End Of Redo (EOR).
4. 4. the primary database is converted into a standby database.
5. 5. The final EOR record is applied to the standby database, this guarantees that no data has been lost, and it converts the standby to the primary role.

82.What is a Failover event?

Ans:

The Failover process is similar to switchover events except that the primary database never has the chance to write an EOR record as this is an unplanned event.

Whether or not a failover results in data loss depends upon the Data Guard protection mode:

a) Maximum Protection >> No Data Loss

b) Maximum Availability >> No Data Loss (except when there was a previous failure (e.g. a network failure) that had INTERRUPTED REDO TRANSPORT and allowed the primary database to move ahead of standby)

c) Maximum Performance (ASYNC) >> may lose any committed transactions that were not transmitted to the standby database before the primary database failed.

Failover event can be of two types:

1) Manual

Administrators have complete control of primary-standby role transitions. It can lengthen the outage by the amount of time required for the administrator to be notified and manual execution of command.

2) Automatic

It uses Data Guard’s Fast-Start Failover feature which automatically detects the failure, evaluates the status of the Data Guard configuration, and, if appropriate, executes the failover to a previously chosen standby database.

83.Which tools can be used for Data Guard Management?

Ans:

1) SQL*Plus – traditional method, can prove most tedious to use

2) Data Guard broker – automates and centralizes the creation, maintenance, and monitoring of a Data Guard configuration. Simplifies and automates many administrative

tasks. It has its own command line (DGMGRL) and syntax.

3) Enterprise Manager – requires that the Data Guard broker be enabled. a GUI to the Data Guard broker, replacing the DGMGRL command line and interfacing directly with the broker’s monitor processes.

84.What is a Data Guard 11g snapshot standby?

Ans:

With 11g, you can thoroughly test your changes on a true replica of your production system and database using actual production workload.

Data Guard 11g physical standby can now be converted to a snapshot standby, independent of the primary database, that is open read-write and able to be used for pre production testing. It uses Flashback Database and sets a guaranteed restore point (GRP) at the SCN before the standby is open read-write.

NOTE: Primary database redo continues to be shipped to a snapshot standby, and while not applied, it is archived for later use.

You can convert this snapshot database back into a synchronized physical standby database when testing is complete. Redo Apply process at standby will take care that all

primary database redo archived while a snapshot standby is applied until it is caught up with the primary database.

85.What is the difference between Recovery Point Objective(RPO) and Recovery Time Objective (RTO)?

Ans:

A) Recovery Point Objective(RPO)

RPO concerns with data. It is the amount of data you are willing to lose when the failure occurs in your database system. Usually people define data loss in terms of time, so possible values can be 5 seconds of data loss, 2 hours of data loss etc.

Remember that each standby database has its own set of attributes and parameters. It means you can mix zero data loss standby databases with minimal data loss standby

databases in the same Data Guard configuration

If you have decided that you want to implement zero data loss strategy, then you should really focus on Networks and Data Loss

B) Recovery Time Objective (RTO)

RTO is defined as how fast you can get back up and running (whereas RPO is concerned with data loss)

So with your RPO strategy you lost say only about 6 seconds of data as you committed to your client but with RTO you need to formulate how fast clients can connect back to the database system after the data loss has occurred.