Create a 19c RAC Standby Database From Overnight RMAN Tape Backups

Here is an example of building a RAC standby database by using overnight NetBackup RMAN backups.

Primary RAC servers: racnode1,racnode2
Standby RAC servers: stdbynode1,stdbynode2
Primary Database Name:  RACTEST
Primary Instance Names: RACTEST1/2
Standby Instance Names: STBTEST1/2

RAC Database version: 19.x.x.x.x

1)On primary database, check most recent RMAN backups are available.

$ rman target /  catalog rman/password@rman
RMAN> list backup of database;

STEP 3 and 4 are not required, if using DUPLICATE.

2) On primary, create a standby controlfile.

SQL> alter database create standby controlfile as '/tmp/RACTEST_standby.ctl';
Database altered.

or

RMAN>  backup device type disk format '/tmp/RACTEST_standby.ctl' current controlfile for standby;

3)Copy standby controlfile onto standby server stdbynode1:/tmp.

[RACTEST1] oracle@racnode1:/tmp$ scp RACTEST_standby.ctl stdbynode1:/tmp/

-4) Copy primary database password file onto standby database server as per “How to copy password file from primary to standby database in 12c onwards“

5) On primary database, make change for FORCE LOGGING, only if database is not in “force logging” yet.

 SQL> ALTER DATABASE FORCE LOGGING;

6) On primary database, change or add below parameters .

DB_UNIQUE_NAME=RACTEST
LOG_ARCHIVE_CONFIG='DG_CONFIG=(STBTEST,RACTEST)'
DB_FILE_NAME_CONVERT='STBTEST','RACTEST'
LOG_FILE_NAME_CONVERT='STBTEST','RACTEST'
STANDBY_FILE_MANAGEMENT=AUTO
FAL_SERVER=STBTEST
FAL_CLIENT=RACTEST
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
log_archive_dest_1='LOCATION=use_db_recovery_file_dest VALID_FOR=( ALL_LOGFILES, ALL_ROLES) DB_UNIQUE_NAME=RACTEST'
LOG_ARCHIVE_DEST_2='SERVICE=STBTEST VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STBTEST'

7) On standby database, change or add below parameters.

DB_NAME=RACTEST
DB_UNIQUE_NAME=STBTEST
LOG_ARCHIVE_CONFIG='DG_CONFIG=(RACTEST,STBTEST)'
DB_FILE_NAME_CONVERT='RACTEST','STBTEST'
LOG_FILE_NAME_CONVERT='RACTEST','STBTEST'
STANDBY_FILE_MANAGEMENT=AUTO
FAL_SERVER=RACTEST
FAL_CLIENT=STBTEST
*.log_archive_dest_1='LOCATION=use_db_recovery_file_dest VALID_FOR=( ALL_LOGFILES, ALL_ROLES) DB_UNIQUE_NAME=STBTEST'
*.LOG_ARCHIVE_DEST_2='SERVICE=RACTEST LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=RACTEST'

8) On standby database, set standby *.control_files=(‘+DATA1′,’+FRA’), and startup database in nomount mode.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1.5366E+10 bytes
Fixed Size 2165488 bytes
Variable Size 7650411792 bytes
Database Buffers 7650410496 bytes
Redo Buffers 63135744 bytes

9) On standby database, restore standby control files. Update parameter “control_files” accordingly.

No need this step if using DUPLICATE.

[STBTEST1] oracle@stdbynode1:/tmp$ rman target /
RMAN> restore controlfile to '+FRA/STBTEST/CONTROLFILE/controlfile2.f' from '/tmp/RACTEST2_standby.ctl';
RMAN> restore controlfile to '+DATA1/STBTEST/CONTROLFILE/controlfile1.f'  from '/tmp/RACTEST2_standby.ctl';

control_files = '+DATA1/STBTEST/CONTROLFILE/controlfile1.f','+fra/STBTEST/CONTROLFILE/controlfile2.f'

10) On standby database, shutdown and mount standby database.

