LLI Wiki — My Digital Knowledge Base

Oracle: archive logs, Fast Recovery Area and RMAN cleanup

Cheat sheet to check ARCHIVELOG mode, measure archived log generation, manage the FRA and free space with RMAN.

This article is a cheat sheet for Oracle archived redo logs: checking the archiving mode, locating and sizing the archive logs, managing the Fast Recovery Area (FRA), and cleaning up with RMAN when a disk fills up.

Archived logs versus the Fast Recovery Area

Check the archiving configuration

ARCHIVE LOG LIST

select dest_name, status, destination from v$archive_dest;
select destination, status from v$archive_dest where status = 'VALID';
show parameter log_archive_format;
show parameter log_archive_dest;
show parameter db_recovery_file_dest;

Example of output for the FRA parameters:

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u1/app/oracle/fast_recovery_a
                                                 rea
db_recovery_file_dest_size           big integer 20G

Check the log mode:

SELECT name, log_mode FROM v$database;
LOG_MODE
------------
NOARCHIVELOG

Or:

SQL> ARCHIVE LOG LIST
Database log mode Archive Mode
Automatic archival Enabled
Archive destination +FLASH
Oldest online log sequence 21160
Next log sequence to archive 21165
Current log sequence 2

Useful views

View Description
V$DATABASE Shows whether the database is in ARCHIVELOG or NOARCHIVELOG mode, and whether manual archiving is specified.
V$ARCHIVED_LOG Historical archived log information from the control file. With a recovery catalog, RC_ARCHIVED_LOG contains similar information.
V$ARCHIVE_DEST Describes the current instance, all archive destinations, and their value, mode and status.
V$ARCHIVE_PROCESSES State of the archive processes of an instance.
V$BACKUP_REDOLOG Information about backups of archived logs. With a recovery catalog, see RC_BACKUP_REDOLOG.
V$LOG All redo log groups and which of them need to be archived.
V$LOG_HISTORY Log history, including which logs were archived and the SCN range of each archived log.

Enable or disable ARCHIVELOG mode

Single instance, enable:

SQL> Shutdown immediate
SQL> Startup mount
SQL> Alter database archivelog;
SQL> Alter database open;

Single instance, disable:

SQL> Shutdown immediate
SQL> Startup mount
SQL> Alter database noarchivelog;
SQL> Alter database open;

Verify with ARCHIVE LOG LIST.

RAC, enable (TEST and RAC are database names used in the original commands):

srvctl stop database -d TEST
srvctl start database -d RAC -o mount
sqlplus "/ as sysdba"
alter database archivelog;
srvctl stop database -d TEST
srvctl start database -d TEST

Manage the Fast Recovery Area

Enable the FRA (the disk group name is an example):

show parameter DB_RECOVERY_FILE_DEST;
show parameter DB_RECOVERY_FILE_DEST_SIZE;
show parameter log_archive_dest_1;
ALTER SYSTEM SET db_recovery_file_dest='+ORA_DVP_FRA' scope=both sid='*';
ALTER SYSTEM SET db_recovery_file_dest_size=10G scope=both sid='*';

Disable it:

ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '';
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '' scope=both sid='*';

Check its status:

SELECT * FROM V$RECOVERY_FILE_DEST;
SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE;
NAME                                     SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
---------------------------------------- ----------- ---------- ----------------- ---------------
/apps/oracle/oradata/FRA                  2.1475E+11 9.3252E+10                 0              20

FILE_TYPE            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ---------------
CONTROL FILE                          0                         0               0
REDO LOG                              0                         0               0
ARCHIVED LOG                          0                         0               0
BACKUP PIECE                      43.42                         0              20
IMAGE COPY                            0                         0               0
FLASHBACK LOG                         0                         0               0
FOREIGN ARCHIVED LOG                  0                         0               0

Increase the FRA size:

select count(*), sum(blocks*block_size) from v$archived_log where backup_count=0 and deleted='NO';
ALTER SYSTEM SET db_recovery_file_dest_size = 20G SCOPE=BOTH SID='*';
SHOW PARAMETER RECOVERY

Flashback Database

Flashback quickly reverts the whole database to a past point in time. A single table can also be brought back in time.

-- status
select flashback_on from v$database;

-- enable / disable
ALTER DATABASE FLASHBACK ON;
ALTER DATABASE FLASHBACK OFF;

Prerequisites: ARCHIVELOG mode and a configured FRA.

select log_mode from v$database;
show parameter db_flashback_retention_target

Analyse archived log generation

Archived logs of the last 7 days, with start and end times:

set linesize 300
set pagesize 1000
COLUMN sequence# format 9999999
COLUMN name format a100
COLUMN creator format a15
select sequence#, name, creator, to_char(first_time,'dd-mm-yyyy hh24:mi:ss'), to_char(completion_time,'dd-mm-yyyy hh24:mi:ss')
from v$archived_log
where first_time > sysdate - 7
order by 1;

Size per day:

select trunc(COMPLETION_TIME) TIME, SUM(BLOCKS * BLOCK_SIZE)/1024/1024/1024 SIZE_GB
from V$ARCHIVED_LOG
group by trunc(COMPLETION_TIME)
order by 1;

