Home > Oracle blogroll > How to put Oracle Database in Archive Log Mode !!

How to put Oracle Database in Archive Log Mode !!

Today i am going to show you how we can change Oracle database Server mode from NO-ARCHIVE LOG to ARCHIVE LOG .

You can run a Oracle Database in ARCHIVE LOG mode or NO-ARCHIVE LOG mode depending upon the requirement.

Normally one would prefer to run in ARCHIVE LOG mode in Production , where as in Test/Development server one might want to avoid , since there are some overheads , but if you are really concerned about your data even on the Development Server, then you can always put DB in ARCHIVE LOG mode.

So basically the advantages of putting db in ARCHIVE LOG mode are :
1) Point in time DB recovery.
2) If you need to perform hot backup or RMAN ( Recovery Manager backup ) then your DB should be running under ARCHIVE LOG mode.

How do I determine if my Oracle DB is running under ARCHIVE LOG mode ??
Once you have taken a connection to SQL Plus , execute following command

SQL> archive log list

OR

SQL> select name "DB Name", log_mode from v$database

Refer to below screen shot for output

SCREEN 1
Check Archive log mode

As we can see currently my db ( testdb ) is in NO-ARCHIVE LOG Mode.

Here goes the steps to put it in ARCHIVE LOG mode.

SQL > shutdown immediate;
This will terminate all sessions, rollback any un-committed
transactions & shutdown Oracle db.

SQL> startup mount;
This will start the db in Mount mode.

SQL> alter database archivelog;
This step will put db in ARCHIVE LOG mode.

Refer to below screen shot for commands in action

SCREEN 2

Now since the DB mode has been changed, we can open the database by executing following command

SQL> alter database open;
This step will open the database.

SCREEN 3

Open the Database

We can verify if db is now running in Archive Log or not by re-executing the following command

SQL> archive log list

OR

SQL> select name "DB Name", log_mode from v$database

Refer to below screen shot for output

SCREEN 4

So going forward all our Redo logs are getting ARCHIVED in Archive Log location instead of getting overwritten … 🙂 , so the next question which you might ask as to since now db is running in ARCHIVE LOG Mode where are the logs getting archived … i mean what’s the ARCHIVE LOG path ??

If you refer above screen shot ( SCREEN 4 carefully ) it says “Archive Destination ” as “USE_DB_RECOVERY_FILE_DEST”, so basically this parameter is holding the path

execute following code

SQL > select name, value from v$parameter where name = 'db_recovery_file_dest';
OUTPUT -- > This gives the path as
"/u01/oracle/product/10.2.0/db_1/flash_recovery_area"

Note : Your system might have a different path
depending upon what's your ORACLE_HOME DIR

Refer to below screen shot

SCREEN 5

Archive Log Path

Now go into this path , in my case I will jump to /u01/oracle/product/10.2.0/db_1/flash_recovery_area and do a file listing at OS Level, the directory contains a folder with my db name i.e. MYTEST and within that there are some sub folder which are expected to holds actual ARCHIVE LOG files

The presence of ARCHIVE FILE would depend upon if Oracle has really Archived any of the REDO file yet or not, but you can always force Oracle to Archive current Redo log by executing the following commands

SQL> alter system switch logfile;



Complete path for Archive log files


IMPORTANT NOTE : Make sure that you have some mechanism setup which should delete the obsolete archive files from this path after every 2-3 days. In case this drive gets full and Oracle cannot archive Redo Log to this location, the database will freeze or hang instead of overwriting existing REDO Logs. I will try to cover this topic in coming weeks as to how we can remove these files after every 2 -3 days.

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

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: