Note: It is recommended to perform a backup of the database (after a shutdown normal or immediate) prior to changing the archive status of a database.
If you would like to perform these tasks using Database Console see the post Enable/Disable Archive Log Mode 10g/11g using Database Console.
Enable Archive Log Mode
The following are the steps required to enable archive log mode on an Oracle 10g or 11g database.
Verify the database log mode.
[oracle@ora1 ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.1.0 Production on Thu Apr 8 12:02:52 2010 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> archive log list Database log mode No Archive Mode Automatic archival Disabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 25 Current log sequence 27 SQL>
The log mode is No Archive Mode. Note that Archive destination is USE_DB_RECOVERY_FILE_DEST. You 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/flash_recovery
_area
db_recovery_file_dest_size big integer 3852M
SQL>
By default, archive logs will be written to the flash recovery area. If you do not want to write archive logs to the flash recovery area you can set the parameter LOG_ARCHIVE_DEST_n to the location in which you wish to write archive logs.
SQL> alter system set log_archive_dest_1='LOCATION=/u02/app/oracle/oradata/orcl/arch' scope = both; System altered. SQL> archive log list; Database log mode No Archive Mode Automatic archival Disabled Archive destination /u02/app/oracle/oradata/orcl/arch Oldest online log sequence 25 Current log sequence 27 SQL>
Now we shutdown the database and bring it backup in mount mode.
SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. 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. SQL>
Lastly all that is needed it set archive log mode and open the database.
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/orcl/arch Oldest online log sequence 25 Next log sequence to archive 27 Current log sequence 27 SQL>
We can now see that archive log mode is enabled. Notice that Automatic archive is enabled as well. In Oracle 9i an earlier another parameter needed to be set in order to enable automatic archiving. This in no longer the case in 10g and 11g as automatic archiving is enabled when the database is placed in archive log mode.
You 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@ora1 ~]$ ls /u02/app/oracle/oradata/orcl/arch 1_27_711369564.dbf [oracle@ora1 ~]$ exit exit SQL>
Disable Archive Log Mode
Note: It is recommended to perform a backup of the database (after a shutdown normal or immediate) prior to changing the archive status of a database.
The following are the steps required to disable archive log mode on an Oracle 10g or 11g database.
Verify the database log mode.
[oracle@ora1 ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.1.0 Production on Thu Apr 8 12:54:05 2010 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options 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 SQL>
The Database log mode is Archive mode. Next we shut down the database and bring up back up in mount mode.
SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. 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. SQL>
All that is left is to disable archive log mode and open the database.
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/orcl/arch Oldest online log sequence 26 Current log sequence 28 SQL>
As you can see, ARCHIVELOG mode has been disabled.
hi thanks so much for the help the steps were so great and all worked without any error
best
Great post.
Very helpful and easy to follow
thanks.. clear and most helpful 🙂
Very nicely and clearly explained.. awesome. keep up the good work. Thanks
Thanks. It is clearly explained.
Awesome Help.
Thnx Onceagain
Before alter database noarchivelog; you should open database (alter database open)
WOW just what I was searching for. Came here by searching for a
AWESOME….Working on Attunity POC and couldn’t do enable archive log using Database control. This worked great for me. Searched all over and this post helped me a lot. Thanks again !!!!
If we just want to turn off the ArchiveLogs and also want to delete the archive log files generated before this point without backing up.
1. What happens to database while in no archive log mode ?
2. What happens to database when we try to take a backup using RMAN in future ?
3. What happens to database if we want to re-enable Archive Log mode ?
Adding answers to these questions will make this article perfact.
1. What happens to database while in no archive log mode ?
-> Nothing happens. The archive log files are not deleted. It is not possible to use them anymore. Except if you want to use them with logminer*.
2. What happens to database when we try to take a backup using RMAN in future ?
-> It is not possible to take a hot backup until you put your database in archivelog mode.
Cold backup remain possible.
3. What happens to database if we want to re-enable Archive Log mode ?
-> the archive log files are obsolete. First, delete or archive them to avoid any misunderstanding.
Nothing else to do.
Logminer is used to retrieve old DML
Perfect. Thanks
It is really helpful.. Thanks!!
I’m no longer positive where you’re getting your info, however good topic.
I must spend some time learning more or working out more.
Thank you for great information I used to be searching for this info for my mission.
to specify an optional secondary archive destination. All locations must be local. Whenever the database archives a redo log, it archives it to every destination specified by either set of parameters.
Thank you for sharing.
Thx.this is perfect