What is monitoring ?
- Supporting production !!!
- Keeping an eye on development, i.e. disabled PKs | FKs.
- Database performance
- In Support of an SLA (service level agreement)
1.) Daily Procedures
A.) Verify all instances are up : Make sure the database is available. Log into each instance and run daily reports or test scripts. Use the below queries to check where database is up or not.
SQL> select name,open_mode from v$database ;
D.) Verify success of database backup : Check the physical location of the database backup and ensure that the database backup is successful .
II. Nightly Procedures
A.) Collect volumetric data : This example collects table row counts. This can easily be extended to other objects such as indexes, and other data such as average row sizes . Analyze schemas and collect data as in case of oracle 11g the optimizer collect the analyse the whole database and collect the fresh statistics .In case of Oracle 9i , collect fresh statistics by running the below command :
SQL> exec dbms_stats.gather_schemas_stats ;
SQL> exec dbms_stats.gather_schemas_stats('SCOTT') ;
III. Weekly Procedures
A.) Check the invalids objects : Check the invalid objects and remove them from the database .The the utlrp.sql script to remove the invalid objects . This scripts is present in "$ORACLE_HOME\rdbms\admin" folder .
B.) Check the Growth of the tablespace in the database : Check the growth of the each tablespace and in the database. Run the scripts to check the growth of tablespace . Tablespace Growth Scripts
IV. Monthly Procedures
C.) Look for I/O Contention : Review database file activity. Compare to past output to identify trends that could lead to possible contention.
D.) Review Fragmentation : Investigate fragmentation (e.g. row chaining, etc.).
Enjoy J J J