DB Version: 11.2.0.4
OS : Oracle Linux 6.5
I have a 4-node RAC DB which is 30 Tera Bytes in size. I wanted to determine the daily archive generation for the whole DB (Not for each Instance).
Query1 shown below uses gv$archived_log view and Query2 uses v$archived_log. Which one provides me the daily archive generation for the whole DB and why does the sum of archive logs's size vary between these views ?
BTW v$archived_log view doesn't seem to be instance specific as I can see columns like THREAD# , ARCHIVAL_THREAD# in it (sample output shown below)
---Query1
SELECT TRUNC(next_time) "Date",
ROUND(SUM(blocks*block_size/1024/1024/1024)) gB
FROM gv$archived_log
where next_time > trunc (sysdate - 5)
GROUP BY TRUNC(next_time)
ORDER BY 1 asc ;
Date GB
--------- ----------
09-MAR-17 488
10-MAR-17 553
11-MAR-17 439
12-MAR-17 403
13-MAR-17 455
14-MAR-17 282
6 rows selected.
--Query2
SELECT TRUNC(next_time) "Date",
ROUND(SUM(blocks*block_size/1024/1024/1024)) gB
FROM v$archived_log
where next_time > trunc (sysdate - 5)
GROUP BY TRUNC(next_time)
ORDER BY 1 asc ;
Date GB
--------- ----------
09-MAR-17 122
10-MAR-17 138
11-MAR-17 110
12-MAR-17 101
13-MAR-17 114
14-MAR-17 70
6 rows selected.
--- v$archived_log contains THREAD# , ARCHIVAL_THREAD#
SQL> select THREAD# , ARCHIVAL_THREAD# From v$archived_log vl where rownum < 8;
THREAD# ARCHIVAL_THREAD#
---------- ----------------
3 3
3 3
2 2
1 1
3 3
2 2
1 1
7 rows selected.