Steps to do in oracle database healthcheck

Oracle health check is used to take all the data and shows the performance of our database. It provides users facing service, including hosted websites, API endpoints, or externally facing load balancers. Oracle health check also services with high-frequency external monitoring to determine the availability and performance of any public issue.

It includes the following :

1. Check the LISTENER.LOG size

2. Check the ALERT.LOG file size

3. Check the backup log

4. Check the Disk Space of the System

5. Check the available space for the ARCHIVE location

6. Check the sync between production and dr

7. Generate AWR report / Statspack report / ADDM report

8. Run the DB_MONITORING.sql

1.The listener.log size needs to be checked. If the listener.log size is very huge, renaming it to be done.

Open a command prompt. Go to LSNRCTL prompt.

Disable logging by:

            LSNRCTL> Set log_status off;

Rename the listener.log file manually

Enable logging by:

            LSNRCTL> Set log_status on;

The new listener.log file will be created automatically.

           LSNRCTL> show log_directory => Gives the listener.log location

2. Check the ALERT.LOG file size: If the alert log has increased to a large size, it needs to be renamed. A new alert.log will be created. Check the ALERT LOG size of the data guard too. (If DR is present).

3. Check the backup log (if present) to ensure that the backup has been successfully completed.

4. Check the Disk Space of the System. If it is Windows OS, check the drives for space availability, and if it is non-windows then check the free space of every mount point where the datafiles, control files, redo log files reside.

5. Check the available space for ARCHIVE location, (if ARCHIVING is ON for the database) If space is less, very old archives need to be deleted to create space as per the client’s retention policy. (Confirm with the client before deleting)                                                                                                                                                                                                                                                                                                                         SQL> Show parameter log_archive_dest => (to get the archive log location).

6. If the customer has a DATAGUARD setup, please check whether the STANDBY is in SYNC with the PRODUCTION or not. If not then take the necessary steps to resolve the issue. To check sequence# in the STANDBY side, use the below command: 

              SQL> select sequence#, applied, archived from v$archived_log where applies=’YES’;

7. The AWR report / Statspack report / ADDM report of the database needs to be collected as required. This should be taken at the peak hours of the application. The client is to be consulted for this information.

8. Check the ALERT.LOG file for any error. Its location can be found by:

              SQL> show parameter background_dump_dest

In the alert log kindly check for any error. If the error can be resolved there at the client site, do the needful. Otherwise, collect the required trace files. If SR needs to be raised regarding the, kindly go through the error and do the needful.

9. Run the DB_MONITORING.sql script to gather the required data.

             SQL> @DB_MONITORING.sql

TO GENERATE STATSPACK REPORT, FOLLOW THE BELOW STEPS:

1. Connect to the DB as sys user as sys/sys as sysdba and check out for the value of the parameter timed_statistics (it should be set to true).

          SQL> show parameter timed_statistics; if it is false set it to true

          SQL> alter system set timed_statistics =true scope=both;

2. Check whether the user perfstat already exists. If the user is frequently used, don’t drop it.

         SQL> select username from dba_users;

3. Otherwise, run the script spdrop ($ORACLE_HOME\rdbms\admin\spdrop.sql) to drop the existing perfstat user.

         SQL> @?\rdbms\admin\spdrop.sql

4. Run the script spcreate ($ORACLE_HOME\rdbms\admin\spcreate.sql) to create user perfstat.

5. It will ask for the user’s password (give perfstat as pwd), the default tablespace(tools or users), and the temporary tablespace (temp).

          SQL> @?\rdbms\admin\spcreate.sql

6. Connect to the DB as the perfstat user

        SQL> conn perfstat\perfstat

7. Run the PL\SQL procedure:

         SQL> execute statspack.snap

8. Wait for 20 minutes before taking the second snapshot ie step 6 again.

9. Finally run the script spreport ($ORACLE_HOME\rdbms\admin\spreport.sql) to generate the report, where it will be asking for the snapshot ids (choose the appropriate ids and create the report).

          SQL> @?\rdbms\admin\spreport.sql

TO GENERATE AWR REPORT, FOLLOW THE BELOW STEPS:

1. Log in to the database as sys/sys as sysdba
2. Run the script: $ORACLE_HOME\rdbms\admin\awrrpt.sql

          SQL> @?\rdbms\admin\awrrpt.sql

3. Give the type of the report as HTML
4. From the list of the snap IDs shown, enter the value of the required begin ID and end ID. This should be depending on the peak hours of the application.
5. Give the location to generate the AWR report.

TO GENERATE ADDM REPORT, FOLLOW THE BELOW STEPS:

1. Log in to the database as sys/sys as sysdba
2. Run the script: $ORACLE_HOME\rdbms\admin\addmrpt.sql

         SQL> @?\rdbms\admin\addmrpt.sql

3. Give the type of the report as HTML
4. From the list of the snap IDs shown, enter the value of the required begin ID and end ID. This should be depending on the peak hours of the application.
5. Give the location to generate an ADDM report.

ASK YOUR CUSTOMER/USER ABOUT ANY KNOWN ISSUE IF REQUIRED PLEASE COLLECT THE RESPECTIVE TRACE FILE.

TO CHECK THE SYNC OF PRODUCTION AND STANDBY DATABASE IN RAC ENVIRONMENT:

On production:

Go node vice:

        SQL> archive log list;

        SQL> select status,error from v$archive_dest;

On standby:

        SQL>  Select max(sequence#) from v$archived_log where applied=’YES’;