Database health checks in Oracle

Performance Tuning

Performing Database health checks, when there is an issue reported by Application users.

  1. Check the Database details
  2. Monitor the consumption of resources
  3. Check the Alert Log
  4. Check Listener log
  5. Check Filesystem space Usage
  6. Generate AWR Report
  7. Generate ADDM Report
  8. Finding Locks,Blocker Session and Waiting sessions in a oracle database
  9. Check for alerts in OEM

1. Check the Database details :-

set pages 9999 lines 300
col OPEN_MODE for a10
col HOST_NAME for a30
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;

For RAC:

set pages 9999 lines 300
col OPEN_MODE for a10
col HOST_NAME for a30
select INST_ID,INSTANCE_NAME, 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;

2. Monitor the consumption of resources :-

select * from v$resource_limit where resource_name in (‘processes’,’sessions’);

The v$session views shows current sessions (which change rapidly),
while the v$resource_limit shows the current and maximum global resource utilization for some system resources.

3. Check the Alert Log :-

$locate alert_

— OR —

UNIX/Linux command to locate the alert log file

$ find / -name ‘alert_*.log’ /dev/null

vi
shift+g
?ORA- —> press enter key
press ‘n’ to check backwards/up side and ‘N’ for forward/down side search.

:q! –and press enter, for exiting vi editor

— OR —

11G

$ sqlplus “/as sysdba”
set pages 9999 lines 300
col NAME for a15
col VALUE for a60
select name, value from v$diag_info where name = ‘Diag Trace’;

On a server with multiple instances, each instance will have it’s own background_dump_dest in $ORACLE_HOME/diag/$ORACLE_SID/trace directory

Before 11G

$ sqlplus “/as sysdba”
set pages 9999 lines 300
show parameter BACKGROUND_DUMP_DEST;

On a server with multiple instances, each instance will have it’s own background_dump_dest in $ORACLE_HOME/admin/$ORACLE_SID/bdump directory

4. Check Listener log :-

$locate listener.log

— OR —

UNIX/Linux command to locate the listener log file

$ find / -name ‘listener.log’ 2> /dev/null
vi
shift+g
?TNS- —> press enter key
press ‘n’ to check backwords and ‘N’ for forword search.

AND

shift+g
?error —> press enter key
press ‘n’ to check backwords and ‘N’ for forword search.

:q! –and press enter, for exiting vi editor

— OR —

$lsnrctl status

from the output you can get the listener log location (see the value for “Listener Log File” in the output).

5. Check Filesystem space Usage :-

df -h (Linux / UNIX)

df -g (AIX)

6. Generate AWR Report :-

Generate AWR report for current and before to compare

SQL> @?/rdbms/admin/awrrpt.sql (For RAC, @?/rdbms/admin/awrrpti.sql – for each instance)

If Required,
SQL> @?/rdbms/admin/awrddrpt.sql —-> Produces Workload Repository Compare Periods Report

7. Generate ADDM Report :-

Generate ADDM report for current and before to compare.

ADDM report provides Findings and Recommendations to fix the issue.

SQL> @?/rdbms/admin/addmrpt.sql (For RAC, @?/rdbms/admin/addmrpti.sql – for each instance)

8. Finding Locks,Blocker Session and Waiting sessions in a oracle database :-

Select * from v$lock;

Select * from gv_$lock; (For RAC)

A fast way to check blocking/waiting situations

SELECT * FROM v$lock WHERE block > 0 OR request > 0;

set pages 50000 lines 32767
select object_name,s.inst_id,s.sid,s.serial#,p.spid,s.osuser,s.program,s.server,s.machine,s.status from gv$locked_object l,gv$session s,gv$process p,dba_objects o where l.object_id=o.object_id and l.session_id=s.sid and s.paddr=p.addr;

set pages 50000 lines 32767
col OBJECT_NAME for a40
col USERNAME for a10
col LOCKED_MODE for a15
col OBJECT_OWNER for a15
col OS_USER_NAME for a12

