SYNC TIME:
SELECT TO_CHAR(CONTROLFILE_TIME,'DD-MON-YYYY HH24:MI:SS') TIME FROM GV$DATABASE;
LAST APPLIED LOGS :
SET LINES 200
COLUMN TIMESTAMP FORMAT A30
COLUMN MESSAGE FORMAT A75
SELECT TIMESTAMP, MESSAGE FROM (SELECT MAX (TO_CHAR (TIMESTAMP, 'YYYY-MON-DD HH24:MI:SS'))TIMESTAMP, MESSAGE FROM GV$DATAGUARD_STATUS WHERE MESSAGE LIKE 'Media%Recovery%Log%' GROUP BY TIMESTAMP, MESSAGE ORDER BY 1 desc ) WHERE ROWNUM BETWEEN 1 AND 10 ;
LAST APPLIED STANDBY LOGS:
SET TIME ON
SET LINES 200
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#;
LOG DIFFERENCE:
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;
APPLIED LOG DETAILS:
SELECT MAX(SEQUENCE#) MAXLOG ,THREAD# FROM V$ARCHIVED_LOG GROUP BY THREAD#;
SELECT MAX(SEQUENCE#) MAXAPPLIED ,THREAD# FROM V$ARCHIVED_LOG WHERE APPLIED='YES' GROUP BY THREAD#;
ARCHIVE LOG LIST;
GENERATION AND APPLIED TIME :
SELECT DISTINCT SEQUENCE#, THREAD#, TO_CHAR(FIRST_TIME, 'YYYY-MON-DD HH24:MI:SS') GENERATION_TIME FROM GV$LOG_HISTORY
WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM GV$ARCHIVED_LOG GROUP BY THREAD#)
ORDER BY 2;
SELECT 'LAST APPLIED : ' LOGS,
TO_CHAR(NEXT_TIME,'DD-MON-YY:HH24:MI:SS') TIME,THREAD#,SEQUENCE#
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,THREAD#,SEQUENCE#
FROM V$ARCHIVED_LOG
WHERE SEQUENCE# =
(SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG );
CHECK THAT ARCHIVE LOGS ARE BEING SHIPPED & THIS QUERY NEEDS TO BE RUN ON THE PRIMARY DATABASE:
SET PAGESIZE 124
COL DB_NAME FORMAT A8
COL HOSTNAME FORMAT A12
COL LOG_ARCHIVED FORMAT 999999
COL LOG_APPLIED FORMAT 999999
COL LOG_GAP FORMAT 9999
COL APPLIED_TIME FORMAT A12
SELECT DB_NAME, HOSTNAME, LOG_ARCHIVED, LOG_APPLIED,APPLIED_TIME,
LOG_ARCHIVED-LOG_APPLIED LOG_GAP
FROM
(
SELECT NAME DB_NAME
FROM V$DATABASE
),
(
SELECT UPPER(SUBSTR(HOST_NAME,1,(DECODE(INSTR(HOST_NAME,'.'),0,LENGTH(HOST_NAME),
(INSTR(HOST_NAME,'.')-1))))) HOSTNAME
FROM V$INSTANCE
),
(
SELECT MAX(SEQUENCE#) LOG_ARCHIVED
FROM V$ARCHIVED_LOG WHERE DEST_ID=1 AND ARCHIVED='YES'
),
(
SELECT MAX(SEQUENCE#) LOG_APPLIED
FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES'
),
(
SELECT TO_CHAR(MAX(COMPLETION_TIME),'DD-MON/HH24:MI') APPLIED_TIME
FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES'
);
FIND ARCHIVE GENERATION OVER LAST ONE MONTH:
SELECT TRUNC(FIRST_TIME)
, COUNT(*)
FROM V$ARCHIVED_LOG
WHERE FIRST_TIME BETWEEN ADD_MONTHS(SYSDATE,-1) AND SYSDATE
AND DEST_ID = 1
GROUP BY TRUNC(FIRST_TIME)
ORDER BY 1;
TO CHECK THE GAP HISTORY (LAST 24 HOURS) IN DR RUN THE BELOW QUERY:
SELECT THREAD#, SEQUENCE#, TO_CHAR(NEXT_TIME,'DD-HH24:MI'), TO_CHAR(COMPLETION_TIME,'DD-HH24:MI'), ROUND((COMPLETION_TIME-NEXT_TIME)*24*60) AS DELTA_MINUTES FROM V$ARCHIVED_LOG WHERE COMPLETION_TIME>SYSDATE-1 AND (COMPLETION_TIME-NEXT_TIME)*24*60>30;
CAN BE USED ON THE PRIMARY DATABASE TO IDENTIFY PEAK ARCHIVE TIMES FOR A SPECIFIED DAY:
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);
SET VERIFY OFF
SET LINESIZE 180
SET PAGESIZE 66
ACCEPT STARTDATE PROMPT 'ENTER START DATE (DD-MON-YYYY): '
ACCEPT ENDDATE PROMPT 'ENTER END DATE (DD-MON-YYYY): '
COLUMN H00 FORMAT 999 HEADING '00'
COLUMN H01 FORMAT 999 HEADING '01'
COLUMN H02 FORMAT 999 HEADING '02'
COLUMN H03 FORMAT 999 HEADING '03'
COLUMN H04 FORMAT 999 HEADING '04'
COLUMN H05 FORMAT 999 HEADING '05'
COLUMN H06 FORMAT 999 HEADING '06'
COLUMN H07 FORMAT 999 HEADING '07'
COLUMN H08 FORMAT 999 HEADING '08'
COLUMN H09 FORMAT 999 HEADING '09'
COLUMN H10 FORMAT 999 HEADING '10'
COLUMN H11 FORMAT 999 HEADING '11'
COLUMN H12 FORMAT 999 HEADING '12'
COLUMN H13 FORMAT 999 HEADING '13'
COLUMN H14 FORMAT 999 HEADING '14'
COLUMN H15 FORMAT 999 HEADING '15'
COLUMN H16 FORMAT 999 HEADING '16'
COLUMN H17 FORMAT 999 HEADING '17'
COLUMN H18 FORMAT 999 HEADING '18'
COLUMN H19 FORMAT 999 HEADING '19'
COLUMN H20 FORMAT 999 HEADING '20'
COLUMN H21 FORMAT 999 HEADING '21'
COLUMN H22 FORMAT 999 HEADING '22'
COLUMN H23 FORMAT 999 HEADING '23'
COLUMN TOTAL FORMAT 999,999 HEADING 'TOTAL'
SPOOL LOG_SWITCH_HISTORY.LST
SELECT
SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR HH:MI:SS'),1,5) DAY
, SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR HH24:MI:SS'),10,2),'00',1,0)) H00
, SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR HH24:MI:SS'),10,2),'01',1,0)) H01
, SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR HH24:MI:SS'),10,2),'02',1,0)) H02
, SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR HH24:MI:SS'),10,2),'03',1,0)) H03
, SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR HH24:MI:SS'),10,2),'04',1,0)) H04
, SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR HH24:MI:SS'),10,2),'05',1,0)) H05
, SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR HH24:MI:SS'),10,2),'06',1,0)) H06
, SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR HH24:MI:SS'),10,2),'07',1,0)) H07
, SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR HH24:MI:SS'),10,2),'08',1,0)) H08
, SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR HH24:MI:SS'),10,2),'09',1,0)) H09
, SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR HH24:MI:SS'),10,2),'10',1,0)) H10
, SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR HH24:MI:SS'),10,2),'11',1,0)) H11
, SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR HH24:MI:SS'),10,2),'12',1,0)) H12
, SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR HH24:MI:SS'),10,2),'13',1,0)) H13
, SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR HH24:MI:SS'),10,2),'14',1,0)) H14
, SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR HH24:MI:SS'),10,2),'15',1,0)) H15
, SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR HH24:MI:SS'),10,2),'16',1,0)) H16
, SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR HH24:MI:SS'),10,2),'17',1,0)) H17
, SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR HH24:MI:SS'),10,2),'18',1,0)) H18
, SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR HH24:MI:SS'),10,2),'19',1,0)) H19
, SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR HH24:MI:SS'),10,2),'20',1,0)) H20
, SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR HH24:MI:SS'),10,2),'21',1,0)) H21
, SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR HH24:MI:SS'),10,2),'22',1,0)) H22
, SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR HH24:MI:SS'),10,2),'23',1,0)) H23
, COUNT(*) TOTAL
FROM
V$LOG_HISTORY A
WHERE
(TO_DATE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR HH:MI:SS'), 1,8), 'MM/DD/RR')
>=
TO_DATE('&STARTDATE', 'DD-MON-YYYY')
)
AND
(TO_DATE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR HH:MI:SS'), 1,8), 'MM/DD/RR')
<=
TO_DATE('&ENDDATE', 'DD-MON-YYYY')
)
GROUP BY SUBSTR(TO_CHAR(FIRST_TIME, 'MM/DD/RR HH:MI:SS'),1,5)
/
SPOOL OFF
HOW TO CALCULATE THE AMOUNT OF REDO PER DAY AND THREAD. TO FIND OUT, USE THIS QUERY:
SELECT TRUNC(FIRST_TIME), THREAD#, ROUND(SUM(BLOCKS * BLOCK_SIZE) / 1024/ 1024,2) M FROM V$ARCHIVED_LOG WHERE DEST_ID = 1 GROUP BY TRUNC(FIRST_TIME), THREAD # ORDER BY 1;
STANDBY LOGS ARE BEING USED BY RUNNING FOLLOWING QUERY :
SET LINES 155 PAGES 9999
COL THREAD# FOR 9999990
COL SEQUENCE# FOR 999999990
COL GRP FOR 990
COL FNM FOR A50 HEAD "FILE NAME"
COL "FISRT SCN NUMBER" FOR 999999999999990
BREAK ON THREAD
# SKIP 1
SELECT A.THREAD#
,A.SEQUENCE#
,A.GROUP# GRP
, A.BYTES/1024/1024 SIZE_MB
,A.STATUS
,A.ARCHIVED
,A.FIRST_CHANGE# "FIRST SCN NUMBER"
,TO_CHAR(FIRST_TIME,'DD-MON-RR HH24:MI:SS') "FIRST SCN TIME"
,TO_CHAR(LAST_TIME,'DD-MON-RR HH24:MI:SS') "LAST SCN TIME" FROM
V$STANDBY_LOG A ORDER BY 1,2,3,4
/
No comments:
Post a Comment