ORACLE DATABASE HEALTH CHECK SCRIPTS
-----------------------------------------------------------------
Check the Database Availability
--------------------------------
sqlplus '/as sysdba'
sql>startup
sql>select instance_name,status from v$instance;
check the database read or write mode
-----------------------------------------------
sql>select name,open_mode from v$database;
check the oracle process run or not
-----------------------------------
# ps -ef |grep pmon
Health check of database instance &Listener
------------------------------------------
open terminal in linux machine
# lsnrctl status<listener-name>
if not started
# lsnrctl start <listener-name>
Check Long running process unix process
---------------------------------------
# ps -ef | grep java
Check Monitor alert log file in oracle
--------------------------------------
Log will be created in directory below the value of diagnostic_dest_parameter
DIAGNOSTIC_DEST parameter DIAGNOSTIC_DEST/diag/rdbms/db_name/oracle_sid/trace
alert.logfile
trace file is created by the user process and it is written in USER_DUMP_DEST
alert file is created by server process and it is written in BACKGROUND_DUMP_DEST
You can delete alertsid.log any time while database is running when any log entry occurs it will create automatically no need to startup or shutdown,
The alertlog file also reffered to as the alert.log is chronlogical log of messages and errors written in out by oracle database typically mesages found in the file is
database startup,shutdown,log switches,space errors etc
Check Monitoring space availability
----------------------------------
my oracle work history
sql>select job_name,session_id,running_instance,elapsed_time,cpu_used from dba_scheduler_running_jobs
Check size of schema
--------------------
sql>select sum(bytes)/1024/1024/1024 as 'size in gb' from dba_segments where owner= 'UPPER';
Check Size of database
----------------------
sql>select round((sum(bytes)/1048576/1024),2) from v$ datafile;
Check Size of table in oracle
-----------------------------
sql>select sum(bytes) from user_segments where segments_type='TABLE';
Check to get size in mb
-------------------------
sql>select segment_name,segment_type,bytes/1024/1024 mb from dba_segments where segment= type='TABLE' and segment_name='emp';
Database, Datafile and Backup
------------------------------
sql>select name,open_mode,log_mode,database_role,force_logging,flashback_on from v$database;
sql>select * from v$recover_file;
sql>select distinct status,count() from v$datafile group by status;
sql>select name, RECOVER from v$datafile_header where RECOVER != 'NO';
sql>select name, ERROR from v$datafile_header where ERROR is NOT NULL;
sql>select name, ts# from v$datafile where upper(name) like '%MISSING%';
sql>select NAME, FILE# from v$datafile where status='RECOVER';
sql>select status,count() from v$backup group by status;
Check DBA Registry
--------------------
sql>set lines 150 pages 500
column COMP_NAME format a45
column version format a15
column status format a12
sql>select comp_name,version,status from dba_registry;
Check DBA Registry History
-----------------------
sql>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
sql>select * from dba_registry_history;
Check Invalid Object
---------------------------
sql>col object_name format a50
col owner format a10
set lines 300
set pages 1000
sql>select object_name,object_type,owner,status from dba_objects where status = 'INVALID';
Queries to check Locks
-----------------------
sql>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
--------------------------
sql>select 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
----------------------------------------------------------------------------------------------------------------------
A)$ sqlplus
sql> spool hcheck.log
sql> @hcheck
sql> spool off
For 12c with Multitenant, connect to each PDB to run the script.
----------------------------------------------------------------
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
Note: info on database health check it may be differ from your environment like production,testing ,development
THANKS YOU FOR VIEWING MY BLOG FOR MORE UPDATES FOLLOW ME OR SUBSCRIBE ME
