Enable / Disable Archive Log Mode In Oracle11g


- Archive log mode is the transaction of database will be recoverable  to any point in time

TO ENABLE ARCHIVE MODE:

 - We can check the archive log mode enable or not by using these queries

   SQL> select log_mode from V$database;

    LOG_MODE
   ------------
    NOARCHIVELOG
 - Other wise we can use this command also 

     SQL> archive log list
     Database log mode              No Archive Mode
     Automatic archival             Disabled
     Archive destination            USE_DB_RECOVERY_FILE_DEST
     Oldest online log sequence     5
     Current log sequence           7
in above

  *Database log mode = status of archive log mode whether it is an enabled or no (No Archive Mode or Archive Mode)

  *Automatic archival = Notification of archive log mode



  *Archive destination = Archive log files stored location by default it stored on flash_recovery_area

  *Oldest online log sequence = generated files of archive log

  *Current log sequence = current archive log which is process to write from redo into archive through ARcn 

Note : Here archive destination is USE_DB_RECOVERY_FILE_DEST.

 - We can determine the path by looking at the parameter RECOVERY_FILE_DEST

      SQL> show parameter recovery_file_dest

       NAME                                 TYPE        VALUE
     ------------------------------------ ----------- ------------------------------
      db_recovery_file_dest                string      /u01/app/oracle/product/11.2.0.3/flash_recovery
                                                 _area
     db_recovery_file_dest_size           big integer 3852M
  - if we do not want to write archive logs to the flash recovery so  we  can set the parameter LOG_ARCHIVE_DEST_n the archive logs which  location wish to write

    SQL> alter system set log_archive_dest_1='LOCATION=/u01/app/oracle/product/11.2.0.3/oradata/orcl/arch';

      system altered.
 - now we check the whether a location is changed or not

    SQL> archive log list;
     Database log mode              No Archive Mode
     Automatic archival             Disabled
     Archive destination            /u01/app/oracle/product/11.2.0.3/oradata/orcl/arch
     Oldest online log sequence     5
     Current log sequence           7
 - now we are going to enable archive log mode so must  shutdown the database

    SQL> Shut immediate
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
Note: we must to use  shut normal or shut immediate command if suppose use other shut down commands it will through an error by enabling  archive log mode

- Put a database in mount stage

    SQL> startup mount
    ORACLE instance started.

    Total System Global Area  849530880 bytes
    Fixed Size                  1339824 bytes
    Variable Size             511708752 bytes
    Database Buffers          331350016 bytes
    Redo Buffers                5132288 bytes
    Database mounted.
- Now we enable the archive log mode  by issuing this command and open the database 

   SQL> alter database archivelog;

    Database altered.

    SQL> alter database open;

    Database altered.

- let us we check the archive log mode status

   SQL> archive log list;
    Database log mode              Archive Mode
    Automatic archival             Enabled
    Archive destination            /u01/app/oracle/product/11.2.0.3/oradata/orcl/arch
    Oldest online log sequence     5
    Current log sequence           7
now the archive log is automated so  the logs are generated and stored in archive destination location we can switch to the log file to see that an archive is written to archive log location.

   SQL> alter system switch logfile;

    System altered.
 
   SQL> host
   [oracle@oracle11g ~]$ ls /u01/app/oracle/product/11.2.0.3/oradata/orcl/arch
     1_7_711369564.dbf
   [oracle@oracle11g ~]$ exit
 
 
TO DISABLE ARCHIVE MODE

  - Now we are going to bring my database into  no archive log mode right now our database in archive log  mode,

   SQL> shutdown immediate
    Database closed.
    Database dismounted.
   ORACLE instance shut down.




- so we  shut the database and bring up  into mount stage
 


SQL> startup mount
    ORACLE instance started.

      Total System Global Area  849530880 bytes
      Fixed Size                  1339824 bytes
     Variable Size             511708752 bytes
     Database Buffers          331350016 bytes
     Redo Buffers                5132288 bytes
     Database mounted.
 the below  command is   change  to our database from archive mode into no archive mode

   SQL> alter database noarchivelog;

   Database altered.

Now open the database

  SQL> alter database open;

  Database  altered
check the archive log mode

   SQL> archive log list;
   Database log mode              No Archive Mode
   Automatic archival             Disabled
   Archive destination            /u01/app/oracle/product/11.2.0.3/oradata/orcl/arch
   Oldest online log sequence     6
   Current log sequence           8