SQL>  create spfile='+DATA1/STBTEST/PARAMETERFILE/spfileSTBTEST.ora' from pfile='/tmp/stbtest.ora';

File created.

$  view $ORACLE_HOME/dbs/initSTBTEST1.ora
$ cat $ORACLE_HOME/dbs/initSTBTEST1.ora
spfile='+DATA1/STBTEST/PARAMETERFILE/spfileSTBTEST.ora' 


SQL> shutdown immediate;
ORA-01507: database not mounted


ORACLE instance shut down.

SQL> startup nomount;
ORACLE instance started.
Total System Global Area 1.0262E+11 bytes
Fixed Size 2265224 bytes
Variable Size 1.2885E+10 bytes
Database Buffers 8.9657E+10 bytes
Redo Buffers 74416128 bytes

-- no need the following steps if using DUPLICATE
--

SQL> alter database mount standby database;
Database altered.

11) On standby database, restore datafiles from overnight RMAN backups.

[STBTEST1] oracle@stdbynode1:/u01/app/oracle/admin/STBTEST/scripts$ cat restore_STBTEST.rman

run {
allocate channel c1 device type sbt_tape parms='ENV=(NB_ORA_CLIENT=racnode1)';
allocate channel c2 device type sbt_tape parms='ENV=(NB_ORA_CLIENT=racnode1)';
allocate channel c3 device type sbt_tape parms='ENV=(NB_ORA_CLIENT=racnode1)';
allocate channel c4 device type sbt_tape parms='ENV=(NB_ORA_CLIENT=racnode1)';
SET NEWNAME FOR DATABASE TO '+DATA1';
restore database;
switch datafile all;
switch tempfile all;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
}

[STBTEST1] oracle@stdbynode1:/u01/app/oracle/admin/STBTEST/scripts$ nohup rman target / catalog rman/password@rman cmdfile=/u01/app/oracle/admin/STBTEST/scripts/restore_STBTEST.rman log=/u01/app/oracle/admin/STBTEST/scripts/restore_STBTEST.log &
[1] 19448

By using “restore database”, you have to add “switch datafile all” , because the ASM disk path for datafiles and redolog files have changed and they are actually from ones in standby control file, and otherwise the datafile name will be like:

*****************************************
WARNING: The converted filename '+REDO/STBTEST/onlinelog/group_2.280.1163406521'
         is an ASM fully qualified filename.
         Changing the filename to '+REDO/MUST_RENAME_THIS_LOGFILE_2.4294967295.4294967295'.
         Please rename it accordingly.
*****************************************

*****************************************
WARNING: The converted filename '+DATA1/STBTEST/datafile/users.854.1163407487'
        is an ASM fully qualified filename.
         Changing the filename to '+DATA1/MUST_RENAME_THIS_DATAFILE_7.4294967295.4294967295'.
         Please rename it accordingly.
*****************************************

For “MUST_RENAME_THIS_TEMPFILE“, it can be ignored, cos when we “alter database open read only” and “alter pluggable database pdb1 open read only”, those TEMP files are created automatically.

PDB1(3):*****************************************
PDB1(3):WARNING: The converted filename '+DATA1/xxx/34cbb1a7a1fcd520e0630c2101erwdd/tempfile/temp.2591.1175684807'
PDB1(3):         is an ASM fully qualified filename.
PDB1(3):         Changing the filename to '+DATA1/MUST_RENAME_THIS_TEMPFILE_21.4294967295.4294967295'.
PDB1(3):         Please rename it accordingly.

After database and PDB opened read only, DBA can drop and add temp files like :

SQL>  alter tablespace temp drop tempfile 14;

Tablespace altered.


SQL> alter tablespace temp add tempfile '+data1' size 100m autoextend on maxsize 32767m;

Tablespace altered.

OR we can

1)Change Standby_File_Management parameter to MANUAL
2) Cleaning and creating Standby Logfiles.
SQL>ALTER DATABASE DROP LOGFILE GROUP 11
…
..

