Managing Physical Standby database #
we start MRP process on standby database in mount stage
Start MRP on standby
sql> alter database recover managed standby database disconnect from session;
To check MRP is running on standby:)
SELECT PROCESS from V$MANAGED_STANDBY where PROCESS like 'MRP%';
To Stop MRP #
Sql> alter database recover managed standby database cancel;
Monitoring STANDBY database
To check what are the background process running on standby, query v$managed_standby
Select
PROCESS,
SEQUENCE#,
STATUS
From
V$MANAGED_STANDBY;
To check MRP is running on standby:)
SELECT PROCESS from V$MANAGED_STANDBY where PROCESS like 'MRP%';
what are the logs shipped from primary and applied on standby database ?
SELECT 'Last Applied : ' Logs,
TO_CHAR (next_time, 'DD-MON-YY:HH24:MI:SS') Time
FROM v$archived_log
WHERE sequence# = (SELECT MAX (sequence#)
FROM v$archived_log
WHERE applied = 'YES')
UNION
SELECT 'Last Received : ' Logs,
TO_CHAR (next_time, 'DD-MON-YY:HH24:MI:SS') Time
FROM v$archived_log
WHERE sequence# = (SELECT MAX (sequence#) FROM v$archived_log);
On Standby site, query “v$archive_dest_status” to find the last archived log received and applied on this site
Select
ARCHIVED_THREAD#,
ARCHIVED_SEQ#,
APPLIED_THREAD#,
APPLIED_SEQ#
From
V$ARCHIVE_DEST_STATUS;
Query’s used to find out archive gap between primary and standby
Views Used # v$archived_log & v$log_history
sql> SELECT ARCH.THREAD# “Thread”, ARCH.SEQUENCE# “Last Sequence Received”, APPL.SEQUENCE# “Last Sequence Applied”, (ARCH.SEQUENCE# – APPL.SEQUENCE#) “Difference” FROM (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, (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 WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;
Views used to Check Database Errors ( v$Archived_gap & v$dataguard_status)
set pages 300 lines 300
column Timestamp Format a20
column Facility Format a24
column Severity Format a13
column Message Format a80 trunc
Select to_char(timestamp,'YYYY-MON-DD HH24:MI:SS') Timestamp,Facility,Severity,error_code,message_num,Message from v$dataguard_status where severity in ('Error','Fatal') order by Timestamp;
sql > select * from v$ARCHIVE_GAP;
--Nikhil Tatineni--
--Standby --
