ADDM and AWR Show Instances in Dropdown that no longer exist

Toad for Oracle
1

We have a database that used to have 4 instances, years ago. When we moved to new servers, we now only have 2 instances, but the 2 old instances are still showing up in the drop down list for ADDM and AWR reports.
Where is it getting this instance information? I don't see those old instances referenced anywhere in the data dictionary or in the parameter file.
We should only be seeing the active instances. If you try to select one of the old instances, you get the message, no snapshots found.

2

It depends on Oracle and Toad versions.

You can turn on Spool SQL and then open the window to find out. Main Menu -> Database -> Spool SQL -> Spool to Screen. Then open the ADDM or AWR windows, and look at the spooled SQL. The first one is the one you're looking for. Mine looks like this:

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 (+));

3

Yes, that is what is so confusing, there is no reference in any of the data dictionary views or queries that toad is using that reference the other instances.
I am using the latest version of Toad but the same behavior was happening in the older versions too.

4

It's in there somewhere. Grab the SQL that Toad is running, and run it yourself in the editor.

5

Yes I have done that. I know it has to be somewhere, but I can't find it.
I'll keep digging.

6

Which version of Toad are you running?

7

15.1.113.1379
But I have tried in 13 and 14 versions too and still see the non-existent instances.

8

Ah, I found it, thanks.

select distinct dbid, db_name
from DBA_HIST_DATABASE_INSTANCE;

Funny, first time I spooled the sql and opened the page I got a different sql that only showed the one dbid record:

select distinct dbid, db_name
from DBA_HIST_DATABASE_INSTANCE;

Thanks

1 Like