HOW TO TURN ARCHIVING ON AND OFF???

Doc Id on Metalink 69739.1

Turning Archiving On and Off

You set a database's initial archiving mode as part of database creation.
Usually, you can use the default of NOARCHIVELOG mode at database creation because there is no need to archive the redo information generated at that time. After creating the database, decide whether to change from the initial archiving mode.

After a database has been created, you can switch the database's archiving mode on demand. However, you should generally not switch the database between archiving modes.

NOTE:

If a database is automatically created during Oracle installation, the initial archiving mode of the database is operating system specific. See your operating system-specific Oracle documentation.

ARCHIVELOG mode is necessary for creating on-line backups and for certain types of database recovery. Configuring the database to operate in ARCHIVELOG mode allows the user to perform complete and point-in-time recovery from media (disk) failures using off-line or on-line backups. If ARCHIVELOG mode is disabled, the database can be restored from a backup in case of failure, but it cannot be rolled forward from that to a point when failure occurred.

Setting the Initial Database Archiving Mode

When you create the database, you set the initial archiving mode of the redo log in the CREATE DATABASE statement. If you do not specify either ARCHIVELOG or NOARCHIVELOG, NOARCHIVELOG is the default. To verify database mode, execute following statement:

Select NAME,
CREATED,
LOG_MODE,
CHECKPOINT_CHANGE#,
ARCHIVE_CHANGE#
from V$DATABASE;

NAME CREATED LOG_MODE CHECKPOINT_CHANGE# ARCHIVE_CHANGE#
---- ----------------- ------------ --------------- --------------
ORCL 05/21/97 17:55:06 NOARCHIVELOG 7.7174E+12 7.7174E+12

Enabling Automatic Archiving at Instance Startup

To enable automatic archiving of filled groups each time an instance is started, include the "LOG_ARCHIVE_START" parameter, set to TRUE, in the database’s parameter file:

LOG_ARCHIVE_START=TRUE

The new value takes effect the next time you start the database.

Enabling Automatic Archiving After Instance Startup

To enable automatic archiving of filled online redo log groups without shutting down the current instance, use the SQL command ALTER SYSTEM with the ARCHIVE LOG START parameter.

The following statement enables archiving:

Oracle 7.x and Oracle8 / 8i

SVRMGRL> ALTER SYSTEM ARCHIVE LOG START;

Oracle 9.x (Logged in as "/ as SYSDBA")

SQL> ALTER SYSTEM ARCHIVE LOG START;

When using this option, the instance does not have to be shut down to enable automatic archiving.

However, if an instance is shutdown and restarted after automatic archiving is enabled using this option, the instance is reinitialized using the settings of the parameter file ("LOG_ARCHIVE_START"), which may or may not enable automatic archiving.

NOTE: On 9i, you can use an SPFILE instead of the standard pfile.

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

Changing the Database Archiving Mode

There are "init.ora" parameters you need to modify in order to properly handle your database being in archive log mode. They are:

LOG_ARCHIVE_START
LOG_ARCHIVE_DEST
LOG_ARCHIVE_FORMAT

LOG_ARCHIVE_START:
Enables automatic archiving of filled groups each time an instance is started.
Set this parameter to TRUE if you do NOT wish to have to MANUALLY archive your redo log files when in ARCHIVELOG mode.

LOG_ARCHIVE_DEST:
This parameter specifies the directory where your archive logs will be placed.

LOG_ARCHIVE_FORMAT:
This parameter names the archive logs in this format. For example, if your format is: arch%s.arc

Your log files will be called: arch1.arc, arch2.arc, arch3.arc where the '1', '2', '3', etc is the sequence number.

To Prepare to Switch Database Archiving Mode

1. Shut down the database instance.

Oracle 7.x and Oracle8 / 8i

SVRMGRL> shutdown

Oracle 9i (Logged in as "/ as SYSDBA")

SQL> shutdown

An open database must be closed and dismounted and any associated
instances shut down before the database's archiving mode can be switched.
Archiving cannot be disabled if any datafiles need media recovery.

2. Backup the database.

This backup can be used with the archive logs that you will generate.

3. Perform any operating system specific steps (optional).

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

Oracle 7.x and Oracle8 / 8i

SVRMGRL>startup mount

Oracle 9i (Logged in as "/ as SYSDBA")

SQL>startup mount

NOTE: If you are using the Oracle Parallel Server, you must mount the database exclusively using one instance to switch the database's archiving mode.

5. Switch the database's archiving mode.

Oracle 7.x and Oracle8 / 8i

SVRMGRL> alter database archivelog;

