Database Health Checks RAC & Stand Alone

**************************************************
Database status
Tablespace Usage
ASM Disk Group Usage(RAC)
Backup
Filesystem Usage
Alert Log Check for ORA errors
Listener status

****************************************************************

Cluster status (RAC)

select name,open_mode from v$database;
select count(*),status from v$session group by status;
SET TERMOUT OFF;
COLUMN current_instance NEW_VALUE current_instance NOPRINT;
SELECT rpad(sys_context('USERENV', 'INSTANCE_NAME'), 17) current_instance
FROM dual;
SET TERMOUT ON;
PROMPT
PROMPT +------------------------------------------------------------------------+
PROMPT | Report   : Oracle RAC Instances                                        |
PROMPT | Instance : &current_instance                                           |
PROMPT +------------------------------------------------------------------------+
SET ECHO        OFF
SET FEEDBACK    6
SET HEADING     ON
SET LINESIZE    180
SET PAGESIZE    50000
SET TERMOUT     ON
SET TIMING      OFF
SET TRIMOUT     ON
SET TRIMSPOOL   ON
SET VERIFY      OFF
CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES
COLUMN instance_name          FORMAT a13         HEAD 'Instance|Name / Number'
COLUMN thread#                FORMAT 99999999    HEAD 'Thread #'
COLUMN host_name              FORMAT a28         HEAD 'Host|Name'
COLUMN status                 FORMAT a6          HEAD 'Status'
COLUMN startup_time           FORMAT a20         HEAD 'Startup|Time'
COLUMN database_status        FORMAT a8          HEAD 'Database|Status'
COLUMN archiver               FORMAT a8          HEAD 'Archiver'
COLUMN logins                 FORMAT a10         HEAD 'Logins?'
COLUMN shutdown_pending       FORMAT a8          HEAD 'Shutdown|Pending?'
COLUMN active_state           FORMAT a6          HEAD 'Active|State'
COLUMN version                                   HEAD 'Version'
SELECT
    instance_name || ' (' || instance_number || ')' instance_name
  , thread#
  , host_name
  , status
  , TO_CHAR(startup_time, 'DD-MON-YYYY HH:MI:SS') startup_time
  , database_status
  , archiver
  , logins
  , shutdown_pending
  , active_state
  , version
FROM
    gv$instance
ORDER BY
    instance_number;

Backup Curruption

select count(*) from v$backup_corruption;

Table space Usage

set pagesize 200
col TABLESPACE_NAME FORMAT A15
  select a.TABLESPACE_NAME,
           a.BYTES MB_USED,
           b.BYTES MB_FREE,
           round(((a.BYTES - b.BYTES)/a.BYTES)*100,2) percent_used
    from
           (
           select TABLESPACE_NAME,
                  sum(BYTES/1024/1024) bytes
           from   dba_data_files
           group  by TABLESPACE_NAME
           ) a,
           (
           select TABLESPACE_NAME ,
                  sum(BYTES/1024/1024) bytes from   dba_free_space
           group  by TABLESPACE_NAME
           ) b
   where   a.TABLESPACE_NAME = b.TABLESPACE_NAME(+)
order by ((a.BYTES - b.BYTES)/a.BYTES) desc;

select tablespace_name, status, sum(blocks) * 8192/1024/1024/1024 GB from dba_undo_extents group by tablespace_name, status;
col tablespace_name for a10
select TABLESPACE_NAME,FILE_ID,sum(BYTES_USED/1024/1024) used_mb,sum(BYTES_FREE/1024/1024) free_mb,
round(sum(BYTES_USED/1024/1024)/(sum(BYTES_FREE/1024/1024)+sum(BYTES_USED/1024/1024))*100,2) "%age" from v$temp_space_header group by TABLESPACE_NAME,FILE_ID;
select count(*) from dba_objects where status like 'INVALID';
SELECT SUBSTR(owner||'.'||segment_name,1,50) OBJECT,EXTENTS, MAX_EXTENTS FROM DBA_SEGMENTS WHERE MAX_EXTENTS - EXTENTS < 30 ORDER BY EXTENTS;
Select segment_name, owner, extents, max_extents from dba_segments where segment_type = 'TABLE' and (extents +1) >= max_extents;
col month for a5
select
  to_char(CREATION_TIME,'RRRR') year,
  sum(bytes)/1024/1024/1024 Bytes_in_GB
from
  v$datafile
group by
  to_char(CREATION_TIME,'RRRR'),
order by
  1;

ASM DISK GROUP 

set lines 132
col name format a14
col PATH format a33
select GROUP_NUMBER,NAME,STATE,TYPE,TOTAL_MB/1024,FREE_MB/1024 from v$asm_diskgroup;

-------------------------------------------------------------------

select 100-(FREE_MB/TOTAL_MB)*100 as "Used % Safely Usable Area",  Name from v$asm_diskgroup_stat

RMAN BACKUP CHECK

ALTER SESSION SET NLS_DATE_FORMAT='DD-MM-YYYY HH24:MI:SS';
SELECT START_TIME,END_TIME,STATUS,INPUT_TYPE FROM V$RMAN_BACKUP_JOB_DETAILS order by start_time;

###### ALert log location#####

SQL>show parameter back

ls -lrt *.log

tail -f alert_xxx.log


*****************************************************************************************************************************************

SQL> select count(*),to_char(completion_time,'DD-MON-YYYY') from v$archived_log group by to_char(completion_time,'DD-MON-YYYY') order by 2;