How to check Data Guard Physical standby status? (Oracle)

For data guard issues from an existing physical standby, I usually only use the following queries to diagnostic the problem. These queries can serve 95% of the cases and tell me where is the problem from.

For the rest 5% that you would like to look deeper, check out the srdc_DGPhyStby_diag.sql from Oracle support site. It gives you a report with all details in html format.

set tab off;
set linesize 300;
set pagesize 1000;
set long 100;
set verify off;
set feedback off;
--spool check_dg_tmp.out;
prompt
prompt
prompt *********************************
prompt showing ..... DB information
prompt *********************************

column host_name format a20
column  "STARTUP_TIME" format a25
select name Database,INSTANCE_NAME,OPEN_MODE,DATABASE_STATUS,database_role,logins,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "STARTUP_TIME" from v$database,v$instance;

prompt
prompt
prompt *********************************
prompt showing ..... Standby applying status
prompt *********************************

select process,status,sequence#,thread#,BLOCK#,BLOCKS,pid from gv$managed_standby order by 2 desc;

prompt
prompt
prompt *********************************
prompt showing ..... DG status
prompt *********************************
select INSTANCE_NAME,database_role, switchover_status,PROTECTION_MODE,PROTECTION_LEVEL , FLASHBACK_ON,force_logging "FORCE_LOGGING"  from v$database,v$instance;

prompt
prompt
prompt *********************************
prompt showing ..... archive log destination (location and roles, 1 - 6)
prompt *********************************
column dest_name format a19
column destination format a22
column db_unique_name format a12
column gap_status format a15
column dest_id format 9999
select a.DEST_ID, a.dest_name, a.type, a.STATUS, a.DESTINATION,  a.VALID_TYPE, a.VALID_ROLE, a.db_unique_name, b.gap_status
FROM V$ARCHIVE_DEST a,  V$ARCHIVE_DEST_STATUS b WHERE a.dest_id=b.dest_id and a.DESTINATION is not null;


prompt
prompt
prompt *********************************
prompt showing ..... archive log state (enable or defer)
prompt *********************************
show parameter log_archive_dest_state_1
show parameter log_archive_dest_state_2
show parameter log_archive_dest_state_3
show parameter log_archive_dest_state_4
show parameter log_archive_dest_state_5
show parameter log_archive_dest_state_6

prompt
prompt
prompt *********************************
prompt showing ..... archive log file format ( %t_%s_%r.arc)
prompt *********************************
show parameter log_archive_format

prompt
prompt
prompt *********************************
prompt showing ..... convert parameters (log and db_file)
prompt *********************************
show parameter convert

prompt
prompt
prompt *********************************
prompt showing ..... flashback retention
prompt *********************************
show parameter  db_flashback_retention_target

prompt
prompt
prompt *********************************
prompt showing ..... DB recover file dest and size
prompt *********************************
show parameter db_recovery

prompt
prompt
prompt *********************************
prompt showing ..... local listener and remote listener
prompt *********************************
show parameter remote_listener
show parameter local_listener

--spool off;
exit;

How to check cursor usage and find high version count sqls? (Oracle)

prompt ====================================================
prompt == Start of check_cursor.sql
prompt ====================================================
prompt ====================================================
prompt == Monitor cursor usage
prompt == Due to bug 21135007, the session_cached_cursor usage might be out
prompt == number due to parallel execution.
prompt ====================================================
set lines 300
col value format a30
col usage format a30
SELECT
   'session_cached_cursors' PARAMETER,
   lpad(VALUE, 5) VALUE,
   decode(VALUE, 0, 'n/a', to_char(100 * used / VALUE, '990') || '%') usage , used
FROM
   (SELECT MAX(s.VALUE) used
      FROM v$statname n, v$sesstat s
      WHERE n.name = 'session cursor cache count'
         AND s.statistic# = n.statistic#
   ),
   (SELECT VALUE
      FROM v$PARAMETER
      WHERE name = 'session_cached_cursors'
   )
