Archive Log Mode

Details
Category: DBA

User Rating: 1 / 5

Star ActiveStar InactiveStar InactiveStar InactiveStar Inactive
 

The process of turning redo log files into archived redo log files is called archiving. It is only possible if the database is running in ARCHIVELOG mode. Here are some advantages to be in ARCHIVELOG mode:

  • Recover a database
  • Update a standby database
  • Perform a hot backup

In this article ...
... Oracle 10g and Oracle 11g are concerned.
... a database SHUTDOWN is required to enable/disable Archive Log.
... SYSTEM privileges is necessary to execute commands. Otherwise, you may get some errors.

Archive log status

Before starting, you should check your db instance archive log status in order to know if your instance already is in archive log mode or not.You have different ways to see that:

-- Show archive log status

ARCHIVE LOG LIST ;
/*
Database log mode            No Archive mode
Automatic archival           Disabled
Archive destination          USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence   2186
Current log sequence         2188
*/

SELECT LOG_MODE FROM SYS.V$DATABASE ;
/*
LOG_MODE
------------
NOARCHIVELOG
*/

As you can see from the result here, the instance is definately not in Archive Log mode.

Enable archive log

Careful ...
... It is recommanded to perform a backup of your instance before doing these changes.

You can let the default parameters to get the archive log works. Otherwise if you want to personalize the archive log parameters, you may want to check how to change the different parameters here first.

We will now enable the archive log for the instance as following:

-- Enable archive log

SHUTDOWN IMMEDIATE ;

STARTUP MOUNT ;

ALTER DATABASE ARCHIVELOG ;

ALTER DATABASE OPEN ;

Let's check the archive log mode information again:

ALTER SYSTEM ARCHIVE LOG LIST ;
/*
Database log mode            Archive mode
Automatic archival           Enabled
Archive destination          /u01/app/oracle/oradata/orcl/arch
Oldest online log sequence   2186
Current log sequence         2188
*/

SELECT LOG_MODE FROM SYS.V$DATABASE ;
/*
LOG_MODE
------------
ARCHIVELOG
*/

Here we go, we are in archive log mode now!

Do you know?
Before Oracle 10g, the following commands should be executed in order to automaticaly enable automatic archiving :
     ALTER SYSTEM ARCHIVE LOG START ;
     ALTER SYSTEM SET LOG_ARCHIVE_START=TRUE SCOPE=SPFILE ;
This is now deprecated on Oracle 10g and onward.

Disable archive log

In case of, you really need to turn off Archive Log for any reasons:

-- Disable archive log

SHUTDOWN IMMEDIATE ;

STARTUP MOUNT ;

ALTER DATABASE NOARCHIVELOG ;

ALTER DATABASE OPEN ;

The instance should now be in NO ARCHIVE LOG mode.

Going futher ...

Archive log parameters

Careful ...
... some parameter changes may require to restart the db instance.

Change archive log destination

By default Archive destination shows USE_DB_RECOVERY_FILE_DEST, it means archive logs will be written to the flash recovery area path define by RECOVERY_FILE_DEST parameter. But if LOG_ARCHIVE_DEST_n is set then it will define the location where the archive logs will be written.

-- Show archive log status (before)

ALTER SYSTEM ARCHIVE LOG LIST ;
/*
Database log mode            No Archive mode
Automatic archival           Disabled
Archive destination          USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence   2186
Current log sequence         2188
*/

-- Get flash recovery parameters

SHOW PARAMETER recovery_file_dest ;
/*
NAME                            TYPE        VALUE
------------------------------- ----------- -----------------------------------
db_recovery_file_dest           string      /u01/app/oracle/flash_recovery_area
db_recovery_file_dest_size      big integer 4096M
*/

-- Set log_archive_dest_1 parameter

ALTER SYSTEM SET log_archive_dest_1='LOCATION=/u01/app/oracle/oradata/orcl/arch' SCOPE=BOTH ;
/*
System altered.
*/

-- Show archive log status (after)

ALTER SYSTEM ARCHIVE LOG LIST ;
/*
		Database log mode            No Archive mode
		Automatic archival           Disabled
		Archive destination          /u01/app/oracle/oradata/orcl/arch
		Oldest online log sequence   2186
		Current log sequence         2188
*/

For more information, please check oracle documentation here.

Others useful archive log parameters

Here are some more parameters, you can be interrested in.

LOG_ARCHIVE_FORMAT specifies the default filename format when archiving redo log files.

  • The following variables can be used in the format:
    • %s log sequence number
    • %S log sequence number, zero filled
    • %t thread number
    • %T thread number, zero filled
    • %a activation ID
    • %d database ID
    • %r resetlogs ID that ensures unique names are constructed for the archived log files across multiple incarnations of the database
  • Example: 
     ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='log%t_%s_%r.arc'SCOPE=BOTH ;

LOG_ARCHIVE_MAX_PROCESSES specifies the maximum number of ARCn processes that can be created.

Enable archive log with manual archiving

It is not recommanded so use it if you really know what you are doing.

-- Enable archive log with manual archiving

SHUTDOWN IMMEDIATE ;

STARTUP MOUNT ;

ALTER DATABASE ARCHIVELOG MANUAL;

ALTER DATABASE OPEN ;

-- The following can be used in automatic archiving as well
-- Manually switch online rego log and archive all unarchived log files

ALTER SYSTEM SWITCH LOGFILE ;
ALTER SYSTEM ARCHIVE LOG ALL ;

Careful ...
If all your online redo logs are filled up, then this mode will hang your database until you manually archive one of them at least.

For more information, please check oracle documentation here.

Some useful queries

-- Daily archives log size generated

SELECT TRUNC(next_time) "Date", ROUND(SUM(blocks*block_size/1024/1024)) Mo
FROM v$archived_log
GROUP BY TRUNC(next_time)
ORDER BY 1 DESC ;
/*
Date             MO
-------- ----------
23/02/12        102
22/02/12        132
21/02/12        127
20/02/12        129
*/

Archive log views

Several dynamic performance views contain useful information about archived redo logs, as summarized in the following table.

Dynamic Performance View Description
V$DATABASE Shows if the database is in ARCHIVELOG or NOARCHIVELOG mode and if MANUAL (archiving mode) has been specified.
V$ARCHIVED_LOG Displays historical archived log information from the control file. If you use a recovery catalog, the RC_ARCHIVED_LOG view contains similar information.
V$ARCHIVE_DEST Describes the current instance, all archive destinations, and the current value, mode, and status of these destinations.
V$ARCHIVE_PROCESSES Displays information about the state of the various archive processes for an instance.
V$BACKUP_REDOLOG Contains information about any backups of archived logs. If you use a recovery catalog, the RC_BACKUP_REDOLOG contains similar information.
V$LOG Displays all redo log groups for the database and indicates which need to be archived.
V$LOG_HISTORY Contains log history information such as which logs have been archived and the SCN range for each archived log.

TODO ...
- Check all commands

References ...

Enjoyed this article? Please like it or share it.

Add comment

Please connect with one of social login below (or fill up name and email)

     


Security code
Refresh

Comments   

Christiane
# Archive Log Mode - UxOra — Christiane 2016-07-11 13:24
Tout est bien expliqué dans cet article, félicitations aux rédacteurs
Reply | Reply with quote | Quote