Dataguard interview questions

Data guard Interview Questions and Scenarios
=====================================

ORACLE DATA GUARD INTERVIEW QUESTION - ANSWER

What is data guard?

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.

What are the advantages in using Oracle Data Guard?

Following are the different benefits in using Oracle Data Guard feature in your environment.

· High Availability.

· Data Protection.

· Off-loading Backup operation to standby database.

· Automatic Gap detection and Resolution in standby database.

· Automatic Role Transition using Data Guard Broker.

What are the Protection Modes in Dataguard?

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.

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 Protection

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.

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

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 RFS process are directly applied to the standby database by using media recovery.so here the apply process read 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, so 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.

Explain the Dataguard Architecture

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

What are the Steps to create Physical Standby database?

1.Take a full hot backup of Primary database

2. Enable force logging to the database

3. Prepare parameter file for primary database

4. Enable archiving

5.Create standby control file

6.Transfer full backup, init.ora, standby control file to standby node.

7.Modify init.ora file on standby node.

8.Restore database

9.Recover Standby database

10.Put Standby database in Managed Recover mode

What are the DATAGUARD PARAMETERS in Oracle?

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.

DB_NAME=chicago
DB_UNIQUE_NAME=chicago
LOG_ARCHIVE_CONFIG='DG_CONFIG=(chicago,boston)'
CONTROL_FILES='/arch1/chicago/control1.ctl', '/arch2/chicago/control2.ctl'
LOG_ARCHIVE_DEST_1=
'LOCATION=/arch1/chicago/
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=chicago'
LOG_ARCHIVE_DEST_2=
'SERVICE=boston LGWR ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=boston'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30

Primary Database: Standby Role Initialization Parameters

FAL_SERVER=boston
FAL_CLIENT=chicago
DB_FILE_NAME_CONVERT='boston','chicago'
LOG_FILE_NAME_CONVERT= '/arch1/boston/','/arch1/chicago/','/arch2/boston/','/arch2/chicago/'
STANDBY_FILE_MANAGEMENT=AUTO

Prepare an Initialization Parameter File for the Standby Database
-----------------------------------------------------------------
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:
CREATE PFILE='/tmp/initboston.ora' FROM SPFILE;

Modifying Initialization Parameters for a Physical Standby Database.

DB_NAME=chicago
DB_UNIQUE_NAME=boston
LOG_ARCHIVE_CONFIG='DG_CONFIG=(chicago,boston)'
CONTROL_FILES='/arch1/boston/control1.ctl', '/arch2/boston/control2.ctl'
DB_FILE_NAME_CONVERT='chicago','boston'
LOG_FILE_NAME_CONVERT= '/arch1/chicago/','/arch1/boston/','/arch2/chicago/','/arch2/boston/'
LOG_ARCHIVE_FORMAT=log%t_%s_%r.arc
LOG_ARCHIVE_DEST_1= 'LOCATION=/arch1/boston/
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=boston'
LOG_ARCHIVE_DEST_2= 'SERVICE=chicago LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=chicago'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
STANDBY_FILE_MANAGEMENT=AUTO
FAL_SERVER=chicago
FAL_CLIENT=boston

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

The services required on the primary database are:

• Log Writer Process (LGWR) - Collects redo information and updates the online redo logs. It can also create local archived redo logs and transmit online redo to standby databases.

• Archiver Process (ARCn) - One or more archiver processes make copies of online redo logs either locally or remotely for standby databases.

• Fetch Archive Log (FAL) Server - Services requests for archive redo logs from FAL clients running on multiple standby databases. Multiple FAL servers can be run on a primary database, one for each FAL request. .

The services required on the standby database are:

• Fetch Archive Log (FAL) Client - Pulls archived redo log files from the primary site. Initiates transfer of archived redo logs when it detects a gap sequence.

• Remote File Server (RFS) - Receives archived and/or standby redo logs from the primary database.

• Archiver (ARCn) Processes - Archives the standby redo logs applied by the managed recovery process (MRP).

• Managed Recovery Process (MRP) - Applies archive redo log information to the standby database.

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

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

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

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

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

What is a 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 it’s 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.

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.

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

A standby database automatically applies redo logs when they arrive from the primary database. But in some cases, we want to create a time lag between the archiving of a redo log at the primary site, and the application of the log at the standby site.

Modify the LOG_ARCHIVE_DEST_n initialization parameter on the primary database to set a delay for the standby database.

Example: For 60min Delay:

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=stdby_srvc DELAY=60';

The DELAY attribute is expressed in minutes.

The archived redo logs are still automatically copied from the primary site to the standby site, but the logs are not immediately applied to the standby database. The logs are applied when the specified time interval expires.

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

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

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

LOG_FILE_NAME_CONVERT parameter is used in Oracle Data Guard setup that to in standby databases. LOG_FILE_NAME_CONVERT parameter are used to update the location of redo log files in standby database. These parameter are used when you are using different directory structure in standby database compare to primary database redo log file location.

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

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

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

