Forgot your password?    
+ Reply to Thread
Results 1 to 1 of 1

Thread: Changing Oracle Database from Noarchivelog to Archivelog mode

  1. #1
    Join Date
    Oct 2011

    Changing Oracle Database from Noarchivelog to Archivelog mode

    What are Archive Redo Log?

    Oracle Database lets you save filled groups of redo log files to one or more offline destinations, known collectively as the archived redo log, or more simply the archive log. The process of turning redo log files into archived redo log files is called archiving. This process is only possible if the database is running in ARCHIVELOG mode. You can choose automatic or manual archiving.

    Choosing Between NOARCHIVELOG and ARCHIVELOG Mode
    This section describes the issues you must consider when choosing to run your database in NOARCHIVELOG or ARCHIVELOG mode, and contains these topics:
    Running a Database in NOARCHIVELOG Mode
    Running a Database in ARCHIVELOG Mode

    Running a Database in NOARCHIVELOG Mode

    When you run your database in NOARCHIVELOG mode, you disable the archiving of the redo log. The database control file indicates that filled groups are not required to be archived. Therefore, when a filled group becomes inactive after a log switch, the group is available for reuse by LGWR.

    NOARCHIVELOG mode protects a database from instance failure but not from media failure. Only the most recent changes made to the database, which are stored in the online redo log groups, are available for instance recovery. If a media failure occurs while the database is in NOARCHIVELOG mode, you can only restore the database to the point of the most recent full database backup. You cannot recover transactions subsequent to that backup.

    In NOARCHIVELOG mode you cannot perform online tablespace backups, nor can you use online tablespace backups taken earlier while the database was in ARCHIVELOG mode. To restore a database operating in NOARCHIVELOG mode, you can use only whole database backups taken while the database is closed. Therefore, if you decide to operate a database in NOARCHIVELOG mode, take whole database backups at regular, frequent intervals.

    Running a Database in ARCHIVELOG Mode

    When you run a database in ARCHIVELOG mode, you enable the archiving of the redo log. The database control file indicates that a group of filled redo log files cannot be reused by LGWR until the group is archived. A filled group becomes available for archiving immediately after a redo log switch occurs.

    The archiving of filled groups has these advantages:

    A database backup, together with online and archived redo log files, guarantees that you can recover all committed transactions in the event of an operating system or disk failure.

    If you keep an archived log, you can use a backup taken while the database is open and in normal system use.

    You can keep a standby database current with its original database by continuously applying the original archived redo logs to the standby.

    You can use archived redo logs to:
    Recover a database
    Update a standby database
    Get information about the history of a database using the LogMiner utility

    Changing the Database Archiving Mode

    To change the archiving mode of the database, use the ALTER DATABASE statement with the ARCHIVELOG or NOARCHIVELOG clause. To change the archiving mode, you must be connected to the database with administrator privileges (AS SYSDBA).

    The following steps switch the database archiving mode from NOARCHIVELOG to ARCHIVELOG:

    1. Check if you are using an spfile . Then Shut down the database instance.

    show parameter spfile;

    An open database must first be closed and any associated instances shut down before you can switch the database archiving mode. You cannot change the mode from ARCHIVELOG to NOARCHIVELOG if any datafiles need media recovery.

    2. Edit the initialization parameter file to include the initialization parameters that specify the destinations for the archive log files
    (If you are using an spfile ,this is not required.

    3. Start a new instance and mount, but do not open, the database.


    To enable or disable archiving, the database must be mounted but not open.

    4. Change the database archiving mode. Then open the database for normal operations.


    b) In case we are using an spfile , change the LOG_ARCHIE_DEST_1 using :

    alter system set log_archive_dest_1='LOCATION=<location of archive logs>';

    c) Check and verify output of :

    archive log list;

    d) Issue the following and confirm that the archives are being generated at the archive destination:

    alter system archive log current;

    5. Shut down the database.


    6. Back up the database.

    Changing the database archiving mode updates the control file.
    After changing the database archiving mode, you must back up all of your database files and control file . as archives cannot be applied to the old backups which were taken earlier while database was in NOARCHIVELOG mode.
    Last edited by Hemant; 01-19-2012 at 07:32 PM.

+ Reply to Thread

Similar Threads

  1. The cloning of the database in noarchivelog mode
    By shoaibansari in forum Database Installation,Upgrades,Migration,Patching and Cloning
    Replies: 1
    Last Post: 07-20-2012, 01:03 PM
  2. Archivelog Mode On RAC
    By devesh_ocp in forum RAC Installation, ASM Install , ASM Administration
    Replies: 0
    Last Post: 04-25-2012, 03:28 PM
  3. What happens when we put either database or tablespace in begin backup mode
    By nemat in forum Backup, Restoration & Recovery, RMAN, Flashback, Datapump, Export, Import
    Replies: 0
    Last Post: 01-13-2012, 02:16 PM
  4. Changing the Archivelog Mode on 9i and earlier
    By Sandeep Sharma in forum Core Database Administration and Monitoring
    Replies: 3
    Last Post: 12-22-2011, 09:29 PM
  5. Changing Look and feel of Oracle Application
    By harsh in forum Concepts,Service Management,Apache,OC4J,OPMN,JServ,Forms, Issues,Troubleshooting,
    Replies: 0
    Last Post: 12-08-2011, 04:28 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

DBA Lounge (P) Ltd. deals in Oracle Technologies on Consulting, Resourcing, Corporate Training

Online and corporate training available on Oracle Database 11g, Oracle 11g Real Application Cluster (RAC), Oracle Applications 11i/R12, Oracle Fusion Middleware 11g, Oracle Identity Management-OIM, Oracle Internet Directory 11g-OID, Oracle Business Intelligence Enterprise Edition-OBIEE, Oracle Golden Gate, Oracle Access Management-OAM, Oracle Internet Directory-ODS, Oracle Identity Analytics Architecture-OIA Statistics