BNFD - Your DBA NoteBook On The Net!!!

Saturday, January 26, 2008

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> select log_mode from v$database;
LOG_MODE
------------
NOARCHIVELOG

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 IN INIT.ORA 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.


OR
If you want to enable archive log mode on a single node instance and a Real Application Clusters (RAC).

Do below,
ALTER SYSTEM SET log_archive_start=TRUE SCOPE=spfile;
If you are getting error : ora-32001:write to SPFILE requested but no SPFILE specified at startup

then you have to do below:
create spfile from pfile;
shut immediate;
startup;

and then you can issue below commands:
ALTER SYSTEM SET log_archive_start=TRUE SCOPE=spfile;
ALTER SYSTEM SET log_archive_dest_1='location=/u01/oradata/MYSID/archive/' SCOPE=spfile;
ALTER SYSTEM SET log_archive_format='arch_%t_%s.arc' SCOPE=spfile;

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ARCHIVE LOG START;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

1 comment:

Anonymous said...

From Oracle10g, the paramter "log_archive_start" is deprecated and also there is no need to issue the command to start the archiving of redolog files as the command "alter database archivelog" automatically starts the archiving process.