SQL>ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 group 11 size 1024m;
3)Cleaning and creating Online Logfiles.
SQL>ALTER DATABASE DROP LOGFILE GROUP 1;
SQL>ALTER DATABASE ADD LOGFILE THREAD 1 group 1 size 1024M

4) Cleaning and creating Temp Datafiles.
SQL> ALTER DATABASE RENAME FILE ‘+DATA/MUST_RENAME_THIS_TEMPFILE_1.4294967295.4294967295’ to ‘+DATA/STEST/TEMPFILE/tmpfile_01.dat’;

5) Change Standby_File_Management parameter to AUTO
SQL> alter system set standby_file_management=’AUTO’;

we prefer the following way to duplicate a standby database without much manual works.

[STBTEST1] oracle@stdbynode1:/u01/app/oracle/admin/STBTEST/scripts$ cat duplicate_STBTEST.rman

run {
allocate auxiliary channel c1 device type sbt_tape parms='ENV=(NB_ORA_CLIENT=racnode1)';
allocate auxiliary channel c2 device type sbt_tape parms='ENV=(NB_ORA_CLIENT=racnode1)';
allocate auxiliary channel c3 device type sbt_tape parms='ENV=(NB_ORA_CLIENT=racnode1)';
allocate auxiliary channel c4 device type sbt_tape parms='ENV=(NB_ORA_CLIENT=racnode1)';
SET NEWNAME FOR DATABASE TO '+DATA1';
DUPLICATE DATABASE RACTEST FOR STANDBY UNTIL TIME "TO_DATE('19-JAN-2025 14:32:00','DD-MON-YYYYHH24:MI:SS')";
}
release channel c1;
release channel c2;
release channel c3;
release channel c4;
}

[STBTEST1] oracle@stdbynode1:/u01/app/oracle/admin/STBTEST/scripts$ nohup rman target / catalog rman/password@rman cmdfile=/u01/app/oracle/admin/STBTEST/scripts/duplicate_STBTEST.rman log=/u01/app/oracle/admin/STBTEST/scripts/duplicate_STBTEST.log &
[1] 19449

12) On standby database, monitor the progressing by querying gv$session_longops.

SQL> select INST_ID,OPNAME,SOFAR*8/1024/ELAPSED_SECONDS,ELAPSED_SECONDS/60,TIME_REMAINING/60 from gv$session_longops where OPNAME like 'RMAN%' and TIME_REMAINING>6 order by 2;
 INST_ID OPNAME SOFAR*8/1024/ELAPSED_SECONDS ELAPSED_SECONDS/60 TIME_REMAINING/60
---------- ---------------------------------------------------------------- ---------------------------- ------------------ -----------------
 1 RMAN: aggregate input 425.803241 20.1666667 102.85
 1 RMAN: full datafile restore 105.103955 21.55 103.016667
 1 RMAN: full datafile restore 112.092109 21.9 94.7833333
 1 RMAN: full datafile restore 113.487487 21.8833333 93.4333333
 1 RMAN: full datafile restore 108.213722 21.8833333 99.35

13) Restore gap archivelogs.

RMAN>restore archivelog from logseq=100 until logseq=200 thread=1;

RMAN>restore archivelog from logseq=80 until logseq=180 thread=2;

14) Add standby online logfiles ( n + 1 ) into both primary and standby database.

SQL> alter database add standby logfile thread 1 group 11 size 1024m;

Database altered.
....
...
..
.

15) On standby database, create spfile if not yet.

SQL> create spfile='+DATA1' from pfile='/u01/app/oracle/product/12.1.0/dbhome_1/dbs/initSTBTEST1.ora';

File created.

$ echo spfile='+DATA1/STBTEST/PARAMETERFILE/spfile.1323.925567677' > initSTBTEST1.ora

$ scp initSTBTEST1.ora stdbynode2:/u01/app/oracle/product/12.1.0/dbhome_1/dbs/initSTBTEST2.ora

16) On standby database, put standby database under recovery.

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

SQL> startup nomount;


ORACLE instance started.