SELECT b.inst_id,b.session_id AS sid,NVL(b.oracle_username, ‘(oracle)’) AS username,a.owner AS object_owner,a.object_name,
Decode(b.locked_mode, 0, ‘None’,1, ‘Null (NULL)’,2, ‘Row-S (SS)’,3, ‘Row-X (SX)’,4, ‘Share (S)’,5, ‘S/Row-X (SSX)’,6, ‘Exclusive (X)’,
b.locked_mode) locked_mode,b.os_user_name FROM dba_objects a, gv$locked_object b WHERE a.object_id = b.object_id ORDER BY 1, 2, 3, 4;

Blocker Session and Waiting sessions

column Username format A15 column Sid format 9990 heading SID
column Type format A4 column Lmode format 990 heading ‘HELD’
column Request format 990 heading ‘REQ’ column Id1 format 9999990
column Id2 format 9999990 break on Id1 skip 1 dup

SELECT SN.Username, M.Sid, M.Type,
DECODE(M.Lmode, 0, ‘None’, 1, ‘Null’, 2, ‘Row Share’, 3, ‘Row
Excl.’, 4, ‘Share’, 5, ‘S/Row Excl.’, 6, ‘Exclusive’,
LTRIM(TO_CHAR(Lmode,’990′))) Lmode,
DECODE(M.Request, 0, ‘None’, 1, ‘Null’, 2, ‘Row Share’, 3, ‘Row
Excl.’, 4, ‘Share’, 5, ‘S/Row Excl.’, 6, ‘Exclusive’,
LTRIM(TO_CHAR(M.Request, ‘990’))) Request,
M.Id1, M.Id2
FROM V$SESSION SN, V$LOCK M
WHERE (SN.Sid = M.Sid and M.Request ! = 0)
or (SN.Sid = M.Sid and M.Request = 0 and Lmode != 4 and (id1, id2)
in (select S.Id1, S.Id2 from V$LOCK S where Request != 0 and S.Id1
= M.Id1 and S.Id2 = M.Id2) ) order by Id1, Id2, M.Request;

USERNAME SID TY LMODE REQUEST ID1 ID2


ORAPLAYERS 10 TX Exclusive None 123456 200
ORAPLAYERS 100 TX None Exclusive 123456 200

Session 10 is blocking(LMODE=Exclusive)

Session 100 is waiting(REQUEST=Exclusive)

The meaning of ID1 and ID2 depends on the lock TYPE.

ï We can see situations where a session is both a Blocker and a Waiter.

ï If there are only two sessions and both are Blockers and Waiters then we got a deadlock situation (which Oracle will solve automatically).

To find waiters:

set pages 50000 lines 32767
col LOCK_TYPE for a10
col MODE_HELD for a10
col MODE_REQUESTED for a10

select * from dba_waiters;

WAITING_SESSION HOLDING_SESSION LOCK_TYPE MODE_HELD MODE_REQUESTED LOCK_ID1 LOCK_ID2


Blocking details:

set pages 50000 lines 32767

select distinct s1.username || ‘@’ || s1.machine || ‘ ( INST=’ || s1.inst_id || ‘ SID=’ || s1.sid || ‘ ) is blocking ‘ || s2.username || ‘@’ || s2.machine || ‘ ( INST=’ || s1.inst_id || ‘ SID=’ || s2.sid || ‘ ) ‘ as blocking_status from gv$lock l1, gv$session s1, gv$lock l2, gv$session s2 where s1.sid=l1.sid and s2.sid=l2.sid and l1.BLOCK=1 and l2.request > 0 and l1.id1 = l2.id1 and l2.id2 = l2.id2 and l1.inst_id = s1.inst_id;

set pages 50000 lines 32767
col BLOCKER for a20
col BLOCKEE for a20
select (select username from v$session where sid = a.sid ) blocker,a.sid, ‘is blocking ‘,(select username from v$session where sid =b.sid) blockee,b.sid from v$lock a, v$lock b where a.block =1 and b.request > 0 and a.id1 = b.id1 and a.id2 = b.id2;

BLOCKER SID ‘ISBLOCKING’ BLOCKEE SID
——- ———- ———- ——- ——–

set pages 50000 lines 32767
select blocking_session, sid, serial#, wait_class,seconds_in_wait, username, osuser, program, logon_time from v$session where blocking_session is not NULL order by 1;

9. Check for alerts in OEM :-

