Important Oracle Database Health check

Here are the Important Oracle Database Health check

Table of Contents

Database, Datafile and Backup

select name,open_mode,log_mode,database_role,force_logging,flashback_on from v$database;
select * from v$recover_file;
select distinct status,count() from v$datafile group by status;
select name, RECOVER from v$datafile_header where RECOVER != 'NO';
select name, ERROR from v$datafile_header where ERROR is NOT NULL;
select name, ts# from v$datafile where upper(name) like '%MISSING%';
select NAME, FILE# from v$datafile where status='RECOVER';
select status,count() from v$backup group by status;

DBA Registry

set lines 150 pages 500
column COMP_NAME format a45
column version format a15
column status format a12
select comp_name,version,status from dba_registry;

DBA Registry History

set lines 150 pages 500
column action format a15
column namespace format a15
column comments format a20 wrap
column ACTION_TIME format a30
col BUNDLE_SERIES format a12
column version format a15
select * from dba_registry_history;

Invalid Object check

col object_name format a50
col owner format a10
set lines 300
set pages 1000
select object_name,
object_type,
owner,
status
from dba_objects
where status = 'INVALID'
/

Queries to check Locks

SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess,
id1, id2, lmode, request, type
FROM V$LOCK
WHERE (id1, id2, type) IN
(SELECT id1, id2, type FROM V$LOCK WHERE request>0)
ORDER BY id1, request;

Check Library cache locks

select /*+ all_rows */ w1.sid waiting_session,
h1.sid holding_session,
w.kgllktype lock_or_pin,
w.kgllkhdl address,
decode(h.kgllkmod, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',
'Unknown') mode_held,
decode(w.kgllkreq, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',
'Unknown') mode_requested
from dba_kgllock w, dba_kgllock h, v$session w1, v$session h1
where
(((h.kgllkmod != 0) and (h.kgllkmod != 1)
and ((h.kgllkreq = 0) or (h.kgllkreq = 1)))
and
(((w.kgllkmod = 0) or (w.kgllkmod= 1))
and ((w.kgllkreq != 0) and (w.kgllkreq != 1))))
and w.kgllktype = h.kgllktype
and w.kgllkhdl = h.kgllkhdl
and w.kgllkuse = w1.saddr
and h.kgllkuse = h1.saddr
/

hcheck.sql – Script to Check for Known Problems in Oracle8i, Oracle9i, Oracle10g, Oracle 11g and Oracle 12c and Above

To provide a single package which looks for common Data Dictionary problems.
The script checks consistency of selected dictionary relationships and looks for certain known issues – some reported “problems” will be normal and expected.

The script can be used with Oracle Release 9i onwards. hcheck8i.sql includes 8.1.
It is a lightweight “read-only” script with no impact.
The script is for use mainly under the guidance of Oracle Support.

$ sqlplus
Enter username: / as sysdba
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Productio
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> spool hcheck.log
SQL> @hcheck
SQL> spool off

For 12c with Multitenant, connect to each PDB to run the script. 

Enter username: / as sysdba
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> show pdbs
CON_ID   CON_NAME     OPEN MODE      RESTRICTED
-------- ------------ -------------- -------------
2        PDB$SEED     READ ONLY      NO
3        CDB1_PDB1    READ WRITE     NO
4        CDB1_PDB2    READ WRITE     NO
SQL> alter session set container=CDB1_PDB1;
Session altered.
SQL> spool hcheck.log
SQL> @hcheck
SQL> spool off

Metalink ID (Doc ID 136697.1)

Hope you like this post on Oracle Database Health check and this helps you in daily DBA work

Related Articles
Oracle DBA scripts :Top oracle dba scripts for Oracle Database for monitoring purpose ,how to see the waits events of the sessions and check the last analyzed
alter system switch logfile :Check out this post for alter system switch logfile ,alter system archive log current command,how to use them and what all they performs in the background
alter table move : Check out this post for the step by step method on How to rebuild the table in oracle using alter table move, How to rebuild table having lobs, long column
https://docs.oracle.com/cd/B19306_01/server.102/b14357/ch12007.htm

Recommended Courses

The following are some of the recommended courses you can buy if you want to get a step further

Given below are the links to some of the courses

Oracle DBA 11g/12c – Database Administration for Junior DBA : This course is good for the people who are starting as Junior DBA or aspire to be Oracle DBA. This will provide a good understanding of backup & recovery and General administration tasks
Oracle Database: Oracle 12C R2 RAC Administration : This course covers the installation, administration of Oracle RAC. A good course for Oracle DBA who want to upgrade his skills for Oracle RAC
Oracle Data Guard: Database Administration for Oracle 12C R2 : This course covers the installation, administration of Oracle Dataguard. A good course for Oracle DBA who want to upgrade his skills for Oracle Dataguard