Total System Global Area 1.0489E+10 bytes
Fixed Size 2162560 bytes
Variable Size 5704253568 bytes
Database Buffers 4764729344 bytes
Redo Buffers 17899520 bytes

SQL> alter database mount standby database;

Database altered.

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> alter database recover managed standby database disconnect nodelay;

17) Add standby database into cluster.

[STBTEST1] oracle@stdbynode1:/u01/app/oracle/product/12.1.0/dbhome_1/dbs$ srvctl add database -n STBTEST -o /u01/app/oracle/product/12.1.0/dbhome_1 -d STBTEST -r physical_standby
[STBTEST1] oracle@stdbynode1:/u01/app/oracle/product/12.1.0/dbhome_1/dbs$ srvctl modify database -d STBTEST -s mount
[STBTEST1] oracle@stdbynode1:/u01/app/oracle/product/12.1.0/dbhome_1/dbs$ srvctl add instance -d STBTEST -i STBTEST1 -n stdbynode1
[STBTEST1] oracle@stdbynode1:/u01/app/oracle/product/12.1.0/dbhome_1/dbs$ srvctl add instance -d STBTEST -i STBTEST2 -n stdbynode2

18) Configure archivelog deletion policy for both primary and standby databases.

a) On primary, log on to RMAN and change the archive log deletion policy by running:

RMAN>CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;

b) On standby, log on to RMAN and change the archive log deletion policy by running:

RMAN>CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;

19) For 12c onwards, no need to add a static registration into LISTENER.ora on all nodes of both primary and standby database. Below is only for under 12c like 11.2.0.4, etc.

#
# DGMGRL>switchover to <standby> Fails with ORA-12514 (Doc ID 1582927.1)
# Each Standby needs a static registration for the switchover to work
#
-- for node racnode1

SID_LIST_LISTENER =
 (SID_LIST =
 (SID_DESC =
 (GLOBAL_DBNAME = STBTEST_DGMGRL)
 (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
 (SID_NAME = RACTEST1)
 )
 )

-- for node racnode2

SID_LIST_LISTENER =
 (SID_LIST =
 (SID_DESC =
 (GLOBAL_DBNAME = STBTEST_DGMGRL)
 (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
 (SID_NAME = RACTEST2)
 )
 )

-- for node stdbynode1

SID_LIST_LISTENER =
 (SID_LIST =
 (SID_DESC =
 (GLOBAL_DBNAME = STBTEST_DGMGRL)
 (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
 (SID_NAME = STBTEST1)
 )
 )

-- for node stdbynode2

SID_LIST_LISTENER =
 (SID_LIST =
 (SID_DESC =
 (GLOBAL_DBNAME = STBTEST_DGMGRL)
 (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
 (SID_NAME = STBTEST2)
 )
 )


DGMGRL> show instance 'STBTEST1' StaticConnectIdentifier
 StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.10.10)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=STBTEST_DGMGRL)(INSTANCE_NAME=STBTEST1)(SERVER=DEDICATED)))'
DGMGRL> show instance 'STBTEST2' StaticConnectIdentifier
 StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.10.12)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=STBTEST_DGMGRL)(INSTANCE_NAME=STBTEST2)(SERVER=DEDICATED)))'
DGMGRL> show instance 'RACTEST1' StaticConnectIdentifier
 StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.10.20)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=RACTEST_DGMGRL)(INSTANCE_NAME=RACTEST1)(SERVER=DEDICATED)))'
DGMGRL> show instance 'RACTEST2' StaticConnectIdentifier
 StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.10.22)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=RACTEST_DGMGRL)(INSTANCE_NAME=RACTEST2)(SERVER=DEDICATED)))'

Create and enable DG broker

set db_broker_config_file1 and 2 on both primary and standby. remove ‘LOG_ARCHIVE_DEST_2’ on standby

-- on standby only
SQL> alter system set LOG_ARCHIVE_DEST_2='';

SQL> alter system set dg_broker_config_file1='+DATA2/RACTEST/DATAGUARDCONFIG/dr1RACTEST.dat';

