В данном посте будут рассмотрены SQL запросы, с помощью которых можно получить объем (размер) archive log файлов, сгенерированных за определенный промежуток времени базой данных (инстанцией) Oracle.

База данных Oracle позволяет сохранять заполненные группы redo logs (журнальных файлов) в один или несколько файлов, которые именуются как archived redo log или archive log (архивные журнальные файлы). Полученные archive log файлы создаются в директории, которая задана в параметре db_recovery_file_dest базы данных. Процесс преобразования redo log файлов в archive log файлы называется архивированием. Процесс архивирования возможен только в том случае, если база данных работает в режиме ARCHIVELOG. Когда база данных работает в режиме ARCHIVELOG процесс записи в redo log файлов (LGWR – LoG WRiter) не может использовать/ перезаписывать redo log файлы, если они еще не заархивированы. Фоновый процесс ARCn автоматизирует операции архивирования, когда режим ARCHIVELOG включен.

Сама информация об объеме/размере генерируемых archive log файлов может быть полезна во многих случаях. Ниже перечислены некоторые из них:

• При планировании дискового пространства для хранения файлов базы данных и операционной системы
• Для анализа сгенерированного объема archive logs за определенный период времени
• Для хранения archive logs для внешних систем, например, для Change Data Capture или GoldenGate
• Для сохранения за определенный период времени для standby инстанции (обеспечение непрерывности работы БД) и rman (утилита для бэкапирования/восстановления БД).

Далее приведены сами SQL запросы. Первый SQL запрос показывает объем ежедневно генерируемых archive log файлов. Выглядит он следующим образом:

SELECT TRUNC(completion_time) Дата, SUM(blocks * block_size)/1024/1024 Размер_в_Мб 
FROM v$archived_log 
GROUP BY TRUNC(completion_time) 
ORDER BY 1

  Дата    Размер_в_Мб
--------- -----------
15-APR-20 16745.6738
16-APR-20 12862.2178
17-APR-20 10879.0049
18-APR-20 3348.20215
19-APR-20 3349.26465
20-APR-20 15216.1572
21-APR-20 12614.5303
22-APR-20 11124.6650
23-APR-20 12318.9580
24-APR-20 12341.1865
25-APR-20 4951.30664
26-APR-20 2475.93945
27-APR-20 13855.7695
28-APR-20 11866.7764
29-APR-20 12592.0986 
30-APR-20 12391.2773  

Для того, чтобы получить информацию об объеме archive log файлов, генерируемых за каждый час, нужно выполнить следующие два SQL запроса:

SQL> ALTER SESSION SET nls_date_format = 'YYYY-MM-DD HH24:MI:SS';
Session altered. 

SELECT completion_time Дата_время, SUM(blocks * block_size)/1024/1024 Размер_в_Мб 
FROM v$archived_log 
GROUP BY completion_time  
ORDER BY completion_time

 Дата_время  Размер_в_Мб
------------ -----------
10-MAY-20 00 778.506841
10-MAY-20 01 968.420928
10-MAY-20 02 974.646486
10-MAY-20 03 353.417969
10-MAY-20 04 378.460941
10-MAY-20 05 470.218753
10-MAY-20 06 760.101568
10-MAY-20 07 775.765634
10-MAY-20 08 1156.40527
10-MAY-20 09 1767.84766
10-MAY-20 10 2026.48828
10-MAY-20 11 1770.52441
10-MAY-20 12 1769.79980
10-MAY-20 13 1772.23145
10-MAY-20 14 3552.05273
10-MAY-20 15 1818.34766
10-MAY-20 16 2541.96484
10-MAY-20 17 1806.23730
10-MAY-20 18 1581.21680
10-MAY-20 20 1768.04785
10-MAY-20 20 1757.55469
10-MAY-20 21 1207.21721
10-MAY-20 22 1227.74210 
10-MAY-20 23 997.412865