Data Guard Interview Questions and Answers in Oracle 11g

In any data-driven environment, Data Guard offers an excellent set of advantages. As the name itself should indicate, Data Guard ensures that the data stays secure and also easily available. Any gaps in the standby database can be easily detected and issues if any are resolved.

In essence, Data Guard offers the following advantages –

• It keeps the data protected.
• A high degree of availability.
• Off-loading of backup operations
• Automatic role transition through the Data Guard Broker.

Data Guard offers you access to the following major protection modes –

• Maximum Protection
• Maximum Availability
• Maximum performance

The Maximum Protection mode ensures that there is no data loss in case of a primary database failure. This can be achieved by writing a redo data on the online redo log and to at least one standby database. These should be written prior to a transaction and will help in protecting a transaction.

The Maximum availability mode is designed to provide a high level of protection without affecting the availability of the primary database. The transaction will not commence until a redo data is written to the online redo log and to at least one standby database.

Maximum performance protection mode This is the default protection mode on Data Guard. This ensures a high degree of protection without affecting performance.

These are the three protection modes in the data guard.

As the name itself should indicate, the physical standby database refers to the physical copy of the entire primary database. This consists of data files, schema, other physical identities available on the primary database. It keeps synchronizing with the primary database and is helpful in applying Redo to the standby database.

A Logical standby database, on the other hand, is the same as the production database. Primary standby can be opened read-only alone and made available to the application users. You cannot apply the redo logs received from the primary database. You will not find such issues with logical standby databases. They can be opened in the normal mode. They also let you apply the logs received from the primary database.

FAL means Fetch Archive Log. The FAL_client and FAL_Server are the two parameters used for resolving the archive gap on the standby database. Do note that they are used by a standby database alone.

FAL_Server parameter specifies the FAL Server for the active standby database. It returns an Oracle Net service name. The server should be configured properly on the standby database so that it points to the desired FAL server.

FAL_Client is used for specifying the FAL client name for use with the FAL service. It is configured through the FAL_server initialization procedure.

Creating redo log files should be more comfortable and straightforward enough.  You need to make use of an Alter Database SQL statement along with ADD LOGFILE clause.

The following statement should add a new group to the redo logs database –

ALTER DATABASE

ADD LOGFILE (‘/oracle/dbs/log1c.rdo’, ‘/oracle/dbs/log2c.rdo’) SIZE 100M;

You can even specify the number of identities as well as in the following example –

ALTER DATABASE

ADD LOGFILE GROUP 10 (‘/oracle/dbs/log1c.rdo’, ‘/oracle/dbs/log2c.rdo’)

SIZE 100M BLOCKSIZE 512;

If you are on Oracle 10g, only nine standby databases can be created. However, on Oracle 11g, you can create up to 30 standby databases.

The different types of services available on Data Guard would include

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

The Snapshot Standby Database is a feature that lets you perform a read and write operation on the standby database. That would mean you can convert a physical standby database into to snapshot standby for the testing purposes. Once you are done with the testing, you can convert the database back into the physical standby database. The changes made to the snapshot standby are reverted once it is converted into a physical database.

A switchover refers to switching the roles between the primary database and the standby database. In essence, the standby database is changed onto the primary database, and a primary database is changed into the standby database. This is done for the purpose of planned maintenance of your primary database.

A failover is what happens when your primary database fails. In such cases, a standby database is made to undertake the role of the primary database. The failover happens only when a serious catastrophic failure of the primary database. In fact, it is used only in the case where recovering the primary database is no longer possible.

Do note that failover may or may not cause data loss and will be dependent on the protection mode in use.

The steps involved in creating a physical standby database would include –

• Take a full backup of your primary database.
• Create a standby control file
• Transfer the entire data, including full backup, init.ora, standby control file to standby node.
• Modify the init.ora file on the standby node
• Restore the database.
• Recover the standby database.
• Setup FAL_CLIENT and FAL_SERVER parameters
• Put the standby database into the Managed Recover mode.

RTS stands for Redo Transport Service. It is primarily used for the transfer of the redo data from the production database to one or more archival destinations.

It can be helpful in a host of areas that can include

• Transferring the redo data between primary and standby systems.
• Resolving the gaps in the archived redo logs as a result of network failure.
• Automatic detection of missing or corrupted redo log files on a standby system.

The Apply Services are designed for the purposes of applying the redo data on the standby system so that the transactions remain in sync between a standby system and a primary system. You can do it either through the means of archived redo files or the standby redo files.

The Active Data Guard will refer to the standby database which is open in read-only, but redo logs can be applied in real-time. It can be a useful feature in more ways than one.

Some of the advantages of using Active Data Guard can include

• You can offload the reporting queries to the standby database.
• Physical block corruptions if any can be repaired entirely automatically whether at the primary or standby database.
• You can initiate RMAN backup from standby. This is helpful in lowering the CPU load on the primary

Do note that Active Data Guard will require you to opt for a separate licensing. You need to contact Oracle for the purpose.

There are two types of redo transport services in Data Guard – SYNC and ASYNC.

The SYNC mode stands for synchronous and is best meant for maximum protection and maximum availability protection modes. It transmits redo data synchronously with respect to the transaction.

The ASYNC or asynchronous mode is used in the maximum performance mode. A transfer or transaction is committed without waiting for the redo generated.

The primary database requires the following services –

• Log Writer Process (LGWR): This will be used to collect redo information and updates the online redo logs. It is also used for creating local archived redo logs and transmit online redo to standby databases.
• Archiver Process (ARCn): The 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.
• Log network server (LNS): LNS is used on the primary for initiating the connection with the standby database.

The service requirements of the secondary database would include

• 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.
• 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): This is used to apply archive redo log information to the standby database.

DG Broker or Data Guard Broker is the management and monitoring tool used for managing the primary and standby databases. It handles the monitoring tasks through the SQL command-line interfaces.

The major functions that it can perform would include

• Creating and enabling data guard configurations. This includes configuring the redo transport and application services.
• Managing the entire Data Guard configuration from any system within the configuration.
• Simplifying the switchover and failover through the easy clicks.

There are three basic methods that can be used for the purpose of Data Guard management – SQL Plus, Data Guard Broker and Enterprise Manager.

SQL Plus involves the traditional method and can prove to be quite tedious to use. The Data Guard Broker is offers you an automated option for creating, maintaining and monitoring of your data guard configuration. It provides you access to several simplified and automated options for most of the tasks. It comes with its own command line and syntax.

Enterprise Manager is what would offer you a graphical interface. It needs you to have the Data Guard Broker.

The SRL files are one of the best options for use with a better redo transport performance and data protection. If you are using Maximum availability and Maximum protection mode, you should use the SRL files. It may not be needed for the Maximum performance mode. However, Oracle recommends using it.

You can check the following video if you want to know how to create/check standby redo logs in oracle 11g RAC:

The Data Guard architecture includes the following –

• Primary Database – This would refer to the production database.
• Standby Database – This is a copy of the primary or production database. You can have more than one standby database.

• Log Transport Service – It helps you manage the transfer of archive files between primary and standby database.
• Network configuration – This is the network connection between the primary and standby database.
• Log Apply services – This is used to apply the archived logs to the standby database.
• Role management services – It manages the role change between primary and standby databases.
• Data Guard Broker – It helps you manage the data guard creation and process and monitors the data guard.

Data Guard has implemented a host of improvements in version 11g. Some of them would include

• 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

These were a few best questions we could sort for providing you with a list of data guard interview questions and answers in Oracle 11g.