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;
- 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’;
- 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;
- 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
- 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.
