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 redolog.
Without Standby Redo Logs, an Archived Redo Log is created by the RFS process and when it has completed,
this Archived Redo Log is applied to the Standby Database by the MRP process . An incomplete ArchiveLog file cannot be applied on the Standby Database and will not be used in a Failover situation. This causes a certain data loss.
If you have Standby Redo Logs, the RFS process will write into the Standby Redo Log ,when there record entered in online redolog and when a log switch occurs, the Archiver Process of the Standby Database will archive this Standby Redo Log to an Archived Redo Log, while the MRP process applies the information to the Standby Database. In a Failover situation, you will also have access to the information already written in the Standby Redo Logs, so the information will not be lost.
2. What are 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 primarya will get shutdown.
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.
3. 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.
4. 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 chnaged 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.
5. 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
6. 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.
fal_server = ‘primdb’
fal_client = ‘stdbydb’
7. What are different types of dataguard and their difference.
There are two types of dataguard setups. PHYSICAL and LOGICAL.
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
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.
8. What is standby_file_management parameter oracle.
9. 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
10. 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.
11.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.
12. What are the different types of redo transport services in dataguard.
SYNC and ASYNC.
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.
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.
13. What is active dataguard?
14. What is dataguard broker and how it is useful?
15. What is dataguard broker and how it is useful?