RMAN catalog backup info

set lines 134
set pages 250
spool /home/oracle/scripts/logs/list_catalog_backup.log;
select DB NAME,dbid,NVL(TO_CHAR(max(backuptype_db),'DD/MM/YYYY HH24:MI'),'01/01/0001:00:00') DBBKP,
NVL(TO_CHAR(max(backuptype_arch),'DD/MM/YYYY HH24:MI'),'01/01/0001:00:00') ARCBKP
from (select a.name DB,dbid,
decode(b.bck_type,'D',max(b.completion_time),'I', max(b.completion_time)) BACKUPTYPE_db,
decode(b.bck_type,'L',max(b.completion_time)) BACKUPTYPE_arch
from rc_database a,bs b where a.db_key=b.db_key
and b.bck_type is not null
and b.bs_key not in(Select bs_key from rc_backup_controlfile where AUTOBACKUP_DATE is not null or AUTOBACKUP_SEQUENCE is not null)
and b.bs_key not in(select bs_key from rc_backup_spfile)
group by a.name,dbid,b.bck_type
) group by db,dbid
order by least(to_date(DBBKP,'DD/MM/YYYY HH24:MI'),to_date(ARCBKP,'DD/MM/YYYY HH24:MI'));

SELECT db_name, input_type, status,
to_char((start_time),'dd-mm-yyyy hh24:mi'),
to_char((end_time),'dd-mm-yyyy hh24:mi'),
output_bytes_display
FROM rc_rman_backup_subjob_details
WHERE start_time > sysdate -1 order by db_name;
spool off;
quit;

----------------------------------------------------
set echo off;
REM srdc_rman_performance.sql - collect RMAN performance information
define SRDCNAME='RMAN_PERFORMANCE'
set markup html on spool on

set TERMOUT off;

COLUMN SRDCSPOOLNAME NOPRINT NEW_VALUE SRDCSPOOLNAME
select 'SRDC_'||upper('&&SRDCNAME')||'_'||upper(instance_name)||'_'||
     to_char(sysdate,'YYYYMMDD_HH24MISS') SRDCSPOOLNAME from v$instance;
REM
spool &&SRDCSPOOLNAME..htm
Set heading off;
set feedback off;
select '+----------------------------------------------------+' from dual
union all
select '| Diagnostic-Name: '||'&&SRDCNAME' from dual
union all
select '| Timestamp: '||
to_char(systimestamp,'YYYY-MM-DD HH24:MI:SS TZH:TZM') from dual
union all
select '| Machine: '||host_name from v$instance
union all
select '| Version: '||version from v$instance
union all
select '| DBName: '||name from v$database
union all
select '| Instance: '||instance_name from v$instance
union all
select '+----------------------------------------------------+' from dual
/
set heading on;
set pagesize 50000;
set echo on;
set feedback on;
Column session_id format 999999999 heading "SESS|ID"
Column session_serial# format 99999999 heading "SESS|SER|#"
Column event format a40
Column total_waits format 9,999,999,999 heading "TOTAL|TIME|WAITED|MICRO"
alter session set NLS_DATE_FORMAT='DD-MON-YYYY HH24MISS';

SELECT sid, spid, client_info
FROM v$process p, v$session s
WHERE p.addr = s.paddr
AND client_info LIKE '%id=rman%';
select SID, START_TIME,TOTALWORK, sofar, (sofar/totalwork) * 100 done,
sysdate + TIME_REMAINING/3600/24 end_at
from v$session_longops
where totalwork > sofar
AND opname NOT LIKE '%aggregate%';

Select session_id, session_serial#, Event, sum(time_waited) total_waits
From v$active_session_history
Where sample_time > sysdate - 1
And program like '%rman%'
And session_state='WAITING' And time_waited > 0
Group by session_id, session_serial#, Event
Order by session_id, session_serial#, total_waits desc;

