Query archive log:

Query archive log:

SELECT TRUNC(COMPLETION_TIME) ARCHIVED_DATE,
SUM(BLOCKS * BLOCK_SIZE) / 1024 / 1024 SIZE_IN_MB
FROM V$ARCHIVED_LOG
GROUP BY TRUNC(COMPLETION_TIME)
ORDER BY 1;

ARCHIVED_ SIZE_IN_MB
——— ———-
06-NOV-20 3423.69922
07-NOV-20 747.664063
08-DEC-20 25235.2773

alter session set nls_date_format = ‘YYYY-MM-DD HH24’;
SELECT TRUNC(COMPLETION_TIME, ‘HH’) ARCHIVED_DATE_HOUR,
SUM(BLOCKS * BLOCK_SIZE) / 1024 / 1024 SIZE_IN_MB
FROM V$ARCHIVED_LOG
GROUP BY TRUNC(COMPLETION_TIME, ‘HH’)
ORDER BY 1;
ARCHIVED_DATE SIZE_IN_MB
————- ———-
2016-11-06 14 549.886719
2016-11-06 15 2504.91797
2016-11-06 17 42.671875
2016-11-06 18 42.6679688
2016-11-06 20 42.671875

col member format a50
select GROUP#,TYPE,MEMBER from v$logfile;
select GROUP#,TYPE,MEMBER from v$logfile;SQL>

GROUP# TYPE MEMBER
———- ——- ————————————————–
1 ONLINE +REDO/CDB01/ONLINELOG/group_1.257.1055595795
1 ONLINE +DATA/CDB01/ONLINELOG/group_1.258.1055595797
2 ONLINE +REDO/CDB01/ONLINELOG/group_2.258.1055595799
2 ONLINE +DATA/CDB01/ONLINELOG/group_2.259.1055595799

Select GROUP#,THREAD#,SEQUENCE#,bytes/1024/1024,MEMBERS,STATUS from v$log;
GROUP# THREAD# SEQUENCE# BYTES/1024/1024 MEMBERS STATUS
———- ———- ———- ————— ———- —————-
10 1 43511 1024 2 INACTIVE
20 1 43513 1024 2 INACTIVE
30 1 43514 1024 2 CURRENT

column REDOLOG_FILE_NAME format a50
SELECT
a.GROUP#,
a.THREAD#,
a.SEQUENCE#,
a.ARCHIVED,
a.STATUS,
b.MEMBER AS REDOLOG_FILE_NAME,
(a.BYTES/1024/1024) AS SIZE_MB
FROM v$log a
JOIN v$logfile b ON a.Group#=b.Group#
ORDER BY a.GROUP#;
GROUP# THREAD# SEQUENCE# ARC STATUS REDOLOG_FILE_NAME SIZE_MB
———- ———- ———- — —————- ————————————————– ———-
10 1 43511 YES INACTIVE +REDODG/CDB01/ONLINELOG/group_10.260.987184743 1024
10 1 43511 YES INACTIVE +DATADG/CDB01/ONLINELOG/group_10.274.987184739 1024

Leave a Reply

Your email address will not be published. Required fields are marked *