Sunday, April 17, 2011

Enabling archivelog in oracle10g


Using Spfile:

1. Cold backup must be performed before enabling archivelog.

2. Take spfile backup

3. Enable archvielog destination

a. Alter system set log_archive_dest_1=’LOCATION=\path’ scope =both

b. alter system set log_archive_format='arch_%t_%s_%r.arc' scope=both;

c. shutdown immediate;

4. connect as sysdba

5. startup mount;

6. alter database archivelog;

7. alter database open;

8. archive log list;

9. SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /u06/csr/oradata/arch

Oldest online log sequence 8

Next log sequence to archive 12

Current log sequence 12

SQL>

10. Perform cold backup after enabling archivelog

Using pfile:

1. Create pfile from spfile;

2. Shutdown database

3. Edit pfile n add

a. *.log_archive_dest_1='LOCATION=/u06/arch'

b. *.log_archive_format='arch_%t_%s_%r.arc'

4. Startup mount pfile=\pfilepath

5. Alter database archivelog

6. alter database open;

7. archive log list;

8. SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /u06/csr/oradata/arch

Oldest online log sequence 8

Next log sequence to archive 12

Current log sequence 12

SQL>

9. Create spfile from pfile;