In this post, I will show you how to enable archivelog mode in 9i. For those who are using Oracle 10g onwards, you may take a look at my post for more: How to Enable Archivelog Mode in Oracle
Create archive log destination directory and enter sqlplus
[oracle@test ~]$ mkdir -p /u01/app/oracle/product/9.2.0/archivelog/oracle9i/
[oracle@test ~]$ sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.8.0 - Production on Thu Apr 14 18:59:03 2012
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to an idle instance.
Startup the database and see if there's any problem.
SQL> startup;
ORACLE instance started.
Total System Global Area 235999648 bytes
Fixed Size 450976 bytes
Variable Size 201326592 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
Check the current status of archive mode before enabling archivlog
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /u01/app/oracle/product/9.2.0/dbs/arch
Oldest online log sequence 11
Current log sequence 13
Bounce the database to mount
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 235999648 bytes
Fixed Size 450976 bytes
Variable Size 201326592 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
Database mounted.
Set necessary parameters in SPFILE
SQL> alter system set log_archive_start=TRUE scope=spfile;
System altered.
SQL> alter system set log_archive_dest_1='location=/u01/app/oracle/product/9.2.0/archivelog/oracle9i/' scope=spfile;
System altered.
SQL> alter system set log_archive_format='arch_%t_%s.arc' scope=spfile;
System altered.
Bounce the database to mount again
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 235999648 bytes
Fixed Size 450976 bytes
Variable Size 201326592 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
Database mounted.
Start and enable archive log
SQL> archive log start;
Statement processed.
SQL> alter database archivelog;
Database altered.
Open the database
SQL> alter database open;
Database altered.
Check the current status of archive mode after enabling archivelog
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/product/9.2.0/archivelog/oracle9i/
Oldest online log sequence 11
Next log sequence to archive 13
Current log sequence 13
Force redo log file to switch
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
Check the log archive destination
[oracle@oracle9i ~]$ ll /u01/app/oracle/product/9.2.0/archivelog/oracle9i/
total 896
-rw-r----- 1 oracle oinstall 871424 Apr 14 19:13 arch_1_13.arc
-rw-r----- 1 oracle oinstall 32256 Apr 14 19:13 arch_1_14.arc
-rw-r----- 1 oracle oinstall 3072 Apr 14 19:13 arch_1_15.arc
-rw-r----- 1 oracle oinstall 1024 Apr 14 19:13 arch_1_16.arc
We're done.
Note: LOG_ARCHIVE_START is deprecated since 10g.