Step by Step Create an Oracle Physical standby database

Standby database can act as failover for critical Production databases. When Production database crashes application can quickly switchover to Standby databases.

There are two types of Standby databases , physical standby and logical standby.

Requirements :-

1. Two databases one for Primary and one for standby.
2. Both databases have network connectivity between them.
3. Oracle Databse 10gR1 have to be installed on both Database servers.
4. Both Database have to be in Archivelog mode.

Values used in this Example :-

1. Primary database name is TEST.
2. Standby database name is STBY.
3. Primary database hostname is primary_server and standby database hostname is standby_server

PFB steps to create a physical standby database for a primary oracle database :-

1. Preparing Primary database for standby database creation :-

[oracle@new TEST]$ export ORACLE_SID=TEST

[oracle@new TEST]$ sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jul 18 20:15:18 2013

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

Enter user-name: /as sysdba

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select name from v$database;

NAME
---------
TEST

a. Check log mode of primary database , if not in Archive mode then change it to Archive mode . Link to change database in archivelog mode Enable Archive mode :-

SQL> SELECT log_mode FROM v$database;

LOG_MODE
------------
ARCHIVELOG

b. Change primary database to Force Logging mode :-

SQL> ALTER DATABASE FORCE LOGGING;

Database altered.

c. Initialization parameters in Primary database :-

SQL>  show parameter db_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      TEST
SQL> show parameter db_unique_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      TEST

d. Set the DG_CONFIG setting of LOG_ARCHIVE_CONFIG parameter. for this DB_UNIQUE_NAME of standby database has to be different from primary.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(TEST,STBY)';

System altered.

e. Set suitable remote archivelog destinations. 

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

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

System altered.

-- NOTE :- Service and DB_UNIQUE_NAME has to be of Standby database

f. Set parameters related to LOG_ARCHIVE and password file parameters :-


SQL> ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE;

System altered.

SQL> ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=30;

System altered.

SQL> ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;

System altered.

g. It is recommended to make sure that primary can switch roles to become a standby. For that we need to set some -convert parameters if there are path differences between the servers :-

SQL> ALTER SYSTEM SET FAL_SERVER=STBY;

System altered.

SQL> ALTER SYSTEM SET DB_FILE_NAME_CONVERT='STBY','TEST' SCOPE=SPFILE;

System altered.

SQL> ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='STBY','TEST'  SCOPE=SPFILE;

System altered.

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

System altered.

h . As some parameters cannot be changed while database is open , we need to restart database to before they take effect :-

SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup
ORACLE instance started.

Total System Global Area  960495616 bytes
Fixed Size                  1222816 bytes
Variable Size             247465824 bytes
Database Buffers          700448768 bytes
Redo Buffers               11358208 bytes
Database mounted.
Database opened.
SQL>

i . In both servers entries of Primary as well as standby are needed in tnsnames.ora file and we are able to do  tnsping.

###################ORACLE_DATA_GAURD############################

TEST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = primary_server)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TEST)
    )
  )

STBY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = standby_server)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = STBY)
    )
  )
###################ORACLE_DATA_GAURD############################


[oracle@new admin]$ tnsping STBY

TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 18-JUL-2013 20:23:36

Copyright (c) 1997, 2005, Oracle.  All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = standby_server)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = STBY)))
OK (0 msec)
[oracle@new admin]$ tnsping TEST

TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 18-JUL-2013 20:23:41

Copyright (c) 1997, 2005, Oracle.  All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = primary_server)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = TEST)))
OK (0 msec)

j. Backup primary database . PFB link to take backup 
Backup of Oracle database

k. Create Standby controlfile and Pfile :-

SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/disk1/stby1.ctl';

Database altered.

SQL> CREATE PFILE='/disk1/stby1.ora' from spfile;

File created.

l. Add standby redo logs to primary database :-

ALTER DATABASE ADD STANDBY LOGFILE ('/disk1/TEST/TEST/standby_redo01.log') SIZE 50M;

ALTER DATABASE ADD STANDBY LOGFILE ('/disk1/TEST/TEST/standby_redo02.log') SIZE 50M;

ALTER DATABASE ADD STANDBY LOGFILE ('/disk1/TEST/TEST/standby_redo03.log') SIZE 50M;

ALTER DATABASE ADD STANDBY LOGFILE ('/disk1/TEST/TEST/standby_redo04.log') SIZE 50M;

2. Preparing Standby database for physical standby creation :-

a. Copy backup of primary database, archivelogs, standby controlfile, parameter file and passwordfile to standby database server. 

copy all backup and archive logs from primary to standby

[oracle@primary_server]$ pwd

/disk1/STBY/flash_recovery_area/TEST

