https://dbissues.blogspot.com/2018/06/oracle-database-health-check.html
1: Check the free space of all mount points from the following command.
Command: df -h
################################################################
2: Check is there any unwanted file or folder which is taking space but don't delete them only tell their
database administrator (You have to check this manually).
#################################################################
3: Check the database size where all the data files are present with the following command.
for example :
cd /u01/oracle/PROD/db/apps_st
Command: du -sh data
#####################################################################
4: Check the amount of memory through the following command and check how much memory is utilizing and how much is free.
Command : top
#######################################################################
5: Must check thier user and group name is database and application is running on same user and group
Or different from the following command.
Command: ls -alrt
##########################################################################
6: Check all users password's check is there any password which is set to default .
for example:
/as sysdba
system / manager
apps/apps
gl/gl
ap/ap
Note: If there is any password which is set to default as above tell there database administrator to change it.
#####################################################################
7: Check from which parameter file database instance is running. Is it is running with pfile Or spfile. from the following command.
Command :
sql>show parameter spfile
Note : Please if it is running with pfile please tell thier database adminitrator to run thier database with
spfile because pfile is easily editible.
#######################################################################
8: Check control file status from sqlplus from the following command. If all control file set to same mount points are same location tell their database administrator to multiplex it.
connect as sysdba
SQL> select status, name from v$controlfile;
STATUS NAME
------- ---------------------------------
/u01/oradata/L102/control01.ctl
/u02/oradata/L102/control02.ctl
###############################################################
9: Check number of Datatop's from the following commands.
conn sqlplus
sql> select filename from dba_data_files;
##############################################################################
10: Check the archiving is enabled and log file is switching successfully to its position. from the following command.
To check location
sql> archive log list.
Switch logfile to check weather its switching to its position or not.
sql> alter system switch logfile.
########################################################################
11: Check alert log file check is there is any error inside error log .Check location from the following command.
sql> show parameter background;
###########################################################################
12: Check rman backup policy from rman through the following command and check Rman backup log
check if there is any error inside or not.
Rman> show all;
#############################################################################
13: Check the tablespace size from following command if need to add tell their database administrator add it.
SELECT a.tablespace_name,
ROUND (((c.BYTES - NVL (b.BYTES, 0)) / c.BYTES) * 100,2) percentage_used,
c.BYTES / 1024 / 1024 space_allocated,
ROUND (c.BYTES / 1024 / 1024 - NVL (b.BYTES, 0) / 1024 / 1024,2) space_used,
ROUND (NVL (b.BYTES, 0) / 1024 / 1024, 2) space_free,
c.DATAFILES
FROM dba_tablespaces a,
( SELECT tablespace_name,
SUM (BYTES) BYTES
FROM dba_free_space
GROUP BY tablespace_name
) b,
( SELECT COUNT (1) DATAFILES,
SUM (BYTES) BYTES,
tablespace_name
FROM dba_data_files
GROUP BY tablespace_name
) c
WHERE b.tablespace_name(+) = a.tablespace_name
AND c.tablespace_name(+) = a.tablespace_name
ORDER BY NVL (((c.BYTES - NVL (b.BYTES, 0)) / c.BYTES), 0) DESC;
##############################################################################
14: Check how much temporary tablespace is running from the following command.
sql> select tablespace_name, sum(bytes)/1024/1024 mb
from dba_temp_files
group by tablespace_name;
#############################################################################
15: Check which temporary tablespace is set to default from the following command.
select * from database_properties where property_name like 'DEFAULT%TABLESPACE';
#################################################################################
16: Check current usage of temporary tablespace.
select ss.tablespace_name,
sum((ss.used_blocks*ts.blocksize))/1024/1024 mb
from gv$sort_segment ss, sys.ts$ ts
where ss.tablespace_name = ts.name
group by ss.tablespace_name;
Note : If need to add than tell thier database administrator to add.
############################################################################
17: Check the size of undo tablespace from the following command.
sql> select tablespace_name,sum(bytes)/1024/1024 "MB" from dba_data_files where TABLESPACE_NAME like '%UNDO%' group by tablespace_name;
#################################################################################
18: check name and retention policy of undo tablespace from the following command.
sql> show parameter undo;
#################################################################################
19: Check the datafile size of undo tablespace from the following command.
select FILE_NAME,TABLESPACE_NAME,sum(bytes)/1024/1024 "MB" from dba_data_files where TABLESPACE_NAME like '%UNDO%' group by FILE_NAME,TABLESPACE_NAME;
##########################################################################
20: Check free space of tablespace.
SELECT SUM(BYTES)/1024/1024 "MB" FROM DBA_FREE_SPACE WHERE TABLESPACE_NAME ='UNDO_TBS';
Note: if need to add then tell their database administrator to add datafile.
#####################################################
21: Check the last log of adpreclone.pl on database Tier check is thier any error inside the log or not from the following location.
for example:
/u01/oracle/VIS/db/tech_st/11.1.0/appsutil/log/VIS_erp/StageDBTier_12251847.log
############################################################################
22: Check the last log of adpreclone.pl on APPS Tier check is their any error inside the log or not from the following location.
for example:
/u01/oracle/VIS/inst/apps/VIS_erp/admin/log/StageAppsTier_12251850.log
#################################################################################
22: Check invalid objects through the following command.
sql> select OWNER,OBJECT_NAME,OBJECT_TYPE,STATUS from dba_objects where status='INVALID';
################################################################################
23: Check tnsnames.ora file to check dataguard tns ping is successful or not through the following command.
Command: tnsping standby
###############################################################################
24: Check cold backup going fine check previous backup size and current backup size through the following command.
Command : du -sh
##############################################################################
25: Check hosts file from location to verify standby ip and hostname through the following command
and ping standby ip on primary server to check no network break found between standby and primary.
cat /etc/hosts
################################################################################
26: Check recycle bin is there any unwanted data is stored in recycle bin from the following command.
select * from recyclebin.
Note: Is there any data which is in recycle bin tell their dba to purge recyclebin to realease space from database.
#################################################################################
27: Sga should be at least 40% or more then 40% of total ram. and check pga must be 20% of sga or higher.
#################################################################################
28: Check DBA_USERS to verify is there any unwanted or expired custom user is there with the following command.
sql> select * from dba_users;
#################################################################################
29: check is there any user which have dba role through the following command.
sql>select * from DBA_ROLE_PRIVS;
#################################################################################
30: check dba_db_links to check is there any unwanted db_link is there through the following command.
sql> select * from dba_db_links;
#################################################################################
31: Check crontab to verify how much job are running automatically and also check are all shell scripts doing there job successfully or not through the command.
crontab -l
#################################################################################
32: Run active user report and active responsibility report from front end to check whether is there any.
expired user left or not.
#################################################################################
33: Check the concurrent status from end that how many concurrent is running and how many is free check this for load testing.
#################################################################################
34: Check is there is any corruption in database.
select * from V$DATABASE_BLOCK_CORRUPTION;
#################################################################################
34: Check audit logs from backend. through the command .
SQL> show parameter audit.
#################################################################################
35: The v$resource_limit shows the current and maximum global resource utilization for some system resources.
sql> select * from v$resource_limit where resource_name in ('processes','sessions',transactions');
#################################################################################
36: Generate AWR report for health check .
sql> @$ORACLE_HOME/rdbms/admin/awrrpt.sql <---AUTOMATUC WORK REPOSITORY
#################################################################################
37: ADDM report provides Findings and Recommendations to fix the issue.
SQL> @?/rdbms/admin/addmrpt.sql
#################################################################################
38: Check database status.
sql> select name DB_NAME,HOST_NAME,DATABASE_ROLE,OPEN_MODE,version DB_VERSION,LOGINS,to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME" from v$database,gv$instance;
#################################################################################
39 : EBS Database Parameter Settings Analyzer (Doc ID 1953468.1)
conn apps/apps
>@db_param_analyzer.sql
fnd users = 300
#################################################################################
40: Undo adviser How much undo have you needed and how much you have.
col "ACTUAL UNDO SIZE [MByte]" for 999999999
col "UNDO RETENTION [Sec]" for a20
col "OPTIMAL UNDO RETENTION [Sec]" for 999999999
SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MB]",
SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
(TO_NUMBER(e.value) * TO_NUMBER(f.value) *
g.undo_block_per_sec) / (1024*1024)
"NEEDED UNDO SIZE [MB]"
FROM (SELECT SUM(a.bytes) undo_size FROM v$datafile a, v$tablespace b, dba_tablespaces c WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE' AND b.name = c.tablespace_name AND a.ts# = b.ts# ) d, v$parameter e, v$parameter f, (
SELECT MAX(undoblks/((end_time-begin_time)*3600*24)) undo_block_per_sec FROM v$undostat ) g WHERE e.name = 'undo_retention'
AND f.name = 'db_block_size';
#################################################################################
