Search This Blog

Total Pageviews

Wednesday 17 May 2017

ORA-00059 : maximum number of DB_FILES exceeded

ORA-00059 :  maximum number of DB_FILES exceeded




alter tablespace anuj add datafile '+DATA' size 1G autoextend on maxsize unlimited; 
*
ERROR at line 1:
ORA-00059: maximum number of DB_FILES exceeded                   


ORA-00059      :  maximum number of DB_FILES exceeded
Cause          :  The value of the DB_FILES initialization parameter was exceeded.
Action         :  Increase the value of the DB_FILES parameter and restart database.

MAXDATAFILES and DB_FILES Parameters (Doc ID 119507.1)
 ORACLE 8i AND ABOVE   
----------------------------  
For Oracle 8i and higher, MAXDATAFILES parameter of the control file will expand automatically upto the db_files parameter.
For example, if MAXDATAFILES is set to 32 and DB_FILES is set to 40, then, when a new file
is added, then, the MAXDATAFILE will increase in the control file automatically.
 


col name       for a15
col par_value  for a12
select name,par_value,records_total,records_used, par_value - records_used remain_value from (  select name,value par_value       from v$parameter where name in ('db_files','DB_FILE'))
                                                                                            ,(  select records_total,records_used from v$controlfile_record_section where type ='DATAFILE');


alter session set nls_Date_format='dd-mm-yyyy hh24:mi:ss'; 
set linesize 200 
col name      for a15
col par_value for a12
col db_name for a12
select  sysdate,sys_context ('USERENV', 'db_name') db_name,a.* from (select name,par_value,records_total,records_used, par_value - records_used remain_value from (select name,value par_value from v$parameter 
where upper(name) in ('DB_FILES')) ,(  select records_total,records_used from v$controlfile_record_section where type ='DATAFILE')) a


SYSDATE             DB_NAME      NAME            PAR_VALUE    RECORDS_TOTAL RECORDS_USED REMAIN_VALUE
------------------- ------------ --------------- ------------ ------------- ------------ ------------
09-03-2021 13:19:00 AAAPROD      db_files        200                   1024           54          146
 

set lines 200 pages 100 feedback off verify off
col type           for a30        HEADING 'Record Type'
col record_size    for 999999     HEADING 'Record|Size'
col records_used   for 999999     HEADING 'Records|Used'
col first_index    for 9999999    HEADING 'First|Index'
col last_index     for 9999999    HEADING 'Last|Index'
col last_recid     for 999999999  HEADING 'Last|Record|ID'
select
   type,
   record_size,
   records_total,
   records_used,
   first_index,
   last_index,
   last_recid
from v$controlfile_record_section
 where 1=1
 and type like 'DATA%'
 ;
 
                                                                                     Last
                                Record               Records    First     Last     Record
Record Type                       Size RECORDS_TOTAL    Used    Index    Index         ID
------------------------------ ------- ------------- ------- -------- -------- ----------
DATABASE                           316             1       1        0        0          0
DATAFILE                           520          1024      54        0        0      18304
DATAFILE COPY                      736          1000       0        0        0          0
DATABASE INCARNATION                56           292       2        1        2          2
DATAFILE HISTORY                   568            57       0        0        0          0
DATABASE BLOCK CORRUPTION           80          8384       0        0        0          0
                 
                      
show parameter db_file

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_files                             integer     200


for total no of file ..
select count(*) from v$datafile;

Change on Standby as well .. if you have a standby  !!!

alter system set db_files=[VALUE] scope=spfile sid='*';

alter system set db_files=400 SCOPE=spfile sid=’*’;

Restart the database !!!


Wednesday 3 May 2017

RMAN SQL TO FIND BACKUP DETAILS ..

RMAN SQL TO FIND BACKUP DETAILS ..


Rman View in 11g ..
select table_name from dict where table_name like 'V$%RMAN%';



SQL> select name from v$fixed_table where name like '%RMAN%';

NAME
------------------------------
GV$RMAN_CONFIGURATION
V$RMAN_CONFIGURATION
GV$RMAN_STATUS_CURRENT
V$RMAN_STATUS
GV$RMAN_OUTPUT
V$RMAN_OUTPUT
V$RMAN_BACKUP_SUBJOB_DETAILS
V$RMAN_BACKUP_JOB_DETAILS
V$RMAN_BACKUP_TYPE
GV$RMAN_ENCRYPTION_ALGORITHMS
V$RMAN_ENCRYPTION_ALGORITHMS
GV$RMAN_COMPRESSION_ALGORITHM
V$RMAN_COMPRESSION_ALGORITHM

13 rows selected.


V$RMAN_COMPRESSION_ALGORITHM provides descriptions of supported compression algorithms. It is used by the RMAN client.
V$RMAN_CONFIGURATION         Information about RMAN persistent configuration settings.
V$RMAN_ENCRYPTION_ALGORITHMS displays supported encryption algorithms. It is used by the RMAN client to validate user-requested algorithms. 


set linesize 300 pagesize 300
CLEAR COLUMNS BREAKS COMPUTES
COLUMN "value" 	FORMAT a100		
select name ||' = '|| value "value" FROM v$rman_configuration ORDER BY name;

value
----------------------------------------------------------------------------------------------------
CONTROLFILE AUTOBACKUP = ON
CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE = DISK TO '/dumps/DGBDR/cf_%F'
RETENTION POLICY = TO REDUNDANCY 1


set linesize 200 pagesize 200 
col username  form a10
col kill        form a20
col os_id  form a6
col client_info form a24
col action  form a21
SELECT
''''||a.sid ||','|| a.serial#||',@'||a.inst_id ||'''' kill
-- ,a.sid || ',' || a.serial# AS kill_string, b.spid AS OS_ID
,a.username
,(CASE WHEN a.client_info IS NULL AND a.action IS NOT NULL THEN 'First Default'
 WHEN a.client_info IS NULL AND a.action IS NULL THEN 'Polling'
 ELSE a.client_info
 END) client_info
,a.action
FROM gv$session a ,gv$process b
WHERE a.program like '%rman%'
and a.inst_id = b.inst_id 
AND a.paddr = b.addr;




set lines 200
column sid format 9999
column spid format 99999
column client_info format a25
column event format a30
column secs format 9999
select ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill ,sid, spid, client_info, event, seconds_in_wait secs, p1, p2, p3 from gv$process p, gv$session s 
where p.addr = s.paddr
and p.inst_id=s.inst_id 
and client_info like 'rman channel=%';



set pagesize 200
col STATUS  format a9
col hrs  format 999.99
select session_key, input_type, status,to_char(start_time,'mm/dd/yy hh24:mi') start_time,to_char(end_time,'mm/dd/yy hh24:mi') end_time,elapsed_seconds/3600 hrs from V$rman_backup_job_details order by session_key;


select ''''||sid ||','|| serial#||',@'||inst_id ||'''' kill, start_time,totalwork, sofar, (sofar/totalwork) * 100 done,sysdate + time_remaining/3600/24 end_at from gv$session_longops 
where totalwork > sofar 
AND opname NOT LIKE '%aggregate%' 
AND opname like 'RMAN%';


select recid,set_stamp,sequence#,first_change#,next_change#   from  v$backup_redolog;


gv$rman_output

select * from v$rman_status 
where session_recid = (select max(session_recid) from v$rman_status) 
order by recid;


alter session set nls_date_format='DD-MM-YYYY HH24:MI:SS';
set lines 200
set numf 999999999999
col status for a25
select start_time, end_time, operation, object_type, status, output_bytes from v$rman_status where session_recid = (select max(session_recid) from v$rman_status) 
order by recid;


column elapsed format a25
select to_char(start_time,'dd-mm-yyyy:hh24:mi:ss') start_time, to_char(end_time,'dd-mm-yyyy:hh24:mi:ss') end_time, 
       lpad(round(floor((end_time-start_time)*24*60*60)/3600),2,'0')
       || ':' ||
       lpad(floor((((end_time-start_time)*24*60*60) -
       floor(((end_time-start_time)*24*60*60)/3600)*3600)/60),2,'0')
       || ':' ||
       lpad(round((((end_time-start_time)*24*60*60) -
       floor(((end_time-start_time)*24*60*60)/3600)*3600 -
       (floor((((end_time-start_time)*24*60*60) -
       floor(((end_time-start_time)*24*60*60)/3600)*3600)/60)*60))),2,'0') elapsed, output_bytes  
from v$rman_backup_job_details 
order by start_time;


select to_char(start_time,'dd-mm-yyyy:hh24:mi:ss') start_time, to_char(end_time,'dd-mm-yyyy:hh24:mi:ss') end_time, output_bytes  
from v$rman_backup_job_details 
order by start_time;



select session_recid,to_char(start_time,'yyyy-mm-dd:hh24:mi:ss') start_time, to_char(end_time,'yyyy-mm-dd:hh24:mi:ss') end_time, output_bytes  
from v$rman_backup_job_details a
where session_recid = (select max(session_recid) from v$rman_backup_job_details b );

select trunc(START_TIME),sum(OUTPUT_BYTES)/1000000  PROCESSED_IN_MB from v$rman_status where STATUS ='COMPLETED'
group by trunc(START_TIME)
order by 1 desc