System altered.

SQL> alter system set dg_broker_config_file2='+DATA2/RACTEST/DATAGUARDCONFIG/dr2RACTEST.dat';

System altered.

SQL> alter system set dg_broker_start=true;

DGMGRL> connect sys
Password:
Connected to "RACTEST"
Connected as SYSDBA.
DGMGRL> CREATE CONFIGURATION DG_RACTEST AS PRIMARY DATABASE IS RACTEST CONNECT IDENTIFIER IS RACTEST;
Configuration "dg_ractest" created with primary database "ractest"

DGMGRL> ADD DATABASE STBTEST AS CONNECT IDENTIFIER is STBTEST MAINTAINED AS PHYSICAL;
Database "stbtest" added

System altered.


DGMGRL> enable configuration;

DGMGRL> show configuration;

Recover Standby Database By Using RMAN Incremental Backup

By using RMAN incremental backups to synchronize a physical standby database, it is fast and easy without installing datafiles or history archive logs required.

This post demonstrates how to recover standby database with a big gap behind primary database in 11g.

Stop the managed recovery process (MRP)

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

On standby database, get the lowest SCN

SQL> SELECT CURRENT_SCN FROM V$DATABASE;

CURRENT_SCN
-----------
7.8880E+12

SQL> set numwidth 16
SQL> /

CURRENT_SCN
----------------
7888036454134

SQL> select min(fhscn) from x$kcvfh;

MIN(FHSCN)
----------------
7883734410408

SQL> select min(f.fhscn) from x$kcvfh f, v$datafile d
where f.hxfil =d.file#
and d.enabled != 'READ ONLY' ; 

MIN(F.FHSCN)
----------------
7883734410408

SQL> select CHECKPOINT_CHANGE# from v$datafile_header;

MIN(CHECKPOINT_CHANGE#)
-----------------------
7883734410408

On primary database, RMAN backup from SCN of previous step

Take a SCN backup, and copy backup onto standby server.

RMAN> BACKUP INCREMENTAL FROM SCN 7883734410408 DATABASE 
      FORMAT '/tmp/testdb_styby_%U' tag 'INCFORSTANDBY';

Prepare Standby ControlFile and New Datafiles

a) Create a standby control file from primary, and then copy onto standby database server. Replace the standby control file by using this newly created one.

-- From primary
--
SQL>ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/TESTDBSTY.ctl';

-- From Standby
--
RMAN> SHUTDOWN IMMEDIATE ;
RMAN> STARTUP NOMOUNT; 
RMAN> RESTORE STANDBY CONTROLFILE FROM '/tmp/TESTDBSTY.ctl';

b) For new datafiles which are in Primary but not in standby database, copy them from primary to standby database server. Then catalog current all standby database datafiles, since the control file has been replaced by primary one.

RMAN> CATALOG START WITH '+DG1/tetsdbsty/datafile/';

c) Switch database to copy from RMAN.

RMAN> SWITCH DATABASE TO COPY;

Recover Standby Database

Catalog incremental backup, then recover standby database by using RMAN incremental backup of primary database.

RMAN> CATALOG START WITH '/tmp/testdb_styby';

RMAN> RECOVER DATABASE NOREDO;

Start the MRP process of standby database.

SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE
     DISCONNECT FROM SESSION;

Check DataGuard Health

There is no gap between primary database and standby database now.

DGMGRL> show configuration;
Configuration - dg_oemrep
Protection Mode: MaxPerformance
Databases:
oemrep -    Primary database
stboemrep - Physical standby database

Fast-Start Failover: Disabled

Configuration Status:
SUCCESS

DGMGRL> show database "stboemrep";

Database - stboemrep

Enterprise Manager Name: OEMREP

Role:                    PHYSICAL STANDBY
Intended State:          APPLY-ON
Transport Lag:           0 seconds (computed 1 second ago)
Apply Lag:               0 seconds (computed 1 second ago)
Apply Rate:              4.93 MByte/s
Real Time Query:         OFF
Instance(s):
STBOEMREP

