Inspect Database State and Structure in Oracle AI Database 26ai

Understand the Instance, Database, and Container

The instance is the running memory and process infrastructure that manages database files. It includes the shared system global area (SGA) and background processes. A client establishes a session through a connection to an instance; the instance does not run inside that session's process.

The CDB supplies the database structure containing the root, the seed, and user-created PDBs. PDBs have their own datafiles within that structure, but do not each have a separate instance or control-file set. With Oracle Real Application Clusters (RAC), multiple instances access the same CDB. Instance identities and redo threads therefore matter when interpreting results from different connections.

Use an account authorized to query the required views. These SQL*Plus examples assume an existing administrative connection, usually to the root for the CDB inventory. They do not require you to restart the database or open a PDB. SHOW commands are SQL*Plus client commands, whereas SELECT statements are SQL.

Distinguish NOMOUNT, MOUNT, and OPEN

Startup progresses through stages with different information available. Determine the current stage before interpreting a failed query or drawing conclusions about application availability.

Not every dynamic performance view can be queried at NOMOUNT. For example, a query requiring a mounted database cannot succeed merely because the instance has started. That failure alone does not demonstrate corruption. Similarly, querying a recorded datafile name at MOUNT does not establish that Oracle has successfully read every block in that file.

Check the Connection and V$INSTANCE

SHOW CON_NAME

SELECT instance_name, host_name, version_full, status,
       database_status, logins, startup_time
FROM v$instance;

Check CDB and PDB Open Modes

Once the database is mounted or open, inspect its identity and role. Inspect PDB modes from an appropriate root connection, normally with the CDB open:

SELECT name, db_unique_name, cdb, log_mode,
       open_mode, database_role
FROM v$database;

SELECT con_id, name, open_mode, restricted
FROM v$pdbs
ORDER BY con_id;

NAME identifies the database, while DB_UNIQUE_NAME distinguishes it in configurations such as Data Guard. Neither is necessarily the service used by the client. CDB identifies the container-database architecture. DATABASE_ROLE helps distinguish a primary from a standby, and LOG_MODE establishes whether archive logging is enabled.

V$DATABASE.OPEN_MODE describes the database opening, while V$PDBS.OPEN_MODE describes individual PDBs. An open primary CDB with a mounted application PDB cannot provide normal access to that PDB's application data. Conversely, a mounted standby or intentionally closed PDB may be operating exactly as intended. Compare results with the expected configuration.

PDB$SEED normally appears as read-only. V$PDBS lists PDBs rather than the root; V$CONTAINERS supports a broader container inventory. Retain CON_ID when recording results so that later file information can be associated with the appropriate container.

Locate the Parameter Source

Oracle recommends a binary server parameter file (SPFILE), which Oracle manages. A text initialization parameter file (PFILE) remains supported. Do not hand-edit an SPFILE. First discover whether the running instance used one:

SHOW PARAMETER spfile

A populated value identifies the SPFILE path. A blank value generally indicates startup from a PFILE and deserves interpretation rather than an automatic failure label. For ordinary default lookup, the familiar filename sequence is spfile<SID>.ora, spfile.ora, then init<SID>.ora. The location depends on the platform and Oracle-home configuration.

That sequence is not a universal description of every startup. An explicit PFILE, a stub PFILE pointing to an ASM SPFILE, or Oracle Restart/Clusterware configuration can determine the source. The generic name spfile.ora does not itself create a shared RAC configuration. Discover real ASM names or aliases instead of constructing a guessed parameter-file path.

Compare Effective and Persistent Settings

Three related views answer different questions: V$PARAMETER shows effective session values, V$SYSTEM_PARAMETER shows instance-wide effective values in the relevant context, and V$SPPARAMETER describes stored SPFILE settings. Inspect selected storage-related values:

SELECT name, display_value, isdefault
FROM v$system_parameter
WHERE name IN ('db_name', 'db_unique_name', 'control_files',
               'db_create_file_dest', 'db_create_online_log_dest_1',
               'db_create_online_log_dest_2',
               'db_recovery_file_dest', 'db_recovery_file_dest_size')
ORDER BY name;

SELECT sid, name, display_value, ordinal
FROM v$spparameter
WHERE isspecified = 'TRUE'
  AND name IN ('control_files', 'db_create_file_dest',
               'db_recovery_file_dest', 'db_recovery_file_dest_size')
ORDER BY name, sid, ordinal;

A persistent change awaiting restart can differ from a running value. The SPFILE query filters explicit entries; if no SPFILE was used at startup, ISSPECIFIED is false. Preserve SID and ORDINAL because instance-specific and list-valued settings require context. A simple row comparison does not reconstruct every effective setting at the next startup.

The destination list is illustrative, not exhaustive. Oracle Managed Files (OMF) creation settings describe destinations used when eligible files are created; changing those settings does not relocate existing files. SPFILE and control-file protection also belongs in the backup plan. RMAN's BACKUP SPFILE and BACKUP CURRENT CONTROLFILE protect different objects and are separate from this inspection.

