a collection of scripts to ease the day-to-day work of an Oracle DBA
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
 
 
 
 
 
 

89 lines
2.7 KiB

--
-- Oracle Database QuickCheck before/after patching
--
-- before and after patching, connect as SYSDBA and run this script. Make sure
-- to set SPOOL before, each time to a different file; e.g.
-- SPOOL pre_patch.txt
-- SPOOL post_patch.txt
-- compare contents afterwards, e.g. using "diff -u"
-- (ideally, the diff should be empty)
--
-- Note this is by far no complete validation but rather a quick check!
-- PS: It's also a good idea to study the output from the first run prior to
-- patching the DB. It's e.g. no good idea to patch when there's block
-- corruption (last query in this script).
-- invalid objects
col owner for a20
col object_name for a30
col subobject_name for a30
col object_type for a23
col status for a7
col oracle_maintained for a1
set lines 250 pages 50
set serverout on
prompt
prompt Invalid Objects
prompt ===============
select owner,object_name,subobject_name,object_type,status,oracle_maintained
from dba_objects
where status!='VALID'
order by 1,2,3,4;
col index_name for a30
prompt
prompt Unusable Indexes
prompt ================
col rebuild_unusable_indexes for a190
select owner,index_name from dba_indexes where status='unusable';
-- status of database jobs
col last_run for a25
col next_run for a25
prompt
prompt DBMS_JOBS
select job,schema_user,failures,to_char(last_date,'dd-mon-yyyy hh24:mi:ss') last_run,
to_char(next_date,'dd-mon-yyyy hh24:mi:ss') next_run
from dba_jobs
order by 1,2,4,5;
col job_name for a30
col state for a15
col fails for 9999
col REPEAT_INTERVAL for a70
col "LAST_RUN / REPEAT_INTERVAL" for a67
col "DURA(d:hh:mm:ss)" for a15
prompt
prompt DBMS_SCHEDULER
select JOB_NAME,OWNER,ENABLED,STATE, FAILURE_COUNT as fails,
to_char(LAST_START_DATE,'DD-Mon-YYYY hh24:mi:ss')||' / '||REPEAT_INTERVAL "LAST_RUN / REPEAT_INTERVAL",
extract(day from last_run_duration) ||':'|| lpad(extract(hour from last_run_duration),2,'0')||':'||
lpad(extract(minute from last_run_duration),2,'0')||':'||lpad(round(extract(second from last_run_duration)),2,'0') "DURA(d:hh:mm:ss)"
from dba_scheduler_jobs
order by ENABLED,STATE;
-- incidents and alerts
col recent_problems_1_week_back for a45
prompt
prompt Incidents in the last 10 days
prompt =============================
select problem_key recent_problems_1_week_back,to_char(firstinc_time,'dd-mon-yy hh24:mi:ss') first_occurence,
to_char(lastinc_time,'dd-mon-yy hh24:mi:ss') last_occurence
from v$diag_problem
where lastinc_time > sysdate -10;
col creation_time for a40
col reason for a80
prompt
prompt Alerts in the last 10 days
prompt ==========================
select reason,creation_time,metric_value from dba_outstanding_alerts;
-- block corruption
prompt
prompt Block corruption
prompt ================
select * from v$database_block_corruption;