ORACLE : Enable Archiving

Enable Archiving

Step -1 check the archive status

SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Current log sequence 2

Step -2 shutdown the database to update the parameter file

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

Step -3 Open the parameter file [initname.ora]
(Using PFILE)
Add two parameters for enable archiving in user defined location and format for the files (we can specify upto 9 destinations)

log_archive_dest_1='LOCATION=c:\archive'
log_archive_format='ARC%S_%R_%T_%S'

Step -4 Startup the database in mount and enable archiving

SQL> startup mount
ORACLE instance started.

Total System Global Area 838860800 bytes
Fixed Size 1251728 bytes
Variable Size 234882672 bytes
Database Buffers 595591168 bytes
Redo Buffers 7135232 bytes
Database mounted.

Alter the database to archive mode and open the database



Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;

Database altered.

Step -5 Check the archive destination and log sequence numbers

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination c:\archive
Oldest online log sequence 1
Next log sequence to archive 2
Current log sequence 2

Using Spfile

Step -1 check the archive status

SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Current log sequence 2

Step -2 update the system parameter file [spfilename.ora]

update two parameters for enable archiving in user defined location and format for the files (we can specify upto 9 destinations)

sql> alter system set
log_archive_dest_1='LOCATION=c:\archive' scope=spfile;

sql> alter system set
log_archive_format='ARC%S_%R_%T_%S' scope=spfile;

Step -3 bounce the database to take effect on updated parameters

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area 838860800 bytes
Fixed Size 1251728 bytes
Variable Size 234882672 bytes
Database Buffers 595591168 bytes
Redo Buffers 7135232 bytes
Database mounted.
Database opened.