Dataguard Administration

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;


Run the primary and see the any Gap in the sequence

select thread#, applied, max(sequence#) from gv$archived_log group by thread#, applied order by thread#, applied desc;

 Last applied and Last received status

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);


- Verify that the last sequence# received and the last sequence# applied to standby
-- database.

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;

The following query
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)

Archive log gaps can be monitored by examining the low and high sequence numbers in the
V$ARCHIVE_GAP view, as shown here:

SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE#
FROM V$ARCHIVE_GAP;

When you detect a redo transport delay, execute the following query on the primary database
to identify archive logs that have not made it to the standby destination:

SELECT L.THREAD#, L.SEQUENCE#
FROM
(SELECT THREAD#, SEQUENCE#
FROM V$ARCHIVED_LOG
WHERE DEST_ID=1) L
WHERE L.SEQUENCE# NOT IN
(SELECT SEQUENCE#
FROM V$ARCHIVED_LOG
WHERE DEST_ID=2
AND THREAD# = L.THREAD#);

Archive log genarating per hour

select day,  MAX(DECODE(thread#, 1, switches_per_thread, NULL)) thread_1,  MAX(DECODE(thread#, 2, switches_per_thread, NULL)) thread_2,MAX(DECODE(thread#, 3, switches_per_thread, NULL)) thread_3 from (select trunc(first_time) day, thread#, sum(1) switches_per_thread from v$log_history group by trunc(first_time), thread#) group by day order by 1

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
 /



column name format a45 trunc
set lines 300
set pages 999
set trimspool on
column dest_id format 9999
column thread# format 9999
alter session set nls_date_format='dd-mon-yy hh24:mi:ss';
select sequence#, name, dest_id, thread#, first_time,
round(sum((blocks*block_size/1024/1024))) mbytes
from v$archived_log
where first_time >=trunc(sysdate-3)
group by sequence#, name, dest_id, thread#, first_time
order by sequence# desc, dest_id asc;


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;




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;

Comments

Post a Comment

Popular posts from this blog

QUERY DATAGUARD AND STANDBY STATUS

-- Find which logs were applied in the last day  SELECT SEQUENCE#, to_char(FIRST_TIME,'hh24:mi:ss dd/mm/yyyy'), to_char(NEXT_TIME,'hh24:mi:ss dd/mm/yyyy'),APPLIED FROM V$ARCHIVED_LOG where next_time>sysdate-1 ORDER BY SEQUENCE# ; -- Find last applied log   select to_char(max(FIRST_TIME),'hh24:mi:ss dd/mm/yyyy') FROM V$ARCHIVED_LOG where applied='YES';   -- What are the managed standby processes doing?   SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;   -- Are we on production or standby?  SELECT DATABASE_ROLE, DB_UNIQUE_NAME INSTANCE, OPEN_MODE, PROTECTION_MODE, PROTECTION_LEVEL, SWITCHOVER_STATUS FROM V$DATABASE;   -- Check for errors  SELECT MESSAGE FROM V$DATAGUARD_STATUS;   -- Check that the DB was openned correctly   SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS; -- important lag statistics  select * from v$dataguard_stats;  -- co...
Read more

Oracle Standby database

Shutting Down a Physical Standby Database Issue the following query to find out if the standby database is performing Redo Apply or real-time apply.  If the MRP0 or MRP process exists, then the standby database is applying redo. SQL> SELECT PROCESS, STATUS FROM V$MANAGED_STANDBY; If Redo Apply is running, cancel it as shown in the following example: SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; Shut down the standby database. SQL> SHUTDOWN IMMEDIATE; Start and mount the physical standby database: SQL> STARTUP MOUNT; Start Redo Apply or real-time apply: To start Redo Apply, issue the following statement: SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  DISCONNECT FROM SESSION; To start real-time apply, issue the following statement: SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  USING CURRENT LOGFILE; ALTER SESSION SET nls_date_format=’DD-MON-YYYY HH24:MI:SS’; select ...
Read more

DBA_LOCKS

select l.session_id, l.oracle_username, l.os_user_name, o.object_name , o.owner from gv$locked_object l, dba_objects o where l.object_id = o.object_id; select l.session_id, l.oracle_username, l.os_user_name, o.object_name , o.owner, 'alter system kill session ''' || s.sid || ',' || s.serial# ||''';' kill_session from v$locked_object l, dba_objects o, v$session s where l.object_id = o.object_id and l.session_id = s.sid; select sid, serial# from gv$session where sid = 0000; alter sysetm kill session ''; select s.sid, s.serial#, p.spid unix_process, s.osuser, s.username, s.machine, s.program, s.sql_hash_value current_hash_value, q1.sql_text current_sql_text, s.prev_hash_value prev_hash_value, q2.sql_text prev_sql_text, to_char(s.logon_time,'DD/MM/YYYY HH24:MI') session_logon_time, o.owner ||'.'|| o.object_name locked_object, l.type lock_type, l.lmode lock_mode, l.ctime lock_time_in_sec from v$lock l, v$session s, v$pro...
Read more