TRUNC(START_TIME)   PROCESSED_IN_MB
------------------- ---------------
03-05-2017 00:00:00              59
02-05-2017 00:00:00          277230
01-05-2017 00:00:00          120287
30-04-2017 00:00:00          116449


set lines 200 pages 200 
col "Start Time" for a20;
col "End Time" for a20;
col STATUS for a15;
select  to_char(START_TIME, 'DD-MON-YY HH24:MI:SS') "Start Time", 
 to_char(END_TIME, 'DD-MON-YY HH24:MI:SS') "End Time", 
 STATUS "Status", 
 INPUT_BYTES/1024/1024/1024 "Input Bytes (in GB)", 
 OUTPUT_BYTES/1024/1024/1024 "Output Bytes (in GB)", 
 ELAPSED_SECONDS "Time in Seconds"
from V$RMAN_BACKUP_JOB_DETAILS
order by session_key;


set pages 200
col STATUS format a9 
col hrs format 999.99 
select SESSION_KEY, INPUT_TYPE, STATUS, to_char(START_TIME,'mm/dd/yy hh24:mi') start_time, to_char(END_TIME,'mm/dd/yy hh24:mi') end_time, elapsed_seconds/3600 hrs 
from V$RMAN_BACKUP_JOB_DETAILS
where input_type like 'DB%'
order by session_key;


SESSION_KEY INPUT_TYPE    STATUS    START_TIME     END_TIME           HRS
----------- ------------- --------- -------------- -------------- -------
      12283 DB INCR       COMPLETED 04/21/17 00:00 04/21/17 07:52    7.88
      12295 DB INCR       COMPLETED 04/22/17 00:00 04/23/17 09:54   33.91
      12302 DB INCR       COMPLETED 04/23/17 00:00 04/23/17 10:39   10.65
      12317 DB INCR       COMPLETED 04/24/17 00:00 04/24/17 07:33    7.55
      12329 DB INCR       COMPLETED 04/25/17 00:00 04/25/17 07:58    7.97
      12341 DB INCR       COMPLETED 04/26/17 00:00 04/26/17 15:44   15.75
      12353 DB INCR       COMPLETED 04/27/17 00:00 04/27/17 06:58    6.97
      12365 DB INCR       COMPLETED 04/28/17 00:00 04/28/17 07:55    7.92
      12377 DB INCR       FAILED    04/29/17 00:00 04/30/17 09:06   33.11
      12384 DB INCR       COMPLETED 04/30/17 00:00 04/30/17 08:22    8.38
      12397 DB INCR       COMPLETED 05/01/17 00:00 05/01/17 07:52    7.87
      12409 DB INCR       RUNNING   05/02/17 00:00 05/03/17 10:38   34.65

12 rows selected.

SELECT vbd.file#, vrbjd.start_time, vrbjd.end_time, vbd.incremental_level, vrbjd.input_type, vrbjd.status FROM v$rman_backup_job_details vrbjd, v$backup_datafile vbd
WHERE vbd.completion_time BETWEEN vrbjd.start_time AND vrbjd.end_time
AND vrbjd.input_type <> 'ARCHIVELOG'
ORDER BY 2,1;



select to_char(START_TIME,'DD MON YY HH24:Mi') START_TIME ,to_char(END_TIME,'DD MON YY HH24:Mi') END_TIME ,OUTPUT_BYTES/1000000  PROCESSED_IN_MB,STATUS
from v$rman_status where trunc(START_TIME)= trunc(sysdate);


START_TIME      END_TIME        PROCESSED_IN_MB STATUS
--------------- --------------- --------------- -------------------------
03 MAY 17 07:56 03 MAY 17 09:29            3049 RUNNING
03 MAY 17 00:00 03 MAY 17 04:30           13746 COMPLETED WITH ERRORS
03 MAY 17 06:18 03 MAY 17 06:18              30 COMPLETED
03 MAY 17 07:56 03 MAY 17 07:56              30 COMPLETED
03 MAY 17 06:18 03 MAY 17 06:18               0 COMPLETED
03 MAY 17 00:00 03 MAY 17 04:30           13746 FAILED

6 rows selected.

select ctime "Date",       decode(backup_type, 'L', 'Archive Log', 'D', 'Full', 'Incremental') backup_type,    bsize "Size MB"
 from (select trunc(bp.completion_time) ctime  , backup_type  , round(sum(bp.bytes/1024/1024),2) bsize
       from v$backup_set bs, v$backup_piece bp
       where bs.set_stamp = bp.set_stamp
       and bs.set_count  = bp.set_count
       and bp.status = 'A'
       group by trunc(bp.completion_time), backup_type)
order by 1, 2;

select max(START_TIME) "START_TIME" ,END_TIME,ELAPSED_SECONDS/60 "ELAPSED_MINS", OUTPUT_BYTES/1024/1024/1024 "OUTPUT_BYTES_GB",STATUS from v$rman_backup_job_details 
group by start_time,END_TIME,ELAPSED_SECONDS,OUTPUT_BYTES, STATUS having max(START_TIME) > sysdate-1;


START_TIME          END_TIME             ELAPSED_MINS OUTPUT_BYTES_GB STATUS
------------------- ------------------- ------------- --------------- ----------
02-05-2017 12:00:02 03-05-2017 06:18:30          1098              68 COMPLETED
03-05-2017 00:00:03 03-05-2017 04:30:06           270              13 FAILED


select /*+ rule */ NVL(min(r.status),'NO BACKUP') as status 
from V$RMAN_BACKUP_JOB_DETAILS r inner join (select distinct session_stamp, incremental_level from v$backup_set_details) b 
on r.session_stamp = b.session_stamp where incremental_level is not null 
and r.start_time > sysdate - 7 
and b.incremental_level = 0;

STATUS
----------
FAILED


TAG Details
-----------

set linesize 200 pagesize 200
col HANDLE for a60
select bp.TAG, bp.handle, bp.STAMP, decode(bs.backup_type, 'L', 'Archive Log', 'D', 'Full', 'Incremental') backup_type,bp.SET_STAMP, to_char(bp.completion_time + 30/1440,'yyyy/mm/dd HH24:MI:SS') end_time 
from v$backup_set bs, v$backup_piece bp 
where bs.set_stamp = bp.set_stamp
and bs.set_count  = bp.set_count ; 
-- and tag='&TAG';


TAG                              HANDLE                                                            STAMP BACKUP_TYPE  SET_STAMP END_TIME
-------------------------------- ------------------------------------------------------------ ---------- ----------- ---------- -------------------
TAG20170429T120003               /backup/VIHAAN/20170429_VIHAAN_13567_1_942587273             942587275 Archive Log  942587273 2017/04/29 15:11:44


col dbsize_mbytes      for 99,999,990.00 justify right head "DBSIZE_MB"
col input_mbytes       for 99,999,990.00 justify right head "READ_MB"
col output_mbytes      for 99,999,990.00 justify right head "WRITTEN_MB"
col output_device_type for a10           justify left head "DEVICE"
col complete           for 990.00        justify right head "COMPLETE %" 
col compression        for 990.00        justify right head "COMPRESS|% ORIG"
col est_complete       for a20           head "ESTIMATED COMPLETION"
col recid              for 9999999       head "ID"

select recid
     , output_device_type
     , dbsize_mbytes
     , input_bytes/1024/1024 input_mbytes
     , output_bytes/1024/1024 output_mbytes
     , (output_bytes/input_bytes*100) compression
     , (mbytes_processed/dbsize_mbytes*100) complete
     , to_char(start_time + (sysdate-start_time)/(mbytes_processed/dbsize_mbytes),'DD-MON-YYYY HH24:MI:SS') est_complete
  from v$rman_status rs  , (select sum(bytes)/1024/1024 dbsize_mbytes from v$datafile) 
 where status='RUNNING'
   and output_device_type is not null
/

select to_char(START_TIME,'DD MON YY HH24:Mi') START_TIME ,STATUS,OPERATION from v$rman_status where STATUS like '%ERROR%'
and  START_TIME > trunc(sysdate) -8
order by 1 desc


START_TIME      STATUS                    OPERATION
--------------- ------------------------- ---------------------------------
29 APR 17 00:00 COMPLETED WITH ERRORS     RMAN
03 MAY 17 00:00 COMPLETED WITH ERRORS     RMAN

set linesize 300 pagesize 300
col x1 format a20 heading "Begin Date/Time"
col x2 format a20 heading "Completion Date/Time"
col x3 format a30 heading "Status"
col x4 format a20 heading "Backup Actual Size"
col x5 format a20 heading "RMAN Compressed Size"
col x6 format a30 heading "Backup Total Time (HH:MI:SS)"
col x7 format a29 heading "Backup Type-Incremental/Full"
col HOST for a16
BREAK ON HOST SKIP 1 ON database_name SKIP 1 
col database_name for a15
select SYS_CONTEXT('USERENV', 'SERVER_HOST') HOST ,SYS_CONTEXT('USERENV','DB_NAME') database_name,to_char(start_time,'dd-mon-yyyy:hh24:mi:ss') x1,to_char(end_time,'dd-mon-yyyy:hh24:mi:ss') x2,status x3,input_bytes_display x4, output_bytes_display x5, time_taken_display x6, input_type x7 
from v$rman_backup_job_details 
where 1=1 
and start_time > sysdate-15
-- and input_type!='ARCHIVELOG'
;

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