[oracle@primary_server]$ ls

archivelog  autobackup  backupset  onlinelog

[oracle@primary_server]$ scp -r * oracle@standby_server:/disk1/TEST/flash_recovery_area/TEST

copy standby controlfile, parameter file and passwordfile

[oracle@standby_server TEST]$ scp oracle@primary_server:/disk1/stby.ctl /disk1/TEST/TEST/control01.ctl

oracle@primary_server's password:

stby.ctl                                                                                                                              100% 6896KB   6.7MB/s   00:00

[oracle@ standby_server  TEST]$

[oracle@ standby_server  TEST]$ cp control01.ctl control02.ctl

[oracle@ standby_server  TEST]$ cp control01.ctl control03.ctl

like this copy parameter file as well as password file to standby server.

b. Edit the standby parameter file according to the standby destinations. The parameters in green have to be change according to Standby server :-

*.audit_file_dest='/disk2/oracle/admin/STBY/adump'

*.background_dump_dest='/disk2/oracle/admin/STBY/bdump'

*.compatible='10.2.0.1.0'

*.control_files='/disk1/STBY/STBY/control01.ctl','/disk1/STBY/STBY/control02.ctl','/disk1/STBY/STBY/control03.ctl'

*.core_dump_dest='/disk2/oracle/admin/STBY/cdump'

*.db_block_size=8192

*.db_domain='local'

*.db_file_multiblock_read_count=16

*.db_file_name_convert='STBY','TEST'

*.db_name='TEST'

*.db_unique_name='STBY'

*.db_recovery_file_dest='/disk1/STBY/flash_recovery_area'

*.db_recovery_file_dest_size=2147483648

*.dispatchers='(PROTOCOL=TCP) (SERVICE=TESTXDB)'

*.fal_server='TEST'

*.job_queue_processes=10

*.log_archive_config='DG_CONFIG=(TEST,STBY)'

*.log_archive_dest_2='SERVICE=TEST NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=TEST'

*.log_archive_dest_state_2='ENABLE'

*.log_archive_format='%t_%s_%r.arc'

*.log_archive_max_processes=30

*.log_file_name_convert='STBY','TEST'

*.open_cursors=300

*.pga_aggregate_target=318767104

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=958398464

*.standby_file_management='AUTO'

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

*.user_dump_dest='/disk2/oracle/admin/STBY/udump'

c. Start the standby database using the standby parameter file restore the backup of primary database

SQL> startup nomount pfile='/disk1/stby.ora'

ORACLE instance started.

Total System Global Area  960495616 bytes

Fixed Size                  1222816 bytes

Variable Size             264243040 bytes

Database Buffers          683671552 bytes

Redo Buffers               11358208 bytes

SQL> create spfile from pfile;

SQL> shu immediate

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup nomount

ORACLE instance started.

Total System Global Area  960495616 bytes

Fixed Size                  1222816 bytes

Variable Size             264243040 bytes

Database Buffers          683671552 bytes

Redo Buffers               11358208 bytes

[oracle@new dbs]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Tue Jul 23 21:17:15 2013

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

connected to target database: TEST (not mounted)

RMAN> alter database mount;

database mounted

RMAN> catalog start with ' /disk1/STBY/flash_recovery_area/STBY/2013_07_25/o1 ';


Starting implicit crosscheck backup at 25-JUL-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=58 device type=DISK
Crosschecked 4 objects
Finished implicit crosscheck backup at 25-JUL-13

Starting implicit crosscheck copy at 25-JUL-13
using channel ORA_DISK_1
Finished implicit crosscheck copy at 25-JUL-13

searching for all files in the recovery area
cataloging files...
no files cataloged

searching for all files that match the pattern /disk1/STBY/flash_recovery_area/STBY/2013_07_25/o1