Oracle 9i

SQL> alter database archivelog;

NOTE: You can also use

alter database noarchivelog

to switch the database's archiving mode.

6. Open the database.

Oracle 7.x and Oracle8 / 8i

SVRMGRL> alter database open;

Oracle 9i

SQL> alter database open;

7. Verify your database is now in archivelog mode.

Oracle 7.x and Oracle8 / 8i

SVRMGRL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination for example: $ORACLE_HOME/dbs/arch
Oldest on-line log sequence 275
Next log sequence 277
Current log sequence 278

Oracle 9i

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination for example: $ORACLE_HOME/dbs/arch
Oldest on-line log sequence 275
Next log sequence 277
Current log sequence 278

8. Archive all your redo logs at this point.

Oracle 7.x and Oracle8 / 8i

SVRMGRL> archive log all;

Oracle 9i

SQL> archive log all;

9. Ensure these newly created Archive log files are added to the backup process.

See the Administration guide & Backup and Recovery guide for more information about switching the archiving mode when using the Oracle Parallel Server.

Disabling Automatic Archiving

You can disable automatic archiving of the online redo log groups at any time.
However, once automatic archiving is disabled, you must manually archive groups of online redo log files in a timely fashion.

If a database is operated in ARCHIVELOG mode but automatic archiving is disabled, all groups of online redo log files are filled but not archived.

LGWR cannot reuse any inactive groups of online redo log groups to continue writing redo log entries. Therefore, database operation is temporarily suspended until the necessary archiving is performed.

To disable automatic archiving after instance startup, you must be connected with administrator privileges and have the ALTER SYSTEM privilege.

Automatic archiving can be disabled at or after instance startup.

Disabling Automatic Archiving at Instance Startup

To disable the automatic archiving of filled online redo log groups each time a
database instance is started, set the "LOG_ARCHIVE_START" parameter of a
database's parameter file to FALSE:

LOG_ARCHIVE_START=FALSE

NOTE: With Oracle9, you can use an SPFILE instead of a standard pfile.

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

The new value takes effect the next time the database is started. The archive
log list; command executed from SVRMGRL will show:

Automatic archival Disabled

Disabling Automatic Archiving after Instance Startup

To disable the automatic archiving of filled online redo log groups without
shutting down the current instance, use the SQL command ALTER SYSTEM
with the ARCHIVE LOG STOP parameter.

The following statement stops archiving:

Oracle 7.x and Oracle8 / 8i

SVRMGRL> ALTER SYSTEM ARCHIVE LOG STOP;

Oracle 9i

SQL> ALTER SYSTEM ARCHIVE LOG STOP;

If ARCH is archiving a redo log group when you attempt to disable automatic archiving, ARCH finishes archiving the current group, but does not begin archiving the next filled online redo log group.

The instance does not have to be shut down to disable automatic archiving. However, if an instance is shut down and restarted after automatic archiving is disabled, the instance is reinitialized using the settings of the parameter file ("LOG_ARCHIVE_START"), which may or may not enable automatic archiving. NOTE: If you choose to disable automatic archiving and have not
disabled archiving altogether, you are responsible to archive all filled redo log groups or else database operation is temporarily suspended (you will experience a database hang)
until the necessary archiving is performed.

Performing Manual Archiving

If a database is operating in ARCHIVELOG mode, inactive groups of filled online redo log files must be archived. You can manually archive groups of the online redo log whether or not automatic archiving is enabled.

If automatic archiving is not enabled, you must manually archive groups of filled online redo log files in a timely fashion. If all online redo log groups are filled but not archived, LGWR cannot reuse any inactive groups of online redo log members to continue writing redo log entries. Therefore, database operation is temporarily suspended until the necessary archiving is performed. You can exercise this scenario by executing alter system switch logfile command when automatic archival is disabled. Attempting to repeat that command with a last redo log group will show hang, and it won?t be completed with ?statement processed? message until archiving is done.

If automatic archiving is enabled, but you want to rearchive an inactive group of filled online redo log members to another location, you can use manual archiving. (However, the instance can decide to reuse the redo log group before you have finished manually archiving, thereby overwriting the files. If this happens, Oracle will display an error message in the ALERT file.)

To manually archive a filled online redo log group, you must be connected with administrator privileges.

Manually archive inactive groups of filled online redo log members using the SQL command:

Oracle 7.x and Oracle8 / 8i

SVRMGRL> ALTER SYSTEM ARCHIVE LOG ALL;

Oracle 9i

SQL> ALTER SYSTEM ARCHIVE LOG ALL;

archives all unarchived log files.