What is Async and Sync in Oracle Dataguard

What is Async and Sync in Oracle Dataguard

There are three protection modes for primary database in oracle dataguard

1.Maximum Protection

2.Maximum Performance

3.Maximum Availability

1.Maximum Protection

Transactions on the primary are not commit until redo information has been written to the online redo log and at least one of the the standby standby location. If there is not suitable standby location is available or found for any reason, the primary database shuts down.

2.Maximum Performance

Transactions on the primary database commit as soon as redo information has been written to the online redo log and Transfer of redo information to the standby server is asynchronous .so in this type of protection mode there is no impact on performance of the primary.

3.Maximum Availability

Transactions on the primary are not commit until redo information has been written to the online redo log and at least one of the the standby standby location. If there is not suitable standby location is available or found for any reason it acts same as the maximum performance mode.

For knowing the protection mode of your primary database use query

SQL>SELECT protection_mode FROM v$database;

if you want to switch into other mode from your current protection mode use

1.Maximum Availability

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=stby AFFIRM SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stby';

SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY;

2.Maximum Performance

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=stby NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stby';

SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;

3.Maximum Protection

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=stby AFFIRM SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stby';

SQL>ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PROTECTION;

How To Use Active Data Guard Feature In Oracle 11g

How To Use Active Data Guard Feature In Oracle 11g

oracle 11g introduces new feature of Active Data Guard in which standby database is in Read Only Mode as well as applying redo from primary database.

Means your standby database is syncing with your primary database and at the same time you can use your standby as a reporting server in OLTP.

For activating Active Data Guard feature in 11g you have to issue these commands at

Oracle Standby Database

1. SHUTDOWN THE STANDBY DATABASE

  SQL> SHUTDOWN IMMEDIATE;


 2. STARTUP THE STANDBY DATABASE IN NOMOUNT STAGE ( AT NOMOUNT STAGE ONLY    PARAMETER FILE IS READ)
 
  SQL> STARTUP NOMOUNT ;


 3. MOUNT THE STANDBY DATABASE( AT MOUNT STAGE CONTROL FILE IS READ)

  SQL> ALTER DATABASE MOUNT STANDBY DATABASE ;


 4. OPEN THE STANDBY DATABASE IN READ ONLY MODE STAGE 

  SQL> ALTER DATABASE OPEN READ ONLY;


 5. APPLY THE MANAGED RECOVERY PROCESS
 
  SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM    SESSION;