ADEEB ORACLE DBA,RAC & APPS DBA Blog

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

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

[oracle@rac2-pub dbs]$ scp -rp orapwTEST2 oracle@192.168.1.161:/u01/app/oracle/product/11.2.0/dbhome_1/dbs

[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

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

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

35 24-APR-17 24-APR-17 NO

36 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

39 24-APR-17 24-APR-17 NO

40 24-APR-17 24-APR-17 NO

41 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

45 24-APR-17 24-APR-17 NO

46 24-APR-17 24-APR-17 NO

47 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#

----------

[STANDBY]

SQL> SELECT SEQUENCE# FROM V$LOG WHERE STATUS='CURRENT';

SEQUENCE#

----------

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 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

2 58 RFS LGWR IDLE 1

1 60 RFS LGWR IDLE 1

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#)

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

Primary

SQL> SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG;

MAX(SEQUENCE#)

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

SQL> SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG WHERE

APPLIED='YES';

MAX(SEQUENCE#)

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

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

34 YES

35 YES

36 YES

37 YES

SEQUENCE# APPLIED

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

38 YES

39 YES

40 YES

41 YES

42 YES

43 YES

SEQUENCE# APPLIED

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

43 YES

44 YES

45 YES

46 YES

47 YES

48 YES

SEQUENCE# APPLIED

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

49 YES

50 YES

51 YES

52 YES

53 YES

54 YES

SEQUENCE# APPLIED

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

54 YES

55 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#)

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

SQL> SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG WHERE

APPLIED='YES'; 2

MAX(SEQUENCE#)

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

SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY

SEQUENCE#; 2

SEQUENCE# APPLIED

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

1 NO

2 NO

3 NO

4 NO

5 NO

6 NO

SEQUENCE# APPLIED

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

7 NO

8 NO

9 NO

10 NO

11 NO

12 NO

SEQUENCE# APPLIED

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

12 NO

13 NO

14 NO

15 NO

16 NO

17 NO

SEQUENCE# APPLIED

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

18 NO

19 NO

20 NO

21 NO

22 NO

23 NO

SEQUENCE# APPLIED

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

23 NO

24 NO

25 NO

26 NO

27 NO

28 NO

SEQUENCE# APPLIED

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

29 NO

30 NO

31 NO

32 NO

33 NO

33 YES

SEQUENCE# APPLIED

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

34 NO

34 YES

35 NO

35 YES

36 NO

36 YES

SEQUENCE# APPLIED

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

36 YES

37 NO

37 YES

38 NO

38 YES

39 NO

SEQUENCE# APPLIED

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

39 YES

40 NO

40 YES

41 NO

41 YES

42 NO

SEQUENCE# APPLIED

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

42 NO

42 YES

43 NO

43 YES

44 NO

44 YES

44 NO

SEQUENCE# APPLIED

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

45 NO

45 YES

45 NO

46 NO

46 YES

46 NO

47 NO

47 YES

SEQUENCE# APPLIED

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

47 NO

48 YES

48 NO

48 YES

48 NO

49 NO

49 YES

50 NO

SEQUENCE# APPLIED

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

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

54 NO

54 YES

55 NO

55 YES

SEQUENCE# APPLIED

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

56 NO

56 YES

56 NO

57 NO

57 YES

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

ADEEB ORACLE DBA,RAC & APPS DBA Blog

Wednesday, April 26, 2017

RAC to non RAC data Guard (11gR2)

Featured post

Migrate OCR & vote from external to normal or High redundancy in ASM

Popular Posts

Facebook

Pages