define NUMBER_OF_DAYS=15
set lines 300 pages 1000
col cf for 9,999
col df for 9,999
col elapsed_seconds heading "ELAPSED|SECONDS"
col i0 for 9,999
col i1 for 9,999
col l for 9,999
col output_mbytes for 999,999,999 heading "OUTPUT|MBYTES"
col session_recid for 999999 heading "SESSION|RECID"
col session_stamp for 99999999999 heading "SESSION|STAMP"
col status for a10 trunc
col time_taken_display for a10 heading "TIME|TAKEN"
col output_instance for 999999999 heading "OUT|INST"
col START_TIME  for a20
col END_TIME for a20
col Sday for a4
col eday for a4
select
  j.session_recid, j.session_stamp,
 (j.output_bytes/1024/1024) output_mbytes, j.status, j.input_type,
  to_char(j.start_time, 'dd-mm-yyyy hh24:mi:ss') start_time,
  to_char(j.end_time, 'dd-mm-yyyy hh24:mi:ss') end_time
  ,decode(to_char(j.start_time, 'd'), 1, 'Mon', 2, 'Tue',3, 'Wed', 4, 'Thu',5, 'Fri', 6, 'Sat',7, 'Sun') Sday 
  ,decode(to_char(j.end_time, 'd'),   1, 'Mon', 2, 'Tue', 3, 'Wed', 4, 'Thu',5, 'Fri', 6, 'Sat',7,  'Sun') Eday 								 
 --, j.elapsed_seconds
 , j.time_taken_display
  ,x.cf, x.df, x.i0, x.i1, x.l,
  ro.inst_id output_instance
from V$RMAN_BACKUP_JOB_DETAILS j
  left outer join (select
                     d.session_recid, d.session_stamp,
                     sum(case when d.controlfile_included = 'YES' then d.pieces else 0 end) CF,
                     sum(case when d.controlfile_included = 'NO'
                               and d.backup_type||d.incremental_level = 'D' then d.pieces else 0 end) DF,
                     sum(case when d.backup_type||d.incremental_level = 'D0' then d.pieces else 0 end) I0,
                     sum(case when d.backup_type||d.incremental_level = 'I1' then d.pieces else 0 end) I1,
                     sum(case when d.backup_type = 'L' then d.pieces else 0 end) L
                   from
                     V$BACKUP_SET_DETAILS d
                     join V$BACKUP_SET s on s.set_stamp = d.set_stamp and s.set_count = d.set_count
                   where s.input_file_scan_only = 'NO'
                   group by d.session_recid, d.session_stamp) x
    on x.session_recid = j.session_recid and x.session_stamp = j.session_stamp
  left outer join (select o.session_recid, o.session_stamp, min(inst_id) inst_id
                   from GV$RMAN_OUTPUT o
                   group by o.session_recid, o.session_stamp)
    ro on ro.session_recid = j.session_recid and ro.session_stamp = j.session_stamp
where j.start_time > trunc(sysdate)-&NUMBER_OF_DAYS
--and j.input_type='DB FULL'  ------------<<<<<<<<<<<<<<<<<
order by j.start_time;








**********



 
 set lines 750 pages 9999
col TIME_TAKEN_DISPLAY for a10
select
  j.session_recid, j.session_stamp,
  to_char(j.start_time, 'dd-mm-yyyy hh24:mi:ss') start_time,
  to_char(j.end_time, 'dd-mm-yyyy hh24:mi:ss') end_time,
  (j.output_bytes/1024/1024) output_mbytes, j.status, j.input_type,
  decode(to_char(j.start_time, 'd'), 1, 'Sunday', 2, 'Monday',
                                     3, 'Tuesday', 4, 'Wednesday',
                                     5, 'Thursday', 6, 'Friday',
                                     7, 'Saturday') dow,
  j.elapsed_seconds, j.time_taken_display,
  x.cf, x.df, x.i0, x.i1, x.l,
  ro.inst_id output_instance
from V$RMAN_BACKUP_JOB_DETAILS j
  left outer join (select
                     d.session_recid, d.session_stamp,
                     sum(case when d.controlfile_included = 'YES' then d.pieces else 0 end) CF,
                     sum(case when d.controlfile_included = 'NO'
                               and d.backup_type||d.incremental_level = 'D' then d.pieces else 0 end) DF,
                     sum(case when d.backup_type||d.incremental_level = 'D0' then d.pieces else 0 end) I0,
                     sum(case when d.backup_type||d.incremental_level = 'I1' then d.pieces else 0 end) I1,
                     sum(case when d.backup_type = 'L' then d.pieces else 0 end) L
                   from
                     V$BACKUP_SET_DETAILS d
                     join V$BACKUP_SET s on s.set_stamp = d.set_stamp and s.set_count = d.set_count
                   where s.input_file_scan_only = 'NO'
                   group by d.session_recid, d.session_stamp) x
    on x.session_recid = j.session_recid and x.session_stamp = j.session_stamp
  left outer join (select o.session_recid, o.session_stamp, min(inst_id) inst_id
                   from GV$RMAN_OUTPUT o
                   group by o.session_recid, o.session_stamp)
    ro on ro.session_recid = j.session_recid and ro.session_stamp = j.session_stamp
order by j.start_time desc;

==========

SET PAGESIZE 300
COL time_taken_display FORM A15 HEAD "Time|Taken|HH:MM:SS"
COL rman_end_time FORM A17
COL i_size_gig FORM 999999.99 HEAD "Input|Gig"
COL o_size_gig FORM 999999.99 HEAD "Output|Gig"
COL compression_ratio FORM 99999.99 HEAD "Comp.|Ratio"
COL status FORM A25
COL input_type FORM A14
--
SELECT
time_taken_display
,TO_CHAR(end_time,'dd-mon-rrrr hh24:mi') AS rman_end_time
,input_bytes/1024/1024/1024 i_size_gig
,output_bytes/1024/1024/1024 o_size_gig
,compression_ratio
,status
,input_type
FROM v$rman_backup_job_details
where 1=1
and status = 'FAILED'
ORDER BY end_time;


set pages 9999 lines 500
set numformat 999999999.99
set trim on
set trims on
alter session set nls_date_format = 'DD-MM-YYYY HH24:MI:SS';
col INSTANCE for a9
col status for a22
col COMMAND_ID for a20
col INPUT_TYPE for a10
col OUTPUT_DEVICE_TYPE for a10
col OUTPUT_BYTES_PER_SEC_DISPLAY for a9
col status for a25 heading "BACKUP|STATUS"
col COMMAND_ID heading "BACKUP NAME"
col STARTED_TIME heading "START TIME"
COL END_TIME heading "END TIME"
col ELAPSED_TIME heading "MINUTES | TAKEN"
col INPUT_TYPE heading "INPUT|TYPE"
col OUTPUT_DEVICE_TYPE heading "OUTPUT|DEVICES"
col INPUT_SIZE heading  "INPUT SIZE|GB"
col OUTPUT_SIZE heading  "OUTPUT SIZE|GB"
col OUTPUT_BYTES_PER_SEC_DISPLAY heading "OUTPUT | RATE|(PER SEC)"
SELECT (SELECT instance_name FROM v$instance) || ' ' || (SELECT instance_number FROM v$instance) instance,rs.sid,
rj.COMMAND_ID,
rj.STATUS,
max(rj.START_TIME) STARTED_TIME,
rj.END_TIME,
rj.ELAPSED_SECONDS/60 ELAPSED_TIME,
rj.INPUT_TYPE,
rj.OUTPUT_DEVICE_TYPE,
rj.INPUT_BYTES/1024/1024/1024 INPUT_SIZE,
rj.OUTPUT_BYTES/1024/1024/1024 OUTPUT_SIZE,
rj.OUTPUT_BYTES_PER_SEC_DISPLAY
from v$rman_backup_job_details rj, v$rman_status rs
where rj.COMMAND_ID=rs.COMMAND_ID
group by rs.sid,rj.COMMAND_ID,rj.STATUS,rj.START_TIME,rj.END_TIME,rj.ELAPSED_SECONDS,rj.INPUT_TYPE,rj.OUTPUT_DEVICE_TYPE,rj.INPUT_BYTES,rj.OUTPUT_BYTES,rj.OUTPUT_BYTES_PER_SEC_DISPLAY
having max(rj.START_TIME) > sysdate -15 
order by rj.START_TIME desc
/

