Oracle 19c New Feature: Automatic Database Diagnostic Monitor (ADDM) in Pluggable Database Level

Oracle 19c has introduced new feature (capability) which is generating ADDM report from a PDB-level, this was not possible in previous releases, as ADDM was generated from a CDB (Container Level).

The following are the steps to enable ADDM in a PDB:

SQL> alter session set container=PDB1;

Session altered.

SQL> ALTER SYSTEM SET AWR_PDB_AUTOFLUSH_ENABLED=TRUE;

System altered.

//configure snapshot interval for AWR

SQL> EXEC dbms_workload_repository.modify_snapshot_settings(interval=>60);

PL/SQL procedure successfully completed.

// take manual snapshot

SQL> BEGIN

   DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();

END;

/

// to check snapshsot details for PDB1 database

SQL> select * from DBA_HIST_SNAPSHOT where CON_ID=3;

As HR user I will create a new table and insert dummy data:

sqlplus hr/hr_123@PDB1

CREATE  TABLE DUMMY_SEPT (sales  number(10));

BEGIN

    FOR v_LoopCounter IN 1..3000 LOOP

 insert into DUMMY_SEPT values (‘4000’);   

COMMIT;

    END LOOP;

END;

/

commit;

select *  from DUMMY_SEPT;

// as SYS or SYSTEM account execute the following to take manual snapshot:

SQL> BEGIN

   DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();

END;

/

Now, I will generate ADDM Report for PDB1 database:

SQL> alter session set container=PDB1;

Session altered.

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

Current Instance

~~~~~~~~~~~~~~~~

   DB Id    DB Name      Inst Num Instance

———– ———— ——– ————

 2381533563 ORCLCDB             1 ORCLCDB

Instances in this Workload Repository schema

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

  DB Id      Inst Num   DB Name      Instance     Host

———— ———- ———    ———-   ——

  2381533563     1      ORCLCDB      ORCLCDB      oracle-19c-v

Using 2381533563 for database Id

Using          1 for instance number

Specify the number of days of snapshots to choose from

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Entering the number of days (n) will result in the most recent

(n) days of snapshots being listed.  Pressing <return> without

specifying a number lists all completed snapshots.

Listing the last 3 days of Completed Snapshots

Instance     DB Name      Snap Id       Snap Started    Snap Level

———— ———— ———- —————— ———-

ORCLCDB      ORCLCDB              1  24 Sep 2020 12:38    1

                                  2  24 Sep 2020 14:00    1

                                  3  24 Sep 2020 14:25    1

                                  4  24 Sep 2020 14:30    1

                                  5  24 Sep 2020 14:50    1

Specify the Begin and End Snapshot Ids

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Enter value for begin_snap: 4

Begin Snapshot Id specified: 4

Enter value for end_snap: 5

End   Snapshot Id specified: 5

Specify the Report Name

~~~~~~~~~~~~~~~~~~~~~~~

The default report file name is addmrpt_1_4_5.txt.  To use this name,

press <return> to continue, otherwise enter an alternative.

Enter value for report_name: ADDM_PDB1_SEPT24.txt

Using the report name ADDM_PDB1_SEPT24.txt

Running the ADDM analysis on the specified pair of snapshots …

Generating the ADDM report for this analysis …

          ADDM Report for Task ‘TASK_15’

          ——————————

Analysis Period

—————

AWR snapshot range from 4 to 5.

Time period starts at 24-SEP-20 02.30.46 PM

Time period ends at 24-SEP-20 02.50.05 PM

Analysis Target

—————

Database ‘ORCLCDB’ with DB ID 2381533563.

Database version 19.0.0.0.0.

ADDM performed an analysis of instance ORCLCDB, numbered 1 and hosted at

oracle-19c-vagrant.

ADDM detected that the system is a PDB.

Activity During the Analysis Period

———————————–

Total database time was 6 seconds.

The average number of active sessions was .01.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

There are no findings to report.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

          Additional Information

          ———————-

Miscellaneous Information

————————-

There was no significant database activity to run the ADDM.

End of Report

Report written to ADDM_PDB1_SEPT24.txt

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s