71. Does the Data Guard Broker
support Real-Time Apply ?
Real
Time apply is supported by the Broker.
In
Oracle 10.1.0.x there is a Property 'RealTimeApply' to toggle Real-Time Apply
In
Oracle 10.2.0.x+ Real-Time Apply is default when the Property 'DelayMins'=0
72. Are Standby Redo Logs
mandatory for Real-Time Apply ?
Standby
Redo log Files (SRL's) are required for Real-Time Apply because the Apply
Process can only apply from a Standby Redo Log rather than from a partial
filled Archive Log.
73. What will happen if Log apply Service cannot cope with the Real
Time Apply ?
If for
some Reason the Apply Service is unable to keep up with the Redo Rate then the
Apply Service it will automatically go to the Archive log Files as needed.
However,
the Apply Service will automatically resume Real-Time Apply (reading the
Standby Redo log Files) as soon as the Problem is resolved
74. How many standby databases we can create (in 10g/11g)?
Till Oracle 10g, 9 standby databases are supported.
From Oracle 11g R2, we can create 30 standby databases.
Till Oracle 10g, 9 standby databases are supported.
From Oracle 11g R2, we can create 30 standby databases.
75. What is the use of fal_server & fal_client, is it mandatory
to set these ?
FAL_SERVER
specifies the FAL (fetch archive log) server for a standby database. The value is an Oracle Net service name, which is assumed to be configured properly on the standby database system to point to the desired FAL server
FAL_CLIENT
specifies the FAL (fetch archive log) client name that is used by the FAL service, configured through the
FAL_SERVER initialization parameter, to refer to the FAL client.
The value is an Oracle Net service name, which is assumed to be configured properly on the FAL server system to point to the FAL client (standby database).
specifies the FAL (fetch archive log) server for a standby database. The value is an Oracle Net service name, which is assumed to be configured properly on the standby database system to point to the desired FAL server
FAL_CLIENT
specifies the FAL (fetch archive log) client name that is used by the FAL service, configured through the
FAL_SERVER initialization parameter, to refer to the FAL client.
The value is an Oracle Net service name, which is assumed to be configured properly on the FAL server system to point to the FAL client (standby database).
76. What are differences between physical, logical, snapshot standby
and Active DG , what are different types of standby databases?
Physical standby – in mount state, MRP will apply archives
ADG – in READ ONLY state, MRP will apply archives
Logical standby – in READ ONLY state, LSP will run
Physical standby – in mount state, MRP will apply archives
ADG – in READ ONLY state, MRP will apply archives
Logical standby – in READ ONLY state, LSP will run
Snapshot
standby databases – Physical standby database can be converted to snapshot
standby database, which will be in READ WRITE mode, can do any kind of testing,
then we can convert back snapshot standby database to physical standby database
and start MRP which will apply all pending archives.
77. How to find out backlog of standby?
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);
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);
78. How can u recover standby which far behind from primary or
without archive logs how can we make standby sync?
By
using RMAN incremental backup.
79. What are new features in 11g Data Guard?
Here
is some data guard category and their enhancement
1) Data Protection
Advanced Compression
Lost-write protection
Fast-Start Failover
1) Data Protection
Advanced Compression
Lost-write protection
Fast-Start Failover
2)
Increase ROI
Active Data Guard
Snapshot Standby
Active Data Guard
Snapshot Standby
3)
High Availability
Faster Redo Apply
Faster failover & switchover
Automatic Failover using ASYNC
4) Manageability
Mixed Windows/Linux
Faster Redo Apply
Faster failover & switchover
Automatic Failover using ASYNC
4) Manageability
Mixed Windows/Linux
80.
What are the uses of standby redo log files
A standby redo log is required for the maximum protection and maximum availability modes and the LGWR ASYNC transport mode is recommended for all databases.
You should plan the standby redo log configuration and create all required log groups and group members when you create the standby database. For increased availability, consider multiplexing the standby redo log files, similar to the way that online redo log files are multiplexed.
If the real-time apply feature is enabled, log apply services can apply redo data as it is received, without waiting for the current standby redo log file to be archived. This results in faster switchover and failover times because the standby redo log files have been applied already to the standby database by the time the failover or switchover begins
A standby redo log is required for the maximum protection and maximum availability modes and the LGWR ASYNC transport mode is recommended for all databases.
You should plan the standby redo log configuration and create all required log groups and group members when you create the standby database. For increased availability, consider multiplexing the standby redo log files, similar to the way that online redo log files are multiplexed.
If the real-time apply feature is enabled, log apply services can apply redo data as it is received, without waiting for the current standby redo log file to be archived. This results in faster switchover and failover times because the standby redo log files have been applied already to the standby database by the time the failover or switchover begins
81.
What is dg_config ?
Specify the DG_CONFIG attribute to identify the DB_UNIQUE_NAME for the primary database and each standby database in the Data Guard configuration. The default value of this parameter enables the primary database to send redo data to remote destinations and enables standby databases to receive redo data. The
DG_CONFIG attribute must be set to enable the dynamic addition of a standby database to a Data Guard configuration that has a Real Application Clusters primary database running in either maximum protection or maximum availability mode.
Specify the DG_CONFIG attribute to identify the DB_UNIQUE_NAME for the primary database and each standby database in the Data Guard configuration. The default value of this parameter enables the primary database to send redo data to remote destinations and enables standby databases to receive redo data. The
DG_CONFIG attribute must be set to enable the dynamic addition of a standby database to a Data Guard configuration that has a Real Application Clusters primary database running in either maximum protection or maximum availability mode.
82.
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.
83. What is the difference between normal MRP (managed apply) and RTA MRP (real time apply)?
The difference between Redo Apply & Real-Time Apply
------------------------------------------------------
Normally, by default, Archiver processes will be responsible for Redo Transport from Primary to Standby.
Once a log switch happens on the Primary, the online redo log is archived in the Local Archive destination as pointed to by Log_archive_dest_1
by an Archiver process.
Another Archiver process will then transmit the redo to the remote standby destination as indicated by Log_archive_dest_2.
Data Guard Remote File Server (RFS) Process on the Standby then writes redo data from the Standby redo log file to archive redo log file.
Log apply services then makes use of Managed Recovery Process (MRP) process to apply the redo to the standby database.
This method of propagating redo from the primary to standby is called Redo Apply and it happens only on log switch at the Primary.
When using Redo Apply mode, the status of MRP in v$managed_standby view will show as WAIT_FOR_LOG.
Real Time Apply, in contrast, uses either LGWR or Archiver on the Primary to write redo data to Standby Redo log on the Standby and Log Apply Services can apply the redo data in real-time without the need of the current standby redo log being archived. 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.
When using Real Time Apply mode, the status of MRP in v$managed_standby view will show as APPLYING_LOG.
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.
83. What is the difference between normal MRP (managed apply) and RTA MRP (real time apply)?
The difference between Redo Apply & Real-Time Apply
------------------------------------------------------
Normally, by default, Archiver processes will be responsible for Redo Transport from Primary to Standby.
Once a log switch happens on the Primary, the online redo log is archived in the Local Archive destination as pointed to by Log_archive_dest_1
by an Archiver process.
Another Archiver process will then transmit the redo to the remote standby destination as indicated by Log_archive_dest_2.
Data Guard Remote File Server (RFS) Process on the Standby then writes redo data from the Standby redo log file to archive redo log file.
Log apply services then makes use of Managed Recovery Process (MRP) process to apply the redo to the standby database.
This method of propagating redo from the primary to standby is called Redo Apply and it happens only on log switch at the Primary.
When using Redo Apply mode, the status of MRP in v$managed_standby view will show as WAIT_FOR_LOG.
Real Time Apply, in contrast, uses either LGWR or Archiver on the Primary to write redo data to Standby Redo log on the Standby and Log Apply Services can apply the redo data in real-time without the need of the current standby redo log being archived. 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.
When using Real Time Apply mode, the status of MRP in v$managed_standby view will show as APPLYING_LOG.
84.
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.
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.
85. What is Static ConnectIdentifier property used for?
11gr2 new database property, StaticConnectIdentifier, which allows the user to specify a static connect identifier that the DGMGRL client will use to start database instances.
86.
What is failover/switchover (or)
what is the difference between failover & switchover
Switchover – This is done when both primary and standby databases are available.
Failover – This is done when the primary database is NO longer available (i.e in a Disaster).
87. What are the background processes involved in Data Guard?
MRP, LSP,
Switchover – This is done when both primary and standby databases are available.
Failover – This is done when the primary database is NO longer available (i.e in a Disaster).
87. What are the background processes involved in Data Guard?
MRP, LSP,
88.
What are the advantages in using
Oracle Data Guard?
Following
are the different benefits in using Oracle Data Guard feature in your
environment.
1.
High
Availability.
2.
Data
Protection.
3.
Off-loading
Backup operation to standby database.
4.
Automatic
Gap detection and Resolution in standby database.
5.
Automatic
Role Transition using Data Guard Broker.
89.
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.
90. How to check what protection mode of primary
database in your Oracle Data Guard?
SELECT PROTECTION_MODE FROM V$DATABASE;
91. How to change protection mode in Oracle Data
Guard setup?
ALTER
DATABASE SET STANDBY DATABASE TO MAXIMUM [PROTECTION|PERFORMANCE|AVAILABILITY];
92. What are
the advantages of using Physical standby database in Oracle Data Guard?
High
Availability.
Load
balancing (Backup and Reporting).
Data
Protection.
Disaster
Recovery.
93.
What is the usage of
DB_FILE_NAME_CONVERT parameter in Oracle Data Guard setup?
DB_FILE_NAME_CONVERT This
parameter is used when you are using different directory structure in
standby database compare to primary database data files location & also
when we duplicating database this parameter can be used to generate files in a
different location.
94.
What are the advantages of
Logical standby database in Oracle Data Guard?
§
Better
usage of resource
§
Data
Protection
§
High
Availability
§
Disaster
Recovery
95.
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.
96. 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.
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.
97.
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.
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.
98.
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.
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.
99.
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.
Data Guard automatically resynchronizes the standby following network or standby outages using redo data that has been archived at the primary.
100. 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.
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.
101. 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
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.
102. 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.
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
103. 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.
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.
104. 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 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.
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.
105. 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.
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.
106. 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.
107. 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.
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.
108. 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.
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.
109. 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.
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.
No comments:
Post a Comment