How to turn on Archivelog in Oracle database

Archive logging is a very very important feature that should be turn on for all your critical production databases that require high availability. It will greatly enhance your backup and recovery strategy of the Oracle databases. This post will teach how to how turn on archivelog for Oracle database, both 10g and 11g.

SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /u01/app/oracle/product/11.2.0/dbs/arch
Oldest online log sequence 13
Current log sequence 15
SQL> alter system set log_archive_dest_1=’LOCATION=/archive/archivelog/css’ scope=spfile;

System altered.

SQL> shutdown immediate;

Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1887350784 bytes
Fixed Size 2222600 bytes
Variable Size 587204088 bytes
Database Buffers 1291845632 bytes
Redo Buffers 6078464 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 /archive/archivelog/css
Oldest online log sequence 13
Next log sequence to archive 15
Current log sequence 15

Now we need to verify that archive log is being created successfully. Do note that if the database will not process any SQL statement if it is unable to create archivelog after using up all the redo log group.

SQL> !ls -l /archive/archivelog/css
total 0

SQL> alter system switch logfile;

System altered.

SQL> !ls -l /archive/archivelog/css
total 418552
-rw-r—– 1 oracle dba 214296576 Oct 14 00:50 T1S15R792685847.arc

Advertisements
  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: