I am vistababa!

How to turn on and off the ARCHIVELOG mode in oracle?

with 11 comments

1. Turning on ARCHIVELOG mode:

We consider a database that is not in ARCHIVELOG mode and also automatic archival is not enabled. To see the status of the database we can use of the following SQL commands:

SQL> select log_mode from v$database;

LOG_MODE
————
NOARCHIVELOG

SQL> show parameter log_archive_start

NAME TYPE VALUE
—————– ——- —–
log_archive_start boolean FALSE
SQL>

Or

SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /archivelog
Oldest online log sequence 7193
Current log sequence 7194
SQL>

We can bring this database to ARCHIVELOG mode by using the following SQL commands:

SQL> shutdown immediate;
SQL> startup mount exclusive;
SQL> alter database archivelog;
SQL> alter database open;

Now we can see that this database is in ARCHIVELOG mode:

SQL> select log_mode from v$database;

LOG_MODE
———-
ARCHIVELOG

SQL>

But automatic archival is disabled yet and DBA must do manual archival by commands like the followings:

SQL> alter system archive log current;
SQL> alter system archive log all;

For enabling automatic archival we can use the following SQL commands:

SQL> alter system archive log start;

Or

SQL> archive log start;

Now we can see that automatic archival is enabled:

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /archivelog
Oldest online log sequence 7194
Next log sequence to archive 7195
Current log sequence 7195
SQL>

But this solution is not permanent and automatic archival will be disabled again after restarting the database; so for permanent change we must set parameter ‘log_archive_start’ to ‘TRUE’:

SQL> alter system set log_archive_start=TRUE scope=spfile;

And then restart the database.

Now the database is in ARCHIVELOG mode and also automatic archival is enabled.

Note1: We must take baseline backup right after enabling ARCHIVELOG mode.

Note2: After enabling ARCHIVELOG mode, it is better if we set the following init.ora parameters: log_archive_dest, log_archive_dest_1, log_archive_format.

2. Turning off ARCHIVELOG mode:

To see the status of the database we can use of the following SQL commands:

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /archivelog
Oldest online log sequence 7194
Next log sequence to archive 7195
Current log sequence 7195
SQL>

To disabling ARCHIVELOG mode we must run the following commands:

SQL> alter system set log_archive_start=False scope=spfile;
SQL> create pfile from spfile;
SQL> shutdown immediate;


SQL> startup mount excluseve;
SQL> alter database noarchivelog;
SQL> alter database open;

Now we can check the status of ARCHIVELOG mode:

SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /archivelog
Oldest online log sequence 7194
Current log sequence 7195
SQL>

Note1: After disabling ARCHIVELOG mode, all the un backed up archived log files are unusable and can be deleted.

References:
Managing Archived Redo Logs
What is an archived redo log file?
How to Turn Archiving ON and OFF?
How does one put a database into ARCHIVELOG mode?
Enabling ARCHIVELOG mode!
Configuring the Database Archiving Mode

About these ads

Written by Vistababa

June 12, 2008 at 4:58 pm

Posted in Oracle

11 Responses

Subscribe to comments with RSS.

  1. Nice HowTo!

    Eduardo

    January 21, 2009 at 8:52 pm

  2. Its really good one.

    jithender

    June 17, 2010 at 10:56 pm

  3. AWESOME!

    Fixed it for us.

    Only got a little problem:
    SQL> create pfile from spfile;

    Did not work, ORA-07392 FCLOSE error or something

    Thanks!

    Rouche

    August 6, 2010 at 12:25 pm

  4. Thanks very much. Even m a fresher thn also i dont have any doubt after reading ur concepts

    thnks alot

    Navneet Singh

    February 8, 2011 at 8:18 am

  5. It really help, Appreciate for the postings.

    Aravind

    March 24, 2011 at 4:07 am

  6. SQL> alter system set log_archive_start=TRUE scope=spfile;

    LOG_ARCHIVE_START
    This parameter is deprecated in Oracle 10g

    bad

    April 29, 2011 at 12:38 am

  7. Nice and short way to understand.

    Good examples. Expect more articles from you.

    Can you also guide about backup/restore using RMAN for 10G R2.

    Rajeev

    May 13, 2011 at 9:38 am

  8. really good post!

    semab

    September 29, 2011 at 4:29 pm

  9. good explanation, thanks,

    nvsr4u

    November 11, 2011 at 4:39 pm

  10. If you disable ARCHIVELOG, then the command: archive log list;
    keeps updating sequence number, whenever log file changes. Can this be disabled?
    Oldest online log sequence 7194
    Current log sequence 7195

    Alex

    February 18, 2013 at 2:53 pm

  11. I think the same as “bad” , LOG_ARCHIVE_START is deprecated in Oracle 10g.

    Mak

    April 5, 2013 at 4:14 pm


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: