TL;DR: When your query joins V$PDBS to get the name of the PDB, you might miss the contents of CDB$ROOT. I fixed that in various ways for my scripts.
(note: this was a classic case of at least two errors on my part: not reading the RTFM on "containers" careful enough, and a case of an INNER JOIN being too strict)
Image generated in style of X-files: Never Trust your own Scripts...
Background: Do I miss some data ??
When you are "administrating" (monitoring) your Container DataBases from CDB$ROOT, you want to see Everything. All PDBs, all datafiles, all services, all session and all processes. And you want to know in which PDB each item resides. Not just the con_id, but preferably the actual name of the PDB.
But when you join to V$PDBS to get a list of the pluggable databases in your container, there is no entry in that list for CDB$ROOT.
In some of my scripts, I ended up with discrepancies. As an example, I'll take my daily "storage-check". But I had similar "issues" with v$services and v$session.
The sum of all filesizes from V$DATAFILE was (example) 999 GB. But when I tried to group the data per PDB, the total of that list was clearly less than 999 GB. Huh?
The difference was that the datafiles from the CDB$ROOT were not taken into account, because my (inner) join with V$PDBS automatically excluded the files where CON_ID=1, e.g. the files from the CDB$ROOT.
It got even stranger: in V$database, when you do select con_id and name from v$database it yields Con_id=0, and name=FREE (e.g. the db_name).
So it seems that CON_ID = 0 and CON_ID = 1 are the exception-cases, and are both referring to CDB$ROOT, or to DB_NAME, depending on where you look ?
Not a huge problem, and there are several simple solutions. But still enough of an anomaly for me to take a closer look (and fix my queries...).
Note: I'll do the demo using the "Free" container from Gerald Venzl (link). If you ever quickly need a demo- or test-database check this out. Some of our devs just love them for their ease-of-use. No license-accept, no login, just pullable-images. I got into the habit of using these containers for just about any "proof of concept" (or any blog-article). You'll have an Instant Database available on any laptop where docker or podman is installed. I might do a blog on "how to" sometime, but in general: Just read the page and get running... Most DEVOPS- and DBA folks figure it out in no-time - And then they suddenly "run a local database"...
The Example: I missed GBs diskpace from the sum of my datafiles.
For demo purposes of this blog, I started one of those containers with FREE and FREEPDB1, I then added several PDBs and then I checked for used disk-space (simplified version of my check-script in link below):
Notice how the list of PDBs adds up to 5 x 790 = 3950 MB, but the total reported is 5673 MB. There is some 1700 MB is missing in the first list, even though both queries report from v$datafile.
From looking at the SQL, or by listing just the v$datafiles, it is +/- evident that the first query missed the files where CON_ID = 1, e.g. it missed the files from the CDB$ROOT:
The two files at the top hold the 1700MB that seems missing. And the cause is classic: the simple, old fashioned, join with V$PDB in the previous query had left out the Con_ID=1.
Aha. A quick fix with LEFT JOIN should do the job. Or.... Even better, after poking around I also found V$CONTAINERS, and that seemed like the Best Fix (for this case):
So Far so Good. This scripts is now working as intended again.
But What About that CON_ID for root ??
On further inspection, I notice that also my session-counts (checking +/- how busy is the system...) were off by a good margin when compared to, say:
select count(*) from v$session;
or even
ps -ef | grep $ORACLE_SID
Same problem of course: When I had done my quick+dirty equal-joins to V$PDBS on CON_ID to pick up PDB-NAME, I suddenly had eliminated all of the sessions for non-PDB connections.
But it got stranger: Here is my check on GV$SESSION, after putting in the "fix" with GV$CONTAINERS to pick up the pdb_names and CDB$ROOT..
The total shows a count of 86 sessions, but the various containers only hold ... 3+3+1 = Seven ?? How can I still miss 79 sessions ?
This is where I had to go RTFM, and the actual information was buried in an older version (v12, link) of the doc:
The values for con_id are explained as follows:
- Con_ID = 0: Pertains to the Entire CDB (example: V$DATABASE contains value of 0, and this is probably why many "background" session have Con_ID=0)
- Con_ID = 1: Pertains to the CDB$ROOT (for example in V$CONTAINERS and V$DATAFILE, you will find the value of Con_id=1, as I found with my storage-check)
- Con_ID = 2: Pertains to the PDB$SEED.
Other, higher values of Con_ID correspond to proper PDBs that have been created, cloned, or plugged into the CDB.
And because there is no Con_ID = 0 in the V$CONTAINERS, I missed some 79 sessions in my uninformed count...
Note that when you check the same RTFM-page for 26ai (click on "view latest" in the previous link) there is no clear mention of this anymore...? This information has either moved to other parts of the documentation or was considered overly confusing (I'd say: Not Knowing this is even more confusing, but maybe I dig too deep...).
In this case, a little more fixing was needed, and the fix did end up as a LEFT-JOIN:
And QED: we have the sessions, counted per container, and we did include the sessions where Con_ID=0. The sum of the part is equal to the whole again. All is well.
For a more elaborate fix, I even experimented with a view that I named All_Con_IDs: a Union of V$PDBS and V$DATABASE. The result was something along these lines: Here is my check-count for "processes" using an inline-view version:
Using this CTE (or inline view), I got the satisfaction of seeing the proper name of the (container-)database displayed with Con_ID=0, instead of some self-designated hardcoded "-root-" value from an improvised-NVL.
Needless to say: I didnt seriously implement that "fix" in a self-made view (yet), but it was an interesting idea for a moment: unite all the Con_IDs in a single view/place to join that to various sources where and when needed...
Maybe someone out there knows of a similar view that already exists ?
Summary: Check your scripts when using Con_ID (and Trus7No!)
From spotting an anomaly in my scripts, I ended up digging into CON_ID values. I discovered that values for CON_Id of 0 (zero) and 1 (one) mean different things in the various gv$ views, and I ended up with better scripts, and a slightly better understanding of my systems.
For the innocent reader: Never fully Trust your own Scripts.. In my case, I notably "missed" some GB sizes of datafiles in the CDB$ROOT... Not a huge thing, but still indicative of what can go wrong.
For Oracle folks (if you guys are reading), I would suggest a few tidbits:
- Document the values for Con_Id=0 and Con_id=1 little better. I found the relevant info in v12c, but not in page "latest version" for v26ai. I suspect my google skipped the relevant pages for v26ai?
- Consider some extra view that unites and explains all the values for CON_ID in one place, similar to my inline-view experiment: all_con_ids ?
And yes, my pwd used to be similar to that of agent Mulder: Trus7No!. Until they started demanding a minimum of 42-characters...
-- -- -- -- -- End of this Blogpost - For Real -- -- -- -- --
No comments:
Post a Comment