Understanding Oracle Multitenant: Querying PDB Status Across Instances

As an Oracle DBA working with Container Databases (CDBs), one of the most common tasks is checking the status of your Pluggable Databases (PDBs). Today I’ll share a useful script that provides a comprehensive view of all PDBs across all instances in your Oracle RAC environment.

The Power of gv$pdbs

The gv$pdbs view is a global view that shows information about all PDBs in a CDB across all instances in an Oracle RAC environment. This is particularly useful when you need to verify the status of PDBs consistently across all nodes.

The Script Explained

Here’s the script we’ll be using:

col inst_id format 99
col con_id format 99
col name format a30
col open_mode format a30
set lines 120 pages 200

SELECT inst_id, con_id, name, open_mode
FROM gv$pdbs
ORDER BY name, inst_id;

Let me break down what each part does:

1. Column Formatting:
2. col inst_id format 99: Formats the instance ID to display as 2 digits
3. col con_id format 99: Formats the container ID to display as 2 digits
4. col name format a30: Allocates 30 characters for the PDB name

5. col open_mode format a30: Allocates 30 characters for the open mode

6. Output Settings:

7. set lines 120: Sets line width to 120 characters

8. set pages 200: Sets page size to 200 lines

9. The Query:

10. Selects instance ID, container ID, PDB name, and open mode
11. From the global view gv$pdbs
12. Orders results by PDB name and instance ID

Sample Output

When you run this script, you’ll see output similar to:

INST_ID CON_ID NAME                           OPEN_MODE
------- ------ ------------------------------ ------------------------------
      1      2 PDB$SEED                       READ ONLY
      1      3 HR_PDB                         READ WRITE
      1      4 FINANCE_PDB                    READ WRITE
      2      2 PDB$SEED                       READ ONLY
      2      3 HR_PDB                         READ WRITE
      2      4 FINANCE_PDB                    MOUNTED

Why This Script is Useful

1. Cross-Instance Visibility: Shows PDB status across all RAC instances
2. Quick Status Check: Immediately identifies PDBs that aren’t in the expected state
3. Consistent Formatting: Clean, readable output regardless of PDB name length
4. Seed Database Included: Shows the PDB$SEED database which is important for PDB creation

Advanced Usage

For more detailed information, you could enhance this query with additional columns:

SELECT inst_id, con_id, name, open_mode,
       restricted, recovery_status, creation_time
FROM gv$pdbs
ORDER BY name, inst_id;

This adds information about restricted mode, recovery status, and creation time.

Conclusion

This simple but powerful script is a must-have in your Oracle DBA toolkit. It provides immediate visibility into your PDB environment across all RAC instances, helping you quickly identify any PDBs that might need attention.

What other PDB-related queries do you find most useful in your daily DBA work? Share your favorites in the comments!