SELECT set_count, device_type, type, filename,
buffer_size, buffer_count, open_time, close_time
FROM v$backup_async_io
where type != 'AGGREGATE'
ORDER BY set_count,type, open_time, close_time;

select device_type "Device", type, filename,
open_time open,
close_time close,
elapsed_time Elapsed, effective_bytes_per_second
from v$backup_async_io
where close_time > sysdate-7
and type != 'AGGREGATE'
order by close_time desc;

SELECT set_count, device_type, type, filename,
buffer_size, buffer_count, open_time, close_time
FROM v$backup_sync_io
ORDER BY set_count,type, open_time, close_time;

select device_type "Device", type, filename,
open_time open,
close_time close,
elapsed_time Elapsed, effective_bytes_per_second
from v$backup_sync_io
where close_time > sysdate-7
and type != 'AGGREGATE'
order by close_time desc;

COL in_sec FORMAT a10
COL out_sec FORMAT a10
COL TIME_TAKEN_DISPLAY FORMAT a10
COL in_size FORMAT a10
COL out_size FORMAT a10
SELECT SESSION_KEY,
OPTIMIZED,
COMPRESSION_RATIO,
INPUT_BYTES_PER_SEC_DISPLAY in_sec,
OUTPUT_BYTES_PER_SEC_DISPLAY out_sec,
TIME_TAKEN_DISPLAY
FROM V$RMAN_BACKUP_JOB_DETAILS
ORDER BY SESSION_KEY;

SELECT SESSION_KEY,
INPUT_TYPE,
COMPRESSION_RATIO,
INPUT_BYTES_DISPLAY in_size,
OUTPUT_BYTES_DISPLAY out_size
FROM V$RMAN_BACKUP_JOB_DETAILS
ORDER BY SESSION_KEY;

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;

set markup html off

spool off

Database size and after refresh- check below

Database size 
col "Database Size" format a20
 col "Free space" format a20
 col "Used space" format a20
 select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"
  , round(sum(used.bytes) / 1024 / 1024 / 1024 ) -
  round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space"
  , round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"
  from (select bytes
  from v$datafile
  union all
  select bytes
  from v$tempfile
  union all
  select bytes
  from v$log) used
  , (select sum(bytes) as p
  from dba_free_space) free
  group by free.p

  /

This script will display a list of Oracle invalid objects:

break on c1 skip 2

set pages 999

col c1 heading 'owner' format a15
col c2 heading 'name' format a40
col c3 heading 'type' format a10

ttitle 'Invalid|Objects'

select 
   owner       c1, 
   object_type c3,
   object_name c2
from 
   dba_objects 
where 
   status != 'VALID'
order by
   owner,
   object_type;

SELECT OBJECT_NAME FROM DBA_OBJECTS WHERE STATUS = 'INVALID';



*/

select
decode(object_type, ‘PACKAGE BODY’, ‘PACKAGE’, object_type) ||
‘ ‘ || owner || ‘.’ ||
object_name
as invalid_objects
from ALL_objects
where status = ‘INVALID’ and object_type in
(‘PROCEDURE’,’FUNCTION’,’PACKAGE’,’PACKAGE BODY’,’TRIGGER’,’VIEW’)
ORDER BY 1
/

————

recompile.sql
/*

Script to generate the SQL commands needed to recompile invalid Oracle objects.
By Rodger Lepinsky

*/

select ‘alter ‘ ||
decode(object_type, ‘PACKAGE BODY’, ‘PACKAGE’, object_type) ||
‘ ‘ || owner || ‘.’ ||
object_name || ‘  compile ‘ ||
decode(object_type, ‘PACKAGE BODY’, ‘BODY’,”)  ||';’
AS  RECOMPILE
from ALL_objects
where status = ‘INVALID’
and object_type in
(‘PROCEDURE’,’FUNCTION’,’PACKAGE’,’PACKAGE BODY’,’TRIGGER’,’VIEW’)
ORDER BY 1
/


