Monday, October 20, 2008

How to switch or enable a database to archive log mode [Oracle 9i]

When a database is in archive log mode oracle ensures that the online redo logs are not overwritten until they are archived. When a database is in archive log mode chances of data loss are minimal.

Here is a step by step procedure of how to enable archive logging in an Oracle database.

First and foremost this requires you to change a few parameters in the parameter file and also shut down the database.

Insert the following lines in the pfile of your database.




The above entries on the pfile ensure:

1) Location of the archive log files (in this case the archive logs are not mirrored)

2) The log_archive_dest_state_1 allows control over the availability state of the specified destination (1). The value enable indicates that Oracle can use the destination, whereas defer indicates that the location is temporarily disabled.

3) The archive logging is started.

Now shut down the database and start the database in mount mode with the pfile.

SQL> shut immediate

SQL> startup mount pfile=<location>

SQL> alter system archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> create spfile from pfile=<location> --- to ensure further db startups can use the spfile.

Now ensure that the database is running archive log mode.

SQL>archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination D:\oracle_databases\test2\archivelog\arc_

Oldest online log sequence 31

Next log sequence to archive 32

Current log sequence 32

SQL> alter system switch logfile;

Check whether the archive logs are being generated in the desired location.

That’s it!!

For more information on automatic archival, click here.