Size per day and per thread (instance):

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

Size per hour:

alter session set nls_date_format = 'YYYY-MM-DD HH24';
SELECT TRUNC(COMPLETION_TIME, 'HH') ARCHIVED_DATE_HOUR,
    to_char(COMPLETION_TIME, 'YYYYMMDD') DAY,
    to_char(COMPLETION_TIME,'hh24') HOUR,
    round(SUM(BLOCKS * BLOCK_SIZE)/1024 /1024,0) SIZE_IN_MB
FROM V$ARCHIVED_LOG
GROUP BY TRUNC(COMPLETION_TIME, 'HH'),to_char(COMPLETION_TIME, 'YYYYMMDD'),to_char(COMPLETION_TIME,'hh24')
ORDER BY 1,2;

Average number and size of logs per working day over the last 7 days (the divisor 5 assumes five working days):

SELECT TRUNC(AVG(cnt/5)) NumberOfLogs, ROUND(AVG(GByte/5), 2) AverageLogSizeGByte
FROM (SELECT COUNT(*) Cnt, ROUND(SUM(blocks*block_size)/(1024*1024*1024), 2) GByte
FROM v$archived_log
WHERE completion_time BETWEEN SYSDATE-7 AND SYSDATE
AND to_char(completion_time, 'DY') NOT IN ('SAT', 'SUN') -- Working Days
);

History per day, with the average daily volume derived from the redo log size:

SELECT A.*,
    Round(A.Count#*B.AVG#/1024/1024) Daily_Avg_Mb
FROM
(
    SELECT
        To_Char(First_Time,'YYYY-MM-DD') DAY,
        Count(1) Count#,
        Min(RECID) Min#,
        Max(RECID) Max#
    FROM
        v$log_history
    GROUP BY To_Char(First_Time,'YYYY-MM-DD')
    ORDER BY 1 DESC
) A,
( SELECT
        Avg(BYTES) AVG#,
        Count(1) Count#,
        Max(BYTES) Max_Bytes,
        Min(BYTES) Min_Bytes
    FROM
        v$log
) B;

Find the sequence number of a log, then the log that contains a given SCN:

select sequence#, name, creator, to_char(first_time,'dd-mm-yyyy hh24:mi:ss'), first_change#, next_change#
from v$archived_log
where first_time > sysdate - 7
order by 1;

select sequence#, name, creator, to_char(first_time,'dd-mm-yyyy hh24:mi:ss'), first_change#, next_change#
from v$archived_log
where <SCN number> between first_change# and next_change#;

Online redo log groups and members:

select group#, sequence#, bytes, members, status from v$log;
select * from v$logfile;

Find the sessions generating the most redo

select s.inst_id, s.sid, serial#, program, module, username, value redo_size
from gv$session s, gv$sesstat ss, v$statname sn
where s.sid = ss.sid
and ss.statistic# = sn.statistic#
and sn.name = 'redo size'
and s.inst_id = ss.inst_id
order by redo_size desc;

Alternative based on block changes:

set linesize 400
SELECT s.sid, s.serial#, s.username, s.program, si.block_changes
FROM gv$session s, gv$sess_io si
WHERE s.sid = si.sid
ORDER BY 5 desc;

To see which tables and operations generated the redo (requires a LogMiner session):

select TABLE_NAME, OPERATION, count(1)
from V$LOGMNR_CONTENTS
group by TABLE_NAME, OPERATION
order by TABLE_NAME, OPERATION;

Change the archived log configuration

Force a log switch:

ALTER SYSTEM switch logfile;

Change the file name format (static parameter, effective after restart):

ALTER SYSTEM SET log_archive_format='arch_%t_%s_%r.arc' SCOPE=spfile;
-- or, on all instances
alter system set log_archive_format='%d_%t_%s_%r.dbf' scope=spfile sid='*';

Using .arc rather than .dbf for archived logs avoids confusing them with datafiles.

Move the archived logs to a new location (the paths are examples). Move the existing files with RMAN, then change the destinations:

-- RMAN
backup as copy archivelog all format '/u3/oracle/fast_recovery_area/%U' delete input;

-- SQL*Plus
ALTER SYSTEM SET db_recovery_file_dest='/u3/oracle/fast_recovery_area/ORCL/archivelog' SCOPE=spfile;
ALTER SYSTEM SET log_archive_dest_1='LOCATION=/u3/oracle/fast_recovery_area/ORCL/archivelog';
alter system switch logfile;

Note that LOG_ARCHIVE_DEST and LOG_ARCHIVE_DEST_n cannot be set at the same time as DB_RECOVERY_FILE_DEST: they are mutually exclusive.

Clean up archived logs with RMAN

Set a readable date format first:

export NLS_DATE_FORMAT="DD-MM-YYYY HH24:MI:SS"

List archived logs:

list archivelog all;
list copy of archivelog until time 'SYSDATE-1';
list copy of archivelog from time 'SYSDATE-1';
list copy of archivelog from time 'SYSDATE-1' until time 'SYSDATE-2';
list copy of archivelog from sequence 1000;
list copy of archivelog until sequence 1500;
list copy of archivelog from sequence 1000 until sequence 1500;

Delete archived logs:

crosscheck archivelog all;
delete expired archivelog all;
delete archivelog all;
report obsolete;
delete obsolete;
delete archivelog until time 'SYSDATE-1';
delete archivelog from time 'SYSDATE-1';
delete archivelog from time 'SYSDATE-1' until time 'SYSDATE-2';
delete archivelog from sequence 1000;
delete archivelog until sequence 1500;
delete archivelog from sequence 1000 until sequence 1500;

Backups and retention:

crosscheck backup;
delete noprompt expired backup;
delete obsolete device type disk;
delete obsolete device type sbt;
SHOW RETENTION POLICY;
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 2 DAYS;

If deleting files at the operating system level, the control file must then be synchronised with RMAN (crosscheck archivelog all then delete expired archivelog all), otherwise the space is not shown as released.

Compare the RMAN catalogue with the files on disk

Generate the list of archived logs known to RMAN, then compare it with the files found on disk. comm -1 prints the lines that are not only in the first file; review the result before deleting anything.

echo "list archivelog all;" | NLS_DATE_FORMAT='yyyymmddhh24miss' rman target / | awk '/Name:/{gsub ("Name:", "", $0); gsub(" ","",$0); print $0}' > rman.lst
find /u3/oracle/oradata/flash_recovery_area/ | sort > dbf.lst
comm -1 rman.lst dbf.lst
comm -1 rman.lst dbf.lst | xargs -n1 stat --format "%n %y"
comm -1 rman.lst dbf.lst | xargs -n1 rm -f

Archive log destination full

  1. Check the usage and the parameters:

    show parameter log_archive_dest;
    archive log list;
    select * from V$FLASH_RECOVERY_AREA_USAGE;
    show parameter recover;
    
  2. Free space with RMAN:

    RMAN> crosscheck archivelog all;
    RMAN> delete expired archivelog all;
    RMAN> delete archivelog until time 'sysdate-1';
    
  3. Check again with V$FLASH_RECOVERY_AREA_USAGE, and if needed increase the size:

    alter system set db_recovery_file_dest_size=4G scope=both;
    

The database does not open

If the database cannot open because the archive area is full, start it in MOUNT mode and clean up from RMAN:

rman
RMAN> connect target /
RMAN> startup mount
RMAN> catalog recovery area;
RMAN> delete archivelog all;
RMAN> alter database open;

To prevent this, schedule a regular database backup plus an archived log backup that deletes the archived logs. If backups and archived logs are not needed, disable archiving while in MOUNT state:

alter database noarchivelog;

Delete RMAN backups

list backup summary
list backup
DELETE BACKUP;
DELETE NOPROMPT BACKUP;
DELETE OBSOLETE;
CROSSCHECK BACKUP;
DELETE BACKUP DEVICE TYPE SBT;
DELETE BACKUP COMPLETED BEFORE 'sysdate-2' DEVICE TYPE DISK;
LIST BACKUP BEFORE 'sysdate-2' DEVICE TYPE DISK;

RMAN-06091: no channel allocated for maintenance

RMAN-06091 means a command that needs a maintenance channel (deleting backup pieces, CROSSCHECK, DELETE EXPIRED) was run without a channel of the appropriate type. The note links this error to a bug introduced by the July 2018 patch sets and fixed in 19.1. The workaround is to allocate a maintenance channel explicitly.

Tape backups:

RMAN> allocate channel for maintenance device type 'sbt_tape' PARMS 'ENV=(NSR_SERVER=, NSR_CLIENT=)';
RMAN> delete obsolete;
RMAN> crosscheck archivelog all;
RMAN> delete archivelog all backed up 2 times to sbt_tape;
RMAN> crosscheck archivelog all;

Disk backups:

RMAN> allocate channel for maintenance device type disk;
RMAN> delete obsolete device type disk;
RMAN> crosscheck archivelog all;
RMAN> delete archivelog all backed up 2 times to disk;
RMAN> crosscheck archivelog all;

Remove old SBT_TAPE backups when the media manager is gone

When the media management library is no longer installed, DELETE BACKUP fails:

RMAN-03002: failure of delete command
ORA-19554: error allocating device, device type: SBT_TAPE, device name:
ORA-27211: Failed to load Media Management Library

Allocate a maintenance channel with the dummy disksbt library, then force the deletion:

RMAN> allocate channel for maintenance device type sbt parms 'SBT_LIBRARY=oracle.disksbt, ENV=(BACKUP_DIR=/tmp)';
RMAN> delete force obsolete device type sbt;
RMAN> delete backup device type sbt;

Restore a pluggable database

run {
ALTER PLUGGABLE DATABASE pdb1, pdb2 CLOSE;
RESTORE PLUGGABLE DATABASE pdb1, pdb2;
RECOVER PLUGGABLE DATABASE pdb1, pdb2;
ALTER PLUGGABLE DATABASE pdb1, pdb2 OPEN;
}

Notes

Mastodon LLIMastodon SocialFosstodon