===
set lines 300 pages 300
col cf for 9,999
col df for 9,999
col elapsed_seconds heading "ELAPSED|SECONDS"
col i0 for 9,999
col i1 for 9,999
col l for 9,999
col output_mbytes for 9,999,999 heading "OUTPUT|MBYTES"
col session_recid for 999999 heading "SESSION|RECID"
col session_stamp for 99999999999 heading "SESSION|STAMP"
col status for a10 trunc
col time_taken_display for a10 heading "TIME|TAKEN"
col output_instance for 9999 heading "OUT|INST"
select
j.session_recid, j.session_stamp,
to_char(j.start_time, 'dd-mm-yyyy hh24:mi:ss') start_time,
to_char(j.end_time, 'dd-mm-yyyy hh24:mi:ss') end_time,
(j.output_bytes/1024/1024) output_mbytes, j.status, j.input_type,
decode(to_char(j.start_time, 'd'), 1, 'Sunday', 2, 'Monday',
3, 'Tuesday', 4, 'Wednesday',
5, 'Thursday', 6, 'Friday',
7, 'Saturday') dow,
j.elapsed_seconds, j.time_taken_display,
x.cf, x.df, x.i0, x.i1, x.l,
ro.inst_id output_instance
from V$RMAN_BACKUP_JOB_DETAILS j
left outer join (select
d.session_recid, d.session_stamp,
sum(case when d.controlfile_included = 'YES' then d.pieces else 0 end) CF,
sum(case when d.controlfile_included = 'NO'
and d.backup_type||d.incremental_level = 'D' then d.pieces else 0 end) DF,
sum(case when d.backup_type||d.incremental_level = 'D0' then d.pieces else 0 end) I0,
sum(case when d.backup_type||d.incremental_level = 'I1' then d.pieces else 0 end) I1,
sum(case when d.backup_type = 'L' then d.pieces else 0 end) L
from
V$BACKUP_SET_DETAILS d
join V$BACKUP_SET s on s.set_stamp = d.set_stamp and s.set_count = d.set_count
where s.input_file_scan_only = 'NO'
group by d.session_recid, d.session_stamp) x
on x.session_recid = j.session_recid and x.session_stamp = j.session_stamp
left outer join (select o.session_recid, o.session_stamp, min(inst_id) inst_id
from GV$RMAN_OUTPUT o
group by o.session_recid, o.session_stamp)
ro on ro.session_recid = j.session_recid and ro.session_stamp = j.session_stamp
where j.start_time > trunc(sysdate)-&NUMBER_OF_DAYS
order by j.start_time;


set lines 300 pages 300
col backup_type for a4 heading "TYPE"
col controlfile_included heading "CF?"
col incremental_level heading "INCR LVL"
col pieces for 999 heading "PCS"
col elapsed_seconds heading "ELAPSED|SECONDS"
col device_type for a10 trunc heading "DEVICE|TYPE"
col compressed for a4 heading "ZIP?"
col output_mbytes for 9,999,999 heading "OUTPUT|MBYTES"
col input_file_scan_only for a4 heading "SCAN|ONLY"
select
d.bs_key, d.backup_type, d.controlfile_included, d.incremental_level, d.pieces,
to_char(d.start_time, 'dd-mm-yyyy hh24:mi:ss') start_time,
to_char(d.completion_time, 'dd-mm-yyyy hh24:mi:ss') completion_time,
d.elapsed_seconds, d.device_type, d.compressed, (d.output_bytes/1024/1024) output_mbytes, s.input_file_scan_only
from V$BACKUP_SET_DETAILS d
join V$BACKUP_SET s on s.set_stamp = d.set_stamp and s.set_count = d.set_count
where session_recid = &SESSION_RECID
and session_stamp = &SESSION_STAMP
order by d.start_time;

set lines 200 pages 2000
col STATUS format a25
col dev format a10
col START_TIME format a16
col END_TIME format a16
col hrs format 99.99
col INBYTES format a10
col OUTBYTES format a10
select SESSION_KEY, INPUT_TYPE, STATUS, output_device_type DEV,
to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi') end_time,
elapsed_seconds/3600 hrs,
input_bytes_display inbytes, output_bytes_display outbytes
from V$RMAN_BACKUP_JOB_DETAILS
where status='RUNNING'
order by session_key;

set lines 300 pages 300 col backup_type for a4 heading "TYPE" col controlfile_included heading "CF?" col incremental_level heading "INCR LVL" col pieces for 999 heading "PCS" col elapsed_seconds heading "ELAPSED|SECONDS" col device_type for a10 trunc heading "DEVICE|TYPE" col compressed for a4 heading "ZIP?" col output_mbytes for 9,999,999 heading "OUTPUT|MBYTES" col input_file_scan_only for a4 heading "SCAN|ONLY" col START_TIME for a27 select d.bs_key, d.backup_type, d.controlfile_included, d.incremental_level, d.pieces, to_char(d.start_time, 'dd-mm-yyyy hh24:mi:ss') start_time, to_char(d.completion_time, 'dd-mm-yyyy hh24:mi:ss') completion_time, d.elapsed_seconds, d.device_type, d.compressed, (d.output_bytes/1024/1024) output_mbytes, s.input_file_scan_only,d.status from V$BACKUP_SET_DETAILS d join V$BACKUP_SET s on s.set_stamp = d.set_stamp and s.set_count = d.set_count where session_recid in ( select SESSION_KEY from V$RMAN_BACKUP_JOB_DETAILS where status='RUNNING' ) --and session_stamp = SESSION_STAMP order by d.start_time;


set lines 200 pages 1000 select output from GV$RMAN_OUTPUT where session_recid in ( select SESSION_KEY from V$RMAN_BACKUP_JOB_DETAILS where status='RUNNING' ) --and session_stamp = SESSION_STAMP order by recid;

===

set linesize 500 pagesize 300 col errors for a20 col backup_type         for a15 col time_frame         for a30 col status for a15 col device_type         for a15 col recid for a12 with rman_status as ( select distinct substr(backup_type,1,instr(backup_type||' datafile',' datafile')) backup_type , round(mbytes_processed/1024,1) total_gb, output_device_type device_type , round((mbytes_processed/1024)/((end_time-start_time))/24,1) gb_hour , start_time, end_time, status, session_stamp, recid , to_char(mod(floor((end_time-start_time)*24),24),'00')||'h' || to_char(mod(floor((end_time-start_time)*24*60),60),'00')||'m' || to_char(mod(floor((end_time-start_time)*24*60*60),60),'00')||'s' elapsed_time , case when status='FAILED' then case when (select count(1) from v$rman_output where session_stamp = x.session_stamp and rman_status_recid = x.recid) = 0 then 'RMAN Output Not Found' else (select nvl(rtrim(xmlagg(xmlelement(e,substr(output,1,instr(output,':')-1)||',')).extract('//text()'),','),'No Errors ~ Session Hung') from v$rman_output where session_stamp = x.session_stamp and rman_status_recid = x.recid and output like 'ORA-%') end else null end errors from (select nvl(substr(y.output,instr(y.output,'starting ')+9),object_type) backup_type , mbytes_processed, object_type, output_device_type , start_time, end_time, status, x.session_stamp, x.recid from v$rman_status x, v$rman_output y where x.session_stamp = y.session_stamp(+) and x.recid = y.rman_status_recid(+) and object_type in ('DB INCR','DB FULL') and status != 'RUNNING' and operation = 'BACKUP' and ((lower(y.output) like '%startingÚtafileºckup%' and lower(y.output) not like '%validation%') or y.output is null)) x order by session_stamp desc, recid desc) select session_stamp, recid||'' recid , substr(replace(initcap(backup_type),'Db ','DB '),1,25) backup_type , substr(device_type,1,10) device_type , substr(status,1,11) status , to_char(start_time,'dd-Mon-yyyy hh24:mi')||' ~ '||to_char(end_time,'hh24:mi')||decode(trunc(end_time-start_time),0,null,'(+1Day)') time_frame , elapsed_time, total_gb, gb_hour, substr(errors,1,30) errors from rman_status; SESSION_STAMP RECID BACKUP_TYPE DEVICE_TYPE STATUS TIME_FRAME ELAPSED_TIME TOTAL_GB GB_HOUR ERRORS ------------- ------------ --------------- --------------- --------------- ------------------------------ ------------ ---------- ---------- -------------------- 1092272403 6185 DISK COMPLETED 26-Dec-2021 01:00 ~ 01:10 00h 10m 02s 33.8 202.2 1091667603 6155 DISK COMPLETED 19-Dec-2021 01:00 ~ 01:10 00h 10m 11s 33.8 199.2 1091062802 6125 DISK COMPLETED 12-Dec-2021 01:00 ~ 01:10 00h 10m 12s 33.7 198 1090458003 6095 DISK COMPLETED 05-Dec-2021 01:00 ~ 01:10 00h 10m 19s 33.6 195.6 1089766803 6065 DISK COMPLETED 28-Nov-2021 01:00 ~ 01:10 00h 10m 11s 33.7 198.5 1089162003 6035 DISK COMPLETED 21-Nov-2021 01:00 ~ 01:10 00h 10m 03s 33.6 200.5 1088557202 6005 DISK COMPLETED 14-Nov-2021 01:00 ~ 01:10 00h 10m 20s 33.8 196.2 1087952403 5975 DISK COMPLETED 07-Nov-2021 01:00 ~ 01:10 00h 10m 11s 33.6 198 1087347602 5946 DISK COMPLETED 31-Oct-2021 01:00 ~ 01:10 00h 10m 13s 33.4 196.4 9 rows selected.