Inventory the Control Files

SELECT name, status, is_recovery_dest_file
FROM v$controlfile;

Inspect Redo Groups and Members

Online redo groups hold successive portions of the redo stream. Members within a group hold redundant copies of that group's redo. Each redo thread needs at least two groups, but the appropriate number and size depend on workload and log-switch behavior.

SELECT group#, thread#, sequence#, bytes / 1024 / 1024 AS size_mb,
       members, status, archived
FROM v$log
ORDER BY thread#, group#;

SELECT group#, type, member, status
FROM v$logfile
ORDER BY group#, member;

The first query describes groups, their threads, sizes, and current states. The second identifies member paths. Retain TYPE because V$LOGFILE can include standby redo members as well as online redo members. A blank member STATUS is not inherently an error.

Connect group status to the checkpoint lesson: a group marked ACTIVE can still be needed for instance recovery even though it is no longer current. Do not interpret ARCHIVED as proof that a usable archived copy or backup remains available. Also, losing one member differs from losing every member containing required redo. This inspection records that structure without attempting repair.

Associate Datafiles and Tempfiles with Containers

Include both permanent datafiles and tempfiles in the inventory. They serve different purposes, so report them separately:

SELECT con_id, file#, name
FROM v$datafile
ORDER BY con_id, file#;

SELECT con_id, file#, name
FROM v$tempfile
ORDER BY con_id, file#;

Use container identifiers to relate files to the root, seed, or application PDB. These recorded names may identify filesystem files or ASM-managed files. Neither the naming convention nor a familiar directory proves a particular storage device is healthy.

These queries do not validate every block, confirm encryption-key availability, or establish that backups cover the required recovery point. They provide the file inventory needed to ask those questions. Preserve the distinction between knowing where a file belongs and demonstrating that it can be restored.

Review Archive Destinations and Recovery Storage

Interpret archival information alongside V$DATABASE.LOG_MODE. ARCHIVELOG supports retaining redo after online groups are reused, but the mode alone does not guarantee that required logs remain accessible or backed up. Inspect configured destinations across the available destination IDs:

SELECT dest_id, status, destination, error
FROM v$archive_dest
WHERE status <> 'INACTIVE'
ORDER BY dest_id;

The filter omits inactive entries while retaining states such as deferred, alternate, and error. Compare each destination with its intended purpose. An alternate destination does not automatically prevent a stall when recovery storage fills: activation rules, mandatory destinations, and minimum successful destination requirements affect the outcome.

The Fast Recovery Area (FRA) is an optional managed destination for recovery-related files. Inspect its configured quota and recorded usage rather than assuming a fixed capacity is appropriate:

SELECT name, space_limit, space_used, space_reclaimable,
       number_of_files
FROM v$recovery_file_dest;

SELECT file_type, percent_space_used, percent_space_reclaimable,
       number_of_files
FROM v$recovery_area_usage;

The space columns in the first query are bytes; the second reports percentages by file type. SPACE_RECLAIMABLE describes space associated with eligible files, not simply unused disk space. The FRA quota and the underlying storage capacity must both be considered. Other consumers may use the same physical storage.

An unconfigured FRA does not prove that no backups exist, just as configured recovery storage does not prove that backups are usable. Investigate unexpected results and archive errors with the alert log and the backup configuration. Deleting files or changing destination settings is outside this read-only exercise.

Interpret Dynamic Views in Context

V$ names are public synonyms for SYS-owned V_$ views, not physical files or ordinary tables named V_$. Their information includes runtime state and metadata derived from sources such as control-file records. View availability and interpretation depend on the current database state.

Container visibility also depends on the view, account privileges, and applicable CONTAINER_DATA settings. A root connection does not automatically grant unrestricted access to every container's information. In RAC, corresponding GV$ views add instance context. For example:

SELECT inst_id, instance_name, host_name, status, startup_time
FROM gv$instance
ORDER BY inst_id;

Record when you run the inspection. Successive dynamic-view queries are not a guaranteed transaction-consistent snapshot; state can change between them. If results appear contradictory, repeat the relevant checks and correlate their timing before concluding that a failure occurred.

Other views answer more specific questions: V$SGAINFO describes memory components, while V$SESSION and V$SQL help investigate activity. Detailed performance diagnosis and RMAN validation are separate tasks. If you later use AWR or ASH, verify the applicable Oracle Diagnostics Pack entitlement.

Record the Inspection Outcome

Finish with a short record containing the connection container, instance and database identities, startup time, database role and open mode, PDB states, parameter source, physical-file inventory, archive destination status, and FRA usage. Compare each observation with the intended operating state.

For example, an open CDB does not resolve an unavailable application if its PDB remains mounted. Likewise, a successful file inventory does not establish that the recovery chain is complete. This distinction makes the inspection useful: it identifies what is known and what still requires a targeted check.

The next lesson brings together the module's database architecture and recovery concepts.