Oracle Technology‎ > ‎Database‎ > ‎

database notes

August 2017 Roadmap

posted 11 Aug 2017, 02:56 by Alan Nolan-Davies


Release 12.2: New releases will be annual and the version will be the last two digits of the release year. The release originally planned as 12.2.0.2 will now be release 18, and the release originally planned as 12.2.0.3 will be release 19. Releases 18 and 19 will be treated as under the umbrella of 12.2 for Lifetime Support purposes. The current plan is for Oracle Database 19 to be the last release for 12.2. This may change in the future to Oracle 20 as the last release for 12.2.  

See Oracle Note Release Schedule of Current Database Releases (Doc ID 742060.1)

RAC and DB Support Tools Bundle (Doc ID 1594347.1)

posted 17 Sep 2014, 09:57 by Alan Nolan-Davies   [ updated 19 Mar 2015, 06:52 ]


Oracle's centre of expertise has been busy building tools to automatically monitor Oracle Databases that runs on the host.

Many customers have put together their own collections of disparate tools to try and do this, but obviously using those provided by Oracle will reduce the cost of developing and deploying these tools with the major advantage that Oracle Support can be contacted when any issues arise.

The Oracle Support Note, where you can download all of these tools bundled together, is RAC and DB Support Tools Bundle (Doc ID 1594347.1)

The tools included in the Support Tools Bundle are:

  • ORAchk (formerly RACcheck) - Proactive, self service tool tool to prevent rediscovery of known issues.  See Document 1268927.1 for additional details
  • Trace File Analyzer Collector (aka TFA) - Diagnostic file collection utility to simplify collection of ALL diagnostic data into a single command executed from a single node.  Diagnostic files are also trimmed around the incident time to drastically reduce diagnostic file upload sizes.  Additional information can be found in Document 1513912.1.
  • OSWatcher (formerly OSWatcher Black Box) - Script to collect and archive OS metrics.  OSWatcher is required for many reactive types of issues including Instance/Node Evictions and Performance Issues.  See Document 301137.1 for additional details.
  • Procwatcher - A script used to automate and capture diagnostic output for Severe Database Performance issues and Session Level Hangs.  See Document 459694.1 for additional details.
  • ORATOP - A utility allowing for near real-time monitoring of databases (RAC and Single Instance), this utility is built for the Linux platform but can remotely monitor databases on ANY platform from a Linux Client.  See Document 1500864.1 for additional details.
  • SQLT - A tool designed to assist in the tuning of a given SQL Statement.  See Document 215187.1 for additional details.
  • RDA - Powerful diagnostic tool which provides a unified package of support diagnostics tools which provides comprehensive picture of the customer's environment to aid in problem diagnosis.  See Document 314422.1for additional details.
  • DA - (Installed with RDA) The Diagnostic Assistant (DA) tool provides a common, light-weight interface to multiple diagnostic collection tools (ADR, RDA, OCM, Explorer, and others).  See Document 201804.1 for additional details.
Be aware though that you must be able to run the bash shell to take advantage of a couple of these tools (Orachk & TFA), this standard came in with Oracle11g and shows the bias that Oracle have towards linux deployments.

Memory Resizing

posted 21 May 2014, 04:15 by Alan Nolan-Davies

select START_TIME, component, oper_type, oper_mode, initial_size/1024/1024 "INITIAL", FINAL_SIZE/1024/1024 "FINAL", END_TIME 
from v$sga_resize_ops 
where component in ('DEFAULT buffer cache', 'shared pool') and status = 'COMPLETE' 
order by start_time, component; 

KGH: NO ACCESS

posted 21 May 2014, 04:12 by Alan Nolan-Davies

select * from v$sgastat where pool = 'shared pool' and (name in ('free memory', 'sql area', 'library cache', 'miscellaneous', 'row cache', 'KGH: NO ACCESS') ); 

Active Sessions with Command

posted 21 May 2014, 03:39 by Alan Nolan-Davies

SELECT vs.inst_id, vs.username, vs.sid, vs.serial#, 
         TO_CHAR(vs.logon_time,'DD-MON-YY hh24:mi:ss') CONNECTED, vs.status,
         CASE 
              when vs.command = 0 then 'IDLE'
              when vs.command = 1 then 'CREATE TABLE' 
              when vs.command = 2 then 'INSERT' 
              when vs.command = 3 then 'SELECT'
              when vs.command = 4 then 'CREATE CLUSTER' 
              when vs.command = 5 then 'ALTER CLUSTER'
              when vs.command = 6 then 'UPDATE' 
              when vs.command = 7 then 'DELETE' 
              when vs.command = 8 then 'DROP CLUSTER' 
              when vs.command = 9 then 'CREATE INDEX' 
              when vs.command = 10 then 'DROP INDEX' 
              when vs.command = 11 then 'ALTER INDEX' 
              when vs.command = 12 then 'DROP TABLE' 
              when vs.command = 13 then 'CREATE SEQUENCE' 
              when vs.command = 14 then 'ALTER SEQUENCE' 
              when vs.command = 15 then 'ALTER TABLE'
              when vs.command = 16 then 'DROP SEQUENCE'
              when vs.command = 17 then 'GRANT OBJECT'
              when vs.command = 18 then 'REVOKE OBJECT'
              when vs.command = 19 then 'CREATE SYNONYM'
              when vs.command = 20 then 'DROP SYNONYM'
              when vs.command = 21 then 'CREATE VIEW'
              when vs.command = 22 then 'DROP VIEW'
              when vs.command = 23 then 'VALIDATE INDEX'
              when vs.command = 24 then 'CREATE PROCEDURE'
              when vs.command = 25 then 'ALTER PROCEDURE'
              when vs.command = 26 then 'LOCK' 
              when vs.command = 27 then 'NO-OP'
              when vs.command = 28 then 'RENAME'
              when vs.command = 29 then 'COMMENT'
              when vs.command = 30 then 'AUDIT OBJECT'
              when vs.command = 31 then 'NOAUDIT OBJECT'
              when vs.command = 32 then 'CREATE DATABASE LINK'
              when vs.command = 33 then 'DROP DATABASE LINK'
              when vs.command = 34 then 'CREATE DATABASE'
              when vs.command = 35 then 'ALTER DATABASE'
              when vs.command = 36 then 'CREATE ROLLBACK SEG'
              when vs.command = 37 then 'ALTER ROLLBACK SEG'
              when vs.command = 38 then 'DROP ROLLBACK SEG'
              when vs.command = 39 then 'CREATE TABLESPACE'
              when vs.command = 40 then 'ALTER TABLESPACE'
              when vs.command = 41 then 'DROP TABLESPACE'
              when vs.command = 42 then 'ALTER SESSION'
              when vs.command = 43 then 'ALTER USER'
              when vs.command = 44 then 'COMMIT'
              when vs.command = 45 then 'ROLLBACK'
              when vs.command = 46 then 'SAVEPOINT'
              when vs.command = 47 then 'PL/SQL EXECUTE'
              when vs.command = 48 then 'SET TRANSACTION'
              when vs.command = 49 then 'ALTER SYSTEM'
              when vs.command = 50 then 'EXPLAIN'
              when vs.command = 51 then 'CREATE USER'
              when vs.command = 52 then 'CREATE ROLE'
              when vs.command = 53 then 'DROP USER'
              when vs.command = 54 then 'DROP ROLE'
              when vs.command = 55 then 'SET ROLE'
              when vs.command = 56 then 'CREATE SCHEMA'
              when vs.command = 57 then 'CREATE CONTROL FILE'
              when vs.command = 59 then 'CREATE TRIGGER'
              when vs.command = 60 then 'ALTER TRIGGER'
              when vs.command = 61 then 'DROP TRIGGER'
              when vs.command = 62 then 'ANALYZE TABLE'
              when vs.command = 63 then 'ANALYZE INDEX'
              when vs.command = 64 then 'ANALYZE CLUSTER'
              when vs.command = 65 then 'CREATE PROFILE'
              when vs.command = 66 then 'DROP PROFILE' 
              when vs.command = 67 then 'ALTER PROFILE' 
              when vs.command = 68 then 'DROP PROCEDURE' 
              when vs.command = 70 then 'ALTER RESOURCE COST' 
              when vs.command = 71 then 'CREATE SNAPSHOT LOG' 
              when vs.command = 72 then 'ALTER SNAPSHOT LOG' 
              when vs.command = 73 then 'DROP SNAPSHOT LOG' 
              when vs.command = 74 then 'CREATE SNAPSHOT' 
              when vs.command = 75 then 'ALTER SNAPSHOT' 
              when vs.command = 76 then 'DROP SNAPSHOT' 
              when vs.command = 77 then 'CREATE TYPE' 
              when vs.command = 78 then 'DROP TYPE' 
              when vs.command = 79 then 'ALTER ROLE' 
              when vs.command = 80 then 'ALTER TYPE' 
              when vs.command = 81 then 'CREATE TYPE BODY' 
              when vs.command = 82 then 'ALTER TYPE BODY' 
              when vs.command = 83 then 'DROP TYPE BODY' 
              when vs.command = 84 then 'DROP LIBRARY' 
              when vs.command = 85 then 'TRUNCATE TABLE' 
              when vs.command = 86 then 'TRUNCATE CLUSTER' 
              when vs.command = 91 then 'CREATE FUNCTION'
              when vs.command = 92 then 'ALTER FUNCTION' 
              when vs.command = 93 then 'DROP FUNCTION' 
              when vs.command = 94 then 'CREATE PACKAGE' 
              when vs.command = 95 then 'ALTER PACKAGE' 
              when vs.command = 96 then 'DROP PACKAGE' 
              when vs.command = 97 then 'CREATE PACKAGE BODY' 
              when vs.command = 98 then 'ALTER PACKAGE BODY' 
              when vs.command = 99 then 'DROP PACKAGE BODY' 
              when vs.command = 100 then 'LOGON' 
              when vs.command = 101 then 'LOGOFF' 
              when vs.command = 102 then 'LOGOFF BY CLEANUP' 
              when vs.command = 103 then 'SESSION REC' 
              when vs.command = 104 then 'SYSTEM AUDIT' 
              when vs.command = 105 then 'SYSTEM NOAUDIT' 
              when vs.command = 106 then 'AUDIT DEFAULT' 
              when vs.command = 107 then 'NOAUDIT DEFAULT' 
              when vs.command = 108 then 'SYSTEM GRANT' 
              when vs.command = 109 then 'SYSTEM REVOKE' 
              when vs.command = 110 then 'CREATE PUBLIC SYNONYM' 
              when vs.command = 111 then 'DROP PUBLIC SYNONYM' 
              when vs.command = 112 then 'CREATE PUBLIC DATABASE LINK' 
              when vs.command = 113 then 'DROP PUBLIC DATABASE LINK' 
              when vs.command = 114 then 'GRANT ROLE' 
              when vs.command = 115 then 'REVOKE ROLE' 
              when vs.command = 116 then 'EXECUTE PROCEDURE' 
              when vs.command = 117 then 'USER COMMENT' 
              when vs.command = 118 then 'ENABLE TRIGGER' 
              when vs.command = 119 then 'DISABLE TRIGGER' 
              when vs.command = 120 then 'ENABLE ALL TRIGGERS' 
              when vs.command = 121 then 'DISABLE ALL TRIGGERS' 
              when vs.command = 122 then 'NETWORK ERROR' 
              when vs.command = 123 then 'EXECUTE TYPE' 
              when vs.command = 157 then 'CREATE DIRECTORY' 
              when vs.command = 158 then 'DROP DIRECTORY' 
              when vs.command = 159 then 'CREATE LIBRARY' 
              when vs.command = 160 then 'CREATE JAVA' 
              when vs.command = 161 then 'ALTER JAVA' 
              when vs.command = 162 then 'DROP JAVA' 
              when vs.command = 163 then 'CREATE OPERATOR' 
              when vs.command = 164 then 'CREATE INDEXTYPE' 
              when vs.command = 165 then 'DROP INDEXTYPE' 
              when vs.command = 167 then 'DROP OPERATOR' 
              when vs.command = 168 then 'ASSOCIATE STATISTICS' 
              when vs.command = 169 then 'DISASSOCIATE STATISTICS' 
              when vs.command = 170 then 'CALL METHOD' 
              when vs.command = 171 then 'CREATE SUMMARY' 
              when vs.command = 172 then 'ALTER SUMMARY' 
              when vs.command = 173 then 'DROP SUMMARY' 
              when vs.command = 174 then 'CREATE DIMENSION' 
              when vs.command = 175 then 'ALTER DIMENSION' 
              when vs.command = 176 then 'DROP DIMENSION' 
              when vs.command = 177 then 'CREATE CONTEXT' 
              when vs.command = 178 then 'DROP CONTEXT' 
              when vs.command = 179 then 'ALTER OUTLINE' 
              when vs.command = 180 then 'CREATE OUTLINE' 
              when vs.command = 181 then 'DROP OUTLINE'
              when vs.command = 182 then 'UPDATE INDEXES' 
              when vs.command = 183 then 'ALTER OPERATOR' 
              else 'EEK'
         END as COMMAND,  vs.osuser, vs.machine, vs.program, vs.module
FROM gv$session vs
where vs.status = 'ACTIVE'
and type = 'USER'

Online Redo Log Members

posted 20 May 2014, 08:37 by Alan Nolan-Davies

SELECT group#        ,
  thread#             ,
  sequence#           ,
  (bytes/1024)/1024 MB,
  members             ,
  status              ,
  first_change#       ,
  TO_CHAR(first_time,'DD-MON-YY HH24:MI SS') FIRST_TIME
from v$log

Log Switch History

posted 20 May 2014, 08:14 by Alan Nolan-Davies

SELECT   TO_CHAR(first_time, 'YYYY-MM-DD') AS "Date", TO_CHAR(first_time, 'Day') AS "Day",
         SUM(DECODE(TO_CHAR(first_time, 'hh24'), '00', 1, 0)) AS "00",
         SUM(DECODE(TO_CHAR(first_time, 'hh24'), '01', 1, 0)) AS "01",
         SUM(DECODE(TO_CHAR(first_time, 'hh24'), '02', 1, 0)) AS "02",
         SUM(DECODE(TO_CHAR(first_time, 'hh24'), '03', 1, 0)) AS "03",
         SUM(DECODE(TO_CHAR(first_time, 'hh24'), '04', 1, 0)) AS "04",
         SUM(DECODE(TO_CHAR(first_time, 'hh24'), '05', 1, 0)) AS "05",
         SUM(DECODE(TO_CHAR(first_time, 'hh24'), '06', 1, 0)) AS "06",
         SUM(DECODE(TO_CHAR(first_time, 'hh24'), '07', 1, 0)) AS "07",
         SUM(DECODE(TO_CHAR(first_time, 'hh24'), '08', 1, 0)) AS "08",
         SUM(DECODE(TO_CHAR(first_time, 'hh24'), '09', 1, 0)) AS "09",
         SUM(DECODE(TO_CHAR(first_time, 'hh24'), '10', 1, 0)) AS "10",
         SUM(DECODE(TO_CHAR(first_time, 'hh24'), '11', 1, 0)) AS "11",
         SUM(DECODE(TO_CHAR(first_time, 'hh24'), '12', 1, 0)) AS "12",
         SUM(DECODE(TO_CHAR(first_time, 'hh24'), '13', 1, 0)) AS "13",
         SUM(DECODE(TO_CHAR(first_time, 'hh24'), '14', 1, 0)) AS "14",
         SUM(DECODE(TO_CHAR(first_time, 'hh24'), '15', 1, 0)) AS "15",
         SUM(DECODE(TO_CHAR(first_time, 'hh24'), '16', 1, 0)) AS "16",
         SUM(DECODE(TO_CHAR(first_time, 'hh24'), '17', 1, 0)) AS "17",
         SUM(DECODE(TO_CHAR(first_time, 'hh24'), '18', 1, 0)) AS "18",
         SUM(DECODE(TO_CHAR(first_time, 'hh24'), '19', 1, 0)) AS "19",
         SUM(DECODE(TO_CHAR(first_time, 'hh24'), '20', 1, 0)) AS "20",
         SUM(DECODE(TO_CHAR(first_time, 'hh24'), '21', 1, 0)) AS "21",
         SUM(DECODE(TO_CHAR(first_time, 'hh24'), '22', 1, 0)) AS "22",
         SUM(DECODE(TO_CHAR(first_time, 'hh24'), '23', 1, 0)) AS "23"
