@PRIMARY
connect / as sysdba
set pages 999 lines 300
col OPEN_MODE for a10
col HOST_NAME for a10
col DATABASE_ROLE for a7
select NAME,DB_UNIQUE_NAME,INSTANCE_NAME,HOST_NAME,OPEN_MODE,LOG_MODE,DATABASE_STATUS,DATABASE_ROLE,SWITCHOVER_STATUS,PROTECTION_MODE,PROTECTION_LEVEL,CONTROLFILE_TYPE,STATUS,LOGINS,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME"from v$database,gv$instance;
SELECT NAME,OPEN_MODE,DATABASE_ROLE,PROTECTION_LEVEL,SWITCHOVER_STATUS,STATUS,LOGINS FROM V$DATABASE,GV$INSTANCE;
select THREAD#,SEQUENCE#,STATUS,ARCHIVED from v$log;
select dest_id,database_mode,recovery_mode,protection_mode,archived_seq#,applied_seq# from v$archive_dest_Status where dest_id in (1,2);
select * from v$archive_gap;
select * from v$dataguard_stats;
show parameter dump;
show parameter listener;
show parameter service;
show parameter log_archive_dest_2;
show parameter log_archive_dest_state_2;
show parameter dg_broker_start;
Set lines 1000
Set pages 1000
Set scan off
Set feed off
BREAK ON ROW SKIP 1
COL "THREAD" for a10
COL "PR-ARCHIVED" for a15
COL "STBY-ARCHIVED" for a15
COL "STBY-APPLIED" for a 15
COL "SHIPPING GAP(PR -> STBY)" for a20
COL "APPLIED GAP(STBY -> STBY)" for a20
set head off
select 'sysdate: '|| to_char(sysdate,'DD-Mon-YYYY Hh24:Mi:ss') from dual;
select '****************Standby Log ship and Log Apply Status*****************' from dual;
set head on
select DEST_ID,DESTINATION,TARGET,STATUS from V$ARCHIVE_DEST where DESTINATION is not null;
select * from (
select
lpad(t1,4,' ') "Thread",
lpad(pricre,9,' ') "PR - Archived",
lpad(stdcre,10,' ') "STBY - Archived",
lpad(stdnapp,9,' ') "STBY - Applied",
lpad(pricre-stdcre,13,' ') "Shipping GAP (PR -> STBY)",
lpad(stdcre-stdnapp,15,' ') "Applied GAP (STBY -> STBY)"
from
(select max(sequence#) stdcre, thread# t1 from v$archived_log where standby_dest='YES' and resetlogs_id in (select max(RESETLOGS_ID) from v$archived_log) and thread# in (1,2,3,4) group by thread#) a,
(select max(sequence#) stdnapp, thread# t2 from v$archived_log where standby_dest='YES' and resetlogs_id in (select max(RESETLOGS_ID) from v$archived_log) and thread# in (1,2,3,4) and applied='YES' group by thread#) b,
(select max(sequence#) pricre, thread# t3 from v$archived_log where standby_dest='NO' and resetlogs_id in (select max(RESETLOGS_ID) from v$archived_log) and thread# in (1,2,3,4) group by thread#) c
where a.t1=b.t2 and b.t2=c.t3 and c.t3=a.t1) order by 1
/
THREAD PR-ARCHIVED STBY-ARCHIVED STBY-APPLIED SHIPPING GAP(PR -> STBY) APPLIED GAP(STBY -> STBY)
alter system switch logfile; or
alter system switch all logfile; or
alter system archive log current;
/
/
Set lines 1000
Set pages 1000
Set scan off
Set feed off
BREAK ON ROW SKIP 1
COL "THREAD" for a10
COL "PR-ARCHIVED" for a15
COL "STBY-ARCHIVED" for a15
COL "STBY-APPLIED" for a 15
COL "SHIPPING GAP(PR -> STBY)" for a20
COL "APPLIED GAP(STBY -> STBY)" for a20
set head off
select 'sysdate: '|| to_char(sysdate,'DD-Mon-YYYY Hh24:Mi:ss') from dual;
select '****************Standby Log ship and Log Apply Status*****************' from dual;
set head on
select DEST_ID,DESTINATION,TARGET,STATUS from V$ARCHIVE_DEST where DESTINATION is not null;
select * from (
select
lpad(t1,4,' ') "Thread",
lpad(pricre,9,' ') "PR - Archived",
lpad(stdcre,10,' ') "STBY - Archived",
lpad(stdnapp,9,' ') "STBY - Applied",
lpad(pricre-stdcre,13,' ') "Shipping GAP (PR -> STBY)",
lpad(stdcre-stdnapp,15,' ') "Applied GAP (STBY -> STBY)"
from
(select max(sequence#) stdcre, thread# t1 from v$archived_log where standby_dest='YES' and resetlogs_id in (select max(RESETLOGS_ID) from v$archived_log) and thread# in (1,2,3,4) group by thread#) a,
(select max(sequence#) stdnapp, thread# t2 from v$archived_log where standby_dest='YES' and resetlogs_id in (select max(RESETLOGS_ID) from v$archived_log) and thread# in (1,2,3,4) and applied='YES' group by thread#) b,
(select max(sequence#) pricre, thread# t3 from v$archived_log where standby_dest='NO' and resetlogs_id in (select max(RESETLOGS_ID) from v$archived_log) and thread# in (1,2,3,4) group by thread#) c
where a.t1=b.t2 and b.t2=c.t3 and c.t3=a.t1) order by 1
/
THREAD PR-ARCHIVED STBY-ARCHIVED STBY-APPLIED SHIPPING GAP(PR -> STBY) APPLIED GAP(STBY -> STBY)
@STANDBY
--------
connect / as sysdba
set pages 999 lines 300
col OPEN_MODE for a10
col HOST_NAME for a10
col DATABASE_ROLE for a7
select NAME,DB_UNIQUE_NAME,INSTANCE_NAME,HOST_NAME,OPEN_MODE,LOG_MODE,DATABASE_STATUS,DATABASE_ROLE,SWITCHOVER_STATUS,PROTECTION_MODE,PROTECTION_LEVEL,CONTROLFILE_TYPE,STATUS,LOGINS,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME"from v$database,gv$instance;
SELECT NAME,OPEN_MODE,DATABASE_ROLE,PROTECTION_LEVEL,SWITCHOVER_STATUS,STATUS,LOGINS FROM V$DATABASE,GV$INSTANCE;
select THREAD#,SEQUENCE#,STATUS,ARCHIVED from v$log;
SELECT PROCESS, CLIENT_PROCESS, SEQUENCE#, STATUS,THREAD#, DELAY_MINS FROM V$MANAGED_STANDBY;
select dest_id,database_mode,recovery_mode,protection_mode,archived_seq#,applied_seq# from v$archive_dest_Status where dest_id in (1,2);
select * from v$archive_gap;
select * from v$dataguard_stats;
show parameter dump;
show parameter listener;
show parameter service;
show parameter log_archive_dest_2;
show parameter log_archive_dest_state_2;
show parameter dg_broker_start;
PROMPT
PROMPT
PROMPT Run on Standby Database. This script checks last log applied and last log received time
PROMPT
PROMPT
col time format a40
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);
PROMPT last sequence# received and the last sequence# applied to standby database.
PROMPT
select al.thrd "Thread", almax "Last Seq Received", lhmax "Last Seq Applied"
from (select thread# thrd, max(sequence#) almax
from v$archived_log
where resetlogs_change#=(select resetlogs_change# from v$database)
group by thread#) al,
(select thread# thrd, max(sequence#) lhmax
from v$log_history
where first_time=(select max(first_time) from v$log_history)
group by thread#) lh
where al.thrd = lh.thrd;
-------------------------------------------OR----------------------------------------------
DR SYNC STATUS
--------------------
@PRIMARY
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;
Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
alter system switch logfile;
/
/
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;
Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
0 OR min
-------------------------------------------OR----------------------------------------------
DR SYNC STATUS
--------------------
@PRIMARY
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
select thread#,max(sequence#) from v$archived_log where archived='YES' group by thread# order by 1;
@STANDBY
select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread# order by 1;
-------------------------------------------OR----------------------------------------------
DR SYNC STATUS
--------------------
@STANDBY
SELECT PROCESS, CLIENT_PROCESS, SEQUENCE#, STATUS,THREAD#, DELAY_MINS FROM V$MANAGED_STANDBY;
PROCESS CLIENT_P SEQUENCE# STATUS
--------- -------- ---------- ------
MRP0 N/A 1000 APPLIED_LOG
select THREAD#,SEQUENCE#,STATUS,ARCHIVED from v$log; ------ the status of redo log files
LGWR RFS 1000 CURRENT
Both the above current values must be same.
-------------------------------------------OR----------------------------------------------
connect / as sysdba
set pages 999 lines 300
col OPEN_MODE for a10
col HOST_NAME for a10
col DATABASE_ROLE for a7
select NAME,DB_UNIQUE_NAME,INSTANCE_NAME,HOST_NAME,OPEN_MODE,LOG_MODE,DATABASE_STATUS,DATABASE_ROLE,SWITCHOVER_STATUS,PROTECTION_MODE,PROTECTION_LEVEL,CONTROLFILE_TYPE,STATUS,LOGINS,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME"from v$database,gv$instance;
SELECT NAME,OPEN_MODE,DATABASE_ROLE,PROTECTION_LEVEL,SWITCHOVER_STATUS,STATUS,LOGINS FROM V$DATABASE,GV$INSTANCE;
select THREAD#,SEQUENCE#,STATUS,ARCHIVED from v$log;
select dest_id,database_mode,recovery_mode,protection_mode,archived_seq#,applied_seq# from v$archive_dest_Status where dest_id in (1,2);
select * from v$archive_gap;
select * from v$dataguard_stats;
show parameter dump;
show parameter listener;
show parameter service;
show parameter log_archive_dest_2;
show parameter log_archive_dest_state_2;
show parameter dg_broker_start;
Set lines 1000
Set pages 1000
Set scan off
Set feed off
BREAK ON ROW SKIP 1
COL "THREAD" for a10
COL "PR-ARCHIVED" for a15
COL "STBY-ARCHIVED" for a15
COL "STBY-APPLIED" for a 15
COL "SHIPPING GAP(PR -> STBY)" for a20
COL "APPLIED GAP(STBY -> STBY)" for a20
set head off
select 'sysdate: '|| to_char(sysdate,'DD-Mon-YYYY Hh24:Mi:ss') from dual;
select '****************Standby Log ship and Log Apply Status*****************' from dual;
set head on
select DEST_ID,DESTINATION,TARGET,STATUS from V$ARCHIVE_DEST where DESTINATION is not null;
select * from (
select
lpad(t1,4,' ') "Thread",
lpad(pricre,9,' ') "PR - Archived",
lpad(stdcre,10,' ') "STBY - Archived",
lpad(stdnapp,9,' ') "STBY - Applied",
lpad(pricre-stdcre,13,' ') "Shipping GAP (PR -> STBY)",
lpad(stdcre-stdnapp,15,' ') "Applied GAP (STBY -> STBY)"
from
(select max(sequence#) stdcre, thread# t1 from v$archived_log where standby_dest='YES' and resetlogs_id in (select max(RESETLOGS_ID) from v$archived_log) and thread# in (1,2,3,4) group by thread#) a,
(select max(sequence#) stdnapp, thread# t2 from v$archived_log where standby_dest='YES' and resetlogs_id in (select max(RESETLOGS_ID) from v$archived_log) and thread# in (1,2,3,4) and applied='YES' group by thread#) b,
(select max(sequence#) pricre, thread# t3 from v$archived_log where standby_dest='NO' and resetlogs_id in (select max(RESETLOGS_ID) from v$archived_log) and thread# in (1,2,3,4) group by thread#) c
where a.t1=b.t2 and b.t2=c.t3 and c.t3=a.t1) order by 1
/
THREAD PR-ARCHIVED STBY-ARCHIVED STBY-APPLIED SHIPPING GAP(PR -> STBY) APPLIED GAP(STBY -> STBY)
alter system switch logfile; or
alter system switch all logfile; or
alter system archive log current;
/
/
Set lines 1000
Set pages 1000
Set scan off
Set feed off
BREAK ON ROW SKIP 1
COL "THREAD" for a10
COL "PR-ARCHIVED" for a15
COL "STBY-ARCHIVED" for a15
COL "STBY-APPLIED" for a 15
COL "SHIPPING GAP(PR -> STBY)" for a20
COL "APPLIED GAP(STBY -> STBY)" for a20
set head off
select 'sysdate: '|| to_char(sysdate,'DD-Mon-YYYY Hh24:Mi:ss') from dual;
select '****************Standby Log ship and Log Apply Status*****************' from dual;
set head on
select DEST_ID,DESTINATION,TARGET,STATUS from V$ARCHIVE_DEST where DESTINATION is not null;
select * from (
select
lpad(t1,4,' ') "Thread",
lpad(pricre,9,' ') "PR - Archived",
lpad(stdcre,10,' ') "STBY - Archived",
lpad(stdnapp,9,' ') "STBY - Applied",
lpad(pricre-stdcre,13,' ') "Shipping GAP (PR -> STBY)",
lpad(stdcre-stdnapp,15,' ') "Applied GAP (STBY -> STBY)"
from
(select max(sequence#) stdcre, thread# t1 from v$archived_log where standby_dest='YES' and resetlogs_id in (select max(RESETLOGS_ID) from v$archived_log) and thread# in (1,2,3,4) group by thread#) a,
(select max(sequence#) stdnapp, thread# t2 from v$archived_log where standby_dest='YES' and resetlogs_id in (select max(RESETLOGS_ID) from v$archived_log) and thread# in (1,2,3,4) and applied='YES' group by thread#) b,
(select max(sequence#) pricre, thread# t3 from v$archived_log where standby_dest='NO' and resetlogs_id in (select max(RESETLOGS_ID) from v$archived_log) and thread# in (1,2,3,4) group by thread#) c
where a.t1=b.t2 and b.t2=c.t3 and c.t3=a.t1) order by 1
/
THREAD PR-ARCHIVED STBY-ARCHIVED STBY-APPLIED SHIPPING GAP(PR -> STBY) APPLIED GAP(STBY -> STBY)
@STANDBY
--------
connect / as sysdba
set pages 999 lines 300
col OPEN_MODE for a10
col HOST_NAME for a10
col DATABASE_ROLE for a7
select NAME,DB_UNIQUE_NAME,INSTANCE_NAME,HOST_NAME,OPEN_MODE,LOG_MODE,DATABASE_STATUS,DATABASE_ROLE,SWITCHOVER_STATUS,PROTECTION_MODE,PROTECTION_LEVEL,CONTROLFILE_TYPE,STATUS,LOGINS,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME"from v$database,gv$instance;
SELECT NAME,OPEN_MODE,DATABASE_ROLE,PROTECTION_LEVEL,SWITCHOVER_STATUS,STATUS,LOGINS FROM V$DATABASE,GV$INSTANCE;
select THREAD#,SEQUENCE#,STATUS,ARCHIVED from v$log;
SELECT PROCESS, CLIENT_PROCESS, SEQUENCE#, STATUS,THREAD#, DELAY_MINS FROM V$MANAGED_STANDBY;
select dest_id,database_mode,recovery_mode,protection_mode,archived_seq#,applied_seq# from v$archive_dest_Status where dest_id in (1,2);
select * from v$archive_gap;
select * from v$dataguard_stats;
show parameter dump;
show parameter listener;
show parameter service;
show parameter log_archive_dest_2;
show parameter log_archive_dest_state_2;
show parameter dg_broker_start;
PROMPT
PROMPT
PROMPT Run on Standby Database. This script checks last log applied and last log received time
PROMPT
PROMPT
col time format a40
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);
PROMPT last sequence# received and the last sequence# applied to standby database.
PROMPT
select al.thrd "Thread", almax "Last Seq Received", lhmax "Last Seq Applied"
from (select thread# thrd, max(sequence#) almax
from v$archived_log
where resetlogs_change#=(select resetlogs_change# from v$database)
group by thread#) al,
(select thread# thrd, max(sequence#) lhmax
from v$log_history
where first_time=(select max(first_time) from v$log_history)
group by thread#) lh
where al.thrd = lh.thrd;
-------------------------------------------OR----------------------------------------------
DR SYNC STATUS
--------------------
@PRIMARY
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;
Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
alter system switch logfile;
/
/
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;
Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
0 OR min
-------------------------------------------OR----------------------------------------------
DR SYNC STATUS
--------------------
@PRIMARY
alter system switch logfile;
alter system switch logfile;
alter system switch logfile;
select thread#,max(sequence#) from v$archived_log where archived='YES' group by thread# order by 1;
@STANDBY
select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread# order by 1;
-------------------------------------------OR----------------------------------------------
DR SYNC STATUS
--------------------
@STANDBY
SELECT PROCESS, CLIENT_PROCESS, SEQUENCE#, STATUS,THREAD#, DELAY_MINS FROM V$MANAGED_STANDBY;
PROCESS CLIENT_P SEQUENCE# STATUS
--------- -------- ---------- ------
MRP0 N/A 1000 APPLIED_LOG
select THREAD#,SEQUENCE#,STATUS,ARCHIVED from v$log; ------ the status of redo log files
LGWR RFS 1000 CURRENT
Both the above current values must be same.
-------------------------------------------OR----------------------------------------------
No comments:
Post a Comment