List of Files Unknown to the Database
=====================================
File Name: /disk1/STBY/flash_recovery_area/STBY/2013_07_25/o1_mf_1_10_8z1gq2o5_.arc
File Name: /disk1/STBY/flash_recovery_area/STBY/2013_07_25/o1_mf_1_5_8z1gdcos_.arc
File Name: /disk1/STBY/flash_recovery_area/STBY/2013_07_25/o1_mf_1_6_8z1gdgnr_.arc
File Name: /disk1/STBY/flash_recovery_area/STBY/2013_07_25/o1_mf_1_7_8z1gks5p_.arc
File Name: /disk1/STBY/flash_recovery_area/STBY/2013_07_25/o1_mf_1_8_8z1gkw29_.arc
File Name: /disk1/STBY/flash_recovery_area/STBY/2013_07_25/o1_mf_1_9_8z1gok73_.arc
File Name: /disk1/STBY/flash_recovery_area/STBY/2013_07_25/o1_mf_annnn_TAG20130725T111209_8z1gol3o_.bkp
File Name: /disk1/STBY/flash_recovery_area/STBY/2013_07_25/o1_mf_annnn_TAG20130725T111259_8z1gq36b_.bkp
File Name: /disk1/STBY/flash_recovery_area/STBY/2013_07_25/o1_mf_ncsnf_TAG20130725T111211_8z1gq1js_.bkp
File Name: /disk1/STBY/flash_recovery_area/STBY/2013_07_25/o1_mf_nnndf_TAG20130725T111211_8z1gomb4_.bkp

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /disk1/STBY/flash_recovery_area/STBY/2013_07_25/o1_mf_1_10_8z1gq2o5_.arc
File Name: /disk1/STBY/flash_recovery_area/STBY/2013_07_25/o1_mf_1_5_8z1gdcos_.arc
File Name: /disk1/STBY/flash_recovery_area/STBY/2013_07_25/o1_mf_1_6_8z1gdgnr_.arc
File Name: /disk1/STBY/flash_recovery_area/STBY/2013_07_25/o1_mf_1_7_8z1gks5p_.arc
File Name: /disk1/STBY/flash_recovery_area/STBY/2013_07_25/o1_mf_1_8_8z1gkw29_.arc
File Name: /disk1/STBY/flash_recovery_area/STBY/2013_07_25/o1_mf_1_9_8z1gok73_.arc
File Name: /disk1/STBY/flash_recovery_area/STBY/2013_07_25/o1_mf_annnn_TAG20130725T111209_8z1gol3o_.bkp
File Name: /disk1/STBY/flash_recovery_area/STBY/2013_07_25/o1_mf_annnn_TAG20130725T111259_8z1gq36b_.bkp
File Name: /disk1/STBY/flash_recovery_area/STBY/2013_07_25/o1_mf_ncsnf_TAG20130725T111211_8z1gq1js_.bkp
File Name: /disk1/STBY/flash_recovery_area/STBY/2013_07_25/o1_mf_nnndf_TAG20130725T111211_8z1gomb4_.bkp

 RMAN>  run

2> {

3> set newname for datafile 1 to '/disk1/STBY/STBY/system01.dbf';

4> set newname for datafile 2 to '/disk1/STBY/STBY/undotbs01.dbf';

5> set newname for datafile 3 to '/disk1/STBY/STBY/sysaux01.dbf';

6> set newname for datafile 4 to '/disk1/STBY/STBY/users01.dbf';

7> restore database;

8> switch datafile all;

9> }

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 25-JUL-13
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /disk1/STBY/STBY/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /disk1/STBY/STBY/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /disk1/STBY/STBY/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /disk1/STBY/STBY/users01.dbf
channel ORA_DISK_1: reading from backup piece /disk1/STBY/flash_recovery_area/STBY/2013_07_25/o1_mf_nnndf_TAG20130725T111211_8z1gomb4_.bkp
channel ORA_DISK_1: piece handle=/disk1/STBY/flash_recovery_area/STBY/2013_07_25/o1_mf_nnndf_TAG20130725T111211_8z1gomb4_.bkp tag=TAG20130725T111211
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 25-JUL-13

datafile 1 switched to datafile copy
input datafile copy RECID=5 STAMP=821709808 file name=/disk1/STBY/STBY/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=6 STAMP=821709808 file name=/disk1/STBY/STBY/undotbs01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=7 STAMP=821709808 file name=/disk1/STBY/STBY/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=8 STAMP=821709808 file name=/disk1/STBY/STBY/users01.dbf

d. Create Online redo logs for Standby server :-

ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;

ALTER DATABASE ADD LOGFILE ('/disk1/STBY/STBY/online_redo01.log') SIZE 50M;

ALTER DATABASE ADD LOGFILE ('/disk1/STBY/STBY/online_redo02.log') SIZE 50M;

ALTER DATABASE ADD LOGFILE ('/disk1/STBY/STBY/online_redo03.log') SIZE 50M;

ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

e. Before Starting the apply process we have to make necessary changes in tnsnames.ora file, as we restored primary to standby , dbname of primary has been changed from STBY to TEST


###################ORACLE_DATA_GAURD############################

TEST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = primary_server)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TEST)
    )
  )

STBY =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = standby_server)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TEST)
    )
  )
###################ORACLE_DATA_GAURD############################

[oracle@new admin]$ tnsping STBY

TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 18-JUL-2013 20:23:36

Copyright (c) 1997, 2005, Oracle.  All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = standby_server)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = TEST)))
OK (0 msec)
[oracle@new admin]$ tnsping TEST

TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 18-JUL-2013 20:23:41

Copyright (c) 1997, 2005, Oracle.  All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = primary_server)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = TEST)))
OK (0 msec)

From primary as well as standby try to connect both database using Service name:-

[oracle@new admin]$sqlplus sys/test@TEST as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jul 18 20:15:18 2013

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

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

[oracle@new admin]$sqlplus sys/test@STBY as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jul 18 20:15:18 2013

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

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

--NOTE :- Before starting the apply process , test from both servers that we are able to connect both primary and standby database using password file. Once this done we can start the redo apply process.

f. Once all steps previous to this completes we can start the apply process :-

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

once we fire this command we get below mentioned output in alert log file of standby server :-

Tue Jul 23 14:51:35 2013

Attempt to start background Managed Standby Recovery process (TEST)

MRP0 started with pid=49, OS id=12659

Tue Jul 23 14:51:35 2013

MRP0: Background Managed Standby Recovery process started (TEST)

Managed Standby Recovery not using Real Time Apply

Clearing online redo logfile 1 /disk1/STBY/STBY/redo01.log

Clearing online log 1 of thread 1 sequence number 8

Clearing online redo logfile 1 complete

Clearing online redo logfile 2 /disk1/STBY/STBY/redo02.log

Clearing online log 2 of thread 1 sequence number 9

Clearing online redo logfile 2 complete

Clearing online redo logfile 3 /disk1/STBY/STBY/redo03.log

Clearing online log 3 of thread 1 sequence number 10

Tue Jul 23 14:51:41 2013

Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION

Tue Jul 23 14:51:41 2013

Clearing online redo logfile 3 complete

Media Recovery Waiting for thread 1 sequence 11

f. Test the Redo Transport from primary database to standby database :-

On Primary Server :-

SQL> ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';

Session altered.

SQL> SELECT sequence#, first_time, next_time

FROM   v$archived_log

ORDER BY sequence#;  2    3

 SEQUENCE# FIRST_TIME           NEXT_TIME

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

     

         8 19-JUL-2013 23:30:42 23-JUL-2013 14:26:31

         9 23-JUL-2013 14:26:31 23-JUL-2013 14:27:12

        10 23-JUL-2013 14:26:31 23-JUL-2013 14:27:12

3 rows selected.

SQL> ALTER SYSTEM SWITCH LOGFILE;

System altered.

On Standby Server :-

SQL> SELECT sequence#, first_time, next_time, applied

FROM   v$archived_log

ORDER BY sequence#;

 SEQUENCE# FIRST_TIME           NEXT_TIME    APP

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

         10  23-JUL-13                      23-JUL-13         YES

         11 23-JUL-13                      23-JUL-13         YES
         12 23-JUL-13                      23-JUL-13         YES
         13 23-JUL-13                      23-JUL-13         YES

once the applied process starts and redo is transported from Primary to Standby then you will get below mentioned output in Alert log file :-

RFS[3]: Identified database type as 'physical standby'

RFS[3]: Successfully opened standby log 4: '/disk1/STBY/STBY/standby_redo01.log'

Tue Jul 23 15:01:41 2013

Media Recovery Log /disk1/TEST/flash_recovery_area/TEST/archivelog/2013_07_23/o1_mf_1_12_8ywmcxdg_.arc

Media Recovery Waiting for thread 1 sequence 13

Tue Jul 23 15:57:41 2013

RFS[1]: Successfully opened standby log 4: '/disk1/STBY/STBY/standby_redo01.log'

Tue Jul 23 15:57:42 2013

Media Recovery Log /disk1/TEST/flash_recovery_area/TEST/archivelog/2013_07_23/o1_mf_1_13_8ywpnxyz_.arc

Media Recovery Waiting for thread 1 sequence 14

g. Check that Primary and Standby are in SYNC :- 

in Primary :-

SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

no rows selected


SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
  2  FROM
  3  (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
  4  (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
  5  WHERE
  6  ARCH.THREAD# = APPL.THREAD#
  7  ORDER BY 1;

    Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
         1                     27                    27          0
         1                     27                    27          0

in Standy :-

SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

no rows selected

Output of both has to be "no rows selected"


SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
FROM
  2    3  (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
  4  (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
  5  WHERE
  6  ARCH.THREAD# = APPL.THREAD#
  7  ORDER BY 1;

    Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
         1                      6                     6          0

Difference has to be zero.

Standby database has been successfully created and redo transported as well as applied from primary to Standby database.

To recover Primary and Standby database from failover scenarios its better to keep them in FLASHBACK mode.

I hope this article helped you.

Regards,

Amit Rath