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