Login to Oracle Enterprise Manager with valid username and password
click on “Alerts” tab
then select the below tabs one by one to see the alerts generated
Targets Down/Critical/Warning/Errors/

====================================================PART-2===============================================================

What SQL is currently using the most resources?

SELECT active_session_history.user_id,
dba_users.username, sqlarea.sql_text,
SUM(active_session_history.wait_time + active_session_history.time_waited) total_wait_time
FROM v$active_session_history active_session_history,
v$sqlarea sqlarea, dba_users
WHERE active_session_history.sample_time BETWEEN SYSDATE – 60/2880 AND SYSDATE
AND active_session_history.sql_id = sqlarea.sql_id
AND active_session_history.user_id = dba_users.user_id
GROUP BY active_session_history.user_id,sqlarea.sql_text, dba_users.username
ORDER BY 4;

========================================================================================================

Database HealthCheck
Stable Environment without any problem need to be monitored by DBA, Trying To Check Database With Specific Period or even everyday it’s called HealthCheck All that to avoid any Problem That could occur in the future which will prevent users and customer doing their work.

Therefore any DBA should perform Healthcheck for the database By Check the below :

1- Check Alert Log.

Check alert log is very important step, which its Indicate for any Error occurs in Database level, So the Data is always append, don’t forget to rotate alert log or purge if you are using 11g you can use ADRCI tools.
you can find alert log location :

in 10g

SQL> show parameter background_dump_dest;

NAME TYPE VALUE


background_dump_dest string /u01/app/oracle/admin/orcl/bdump
in 11g

SQL> show parameter diagnostic_dest

NAME TYPE VALUE


diagnostic_dest string /u01/app/oracle
2.Check Dump_File_szie

As we know Oracle Generate Trace file On OS level, But how Oracle manage their size, all this happened By Parameter Called “Max_dump_File_size” This Parameter define Max Size for OS Disk Space.

SQL> show parameter max_dump_file_size

NAME TYPE VALUE


max_dump_file_size string UNLIMITED
3.Audit Files

If you are enable Audit Parameter, Or SYSDBA Operation is Enable Oracle Will Start generate audit files which is take significant amount of space. and if you didn’t monitor this space it will prevent you access to oracle database until you remove them.

4.Check TableSpace (System, SysAux , Temp … )

You should monitor tablespace and check the free space and status for these table space and all the tablespace should be monitored to avoid any problem that could occur in the future since it will prevent users continuous their works, the below script check Size for each table space and Free space

SELECT /* + RULE */ df.tablespace_name “Tablespace”,
df.bytes / (1024 * 1024) “Size (MB)”,
SUM(fs.bytes) / (1024 * 1024) “Free (MB)”,
Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) “% Free”,
Round((df.bytes – SUM(fs.bytes)) * 100 / df.bytes) “% Used”
FROM dba_free_space fs,
(SELECT tablespace_name,SUM(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,df.bytes
UNION ALL
SELECT /* + RULE */ df.tablespace_name tspace,
fs.bytes / (1024 * 1024),
SUM(df.bytes_free) / (1024 * 1024),
Nvl(Round((SUM(fs.bytes) – df.bytes_used) * 100 / fs.bytes), 1),
Round((SUM(fs.bytes) – df.bytes_free) * 100 / fs.bytes)
FROM dba_temp_files fs,
(SELECT tablespace_name,bytes_free,bytes_used
FROM v$temp_space_header
GROUP BY tablespace_name,bytes_free,bytes_used) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
ORDER BY 4 DESC;

——OR——-

SELECT d.STATUS “Status”,
d.tablespace_name “Name”,
d.contents “Type”,
d.extent_management “Extent Management”,
d.initial_extent “Initial Extent”,
TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),’99,999,990.900′) “Size (M)”,
TO_CHAR(NVL(a.bytes – NVL(f.bytes, 0), 0)/1024/1024,’99,999,999.999′) “Used (M)”,
TO_CHAR(NVL((a.bytes – NVL(f.bytes, 0)) / a.bytes * 100, 0), ‘990.00’) “Used %”,
TO_CHAR(NVL(a.maxbytes / 1024 / 1024, 0),’99,999,990.900′) “MaxSize (M)”,
TO_CHAR(NVL((a.bytes – NVL(f.bytes, 0)) / a.maxbytes * 100, 0), ‘990.00’) “Used % of Max”
FROM sys.dba_tablespaces d,
(SELECT tablespace_name,
SUM(bytes) bytes,
SUM(decode(autoextensible,’NO’,bytes,’YES’,maxbytes))
maxbytes FROM dba_data_files GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM(bytes) bytes FROM dba_free_space
GROUP BY tablespace_name) f
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = f.tablespace_name(+)
ORDER BY 10 DESC;

  1. Data Files Locations

