Real Time Log Apply on Standby Database

 

              Configure Real Time Log Apply on Standby

By default, log apply services wait for the full archived redo log file to arrive on the standby database before applying it to the standby database. If the real-time apply feature is enabled, log apply services can apply redo data as it is received from the Primary DB, without waiting for the current standby redo log file to be archived. We can use the ALTER DATABASE statement to enable the real-time apply feature, as below:

• For physical standby databases, issue the ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE statement.

• For logical standby databases, issue the ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE statement.

NOTE : Standby redo log files are required to use real-time apply.


Lets Test it:

oracle@ORCLSTDBY:[~] $ sqlplus /"as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Tue Oct 4 10:57:52 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> recover managed standby database cancel;
Media recovery complete.

SQL> select dest_name,status,type,srl,recovery_mode from v$archive_dest_status where dest_id=1;

DEST_NAME            STATUS     TYPE       SRL RECOVERY_MODE
-------------------- ---------- ---------- --- ------------------------------
LOG_ARCHIVE_DEST_1   VALID      LOCAL      NO  IDLE

-- Dest_id can be different in your database. but mostly it will be set to local.
-- Lets start applying logs and start the recovery mode Default (apply on log fill up)

SQL> recover managed standby database disconnect from session;
Media recovery complete.

-- Query the Recovery Mode now:

SQL> col DEST_NAME format A20
col status format A10
col type format A10
col recovery_mode format A30

select dest_name,status,type,srl,recovery_mode from v$archive_dest_status where dest_id=1;

DEST_NAME            STATUS     TYPE       SRL RECOVERY_MODE
-------------------- ---------- ---------- --- ------------------------------
LOG_ARCHIVE_DEST_1   VALID      LOCAL      NO  MANAGED

-- See that Recovery Mode will be just Managed. 

-- Lets stop log Apply and change it the recovery mode to Real-Time Apply

SQL> recover managed standby database cancel;
Media recovery complete.

SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

SQL> select dest_name,status,type,srl,recovery_mode from v$archive_dest_status where dest_id=1;

DEST_NAME                       STATUS      TYPE       SRL        RECOVERY_MODE
--------------------                   ----------       ----------     ---          ------------------------------
LOG_ARCHIVE_DEST_1   VALID      LOCAL   NO  MANAGED REAL TIME APPLY


-- We can also check this in alertlog_File.log


Completed: ALTER DATABASE RECOVER  managed standby database cancel
Tue Oct 04 11:00:47 2016
.
.
alter database recover managed standby database using current logfile disconnect from session
Attempt to start background Managed Standby Recovery process (ORCLSTDBY)
Tue Oct 04 11:00:47 2016
MRP0 started with pid=58, OS id=40557
MRP0: Background Managed Standby Recovery process started (ORCLSTDBY)
 started logmerger process
Tue Oct 04 11:00:52 2016
Managed Standby Recovery starting Real Time Apply
Parallel Media Recovery started with 64 slaves
Waiting for all non-current ORLs to be archived...


Reference Oracle Docs:

https://docs.oracle.com/cd/B19306_01/server.102/b14239/log_apply.htm#i1034632


 Similar Posts :

• How to add second standby database 
• How to create Standby database using RMAN
• Open Standby Database in Read-Only  
• Find Archive Gap between Primary and Standby Database

CHANGE STANDBY DATABASE PROTECTION MODE

SQL> select protection_mode from v$database;

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

SQL> show parameter log_archive_dest_2

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2             string     SERVICE=ORCLSTB1 NOAFFIRM ASYN
                         C VALID_FOR=(ONLINE_LOGFILES,P
                         RIMARY_ROLE) DB_UNIQUE_NAME=OR
                         CLSTB1
log_archive_dest_20             string
log_archive_dest_21             string
log_archive_dest_22             string
log_archive_dest_23             string
log_archive_dest_24             string
log_archive_dest_25             string
log_archive_dest_26             string

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_27             string
log_archive_dest_28             string
log_archive_dest_29             string
SQL> show parameter db_unique_name

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                 string     ORCL
SQL> show parameter log_archive_config

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
log_archive_config             string     dg_config=(ORCL,ORCLSTB1,ORCLS
                         TB2)
