Thursday 26 March 2009

How does one put a database into ARCHIVELOG mode?

The main reason for running in archivelog mode is that one can provide 24-hour availability and guarantee complete data recoverability. It is also necessary to enable ARCHIVELOG mode before one can start to use on-line database backups.

Issue the following commands to put a database into ARCHVELOG mode:
SQL> CONNECT sys AS SYSDBA
SQL> STARTUP MOUNT EXCLUSEVE;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ARCHIVE LOG START;
SQL> ALTER DATABASE OPEN;

Alternatively, add the above commands into your database's startup command script, and bounce the database. The following parameters needs to be set for databases in ARCHIVELOG mode:
log_archive_start = TRUE
log_archive_dest_1 = 'LOCATION=/arch_dir_name'
log_archive_dest_state_1 = ENABLE
log_archive_format = %d_%t_%s.arc

NOTE 1: Remember to take a baseline database backup right after enabling archivelog mode. Without it one would not be able to recover. Also, implement an archivelog backup to prevent the archive log directory from filling-up.

NOTE 2: ARCHIVELOG mode was introduced with Oracle V6, and is essential for database point-in-time recovery.Archiving can be used in combination with on-line and off-line database backups.

NOTE 3: You may want to set the following INIT.ORA parameters when enabling ARCHIVELOG mode:
log_archive_start=TRUE, log_archive_dest=..., and log_archive_format=...

NOTE 4: You can change the archive log destination of a database on-line with the ARCHIVE LOG START TO 'directory'; statement. This statement is often used to switch archiving between a set of directories.

NOTE 5: When running Oracle Real Application Server (RAC), you need to shut down all nodes before changing the database to ARCHIVELOG mode.

No comments:

Post a Comment