Database Status:
SUCCESS

DGMGRL>

ORA-16849 ORA-16816 After Uncompleted Data Guard Switchover

For some reason, DG ( Data Guard ) switchover command is incomplete with following warnings:

Database Error(s):
 ORA-16849: a previous switchover from this database was interrupted 
            and aborted
 ORA-16816: incorrect database role

After detailed checks, it is confirmed that database role has been changed successfully.

-- on old primary database
--
SQL> select open_mode, database_role from v$database;

OPEN_MODE            DATABASE_ROLE
-------------------- ----------------
MOUNTED              PHYSICAL STANDBY

-- on old standby database
--
SQL> select open_mode, database_role from v$database;

OPEN_MODE            DATABASE_ROLE
-------------------- ----------------
MOUNTED              PRIMARY

So the issues is the final role change has not been recognized by the DG broker, so we have to rebuild the Data Guard Broker Configuration when the Databases have been restarted:

DGMGRL> remove configuration;
DGMGRL> create configuration 'PRIMDB' AS PRIMARY DATABASE IS 
       'PRIMDB' CONNECT IDENTIFIER IS 'PRIMDB'; 
DGMGRL> add database 'STDBYDB' AS CONNECT IDENTIFIER IS 'STDBYDB' 
        maintained as physical;
DGMGRL> enable configuration;
DGMGRL> show configuration;
DGMGRL> show database verbose 'PRIMDB';
DGMGRL> show database verbose 'STDBYDB';

ORA-16826: apply service state is inconsistent with the DelayMins property

For DataGuard Real Time Apply”, USING “CURRENT LOGFILE” option for “ALTER DATABASE RECOVER MANAGED STANDBY DATABASE” matches property “DelayMins = ‘0’”.
Non Real Time Apply : alter database recover managed standby database disconnect from session
REAL TIME APPLY : ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION
Achieve real time apply by using online standby redo logs

OEM alert for DataGuard status is “ORA-16826: apply service state is inconsistent with the DelayMins property”.

Oracle official explanation is as below :

$oerr ora 16826
16826, 0000, "apply service state is inconsistent with the DelayMins property"
// *Cause: This warning was caused by one of the following reasons:
// - The apply service was started without specifying the real-time
// apply option or without the NODELAY option when the DelayMins
// property was set to zero.
// - The apply service was started with the real-time apply option or
// with the NODELAY option when the DelayMins property was set to
// a value greater than zero.
// *Action: Reenable the standby database to allow the broker to restart
// the apply service with the apply options that are consistent
// with the specified value of the DelayMins property.

It seems the “DelayMins” property is not matching the option used in “alter database recover managed standby database …..”. Let’s have a detail check.

DGMGRL> show configuration;
..
Protection Mode: MaxPerformance
 Databases:
 PRIMYDB - Primary database
 STDBYDB - Physical standby database
 Warning: ORA-16826: apply service state is inconsistent with the DelayMins property

Fast-Start Failover: DISABLED
Configuration Status:
WARNING

Primary database side is good, the warning is from standby database side.

DGMGRL> show database verbose "STDBYDB";

Database - STDBYDB

 Role: PHYSICAL STANDBY
 Intended State: APPLY-ON
 Transport Lag: 0 seconds (computed 0 seconds ago)
 Apply Lag: 13 minutes 1 second (computed 0 seconds ago)
 Apply Rate: 61.33 MByte/s
 Real Time Query: OFF
 Instance(s):
 STDBYDB1
 STDBYDB2 (apply instance)
 
Database Warning(s):
 ORA-16826: apply service state is inconsistent with the DelayMins property

DGMGRL> show database "STDBYDB" "DelayMins"
 DelayMins = '0'
DGMGRL>

On standby database applying instance STDBYDB2. From alert.log, we can see the standby database “not using Real Time Apply”.

