Oracle Data Guard redo log transport offers synchronous log transport mode (LogXptMode = 'SYNC') or asynchronous log transport mode (LogXptMode = 'ASYNC'). The difference is all about when the COMMIT happens .
LogXptMode = ('SYNC'):
As the name implies, SYNC mode synchronizes the primary with the
standby database and all DML on the primary server will NOT be committed
until the logs have been successfully transported to the standby
servers. The synchronous log transport mode is required for the Maximum
Protection and Maximum Availability data protection modes.
LogXptMode = ('ASYNC'): Conversely,
asynchronous mode (ASYNC) allows updates (DML) to be committed on the
primary server before the log file arrives on the standby servers. The
asynchronous log transport mode is required for the Maximum Performance
data protection mode.
----------------------------------------------------------------------------------------------
LGWR is an attribute of the
LOG_ARCHIVE_DEST_n parameter which is used to specify the network
transmission mode. Specifying the SYNC attribute (which is the default),
tells the LGWR process to synchronously archive to the local online
redo log files at the same time it transmits redo data to archival
destinations. Specifically, the SYNC atrribute performs all network I/O
synchornously in conjunction with each write operation to the online
redo log file. Transactions are not committed on the primary database
until the redo data necessary to recover the transactions is received by
the destination.
The ASYNC attribute perfoms all
network I/O asynchronously and control is returned to the executing
application or user immediately. When this attribute is specified, the
LGWR process archives to the local online redo log file and submits the
network I/O request to the network server (LNSn process for that
destination, and the LGWR process continues processing the next request
without waiting for the network I/O to complete.
What happens if the network between the Primary and Standby [database] is lost with LGWR SYNC and ASYNC ? or What happens if the standby database is shutdown with LGWR SYNC and ASYNC?
This
is dependent upon the database mode we have set. If we have set Maximum
Protection, we have chosen a configuration that guarantees that no data
loss will occur. We have set this up by specifying the LWGR, SYNC, and
AFFIRM attributes of the LOG_ARCHIVE_DEST_n parameter for at least one
standby database. This mode provides the highest level of data
protection possible and to achieve this the redo data needed to recover
each transaction must be written to both the local online redo log and
the standby redo log on at least one standby database before the
transaction commits. To ensure data loss cannot occur, the primary
database shuts down if a fault (such as the network going down) prevents
it from writing its redo stream to at least one remote standby redo
log.
If we have set the Maximum
Availability mode, we have chosen a configuration that provides the
highest level of data protection that is possible without compromising
the availablity of the primary database. Like the maximum database does
not shut down if a fault prevents it from writing its redo stream to a
remote standby redo log. Instead, the primary database operates in
maximum performance mode until the fault is corrected and all gaps in
redo log files are resolved. When all gaps are resolved, the primary
database automatically resumes operating in maximum availabitly mode.
This guarantees that no data loss will occur if the primary database
fails, but only if a second fault does not complete set of redo data
being sent from the primary database to at least one standby database.
If we have set the Maximum
Performance mode (the default), we have chosen a mode that provides the
highest level of data protection that is possible without affecting the
performance of the primary database. This is accomplished by allowing a
transaction to commit as soon as the redo data needed to recover the
transaction is written to the local online redo log. The primary
database's redo data stream is also written to at least one standby
database, bu that the redo stream is written asynchronously with respect
to the commitment of the transactions that create the redo data.
The maximum performance mode
enables us to either set the LGWR and AYSNC attributes, or set the ARCH
attribute on the LOG_ARCHIVE_DEST_n parameter for the standby database
destination. If the primary database fails, we can reduce the amount of
data that is not received on the standby destination by setting the LGWR
and ASYNC attributes.
If
LGWR SYNC or ASYNC is deployed, what process(es) bring(s) the standby
database back into sync with the primary [database] if the network is
lost and is then restored? How does it do it?
Again, this is dependent
upon the mode we have chosen for our database. The LGWR process (and
possibly the LNSn process if we have multiple standby databases) is
responsible for closing the gap.
My biggest question is, when
the network to the standby is lost with SYNC or ASYNC, where is the
information queued and how is it retransmitted once the network has been
re-established?
This
implies that our database has been set to either maximum availability or
maximum performance mode. We cannot use the ASYNC attribute with
maximum protection mode. The information is queued in the local online
redo log and the LGWR (and the LNSn) process will transmit the data to
the standby database's online redo log file to close the gap once the
network connectivity has been re-established
Gap recovery is handled through
the polling mechanism. For physical and logical standby databases,
Oracle Change Data Capture, and Oracle Streams, Data Guard performs gap
detection and resolution by automatically retrieving missing archived
redo log files from the primary database. No extra configuration
settings are required to poll the standby database(s) to detect any gaps
or to resolve the gaps.
The important consideration here
is that automatic gap recovery is contigent upon the availablity of the
primary database. If the primary database is not available and we have a
configuration with mulitple physical standby databases, we can set up
additional initialization parameters so that the Redo Apply can resolve
archive gaps from another standby database.
It is possible to manually
determine if a gap exists and to resolve those archive gaps. To manually
determine if a gap exists, query the V$ARCHIVE_GAP view on our physical
standby database. If a gap is found, we will then need to locate the
archived log files on our primary database, copy them to our standby
database, and register them.
No comments:
Post a Comment