Here i created a physical standby database standby for RAC database TEST
instance names TEST1 and TEST2.
|
PRIMARY(2node
Rac)
|
STANDBY
|
IP
|
192.168.1.151
192.168.1.152
|
192.168.1.161
|
HOSTNAME
|
rac1-pub.dba.com
rac2-pub.dba.com
|
standby161.dba.com
|
DB
NAME
|
TEST
|
TEST
|
DB
UNIQUE NAME
|
TEST
|
STANDBY
|
DATAFILE
PATH
|
+DATA/TEST/DATAFILE
|
/u01/standby/datafile
|
LOGFILE
PATH
|
+DATA/TEST/ONLINELOG
|
/u01/standby/onlinelog
|
|
|
|
We assume:
·
There is an existing RAC database (TEST) on two nodes:
rac1-pub and rac2-pub.
·
The standby server has no existing database.
·
The Oracle software 11gr2
is installed at standby site
·
The Primary database is running in ARCHIVELOG mode
Steps in primary database Servers:
- ·
Database
must in Archivelog mode.
- ·
Enable
Force Logging.
- ·
Create
the Standby Redo logs.
- ·
Create
a password file for all instances ( Password must same in all nodes).
- ·
Update
listener.ora file to include SID information on each node in cluster. Verify
cluster Service is available.
- ·
Verify
TNSNAMES.ORA file.
- ·
Modify
init.ora Parameters For DataGuard Configuration.
- ·
Create
temporary directory to hold the RMAN backup of this database.
- ·
Backup
the Primary Database for Standby.
- ·
Copy
the Primary Database backup to the standby database server to create standby
database.
Steps in Standby database server:
- ·
Create
required folders and directories.
- ·
Modify
the init.ora parameter on standby database.
- ·
create
a password file(Password must same as primary database instances)
- ·
Update
listener.ora file to include SID information
- ·
Add
required TNS entries for standby database on all servers.
- ·
Verify
TNSNAMES.ORA file.
- ·
Create
standby database.
1.
ARCHIVELOG MODE
·
Primary
database should be in archivelog mode. Enable archivelog mode if it is not done
already.
2. FORCE
LOGGING
·
Force
logging should be enabled in primary database.
SQL>
select instance_name,status from gv$instance;
INSTANCE_NAME STATUS
----------------
------------
TEST1 OPEN
TEST2 OPEN
SQL>
select name, db_unique_name , database_role from gv$database;
NAME DB_UNIQUE_NAME DATABASE_ROLE
---------
------------------------------ ----------------
TEST TEST PRIMARY
TEST TEST PRIMARY
SQL>
select name, force_logging from v$database;
NAME FOR
---------
---
TEST NO
SQL>
alter database force logging;
Database
altered.
SQL>
select name, force_logging from v$database;
NAME FOR
---------
---
TEST YES
3. TNS SETTINGS
·
Both primary and standby servers must be able to tnsping each
other.
PRIMARY (TEST):
STANDBYTNS =
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP)(HOST = standby161.dba.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER =
DEDICATED)
(SERVICE_NAME =
standby)
standby:
TEST =
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP)(HOST = scan)(PORT = 1521))
(CONNECT_DATA =
(SERVER =
DEDICATED)
(SERVICE_NAME =
TEST)
CREATE lisneter.ora
[STANDBY]
SID_LIST_STANDBYLIST =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME =
standby)
(ORACLE_HOME =
/u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME =
standby)
)
)
ADR_BASE_STANDBYLIST = /u01/app/oracle
STANDBYLIST =
(DESCRIPTION =
(ADDRESS =
(PROTOCOL = TCP)(HOST = standby161)(PORT = 1521))
)
4.
STANDBY LOG FILEs
·
The size
of the standby logfile must be the same with the online redo logs.
·
There
should be at least one more standby redo log group per thread than the online
redo logs.
SQL> select MEMBERS, STATUS,
GROUP# from v$log;
MEMBERS STATUS GROUP#
---------- ----------------
----------
2 INACTIVE 1
2 CURRENT 2
2 CURRENT 3
2 INACTIVE 4
SQL> select group#,bytes from
v$Log;
GROUP#
BYTES
---------- ----------
1
52428800
2
52428800
3
52428800
4
52428800
SQL> col member for a56
SQL> select
group#,member,type from v$logfile order by 1;
GROUP# MEMBER
TYPE
----------
-------------------------------------------------------- -------
1 +FRA/test/onlinelog/group_1.286.941820983 ONLINE
1
+DATA/test/onlinelog/group_1.277.941820977 ONLINE
2
+FRA/test/onlinelog/group_2.287.941820991 ONLINE
2
+DATA/test/onlinelog/group_2.278.941820987 ONLINE
3
+DATA/test/onlinelog/group_3.281.941821451 ONLINE
3
+FRA/test/onlinelog/group_3.288.941821457 ONLINE
4
+DATA/test/onlinelog/group_4.282.941821463 ONLINE
4
+FRA/test/onlinelog/group_4.289.941821467 ONLINE
8 rows selected.
SQL> alter database add
standby logfile thread 1
group 5 size 5M,
group 6 size 5M; 2 3
Database altered.
SQL> alter database add
standby logfile thread 2
group 7 size 5M,
group 8 size 5M; 2 3
Database altered.
SQL> select group#,member,type from v$logfile
order by 1;
GROUP# MEMBER
TYPE
----------
-------------------------------------------------------- -------
1
+DATA/test/onlinelog/group_1.277.941820977 ONLINE
1
+FRA/test/onlinelog/group_1.286.941820983 ONLINE
2
+DATA/test/onlinelog/group_2.278.941820987 ONLINE
2
+FRA/test/onlinelog/group_2.287.941820991 ONLINE
3
+DATA/test/onlinelog/group_3.281.941821451 ONLINE
3
+FRA/test/onlinelog/group_3.288.941821457 ONLINE
4 +DATA/test/onlinelog/group_4.282.941821463 ONLINE
4
+FRA/test/onlinelog/group_4.289.941821467 ONLINE
5
+DATA/test/onlinelog/group_5.264.941822245 STANDBY
5
+FRA/test/onlinelog/group_5.256.941822245 STANDBY
6
+DATA/test/onlinelog/group_6.259.941822245 STANDBY
GROUP# MEMBER
TYPE
----------
-------------------------------------------------------- -------
6 +FRA/test/onlinelog/group_6.282.941822247 STANDBY
7
+DATA/test/onlinelog/group_7.258.941822269 STANDBY
7
+FRA/test/onlinelog/group_7.281.941822269 STANDBY
8 +DATA/test/onlinelog/group_8.257.941822269 STANDBY
8
+FRA/test/onlinelog/group_8.280.941822269 STANDBY
16 rows selected.
5.
PRIMARY PARAMETERS
·
Change
the following dataguard related parameters in primary.
[PRIMARY] [on any node run once]
SQL> alter system set
LOG_ARCHIVE_CONFIG='DG_CONFIG=(test,standby)' sid='*' scope=both;
SQL> alter system set
LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST
VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=test' sid='*'
scope=both;
SQL> alter system set
LOG_ARCHIVE_DEST_2='SERVICE=standbytns LGWR ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby' sid='*'
scope=both;
SQL> alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE
sid='*' scope=both;
SQL> alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE
sid='*' scope=both;
SQL> alter system set FAL_SERVER='standbytns' sid='*'
scope=both;
SQL> alter system set FAL_CLIENT='test' sid='*'
scope=both;
SQL> alter system set log_archive_max_processes=10 sid='*'
scope=both;
SQL> alter system set log_file_name_convert='/u01/standby/onlinelog','+DATA/TEST/DATAFILE'
sid='*' scope=spfile;
SQL> alter system set db_file_name_convert=
'/u01/standby/datafile','+DATA/TEST/ONLINELOG' sid='*' scope=spfile;
SQL> alter system set REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
sid='*' scope=spfile;
SQL> alter system set STANDBY_FILE_MANAGEMENT=AUTO sid='*' scope=both;
SQL> alter system set DB_UNIQUE_NAME=TEST;
Bounce the db nodes for
some of the parameters to be
activated.
6.
PASSWORD FILE
·
Copy the
primary database’s password file to standby under $ORACLE_HOME/dbs
[PRIMARY] [on any node run once]
[oracle@rac2-pub dbs]$ ls
hc_PROD2.dat
hc_TEST2.dat init.ora initTEST2.ora
orapwTEST2
[STANDBY]
[oracle@standby161 dbs]$ ls
init.ora orapwTEST2
[oracle@standby161 dbs]$ mv orapwTEST2 orapwSTANDBY
7. Take backup
(Primary side)
·
First connect to rman target /
·
Backup database format ‘/location/bkp_database%U’;
·
Backup archivelog all format
·
Backup current controlfile for standby format
8. PFILE
·
Create
pfile on standby
[STANDBY]
[oracle@standby161
dbs]$ cat initstandby.ora
*.audit_file_dest='/u01/app/oracle/admin/standby/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='11.2.0.4.0'
*.control_files='/u01/standby/controlfile.ctl'
*.db_block_size=8192
*.db_create_file_dest='/u01/standby/datafile'
*.db_domain=''
*.db_file_name_convert='+DATA/TEST/DATAFILE','/u01/standby/datafile'
*.db_name='TEST'
*.db_recovery_file_dest='/u01/standby/fra'
*.db_recovery_file_dest_size=10645143552
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP)
(SERVICE=STANDBYXDB)'
*.fal_server='test'
*.fal_client='standbytns'
*.log_archive_config='DG_CONFIG=(standby,test)'
*.log_archive_dest_1='LOCATION=/u01/standby/arch'
*.log_archive_dest_2='SERVICE=test
LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=test'
*.DB_UNIQUE_NAME='standby'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_format='%t_%s_%r.dbf'
*.log_archive_max_processes='10'
*.log_file_name_convert='+DATA/TEST/ONLINELOG','/u01/standby/onlinelog'
*.memory_target=834666496
*.open_cursors=300
*.processes=150
*.remote_listener='scan:1521'
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS'
*.cluster_database='false '
9. DUPLICATING THE PRIMARY ONTO STANDBY
SQL> startup nomount;
ORACLE instance started.
Total System Global Area
830930944 bytes
Fixed Size
2257800 bytes
Variable Size
536874104 bytes
Database Buffers
289406976 bytes
Redo Buffers
2392064 bytes
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition
Release 11.2.0.4.0 - 64
With the Partitioning, OLAP, Data Mining and Real Application
Testing options
[oracle@standby161 dbs]$ clear
[oracle@standby161 dbs]$ rman target sys/manager@test
auxiliary /
Recovery Manager: Release 11.2.0.4.0 - Production on Mon Apr
24 14:58:18 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: TEST (DBID=2239967401)
connected to auxiliary database: TEST (not mounted)
RMAN> duplicate target database for standby
nofilenamecheck;
Starting Duplicate Db at 24-APR-17
using target database control file instead of recovery
catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=19 device type=DISK
contents of Memory Script:
{
restore clone standby
controlfile;
}
executing Memory Script
Starting restore at 24-APR-17
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece
/u01/backup/bkp_contrl0is2g7u6
channel ORA_AUX_DISK_1: piece
handle=/u01/backup/bkp_contrl0is2g7u6_1_1 tag=TAG2
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time:
00:00:08
output file name=/u01/standby/controlfile.ctl
Finished restore at 24-APR-17
contents of Memory Script:
{
sql clone 'alter
database mount standby database';
}
executing Memory Script
sql statement: alter database mount standby database
RMAN-05529: WARNING: DB_FILE_NAME_CONVERT resulted in invalid
ASM names; names c
contents of Memory Script:
{
set newname for
tempfile 1 to
"+data";
switch clone
tempfile all;
set newname for
datafile 1 to
"/u01/standby/datafile/system.272.941820785";
set newname for
datafile 2 to
"/u01/standby/datafile/sysaux.273.941820787";
set newname for
datafile 3 to
"/u01/standby/datafile/undotbs1.274.941820787";
set newname for
datafile 4 to
"/u01/standby/datafile/users.275.941820787";
set newname for
datafile 5 to
"/u01/standby/datafile/undotbs2.280.941821093";
restore
clone database
;
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to +data in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 24-APR-17
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore
from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to
/u01/standby/datafile/system
channel ORA_AUX_DISK_1: restoring datafile 00002 to
/u01/standby/datafile/sysaux
channel ORA_AUX_DISK_1: restoring datafile 00003 to
/u01/standby/datafile/undotb
channel ORA_AUX_DISK_1: restoring datafile 00004 to
/u01/standby/datafile/users.
channel ORA_AUX_DISK_1: restoring datafile 00005 to
/u01/standby/datafile/undotb
channel ORA_AUX_DISK_1: reading from backup piece
/u01/backup/database_0fs2g71t_
channel ORA_AUX_DISK_1: piece
handle=/u01/backup/database_0fs2g71t_1_1 tag=TAG20
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time:
00:01:35
Finished restore at 24-APR-17
contents of Memory Script:
{
switch clone datafile
all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=5 STAMP=942159673 file
name=/u01/standby/datafile/syst
datafile 2 switched to datafile copy
input datafile copy RECID=6 STAMP=942159673 file
name=/u01/standby/datafile/sysa
datafile 3 switched to datafile copy
input datafile copy RECID=7 STAMP=942159673 file
name=/u01/standby/datafile/undo
datafile 4 switched to datafile copy
input datafile copy RECID=8 STAMP=942159673 file
name=/u01/standby/datafile/user
datafile 5 switched to datafile copy
input datafile copy RECID=9 STAMP=942159673 file
name=/u01/standby/datafile/undo
Finished Duplicate Db at 24-APR-17
10. START
DATAGUARD RECOVERY
[STANDBY]
SQL> shut immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area
830930944 bytes
Fixed Size
2257800 bytes
Variable Size
536874104 bytes
Database Buffers
289406976 bytes
Redo Buffers
2392064 bytes
SQL> alter database mount standby database;
Database altered.
SQL> alter database recover managed standby database
disconnect from session;
Database altered.
[STANDBY]
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/standby/arch
Oldest online log sequence 48
Next log sequence to archive 0
Current log sequence 49
PRIMARY
(TEST)
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 48
Next log sequence to archive 49
Current log sequence 49
11.
CONTROLS
CONTROL
LOGS
·
If redo
logs are being applied correctly, we should see IN-MEMORY phrased instead of
NO. Switch the logfile of the primary database and observe the change
insequence #.
[STANDBY]
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME,APPLIED FROM
V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# FIRST_TIM
NEXT_TIME APPLIED
---------- --------- --------- ---------
32 24-APR-17
24-APR-17 YES
33 24-APR-17
24-APR-17 YES
33 24-APR-17
24-APR-17 NO
34 24-APR-17
24-APR-17 NO
34 24-APR-17
24-APR-17 NO
35 24-APR-17
24-APR-17 NO
35 24-APR-17
24-APR-17 NO
36 24-APR-17
24-APR-17 NO
36 24-APR-17
24-APR-17 NO
37 24-APR-17
24-APR-17 NO
37 24-APR-17
24-APR-17 NO
SEQUENCE# FIRST_TIM
NEXT_TIME APPLIED
---------- --------- --------- ---------
38 24-APR-17
24-APR-17 NO
38 24-APR-17
24-APR-17 NO
39 24-APR-17
24-APR-17 NO
39 24-APR-17
24-APR-17 NO
40 24-APR-17
24-APR-17 NO
40 24-APR-17
24-APR-17 NO
41 24-APR-17
24-APR-17 NO
41 24-APR-17
24-APR-17 NO
42 24-APR-17
24-APR-17 NO
42 24-APR-17
24-APR-17 NO
43 24-APR-17
24-APR-17 NO
SEQUENCE# FIRST_TIM
NEXT_TIME APPLIED
---------- --------- --------- ---------
43 24-APR-17
24-APR-17 NO
44 24-APR-17
24-APR-17 NO
44 24-APR-17
24-APR-17 NO
45 24-APR-17
24-APR-17 NO
45 24-APR-17
24-APR-17 NO
46 24-APR-17
24-APR-17 NO
46 24-APR-17
24-APR-17 NO
47 24-APR-17
24-APR-17 NO
47 24-APR-17
24-APR-17 NO
48 24-APR-17
24-APR-17 NO
48 24-APR-17 24-APR-17 NO
33 rows selected.
SQL> SELECT * FROM
V$STANDBY_LOG;
GROUP# DBID
THREAD# SEQUENCE#
---------- ----------------------------------------
---------- ----------
BYTES BLOCKSIZE USED ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- --- ---------- -------------
---------
NEXT_CHANGE# NEXT_TIME LAST_CHANGE# LAST_TIME
------------ --------- ------------ ---------
5
UNASSIGNED 1 0
5242880 512 0 YES UNASSIGNED
6
UNASSIGNED 1 0
5242880 512 0 YES UNASSIGNED
GROUP# DBID
THREAD# SEQUENCE#
---------- ----------------------------------------
---------- ----------
BYTES BLOCKSIZE USED ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- --- ---------- -------------
---------
NEXT_CHANGE# NEXT_TIME LAST_CHANGE# LAST_TIME
------------ --------- ------------ ---------
7
UNASSIGNED 2 0
5242880 512 0 NO
UNASSIGNED
8
UNASSIGNED 2 0
5242880 512 0 YES UNASSIGNED
GROUP# DBID
THREAD# SEQUENCE#
---------- ----------------------------------------
---------- ----------
BYTES BLOCKSIZE USED ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- --- ---------- -------------
---------
NEXT_CHANGE# NEXT_TIME LAST_CHANGE# LAST_TIME
------------ --------- ------------ --------
12. GAP CONTROL
·
The gap query should
return no-rows.
[STANDBY]
SQL> SELECT * FROM
V$ARCHIVE_GAP;
no rows selected
13. CROSSCHECK THE SEQUENCES
[PRIMARY]
SQL> SELECT SEQUENCE# FROM V$LOG WHERE
STATUS='CURRENT';
SEQUENCE#
----------
49
49
[STANDBY]
SQL> SELECT SEQUENCE# FROM V$LOG WHERE
STATUS='CURRENT';
SEQUENCE#
----------
49
49
14. Post-installation steps
1.
Connect the standby database using SQL*Plus and check for
the database role and status to ensure the database role is the physical
standby.
SQL> select db_unique_name,database_role,open_mode
from
v$database;
DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE
------------------------------
---------------- --------------------
standby PHYSICAL STANDBY
MOUNTED
2.
Use the v$logfile view to check the location of online
and standby redo logfiles in the standby database.
SQL> select group#,type,member
from v$logfile;
GROUP# TYPE
---------- -------
MEMBER
--------------------------------------------------------------------------------
2 ONLINE
/u01/standby/onlinelog/group_2.278.941820987
2 ONLINE
/u01/standby/datafile/TEST/onlinelog/o1_mf_2_dhvkh6oz_.log
1 ONLINE
/u01/standby/onlinelog/group_1.277.941820977
GROUP# TYPE
---------- -------
MEMBER
--------------------------------------------------------------------------------
1 ONLINE
/u01/standby/datafile/TEST/onlinelog/o1_mf_1_dhvkh2h1_.log
3 ONLINE
/u01/standby/onlinelog/group_3.281.941821451
3 ONLINE
/u01/standby/datafile/TEST/onlinelog/o1_mf_3_dhvkh9w6_.log
GROUP# TYPE
---------- -------
MEMBER
--------------------------------------------------------------------------------
4 ONLINE
/u01/standby/onlinelog/group_4.282.941821463
4 ONLINE
/u01/standby/datafile/TEST/onlinelog/o1_mf_4_dhvkhhn7_.log
5 STANDBY
/u01/standby/onlinelog/group_5.264.941822245
GROUP# TYPE
---------- -------
MEMBER
--------------------------------------------------------------------------------
5 STANDBY
/u01/standby/datafile/TEST/onlinelog/o1_mf_5_dhvkhox4_.log
6 STANDBY
/u01/standby/onlinelog/group_6.259.941822245
6 STANDBY
/u01/standby/datafile/TEST/onlinelog/o1_mf_6_dhvkhqbv_.log
GROUP# TYPE
---------- -------
MEMBER
--------------------------------------------------------------------------------
7 STANDBY
/u01/standby/onlinelog/group_7.258.941822269
7 STANDBY
/u01/standby/datafile/TEST/onlinelog/o1_mf_7_dhvkhqtb_.log
8 STANDBY
/u01/standby/onlinelog/group_8.257.941822269
GROUP# TYPE
---------- -------
MEMBER
--------------------------------------------------------------------------------
8 STANDBY
/u01/standby/datafile/TEST/onlinelog/o1_mf_8_dhvkhrpm_.log
1 ONLINE
/u01/standby/fra/TEST/onlinelog/o1_mf_1_dhvkh49o_.log
2 ONLINE
/u01/standby/fra/TEST/onlinelog/o1_mf_2_dhvkh7dg_.log
GROUP# TYPE
---------- -------
MEMBER
--------------------------------------------------------------------------------
3 ONLINE
/u01/standby/fra/TEST/onlinelog/o1_mf_3_dhvkhd53_.log
4 ONLINE
/u01/standby/fra/TEST/onlinelog/o1_mf_4_dhvkhhqc_.log
5 STANDBY
/u01/standby/fra/TEST/onlinelog/o1_mf_5_dhvkhoxm_.log
GROUP# TYPE
---------- -------
MEMBER
--------------------------------------------------------------------------------
6 STANDBY
/u01/standby/fra/TEST/onlinelog/o1_mf_6_dhvkhqcj_.log
7 STANDBY
/u01/standby/fra/TEST/onlinelog/o1_mf_7_dhvkhqtp_.log
8 STANDBY
/u01/standby/fra/TEST/onlinelog/o1_mf_8_dhvkhrpz_.log
24 rows selected.
3.
Verify if the redo transport service is active using the v$managed_standby
view
on the standby database:
SQL> SELECT
THREAD#,SEQUENCE#,PROCESS,CLIENT_PROCESS,STATUS,BLOCKS
FROM V$MANAGED_STANDBY;
THREAD# SEQUENCE# PROCESS CLIENT_P STATUS BLOCKS
---------- ---------- --------- -------- ------------
----------
1 57 ARCH ARCH
CLOSING 667
0 0 ARCH ARCH
CONNECTED 0
0
0 ARCH ARCH CONNECTED 0
0 0 ARCH ARCH
CONNECTED 0
0 0 ARCH ARCH
CONNECTED 0
0 0 ARCH ARCH
CONNECTED 0
0
0 ARCH ARCH CONNECTED 0
0 0 ARCH ARCH
CONNECTED 0
0 0 ARCH ARCH
CONNECTED 0
0 0 ARCH ARCH
CONNECTED 0
0 0 RFS ARCH
IDLE 0
THREAD# SEQUENCE# PROCESS CLIENT_P STATUS BLOCKS
---------- ---------- --------- -------- ------------
----------
1 60 MRP0 N/A
WAIT_FOR_LOG 0
0 0 RFS UNKNOWN
IDLE 0
0 0 RFS UNKNOWN
IDLE 0
0 0 RFS UNKNOWN
IDLE 0
2 58 RFS LGWR
IDLE 1
1 60 RFS LGWR
IDLE 1
0 0 RFS UNKNOWN
IDLE 0
0 0 RFS UNKNOWN
IDLE 0
0 0 RFS UNKNOWN
IDLE 0
0 0 RFS UNKNOWN
IDLE 0
0 0 RFS UNKNOWN
IDLE 0
THREAD# SEQUENCE# PROCESS CLIENT_P STATUS BLOCKS
---------- ---------- --------- -------- ------------
----------
0 0 RFS UNKNOWN
IDLE 0
0 0 RFS ARCH
IDLE 0
24 rows selected.
4. On the
standby database, query the V$ARCHIVED_LOG view for the archived and applied sequences.
For the last archived sequence, use the following:
Standby
SQL> SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG;
MAX(SEQUENCE#)
--------------
59
Primary
SQL> SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG;
MAX(SEQUENCE#)
--------------
59
SQL> SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG WHERE
APPLIED='YES';
MAX(SEQUENCE#)
--------------
59
5.
Check the status of the latest log sequence.
standby
SQL> SELECT SEQUENCE#,APPLIED
FROM V$ARCHIVED_LOG ORDER BY
SEQUENCE#;
SEQUENCE# APPLIED
---------- ---------
32 YES
33 YES
33 YES
34 YES
34 YES
35 YES
35 YES
36 YES
36 YES
37 YES
37 YES
SEQUENCE# APPLIED
---------- ---------
38 YES
38 YES
39 YES
39 YES
40 YES
40 YES
41 YES
41 YES
42 YES
42 YES
43 YES
SEQUENCE# APPLIED
---------- ---------
43 YES
44 YES
44 YES
45 YES
45 YES
46 YES
46 YES
47 YES
47 YES
48 YES
48 YES
SEQUENCE# APPLIED
---------- ---------
49 YES
49 YES
50 YES
50 YES
51 YES
51 YES
52 YES
52 YES
53 YES
53 YES
54 YES
SEQUENCE# APPLIED
---------- ---------
54 YES
55 YES
55 YES
56 YES
56 YES
57 YES
57 NO
58 YES
59 YES
53 rows selected.
primary
QL> SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG;
MAX(SEQUENCE#)
--------------
59
SQL> SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG WHERE
APPLIED='YES'; 2
MAX(SEQUENCE#)
--------------
59
SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY
SEQUENCE#; 2
SEQUENCE# APPLIED
---------- ---------
1 NO
2 NO
2 NO
3 NO
3 NO
4 NO
4 NO
5 NO
5 NO
6 NO
6 NO
SEQUENCE# APPLIED
---------- ---------
7 NO
7 NO
8 NO
8 NO
9 NO
9 NO
10 NO
10 NO
11 NO
11 NO
12 NO
SEQUENCE# APPLIED
---------- ---------
12 NO
13 NO
13 NO
14 NO
14 NO
15 NO
15 NO
16 NO
16 NO
17 NO
17 NO
SEQUENCE# APPLIED
---------- ---------
18 NO
18 NO
19 NO
19 NO
20 NO
20 NO
21 NO
21 NO
22 NO
22 NO
23 NO
SEQUENCE# APPLIED
---------- ---------
23 NO
24 NO
24 NO
25 NO
25 NO
26 NO
26 NO
27 NO
27 NO
28 NO
28 NO
SEQUENCE# APPLIED
---------- ---------
29 NO
29 NO
30 NO
30 NO
31 NO
31 NO
32 NO
32 NO
33 NO
33 NO
33 YES
SEQUENCE# APPLIED
---------- ---------
34 NO
34 NO
34 YES
34 YES
35 NO
35 NO
35 YES
35 YES
36 NO
36 NO
36 YES
SEQUENCE# APPLIED
---------- ---------
36 YES
37 NO
37 NO
37 YES
37 YES
38 NO
38 NO
38 YES
38 YES
39 NO
39 NO
SEQUENCE# APPLIED
---------- ---------
39 YES
39 YES
40 NO
40 NO
40 YES
40 YES
41 NO
41 NO
41 YES
41 YES
42 NO
SEQUENCE# APPLIED
---------- ---------
42 NO
42 YES
42 YES
43 NO
43 NO
43 YES
43 YES
44 NO
44 YES
44 YES
44 NO
SEQUENCE# APPLIED
---------- ---------
45 NO
45 YES
45 YES
45 NO
46 NO
46 YES
46 YES
46 NO
47 NO
47 YES
47 YES
SEQUENCE# APPLIED
---------- ---------
47 NO
48 YES
48 NO
48 YES
48 NO
49 NO
49 NO
49 YES
49 YES
50 NO
50 NO
SEQUENCE# APPLIED
---------- ---------
50 YES
50 YES
51 NO
51 YES
51 NO
51 YES
52 NO
52 YES
52 NO
52 YES
53 NO
SEQUENCE# APPLIED
---------- ---------
53 NO
53 YES
53 YES
54 NO
54 NO
54 YES
54 YES
55 NO
55 NO
55 YES
55 YES
SEQUENCE# APPLIED
---------- ---------
56 NO
56 YES
56 YES
56 NO
57 NO
57 YES
57 NO
57 NO
58 YES
58 NO
59 YES
SEQUENCE# APPLIED
---------- ---------
59 NO
166 rows selected.
6.
You can also check the apply lag on the standby database
using the V$DATAGUARD_ STATS view in terms of time. Run the
following query on the standby database.
SQL> SELECT name, value,
datum_time, time_computed FROM
V$DATAGUARD_STATS WHERE name like
'apply lag';
NAME
--------------------------------
VALUE
----------------------------------------------------------------
DATUM_TIME TIME_COMPUTED
------------------------------
------------------------------
apply lag
+00 00:41:02
04/25/2017 14:14:28 04/25/2017 14:15:28