...
..
alter database recover managed standby database disconnect from session
Attempt to start background Managed Standby Recovery process (STDBYDB2)
Thu Jan 12 16:00:06 2017
MRP0 started with pid=55, OS id=66078
MRP0: Background Managed Standby Recovery process started (STDBYDB2)
 started logmerger process
Thu Jan 12 16:00:11 2017
Managed Standby Recovery not using Real Time Apply
Parallel Media Recovery started with 32 slaves
Thu Jan 12 16:00:15 2017
Block change tracking file is current.
Starting background process CTWR
Thu Jan 12 16:00:15 2017
CTWR started with pid=89, OS id=66416
Block change tracking service is active.
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Media Recovery Log +FRA/stdbydb/archivelog/2017_01_12/thread_1_seq_328339.32376.933091003
Media Recovery Log +FRA/stdbydb/archivelog/2017_01_12/thread_2_seq_555739.2592.933091137
Thu Jan 12 16:00:17 2017
Completed: alter database recover managed standby database disconnect from session
...
..

Move on to any primay database instance and run below query, we can see the same — the standby database “not using Real Time Apply”

SQL> select DEST_NAME,STATUS,DATABASE_MODE,RECOVERY_MODE 
       from V$ARCHIVE_DEST_STATUS 
       where status!='INACTIVE';

DEST_NAME           STATUS      DATABASE_MODE   RECOVERY_MODE 
------------------- ----------- --------------  --------------
LOG_ARCHIVE_DEST_1  VALID       OPEN IDLE
LOG_ARCHIVE_DEST_2  VALID       MOUNTED-STANDBY MANAGED

On standby database, stop the Redo Apply and then start the redo apply in real time mode.

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Database altered.

Check standby database alert.log again, we can see standby database in “Real Time Apply” by using standby online logs:

...
..
Thu Jan 12 23:01:56 2017
Managed Standby Recovery Canceled (STDBYDB2)
Completed: alter database recover managed standby database cancel
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION
Attempt to start background Managed Standby Recovery process (STDBYDB2)
Thu Jan 12 23:02:00 2017
MRP0 started with pid=54, OS id=106637
MRP0: Background Managed Standby Recovery process started (STDBYDB2)
 started logmerger process
Thu Jan 12 23:02:05 2017
Managed Standby Recovery starting Real Time Apply
...
..
Thu Jan 12 23:02:09 2017
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION
Thu Jan 12 23:02:39 2017
Media Recovery Log +FRA/stdbydb/archivelog/2017_01_12/thread_4_seq_273573.33789.933116469
Media Recovery Waiting for thread 1 sequence 328381 (in transit)
Recovery of Online Redo Log: Thread 1 Group 101 Seq 328381 Reading mem 0
 Mem# 0: +DATA2/stdbydb/onlinelog/group_101.1037.903651087
....
...

On any primary database instance, run below sql, we can see the same — standby database in “Real Time Apply” by using standby online logs:

SQL> select DEST_NAME,STATUS,DATABASE_MODE,RECOVERY_MODE from V$ARCHIVE_DEST_STATUS where status!='INACTIVE';

DEST_NAME           STATUS      DATABASE_MODE   RECOVERY_MODE 
------------------- ----------- --------------  --------------
LOG_ARCHIVE_DEST_1  VALID       OPEN IDLE
LOG_ARCHIVE_DEST_2  VALID       MOUNTED-STANDBY REAL TIME APPLY

Finally check DataGuard configurations:

DGMGRL> show configuration;

...

Protection Mode: MaxPerformance
 Databases:
 PRIMYDB - Primary database
 STDBYDB - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> show database verbose "STDBYDB";

Database - STDBYDB

 Role: PHYSICAL STANDBY
 Intended State: APPLY-ON
 Transport Lag: 0 seconds (computed 0 seconds ago)
 Apply Lag: 0 seconds (computed 0 seconds ago)
 Apply Rate: 8.93 MByte/s
 Real Time Query: OFF
 Instance(s):
 STDBYDB1
 STDBYDB2 (apply instance)
 
Database Warning(s):
SUCCESS