Essential DBA Scripts

 

Essential DBA Scripts

Database Size check

-- Calculates total size of datafiles, redo logs, control files, and overall database size in MB

SELECT 

    DF.TOTAL / 1048576 AS "DataFile Size MB",

    LOG.TOTAL / 1048576 AS "Redo Log Size MB",

    CONTROL.TOTAL / 1048576 AS "Control File Size MB",

    (DF.TOTAL + LOG.TOTAL + CONTROL.TOTAL) / 1048576 AS "Total Size MB"

FROM 

    dual,

    (SELECT SUM(bytes) AS TOTAL FROM dba_data_files) DF,

    (SELECT SUM(bytes) AS TOTAL FROM v$log) LOG,

    (SELECT SUM((cffsz+1)*cfbsz) AS TOTAL FROM x$kcccf) CONTROL;

Database Uptime

-- Shows database instance startup time

SELECT 

    instance_name,

    TO_CHAR(startup_time, 'MM/DD/YYYY HH24:MI:SS') AS startup_time 

FROM 

    v$instance;

-- Displays the database name and PMON process start time (approximate DB start time)

COL database_name FOR a20

SELECT 

    database_name, 

    TO_CHAR(logon_time, 'DD-MM-YYYY HH24:MI:SS') AS pmon_start_time 

FROM 

    v$session 

WHERE 

    program LIKE '%PMON%';

Redo Log Details

-- Redo log group and member details with size and status

COL REDOLOG_FILE_NAME FORMAT a50

COL STATUS FOR a15

SELECT

    a.GROUP#,

    a.THREAD#,

    a.SEQUENCE#,

    a.ARCHIVED,

    a.STATUS,

    b.MEMBER AS REDOLOG_FILE_NAME,

    (a.BYTES / 1024 / 1024) AS SIZE_MB

FROM 

    v$log a

JOIN 

    v$logfile b ON a.GROUP# = b.GROUP#

ORDER BY 

    a.GROUP#;

-- Summary of redo log groups

SELECT 

    GROUP#,

    THREAD#,

    SEQUENCE#,

    BYTES / 1024 / 1024 AS SIZE_MB,

    MEMBERS,

    STATUS 

FROM 

    v$log;

Control File Details

-- Displays the location of control files from the parameter file

SHOW PARAMETER control_files;

-- Lists control file names from dynamic performance view

SELECT NAME FROM V$CONTROLFILE;

RMAN Backup Location

-- (Placeholder) You may run:

SHOW ALL;

-- or check the default backup destination

SELECT * FROM v$rman_configuration;

Daily Archive Log Generation

-- Archive log generation per day in GB and number of archives

SELECT 

    TRUNC(COMPLETION_TIME, 'DD') AS day,

    THREAD#,

    ROUND(SUM(BLOCKS * BLOCK_SIZE) / 1024 / 1024 / 1024) AS GB,

    COUNT(*) AS archives_generated

FROM 

    v$archived_log

GROUP BY 

    TRUNC(COMPLETION_TIME, 'DD'), THREAD#

ORDER BY 

    day;

Hourly Archive Log Generation

-- Displays hourly archive generation in GB

SET PAGES 1000;

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

SELECT 

    TRUNC(COMPLETION_TIME, 'HH') AS hour,

    THREAD#,

    ROUND(SUM(BLOCKS * BLOCK_SIZE) / 1024 / 1024 / 1024) AS GB,

    COUNT(*) AS archives

FROM 

    v$archived_log

GROUP BY 

    TRUNC(COMPLETION_TIME, 'HH'), THREAD#

ORDER BY 

    hour;

Character Set of the Database

-- Check current NLS character set used in the database

COL PARAMETER FOR a30

COL VALUE FOR a50

SELECT 

    * 

FROM 

    nls_database_parameters 

WHERE 

    parameter = 'NLS_CHARACTERSET';