Oracle Controlling Archive Logging

There are two possible states for archive logging, either off or on. If archive logging is on there are two possible states, either automatic or manual. These are controlled via the initialization parameter ARCHIVE_LOGGING and the database create option ARCHIVELOG or NOARCHIVELOG.

If a database is created with the ARCHIVELOG option but the LOG_ARCHIVE_START parameter in the initialization file is set to FALSE (the default), the database is in manual archive mode.

If the database is created with ARCHIVELOG option and the initialization parameter is set to TRUE the database is in automatic mode (providing there is a valid LOG_ARCHIVE_DEST parameter setting.)

If the NOARCHIVELOG mode is set on database creation the LOG_ARCHIVE_START and LOG_ARCHIVE_DEST parameters are ignored.

It is suggested that the database be created with the ARCHIVELOG parameter set to NOARCHIVELOG and that once all initial setup, and data loading operations are complete the DBA alter the database to perform archive logging. This prevents the DBA from having to deal with the numerous logs that will be created due to initial data load operations. This procedure is shown next:

1. Create database using NOARCHIVELOG
    
2. Perform initial setup and data loads.
    
3. Alter the INIT.ORA parameters LOG_ARCHIVE_START, LOG_ARCHIVE_DEST, and LOG_ARCHIVE_FORMAT to valid values.
    
4. Alter the database to closed
    
5. Alter the database to ARCHIVELOG
    
6. Shutdown, backup and restart the database.

The format for the command to alter the database archive log mode is:

ALTER DATABASE database name ARCHIVELOG;

— or–

ALTER DATABASE database name NOARCHIVELOG;

If a specified archive log destination fills, archive logging will stop and so will the database once the last available log file fills. In this situation log in to SQLPLUS and use the

ARCHIVE LOG START 'new destination';

command to alter the location the archive logs are being sent.