The redo log files keep a trace of every data alteration, so that after a crash they can replay the changes. You need at least two, and they deserve careful attention for both backup and access optimisation.
In ARCHIVELOG mode the redo logs are archived โ keeping a full trace of all changes, not just what fits within the redo log file size. The redo buffer is flushed to disk when it is full, so the redo log files should be at least as large as the redo log buffer (log_buffer).
Sizing rules of thumb: a bigger file takes longer to archive, a smaller one archives faster. Aim for ~two archives generated per hour (fewer = less I/O). Spread the files across several disks (ideally a different disk from the database itself) โ if the data disks are corrupted and the redo logs are on them, recovery becomes impossible.
1SELECT groups, current_group#, sequence# FROM v$thread;
2SELECT group#, sequence#, bytes, members, status FROM v$log;
3SELECT * FROM v$logfile;
Redo log status:
UNUSED โ never written.CURRENT โ online, currently being written.ACTIVE โ online, currently being archived.INACTIVE โ online, archived, not in use.Force a switch / checkpoint:
1ALTER SYSTEM SWITCH LOGFILE; -- archive the current group and activate the next.
2ALTER SYSTEM CHECKPOINT; -- archive the current group.
Both force a log switch, but work differently:
ALTER SYSTEM SWITCH LOGFILE | ALTER SYSTEM ARCHIVE LOG CURRENT | |
|---|---|---|
| Control | returns immediately (does not wait for the archiver) | waits for the archiver to finish |
| How | issues a checkpoint, starts the new group, then archives in background | synchronous โ waits for the online redo log to be written |
| RAC | only the local node | all nodes (recommended in RAC) |
| Thread | archives the current thread only | you can specify which thread to archive |
| RMAN | โ | the trusted command inside RMAN backups |
1ALTER DATABASE ADD LOGFILE GROUP 2 '/u01/oradata/orcl/REDO03.LOG' SIZE 10M;
2ALTER DATABASE DROP LOGFILE GROUP 2;
3ALTER DATABASE DROP LOGFILE MEMBER '/u01/oradata/orcl/REDO02.LOG';
Each redo group usually has two members โ one in +DATA and one mirrored in +FRA:
1ALTER DATABASE ADD LOGFILE GROUP 4 ('+DATA/orcl/redo04a.log', '+FRA/orcl/redo04b.log') SIZE 200M;
2
3SELECT * FROM v$logfile;
4-- GROUP# STATUS TYPE MEMBER
5-- 4 ONLINE +DATA/orcl/redo04a.log
6-- 4 ONLINE +FRA/orcl/redo04b.log
Scenario: two groups (1 and 2), two members each.
1ALTER DATABASE ADD LOGFILE GROUP 3 '/u01/oradata/orcl/REDO05.LOG' SIZE 10M;
CURRENT):1ALTER SYSTEM SWITCH LOGFILE;
2ALTER SYSTEM SWITCH LOGFILE;
1ALTER DATABASE DROP LOGFILE MEMBER '/u01/oradata/orcl/REDO01.LOG';
2ALTER DATABASE ADD LOGFILE GROUP 1 '/u01/oradata/orcl/redo/REDO01.LOG' SIZE 10M;
3-- ... repeat per member ...
1ALTER SYSTEM SWITCH LOGFILE; -- (several times)
2ALTER DATABASE DROP LOGFILE GROUP 3;
Activate / deactivate:
1shutdown immediate;
2startup mount;
3alter database archivelog; -- or: alter database noarchivelog
4alter database open;
On a Grid/Clusterware environment:
1srvctl stop database -d MYDB
2srvctl start database -d MYDB -o mount
3sqlplus / as sysdba
4SQL> alter database archivelog;
5srvctl stop database -d MYDB
6srvctl start database -d MYDB
Verify:
1archive log list;
2SELECT name, log_mode FROM v$database;
3SELECT archiver FROM v$instance;
From RMAN:
1rman> connect target
2rman> list archivelog all;
1ALTER SYSTEM SET db_recovery_file_dest = '+FRA' SCOPE=both sid='*';
2ALTER SYSTEM SET db_recovery_file_dest_size = '54G' SCOPE=both sid='*';
3ALTER SYSTEM SET log_archive_dest_1 = 'LOCATION=/orarch/orcl' SCOPE=both sid='*';
1ALTER DATABASE ARCHIVELOG;
2ALTER DATABASE OPEN;
3ALTER SYSTEM SWITCH LOGFILE; -- force a switch to start archiving.
Check the FRA usage:
1COL name FOR A40
2SELECT name, CEIL(space_limit/1024/1024) size_m, CEIL(space_used/1024/1024) used_m,
3 DECODE(NVL(space_used,0), 0, 0, CEIL((space_used/space_limit)*100)) pct_used
4FROM v$recovery_file_dest ORDER BY name;
5
6SELECT * FROM v$recovery_file_dest;
7SELECT * FROM v$flash_recovery_area_usage;
See the ASM diskgroups (DATA / FRA):
1. oraenv +ASM1
2asmcmd lsdg