set linesize 300 pagesize 300 col x1 format a20 heading "Begin Date/Time" col x2 format a20 heading "Completion Date/Time" col x3 format a10 heading "Status" col x4 format a20 heading "Backup Actual Size" col x5 format a20 heading "RMAN Compressed Size" col x6 format a30 heading "Backup Total Time (HH:MI:SS)" col x7 format a29 heading "Backup Type-Incremental/Full" col HOST for a24 BREAK ON HOST SKIP 1 ON database_name SKIP 1 col database_name for a10 select SYS_CONTEXT('USERENV', 'SERVER_HOST') HOST ,SYS_CONTEXT('USERENV','DB_NAME') database_name,to_char(start_time,'dd-mon-yyyy:hh24:mi:ss') x1,to_char(end_time,'dd-mon-yyyy:hh24:mi:ss') x2,status x3,input_bytes_display x4, output_bytes_display x5, time_taken_display x6, input_type x7 from v$rman_backup_job_details where 1=1 and start_time > sysdate -7 -- and input_type!='ARCHIVELOG' -- and input_type='DB INCR' -- and status in ('COMPLETED','RUNNING') order by 3 ;






define NUMBER_OF_DAYS=30 set pages 1000 linesize 300 col cf for 9,999 col df for 9,999 col elapsed_seconds heading "ELAPSED|SECONDS" col i0 for 9,999 col i1 for 9,999 col l for 9,999 col output_mbytes for 9,999,999 heading "OUTPUT|MBYTES" col session_recid for 999999 heading "SESSION|RECID" col session_stamp for 99999999999 heading "SESSION|STAMP" col status for a20 trunc col time_taken_display for a10 heading "TIME|TAKEN" col output_instance for 9999 heading "OUT|INST" col START_TIME for a24 col END_TIME for a24 PROMPT Enter Number of Days Back to look PROMPT select j.session_recid, j.session_stamp, to_char(j.start_time, 'yyyy-mm-dd hh24:mi:ss') start_time, to_char(j.end_time, 'yyyy-mm-dd hh24:mi:ss') end_time, (j.output_bytes/1024/1024) output_mbytes, j.status, j.input_type, decode(to_char(j.start_time, 'd'), 1, 'Sunday', 2, 'Monday', 3, 'Tuesday', 4, 'Wednesday', 5, 'Thursday', 6, 'Friday', 7, 'Saturday') dow, j.elapsed_seconds, j.time_taken_display, x.cf, x.df, x.i0, x.i1, x.l, ro.inst_id output_instance from V$RMAN_BACKUP_JOB_DETAILS j left outer join (select d.session_recid, d.session_stamp, sum(case when d.controlfile_included = 'YES' then d.pieces else 0 end) CF, sum(case when d.controlfile_included = 'NO' and d.backup_type||d.incremental_level = 'D' then d.pieces else 0 end) DF, sum(case when d.backup_type||d.incremental_level = 'D0' then d.pieces else 0 end) I0, sum(case when d.backup_type||d.incremental_level = 'I1' then d.pieces else 0 end) I1, sum(case when d.backup_type = 'L' then d.pieces else 0 end) L from V$BACKUP_SET_DETAILS d join V$BACKUP_SET s on s.set_stamp = d.set_stamp and s.set_count = d.set_count where s.input_file_scan_only = 'NO' group by d.session_recid, d.session_stamp) x on x.session_recid = j.session_recid and x.session_stamp = j.session_stamp left outer join (select o.session_recid, o.session_stamp, min(inst_id) inst_id from GV$RMAN_OUTPUT o group by o.session_recid, o.session_stamp) ro on ro.session_recid = j.session_recid and ro.session_stamp = j.session_stamp where j.start_time > trunc(sysdate)-&NUMBER_OF_DAYS order by j.start_time;
--- from catalog set linesize 500 define db_name='' define NUMBER_OF_DAYS=30 col "Input size" format a14 col "output size" format a14 col "time_taken_display" format a10 col "output/sec" format a14 set pages 1000 col cf for 9,999 col df for 9,999 col elapsed_seconds heading "ELAPSED|SECONDS" col i0 for 9,999 col i1 for 9,999 col l for 9,999 col output_mbytes for 9,999,999 heading "OUTPUT|MBYTES" col session_recid for 999999 heading "SESSION|RECID" col session_stamp for 99999999999 heading "SESSION|STAMP" col status for a20 trunc col time_taken_display for a10 heading "TIME|TAKEN" col output_instance for 9999 heading "OUT|INST" set pages 35 select * from( select J.DB_NAME,j.session_recid, j.session_stamp, j.session_key, j.time_taken_display, x.cf, x.df, x.i0, x.i1, x.l, j.input_bytes_display as "Input Size", j.output_bytes_display "Output Size", j.output_bytes_per_sec_display as "Output/Sec", j.status, j.input_type, decode(to_char(j.start_time, 'd'), 1, 'Sunday', 2, 'Monday', 3, 'Tuesday', 4, 'Wednesday', 5, 'Thursday', 6, 'Friday', 7, 'Saturday') dow, j.elapsed_seconds, to_char(j.start_time, 'yyyy-mm-dd hh24:mi:ss') start_time, to_char(j.end_time, 'yyyy-mm-dd hh24:mi:ss') end_time -- x.cf, x.df, x.i0, x.i1, x.l -- ro.inst_id output_instance from RC_RMAN_BACKUP_JOB_DETAILS j left outer join (select d.session_recid, d.session_stamp, sum(case when d.controlfile_included = 'BACKUP' then d.pieces else 0 end) CF, sum(case when d.controlfile_included = 'NO' and d.backup_type||d.incremental_level = 'D' then d.pieces else 0 end) DF, sum(case when d.backup_type||d.incremental_level = 'D0' then d.pieces else 0 end) I0, sum(case when d.backup_type||d.incremental_level = 'I1' then d.pieces else 0 end) I1, sum(case when d.backup_type = 'L' then d.pieces else 0 end) L from RC_BACKUP_SET_DETAILS d join RC_BACKUP_SET s on s.set_stamp = d.set_stamp and s.set_count = d.set_count where s.input_file_scan_only = 'NO' group by d.session_recid, d.session_stamp) x on x.session_recid = j.session_recid and x.session_stamp = j.session_stamp /* left outer join (select o.session_recid, o.session_stamp, min(inst_id) inst_id from RC_RMAN_OUTPUT o group by o.session_recid, o.session_stamp) ro on ro.session_recid = j.session_recid and ro.session_stamp = j.session_stamp */ where j.start_time > trunc(sysdate)-&NUMBER_OF_DAYS order by j.start_time ) --where db_name=upper('&db_name') ;

====


set linesize 300 pagesize 300
col kill for a60
col kill1 for a16
COL opname FORM A35
COL pct_complete FORM 99.99 HEAD "% Comp."
COL start_time FORM A15 HEAD "Start|Time"
COL hours_running FORM 9999.99 HEAD "Hours|Running"
COL minutes_left FORM 999999 HEAD "Minutes|Left"
COL est_comp_time FORM A15 HEAD "Est. Comp.|Time"
col MESSAGE for a50 trunc
--
SELECT 
--sid, serial#, 
''''||sid ||','|| serial#||',@'||inst_id ||'''' kill1,
opname,
ROUND(sofar/totalwork*100,2) AS pct_complete,
TO_CHAR(start_time,'dd-mon-yy hh24:mi') start_time,
(sysdate-start_time)*24 hours_running,
((sysdate-start_time)*24*60)/(sofar/totalwork)-(sysdate-start_time)
*24*60 minutes_left,
TO_CHAR((sysdate-start_time)/(sofar/totalwork)+start_time,'dd-mon-yy hh24:mi')
est_comp_time,MESSAGE
,       'ALTER SYSTEM KILL SESSION '''||SID||','||SERIAL#||',@'||inst_ID||''' IMMEDIATE;' kill
FROM gv$session_longops
WHERE opname LIKE 'RMAN%'
AND opname NOT LIKE '%aggregate%'
AND totalwork != 0
AND sofar <> totalwork;

===========

alter session set nls_date_format='dd-mon-yy hh24:mi:ss';
set lines 1500
set pages 100
col CLI_INFO format a10
col spid format a5
col ch format a20
col seconds format 999999.99
col filename format a65
col bfc  format 9
col "% Complete" format 999.99
col event format a40
set numwidth 10

col kill for a17
select sysdate from dual;

REM gv$session_longops (channel level)



define 1='18-MAR-15 11:00:00'



prompt
prompt Channel progress - gv$session_longops:
prompt
select ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill, CLIENT_INFO ch, context, sofar, totalwork,  round(sofar/totalwork*100,2) "% Complete"    
FROM gv$session_longops o, gv$session s
     WHERE opname LIKE 'RMAN%'
     AND opname NOT LIKE '%aggregate%'
     AND o.sid=s.sid
     AND totalwork != 0
     AND sofar <> totalwork;




prompt
prompt Session progess - CURRENT wait events and time in wait so far:
prompt


select ''''||sid ||','|| serial#||',@'||inst_id ||'''' kill, CLIENT_INFO ch, seq#, event, state, wait_time_micro/1000000 seconds 
from gv$session where program like '%rman%' 
and wait_time = 0 
and not action is null;

