Friday, 1 August 2014

How to Enable/Disable Archive Log Mode in 10g/11g

By default when you enter CREATE DATABASE statement, it is in noarchive log mode, to change the archiving mode of the database, use the ALTER DATABASE statement with the ARCHIVELOG or NOARCHIVELOG clause using administrator privileges (AS SYSDBA).


By default, Oracle database store archive logs data into the flash recovery area. If you want to set  new location for archive logs, you can set using the parameter LOG_ARCHIVE_DEST_n to the location in which you wish to write archive logs.

SQL> show parameter recovery_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u01/app/oracle/flash_recovery
                                                 _area
db_recovery_file_dest_size           big integer 2782M
SQL>




SQL> alter system set log_archive_dest_1='LOCATION=/u02/app/oracle/oradata/orcl01/arch01' scope = both;

System altered.

SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /u02/app/oracle/oradata/orcl01/arch01
Oldest online log sequence     26
Current log sequence           28
SQL> 

How to Enable Archive log Mode

Shut down the database instance and startup in mount stage:

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

SQL> startup mount
ORACLE instance started.

Total System Global Area  606806016 bytes
Fixed Size                  1376268 bytes
Variable Size             394268660 bytes
Database Buffers          205520896 bytes
Redo Buffers                5640192 bytes
Database mounted.


SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u02/app/oracle/oradata/orcl01/arch01
Oldest online log sequence     26
Next log sequence to archive   28
Current log sequence           28

How to Disable Archive log Mode

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u02/app/oracle/oradata/orcl/arch
Oldest online log sequence     26
Next log sequence to archive   28
Current log sequence           28

Shut down the oracle database instance and start in mount stage.

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

SQL> startup mount
ORACLE instance started.

Total System Global Area  606806016 bytes
Fixed Size                  1376268 bytes
Variable Size             394268660 bytes
Database Buffers          205520896 bytes
Redo Buffers                5640192 bytes
Database mounted.

SQL> alter database noarchivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /u02/app/oracle/oradata/orcl01/arch01
Oldest online log sequence     26
Current log sequence           28

You can also check official oracle docs for enabling/disabling archivelog mode here:

Controlling Archive

No comments:

Post a Comment