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