FROM     v$log_history
GROUP BY TO_CHAR(first_time, 'YYYY-MM-DD'), TO_CHAR(first_time, 'Day') 
ORDER BY TO_CHAR(first_time, 'YYYY-MM-DD'), TO_CHAR(first_time, 'Day')

RAC threads

posted 20 May 2014, 07:46 by Alan Nolan-Davies

select thread#, status, enabled, groups, instance,
       to_char(open_time,'dd-mon-yy hh24:mi') as OPEN_TIME,
       current_group#, sequence#, 
       checkpoint_change#, to_char(checkpoint_time,'dd-mon-yy hh24:mi') as CHECKPOINT_TIME,
       enable_change#, to_char(enable_time,'dd-mon-yy hh24:mi') as ENABLE_TIME,
       disable_change#, to_char(disable_time,'dd-mon-yy hh24:mi') as DISABLE_TIME,
       last_redo_sequence#, last_redo_block,
       last_redo_change#, to_char(last_redo_time,'dd-mon-yy hh24:mi') as LAST_REDO_TIME
from sys.V_$Thread

Waiting Sessions

posted 20 May 2014, 07:41 by Alan Nolan-Davies

select sw.inst_id, sw.sid, sw.state, sw.event, sw.seconds_in_wait seconds, 
sw.p1, sw.p2, sw.p3, sa.sql_text last_sql 
from gv$session_wait sw, gv$session s, gv$sqlarea sa 
where sw.event not in 
('rdbms ipc message','smon timer','pmon timer', 
'SQL*Net message from client','lock manager wait for remote message', 
'ges remote message', 'gcs remote message', 'gcs for action', 'client message', 
'pipe get', 'null event', 'PX Idle Wait', 'single-task message', 
'PX Deq: Execution Msg', 'KXFQ: kxfqdeq - normal deqeue', 
'listen endpoint status','slave wait','wakeup time manager') 
and sw.seconds_in_wait > 0 
and (sw.inst_id = s.inst_id and sw.sid = s.sid) 
and (s.inst_id = sa.inst_id and s.sql_address = sa.address) 
order by seconds desc

Top 10 Wait Events

posted 20 May 2014, 07:40 by Alan Nolan-Davies

select inst_id, event, time_waited, total_waits, total_timeouts 
from (select inst_id, event, time_waited, total_waits, total_timeouts 
from gv$system_event where event not in ('rdbms ipc message','smon timer', 
'pmon timer', 'SQL*Net message from client','lock manager wait for remote message', 
'ges remote message', 'gcs remote message', 'gcs for action', 'client message', 
'pipe get', 'null event', 'PX Idle Wait', 'single-task message', 
'PX Deq: Execution Msg', 'KXFQ: kxfqdeq - normal deqeue', 
'listen endpoint status','slave wait','wakeup time manager') 
order by time_waited desc) 
where rownum < 11 
order by time_waited desc;

1-10 of 14