REM use the following for 10G
--select  inst_id, sid, CLIENT_INFO ch, seq#, event, state, seconds_in_wait secs
--from gv$session where program like '%rman%' and
--wait_time = 0 and
--not action is null;



REM gv$backup_async_io

prompt
prompt Disk (file and backuppiece) progress - includes tape backuppiece 
prompt if backup_tape_io_slaves=TRUE:
prompt
select ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill, CLIENT_INFO Ch, a.STATUS,open_time, round(BYTES/1024/1024,2) "SOFAR Mb" , round(total_bytes/1024/1024,2) TotMb, io_count,
round(BYTES/TOTAL_BYTES*100,2) "% Complete" , a.type, filename
from gv$backup_async_io a,  gv$session s
where not a.STATUS in ('UNKNOWN')
and a.sid=s.sid 
and open_time > to_date('&1', 'dd-mon-rr hh24:mi:ss') 
order by 2,7;




REM gv$backup_sync_io
prompt
prompt Tape backuppiece progress (only if backup_tape_io_slaves=FALSE):
prompt
select s.inst_id, a.sid, CLIENT_INFO Ch, filename, a.type, a.status, buffer_size bsz, buffer_count bfc,open_time open, io_count from gv$backup_sync_io a, gv$session s
where 1=1
and a.sid=s.sid and
open_time > to_date('&1', 'dd-mon-rr hh24:mi:ss') ;


select file#,
avg(datafile_blocks),
avg(blocks_read),
avg(blocks_read/datafile_blocks) * 100 as "% read for backup"
from v$backup_datafile
where incremental_level > 0
and used_change_tracking = 'YES'
group by file#
order by file#;


Tuesday 2 May 2017

Oracle pre upgrade script DBUPGDIAG.SQL


 Oracle pre upgrade script DBUPGDIAG.SQL 


--  NAME:  DBUPGDIAG.SQL  
--  Version: 1.2
--  Executed as SYS as sysdba

