Oracle Archive Log Mode
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
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';
Configure the size of the FRA available for archived redo logs db_recoery_file_dest_size=<integer [M, G, T]>;
conn / as sysdba

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

ALTER SYSTEM SET db_recovery_file_dest_size=4G;

SELECT value
FROM gv$parameter
WHERE name = 'db_recovery_file_dest_size';
 
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;

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

Related Topics
Backup & Recovery
Flashback Database
Log Files
Redo
RMAN
RMAN Demos
Startup Parameters

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved