column db_unique_name format a15
column version format a12
select db_unique_name, substr(value,instr(value,'FIL_SV'),11) version, b.latest from rc_rman_configuration a,
(select db_key, max(completion_time) latest from rc_backup_piece group by db_key) b
  where a.db_key=b.db_key(+) and name = 'CHANNEL' order by 1


export ORAENV_ASK=NO
nohup cat /etc/oratab | grep -v ^# | grep -v ^$ | grep -v "+ASM" | grep -v agent | grep -v client | grep -v _DG | while read line
do
  export ORACLE_SID=`echo $line | awk -F: '{print $1}'`
  . oraenv
rman target / catalog <CATALOG> <<EOF
register database;
allocate channel for maintenance device type 'SBT_TAPE' parms "SBT_LIBRARY=oracle.disksbt, ENV=(BACKUP_DIR=/tmp)";
allocate channel for maintenance device type DISK;
delete force noprompt backup completed before 'sysdate-99';
list backup summary;
EOF
done &

crosscheck backup completed before 'sysdate-92';

select a.name, b.maxtime, cnt, 'exec DBMS_RCVCAT.unregisterdatabase('||a.db_key||','||a.dbid||');' sql from rc_database a,
(select db_key, max(completion_time) maxtime, count(1) cnt from rc_backup_piece group by db_key) b, rc_database c
where a.db_key = b.db_key and a.name = c.name and a.db_key <> c.db_key order by cnt, maxtime desc

select a.name, b.mintime, b.maxtime, cnt, 'exec DBMS_RCVCAT.unregisterdatabase('||a.db_key||','||a.dbid||');' sql from rc_database a,
(select db_key, min(completion_time) mintime, max(completion_time) maxtime, count(1) cnt from rc_backup_piece group by db_key) b
where a.db_key = b.db_key and (mintime<sysdate-40 and maxtime<sysdate-40) order by cnt, maxtime desc


select db.name, bp.db_key, count(1), max(completion_time) from rc_backup_piece bp , rc_database db
  where bp.db_key=db.db_key and (tag like 'TAG201___2%' or tag like 'TAG201___1%' or tag like 'TAG201___3%') and completion_time < sysdate-99
group by db.name, bp.db_key order by 3

column db_unique_name format a18
column version format a15
select db_unique_name, substr(value,instr(value,'FIL_SV'),11) version, b.latest from rc_rman_configuration a,
(select db_key, max(completion_time) latest from rc_backup_piece group by db_key) b
  where a.db_key=b.db_key(+) and name = 'CHANNEL' order by 1


