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
Geodata Master 