Oracle Archive Log Mode

Oracle Archive Log Mode Version 11.2.0.3

General Information

Archive logging is essential for production databases where the loss of a transaction might be fatal. It is generally considered unnecessary in development and test environments.

Data Dictionary Objects

V$DATABASE

V$PARAMETER

Initialization Parameters

Configure for multiple archiver processes

log_archive_max_processes=<integer>;

conn / as sysdba

SELECT value
FROM gv$parameter
WHERE name = 'log_archive_max_processes';

ALTER SYSTEM SET log_archive_max_processes=3;

SELECT value
FROM gv$parameter
WHERE name = 'log_archive_max_processes';

Startup The Database In Archivelog Mode

Steps Required To Take A Database Not In Archive Log Mode And Alter It To Archive Log Mode

SELECT log_mode
FROM v$database;

SHUTDOWN;

STARTUP MOUNT EXCLUSIVE;

ALTER DATABASE ARCHIVELOG;

ALTER DATABASE OPEN;

SELECT log_mode
FROM v$database;

Startup The Database In NoArchivelog Mode

Steps Required To Take A Database In Archive Log Mode And Alter It To No Archive Log Mode

SELECT log_mode
FROM v$database;

SHUTDOWN;

STARTUP MOUNT EXCLUSIVE;

ALTER DATABASE NOARCHIVELOG;

ALTER DATABASE OPEN;

SELECT log_mode
FROM v$database;

Disable Log Archiving

Stop log file archiving

The following is undocumented and unsupported and should be used only with great care and following through tests. One might consider this for loading a data warehouse. Be sure to restart logging as soon as the load is complete or the system will be at extremely high risk.

The rest of the database remains unchanged. The buffer cache works in exactly the same way, old buffers get overwritten, old dirty buffers get written to disk. It's just the process of physically flushing the redo buffer that gets disabled.

I used it in a very large test environment where I wanted to perform a massive amount of changes (a process to convert blobs to clobs actually) and it was going to take days to complete. By disabling logging, I completed the
task in hours and if anything untoward were to have happened, I was quite happy to restore the test database back from backup.

~ the above paraphrased from a private email from Richard Foote.

conn / as sysdba

SHUTDOWN;

STARTUP MOUNT EXCLUSIVE;

ALTER DATABASE NOARCHIVELOG;

ALTER DATABASE OPEN;

ALTER SYSTEM SET "_disable_logging"=TRUE;

Restart After Archiving Logging Failure

Archive Logging Restart

SHUTDOWN;

STARTUP;

ARCHIVE LOG START;

ARCHIVE LOG ALL;

Archive Log Related Commands

Start Archive Logging

archive log start;

Stop Archive Logging

-- must be in archivelog mode
ALTER SYSTEM archive log stop;

Force archiving of all log files

-- must be in archivelog mode
ALTER SYSTEM archive log all;

Force archiving of the current log file

-- must be in archivelog mode
ALTER SYSTEM archive log current;

Shell Scripts

Move Archive Logs

export ARCH_DIR="/tmp/rim"
NEW_DIR ="/tmp/rim/new_dir"
export FILE_EXT="arc"
export MOVELIST="/tmp/move.list"
export CALF="/tmp/calc.tmp"
export TMPF="/tmp/workfile.tmp"
CMD="ls -ltr $ARCH_DIR/*.$FILE_EXT | awk {'print $9'} | sort -r > $TMPF"
export FILE_COUNT="

echo "Number of files foundis $FILE_COUNT"
cat $TMPF
echo $FILE_COUNT - 1" > $CALF
echo "quit" >> $CALF
MOVE ="/usr/bin/bc/ $CALF"
echo "Number of files to move is $MOVE"
/usr/bin/tail -$MOVE $TMPF > $MOVELIST
echo "File to be moved"
cat $MOVELIST
while read FILE
do

echo "Moving file $FILE to $NEW_DIR"
done < $MOVELIST