you should check the location for datafiles to make sure there’s no misunderstanding and check autoextened

SQL> select * from v$dbfile;

to Check if the auto extend is on

SQL> select file_id, tablespace_name, bytes, maxbytes, maxblocks, increment_by, file_name
from dba_data_files where autoextensible = ‘YES’;

  1. Redo Log

Redo log is very important components in database since its minimize loss of data in the database Redo log files are used in a situation such as instance failure to recover commited data that has not yet been written to the data file.

SQL> select * from v$logfile;

  1. Parameter files

Check Spfile, Or pfile in database to Ensure startup and database parameters

SQL> show parameter pfile ;

NAME TYPE VALUE


spfile string /u01/app/oracle/product/10.2.0/db_1/dbs/spfileorcl.ora

  1. Backup

You have to Check if the backup Script Run successfully without any problems

RMAN > list backup summary

This is how health check database there’s others things you can do such as indexes,and memory

==========================================================================================================

How To Monitor Oracle Database

I talked before about Oracle Users, and The effect Of them In Our Database , Today i will Share Some Idea to monitor Oracle Database in Simple Way without any third Party

Any Company should be Concern about Powerful user in their company, i am talking here about database users. Specially In Production System.

as we all know Oracle database comes with two powerful account SYSTEM, SYS Very often individual accounts with DBA roles also are created for DBAs to perform their daily duties without using the SYS or SYSTEM accounts.These admin accounts in the Oracle database usually have the ability to manage user security, maintain database storage, and perform backup and recovery tasks.but when the both left without monitored they may perform fraudulent activities without leaving any trace, Stealing Backup, Data or even take look at some personal Data such as Credit card number , Social Number and Mobile number for example.Granting DBA access to business owners is a blatant violation of segregation of duties. However, the list of powerful users should not be limited to these admin accounts. Users with special database privileges such as UPDATE ALL TABLE and SELECT ALL TABLE should also be considered powerful users and should be targets of database monitoring.

usually you need to monitor different activities on your system/applications or database.the best practices of monitoring database for example : logon/logoff activities of SYS/SYSTEM, database schema structure changes and DML (update, insert, delete) on different tables such as tables contain sensentive data ( bank account …. ) .

====Oracle Provide with basic and Easy tools to do all the above such:

1- Basic Oracle Audit Trail

By enable AUDIT_TRAIL Parameter ( DB,OS ,NONE) Check Oracle Documentation here, For Example if you set this parameter to OS you should Check AUDIT_FILE_DEST, On another hand if you set this parameter to DB the audit trail will be recorded in a system table named SYS.AUD$ and each SYS user will be audited if the AUDIT_SYS_OPERATIONS parameter is set to TRUE.

Oracle audit utilities allow one to audit by session, user, action and object. also privilege and object can be audited For example, to audit the TEST userís login activity, one can issue the following statement: AUDIT SESSION BY TEST.In addition to the standard audit features, Oracleís finegrained audit package DBMS_FGA allows the monitoring of data access based on content. One can specify value-based audit policies using SQL statements while using the finegrained audit package.

2- Create Your Own Trigger

Triggers are database procedures fired off by a specified event. Database triggers can be associated with a table, schema or database. They also can be used as a complementary mechanism to the standard features of Oracle audit facilities

3- Emails

You can enable this feature by command line or by OEM, This utility allow you to receive any error in alert log.

4-LOGMINER/Archivelog

LogMiner Can detect wrongdoings by the database users. Most production databases have turned on the archive log to write archive logs to multiple destinations. The archive logs are used to restore Oracle databases to a point in time. All DDL and DML activities can be reversed using the archive logs. The fraudulent activities of SYS or SYSTEM are recorded there.You can Check V$SQL_TEX.