Synchronous transport (SYNC)

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

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

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

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 betweeen primary and standby databases as network RTT increases with distance.

What is Data Guard’s Automatic Gap Resolution?

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

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

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

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

For physical standby database, 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 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 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 dataguard.

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

On the primary database:

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 detect corruptions on the primary database and protect 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.

What is Switchover event?

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

What is Failover event?

The Failover process is similar to switchover event 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
Administrator 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.

Which tools can be used for Data Guard Management?

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.

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

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.

What are Standby Redo Log (SRL) files?

The SRL files are where the Remote File Server (RFS) process at your standby database writes the incoming redo so that it is persistent on disk for recovery. SRL files are important for better redo transport performance and data protection.

SRL are MUST in Maximum Availability or Maximum Protection mode and OPTIONAL (but recommended) in Maximum Performance mode.

If there are no Standby Redo Log (SRL) files, then at each log switch in the primary database, the RFS process on the standby database that is serving an asynchronous standby destination has to create an archive log of the right size. While the RFS is busy doing creating the archive log file, the LNS process at the primary database has to wait, getting further and further behind the LGWR (in case of Maximum Performance mode). That is why it recommended to have Standby Redo Log (SRL) files in Maximum Performance mode also.

We generally configure them on our primary database as well in preparation for a role transition b/w primary-standby.

Also, do not multiplex SRLs. Since Data Guard will immediately request a new copy of the archive log if an SRL file fails, there is no real need to have more than one copy of each.

1. 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 standby redolog on standby database.

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 whenever a record is inserted into oracle redolog , the RFS process will write into the Standby Redo Log of standby redolog and it gets applied by MRP , which ensures real time sync. 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.

