Search this blog for other interesting articles

Thursday 28 July 2011

Enabling or Disabling archivelog mode in Oracle


This article answers the following queries :
·         How to findout  whether current archive mode  in Oracle ?
·         How to connect to SQL in oracle?
·         How to findout whether archive mode is on or off in oracle ?
·         How to enable archive log mode in Oracle ?
·         How to disable archive log mode in Oracle ?
·         What are the steps to be followed in Oracle to change archive log  mode?
·         How to shut down the oracle database ?
·         How to startup the oracle database in mount mode?


How to findout current archive mode in Oracle ?

Goto SQL prompt of oracle and provide the following command to find out the archive mode  in Oracle.

SQL > select  log_mode from v$database;

It ouptus similar to as shown below if the database is in no archive mode.
LOG_MODE
------------------
NO ARCHIVE LOG

Steps to be followed in Oracle to Change the archive log mode

1)      Connect to SQL plus
How to connect to SQL ?
First login to the database server using orasid user
             Then connect to sqlplus as follows
sqlplus /nolog
conn /as sysdba

Then you will be connected to database and SQL prompt appears as below:
SQL >

2)     Find out the current archive log mode by providing below command as explained earlier:
SQL > select  log_mode from v$database;

3)     If current archive log is NO ARCHIVE LOG mode and if you would like to set to ARCHIVE LOG mode, first of all shut down the oracle database.
How to shutdown oracle database?
Proceed as follows to shutdown the database
           SQL > shutdown immediate;
            Please wait for a while and system brings down  the oracle database and SQL prompt   
            appears.

4)     Please note database should be in mount mode while changing the archive log mode in Oracle.
How to start the oracle database in Mount mode?
Start the database in mount mode as shown below :
SQL> startup mount
An output similar to below will appear:
ORACLE Instance started
Total System Area  xxxxxxxx  bytes
Fixed size                     xxxxxx bytes
Variable size              xxxxxxx bytes
Database buffers            xxxxx bytes
Redo buffers                     xxxx bytes
Database mounted

5)     Now since database is mounted provide the following command at SQL      
      prompt

SQL > alter database archivelog;
Once this command is given System prompts you with a message “Database altered”

DISABLING ARCHIVE LOG MODE
Please note : For example, if you would like to set the database in NO ARCHIVE LOG mode ( or disable archive log mode ) then command should be
SQL > alter database  noarchivelog;

6)     Now open the database using the following command
SQL > alter database open ;
After this system prompts you with a message “Database altered” confirming that the data base opened

7)     Now cross check the current archive mode using the command below
SQL > select  log_mode from v$database;
If you have enabled archive log mode then system prompts with a message like
LOG_MODE
----------------
ARCHIVE LOG

Please note changing archive log mode can also be done through brtools utility also. Will explain that process for the same in another article







Enter your email address:


Delivered by FeedBurner



7 comments:

sap erp training said...
This comment has been removed by a blog administrator.
Unknown said...

Simple and excellent !
Great work

Unknown said...

It is great to have blog like this.....can you please post detail steps(starting to end) regarding DB refresh, errors pertaining to control file...

Anonymous said...

its our pleasure having blogs like this,,,,,,,,

Unknown said...

db was down how to check it? wr we can check?

Ninad Khaire said...

we can also check archive log mode by "archive log list;" instead of "select log_mode from v$database;"
m i right?

Anonymous said...

YES

Search this blog for other interesting articles

Please subscribe & activate link received to your email id to receive latest articles

Enter your email address:

Delivered by FeedBurner