YourInfoBucket
  • Home
  • Business
  • Technology
  • Health
  • Fashion
  • Travel
  • More
    • Sports
    • Education
    • Entertainment
    • Food & Nutrition
No Result
View All Result
Write For Us
  • Home
  • Business
  • Technology
  • Health
  • Fashion
  • Travel
  • More
    • Sports
    • Education
    • Entertainment
    • Food & Nutrition
No Result
View All Result
Write For Us
No Result
View All Result

Steps to do in oracle database healthcheck

by Simran
April 1, 2021
in Education
Reading Time: 5 mins read
0
Oracle healthcheck
24
SHARES
125
VIEWS

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.

Table of Contents

  • It includes the following :
      • Check the LISTENER.LOG size
      • Check the ALERT.LOG file size
      • Check the backup log
      • Check the Disk Space of the System
      • Check the available space for the ARCHIVE location
      • Check the sync between production and dr
      • Generate AWR report / Statspack report / ADDM report
      • Run the DB_MONITORING.sql
      • Disable logging by:
        • Rename the listener.log file manually
      • Enable logging by:
        • The new listener.log file will be created automatically.
      • TO GENERATE STATSPACK REPORT, FOLLOW THE BELOW STEPS:
      • TO GENERATE AWR REPORT, FOLLOW THE BELOW STEPS:
      • TO GENERATE ADDM REPORT, FOLLOW THE BELOW STEPS:
      • 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:
        • On standby:

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

  1. Give the type of the report as HTML
  2. 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.
  3. 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

  1. Give the type of the report as HTML
  2. 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.
  3. 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’;

Tags: addmawrdatabasehealtchecklistenerlogoraclespreport

Related Posts

Programming Language
Education

Which Programming Languages Should You Learn in 2022?

by Simran
September 30, 2022
DevOps course
Education

What Career Opportunities will be there After Completing the DevOps Course?

by Simran
December 9, 2022
How Image to Text Converter Technology Can Help Students in Assignment Writing
Education

How Image to Text Converter Technology Can Help Students in Assignment Writing

by Simran
August 16, 2022
school curriculum
Education

Dennis Bonnen Shares 5 Reasons Why Schools Should Have Flexibility In Creating Their Curriculums

by Simran
August 25, 2022
Repair a Corrupt MS SQL Database
Education

Want to Repair a Corrupt MS SQL Database? Here is the Easy Way!

by Simran
August 2, 2022

About Us

Yourinfobucket is a platform where you can keep yourself updated with the latest happenings in the field of Business, Technology, Food, Health, Entertainment and Fashion. The purpose of YourInfoBucket is to helping the reader to expand their knowledge globally and sharing their experiences through it.

Categories

  • Business
  • Education
  • Entertainment
  • Fashion
  • Food $ Nutrition
  • Health
  • Menu Price
  • Sports
  • Technology
  • Travel

Important Page

  • About Us
  • Write For Us
  • Contact Us
  • Privacy Policy
  • Terms & Conditions
  • Disclaimer
  • Sitemap
  • HTML Sitemap

Recent Posts

  • Essential Features That Contact Center Solutions Should Have
  • 10 Practical Shopping Tips for Parents of Growing Kids
  • How to Start Investing in SIP? A Complete Guide

© 2022 YourInfoBucket.com

No Result
View All Result
  • Home
  • Business
  • Technology
  • Health
  • Fashion
  • Travel
  • Sports
  • Education
  • Entertainment
  • Food & Nutrition
Write For Us

© 2022 YourInfoBucket.com