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.
|