What are Important Parameter Which Is Used For configuring data guard or standby Database?
What are the different types of redo transport services in dataguard?SYNC and ASYNC?
SYNC(SYNCHRONOUS):This mode is used for maximum protection and maximum availability protection mode. The synchronous redo transport mode transmits redo data synchronously with respect to transaction commitment. A transaction cannot commit until all redo generated by that transaction has been successfully sent to every standby 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.
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”.
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
What Is The Difference Between Sync/async, Lgwr/arch, And Affirm/noaffirm ?
Specifies that network I/O is to be done synchronously (SYNC) or asynchronously (ASYNC) when archival is performed using the log writer process (LGWR).
Specifies whether redo transport services use archiver processes (ARCn) or the log writer process (LGWR) to collect transaction redo data and transmit it to standby destinations. If neither the ARCH or LGWR attributes are specified, the default is ARCH.
Controls whether redo transport services use synchronous or asynchronous I/O to write redo data to disk
AFFIRM—specifies that all disk I/O to archived redo log files and standby redo log files is performed synchronously and completes successfully before the log writer process continues.
NOAFFIRM—specifies that all disk I/O to archived redo log files and standby redo log files is performed asynchronously; the log writer process on the primary database does not wait until the disk I/O completes before continuing.
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 are the different services available in Oracle Data Guard?
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 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.
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.
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 are the important database parameters related to Data Guard corruption prevention?
On the primary database:
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:
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.
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 Fast Start Fail Over (FSFO)?
Main criticism of Oracle standby databases has always been that too much manual interaction is required in case of disaster situation. FSFO helps in filling up this requirement. FSFO quickly and reliably fails over the target standby database to the primary database role, without requiring you to perform any manual steps to invoke the failover. Please keep in mind that you need to have Broker configuration done to be able to use FSFO feature.
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:
Transmit redo data from the primary system to the standby systems in the configuration.
Manage the process of resolving any gaps in the archived redo log files due to a network failure.
Automatically detect missing or corrupted archived redo log files on a standby system and automatically retrieve replacement archived redo log files from the primary database or another standby database.
What Is RTA (real Time Apply) Mode Mrp?
real-time apply where before log shipping the LGWR process writes to a standbylog file simultaneously along with the online redolog file.
This standby logfile is written to standby log file on standby server. There is no loss of any committed transaction whatsoever in Real-Time Apply scenario.
In Real Time Apply, once a transaction is committed on the Primary, the committed changes will be available on the Standby in Real Time even without switching the log at the Primary
MRP – Managed recovery process – For Data Guard, the background process that applies archived redo log to the standby database.
What Is Dg Broker?
DG Broker “it is the management and monitoring tool”.
Oracle dataguard broker is a distributed management framework that automates and centralizes the creation , maintenance and monitoring of DG configuration.
All management operations can be performed either through OEM, which uses the broker (or) broker specified command-line tool interface “DGMGRL”.