8/30/2017

Oracle set database in archivelog mode

  • How to find out whether current archive mode in Oracle?
  • How to connect to SQL in Oracle?
  • How to find out 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 find out current archive mode in Oracle?
Open  SQLPlus command line and provide the following command to find out the archive mode in Oracle.
SQL > select  log_mode from v$database;

It outputs 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

No comments:

Post a Comment

Popular Posts