col TODAY NEW_VALUE _DATE
col VERSION NEW_VALUE _VERSION
set termout off
select to_char(SYSDATE,'fmMonth DD, YYYY') TODAY from DUAL;
select version from v$instance;
set termout on  echo off  feedback off  head off  verify off
Prompt
PROMPT Enter location for Spooled output:
Prompt
DEFINE log_path = &1
column timecol new_value timestamp
column spool_extension new_value suffix
SELECT to_char(sysdate,'dd_Mon_yyyy_hhmi') timecol,'.log' spool_extension FROM 
sys.dual;
column output new_value dbname
SELECT value || '_' output FROM v$parameter WHERE name = 'db_name';
spool &log_path/db_upg_diag_&&dbname&&timestamp&&suffix
set linesize 150  pages 300  trim on  trims on
col Compatible for a35
col comp_id for a12
col comp_name for a40
col org_version for a11
col prv_version for a11
col owner for a12
col object_name for a40
col object_type for a40
col Wordsize for a25
col Metadata for a8
col 'Initial DB Creation Info' for a35
col 'Total Invalid JAVA objects' for a45
col 'Role' for a30
col 'User Existence' for a27
col "JAVAVM TESTING" for a15
Prompt
Prompt
set feedback off head off
select LPAD('*** Start of LogFile ***',50) from dual;
select LPAD('Oracle Database Upgrade Diagnostic Utility',44)||
       LPAD(TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS'),26) from dual;
Prompt
Prompt ===============
Prompt Hostname
Prompt ===============
select host_name from v$instance;
Prompt
Prompt ===============
Prompt Database Name
Prompt ===============
select name from v$database;
Prompt
Prompt ===============
Prompt Database Uptime
Prompt ===============
SELECT to_char(startup_time, 'HH24:MI DD-MON-YY') "Startup Time" 
FROM v$instance;
Prompt
Prompt =================
Prompt Database Wordsize
Prompt =================
SELECT distinct('This is a ' || (length(addr)*4) || '-bit database') "WordSize" 
FROM v$process;
Prompt
Prompt ================
Prompt Software Version
Prompt ================
SELECT * FROM v$version;
Prompt
Prompt =============
Prompt Compatibility
Prompt =============
SELECT 'Compatibility is set as '||value Compatible 
FROM v$parameter WHERE name ='compatible';
Prompt
Prompt ================
Prompt Archive Log Mode
Prompt ================
Prompt
archive log list
Prompt
Prompt ================
Prompt Auditing Check
Prompt ================
Prompt
set head on
show parameter audit
Prompt
Prompt ================
Prompt Cluster Check
Prompt ================
show parameter cluster_database
Prompt
DOC
################################################################

 If CLUSTER_DATABASE is set to TRUE, change it to FALSE before
 upgrading the database 

################################################################
#
Prompt
Prompt ===========================================
Prompt Tablespace and the owner of the aud$ table  ( IF Oracle Label Security and Oracle Database Vault are installed then aud$ will be in SYSTEM.AUD$)
Prompt ===========================================
select owner,tablespace_name from dba_extents where segment_name='AUD$' group by owner,tablespace_name;
Prompt
Prompt ============================================================================
Prompt count of records in the sys.aud$ table where dbid is null- Standard Auditing
Prompt ============================================================================
Prompt
set head off
select count(*) as Records  from sys.aud$ where dbid is null;
Prompt
Prompt
Prompt ============================================================================================
Prompt count of records in the system.aud$ when dbid is null, Std Auditing with OLS or DV installed
Prompt ============================================================================================
set head off
select count(*) from system.aud$ where dbid is null;
Prompt
Prompt
Prompt =============================================================================
Prompt count of records in the sys.fga_log$ when dbid is null, Fine Grained Auditing
Prompt =============================================================================
set head off
select count(*) from sys.fga_log$ where dbid is null;
Prompt
Prompt
prompt
Prompt ==========================================
Prompt Oracle Label Security is installed or not 
Prompt ==========================================
set head off
SELECT case count(schema)
WHEN 0 THEN 'Oracle Label Security is NOT installed at database level'
ELSE 'Oracle Label Security is installed '
END  "Oracle Label Security Check"
FROM dba_registry
WHERE schema='LBACSYS';
Prompt
Prompt ================
Prompt Number of AQ Records in Message Queue Tables
Prompt ================
Prompt
SET SERVEROUTPUT ON SIZE 100000
declare
   V_COUNT NUMBER;
     cursor c1 is
         select owner,queue_table from dba_queue_tables where owner in ('SYS', 'SYSTEM', 'SYSMAN', 'DBSNMP','WMSYS');
 begin
    for c in c1
     loop
        execute immediate 'select count(1) from ' || c.owner || '.'  || c.queue_table into v_count;
        dbms_output.put_line(c.owner || ' - ' || c.queue_table  || ' - ' || v_count);
     end loop;
 END;
/
Prompt
Prompt ================
Prompt Time Zone version 
Prompt ================
Prompt
SELECT version from v$timezone_file;
Prompt
Prompt ================
Prompt Local Listener
Prompt ================
Prompt
select substr(value,1,50) "Local Listener" from v$parameter where name='local_listener';
Prompt
Prompt ================
Prompt Default and Temporary Tablespaces By User
Prompt ================
Prompt
set head on
COLUMN USERNAME FORMAT A28
COLUMN TEMPORARY_TABLESPACE FORMAT A22
COLUMN DEFAULT_TABLESPACE FORMAT A22
SELECT username, temporary_tablespace,default_tablespace FROM DBA_USERS;
Prompt
Prompt
Prompt ================
Prompt Component Status
Prompt ================
Prompt
SET SERVEROUTPUT ON;
DECLARE

ORG_VERSION varchar2(12);
PRV_VERSION varchar2(12);
P_VERSION VARCHAR2(10);

BEGIN 

SELECT version INTO p_version 
FROM registry$ WHERE cid='CATPROC' ;

IF SUBSTR(p_version,1,5) = '9.2.0' THEN

DBMS_OUTPUT.PUT_LINE(RPAD('Comp ID', 8) ||RPAD('Component',35)|| 
   RPAD('Status',10) ||RPAD('Version', 15));

DBMS_OUTPUT.PUT_LINE(RPAD(' ',8,'-') ||RPAD(' ',35,'-')|| 
   RPAD(' ',10,'-') ||RPAD(' ',15,'-'));

FOR x in (SELECT SUBSTR(dr.comp_id,1,8) comp_id,
 SUBSTR(dr.comp_name,1,35) comp_name, 
 dr.status Status,SUBSTR(dr.version,1,15) version
 FROM dba_registry dr,registry$ r
 WHERE dr.comp_id=r.cid and dr.comp_name=r.cname
 ORDER BY 1)

LOOP

DBMS_OUTPUT.PUT_LINE(RPAD(SUBSTR(x.comp_id,1,8),8) || 
   RPAD(SUBSTR(x.comp_name,1,35),35)||
   RPAD(x.status,10) || RPAD(x.version, 15));
END LOOP;

ELSIF SUBSTR(p_version,1,5) != '9.2.0' THEN

DBMS_OUTPUT.PUT_LINE(RPAD('Comp ID', 8) ||RPAD('Component',35)||  
   RPAD('Status',10) ||RPAD('Version', 15)||
   RPAD('Org_Version',15)||RPAD('Prv_Version',15));

DBMS_OUTPUT.PUT_LINE(RPAD(' ',8,'-') ||RPAD(' ',35,'-')|| 
   RPAD(' ',10,'-')||RPAD(' ',15,'-')||RPAD(' ',15,'-')||
   RPAD(' ',15,'-'));

FOR y in (SELECT SUBSTR(dr.comp_id,1,8) comp_id,
 SUBSTR(dr.comp_name,1,35) comp_name, dr.status Status, 
 SUBSTR(dr.version,1,11) version,org_version,prv_version
 FROM dba_registry dr,registry$ r
 WHERE dr.comp_id=r.cid and dr.comp_name=r.cname
 ORDER BY 1)

LOOP

DBMS_OUTPUT.PUT_LINE(RPAD(substr(y.comp_id,1,8), 8) || 
    RPAD(substr(y.comp_name,1,35),35)||RPAD(y.status,10) ||
    RPAD(y.version, 15)||RPAD(y.org_version,15)||RPAD(y.prv_version,15));

END LOOP;

END IF;
END;
/
SET SERVEROUTPUT OFF
Prompt
Prompt
Prompt
Prompt ======================================================
Prompt List of Invalid Database Objects Owned by SYS / SYSTEM
Prompt ======================================================
Prompt
set head on
SELECT case count(object_name)
WHEN 0 THEN 'There are no Invalid Objects'
ELSE 'There are '||count(object_name)||' Invalid objects'
END "Number of Invalid Objects"
FROM dba_objects 
WHERE status='INVALID'
AND owner in ('SYS','SYSTEM');
Prompt
DOC 
################################################################

 If there are no Invalid objects below will result in zero rows.

################################################################
#
Prompt
set feedback on
SELECT owner,object_name,object_type 
FROM dba_objects 
WHERE status='INVALID' 
AND owner in ('SYS','SYSTEM')
ORDER BY owner,object_type;
set feedback off
Prompt
Prompt ================================
Prompt List of Invalid Database Objects
Prompt ================================
Prompt
set head on
SELECT case count(object_name)
WHEN 0 THEN 'There are no Invalid Objects'
ELSE 'There are '||count(object_name)||' Invalid objects'
END "Number of Invalid Objects"
FROM dba_objects 
WHERE status='INVALID'
AND owner  in 
('SYSMAN','CTXSYS','ORDSYS','MDSYS','EXFSYS','WKSYS','WKPROXY','WK_TEST','OLAPSYS','OUTLIN','TSMSYS',
'FLOWS_FILES','SI_INFORMTN_SCHEMA','ORACLE_OCM','ORDPLUGINS','ORDDATA','DBSNMP');
Prompt
DOC
################################################################

 If there are no Invalid objects below will result in zero rows.

################################################################
#
Prompt
set feedback on
SELECT owner,object_name,object_type 
FROM dba_objects 
WHERE status='INVALID' 
AND owner in ('SYSMAN','CTXSYS','ORDSYS','MDSYS','EXFSYS','WKSYS','WKPROXY','WK_TEST','OLAPSYS','OUTLIN','TSMSYS',
'FLOWS_FILES','SI_INFORMTN_SCHEMA','ORACLE_OCM','ORDPLUGINS','ORDDATA','DBSNMP')
ORDER BY owner,object_type;
set feedback off
Prompt
Prompt ======================================================
Prompt Count of Invalids by Schema
Prompt ======================================================
Prompt
select owner,object_type,count(*) from dba_objects where status='INVALID'
group by owner,object_type order by owner,object_type ;
Prompt ==============================================================
Prompt Identifying whether a database was created as 32-bit or 64-bit
Prompt ==============================================================
Prompt
DOC 
###########################################################################

 Result referencing the string 'B023' ==> Database was created as 32-bit
 Result referencing the string 'B047' ==> Database was created as 64-bit
 When String results in 'B023' and when upgrading database to 10.2.0.3.0 
 (64-bit) , For known issue refer below articles
  
 Note 412271.1 ORA-600 [22635] and ORA-600 [KOKEIIX1] Reported While 
               Upgrading Or Patching Databases To 10.2.0.3
 Note 579523.1 ORA-600 [22635], ORA-600 [KOKEIIX1], ORA-7445 [KOPESIZ] and 
              OCI-21500 [KOXSIHREAD1] Reported While Upgrading To 11.1.0.6

###########################################################################
#
Prompt
SELECT SUBSTR(metadata,109,4) "Metadata",
CASE SUBSTR(metadata,109,4)
WHEN 'B023' THEN 'Database was created as 32-bit'
WHEN 'B047' THEN 'Database was created as 64-bit'
ELSE 'Metadata not Matching'
END "Initial DB Creation Info"
FROM sys.kopm$;
Prompt
Prompt ===================================================
Prompt Number of Duplicate Objects Owned by SYS and SYSTEM
Prompt ===================================================
Prompt
Prompt Counting duplicate objects ....
Prompt
SELECT count(1) 
FROM dba_objects 
WHERE object_name||object_type in 
   (SELECT object_name||object_type  
    from dba_objects 
    where owner = 'SYS')
AND owner = 'SYSTEM'
AND object_name NOT in ('AQ$_SCHEDULES','AQ$_SCHEDULES_PRIMARY','DBMS_REPCAT_AUTH','DBMS_REPCAT_AUTH') ;
Prompt
Prompt =========================================
Prompt Duplicate Objects Owned by SYS and SYSTEM
Prompt =========================================
Prompt
Prompt Querying duplicate objects ....
Prompt
SELECT object_name, object_type, subobject_name, object_id 
FROM dba_objects 
WHERE object_name||object_type in 
   (SELECT object_name||object_type  
    FROM dba_objects 
    WHERE owner = 'SYS')
AND owner = 'SYSTEM'
AND object_name NOT in ('AQ$_SCHEDULES','AQ$_SCHEDULES_PRIMARY','DBMS_REPCAT_AUTH','DBMS_REPCAT_AUTH') ; 
Prompt
DOC

################################################################################
Below are expected and required duplicates objects and OMITTED in the report .

Without replication installed:
INDEX           AQ$_SCHEDULES_PRIMARY
TABLE           AQ$_SCHEDULES

If replication is installed by running catrep.sql:
INDEX           AQ$_SCHEDULES_PRIMARY
PACKAGE         DBMS_REPCAT_AUTH
PACKAGE BODY    DBMS_REPCAT_AUTH
TABLE           AQ$_SCHEDULES

If any objects found please follow below article.
Note 1030426.6 How to Clean Up Duplicate Objects Owned by SYS and SYSTEM schema
Read the Exceptions carefully before taking actions.

################################################################################
#
Prompt
Prompt ========================
Prompt Password protected roles
Prompt ========================
Prompt
DOC

################################################################################

 In version 11.2 password protected roles are no longer enabled by default so if 
 an application relies on such roles being enabled by default and no action is
 performed to allow the user to enter the password with the set role command, it 
 is recommended to remove the password from those roles (to allow for existing 
 privileges to remain available). For more information see:

 Note 745407.1 : What Roles Can Be Set as Default for a User?

################################################################################
#
Prompt
Prompt Querying for password protected roles ....
Prompt
break on "Password protected Role"
select r.ROLE "Password protected Role",
p.grantee "Assigned by default to user"
from dba_roles r, dba_role_privs p
where r.PASSWORD_REQUIRED = 'YES' and p.GRANTED_ROLE = r.role
and p.default_role = 'YES'
and p.grantee <> 'SYS' and r.role not in
(select role from dba_application_roles);

Prompt
Prompt ================
Prompt JVM Verification
Prompt ================
Prompt
SET SERVEROUTPUT ON
DECLARE

V_CT NUMBER;
P_VERSION VARCHAR2(10);

BEGIN

-- If so, get the version of the JAVAM component
EXECUTE IMMEDIATE 'SELECT version FROM registry$ WHERE cid=''JAVAVM'' 
   AND status <> 99' INTO p_version;

SELECT count(*) INTO v_ct FROM dba_objects
WHERE object_type LIKE '%JAVA%' AND owner='SYS';

IF SUBSTR(p_version,1,5) = '8.1.7' THEN
IF v_ct>=6787 THEN
DBMS_OUTPUT.PUT_LINE('JAVAVM - Installed properly');
ELSE
DBMS_OUTPUT.PUT_LINE('JAVAVM - Not Installed properly');
END IF;
ELSIF SUBSTR(p_version,1,5) = '9.0.1' THEN
IF v_ct>=8585 THEN
DBMS_OUTPUT.PUT_LINE('JAVAVM - Installed properly');
ELSE
DBMS_OUTPUT.PUT_LINE('JAVAVM - Not Installed properly');
END IF;
ELSIF SUBSTR(p_version,1,5) = '9.2.0' THEN
IF v_ct>=8585 THEN
DBMS_OUTPUT.PUT_LINE('JAVAVM - Installed properly');
ELSE
DBMS_OUTPUT.PUT_LINE('JAVAVM - Not Installed properly');
END IF;
ELSIF SUBSTR(p_version,1,6) = '10.1.0' THEN
IF v_ct>=13866 THEN
DBMS_OUTPUT.PUT_LINE('JAVAVM - Installed properly');
ELSE
DBMS_OUTPUT.PUT_LINE('JAVAVM - Not Installed properly');
END IF;
ELSIF SUBSTR(p_version,1,6) = '10.2.0' THEN
IF v_ct>=14113 THEN
DBMS_OUTPUT.PUT_LINE('JAVAVM - Installed properly');
ELSE
DBMS_OUTPUT.PUT_LINE('JAVAVM - Not Installed properly');
END IF;
END IF;

EXCEPTION WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('JAVAVM - NOT Installed. Below results can be ignored');

END;
/
SET SERVEROUTPUT OFF
Prompt
Prompt ================================================
Prompt Checking Existence of Java-Based Users and Roles
Prompt ================================================
Prompt
DOC

################################################################################

 There should not be any Java Based users for database version 9.0.1 and above.
 If any users found, it is faulty JVM.

################################################################################
#

Prompt
SELECT CASE count(username)
WHEN 0 THEN 'No Java Based Users'
ELSE 'There are '||count(*)||' JAVA based users'
END "User Existence"
FROM dba_users WHERE username LIKE '%AURORA%' AND username LIKE '%OSE%';
Prompt
DOC

###############################################################

 Healthy JVM Should contain Six Roles. 
 If there are more or less than six role, JVM is inconsistent.

###############################################################
#

Prompt
SELECT CASE count(role)
WHEN 0 THEN 'No JAVA related Roles'
ELSE 'There are '||count(role)||' JAVA related roles'
END "Role"
FROM dba_roles 
WHERE role LIKE '%JAVA%';
Prompt
Prompt Roles
Prompt
SELECT role FROM dba_roles WHERE role LIKE '%JAVA%';
set head off
Prompt
Prompt =========================================
Prompt List of Invalid Java Objects owned by SYS
Prompt =========================================
SELECT CASE count(*) 
       WHEN 0 THEN 'There are no SYS owned invalid JAVA objects'
       ELSE 'There are '||count(*)||' SYS owned invalid JAVA objects'
       END "Total Invalid JAVA objects"
FROM dba_objects 
WHERE object_type LIKE '%JAVA%' 
AND status='INVALID' 
AND owner='SYS';
Prompt
DOC

#################################################################

 Check the status of the main JVM interface packages DBMS_JAVA 
 and INITJVMAUX and make sure it is VALID.

 If there are no Invalid objects below will result in zero rows.

#################################################################
#
Prompt
set feedback on
SELECT owner,object_name,object_type
FROM dba_objects 
WHERE object_type LIKE '%JAVA%' 
AND status='INVALID' 
AND owner='SYS';
set feedback off
Prompt
DOC

#################################################################

 If the JAVAVM component is not installed in the database (for 
 example, after creating the database with custom scripts), the 
 next query will report the following error:

   select dbms_java.longname('foo') "JAVAVM TESTING" from dual
   *
   ERROR at line 1:
   ORA-00904: "DBMS_JAVA"."LONGNAME": invalid identifier

 If the JAVAVM component is installed, the query should succeed 
 with 'foo' as result.

#################################################################
#
Prompt
set heading on
select dbms_java.longname('foo') "JAVAVM TESTING" from dual;
set heading off
Prompt 
SET FEEDBACK ON HEAD ON serveroutput on
Prompt ===================================
Prompt Oracle Multimedia/InterMedia status
Prompt ===================================
Prompt

DECLARE
   v_count            NUMBER;
   v_version          varchar2(200);
   v_user_count       number;
   v_status           VARCHAR2(200);
   v_xdb_installed    NUMBER;
   v_xdk_installed    NUMBER;
   v_javavm_installed NUMBER;
   TYPE string_tt IS TABLE OF VARCHAR2 (100)
      INDEX BY BINARY_INTEGER;
   v_user             string_tt;

 BEGIN

 v_count            := 0;
 v_version          := '';
 v_user_count       := 0;
 v_status           := '';
 v_xdb_installed    := 0;
 v_xdk_installed    := 0;
 v_javavm_installed := 0;

 SELECT 1,version,status
 INTO   v_count, v_version, v_status
 FROM dba_registry
 WHERE comp_id='ORDIM';
                                                       
 IF v_count > 0 then
  DBMS_OUTPUT.PUT_LINE ('.');
  DBMS_OUTPUT.PUT_LINE ('Oracle Multimedia/interMedia is installed and listed with the following version: '||v_version||' and status: '||v_status);
  DBMS_OUTPUT.PUT_LINE ('.');

/* check if all users are installed.*/

 v_user(1) := 'ORDSYS';
 v_user(2) := 'ORDPLUGINS';
 v_user(3) := 'MDSYS';
 v_user(4) := 'SI_INFORMTN_SCHEMA';
 v_user(5) := 'ORDDATA';

 DBMS_OUTPUT.PUT_LINE('Checking for installed Database Schemas...');

   FOR i IN v_user.first .. v_user.last LOOP
   SELECT COUNT(username)
   INTO   v_user_count
   FROM   dba_users 
   WHERE  username = v_user(I);

/* ORDDATA user only exists starting 11.2 so no test if v_version is different */

   IF v_user(i) = 'ORDDATA' AND SUBSTR(V_VERSION,1,6) NOT IN ('11.2.0','12.1.0') THEN v_user_count :=2; 
   END IF;

/* SI_INFORMTN_SCHEMA user only exists starting 11.2 so no test if v_version is different */

   IF v_user(i) = 'SI_INFORMTN_SCHEMA' AND SUBSTR(V_VERSION,1,2) NOT IN ('10','11','12') THEN v_user_count :=2; 
   END IF; 

   CASE v_user_count
   WHEN 0 THEN  DBMS_OUTPUT.PUT_LINE (v_user(I)||' user does not exist.');
   WHEN 2 THEN NULL; -- user does not exist in that version
   ELSE DBMS_OUTPUT.PUT_LINE (v_user(I)||' user exists.');
   END CASE;
   END LOOP;

 DBMS_OUTPUT.PUT_LINE('.');

/* Prerequisites Check*/
       DBMS_OUTPUT.PUT_LINE ('Checking for Prerequisite Components...');
       
/* for versions >= 10.2 we will verify, if XDB and XDK are installed and valid */

 SELECT COUNT(1) 
 INTO   v_javavm_installed
 FROM   dba_registry
 WHERE  comp_id='JAVAVM';

 IF v_javavm_installed <> 1 THEN DBMS_OUTPUT.PUT_LINE ('JAVAVM is not installed or not valid'); 
 ELSE
      DBMS_OUTPUT.PUT_LINE('JAVAVM installed and listed as valid');
 END IF;
  
  IF  SUBSTR(V_VERSION,1,2) IN ('11','12') OR 
      SUBSTR(V_VERSION,1,6) = ('10.2.0')      THEN
             
      SELECT COUNT(1) 
      INTO   v_xdk_installed
      FROM   dba_registry
      WHERE  comp_id='XML';

      IF v_xdk_installed <> 1 THEN DBMS_OUTPUT.PUT_LINE ('XDK is not installed or not valid');
      ELSE
        DBMS_OUTPUT.PUT_LINE('XDK installed and listed as valid'); 
      END IF;
                   
      SELECT COUNT(1) 
      INTO   v_xdb_installed
      FROM   dba_registry
      WHERE  comp_id='XDB';
             
      IF v_xdb_installed <> 1 THEN DBMS_OUTPUT.PUT_LINE ('XDB is not installed or not valid');
      ELSE
        DBMS_OUTPUT.PUT_LINE ('XDB installed and listed as valid'); 
      END IF;

  END IF;               
                       
 /* for versions >= 11 we run validate_ordim */

    DBMS_OUTPUT.PUT_LINE ('Validating Oracle Multimedia/interMedia...(no output if component status is valid)'); 

    IF SUBSTR(V_VERSION,1,2) IN ('11','12') THEN
         EXECUTE IMMEDIATE 'begin validate_ordim; end;';
    ELSIF SUBSTR(V_VERSION,1,2) IN ('8.','9.','10') AND  v_status <> 'VALID' THEN
         DBMS_OUTPUT.PUT_LINE('Please run $ORACLE_HOME/ord/im/admin/imchk.sql to display details about invalid interMedia installation');
    END IF;
    
 END IF;

 EXCEPTION
   WHEN NO_DATA_FOUND THEN 
   DBMS_OUTPUT.PUT_LINE ('Oracle Multimedia/interMedia is NOT installed at database level');
 END;
/

set feedback off head off
select LPAD('*** End of LogFile ***',50) from dual;
set feedback on head on
Prompt
spool off
Prompt
set heading off
set heading off
set feedback off
select 'Upload db_upg_diag_&&dbname&&timestamp&&suffix from "&log_path" directory' 
from dual;
set heading on
set feedback on
Prompt
-- - - - - - - - - - - - - - - - Script ends here - - - - - - - - - - - - - - 




Oracle DBA

anuj blog Archive