Toad World® Forums

Beta 13.2.0.156 - ADDM problem: ORA-13703 snapshots not found

Toad for Oracle - Beta
#6

OK, got it working after advised from Oracle Support, let's see:

Please see the note from https://docs.oracle.com/en/database/oracle/oracle-database/19/tgdba/automatic-performance-diagnostics.html#GUID-B4B0899D-90A3-4E19-8654-1D5C34A48FD5

Note:

ADDM is enabled by default in a CDB root.
ADDM does not work in a PDB by default, because automatic AWR snapshots are disabled by default in a PDB. To use ADDM in a PDB, you must enable automatic AWR snapshots in the PDB.
A user whose current container is the CDB root can view ADDM results for the entire CDB. The ADDM results can include information about multiple PDBs. ADDM results related to a PDB are not included if the PDB is unplugged. The ADDM results stored on the CDB root cannot be viewed when the current container is a PDB.
ADDM results on a PDB provide only PDB-specific findings and recommendations. A user whose current container is a PDB can view ADDM results for the current PDB only. The ADDM results exclude findings that apply to the CDB as a whole, for example, I/O problems relating to the buffer cache size.
Enabling AWR snapshots on a PDB does not change the ADDM report on the CDB root.
AWR data on a PDB cannot be accessed from the CDB root.

Also please see the section:

7.1.3.1 Enabling ADDM in a Pluggable Database

ADDM does not work in a pluggable database (PDB) by default, because automatic AWR snapshots are disabled by default in a PDB. To use ADDM in a PDB, you must enable automatic AWR snapshots in the PDB by setting the AWR_PDB_AUTOFLUSH_ENABLED initialization parameter to TRUE and AWR snapshot interval greater than 0.

To enable ADDM in a PDB:

Set the AWR_PDB_AUTOFLUSH_ENABLED initialization parameter to TRUE in the PDB using the following command:

SQL> ALTER SYSTEM SET AWR_PDB_AUTOFLUSH_ENABLED=TRUE;

Set the AWR snapshot interval greater than 0 in the PDB using the command as shown in the following example:

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

Then I tried this and worked fine:
var tname VARCHAR2(60);
BEGIN
:tname := 'addm_test';
DBMS_ADDM.ANALYZE_INST(:tname, 1, 2);
END;
/

Now they're looking at the documentation mismatch issue, I'll keep you updated.

Regards,

#7

Excellent! Thanks for the follow up.

#8

At this moment, TOAD is not PDB-AWR capable... for a PDB, it should list the AWR snapshots attending to the PDB DBID (right now it's showing the CDB's snaps) and fails.

#9

After running the SQL that you gave me above, I am getting 2 DBIDs (a DBID dropdown appears in the ADDM/AWR window's toolbar)

If I choose the 2nd one, I can choose a pair of snapshots belonging to the PDB and the ADDM report works.

image
image.jpg851×791 215 KB

#10

I use this query to find the DBIDs in the database. Unfortunately I don't see a way to tell which one belongs to the pluggable.

select distinct dbid, db_name
from DBA_HIST_DATABASE_INSTANCE;

edit: CDB_HIST_DATABASE_INSTANCE holds the DBID for the pluggable.

#11

The "DBID" dropdown in today's beta will make it more clear which DBID belongs to the container and which belongs to the pluggable (they both have the same DB Name in DBA/CDB_HIST_DATABASE_INSTANCE, which seems like a bug but that's on Oracle)

#12

Maybe you could try using/joining with DBA_PDBS/CDB_PDBS views? They have both the DBID and the (correct) PDB_NAME.

#13

Thanks. OK, I can get the pluggables from there and the container from DBA_HIST_DATABASE_INSTANCE.

#14

So, after a few months they finally solved the SR I opened about wrong documentation... they removed those missing parameters in ANALYZE_INST at https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_ADDM.html#GUID-A7B371FD-8A79-45A7-9F46-B285EE24A36E :expressionless:

1 Like
#15

I did some work in the 13.3 beta to make the ADDM/AWR window work better with pluggables.

You still can't run the ADDM report on a pluggable on 18c and older but you can run it now on 19c pluggables in Toad. Give it a try.

-John

#16

Yep, working for me on 19c :grinning:

#17

hello all,

question. I am running toad 13.2.0.258 oracle 19c with PDB's

One of my instances has the DB ID drop down in the ADDM window but others do not. Is this a config issue i have in my toad?

Thanks
Dave

#18

actually, it may be in your database.

Does this query not return anything? That's where we get the DBID

select dbid, cdb_root_dbid,
       case when cdb_root_dbid = dbid then db_name
            else pdb_name end as db_name
from (select distinct i.dbid, i.db_name, i.cdb_root_dbid, p.pdb_name
      from   DBA_HIST_DATABASE_INSTANCE i, DBA_PDBS p
      where  i.dbid = p.dbid (+));
#19

well thats interesting when I run that query in the PDB that has the drop down it returns the Root and the PDB

|2782172003|2782172003|AZTEST2C|

|1070155216|2782172003|TESTPDB|

--

2974455360 2974455360 AZTEST1

so still pretty new to CDB's and PDB's, what does this mean?

#20

That's what I'd expect to see. What does it return in the database where DBID is not shown in the ADDM window?

CDB is Container DataBase (also called the Root)
PDB is Pluggable DataBase.

The Container is like the "parent" database. That's where the pluggables live.
Pluggable databases are more like the "normal" databases of earlier Oracle versions, except they can be quickly cloned, deleted and snapshotted inside the container database. A container can have one or many pluggables.

#21

sorry that was my mistake in the information

this is one CDB-- this one displays correctly in the ADDM window
|2782172003|2782172003|AZTEST2C|--cdb
|1070155216|2782172003|TESTPDB|--pdb

this is another CDB that doesnt display correctly
2974455360 2974455360 AZTEST1
there should be a PDB listed here but it is not. I can go to oracle support but figured you would probably be able to provide a faster response

#22

It's probably just not set up on the pluggable. There are some steps to do that here.
Scroll down to "Enabling ADDM in a Oracle 19c Pluggable Database" (about 1/4 of the way down)

#23

I did do those steps before but it looks like i may have messed something up. I set it up on on 13 PDB's and only some are having the issue. what is the +40150
SRC_DBNAME SNAP_INTERVAL
——————– ——————–
AZTEST1 +40150 00:01:00.000000

If this is getting beyond the scope of this conversion i dont mind going to oracle.

I also noticed that the PDB with the ADDM issues do not show up in the DBA_HIST_DATABASE_INSTANCE view.

#24

ok 100% my fault i forgot my / at the end of the EXEC dbms_workload_repository.modify_snapshot_settings(interval=>60)
in the script where the face smack emoji

thanks for your time

:man_facepalming:

1 Like
#25

Glad you got it going. I added the face smack to your post. :smiley:

+40150 00:01:00.000000 is the snapshot interval. This is how often a snapshot of database statistics will be taken. So, that's every 40,150 days and 1 minute, so basically, never. It should have changed when you did modify_snapshot_settings (with the slash, of course).

BTW, you can modify the settings in Toad also, on the snapshot management tab. Just edit the snapshot interval or retention and then click the checkbox above to apply. I'm not sure if it would have worked before the ADDM window in Toad knew your DBID, that's why I didn't mention it earlier.

1 Like