Skip to content
Oracle DBA Scripts & Database Utilities

Database Status (vdb.sql)

What This Script Does

Section titled “What This Script Does”

This script provides comprehensive database status information including database name, creation date, log mode, Data Guard configuration, flashback settings, and supplemental logging status. Essential for understanding database configuration and operational status.

The Script

Section titled “The Script”
rem  VDB.SQL
rem
ttitle 'DATABASE STATUS'
rem
set linesize 200
col NAME format a9 heading 'NAME'
col DB_UNIQUE_NAME format a9 heading 'DB|UNIQUE|NAME'
col OPEN_MODE format a5 heading 'OPEN|MODE'
col CREATED format a11 heading 'CREATED'
col LOG_MODE format a12 heading 'LOG MODE'
col FLASHBACK_ON format a5 heading 'FLASH|BACK|ON'
col PROTECTION_MODE format a20 heading 'PROTECT|MODE'
col PROTECTION_LEVEL format a20 heading 'PROTECT|LEVEL'
col SUPPLEMENTAL_LOG_DATA_MIN format a5 heading 'SUPP|LOG|MIN'
col SUPPLEMENTAL_LOG_DATA_PK  format a5 heading 'SUPP|LOG|PK'
col SUPPLEMENTAL_LOG_DATA_UI  format a5 heading 'SUPP|LOG|UI'




rem
select NAME
     , DB_UNIQUE_NAME
     , OPEN_MODE
     , DATABASE_ROLE
     , to_char(CREATED,'YYYY-MM-DD HH24:MI:SS') CREATED
     , LOG_MODE
     , FLASHBACK_ON
     , CURRENT_SCN
     , PROTECTION_MODE
     , PROTECTION_LEVEL
     , SUPPLEMENTAL_LOG_DATA_MIN
     , SUPPLEMENTAL_LOG_DATA_PK
     , SUPPLEMENTAL_LOG_DATA_UI
  from V$DATABASE;

Parameters

Section titled “Parameters”

The script prompts for:

  • None - this script requires no parameters

Usage

Section titled “Usage”
-- Basic usage
@vdb.sql

Sample Output

Section titled “Sample Output”
                               DATABASE STATUS


NAME      DB        OPEN  DATABASE_ROLE    CREATED             LOG MODE     FLASH CURRENT_SCN      PROTECT              PROTECT              SUPP  SUPP  SUPP
          UNIQUE    MODE                                                    BACK               MODE                 LEVEL                LOG   LOG   LOG
          NAME                                                              ON                                                            MIN   PK    UI
--------- --------- ----- ---------------- ------------------- ------------ ----- -------------- -------------------- -------------------- ----- ----- -----
PRODDB    PRODDB    READ  PRIMARY          2024-01-15 08:30:45 ARCHIVELOG   YES   123456789012   MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE  YES   NO    NO