Enable Archive log in Oracle10g

How do we enable the archive log mode in oracle10g?

The below article is tested in oracle10g R2 version. Please remember, below steps directories and folders would change based on the environment and operating systems. Here the database name is ORCL.

Step 1 We need to build the pfile from spfile to add new entries. If you have already recent pfile, the you do not need to do this step.

Login as sys user and execute this to create pfile.

create pfile='C:\oracle\product\10.2.0\db_1\dbs\PFILE.ORA' from spfile;

Step 2 Once pfile is created, then edit the pfile and add the below two parameters.

log_archive_format=Log_%s_%t_%r.arc
log_archive_dest='C:\oracle\product\10.2.0\db_1\database\archive'

In case, if you already have the below two entries in the pfile, then we need to remove or comment this below two entires. Since we can not have this below two entry with above new two parameters.

db_recovery_file_dest='C:\oracle\product\10.2.0\flash_recovery_area'
db_recovery_file_dest_size=2147483648

Till oracle9i, we use log_archive_start=true in parameter file. Since from oracle10g, this parameter is deprecated. We should not add this entry in pfile from oracle10g. If we have this entry in oracle10g, we get the below error.

ORA-32004:obsolete and/or deprecated parameter specified.

Step 3 Once pfile is edited, then we need to create the spfile with modified pfile. Login as sys user and execute the below command.

create spfile = 'C:\oracle\product\10.2.0\db_1\dbs\XX.ORA' from pfile='C:\oracle\product\10.2.0\db_1\dbs\PFILE.ORA'

Step 4 Rename the original SPFILEORCL.ORA to different name. Then rename the XX.ORA to SPFILEORCL.ORA.

Step 5 Login as sys user and shutdown the database and follow the steps.

Mount the database
alter database archivelog
alter database open;

Here is the screen output....

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 612368384 bytes
Fixed Size 1250428 bytes
Variable Size 188746628 bytes
Database Buffers 415236096 bytes
Redo Buffers 7135232 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination C:\oracle\product\10.2.0\db_1\database\archive
Oldest online log sequence 2
Next log sequence to archive 4
Current log sequence 4
SQL>

I have discussed some of the other topics which are related to this thread. Please go through below links in case if you are interested....

How do we set up RMAN in oracle? Click here

What are the new features in RMAN since Oracle9i? Click here

What is difference between traditional backup and RMAN backup? Click here

Different type of recovery scenario in RMAN? Click here