Saturday, February 7, 2009

Oracle: How to enable and disable archive log mode

Let us check a very simple procedure to enable or disable archive log mode

How to enable archive log mode :

let us check in which mode my database in now:

sys@LION> archive log list;

Database log mode No Archive Mode

Automatic archival Disabled
Archive destination d:\lion\archive
Oldest online log sequence 35
Current log sequence 37

so, currently it is in noarchive log mode . to switch to archive log mode , follow the steps:
i) restart the database in mount mode
step 1: sys@LION> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
step 2: sys@LION> startup mount
ORACLE instance started.

Total System Global Area 209715200 bytes
Fixed Size 1248164 bytes
Variable Size 125830236 bytes
Database Buffers 79691776 bytes
Redo Buffers 2945024 bytes
Database mounted.
step 3: sys@LION> alter database archivelog;

Database altered.
[to put the database in archive log mode]
step 4: sys@LION> alter database open;

Database altered.

step 5: sys@LION> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination d:\lion\archive
Oldest online log sequence 35
Next log sequence to archive 37
Current log sequence 37
[verify it is in archive log mode]

NOTE: Please perform all the actions as sys user and take a full database backup after you toggle the database to archive or noarchive log mode

How to disable archive log mode :

sys@lion> shutdown immediate;
sys@lion> startup mount;
sys@lion> alter database noarchivelog;
sys@lion> alter database open;

You can check from thw follwing view about whether your database is in archivelog mode or not
sys@LION> select log_mode from v$database;



have a nice day..............

We are still masters of our fate.
We are still captains of our souls.

Winston Churchil

No comments: