Command to Check Archivelog Size
2 min readOracle databases maintain a detailed history of all changes made to the database through archived redo logs, commonly known as archivelogs. These archivelogs are crucial for data recovery and are often managed by database administrators. Monitoring the size of archivelogs is essential to ensure optimal database performance and sufficient storage capacity.
Here are some ways to check the archivelog size in oracle:
1. Using SQL*PLUS (view v$archived_log)
——————————————
The v$archived_log dynamic performance view provides information about all archived logs. You can use this view to get the size of each archivelog.
SELECT
THREAD#,
SEQUENCE#,
FIRST_TIME,
COMPLETION_TIME,
BLOCKS,
BLOCK_SIZE,
BLOCKS * BLOCK_SIZE / 1024 / 1024 AS SIZE_MB
FROM
V$ARCHIVED_LOG
ORDER BY
COMPLETION_TIME DESC;
SELECT
TO_CHAR(COMPLETION_TIME, 'YYYY-MM-DD') AS ARCHIVELOG_DATE,
SUM(BLOCKS * BLOCK_SIZE) / 1024 / 1024 AS SIZE_MB
FROM
V$ARCHIVED_LOG
GROUP BY
TO_CHAR(COMPLETION_TIME, 'YYYY-MM-DD')
ORDER BY
ARCHIVELOG_DATE DESC;
SELECT
THREAD#,
SEQUENCE#,
FIRST_TIME,
COMPLETION_TIME,
BLOCKS,
BLOCK_SIZE,
BLOCKS * BLOCK_SIZE / 1024 / 1024 AS SIZE_MB
FROM
V$ARCHIVED_LOG
WHERE
COMPLETION_TIME >= SYSDATE - 1
ORDER BY
COMPLETION_TIME DESC;
2. Using a Shell Script (Automation)
————————————
For automated monitoring, shell scripting can be used that queries the database and reports the size of archivelogs.
Save this script as check_archivelog_size.sh, make it executable, and run it to get the archivelog sizes.
#!/bin/bash
# Set Oracle environment variables
export ORACLE_SID=<your_sid>
export ORACLE_HOME=/path/to/oracle_home
export PATH=$ORACLE_HOME/bin:$PATH
# Connect to the database and run the SQL query
sqlplus -s / as sysdba <<EOF
SET PAGESIZE 100
SET LINESIZE 200
COLUMN SIZE_MB FORMAT 999.99
SELECT
THREAD#,
SEQUENCE#,
NAME,
BLOCKS * BLOCK_SIZE / (1024 * 1024) AS SIZE_MB
FROM
V$ARCHIVED_LOG
ORDER BY
SEQUENCE#;
EXIT;
EOF
To schedule this script use CRONTAB
Checkout the following link to get to use & know about CRONTAB:
https://asrblogger.com/crontab/
3. Using Oracle Enterprise Manager (OEM)
———————————————-
Oracle Enterprise Manager (OEM) provides a convenient way to monitor archivelog sizes:
a) Log in to Oracle Enterprise Manager:
Access OEM through your web browser and log in with your credentials.
b) Navigate to the Database:
Select the database instance you want to monitor.
c) Access the Archivelog Section:
Under the “Availability” tab, go to “Recovery” and then click on “Archived Log”.
d) View Archivelog Details:
OEM will display detailed information about archived logs, including their sizes.
*************************************************************************************
