You set a database’s initial archiving mode as part of database creation. Usually, you can use the default of NOARCHIVELOG mode at database creation because there is no need to archive the redo information generated at that time. After creating the database, you can decide whether to change from the initial archiving mode.
After a database has been created, you can switch the database’s archiving mode on demand. However, you should generally not switch the database between archiving modes.
ARCHIVELOG mode is necessary for creating online backups and for certain types of database recovery. Configuring the database to operate in ARCHIVELOG mode allows the user to perform complete and point-in-time recovery from media (disk) failures using off-line or online backups. If ARCHIVELOG mode is disabled, the database can be restored from a backup in case of failure, but it cannot be rolled forward from that to a point when the failure occurred.
Oracle recommends ARCHIVELOG mode for all production databases
Setting the Initial Database Archiving Mode
When you create the database, you set the initial archiving mode of the redo log in the CREATE DATABASE statement. If you do not specify either ARCHIVELOG or NOARCHIVELOG, NOARCHIVELOG is the default. To verify database mode, execute the following statement:
SQL> Select NAME, CREATED, LOG_MODE, CHECKPOINT_CHANGE#, ARCHIVE_CHANGE# from V$DATABASE NAME CREATED LOG_MODE CHECKPOINT_CHANGE# ARCHIVE_CHANGE# --------- --------- ------------ ------------------ --------------- O112 19-MAR-18 NOARCHIVELOG 1438426 135961
Changing the Database Archiving Mode
There are “init.ora” parameters you need to modify in order to properly handle your database being in archive log mode. They are:
- LOG_ARCHIVE_DEST: This parameter specifies the directory where your archive logs will be placed.
- LOG_ARCHIVE_FORMAT: This parameter names the archive logs in this format. For example, if your format is: arch%s.arc, your log files will be called: arch1.arc, arch2.arc, arch3.arc where the ‘1’, ‘2’, ‘3’, etc is the sequence number.
Switching Database Archiving Mode
1. Shut down the database instance.
SQL> shutdown immediate
An open database must be closed and dismounted and any associated instances shut down before the database’s archiving mode can be switched. Archiving cannot be disabled if any datafiles need media recovery.
2. Backup the database. This backup can be used with the archive logs that you will generate.
3. Perform any operating system specific steps (optional).
4. Start up a new instance and mount, but do not open the database.
SQL> startup mount
5. Put the database into archivelog mode
SQL> alter database archivelog;
NOTE: You can also use below shown query to take the database out of archivelog mode.
SQL> alter database noarchivelog;
6. Open the database.
SQL> alter database open;
7. Verify your database is now in archivelog mode.
SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 22 Next log sequence to archive 24 Current log sequence 24
8. Archive all your redo logs at this point.
SQL> archive log all;
9. Ensure these newly created Archive log files are added to the backup process.