UNION ALL
SELECT 'open_cursors', lpad(VALUE, 5), to_char(100 * used / VALUE, '990') || '%', used
FROM(SELECT MAX(SUM(s.VALUE)) used
      FROM v$statname n, v$sesstat s
      WHERE n.name ='opened cursors current'
         AND s.statistic# = n.statistic#
      GROUP BY s.sid
   ),
   (  SELECT VALUE
      FROM v$PARAMETER
      WHERE  name = 'open_cursors'
   );

prompt ====================================================
prompt == Top 10 cursor hungry sql statements
prompt ====================================================
select *
from (select  t.value ,s.sid, s.sql_id
from v$sesstat t, v$statname n, v$session s
where t.sid=s.sid and n.name= 'session cursor cache count' and t.statistic# = n.statistic#  order by 1 desc) where rownum <=10;

prompt ====================================================
prompt == Total cursors open, by username & machine
prompt ====================================================
select sum(a.value) total_cur, avg(a.value) avg_cur, max(a.value) max_cur, s.username, s.machine
from   v$sesstat a, v$statname b, v$session s
where  a.statistic# = b.statistic# and s.sid=a.sid
and    b.name = 'opened cursors current'
group by s.username, s.machine
order by 1 desc;

prompt ====================================================
prompt == Show sqls having high child cursor count > 100
prompt ====================================================

select inst_id, sql_id, sql_profile, plan_hash_value, max(child_number)
from gv$sql group by inst_id, sql_id, sql_profile, plan_hash_value
having max(child_number) >100;


prompt ====================================================
prompt == End of check_cursor.sql
prompt ====================================================

How to check memory components of your database? (Oracle)

This script shows you information about your memory configurations, current usage, adviser views, etc. Before jumping into any conclusions, you should check if the database is using AMM, HugePage, or any other OS related configurations. Changing memory configuration involves at least a rolling restart in most cases.

prompt ====================================================
prompt == start of check_memory.sql
prompt ====================================================

prompt ====================================================
prompt == Show Memory parameters
prompt ====================================================
show parameter memory

prompt ====================================================
prompt == Top 50 memory allocations by sessions
prompt ====================================================

SET LINESIZE 300
SET Pages 300

COLUMN username FORMAT A20
COLUMN module FORMAT A20

SELECT * FROM
(SELECT a.inst_id,
       NVL(a.username,'(oracle)') AS username,
       a.sid,
       a.module,
       a.program,
       Trunc(b.value/1024/1024) AS memory_mb
FROM   gv$session a,
       gv$sesstat b,
       gv$statname c
WHERE  a.sid = b.sid
AND    a.inst_id = b.inst_id
AND    b.statistic# = c.statistic#
AND    b.inst_id = c.inst_id
AND    c.name = 'session pga memory'
AND    a.program IS NOT NULL
ORDER BY b.value DESC)
WHERE rownum <=50;

prompt ====================================================
prompt == Show SGA PGA hourly usage in last 10 days
prompt ====================================================
set lines 200
set pages 1000
select sn.INSTANCE_NUMBER, sga.allo sga, pga.allo pga,(sga.allo+pga.allo) totalGb,to_char(SN.END_INTERVAL_TIME,'YYYY-MM-DD HH24:MI') datetime
from
(select snap_id,INSTANCE_NUMBER,round(sum(bytes)/1024/1024/1024,3) allo
from DBA_HIST_SGASTAT
group by snap_id,INSTANCE_NUMBER) sga
,(select snap_id,INSTANCE_NUMBER,round(sum(value)/1024/1024/1024,3) allo
from DBA_HIST_PGASTAT where name = 'total PGA allocated'
group by snap_id,INSTANCE_NUMBER) pga
, dba_hist_snapshot sn
where sn.snap_id=sga.snap_id
and sn.INSTANCE_NUMBER=sga.INSTANCE_NUMBER
and sn.snap_id=pga.snap_id
and sn.INSTANCE_NUMBER=pga.INSTANCE_NUMBER
and sn.END_INTERVAL_TIME > sysdate - 10
order by sn.snap_id desc, sn.INSTANCE_NUMBER
;
prompt ====================================================
prompt == V$SHARED_POOL_ADVICE
prompt ====================================================
select * from V$SHARED_POOL_ADVICE;

prompt ====================================================
prompt == V$SGA_TARGET_ADVICE
prompt ====================================================
select * from v$sga_target_advice;

prompt ====================================================
prompt == V$MEMORY_TARGET_ADVICE
prompt ====================================================
select * from  v$memory_target_advice;

prompt ====================================================
prompt == Show SGA
prompt ====================================================
show sga
show parameter sga

prompt ====================================================
prompt == Current Free SGA
prompt ====================================================
select current_size/1024/1024/1024 free_gb
from V$SGA_DYNAMIC_FREE_MEMORY;

prompt ====================================================
prompt == Current SGA Components
prompt ====================================================
select component, current_size/1024/1024/1024 current_Gb
from V$SGA_DYNAMIC_COMPONENTS;

prompt ====================================================
prompt == Show PGA
prompt ====================================================
show parameter pga

prompt ====================================================
prompt == Max PGA allocated
prompt ====================================================
select name, value/1024/1024/1024 Gb
from V$PGASTAT where name='maximum PGA allocated';


prompt ====================================================
prompt == End of check_memory.sql
prompt ====================================================

How to see progress of long running sql statements? (Oracle)

From now and then, you are asked by your teammates about when his/her sql statements will finish. Here is a script that shows an estimation. It can be used for any long running sqls including RMAN jobs as well.

Sometimes this estimation might not be accurate. The elapsed_seconds might change to zero or a few seconds frequently for some long running jobs. That means the job is progressing and it is not hang.

prompt ====================================================
prompt == Start of check_longops.sql
prompt ====================================================

prompt ====================================================
prompt == Check Long Operation notes
prompt == These are just Estimations and sometimes not accurate.
prompt ==
prompt ==
prompt ====================================================

SET PAGES 100
SET LINE 200
COLUMN sid FORMAT 999
COLUMN serial# FORMAT 9999999
COLUMN machine FORMAT A20
COLUMN progress_pct FORMAT 99999999.00
COLUMN elapsed FORMAT A10
COLUMN remaining FORMAT A10
COLUMN message FORMAT a80
COLUMN opname FORMAT a20
column username format a20

SELECT s.sid,
       sl.username,
       opname,
       MESSAGE,
       s.serial#,
       s.machine,
       ROUND (sl.elapsed_seconds / 60) || ':' || MOD (sl.elapsed_seconds, 60)
           elapsed,
       ROUND (sl.time_remaining / 60) || ':' || MOD (sl.time_remaining, 60)
           remaining,
       DECODE (totalwork, 0, 0, ROUND (sl.sofar / sl.totalwork * 100, 2))
           progress_pct
  FROM gv$session s, gv$session_longops sl
 WHERE s.sid = sl.sid AND s.serial# = sl.serial# AND sofar <> totalwork;

 prompt ====================================================
 prompt == End of check_longops.sql
 prompt ====================================================

How to check locking status? (Oracle)

When you are called to help an application hang issue, you would like to know if there are any database sessions are blocked and what transactions are blocking them and who/which session/which machines are blocking them.

The following script would help you find out if there are any locks in the database and who is blocking them.

prompt ====================================================

prompt == Start of check_lock.sql

prompt ====================================================

prompt ====================================================

prompt == Check Locks notes

prompt == 

prompt == Who is blocking whom?

prompt == 

prompt ====================================================

select s1.username || ' '|| s1.machine 

|| ' ( SID=' || s1.sid || ' ) is blocking ' 

|| s2.username || ' '|| s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status , s2.event as wait_event

from v$lock l1, v$session s1, v$lock l2, v$session s2 

where s1.sid=l1.sid and s2.sid=l2.sid 

and l1.BLOCK=1 and l2.request > 0 

and l1.id1 = l2.id1 

and l2.id2 = l2.id2 ; 

prompt ====================================================

prompt == End of check_lock.sql

prompt ====================================================

 

How to check IO performance? (Oracle)

During performance tuning or being called to debug a performance issues of the database system, we always would like to know the current I/O status and how it compares with baseline periods. This is a handy script that would give you useful information.

Please keep in mind that busy I/O might not necessary lead to the conclusion that I/O is the bottleneck or culprit of the performance issue.

prompt ====================================================
prompt == Start of check_io.sql
prompt ====================================================

prompt ====================================================
prompt == Check IO notes
prompt == OS level should support Async for better performance.
prompt == In general, for waits 1000+ per hour, elapsed time
prompt == should be less than 15~20ms.
prompt ==
prompt == Single Block -->wait on db file sequential read -->index visit or rollback
prompt ==
prompt == Multi Block -->wait on db file scattered read -->full table scan or index fast full scan
prompt ==
prompt ==
prompt ==
prompt == How to Tell if the I/O of the Database is Slow (Doc ID 1275596.1)
prompt ====================================================
prompt ====================================================
prompt == Show IO Stats
prompt ====================================================
set lines 300
set pages 300
select * from DBA_HIST_IOSTAT_DETAIL;

prompt ====================================================
prompt == Show IO Metric
prompt ====================================================
select * from dba_hist_sysmetric_history where metric_name like 'I/O%Megabytes%';

prompt ====================================================
prompt == End of check_io.sql
prompt ====================================================

How to generate a report of all invalid objects in the database? (Oracle)

When should we use this script?
--Before and after upgrading your database
--Before and after deploying new DDL changes to your database
--Debug ORA-04063 or similar errors


DOC
#######################################################################
#######################################################################
   The following statement will generate a html report Invalid.html
   which shows invalid object details of the database.
   Please note that the last two statements might fail in higher db
   versions.
#######################################################################
#######################################################################
#

spool Invalid.html

set pagesize 50000
set markup html on

col comp_id format a10
col comp_name format a30
col version format a10
col object_name format a30
col owner format a15
select * from v$version;
select comp_id, comp_name, status, version from dba_registry;
select count(*) from dba_objects where status='INVALID';
select owner,object_type,count(*) from dba_objects where status='INVALID' group by owner,object_type;
select owner,object_name,object_type, status from dba_objects where status = 'INVALID';
select owner,object_name,object_type, status from dba_objects where status = 'INVALID'and owner IN ('SYSTEM' , 'SYS' );
select comp_name, version,status from dba_registry where STATUS <> 'VALID';
select * from registry$sys_inv_objs;
select * from registry$nonsys_inv_objs;

set markup html off
spool off

How to find the sessions that are using most CPUs? (Oracle)

Here is a handy script I usually use.

check_cpu.sql

prompt ====================================================
prompt == Start of check_cpu.sql
prompt ====================================================

prompt ====================================================
prompt == Check CPU notes
prompt == OS level CPU usage can be monitor by top or topas
prompt == Where you should be able to pin point the top
prompt == process ID
prompt == 
prompt ====================================================
prompt ====================================================
prompt == Show top 20 CPU usage sessions
prompt ====================================================
set lines 300
set pages 300
col machine format a20
col program format a30
col name format a30
select * from
(SELECT se.username, ss.sid,se.machine,se.program, name, se.status, ROUND (value/100) "CPU Usage Seconds", se.sql_id
FROM v$session se, v$sesstat ss, v$statname st
WHERE ss.statistic# = st.statistic#
    AND name LIKE  '%CPU used by this session%'
   AND se.sid = ss.SID 
   AND se.username IS NOT NULL
  ORDER BY value DESC)
where rownum <=20;


prompt ====================================================
prompt == Check Sql Text related to above sql_id
prompt ====================================================
SET LONG 100000 
select sql_id, SQL_TEXT from v$SQL where SQL_ID in (
select sql_id from
(SELECT se.username, ss.sid,se.machine,se.program, name, se.status, ROUND (value/100) "CPU Usage Seconds", se.sql_id
FROM v$session se, v$sesstat ss, v$statname st
WHERE ss.statistic# = st.statistic#
    AND name LIKE  '%CPU used by this session%'
   AND se.sid = ss.SID 
   AND se.username IS NOT NULL
  ORDER BY value DESC)
where rownum <=20
);

prompt ====================================================
prompt == End of check_cpu.sql
prompt ====================================================