Database size

COLUMN "Total Gb" FORMAT 999,999,999.0
COLUMN "Redo Gb" FORMAT 999,999,999.0
COLUMN "Temp Gb" FORMAT 999,999,999.0
COLUMN "Data Gb" FORMAT 999,999,999.0

Prompt
Prompt "Database Size"

select (select sum(bytes/1073741824) from dba_data_files) "Data Gb", 
(select NVL(sum(bytes/1073741824),0) from dba_temp_files) "Temp Gb",
(select sum(bytes/1073741824)*max(members) from v$log) "Redo Gb",
(select sum(bytes/1073741824) from dba_data_files) +
(select NVL(sum(bytes/1073741824),0) from dba_temp_files) +
(select sum(bytes/1073741824)*max(members) from v$log) "Total Gb"
from dual;



-- ***************************************************************** 
drop table SYSTEM.SEM_DASD_GROWTH_temp;
--truncate table SEM_CLONING_DATAFILES;    
-- *****************************************************************  
    set echo      off 
    set heading   off 
    set pagesize  0 
    set LINESIZE 120 
    set feedback  off
set verify off 

-- ***************************************************************** 
create  table SYSTEM.sem_dasd_growth_temp
      (TABLESPACE  VARCHAR2(44),
       FILE_NAME   VARCHAR2(64),            
       CURRENT_SIZE  NUMBER(15),
       FREE_SPACE  NUMBER(15),
       LARGEST_FREE  NUMBER(15));
         
INSERT INTO SYSTEM.SEM_DASD_GROWTH_temp
 SELECT  d.tablespace_name 
  ,d.file_name 
  ,d.current_size 
 ,round(nvl(f.free_space,0),2)
 ,round(nvl(f.largest_free,0),2)
 FROM
 (select tablespace_name
 , file_id
 , sum(f.bytes/(1024*1024)) free_space
 , max(f.bytes/(1024*1024)) largest_free
  from sys.dba_free_space f
  group by tablespace_name, file_id) f
,
(select tablespace_name
, file_id
, file_name
, blocks*(select value from v$parameter
 where name = 'db_block_size')/(1024*1024) current_size
 , maxblocks*(select value from v$parameter
  where name = 'db_block_size')/(1024*1024) maxblocks
  , increment_by*(select value from v$parameter
   where name = 'db_block_size')/(1024*1024) increment_by
   , autoextensible
 from sys.dba_data_files
 group by tablespace_name, file_id, file_name, blocks, maxblocks, 
           increment_by, autoextensible) d
WHERE d.FILE_ID = f.FILE_ID(+);
commit;
-- ***************************************************************** 
/*
drop table SYSTEM.SEM_DASD_GROWTH;
--truncate table SYSTEM.RR_SEM_GROWTH;
--truncate table SEM_CLONING_DATAFILES;    
-- *****************************************************************  

--    column "Ddname"        format a20
--    column "TABLESPACE"    format a40

-- ***************************************************************** 
create  table SYSTEM.RR_SEM_GROWTH
      (TABLESPACE  VARCHAR2(44),
       FILE_NAME   VARCHAR2(64),            
       CURRENT_SIZE  NUMBER(15),
       FREE_SPACE  NUMBER(15),
       LARGEST_FREE  NUMBER(15),
       DDNAME      VARCHAR2(40),
  run_date timestamp(7));
*/
                   
   
-- ***************************************************************** 
insert all
         into system.RR_SEM_GROWTH values(tablespace,substr(file_name,8,64),
 current_size,free_space,largest_free,substr(file_name,20,40),sysdate)
         select *
          from SYSTEM.RR_SEM_GROWTH_temp;
--          order by tablespace;

  commit;

exit       
-- ***************************************************************** 

/*

select file_name from system.RR_SEM_GROWTH;

select * from SYSTEM.RR_SEM_GROWTH_temp;


select * from system.RR_SEM_GROWTH;