Monday, October 1, 2012

Change Data guard Protection Mode


There are 3 ways of shipping redo data to a physical standby:

LGWR SYNC = Max Availability & Max Protection
LGWR ASYNC = Max Performance
ARCH


Maximum Protection = This protection level uses a synchronous replication process to ensure that no data loss will occur if the primary database fails. It also enforces rules that prevent multiple failure events from causing data loss. This protection level will never allow a primary database to acknowledge commit success for an unprotected transaction.To provide this level of protection, the redo data that is needed to recover each transaction must be written to both the local online redo log and to the standby redo log on at least one standby database before Oracle can acknowledge commit success to the application. To ensure that data loss cannot occur, the primary database will shut down if a fault prevents it from writing its redo stream to the standby redo log of at least one standby database.


Maximum Availability = This protection level uses a synchronous replication process that provides zero data loss protection without compromising the availability of the primary database. Like Maximum Protection, commit success is not acknowledged to the application until the redo that is needed to recover that transaction is written to the local online redo log and to the standby redo log of at least one standby database. Unlike Maximum Protection, however, the primary database does not shut down if a fault prevents it from writing its redo stream to a remote standby redo log. The primary database will stall for a maximum of net_timout seconds (user configurable) before proceeding, in order to maintain availability of the primary database. Data Guard automatically resynchronizes primary and standby databases when the connection is restored. Data loss is possible if a second failure occurs before the resynchronization process is complete.

Maximum performance = This protection mode (the default) is an asynchronous replication process that provides the highest level of data protection that is possible without affecting the performance of the primary database. This is accomplished by acknowledging commit success as soon as the redo data that is needed to recover that transaction is written to the local online redo log without waiting for confirmation by the standby that the data is protected. The redo data stream of the primary database is transmitted to the standby database directly from the Oracle in-memory log buffer as quickly as it is generated. .

The default is Maximum Performance.

SYNC | ASYNC = Specifies whether the redo data is to be received at the destination before the transaction is committed. ASYNC is default.

AFFIRM | NOAFFIRM = Control whether the redo destination acknowledges received redo data. NOAFFIRM is the default for ASYNC if not specified. AFFIRM is the default for SYNC for if not specified.

NET_TIMEOUT = Specifies the time in seconds that the primary database log writer will wait for a response from the Log Network Service (LNS) before terminating the connection and marking the standby (destination) as failed. The default value is 30 seconds.

REOPEN = Specifies the time in seconds that the log writer should wait before attempting to access a previously failed standby (destination). The default is 300 seconds.

Use LGWR SYNC if you use either Maximum Protection OR Maximum Availability.
so here LGWR is directly connected with RFS,henc there will be no LNS process.

On the primary database, query the RECOVERY_MODE column in the V$ARCHIVE_DEST_STATUS view, which displays the standby database's operation as MANAGED_RECOVERY for Redo Apply and MANAGED REAL TIME APPLY for real-time apply.


By default standby is running in MAXIMUM PERORMANCE Mode,

SQL> alter system set log_archive_dest_2='SERVICE=STDBY LGWR ASYNC COMPRESSION=ENABLE REOPEN=15 NET_TIMEOUT=30 MAX_FAILURE=10 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STDBY

SQL> select PROTECTION_MODE,PROTECTION_LEVEL FROM V$DATABASE;

PROTECTION_MODE      PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE


============Change to MAXIMUM AVAILABILITY========================

STDBY-> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

System altered.

--On Primary Change Log shipping attributes. 

PROD-> alter system set log_archive_dest_2='SERVICE=STDBY SYNC AFFIRM NET_TIMEOUT=100 REOPEN=300 DB_UNIQUE_NAME=STDBY VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE)' scope = both;

System altered.

--On Primary Change Mode.
PROD-> alter database set standby database to maximize availability;

Database altered.

---Start Recovery Process on STANDBY. 
STDBY-> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

STDBY-> select PROTECTION_MODE,PROTECTION_LEVEL FROM V$DATABASE;

PROTECTION_MODE      PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY


--In Primary database Alertlog.

ALTER SYSTEM SET log_archive_dest_2='SERVICE=STDBY SYNC AFFIRM NET_TIMEOUT=100 REOPEN=300 DB_UNIQUE_NAME=STDBY VALID_FOR=(ALL_LOGFILES,PRIMARY_ROLE)' SCOPE=BOTH;
Wed Aug 15 06:35:30 2012
Destination LOG_ARCHIVE_DEST_2 is UNSYNCHRONIZED
******************************************************************
LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
******************************************************************
LGWR: Standby redo logfile selected to archive thread 1 sequence 19
LGWR: Standby redo logfile selected for thread 1 sequence 19 for destination LOG_ARCHIVE_DEST_2
Thread 1 advanced to log sequence 19 (LGWR switch)
  Current log# 1 seq# 19 mem# 0: /u01/test/redo/redo01.log
