Important Health checks

Daily Activity

1. Find out the version of oracle instance

SELECT version FROM V$INSTANCE

SET SERVEROUTPUT ON;

BEGIN 
DBMS_OUTPUT.PUT_LINE(DBMS_DB_VERSION.VERSION || '.' || DBMS_DB_VERSION.RELEASE); 
END;

2. Find out instance is running

in Unix:
ps -ef|grep PMON

in Windows
--   List all the oracle services.
     C:\> sc query state= all | find "SERVICE_NAME" | find "Oracle"

-- Check Oracle service is running.
 

C:\>sc query OracleServiceORCL

SERVICE_NAME: OracleServiceORCL
        TYPE               : 10  WIN32_OWN_PROCESS
        STATE              : 4  RUNNING   (STOPPABLE, PAUSABLE, ACCEPTS_SHUTDOWN)
        WIN32_EXIT_CODE    : 0  (0x0)
        SERVICE_EXIT_CODE  : 0  (0x0)
        CHECKPOINT         : 0x0
        WAIT_HINT          : 0x0

C:\>sc qc OracleServiceORCL

[SC] QueryServiceConfig SUCCESS

SERVICE_NAME: OracleServiceORCL

        TYPE               : 10  WIN32_OWN_PROCESS

        START_TYPE         : 3   DEMAND_START

        ERROR_CONTROL      : 1   NORMAL

        BINARY_PATH_NAME   : c:\parvez\app\product\11.2.0\dbhome_1\bin\ORACLE.EXE ORCL

        LOAD_ORDER_GROUP   :

        TAG                : 0

        DISPLAY_NAME       : OracleServiceORCL

        DEPENDENCIES       :

        SERVICE_START_NAME : LocalSystem



3. Database Listener is running or not.

>LSNRCTL STATUS

4. Check any session blocking the other session

SELECT
   s.blocking_session,
   s.sid,
   s.serial#,
   s.seconds_in_wait
FROM
   v$session s
WHERE
   blocking_session IS NOT NULL;

5. Check the alert log for an error

SQL>show parameter BACKGROUND_DUMP_DEST

Go to that location, and check alert_orcl.log file. Normally it stays under app\diag\rdbms\orcl\orcl\trace folder.


6. Check is there any dbms jobs running & check the status of the same

Check the Top session using more Physical I/O
Check the number of log switch per hour
How_much_redo_generated_per_hour.sql
Run the statpack report
Detect lock objects
Check the SQL query consuming lot of resources.
Check the usage of SGA
Display database sessions using rollback segments
State of all the DB Block Buffer
Weekly Activity

Check the objects fragmented
Check the Chaining & Migrated Rows
Check the size of tables & check weather it need to partition or not
Check for Block corruption
Check the tables without PK
Check the tables having no Indexes
Check the tables having more Indexes
Check the tables having FK but there is no Index
Check the objects having the more extents
Check the frequently pin objects & place them in separate tablespace & in cache
Check the objects reload in memory many time
Check the free space at O/s Level
Check the CPU, Memory usage at O/s level define the threshold for the same.
Check the used & free Block at object level as well as on tablespaces.
Check the objects reaching to it’s Max extents
Check free Space in the tablespace
Check invalid objects of the database
Check open cursor not reaching to the max limit
Check locks not reaching to the max lock
Check free quota limited available of each user
Check I/O of each data file
Monthly Activity

Check the database size & compare it previous size to find the exact growth of the database
Find Tablespace Status, segment management, initial & Max Extents and Extent Management
Check location of data file also check auto extendable or not
Check default tablespace & temporary tablespace of each user
Check the Indexes which is not used yet
Check the Extents of each object and compare if any object extent are overridden which is define at tablespace level
Tablespace need coalescing
Check the overall database statistics
Trend Analysis of objects with tablespace, last analyzed, no. of Rows, Growth in days & growth in KB

Nightly Activity

Analyzed the objects routinely.

Check the Index need to Rebuild

Check the tablespace for respective Tables & Indexes

One Time Activity

Database user creation with required privileges
Make the portal of Oracle Predefined error with possible solution.
Check database startup time(if not 24X7)
Check location of control file
Check location of log file
Prepare the Backup strategy and test all the recovery scenario