SQL> alter system set log_archive_dest_2='SERVICE=ORCLSTB1 NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCLSTB1';

System altered.

SQL> show parameter log_archive_dest_2

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2             string     SERVICE=ORCLSTB1 NOAFFIRM ASYN
                         C VALID_FOR=(ONLINE_LOGFILES,P
                         RIMARY_ROLE) DB_UNIQUE_NAME=OR
                         CLSTB1
log_archive_dest_20             string
log_archive_dest_21             string
log_archive_dest_22             string
log_archive_dest_23             string
log_archive_dest_24             string
log_archive_dest_25             string
log_archive_dest_26             string

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_27             string
log_archive_dest_28             string
log_archive_dest_29             string

SQL> alter system set log_archive_dest_2='SERVICE=ORCLSTB1 NOAFFIRM SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCLSTB1';

System altered.


SQL> alter database set standby database to maximize availability;

Database altered.

NOTE: You don’t need to shutdown your instance, when you are changing  protection mode from MAXIMUM PERFORMANCE TO MAXIMUM AVAILABILITY.But you need to if you are going to MAXIMUM PROTECTION.

SQL> alter system switch logfile;

System altered.

SQL> select protection_mode from v$database;

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

SQL> archive log list;
Database log mode           Archive Mode
Automatic archival           Enabled
Archive destination           /u01/app/oracle/oraarch/
Oldest online log sequence     239
Next log sequence to archive   241
Current log sequence           241
SQL> select group#,bytes/1024/1024 from v$standby_log;

    GROUP# BYTES/1024/1024
---------- ---------------
     4        52
     5        52
     6        52
     7        52


SQL> select thread#,max(sequence#) from v$archived_log group by thread#;

   THREAD# MAX(SEQUENCE#)
---------- --------------
     1          240

SQL> select protection_mode from v$database;

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



SQL> alter system switch logfile;

System altered.

ENABLE REAL TIME APPLY ON STANDBY DATABASE

*********************************************************************
    REAL TIME APPLY OF ARCHIVE LOG FILES IN READ ONLY MODE:
*********************************************************************

SQL> select PROTECTION_MODE,PROTECTION_LEVEL FROM V$DATABASE;

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

SQL> archive log list;
Database log mode           Archive Mode
Automatic archival           Enabled
Archive destination           /u01/app/oracle/oraarch/
Oldest online log sequence     250
Next log sequence to archive   0
Current log sequence           252

SQL> recover managed standby database using current logfile disconnect from session;
Media recovery complete.
SQL> alter database open read only;

Database altered.


***********************************************************************************
    NOW ON PRIMARY DATABASE I CREATE TABLE IN ONE OF USER ADD INSERT SOME DATA
***********************************************************************************

[oracle@Linux01 Desktop]$ sqlplus /"As sysdba"

SQL> select name,open_mode,db_unique_name,PROTECTION_MODE,PROTECTION_LEVEL FROM V$DATABASE;

NAME          OPEN_MODE                   DB_UNIQUE_NAME   PROTECTION_MODE        PROTECTION_LEVEL
--------     --------------------    ---------         --------------------         ---------
ORCL          READ ONLY WITH APPLY     ORCLSTB1        MAXIMUM PERFORMANCE      MAXIMUM PERFORMAN



SQL> conn atoorpu/XXXXX;
Connected.
SQL> create table abcd (name varchar2(20));

Table created.

SQL> insert into abcd values ('arvind');

1 row created.

SQL> commit;

Commit complete.


SQL> alter system switch logfile;

System altered.

SQL> insert into abcd values ('reddy');

1 row created.

SQL> commit;

Commit complete.

SQL> create table abcds as select * from abcd;

Table created.

***********************************************************************************
    NOW ON STANDBY DATABASE LETS QUERY SAME DATA SOME DATA
***********************************************************************************

[oracle@Linux02 Desktop]$ sqlplus /"As sysdba"

SQL> select name,open_mode,db_unique_name,PROTECTION_MODE,PROTECTION_LEVEL FROM V$DATABASE;

NAME          OPEN_MODE                   DB_UNIQUE_NAME   PROTECTION_MODE        PROTECTION_LEVEL
--------     --------------------    ---------         --------------------         ---------
ORCL          READ ONLY WITH APPLY     ORCLSTB1        MAXIMUM PERFORMANCE      MAXIMUM PERFORMAN

SQL> conn atoorpu/XXXXX;
Connected.
SQL> select * from abcd;

NAME
--------------------
arvind

SQL> select * from abcd;

NAME
--------------------
arvind
reddy

OPEN STANDBY DATABASE IN READ ONLY

SQL> select PROTECTION_MODE,PROTECTION_LEVEL FROM V$DATABASE;

PROTECTION_MODE      PROTECTION_LEVEL

-------------------- --------------------

MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE

SQL> select name,db_unique_name,open_mode,DATABASE_ROLE from v$database;

NAME   DB_UNIQUE_NAME  OPEN_MODE       DATABASE_ROLE

--------- ------------------------------ -------------------- ----------------

ORCL   ORCLSTB1  MOUNTED       PHYSICAL STANDBY

 SQL> alter database open read only;

Database altered.

SQL> select name,db_unique_name,open_mode,DATABASE_ROLE from v$database;

NAME   DB_UNIQUE_NAME  OPEN_MODE       DATABASE_ROLE

--------- ------------------------------ -------------------- ----------------

ORCL   ORCLSTB1  READ ONLY       PHYSICAL STANDBY

SQL> select PROTECTION_MODE,PROTECTION_LEVEL FROM V$DATABASE;

PROTECTION_MODE      PROTECTION_LEVEL

-------------------- --------------------

MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY

SQL> SELECT database_role, open_mode FROM v$database;

DATABASE_ROLE  OPEN_MODE

---------------- --------------------

PHYSICAL STANDBY READ ONLY WITH APPLY

SQL> select process,status from v$managed_standby;

PROCESS   STATUS
--------- ------------
ARCH      CLOSING
ARCH      CLOSING
ARCH      CONNECTED
ARCH      CLOSING
ARCH      CLOSING
RFS      IDLE
RFS      IDLE
RFS      IDLE
RFS      IDLE
MRP0      APPLYING_LOG
 


 Similar Posts :

• How to add second standby database 
• How to create Standby database using RMAN
• Open Standby Database in Read-Only  
• Find Archive Gap between Primary and Standby Database

database switch over using dgmgrl

Perform a switch over test:


ON PRIMARY DB SERVER :

SQL> select name,open_mode,db_unique_name ,DATABASE_ROLE from v$database;

NAME  OPEN_MODE       DB_UNIQUE_NAME      DATABASE_ROLE
--------- -------------------- ------------------------------ ----------------
ORCL  READ WRITE       ORCL      PRIM

ON STANDBY DB SERVER :

SQL> select name,open_mode,db_unique_name ,DATABASE_ROLE from v$database;

NAME  OPEN_MODE       DB_UNIQUE_NAME      DATABASE_ROLE
--------- -------------------- ------------------------------ ----------------
ORCL  MOUNTED       ORCLSTB1      PHYSICAL STANDBY


LETS CONNECT TO DGMGRL AND SWITCH OVER ROLES

DGMGRL> switchover to 'ORCLSTB1';
Performing switchover NOW, please wait...
Operation requires a connection to instance "ORCLSTB1" on database "ORCLSTB1"
Connecting to instance "ORCLSTB1"...
Connected.
New primary database "ORCLSTB1" is opening...
Operation requires startup of instance "ORCLPRIM" on database "ORCL"
Starting instance "ORCLPRIM"...
Unable to connect to database
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Failed.
Warning: You are no longer connected to ORACLE.

Please complete the following steps to finish switchover:
start up and mount instance "ORCLPRIM" of database "ORCL"


ON OLD PRIMARY DB SERVER (ORCL) :

I have to start it manually coz dgmgrl was unable to connect to lsnr after role transfer.

SQL> startup mount;
ORACLE instance started.

Total System Global Area  885211136 bytes
Fixed Size    2258320 bytes
Variable Size  566233712 bytes
Database Buffers  310378496 bytes
Redo Buffers    6340608 bytes
Database mounted

SQL> select name,open_mode,db_unique_name ,DATABASE_ROLE from v$database;

NAME  OPEN_MODE       DB_UNIQUE_NAME      DATABASE_ROLE
--------- -------------------- ------------------------------ ----------------
ORCL  MOUNTED       ORCL      PHYSICAL STANDBY


