Monitor Dataguard Status

From Primary:


SQL> select protection_mode,
2 protection_level,
3 database_role,
4 switchover_status
5 from v$database;

PROTECTION_MODE PROTECTION_LEVEL DATABASE_ROLE SWITCHOVER_STATUS
-------------------- -------------------- ---------------- --------------------
MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE PRIMARY TO STANDBY


SQL> set pages 999
SQL> select to_char(timestamp,'YYYY-MON-DD HH24:MI:SS')||' '||message
2 from v$dataguard_status;

TO_CHAR(TIMESTAMP,'YYYY-MON-DDHH24:MI:SS')||''||MESSAGE
--------------------------------------------------------------------------------
2011-JAN-10 08:43:34 ARC0: Archival started
2011-JAN-10 08:43:34 ARC1: Archival started
2011-JAN-10 08:43:34 ARC2: Archival started
2011-JAN-10 08:43:34 ARC1: Becoming the 'no FAL' ARCH
2011-JAN-10 08:43:34 ARC1: Becoming the 'no SRL' ARCH
2011-JAN-10 08:43:34 ARC2: Becoming the heartbeat ARCH
2011-JAN-10 08:43:35 Error 12514 received logging on to the standby
2011-JAN-10 08:43:35 PING[ARC2]: Heartbeat failed to connect to standby 'drorc
l'. Error is 12514.

2011-JAN-10 08:43:35 ARC1: Beginning to archive thread 1 sequence 58 (305558-3
26454)

2011-JAN-10 08:43:35 ARC1: Completed archiving thread 1 sequence 58 (305558-32
6454)

2011-JAN-10 08:43:35 ARC3: Archival started
2011-JAN-10 08:43:37 Error 12514 received logging on to the standby
2011-JAN-10 08:43:37 FAL[server, ARC2]: Error 12514 creating remote archivelog
file 'drorcl'

2011-JAN-10 08:43:38 ARC3: Beginning to archive thread 1 sequence 59 (326454-3
26515)

2011-JAN-10 08:43:38 ARC3: Completed archiving thread 1 sequence 59 (326454-32
6515)

2011-JAN-10 08:46:52 ARC2: Standby redo logfile selected for thread 1 sequence
58 for destination LOG_ARCHIVE_DEST_2

2011-JAN-10 08:46:52 ARC0: Beginning to archive thread 1 sequence 60 (326515-3
26771)

2011-JAN-10 08:46:52 ARC0: Completed archiving thread 1 sequence 60 (326515-32
6771)

2011-JAN-10 08:46:52 LNS: Standby redo logfile selected for thread 1 sequence
61 for destination LOG_ARCHIVE_DEST_2

2011-JAN-10 08:46:52 LNS: Beginning to archive log 1 thread 1 sequence 61
2011-JAN-10 08:46:53 ARC0: Standby redo logfile selected for thread 1 sequence
60 for destination LOG_ARCHIVE_DEST_2

2011-JAN-10 10:20:03 LNS: Attempting destination LOG_ARCHIVE_DEST_2 network re
connect (3135)

2011-JAN-10 10:20:03 LNS: Destination LOG_ARCHIVE_DEST_2 network reconnect aba
ndoned

2011-JAN-10 10:20:03 Error 3135 for archive log file 1 to 'drorcl'
2011-JAN-10 10:20:03 LNS: Failed to archive log 1 thread 1 sequence 61 (3135)
2011-JAN-10 10:25:22 Error 1031 received logging on to the standby
2011-JAN-10 10:25:22 PING[ARC2]: Heartbeat failed to connect to standby 'drorc
l'. Error is 1031.

2011-JAN-10 10:26:41 Error 1031 received logging on to the standby
2011-JAN-10 10:26:41 PING[ARC2]: Heartbeat failed to connect to standby 'drorc
l'. Error is 1031.

2011-JAN-10 10:28:02 Error 1031 received logging on to the standby
2011-JAN-10 10:28:02 PING[ARC2]: Heartbeat failed to connect to standby 'drorc
l'. Error is 1031.

2011-JAN-10 10:29:21 Error 1031 received logging on to the standby
2011-JAN-10 10:29:21 PING[ARC2]: Heartbeat failed to connect to standby 'drorc
l'. Error is 1031.

2011-JAN-10 10:30:40 Error 12514 received logging on to the standby
2011-JAN-10 10:30:40 PING[ARC2]: Heartbeat failed to connect to standby 'drorc
l'. Error is 12514.

