ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling
complimentary technical Workshops on Database Security for the first 30
Oracle Database customers located anywhere in North America, EMEA, LATAM, or
APAC that send an email to
asra_us@oracle.com. Request a Workshop for
your organization today.
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=6;
SELECT value
FROM gv$parameter
WHERE name = 'log_archive_max_processes';
Configure the size of the FRA available for archived redo logs
db_recovery_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) 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;
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;
