OracleInstance & database

Database and instance overview

One row per instance: host, version, uptime, role, open mode, archive log mode and whether it's a CDB. The first thing to run when you land on an unfamiliar server.

12c+All RAC instances

Not yet verified. How scripts are tested

ora-dbinfo.sql

1CLEAR COLUMNS2SET LINESIZE 200 PAGESIZE 100 TRIMOUT ON TAB OFF3COLUMN inst_name HEADING "INSTANCE"  FORMAT A124COLUMN host_name HEADING "HOST"      FORMAT A25 TRUNCATE5COLUMN version   FORMAT A116COLUMN status    FORMAT A87COLUMN started   FORMAT A168COLUMN up_days   HEADING "UP DAYS"   FORMAT 9990.99COLUMN db_name   HEADING "DB"        FORMAT A910COLUMN role      FORMAT A1611COLUMN open_mode HEADING "OPEN MODE" FORMAT A2012COLUMN log_mode  HEADING "LOG MODE"  FORMAT A1213COLUMN cdb       FORMAT A314 15SELECT i.instance_name AS inst_name, i.host_name, i.version, i.status,16       TO_CHAR(i.startup_time, 'YYYY-MM-DD HH24:MI') AS started,17       ROUND(SYSDATE - i.startup_time, 1) AS up_days,18       d.name AS db_name, d.database_role AS role,19       d.open_mode, d.log_mode, d.cdb20FROM gv$instance i21CROSS JOIN v$database d22ORDER BY i.inst_id;

Save it as ora-dbinfo.sql and run it with SQL> @ora-dbinfo.

Open in denrepo

Part of these runbooks

Morning health check

More Oracle scripts: Instance & database

Instance status on every nodeHost, instance, version, status, whether logins are allowed, startup time and archiver state for every instance. A quick RAC-wide check after a…
Working with the spfileShows which spfile the instance is using, where parameter files live and the order Oracle searches for them, and what SCOPE = MEMORY, SPFILE and BOTH…
Non-default initialization parametersEvery parameter that has been changed from its default. Handy for comparing two databases or documenting a build.
Processes and sessions against their limitsCurrent and peak usage of processes, sessions and transactions since startup. A PEAK % near 100 means the next connection storm will hit ORA-00020.
Installed components and their statusEverything in DBA_REGISTRY. Anything not VALID after a patch or upgrade needs attention before you hand the database back.
Applied patches (datapatch history)Release updates and one-off patches applied through datapatch, newest first. Confirms the SQL side of a patch actually ran.