There are 15+ databases on host.
Here’s a nice and easy method to run any SQL for all databases.
[oracle@xxxxxxxxxx dinh]$ for db in $(ps -ef|grep [p]mon|egrep -v 'ASM|APX|MGMTDB'|cut -d_ -f3|sort); do echo 'Database check' $db; . oraenv <<< $db; sqlplus -S / as sysdba @backup_review.sql; done;
Database check DEBUGX1
ORACLE_SID = [xxxxxxxxxx] ? The Oracle base remains unchanged with value /u01/app/oracle
Session altered.
NAME DB_UNIQUE_NAME DATABASE_ROLE
--------- ------------------------------ ----------------
DEBUGX DEBUGX PRIMARY
DATAFILE_CT
-----------
66
START_DATE STATUS MINUTES INPUT_TYPE OUTPUT_TYPE INPUT_TYPE_CT OUTPUT_TYPE_CT INPUT_MB OUTPUT_MB
----------- ------------------------- ---------- ------------- ------------ ------------- -------------- ------------ ------------
2021-DEC-20 COMPLETED 9 ARCHIVELOG DISK 22 22 902 691
2021-DEC-20 COMPLETED 1 DB INCR DISK 1 1 17,563 41
2021-DEC-21 COMPLETED 8 ARCHIVELOG DISK 23 23 970 730
2021-DEC-21 COMPLETED 1 DB INCR DISK 1 1 17,527 36
2021-DEC-22 COMPLETED 8 ARCHIVELOG DISK 23 23 977 732
2021-DEC-22 COMPLETED 1 DB INCR DISK 1 1 17,537 38
2021-DEC-23 COMPLETED 8 ARCHIVELOG DISK 23 23 934 719
2021-DEC-23 COMPLETED 1 DB INCR DISK 1 1 17,523 38
2021-DEC-24 COMPLETED 8 ARCHIVELOG DISK 23 23 937 720
2021-DEC-24 COMPLETED 1 DB INCR DISK 1 1 17,507 36
2021-DEC-25 COMPLETED 8 ARCHIVELOG DISK 23 23 1,024 746
2021-DEC-25 COMPLETED 1 DB INCR DISK 1 1 17,507 36
2021-DEC-26 COMPLETED 9 ARCHIVELOG DISK 23 23 1,040 750
2021-DEC-26 COMPLETED 9 DB INCR DISK 1 1 20,617 3,515
2021-DEC-27 COMPLETED 9 ARCHIVELOG DISK 22 22 906 692
2021-DEC-27 COMPLETED 2 DB INCR DISK 1 1 17,528 39
2021-DEC-28 COMPLETED 8 ARCHIVELOG DISK 23 23 967 729
2021-DEC-28 COMPLETED 1 DB INCR DISK 1 1 17,544 39
2021-DEC-29 COMPLETED 8 ARCHIVELOG DISK 23 23 988 736
2021-DEC-29 COMPLETED 1 DB INCR DISK 1 1 17,546 38
2021-DEC-30 COMPLETED 8 ARCHIVELOG DISK 23 23 921 716
2021-DEC-30 COMPLETED 1 DB INCR DISK 1 1 17,578 48
2021-DEC-31 COMPLETED 8 ARCHIVELOG DISK 23 23 945 723
2021-DEC-31 COMPLETED 1 DB INCR DISK 1 1 17,517 36
2022-JAN-01 COMPLETED 8 ARCHIVELOG DISK 23 23 1,016 744
2022-JAN-01 COMPLETED 1 DB INCR DISK 1 1 17,530 38
2022-JAN-02 COMPLETED 9 ARCHIVELOG DISK 23 23 1,009 742
2022-JAN-02 COMPLETED 9 DB INCR DISK 1 1 20,627 3,514
2022-JAN-03 COMPLETED 8 ARCHIVELOG DISK 22 22 903 691
2022-JAN-03 COMPLETED 1 DB INCR DISK 1 1 17,567 42
2022-JAN-04 COMPLETED 8 ARCHIVELOG DISK 23 23 958 727
2022-JAN-04 COMPLETED 1 DB INCR DISK 1 1 17,530 36
2022-JAN-05 COMPLETED 5 ARCHIVELOG DISK 14 14 511 421
2022-JAN-05 COMPLETED 1 DB INCR DISK 1 1 17,511 37
------------ ------------
sum 320,167 19,616
34 rows selected.
Database check RHPNEWDB1
ORACLE_SID = [XXXXXXXX] ? ORACLE_BASE environment variable is not being set since this
information is not available for the current user ID oracle.
You can set ORACLE_BASE manually if it is required.
Resetting ORACLE_BASE to its previous value or ORACLE_HOME
The Oracle base remains unchanged with value /u01/app/oracle
Session altered.
NAME DB_UNIQUE_NAME DATABASE_ROLE
--------- ------------------------------ ----------------
RHPNEWDB RHPNEWDB_XXXXXXX PRIMARY
DATAFILE_CT
-----------
33
START_DATE STATUS MINUTES INPUT_TYPE OUTPUT_TYPE INPUT_TYPE_CT OUTPUT_TYPE_CT INPUT_MB OUTPUT_MB
----------- ------------------------- ---------- ------------- ------------ ------------- -------------- ------------ ------------
2021-DEC-26 COMPLETED WITH WARNINGS 17 ARCHIVELOG DISK 5 5 18,692 6,516
2021-DEC-26 COMPLETED WITH WARNINGS 414 DB INCR DISK 1 1 1,183,591 182,525
2021-DEC-27 COMPLETED WITH WARNINGS 59 ARCHIVELOG DISK 17 17 67,941 23,384
2021-DEC-27 COMPLETED WITH WARNINGS 8 DB INCR DISK 1 1 18,048 3,537
2021-DEC-28 COMPLETED WITH WARNINGS 62 ARCHIVELOG DISK 22 22 68,189 23,623
2021-DEC-28 COMPLETED WITH WARNINGS 9 DB INCR DISK 1 1 19,403 3,826
2021-DEC-29 COMPLETED WITH WARNINGS 65 ARCHIVELOG DISK 22 22 68,835 23,807
2021-DEC-29 COMPLETED WITH WARNINGS 9 DB INCR DISK 1 1 19,801 3,934
2021-DEC-30 COMPLETED WITH WARNINGS 61 ARCHIVELOG DISK 22 22 68,416 23,680
2021-DEC-30 COMPLETED WITH WARNINGS 11 DB INCR DISK 1 1 22,870 4,739
2021-DEC-31 COMPLETED WITH WARNINGS 58 ARCHIVELOG DISK 22 22 64,633 22,392
2021-DEC-31 COMPLETED WITH WARNINGS 10 DB INCR DISK 1 1 20,545 4,125
2022-JAN-01 COMPLETED WITH WARNINGS 57 ARCHIVELOG DISK 22 22 61,385 21,477
2022-JAN-01 COMPLETED WITH WARNINGS 9 DB INCR DISK 1 1 17,463 3,689
2022-JAN-02 COMPLETED WITH WARNINGS 52 ARCHIVELOG DISK 21 21 55,319 19,407
2022-JAN-02 COMPLETED WITH WARNINGS 419 DB INCR DISK 1 1 1,193,071 185,202
2022-JAN-03 COMPLETED WITH WARNINGS 62 ARCHIVELOG DISK 17 17 68,181 23,433
2022-JAN-03 COMPLETED WITH WARNINGS 12 DB INCR DISK 1 1 23,149 5,028
2022-JAN-04 COMPLETED WITH WARNINGS 62 ARCHIVELOG DISK 22 22 68,714 23,797
2022-JAN-04 COMPLETED WITH WARNINGS 8 DB INCR DISK 1 1 18,222 3,418
2022-JAN-05 COMPLETED WITH WARNINGS 38 ARCHIVELOG DISK 16 16 41,959 14,496
------------ ------------
sum 3,188,427 626,035
21 rows selected.
[oracle@xxxxxxxxxx dinh]$
----- COMPLETED WITH WARNINGS
Check RMAN log file to find
RMAN-08120: warning: archived log not deleted, not yet applied by standby
for db in $(ps -ef|grep [p]mon|egrep -v 'ASM|APX|MGMTDB'|cut -d_ -f3|sort); do echo 'Database check' $db; . oraenv <<< $db; sqlplus -S / as sysdba @backup_review.sql; done;
--- backup_review.sql
col input_mb format 999,999,999
col output_mb format 999,999,999
col output_type for a12
col status for a25
set lines 200 pages 5000
break on report
compute SUM of input_mb on report
compute SUM of output_mb on report
break on report
alter session set nls_date_format = 'YYYY-MON-DD';
set echo on
select name, db_unique_name, database_role from v$database;
select count(*) datafile_ct from v$datafile;
select
TRUNC(start_time) start_date,
status,
round(sum(elapsed_seconds)/60) minutes,
input_type,
output_device_type output_type,
count(input_type) input_type_ct,
count(output_device_type) output_type_ct,
round(sum(input_bytes)/1024/1024) input_mb,
round(sum(output_bytes)/1024/1024) output_mb
from V$RMAN_BACKUP_JOB_DETAILS
where TRUNC(start_time)>=TRUNC(sysdate)-16
group by TRUNC(start_time), input_type, output_device_type, status
order by 1 asc, input_type asc, 2
;
/* "OUTPUT_DEVICE_TYPE (*) indicates more than one device" */
exit
Thinking Out Loud 