Database Health Check

Oracle

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 e 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.