Daily Archives: February 9, 2011

How to Turn Archiving ON and OFF

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.

Oracle recommends ARCHIVELOG mode for all production databases

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:


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:

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

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.

SQL> shutdown immediateAn 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.

SQL> startup mountNOTE: 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. Put the database into archivelog mode

SQL> alter database archivelog; NOTE: You can also use

alter database noarchivelog to take the database out of archivelog mode

6. Open the database.

SQL> alter database open; 7. Verify your database is now in archivelog mode.

SQL> archive log listDatabase log mode Archive ModeAutomatic archival EnabledArchive destination USE_DB_RECOVERY_FILE_DESTOldest online log sequence 22Next log sequence to archive 24Current log sequence 248. Archive all your redo logs at this point.

SQL> archive log all; 9. Ensure these newly created Archive log files are added to the backup process.

Ref: Metalink ID 69739.1


What Happens When A Tablespace/Database Is Kept In Begin Backup Mode

To perform online/hot backup we have to put the tablespace in begin backup mode followed by copying the datafiles and then putting the tablespace to end backup.

In 8i, 9i we have to put each tablespace individually in begin/end backup mode to perform the online backup. From 10g onwards the entire database can be put in begin/end backup mode.

Make sure that the database is in archivelog mode

Example :

Performing a single tablespace backup

+ sql>alter tablespace system begin backup;
+ Copy the corresponding datafiles using appropriate O/S commands.
+ sql>alter tablespace system end backup;

Performing a full database backup (starting from 10g)

+ sql> alter database begin backup;
+ Copy all the datafiles using appropriate O/S commands.
+ sql> alter database end backup;

One danger in making online backups is the possibility of inconsistent data within a block. For example, assume that you are backing up block 100 in datafile users.dbf. Also, assume that the copy utility reads the entire block while DBWR is in the middle of updating the block. In this case, the copy utility may read the old data in the top half of the block and the new data in the bottom top half of the block. The result is called a fractured block, meaning that the data contained in this block is not consistent. at a given SCN.

Therefore oracle internally manages the consistency as below :

1. The first time a block is changed in a datafile that is in hot backup mode, the entire block is written to the redo log files, not just the changed bytes. Normally only the changed bytes (a redo vector) is written. In hot backup mode, the entire block is logged the first time. This is because you can get into a situation where the process copying the datafile and DBWR are working on the same block simultaneously.

Lets say they are and the OS blocking read factor is 512bytes (the OS reads 512 bytes from disk at a time). The backup program goes to read an 8k Oracle block. The OS gives it 4k. Meanwhile — DBWR has asked to rewrite this block. the OS schedules the DBWR write to occur right now. The entire 8k block is rewritten. The backup program starts running again (multi-tasking OS here) and reads the last 4k of the block. The backup program has now gotten an fractured block — the head and tail are from two points in time.

We cannot deal with that during recovery. Hence, we log the entire block image so that during recovery, this block is totally rewritten from redo and is consistent with itself atleast. We can recover it from there.

2. The datafile headers which contain the SCN of the last completed checkpoint are not updated while a file is in hot backup mode. This lets the recovery process understand what archive redo log files might be needed to fully recover this file.

To limit the effect of this additional logging, you should ensure you only place one tablepspace at a time in backup mode and bring the tablespace out of backup mode as soon as you have backed it up. This will reduce the number of blocks that may have to be logged to the minimum possible.

Ref : Metalink ID 469950.1