Enable/Disable Archive Log Mode 10g/11g

by Eric Jenkinson on April 8, 2010

Categories: Backup and Recovery

Tagged: ,

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.

Comments

  • Joseph (July 30, 2010 1:14 pm)

    Very clear. Thank you. Joseph

  • Swathi (December 20, 2010 3:23 am)

    While enabling archivelog mode, there is an Listner error.
    Below are the steps followed:

    SQL> alter system set log_archive_dest_1=’location=D:\oracle\product\10.2.0\flash_recovery_area’ sco
    pe=both;

    System altered.

    SQL> archive log list;
    Database log mode No Archive Mode
    Automatic archival Disabled
    Archive destination D:\oracle\product\10.2.0\flash_recovery_area
    Oldest online log sequence 8
    Current log sequence 10
    SQL> shutdown immediate;
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    SQL> startup mount;
    ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

    Oracle version is
    SQL> select * from v$version ;

    BANNER
    —————————————————————-
    Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Prod
    PL/SQL Release 10.2.0.1.0 – Production
    CORE 10.2.0.1.0 Production
    TNS for 32-bit Windows: Version 10.2.0.1.0 – Production
    NLSRTL Version 10.2.0.1.0 – Production

    Please provide me solution how to resovlve this error.

    Regards
    Swathi.

  • Eric Jenkinson (December 21, 2010 5:46 am)

    Thank you for visiting Oracle Distilled.

    See the following post ORA-12514 TNS:listener does not currently know of service requested in connect descriptor

  • Ranvir (January 28, 2011 2:09 am)

    HI Swati,

    You are using 32 bit windows server may 2003 & Oracle 10g.2.

    Click on windows start button–> then select Run—>then write–>cmd
    —> then write lsnrctl start, then your listener is go start…
    after is you can issue your command.
    sql> archive log list;
    sql> shut immediate;
    sql> startup mount;
    sql> alter database archivelog;
    sql> archive log list #
    sql> show parameter log_

    Thanks & Regards,
    Ranvir Meshram
    +919890673586

  • PURVESH (March 21, 2011 3:11 am)

    Dear Eric,

    What is the advantage to enable archive mode in oracle?

    Regards,
    Purvesh Prajapati

  • PURVESH (March 21, 2011 4:38 am)

    Dear Eric,

    I have done the archiv log enble on our UAT server. I will do the monitoring and get back if any issue.

    Thanks for your instructions to enable archive.

    Regards,
    Purvesh Prajapati

  • Eric Jenkinson (March 21, 2011 6:19 am)

    Hello Purvesh,

    Enabling archive log mode allows you to employ high availability features in Oracle such data guard and flashback database. Archive log mode also allows you to perform database backups while the database is online and allows point in time recovery.

  • Eric Jenkinson (March 21, 2011 6:28 am)

    Hello Purvesh,

    One thing to monitor in your UAT environment is the LOG_ARCHIVE_DEST_1 directory. Make sure that location has enough space to hold a days worth of archive logs. If the location is filled with archive logs the database will not be able to write archive logs and will hang until space is available.

    The easiest way to clear the archive logs is to back them up. See the post Quick RMAN configuration for disk based backups for a quick tutorial on setting up RMAN. After that all you need to do is issues the command in RMAN.

    backup archivelog all delete input;
    

    This will backup the archive logs to the configured location and delete archive logs in the archive log directory.

  • Ann Nicholls (April 25, 2011 9:55 am)

    I have a new Oracle 11g DB on an AIX platform and the DB was not in archive log mode so that import would not fill up the archive log space. I now want to put the DB in archivelog mode. After following the steps above, I attempted to backup the DB and it failed because it expected an archivelog file to exist that did not. What did I miss? I then attempted to backup just the archive logs with RMAN and received the same message. Any assistance would be reatly appreciated. I am new to 11g. Ann

    RMAN> backup archivelog all delete input;

    Starting backup at 25-APR-11
    current log archived
    using target database control file instead of recovery catalog
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: SID=398 device type=DISK
    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-03002: failure of backup command at 04/25/2011 10:47:30
    RMAN-06059: expected archived log not found, lost of archived log compromises recoverability
    ORA-19625: error identifying file /u05/oradata/oaktst11/archive/1_104_742662353.arc
    ORA-27037: unable to obtain file status
    IBM AIX RISC System/6000 Error: 2: No such file or directory
    Additional information: 3

  • Younus (May 25, 2011 9:38 pm)

    Hi Ann,

    This is possible that some one has edited the file or deleted the required archive log file, you can do two things if you have a file backup from os you can put it back at the same location or you can unregister rman delete all the log file and then register rman * only if database is running fine

    Regards

  • yass (July 16, 2011 8:16 am)

    Thanks

  • David H (July 20, 2011 5:10 pm)

    I have Oracle 11g R2 installed in Windows 2008 R2.The instance is running in noarchivelog mode now. It is running fine.
    I need to put it in archivelog mode but I am having issue trying to get it startup in mount mode since it has to have the OracleServiceORCL started.
    Once this service is started the database is in OPEN mode.
    Anyone knows, How to put OracleServiceORCL in windows to start in mount mode so I can put the database in Archivelog mode?
    I gave shutdown immediate – it went down properly
    SQL>
    SQL> shutdown immediate;
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    SQL> startup nomount;
    ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

    At this point, I have to start OracleServiceORCL to start it but it will go straight to OPEN mode.

    Thank you

  • Eric Jenkinson (July 21, 2011 1:07 pm)

    Hello David,

    It looks like you are connected using “connect sys/password@dbsid”. When the database is down it is no longer registered with the listener hence the error. There are two things you can do to resolve this issue.

    1. Connect to the db without using a network connection “connect / as sysdba”
    2. Statically register the database with the listener. I have document on how to do this: Configure Static Service Information for a Database

    Eric Jenkinson

  • vasanth (September 8, 2011 2:41 am)

    great work.. very very clear.. like spoon feed.. thanks for the post…

  • saleem (December 11, 2011 12:47 pm)

    i have low tablespace due to arrive logs

  • kumar (December 26, 2011 3:40 am)

    why should we enable/disable archivelog when database is in mounted not in open?

  • Giancarlo Reina (April 11, 2012 5:24 pm)

    Great Work. It was very helpful.

    Regards from Guatemala.

  • abhay Sequeira (April 13, 2012 11:17 am)

    Thanks! Very Well Explained.

  • WiSch GbR (June 10, 2012 3:00 pm)

    […] [1][2][3][4] Administration, Datenbank, SQL Datenbank, Oracle, […]

  • Fortino (June 16, 2012 9:51 pm)

    Very easy.
    Thank you.

  • ndah (November 12, 2012 6:13 am)

    Hello Eric,

    I like your demosntrations, I would like to know how to display the out put with numbers in the clip board. Is there any software to do it?

  • Baba Fakruddin (November 19, 2012 12:18 am)

    Hello Eric,

    Very nice explanation.

    Thanks,
    Baba.K

  • yogesh (January 11, 2013 10:51 pm)

    Thank you very much ……. now it clears my concept

  • Pressley (January 22, 2013 3:33 am)

    Thanks.

    Very good explanation.

  • Vamsi Krishna (January 28, 2013 2:04 am)

    Hi Eric,

    Very nice explanation, thank you for your time in sharing your knowledge. I guess I’m a late visitor to this site :)

    I feel very nice about your website.

    Thanks again

    Vamsi

  • Nelson (March 18, 2013 9:13 pm)

    Simple enough. Thank you

  • Nelson (March 18, 2013 9:14 pm)

    SAludos desde Honduras

  • Srinivasa Reddy (April 22, 2013 8:52 pm)

    hi sir,

    nice explanation, i have a small doubt

    why should we put database in “mount state” to change database from ‘no archive’ to ‘archive’ and ‘archive’ to ‘no archive'(i mean why dont we put it in nomount or open state).

    Thanku and regards,
    Srinivasa Reddy.A

  • Denis (May 28, 2013 6:41 am)

    Nice simple clear explanation.
    Thanks you

  • Ceddy (June 11, 2013 1:40 pm)

    Excellent article, Eric!
    Very well written, clear and concise.

  • Dawa (July 18, 2013 7:13 am)

    hats off for expalantaion… my leave for today is worth

  • SAlem (August 28, 2013 10:37 am)

    Hi,

    I followed your instruction but with this you cannot enable Flash area recovery, how can we enable both?

  • Rajat (October 5, 2013 9:31 am)

    Excellent work

  • Rajasekar Paramasivan (November 14, 2013 12:18 am)

    Swathi (December 20, 2010 3:23 am)
    While enabling archivelog mode, there is an Listner error.
    Below are the steps followed:

    SQL> alter system set log_archive_dest_1=’location=D:\oracle\product\10.2.0\flash_recovery_area’ sco
    pe=both;

    System altered.

    SQL> archive log list;
    Database log mode No Archive Mode
    Automatic archival Disabled
    Archive destination D:\oracle\product\10.2.0\flash_recovery_area
    Oldest online log sequence 8
    Current log sequence 10
    SQL> shutdown immediate;
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    SQL> startup mount;
    ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

    Oracle version is
    SQL> select * from v$version ;

    BANNER
    —————————————————————-
    Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Prod
    PL/SQL Release 10.2.0.1.0 – Production
    CORE 10.2.0.1.0 Production
    TNS for 32-bit Windows: Version 10.2.0.1.0 – Production
    NLSRTL Version 10.2.0.1.0 – Production

    Please provide me solution how to resovlve this error.

    Regards
    Swathi.

    solution for the problem
    *************************

    connect sys user without connection_String

    instead of sys@conn_string as sysdba
    connect like given below

    conn sys/sys as sysdba

    that is connect sys user without connection string

    then the above problem will solve

  • Rajasekar Paramasivan (November 14, 2013 12:19 am)

    Swathi (December 20, 2010 3:23 am)
    While enabling archivelog mode, there is an Listner error.
    Below are the steps followed:

    SQL> alter system set log_archive_dest_1=’location=D:\oracle\product\10.2.0\flash_recovery_area’ sco
    pe=both;

    System altered.

    SQL> archive log list;
    Database log mode No Archive Mode
    Automatic archival Disabled
    Archive destination D:\oracle\product\10.2.0\flash_recovery_area
    Oldest online log sequence 8
    Current log sequence 10
    SQL> shutdown immediate;
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    SQL> startup mount;
    ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

    Oracle version is
    SQL> select * from v$version ;

    BANNER
    —————————————————————-
    Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Prod
    PL/SQL Release 10.2.0.1.0 – Production
    CORE 10.2.0.1.0 Production
    TNS for 32-bit Windows: Version 10.2.0.1.0 – Production
    NLSRTL Version 10.2.0.1.0 – Production

    Please provide me solution how to resovlve this error.

    Regards
    Swathi.

    solution for the problem
    *************************

    connect sys user without connection_String

    instead of sys@conn_string as sysdba
    connect like given below

    conn sys/sys as sysdba

    that is connect sys user without connection string

    then the above problem will solve

    Thanks & Regards,

    P.RAJASEKAR

  • Prathap (January 31, 2014 5:57 pm)

    Good article

  • ramesh (May 9, 2014 11:08 am)

    archivelog set then, system alterd .
    it returns error:

    please let me know to solve the issue…

    SQL> alter database archivelog
    2 ;
    alter database archivelog
    *
    ERROR at line 1:
    ORA-00265: instance recovery required, cannot set ARCHIVELOG mode

  • Ravi (September 18, 2014 12:25 pm)

    Excellent note.

    Thanks

Post A Comment