Oracle recommends to keep standby redologs on both primary and standby database.(Though SLR is not mandatory for primary, it will make the switchover quick without any additional DBA activity. i.e If you don’t standby redologs on primary, whenever we do switchover, we need to create the SLR .

2. How many standby logs need to be created? What should be the size?

It is recommended to create one additional standby log group per thread . And its size should be same as that of primary redo log.

Suppose, we have 4 number of redo log groups of 200M on primary , Then we need to create 5 standby redo logs of 200M.

Why extra:

During heavy dml activity on primary, this extra standby log will help as a buffer in coping with the sync up.

Please note, Oracle recommends not to multiplex the standby redologs on , As it puts unnecessary i/o overhead during log shipping.

3. Explain different protection modes in dataguard?

There are three modes .

1. MAXIMUM PROTECTION
This mode provides maximum protection. It guarantees zero data loss. In this mode the redo/transaction data must be written to both primary redo log and standby redo log. For any reason(mostly N/W issue) if it is unable to write to standby, Then primary database will get shutdown. i.e if a user commits transaction, then that commit will not be be completed, unless primary receives a confirmation that write is completed on standby.

2. MAXIMUM AVAILABILITY:
It provides the highest level of data protection that is possible without affecting the availability of the primary database.Transactions are not allowed to commit until all redo data are written to the online redo logs and propagated to at least one synchronized secondary database. If for any reason, the primary database cannot propagate its redo stream to one secondary database, the primary will NOT shutdown and operates as it it were in maximum performance mode until issues are fixed.

3. MAXIMUM PERFORMANCE:(default one)
This is the default protection mode. With this protection mode, a transaction is committed as soon as the redo data needed to recover the transaction is written to the local (online) redo log.

4. What is snapshot standby database.

Snapshot standby is a feature in Oracle 11g that allows doing a read-write operation on the standby database i. e
we can convert the physical standby database to snapshot standby for testing purpose. On that, we can do all types of testing (BOTH READ/WRITE) or can be used as a development database (which is an exact replication of production ). Once the testing is over we can again convert
the snapshot database to physical standby. Once it is converted physical standby database, whatever changes were done to the snapshot standby will be reverted.
5. Support my dataguard setups is having one primary and standby and it is in maximum protection mode. Can i convert the standby to snapshot standby database?

No we cannot covert the standby database to snapshot in maximum protection, when this is the only standby db. Because in maximum protection mode , transaction data must be written to standby redo , before proceeding further.

6. What is the difference between switchover and failover:

A switchover means just switching roles between the primary database and standby db.
nswitchover, the primary database changed to a standby role, and the standby database changed to the primary role.
This is typically done for planned maintenance of the primary db server.

A failover is when the primary database fails and one of the standby databases is transitioned to take over the primary role. Failover is performed only in the event of a catastrophic failure of the primary database, and there is no possibility of recovering the primary database in a timely manner. Failover may or may not result in data loss depending on the protection mode in effect at the time of the failover.

7. What is the process to apply a psu patch in dataguard setup.

· Make sure lag between primary and standby is zero.

· Cancel the recovery (MRP) on standby.

· Shutdown standby db and listener.

· Apply patch to the standby database oracle home binary using opatch apply command.

· Once patch applied to binary , startup the listener and standby in mount stage or OPEN(if active dataguard).

· Now shutdown primary db and listener.

· Apply patch to primary database home binary using opatch apply command.

· Once patch applied to binary , startup open the primary database and listener

· Start the MRP recovery process on standby .

· Run post patching script catbundle.sql(if 11g) or datapatch -verbose command( if 12c) on primary database

For exact steps – How to apply patch in standby database

8. What is fal_client and fal_server parameter:

FAL Means – Fetch Archive log. FAL_CLIENT and FAL_SERVER parameters are used on standby database for archive gap resolution.

FAL_SERVER and FAL_CLIENT parameters are required on standby database only .

FAL_SERVER: Specify an Oracle Net Service Name (TNS-Alias or Connect Descriptor) that points to the Database from where the missing ArchiveLog(s) should be requested i.e the tns alias of the primary db.

FAL_CLIENT: Specify an Oracle Net Service Name (TNS-Alias or Connect Descriptor) that points from FAL_SERVER TO standby db. i.e the tns alias of the standby db. This parameter has been deprecated.

fal_server = ‘primdb’
fal_client = ‘stdbydb’

9. What are different types of dataguard and their difference.

There are three types of dataguard setups. PHYSICAL and LOGICA and SNAPSHOT.

PHYSICAL STANDBY:

A physical standby database is an exact, block-for-block copy of a primary database. A physical standby is
maintained as an exact copy through a process called REDO APPLY, in which redo data received from a
primary database is continuously applied to a physical standby database using the database recovery mechanisms. So it will be always in sync with primary.

This Standby database can be opened in read only mode( knows as ACTIVE DATA GUARD), for reporting purpose. Most of the corporations use physical standby for dataguard
configuration.

Physical standby database is suitable for high availability and disaster recovery

LOGICAL STANDBY:

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. So it contains same logical information as that of production , but physical structure of data can be different.

Logical standby is suitable for reporting purpose, Where we can do read,write operations. ( But we should not modify the standby objects, that exists on primary). This also helps in near zero down time database upgrade.

SNAPSHOT STANDBY:

10. What is standby_file_management parameter oracle.

Standby_file_management parameter can be have two values. MANUAL or AUTO(Recommended).

AUTO – > Datafiles created on primary will be create automatically on standby .

MANUAL – > Datafiles created on primary , will not be replicated to standby. The datafile need to be created manually on Standby

11. With standby_file_management set to AUTO, If I rename a datafile in primary, will the changes be propagated to standby .

No, for renaming a data file, the rename command need to be run on standby database as well .

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

13. Lets say, few of archive logs are missing from primary db server, before it is shipped to standby. In this case, How you will resolve the gap and keep the standby in sync with that of primary??

Recover the standby database by taking an incremental backup of primary db (using scn of standby db) . Refer the below link for complete article.

How to recover standby database when archive logs are missing in primary

14.The support DBA , added a tempfile on primary database, but the tempfile is not reflecting on standby database despite, the standby_file_management is set to AUTO.

Adding tempfiles to TEMP tablespaces in primary database, will not automatically create on standby database. Because no redo is generated, while adding tempfile. So DBA have to add the temp file manually.

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

1 . ARCH transportation mode:

In this mode, logs are sent by ARCH process. LNS process is not active here. Also standby redologs are not mandatory. i.e Real time APPLY will not happen here. In this mode. Archive logs will be copied to standby server and will be applied there.

2. SYNC and ASYNC transportation mode:( real time apply)

In below cases, standby redologs will be required. Redo is read and sent from redo log buffer to standby by LNS process.

If redo logs buffer is flushed to online redolog , before redo being sent to standby, then LNS will read the online redo log files and sent to standby.

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.

16. Which parameter defines the attributes of redo log transport service.

log_archive_dest_2(n) – > This parameter defines the service name of the standby database and whether this is SYNC or ASYNC mode and the attributes like AFFIRM/NOAFFIRM.

Apart From that , there are additional attributes like ,

17. What are the different types of Apply services in dataguard.

REDO APPLY:

With redo apply, apply services waits for a standby redo log file to be archived before applying the redo to standby.

REAL TIME APPLY:

If the real-time apply feature is enabled, then apply services can apply redo data ( either from redo log buffer or redo log file)as it is received, without waiting for the current standby redo log file to be archived.

18. What is fast start failover(FSFO)?

Fast-Start Failover is a feature that allows the Oracle Data Guard broker to failover a failed primary database automatically to a predetermined standby database.

Observer process which runs on different server than that of primary and standby , continuously checks the availability of primary database. If both observer and standby database both are unable to connect with primary database for a pre defined time, Then failover will be initiated automatically. The FastStartFailoverThreshold paramter defines the time limit for failover.

And post failover, If observer is able to establish the connection with old primary, then it will reinstate the database using flashback.

Prerequisites for FSFO:

· Flashback should be enabled on both primary and standby database.

· Observer need to present on a server different from primary and standby .

NOTE – We can run observer on either primary or standby . But it is always recommended to run observer on a different server.

Because , Lets say, you are running observer on primary and if primary server goes does, then observer cannot take any action , it is also not available.

19. Let’s say, Fast start failover is configured in the dataguard setup. And the DBA shutdown the primary database using Shutdown immediate, Then will failover happen?

No. failover will not initiate if shutdown immediate or shutdown normal issued on primary by user action.

But if shutdown abort is issued, then automatic failover will be initiated.

20. What will be the impact on standby setup , when observer is down?

If the observer is down, then there will be no impact on the dataguard setup. But FSFO i.e automatic failover will not happen if primary becomes inaccessible.

21. What is a far sync instance in dataguard?

T Far sync instance is an proxy/dummy instance, which receives redos from primary in sync mode and ships them to one or multiple standby databases (upto 30 members) in async mode.

It has only standby control file. It doesn’t have any user data file. And the instance will be always in mount state. This feature need s active data guard license.

Far sync instance is usually useful, when primary and standby databases are far away.Far sync instances stay close to primary database and manages the near real time apply data.

22. What are the new features of oracle 19c /20c/21c dataguard?

Automatic flashback of standby db( in. 19c) – If you flashback the primary database, then standby database will also be flashed back automatically.

Restore Point Replication( in. 19c) – If we create a restore on primary , then same restore point will be created on the standby database.

Active Dataguard DML Redirection(ADR) – If you run a DML statement on standby , then it will be redirected to primary and the requested data will be send to standby again.

Database Buffer cache status maintained(18c)– Database buffer cache status will be maintained after role transition on standby also. Users on the standby will be able to continue exactly where they left off after a role change (switchover of failover) with the same performance .

23. What is dataguard broker and how it is useful?

Dgbroker is used for easy management and administration of one or multiple standby databases. It helps in simple switchover, failover, FSFO, role transitions etc.

DMON process run when dataguard broker is enabled.

dgmgrl utility is used for administration of dgbroker.

Whenever we issue any command from dgmgrl prompt, dmon process will process the request on primary and coordinate the same all standby databases. And it will update the configurations if required.

24. How the apply process in ORACLE RAC dataguard works?

Prior to 12.2, The MRP ( recovery process) can be started only on one node . However from 12.2 onwards, MRP can started on multiple or all nodes.

Below is the command:

-- Start MRP from all the instances available:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION INSTANCES ALL;

— Start MRP on 2 Instances:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION INSTANCES 2;

25. Why we need to enable force logging on primary database, before setting up standby.

If somone does nologging operations on primary, then these data will be missed on standby, which will make the standby inconsistent with primary. So to ensure all the transaction on primary should generate redolog , force logging is enabled.

26. Which additional parameters we add in primary for configuring standby database.

log_archive_config: –

db_unique_name

standby_file_management=auto

log_archive_dest_2 – tns details of standby database, to which logs will be shipped

log_archive_dest_state_2

db_file_name_convert – for mapping the database files of primary and standby db ( when directory structures are different on primary and standby)

log_file_name_convert – For mapping redologs of primary and standby

fal_server – > This defines from where the standby database should request the missing archive logs. i.e on standby database its values will be the tns service_name of primary .

27. Will the db_name and db_unique_name will same for primary and standby databases?

DB_NAME will be same for primary and its standby databases. But everyone will have a different db_unique_name.

28. Except snapshot standby method, is there any way we can open the standby database in read write mode for testing and revert it back once testing is done.

Flashback method can be used.

29. For security reason, you are not allowed to use sys user for redo transport service. Is there any alternative way or we need use sys user only.

If we want to use an user other than SYS, then we can create a user with sysoper privilege and set the parameter REDO_TRANSPORT_USER to that username.

30. Explain the standby database /dataguard related background process.

MRP – > Managed recovery process, which is responsible for read and redo and apply , through multiple parallel process like Pr(n) process.

RFS – > Remote file service.

LNS – > Log network service ( From 12c LNS is replaced with NSS( FOR SYNC) and NSA( For ASYNC).

DMON – > DG BROKER monitor

FSFP – > Fast start failover process.

31. How oracle detects gaps and resolves it?

Two methods are there.

Automatic gap resolution:

This is done by log transport service. When there is a mismatch between currently transfered redo with that of last received log in standby, then RFS will request the missing log sequences from primary via arch-rfs hearbeat ping.

No special setting is required.

Fetch archive log( FAL Method):

FAL_SERVER – specifies the tns service database from where the missing archive logs need to be fetched.

When a archive is shipped to standby , it gets registered in the standby controlfile. When log apply service detects a gap , it sends a request to fal server to resend the missing logs.

32. We did failover, and the standby became the new primary. Now we need to make the old primary as new standby. But the database is very high , So rebuilding it will take time. Is there any way to fix it?

There is a way, But only if flashback is enabled. If flashback is enabled , then we can get the current scn from the new primary database and flashback the old primary(new standby) upto that scn.

33. Can we convert the physical standby database to logical standby database.

Yes it is possible to convert physical to logical standby.

34. Can we enable tracing in dataguard .

Yes we can set LOG_ARCHIVE_TRACE parameter to trace redo transport and apply services on primary and standby.

possible values are 0(default means no tracing) , 1,2,4,8,16,32,64 …. 8192

35. Someone configured the data guard setup, without enabling force logging on primary. And in primary few no logging operations happened . So getting error like data block was loading using NOLOGGINE option. How can We fix it?

In that case, we can get FIRST_NONLOGGED_SCN value from v$datafile on standby database. Then we can take an incremental backup using the SCN from Primary. and apply the same on standby.

36. Explain the physical standby architecture . How standby gets synced with primary?

37. What is AFFIRM/NOAFFIRM in dataguard?

38. What are the common issues you face in dataguard environments?

1. Replication issues due to network issues

2. Archive log Missing errors

3. If a datafile is renamed on primary, then also error will come in standby.

4. Issue may occur if someone mistakenly changes the dataguard related parameters like log_archive_config, log_archive_dest parameters.

39. What is FastSync?

Fast Sync provides an easy way of improving performance in synchronous zero data loss configurations. Fast Sync allows a standby to acknowledge the primary database as soon as it receives redo in memory, without waiting for disk I/O to a standby redo log file (SYNC NOAFFIRM). This reduces the impact of synchronous transport on primary database performance by shortening the total round-trip time between primary and standby.

40. What is Multi instance Redo Apply? How can we enable this?

Prior to 12.2 , If your dataguard is RAC, then MRP process can be run only on one node. However From 12.2 Onward, We can enable MIRA( Multi instance Redo apply), i.e MRP can be run on multiple instances or all the nodes.

Below are the syntax:

-- Start MRP from all the instances available:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION INSTANCES ALL;

-- Start MRP on 2 Instances:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION INSTANCES 2;

41. If there is any block corruption on standby database, How can we recover that?

--- First check on standby which blocks got corrupted.

SQL> Select * from v$database_block_corruption ;

----Cancel the Recovery:

alter database Recover managed standby database Cancel;

--Restore the datafile,

SQL> Restore datafile 9 FORCE from service 'PRIM_DB' ;

-- Start MRP:

SQL> alter database Recover managed standby database using current logfile disconnect from session;

42. Can i generate awr report in a standby database? If yes how?

43. Can i run sql tuning advisor in standby database?

Yes we can do that , using the concept of db_link.

in dbms_sqltune module, there is a parameter database_link_to which we can se to a database_link(db_link with connect SYS$UMF , pointing to primary). and then we can run the task on standby.

Reference – https://fatdba.com/2022/01/15/part-1-running-sql-tuning-advisor-for-a-slow-sql-in-a-read-only-standby-database/#:~:text=You%20can%20issue%20SQL%20Tuning,write%20the%20SQL%20tuning%20data.

44. We are having dataguard setup in maximum protection mode , and we created one datafile in a new diskgroup on primary, But that diskgroup was not present in standby? What will be the outcome?

the primary will shutdown .

45. Can i set sync,, AFFIRM with ARCH in log_archive_dest_n paramter?

46.Which process on standby receive the changes from primary?

47. Can i run expdp command on standby?

Yes we can run expdp on standby database by createing db_link and using network_link parameter in expdp.

How to run expdp on physical standby database

48. Can I take full backup on standby and incremental backup on primary?

Yes we can do that.

49. Is there any prerequisite for converting physical standby to snapshot standby database?

You need to set db_recovery_file_dest and db_recovery_file_dest_size parameter on standby and then enable flashback.

50. Some one mistakenly truncated a table on primary database. We already have a dataguard setup. Is there any way we can recover that table?

Below are the steps.

51. If observer is unable to connect with primary , but it can connect with standby, then what will happen?

If the observer is unable to connect to primary, then it will check the status of primary, through standby.ing: Shrinking and plani...

4. How many standby databases we can create?
Answer :
Till Oracle 10g, 9 standby databases are supported.From Oracle 11g R2, we can create 30 standby databases

Play

Unmute

Loaded: 24.38%

Remaining Time -5:54

Captions

Fullscreen

Play Video

4. What is snapshot standby database.

Snapshot standby is a feature in Oracle 11g that allows doing a read-write operation on the standby database i. e
we can convert the physical standby database to snapshot standby for testing purpose. On that, we can do all types of testing (BOTH READ/WRITE) or can be used as a development database (which is an exact replication of production ). Once the testing is over we can again convert
the snapshot database to physical standby. Once it is converted physical standby database, whatever changes were done to the snapshot standby will be reverted.

5. Support my dataguard setups is having one primary and standby and it is in maximum protection mode. Can i convert the standby to snapshot standby database?

6. What is the difference between switchover and failover:

7. What is the process to apply a psu patch in dataguard setup.

· Make sure lag between primary and standby is zero.

· Cancel the recovery (MRP) on standby.

· Shutdown standby db and listener.

· Apply patch to the standby database oracle home binary using opatch apply command.

· Once patch applied to binary , startup the listener and standby in mount stage or OPEN(if active dataguard).

· Now shutdown primary db and listener.

· Apply patch to primary database home binary using opatch apply command.

· Once patch applied to binary , startup open the primary database and listener

· Start the MRP recovery process on standby .

· Run post patching script catbundle.sql(if 11g) or datapatch -verbose command( if 12c) on primary database

For exact steps – How to apply patch in standby database

8. What is fal_client and fal_server parameter:

FAL Means – Fetch Archive log. FAL_CLIENT and FAL_SERVER parameters are used on standby database for archive gap resolution.

FAL_SERVER and FAL_CLIENT parameters are required on standby database only .

fal_server = ‘primdb’
fal_client = ‘stdbydb’

9. What are different types of dataguard and their difference.

There are three types of dataguard setups. PHYSICAL and LOGICA and SNAPSHOT.

PHYSICAL STANDBY:

This Standby database can be opened in read only mode( knows as ACTIVE DATA GUARD), for reporting purpose. Most of the corporations use physical standby for dataguard
configuration.

Physical standby database is suitable for high availability and disaster recovery

LOGICAL STANDBY:

SNAPSHOT STANDBY:

10. What is standby_file_management parameter oracle.

Standby_file_management parameter can be have two values. MANUAL or AUTO(Recommended).

AUTO – > Datafiles created on primary will be create automatically on standby .

MANUAL – > Datafiles created on primary , will not be replicated to standby. The datafile need to be created manually on Standby

11. With standby_file_management set to AUTO, If I rename a datafile in primary, will the changes be propagated to standby .

No, for renaming a data file, the rename command need to be run on standby database as well .

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

13. Lets say, few of archive logs are missing from primary db server, before it is shipped to standby. In this case, How you will resolve the gap and keep the standby in sync with that of primary??

Recover the standby database by taking an incremental backup of primary db (using scn of standby db) . Refer the below link for complete article.

How to recover standby database when archive logs are missing in primary

14.The support DBA , added a tempfile on primary database, but the tempfile is not reflecting on standby database despite, the standby_file_management is set to AUTO.

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

1 . ARCH transportation mode:

2. SYNC and ASYNC transportation mode:( real time apply)

In below cases, standby redologs will be required. Redo is read and sent from redo log buffer to standby by LNS process.

If redo logs buffer is flushed to online redolog , before redo being sent to standby, then LNS will read the online redo log files and sent to standby.

SYNC(SYNCHRONOUS):

ASYNC(ASYNCHRONOUS):

16. Which parameter defines the attributes of redo log transport service.

log_archive_dest_2(n) – > This parameter defines the service name of the standby database and whether this is SYNC or ASYNC mode and the attributes like AFFIRM/NOAFFIRM.

Apart From that , there are additional attributes like ,

17. What are the different types of Apply services in dataguard.

REDO APPLY:

With redo apply, apply services waits for a standby redo log file to be archived before applying the redo to standby.

REAL TIME APPLY:

18. What is fast start failover(FSFO)?

Fast-Start Failover is a feature that allows the Oracle Data Guard broker to failover a failed primary database automatically to a predetermined standby database.

And post failover, If observer is able to establish the connection with old primary, then it will reinstate the database using flashback.

Prerequisites for FSFO:

· Flashback should be enabled on both primary and standby database.

· Observer need to present on a server different from primary and standby.

NOTE – We can run observer on either primary or standby. But it is always recommended to run observer on a different server.

Because, Lets say, you are running observer on primary and if primary server goes does, then observer cannot take any action, it is also not available.

19. Let’s say, Fast start failover is configured in the dataguard setup. And the DBA shutdown the primary database using Shutdown immediate, Then will failover happen?

No. failover will not initiate if shutdown immediate or shutdown normal issued on primary by user action.

But if shutdown abort is issued, then automatic failover will be initiated.

20. What will be the impact on standby setup , when observer is down?

If the observer is down, then there will be no impact on the dataguard setup. But FSFO i.e automatic failover will not happen if primary becomes inaccessible.

21. What is a far sync instance in dataguard?

T Far sync instance is an proxy/dummy instance, which receives redos from primary in sync mode and ships them to one or multiple standby databases (upto 30 members) in async mode.

It has only standby control file. It doesn’t have any user data file. And the instance will be always in mount state. This feature need s active data guard license.

Far sync instance is usually useful, when primary and standby databases are far away.Far sync instances stay close to primary database and manages the near real time apply data.

22. What are the new features of oracle 19c /20c/21c dataguard?

Automatic flashback of standby db( in. 19c) – If you flashback the primary database, then standby database will also be flashed back automatically.

Restore Point Replication( in. 19c) – If we create a restore on primary , then same restore point will be created on the standby database.

Active Dataguard DML Redirection(ADR) – If you run a DML statement on standby , then it will be redirected to primary and the requested data will be send to standby again.

23. What is dataguard broker and how it is useful?

Dgbroker is used for easy management and administration of one or multiple standby databases. It helps in simple switchover, failover, FSFO, role transitions etc.

DMON process run when dataguard broker is enabled.

dgmgrl utility is used for administration of dgbroker.

Whenever we issue any command from dgmgrl prompt, dmon process will process the request on primary and coordinate the same all standby databases. And it will update the configurations if required.

24. How the apply process in ORACLE RAC dataguard works?

Prior to 12.2, The MRP ( recovery process) can be started only on one node . However from 12.2 onwards, MRP can started on multiple or all nodes.

Below is the command:

--Start MRP from all the instances available:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION INSTANCES ALL;

— Start MRP on 2 Instances:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION INSTANCES 2;

25. Why we need to enable force logging on primary database, before setting up standby.

26. Which additional parameters we add in primary for configuring standby database.

log_archive_config: –

db_unique_name

standby_file_management=auto

log_archive_dest_2 – tns details of standby database, to which logs will be shipped

log_archive_dest_state_2

db_file_name_convert – for mapping the database files of primary and standby db ( when directory structures are different on primary and standby)

log_file_name_convert – For mapping redologs of primary and standby

fal_server – > This defines from where the standby database should request the missing archive logs. i.e on standby database its values will be the tns service_name of primary .

27. Will the db_name and db_unique_name will same for primary and standby databases?

DB_NAME will be same for primary and its standby databases. But everyone will have a different db_unique_name.

28. Except snapshot standby method, is there any way we can open the standby database in read write mode for testing and revert it back once testing is done.

Flashback method can be used.

29. For security reason, you are not allowed to use sys user for redo transport service. Is there any alternative way or we need use sys user only.

If we want to use an user other than SYS, then we can create a user with sysoper privilege and set the parameter REDO_TRANSPORT_USER to that username.

30. Explain the standby database /dataguard related background process.

MRP – > Managed recovery process, which is responsible for read and redo and apply , through multiple parallel process like Pr(n) process.

RFS – > Remote file service.

LNS – > Log network service ( From 12c LNS is replaced with NSS( FOR SYNC) and NSA( For ASYNC).

DMON – > DG BROKER monitor

FSFP – > Fast start failover process.

31. How oracle detects gaps and resolves it?

Two methods are there.

Automatic gap resolution:

No special setting is required.

Fetch archive log( FAL Method):

FAL_SERVER – specifies the tns service database from where the missing archive logs need to be fetched.

When a archive is shipped to standby , it gets registered in the standby controlfile. When log apply service detects a gap , it sends a request to fal server to resend the missing logs.

32. We did failover, and the standby became the new primary. Now we need to make the old primary as new standby. But the database is very high , So rebuilding it will take time. Is there any way to fix it?

There is a way, But only if flashback is enabled. If flashback is enabled , then we can get the current scn from the new primary database and flashback the old primary(new standby) upto that scn.

33. Can we convert the physical standby database to logical standby database.

Yes it is possible to convert physical to logical standby.

34. Can we enable tracing in dataguard .

Yes we can set LOG_ARCHIVE_TRACE parameter to trace redo transport and apply services on primary and standby.

possible values are 0(default means no tracing) , 1,2,4,8,16,32,64 …. 8192

35. Some one configured the dataguard setup, without enabling force_logging on primary. And in primary few nologging operations happened . So getting error like data block was loading using NOLOGGINE option. How can We fix it?

In that case, we can get FIRST_NONLOGGED_SCN value from v$datafile on standby database. Then we can take an incremental backup using the SCN from Primary. and apply the same on standby.

36. Explain the physical standby architecture . How standby gets synced with primary?

37. What is AFFIRM/NOAFFIRM in dataguard?

38. What are the common issues you face in dataguard environments?

1. Replication issues due to network issues

2. Archive log Missing errors

3. If a datafile is renamed on primary, then also error will come in standby.

4. Issue may occur if someone mistakenly changes the dataguard related parameters like log_archive_config, log_archive_dest parameters.

39. What is FastSync?

40. What is Multi instance Redo Apply? How can we enable this?

Below are the syntax:

-- Start MRP from all the instances available:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION INSTANCES ALL;

-- Start MRP on 2 Instances:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION INSTANCES 2;

41. If there is any block corruption on standby database, How can we recover that?

--- First check on standby which blocks got corrupted.

SQL> Select * from v$database_block_corruption ;

----Cancel the Recovery:

alter database Recover managed standby database Cancel;

--Restore the datafile,

SQL> Restore datafile 9 FORCE from service 'PRIM_DB' ;

-- Start MRP:

SQL> alter database Recover managed standby database using current logfile disconnect from session;

42. Can i generate awr report in a standby database? If yes how?

43. Can i run sql tuning advisor in standby database?

Yes we can do that , using the concept of db_link.

in dbms_sqltune module, there is a parameter database_link_to which we can se to a database_link(db_link with connect SYS$UMF , pointing to primary). and then we can run the task on standby.

44. We are having dataguard setup in maximum protection mode , and we created one datafile in a new diskgroup on primary, But that diskgroup was not present in standby? What will be the outcome?

the primary will shutdown .

45. Can i set sync,, AFFIRM with ARCH in log_archive_dest_n paramter?

46.Which process on standby receive the changes from primary?

47. Can i run expdp command on standby?

Yes we can run expdp on standby database by createing db_link and using network_link parameter in expdp.

How to run expdp on physical standby database

48. Can I take full backup on standby and incremental backup on primary?

Yes we can do that.

49. Is there any prerequisite for converting physical standby to snapshot standby database?

You need to set db_recovery_file_dest and db_recovery_file_dest_size parameter on standby and then enable flashback.

50. Some one mistakenly truncated a table on primary database. We already have a dataguard setup. Is there any way we can recover that table?

Below are the steps.

51. If observor is unable to connect with primary , but it can connect with standby, then what will happen?

If the observor is unable to connect to primary, then it will check the status of primary, through standby.

1. How to set up Data Guard?
Answer :

2. What are the advantages of using Oracle Data Guard?
Answer :

3. What are the different types of protection modes in Data Guard and which is the default?
Answer :
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.

4. How many standby databases we can create?
Answer :
Till Oracle 10g, 9 standby databases are supported.
From Oracle 11g R2, we can create 30 standby databases.

5. What are the parameters we have to set in primary/standby for Data Guard?
Answer :

6. What is the use of fal_server and fal_client, is it mandatory to set these?
Answer :

7. What are the differences between physical, logical, snapshot standby and ADG (or) what are different types of standby databases?
Answer :
Physical standby – in mount state, MRP will apply archives
ADG (Active Data Guard) – 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 all pending archives.

8. How to find out the backlog of standby?
Answer :
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);

9. If you didn't have access to the standby database and you want 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?
Answer :
You can check the v$dataguard_status view.
select message from v$dataguard_status;

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

11. 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?
Answer :
Till Oralce 10g, create a guaranteed restore point, open in read write, let the user do DMLs on snapshot standby, flashback to restore point, start MRP.
From Oracle 11g, convert physical standby to snapshot standby, let user do DMLs on snapshot standby, convert to physical standby, start MRP.

12. What were new features in 11g Data Guard?
Answer :

13. What are the uses of standby redo log files?
Answer :
A standby redo log resides on the standby database site. The standby redolog file is similar to an online redo log, except that a standby redo log is used to store redo data that has been received from another/primary database.

14. What is dg_config?
Answer :

15. What is RTA (real time apply) mode MRP? How will you identify if DB is running in real time apply mode or not?
Answer :

16. What is the difference between normal MRP (managed apply) and RTA MRP (real time apply)?
Answer :

17. What are various parameters in log_archive_dest and their use?
Answer :

18. I have added a tempfile on the primary database, but the tempfile is not reflecting on standby database (despite standby_file_management is set to AUTO), Why ?
Answer :

19. What is the difference between SYNC/ASYNC, LGWR/ARCH, and AFFIRM/NOAFFIRM?
Answer :

20. What is DataGuard broker (or) what is the use of dgmgrl?
Answer :

21. What is StaticConnectIdentifier property used for?
Answer :

22. What is failover/switchover (or) what is the difference between failover and switchover?
Answer :
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 (i.e. in a Disaster). It is not pre-planned.

23. What is the usage of DB_FILE_NAME_CONVERT or LOG_FILE_NAME_CONVERT parameters in Oracle Data Guard setup?
Answer :

24. What are the background processes involved in Data Guard?
Answer :
RFS, MRP, LSP

25. What are the different services available in Oracle Data Guard?
Answer :

26. How to change protection mode in Oracle Data Guard setup?
Answer :

27. How to check what protection mode of primary or standby database is in Oracle Data Guard?
Answer :

28. What is Active DataGuard (ADG). Does it need additional licensing?
Answer :

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

Q5. What is active dataguard. 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 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 data guard, you need additional license from oracle.

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

Q7. What are the different types of redo transport services in data guard?

Ans: SYNC and ASYNC.

SYNC(SYNCHRONOUS):

ASYNC(ASYNCHRONOUS):

Q9. What are the new feature for DG in 11g?

Ans: Active database duplicate, snapshot stand by,Active data guard,Redo compression

32 standby database configuration support (earlier only 9)

FAL_CLIENT not require

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

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

Q11. What is 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 apply AS SOON AS it receive from primary. But in normal apply mode : LOG APPLY service will wait for SRL to archive.

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

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

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

Ans: LGWR or ARCH.

Q14. What are the advantages of using 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.

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

Q16. What is 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.

Q17. What is difference between Physical stand by and snapshot standby?

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

Q18. What is 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.

Q19. What is active dataguard duplicate?

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

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