2011-JAN-10 10:31:59 Error 12514 received logging on to the standby
2011-JAN-10 10:31:59 PING[ARC2]: Heartbeat failed to connect to standby 'drorc
l'. Error is 12514.

2011-JAN-10 10:33:18 Error 12514 received logging on to the standby
2011-JAN-10 10:33:18 PING[ARC2]: Heartbeat failed to connect to standby 'drorc
l'. Error is 12514.

2011-JAN-10 10:34:37 Error 12528 received logging on to the standby
2011-JAN-10 10:34:37 PING[ARC2]: Heartbeat failed to connect to standby 'drorc
l'. Error is 12528.

2011-JAN-10 10:34:37 ARC3: Beginning to archive thread 1 sequence 61 (326771-3
33704)

2011-JAN-10 10:34:38 ARC3: Completed archiving thread 1 sequence 61 (326771-33
3704)

2011-JAN-10 10:34:38 LNS: Standby redo logfile selected for thread 1 sequence
62 for destination LOG_ARCHIVE_DEST_2

2011-JAN-10 10:34:38 LNS: Beginning to archive log 2 thread 1 sequence 62
2011-JAN-10 10:34:38 ARC0: Standby redo logfile selected for thread 1 sequence
61 for destination LOG_ARCHIVE_DEST_2


46 rows selected.

From standby database:

SQL> select protection_mode,
2 protection_level,
3 database_role,
4 switchover_status
5 from v$database;

PROTECTION_MODE PROTECTION_LEVEL DATABASE_ROLE SWITCHOVER_STATUS
-------------------- -------------------- ---------------- --------------------
MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE PHYSICAL STANDBY NOT ALLOWED

SQL> select process,
2 status,
3 thread#,
4 sequence#,
5 block#,
6 blocks
7 from v$managed_standby;

PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
ARCH CLOSING 1 61 10240 1111
RFS IDLE 0 0 0 0
RFS IDLE 1 62 1057 1
RFS IDLE 0 0 0 0
MRP0 APPLYING_LOG 1 62 1057 102400

8 rows selected.

SQL> select to_char(timestamp,'YYYY-MON-DD HH24:MI:SS')||' '||message from v$dataguard_status;

TO_CHAR(TIMESTAMP,'YYYY-MON-DDHH24:MI:SS')||''||MESSAGE
--------------------------------------------------------------------------------
2011-JAN-10 10:34:36 ARC0: Archival started
2011-JAN-10 10:34:37 ARC1: Archival started
2011-JAN-10 10:34:37 ARC2: Archival started
2011-JAN-10 10:34:37 ARC1: Becoming the 'no FAL' ARCH
2011-JAN-10 10:34:37 ARC2: Becoming the heartbeat ARCH
2011-JAN-10 10:34:37 ARC2: Becoming the active heartbeat ARCH
2011-JAN-10 10:34:38 Primary database is in MAXIMUM PERFORMANCE mode
2011-JAN-10 10:34:38 RFS[1]: Assigned to RFS process 8979
2011-JAN-10 10:34:38 RFS[2]: Assigned to RFS process 8981
2011-JAN-10 10:34:38 ARC3: Archival started
2011-JAN-10 10:34:38 ARC3: Beginning to archive thread 1 sequence 61 (326771-3
33704)

2011-JAN-10 10:34:39 ARC3: Completed archiving thread 1 sequence 61 (0-0)
2011-JAN-10 10:34:51 Attempt to start background Managed Standby Recovery proc
ess

2011-JAN-10 10:34:51 MRP0: Background Managed Standby Recovery process started
2011-JAN-10 10:34:57 Managed Standby Recovery not using Real Time Apply
2011-JAN-10 10:34:57 Media Recovery Log /u01/app/oracle/fast_recovery_area/DRO
RCL/archivelog/2011_01_10/o1_mf_1_61_6lnw1yrp_.arc

2011-JAN-10 10:34:57 Media Recovery Waiting for thread 1 sequence 62 (in trans
it)

2011-JAN-10 10:35:06 MRP0: Background Media Recovery cancelled with status 160
37

2011-JAN-10 10:35:06 MRP0: Background Media Recovery process shutdown
2011-JAN-10 10:35:06 Managed Standby Recovery Canceled
2011-JAN-10 10:35:12 Attempt to start background Managed Standby Recovery proc
ess

2011-JAN-10 10:35:12 MRP0: Background Managed Standby Recovery process started
2011-JAN-10 10:35:18 Managed Standby Recovery starting Real Time Apply
2011-JAN-10 10:35:18 Media Recovery Waiting for thread 1 sequence 62 (in trans
it)


24 rows selected.