LETS CONFIRM THE DB_ROLE BY QUERYING STANDBY DATABASE

SQL> select name,open_mode,db_unique_name ,DATABASE_ROLE from v$database;

NAME  OPEN_MODE       DB_UNIQUE_NAME      DATABASE_ROLE
--------- -------------------- ------------------------------ ----------------
ORCL  READ WRITE       ORCLSTB1      PRIMARY

FIND ARCHIVE GAP BETWEEN PRIMARY AND STANDBY

Finding Archive gap between PRIMARY and STANDBY

  
  A Physical Standby database syncs with Primary by continuous apply of archive logs from a Primary Database. When the logs are missing on standby is less than 10-15 numbers, We can simple ship the logs which are missing in the standby database from primary database by using SCP/FTP and then register the log files in standby to resolve the gap.

Use below query on standby Db to see if there is any gap. This will be help full if u have logs shipped and not applied. But in situations when the listener is down or network disturbances. etc the logs wont be shipped so this query might not work.

Run on Primary DB :

Primary: SQL > select thread#, max(sequence#) "Last Primary Seq Generated"
from v$archived_log val, v$database vdb
where val.resetlogs_change# = vdb.resetlogs_change#

group by thread# order by 1;

The primary thread max seq should match standby.

Run on Standby DB:

******************************************************************************
----- More Detailed info with max received seqno# & max applied seqno# included -----
******************************************************************************

select  MAX_RECEIVED_SEQNO,MAX_APPLIED_SEQNO,(MAX_RECEIVED_SEQNO - MAX_APPLIED_SEQNO) as "Difference" from (SELECT 
   ARCHIVED_THREAD#, MAX(ARCHIVED_SEQ#) as MAX_RECEIVED_SEQNO, 
   APPLIED_THREAD#,  MAX(APPLIED_SEQ#) as  MAX_APPLIED_SEQNO
FROM 
  V$ARCHIVE_DEST_STATUS) ARCH ;

Sample output :


SQL> select  MAX_RECEIVED,MAX_APPLIED,(MAX_RECEIVED - MAX_APPLIED) as "Difference" from (SELECT
   ARCHIVED_THREAD#, MAX(ARCHIVED_SEQ#) as MAX_RECEIVED,
   APPLIED_THREAD#,  MAX(APPLIED_SEQ#) as  MAX_APPLIED
FROM
  V$ARCHIVE_DEST_STATUS) ARCH ;  

MAX_RECEIVED          MAX_APPLIED         Difference
------------                                -----------                ----------
       26424                                   26419                       5


*********************************************************************************
----- Simple query  to just display difference-----
*********************************************************************************

select  MAX_RECEIVED,MAX_APPLIED,(MAX_RECEIVED - MAX_APPLIED) as "Difference" from (SELECT 
   ARCHIVED_THREAD#, MAX(ARCHIVED_SEQ#) as MAX_RECEIVED, 
   APPLIED_THREAD#,  MAX(APPLIED_SEQ#) as  MAX_APPLIED
FROM 
  V$ARCHIVE_DEST_STATUS) ARCH ;

Sample output :


SQL> select (MAX_RECEIVED - MAX_APPLIED) as "Difference" from (SELECT
   ARCHIVED_THREAD#, MAX(ARCHIVED_SEQ#) as MAX_RECEIVED,
   APPLIED_THREAD#,  MAX(APPLIED_SEQ#) as  MAX_APPLIED
FROM
  V$ARCHIVE_DEST_STATUS) ARCH ; 

Difference
----------
         3


  How to Sync small gap betwen Primary and Standby :

Lets say there is a Gap between the DB's, try to sync them manually if gap is small. Follow below process :

COPY MISSING ARCHIVELOG FILE'S FROM PRIMARY TO STANDBY :

$ scp log_file_1234.arc oracle@standby:/log_location/log_file_1234.arc

NOW REGISTER LOGFILE IN STANDBY DB:

SQL> alter database register logfile ‘/log_location/log_file_n.arc'; 
logfile registered

Repeat the same process for all the log files which are missing at standby.In case the gap is huge You can use Rman backup to cover the gap.

 Similar Posts :

• How to add second standby database 
• How to create Standby database using RMAN
• Open Standby Database in Read-Only