I've just downloaded latest Beta and tried running an ADDM report (Oracle 19.3) but it fails with the following message:
ORA-13703: The snapshot pair [6855, 6900] for database_id 3797924084 and instance_id [1] are not found in the current repository.
ORA-06512: at "SYS.DBMS_ADVISOR", line 226
ORA-06512: at "SYS.PRVT_ADVISOR", line 3546
ORA-06512: at "SYS.PRVT_ADVISOR", line 932
ORA-06512: at "SYS.PRVT_HDM", line 10
ORA-06512: at "SYS.WRI$_ADV_HDM_T", line 39
ORA-06512: at "SYS.PRVT_ADVISOR", line 915
ORA-06512: at "SYS.PRVT_ADVISOR", line 3451
ORA-06512: at "SYS.DBMS_ADVISOR", line 276
ORA-06512: at "SYS.DBMS_ADVISOR", line 221
ORA-06512: at line 10
In Oracle 19c new features, I could read the following:
##### Automatic Database Diagnostic Monitor (ADDM) Support for Pluggable Databases (PDBs) You can now use ADDM analysis for PDBs in a multitenant environment. ADDM analysis at a PDB level enables you to tune a PDB effectively for better performance.
Is this just some feature yet to be added to TOAD? In that case... should be possible to disable ADDM when connected to a PDB to avoid the above error meanwhile?
Looking at the oracle docs, I see that it references dbms_addm with the comment about now having support for pluggables. Toad uses dbms_advisor. But interesting to note, in 18c and prior with pluggable databases, the error message was something along the lines of "you can't do this in a pluggable database"
Anyway, looking at dbms_addm, I see this in the documentation....
DBMS_ADDM.ANALYZE_INST (
task_name IN OUT VARCHAR2,
begin_snapshot IN NUMBER,
end_snapshot IN NUMBER,
cdb_type_override IN VARCHAR2,
read_only_type_override IN VARCHAR2,
instance_number IN NUMBER := NULL,
db_id IN NUMBER := NULL);
which looks promising because I can now tell it what kind of database I have (pluggable, container, etc), but when I try that, I get PLS-00306 "Wrong number or types of arguments in call to ANALYZE_INST. Doing an F4 on it, I see this:
...the parameters differ from what the documentation says!
Commenting out the parameters that don't exist and trying DBMS_ADDM.ANALYZE_INST again, I get the same error as in your original post.
So it seems that Oracle jumped the gun a bit on this one. I hesitate to just disable it in Toad incase Oracle fixes it in a patch. Although the error is a bit confusing because the snapshots do exist, the error will not cause any problems down the line in Toad.
Maybe we both should! I opened a case with them a year ago about dbms_tsdp_manage when 18c was newish...it was open for over a year and I never really did get straight answer about why what I was trying to do wasn't working.
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:
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.
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)
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 (+));
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.
