select max(al.sequence#) "Last Seq Recieved", max(lh.sequence#) "Last Seq Applied" from v$archived_log al, v$log_history lh;
To Verify Managed Recovery...
select process,status,thread#,sequence#,block#,blocks from v$managed_standby;
To determine the level of synchronization.
SELECT ARCHIVED_THREAD#, ARCHIVED_SEQ#, APPLIED_THREAD#,APPLIED_SEQ# FROM V$ARCHIVE_DEST_STATUS;
Archive Log received
SELECT REGISTRAR, CREATOR, THREAD#, SEQUENCE#, FIRST_CHANGE#, NEXT_CHANGE# FROM V$ARCHIVED_LOG order by sequence# ;
Archive Log Applied
SELECT THREAD#, SEQUENCE#, FIRST_CHANGE#, NEXT_CHANGE# FROM V$LOG_HISTORY;
SELECT MESSAGE FROM V$DATAGUARD_STATUS;
SELECT TO_CHAR(TRUNC(FIRST_TIME),'Mon DD') "DG Date",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'00',1,0)),'9999') "12AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'01',1,0)),'9999') "01AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'02',1,0)),'9999') "02AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'03',1,0)),'9999') "03AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'04',1,0)),'9999') "04AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'05',1,0)),'9999') "05AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'06',1,0)),'9999') "06AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'07',1,0)),'9999') "07AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'08',1,0)),'9999') "08AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'09',1,0)),'9999') "09AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'10',1,0)),'9999') "10AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'11',1,0)),'9999') "11AM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'12',1,0)),'9999') "12PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'13',1,0)),'9999') "1PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'14',1,0)),'9999') "2PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'15',1,0)),'9999') "3PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'16',1,0)),'9999') "4PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'17',1,0)),'9999') "5PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'18',1,0)),'9999') "6PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'19',1,0)),'9999') "7PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'20',1,0)),'9999') "8PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'21',1,0)),'9999') "9PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'22',1,0)),'9999') "10PM",
TO_CHAR(SUM(DECODE(TO_CHAR(FIRST_TIME,'HH24'),'23',1,0)),'9999') "11PM"
FROM V$LOG_HISTORY
GROUP BY TRUNC(FIRST_TIME)
ORDER BY TRUNC(FIRST_TIME) DESC
/
select round((sysdate-max(first_time))*24,2) "Hrs" from v$log_history;
After you issue the first sql statement to start you can actually check if it's running with the following sql (on the standby):
SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID=2;
if it says idle you know, you have failed to start.
SELECT L.SEQUENCE#, L.FIRST_TIME, (CASE WHEN L.NEXT_CHANGE# < p.read_scn then 'YES' when l.FIRST_CHANGE# < P.applied_scn then 'CURRENT' ELSE 'NO' END) APPLIED
FROM DBA_LOGSTDBY_LOG L, DBA_LOGSTDBY_PROGRESS P ORDER BY SEQUENCE#
=============================================================================
Example
select max(al.sequence#) "Last Seq Recieved", max(lh.sequence#) "Last Seq Applied" from v$archived_log al, v$log_history lh;
Last Seq Recieved Last Seq Applied
----------------- ----------------
355 336
select process,status,thread#,sequence#,block#,blocks from v$managed_standby;
PROCESS STATUS THREAD#
--------------------------- ------------------------------------ ----------
SEQUENCE# BLOCK# BLOCKS
---------- ---------- ----------
ARCH CLOSING 1
350 63489 515
ARCH CLOSING 1
355 1 1
RFS RECEIVING 1
339 1 1
PROCESS STATUS THREAD#
--------------------------- ------------------------------------ ----------
SEQUENCE# BLOCK# BLOCKS
---------- ---------- ----------
RFS WRITING 1
356 13714 204800
SQL> recover managed standby database cancel;
ORA-16136: Managed Standby Recovery not active
SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID=2;
RECOVERY_MODE
---------------------
IDLE
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL> SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID=2;
RECOVERY_MODE
---------------------
MANAGED
select process,status,thread#,sequence#,block#,blocks,DELAY_MINS from v$managed_standby;
PROCESS STATUS THREAD#
--------------------------- ------------------------------------ ----------
SEQUENCE# BLOCK# BLOCKS
---------- ---------- ----------
ARCH CLOSING 1
350 63489 515
ARCH CLOSING 1
355 1 1
RFS RECEIVING 1
339 1 1
RFS WRITING 1
356 13897 204800
MRP0 APPLYING_LOG 1
350 50121 64003
SQL> select max(al.sequence#) "Last Seq Recieved", max(lh.sequence#) "Last Seq Applied" from v$archived_log al, v$log_history lh;
Last Seq Recieved Last Seq Applied
----------------- ----------------
355 352
SQL> select max(al.sequence#) "Last Seq Recieved", max(lh.sequence#) "Last Seq Applied" from v$archived_log al, v$log_history lh;
Last Seq Recieved Last Seq Applied
----------------- ----------------
355 355
SQL> select round((sysdate-max(first_time))*24,2) "Hrs" from v$log_history;
Hrs
----------
2.75
alter database register logfile '/data/a01/OTTPRDDR/OTTPRD01_1_18527_728567327.arc';
ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/home/oracle/stbyctrl7feb.ctl';
startup nomount
alter database mount standby database;
alter database recover managed standby database disconnect from session;
No comments:
Post a Comment