Wed Aug 15 06:35:30 2012
Archived Log entry 24 added for thread 1 sequence 18 ID 0xbacde77 dest 1:
Wed Aug 15 06:35:48 2012
Destination LOG_ARCHIVE_DEST_2 is SYNCHRONIZED
LGWR: Standby redo logfile selected to archive thread 1 sequence 20
LGWR: Standby redo logfile selected for thread 1 sequence 20 for destination LOG_ARCHIVE_DEST_2
Thread 1 advanced to log sequence 20 (LGWR switch)
  Current log# 2 seq# 20 mem# 0: /u01/test/redo/redo02.log
Wed Aug 15 06:35:49 2012
Archived Log entry 26 added for thread 1 sequence 19 ID 0xbacde77 dest 1:
Wed Aug 15 06:36:29 2012
alter database set standby database to maximize availability
Completed: alter database set standby database to maximize availability

========================Change it to MAXIMUM PROTECTION ==============================

STDBY->select PROTECTION_MODE,PROTECTION_LEVEL FROM V$DATABASE;

PROTECTION_MODE      PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY

Elapsed: 00:00:00.05

PROD-> select PROTECTION_MODE,PROTECTION_LEVEL FROM V$DATABASE;

PROTECTION_MODE      PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY

----STOP RECOVERY PROCESS ON STANDBY. 
STDBY->ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

Elapsed: 00:00:00.22

--On Primary Change Mode.
PROD-> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PROTECTION;

Database altered.

PROD-> select PROTECTION_MODE,PROTECTION_LEVEL FROM V$DATABASE;

PROTECTION_MODE      PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM PROTECTION   MAXIMUM PROTECTION

---Start Recovery Process on STANDBY.  
STDBY->ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Database altered.

Elapsed: 00:00:06.07
STDBY->select PROTECTION_MODE,PROTECTION_LEVEL FROM V$DATABASE;

PROTECTION_MODE      PROTECTION_LEVEL
-------------------- --------------------
MAXIMUM PROTECTION   MAXIMUM PROTECTION

Elapsed: 00:00:00.00

--In Standby database Alertlog.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
Mon Oct 01 13:29:59 2012
MRP0: Background Media Recovery cancelled with status 16037
Errors in file /u00/app/oracle/diag/rdbms/stdby/STDBY/trace/STDBY_mrp0_3240.trc:
ORA-16037: user requested cancel of managed recovery operation
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Recovered data files to a consistent state at change 1054907
Errors in file /u00/app/oracle/diag/rdbms/stdby/STDBY/trace/STDBY_mrp0_3240.trc:
ORA-16037: user requested cancel of managed recovery operation
MRP0: Background Media Recovery process shutdown (STDBY)
Waiting for MRP0 pid 3240 to terminate
Managed Standby Recovery Canceled (STDBY)
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
Mon Oct 01 13:34:39 2012
Changing standby controlfile to MAXIMUM PROTECTION mode
Mon Oct 01 13:34:39 2012
Archived Log entry 23 added for thread 1 sequence 30 ID 0xbacde77 dest 1:
RFS[1]: Selected log 4 for thread 1 sequence 31 dbid 195900002 branch 784012804
Mon Oct 01 13:34:59 2012
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION
Attempt to start background Managed Standby Recovery process (STDBY)
Mon Oct 01 13:34:59 2012
MRP0 started with pid=28, OS id=3256
MRP0: Background Managed Standby Recovery process started (STDBY)
Serial Media Recovery started
Managed Standby Recovery starting Real Time Apply
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Media Recovery Log /u01/stdby/archive/1_30_784012804.dbf
Media Recovery Waiting for thread 1 sequence 31 (in transit)
Recovery of Online Redo Log: Thread 1 Group 4 Seq 31 Reading mem 0
  Mem# 0: /u01/stdby/redoredo04.log
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION
Mon Oct 01 13:45:08 2012
Standby controlfile consistent with primary
RFS[1]: Selected log 5 for thread 1 sequence 32 dbid 195900002 branch 784012804
Mon Oct 01 13:45:10 2012
Archived Log entry 24 added for thread 1 sequence 31 ID 0xbacde77 dest 1:
Mon Oct 01 13:45:11 2012
Media Recovery Waiting for thread 1 sequence 32 (in transit)
Recovery of Online Redo Log: Thread 1 Group 5 Seq 32 Reading mem 0
  Mem# 0: /u01/stdby/redoredo05.log
  

===================================================================================================================

1 comment:

  1. Use LGWR SYNC if you use either Maximum Protection OR Maximum Availability.
    so here LGWR is directly connected with RFS,henc there will be no LNS process.

    - Above point is incorrect. Refer the official doc

    http://docs.oracle.com/cd/B19306_01/server.102/b14239/log_transport.htm#i1268542



    ReplyDelete