Precheck script for patching Oracle database

set feedback off
set serverout on
set wrap off
set pages 300
set lines 150
col file_name for a50
col name for a50
col member for a50
col file_id for a5
col "Percent Used" for a20
col segment_name for a30
col tablespace_name for a30
col STATUS for a16
col owner for a20
col table_name for a35
col index_name for a35
col username format a25
col default_tablespace format a25
col temporary_tablespace format a25


spool c:\precheck.txt


PROMPT ================================================================
PROMPT DATABASE HEALTH CHECK REPORT
PROMPT ================================================================

PROMPT
PROMPT
PROMPT DATABASE STATUS
PROMPT =================

select INSTANCE_NAME,STATUS,DATABASE_STATUS,ACTIVE_STATE,STARTUP_TIME from v$instance;


PROMPT
PROMPT
PROMPT DATABASE NAME AND MODE
PROMPT ========================

select name, open_mode, log_mode from v$database;



PROMPT
PROMPT
PROMPT COUNT OF TABLESPACES
PROMPT ========================
select count(*) AS "No. of tablespaces" from v$tablespace;


PROMPT
PROMPT
PROMPT COUNT OF DATAFILES
PROMPT ========================

select count(*) AS "No. of Datafiles" from dba_data_files;


PROMPT
PROMPT
PROMPT COUNT OF INVALID OBJECTS
PROMPT ==========================

select count(*) from dba_objects where status='INVALID';


PROMPT
PROMPT
PROMPT COUNT OF ARCHIVED GENERATED LAST DAY
PROMPT =====================================
Select count(*) "No. of Archive Logs generated" from v$log_history  where to_char(first_time,'dd-mon-rrrr') in (to_char(sysdate-1,'dd-mon-rrrr'));



PROMPT
PROMPT
PROMPT DB PHYSICAL SIZE
PROMPT =====================================
select sum(bytes/1024/1024/1024) "DB Physical Size(GB)" from dba_data_files;


PROMPT
PROMPT
PROMPT DB ACUTAL SIZE
PROMPT =====================================
select sum(bytes/1024/1024/1024) "DB Actual Size(GB)" from dba_segments;

PROMPT
PROMPT
PROMPT PATCH APPLIED
PROMPT =====================================
col action_time for a28
col action for a8
col version for a8
col comments for a30
col status for a10
set line 999 pages 999
select patch_id,  version, status, Action,Action_time from dba_registry_sqlpatch order by action_time;


PROMPT
PROMPT
PROMPT INVALID OBJECTS
PROMPT =====================================
Select count(*) from dba_objects where status=’INVALID’;


PROMPT
PROMPT
PROMPT Check registry component status
PROMPT =====================================
col comp_id for a10
col version for a11
col status for a10
col comp_name for a38
select comp_id,comp_name,version,status from dba_registry;


PROMPT
PROMPT
PROMPT Check the SYSTEM and SYSAUS tablespace
PROMPT =====================================
column "MAXSIZE (MB)"  format 9,999,990.00
column "USED (MB)" format 9,999,990.00
select  a.tablespace_name,a.bytes_alloc/(1024*1024) "MAXSIZE (MB)",
nvl(b.tot_used,0)/(1024*1024) "USED (MB)" from (select tablespace_name,
sum(bytes) physical_bytes,sum(decode(autoextensible,'NO',bytes,'YES',maxbytes)) bytes_alloc
from  dba_data_files group by tablespace_name ) a,(select tablespace_name, 
sum(bytes) tot_used from dba_segments  group by tablespace_name ) b
where  a.tablespace_name = b.tablespace_name (+) and a.tablespace_name not in 
(select distinct  tablespace_name  from dba_temp_files) and a.tablespace_name in ('SYSTEM','SYSAUX');

spool off








PROMPT
PROMPT
PROMPT Check the Error in Alert log last 20 days
PROMPT =====================================
SET linesize 200 pagesize 200
col RECORD_ID FOR 9999999 head ID
col MESSAGE_TEXT FOR a120 head Message
SELECT record_id, to_char(originating_timestamp,’DD-MON-YYYY HH24:MI:SS’) , message_text FROM X$DBGALERTEXT WHERE originating_timestamp > systimestamp - 20 AND regexp_like(message_text, '(ORA-|error)') order by record_id;