oracle存档模式,oracle归档模式管理

很少看到production

DB是处于非归档模式的，Oracle中的一些重要特性只能当数据库处于归档模式时才能使用，例如Flashback Database,PITR恢复等，归档模式下，用户的数据在数据库中存了两份：一份在数据文件中，另一份存在归档日志中。本文主要介绍如何查看数据库是否处于归档模式、如何相关参数和手动将数据库至于归档模式下。

注：相关实验都是在单实例的DB上进行的，

oracle 11.2.0.4 64

bit on Redhat 6.5 64 bit;

oracle 11.2.0.3 64

bit on AIX 6.1 64 bit

1.查看数据库是否开启归档模式：

使用sqlplus连接到数据库，然后输入命令archive log list

Database log mode是Archive mode还是No Archive mode就可以判断其是否开启归档模式

sqlplus / as

sysdba

SQL*Plus: Release

11.2.0.4.0 Production on Thu Jun 19 04:14:03 2014

Copyright (c)

1982, 2013, Oracle.All rights reserved.

Connected to:

Oracle Database

11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the

Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real

Application Testing options

sys@ANDREW>archive log list

Database log modeArchive Mode

Automatic archivalEnabled

Archive destinationUSE_DB_RECOVERY_FILE_DEST

Oldest online log sequence103

Next log sequence

to archive108

Current log

sequence108

sys@ANDREW>

11:56:14

ora11g@resotest3:/home/ora11g

$ sqlplus / as

sysdba

SQL*Plus: Release

11.2.0.3.0 Production on Thu Jun 19 11:56:19 2014

Copyright (c)

1982, 2011, Oracle.All rights reserved.

Connected to:

Oracle Database

11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the

Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

archive log list

Database

log modeNo Archive Mode

Automatic

archivalDisabled

Archive

destination/u14/app/product/11g/db/dbs/arch

Oldest online log

sequence1038

Current log

sequence1040

SQL>

2.配置ARCHIVELOG模式

2.1 DBCA设置归档模式

在使用DBCA创建数据库的时候，可以勾选复选框将数据库至于归档模式下，同时修改归档格式archive_log_format,如下图所示:

archive_log_format一般设置为%t_%s_%r.dbf

当设置Fast Recovery AREA时，如果不显式设置参数archive_log_dest,那么归档日志文件将默认存储在FRA目录下，当然也可以显式设置非默认归档路径，如上图所示。

2.2从非归档模式转到归档模式

在操作之前，需要设置下相关参数：归档日志格式和归档路径

archive_log_format:%t_%s_%.arch

archive_log_dest/archive_log_dup_dest:只能设置为本地磁盘,local

archive_log_dest_n(1~31)

:可将归档日志写入到本地和远程机器上

archive_log_dest_n_status=enable(默认 enable)

archive_log_dest_n和archive_log_dest/archive_log_dup_dest不能同时使用.设置好相关参数之后进行如下操作:

SQL> SHUTDOWN IMMEDIATE

SQL> STARTUP MOUNT

SQL> ALTER DATABASE ARCHIVELOG;

SQL> ALTER DATABASE OPEN;

数据库处于NOARCHIVELOG模式时，只能恢复到最后一次备份时的状态,在该备份之后执行的所有事务处理都会丢失，用户需要重新输入所有丢失的数据。在ARCHIVELOG模式下，可一直恢复到最后一次提交时的状态,多数生产数据库都在ARCHIVELOG模式下运行。

3．配置归档路径

Archive destinationUSE_DB_RECOVERY_FILE_DEST

Archive destination/u14/app/product/11g/db/dbs/arch

USE_DB_RECOVERY_FILE_DEST表明归档日志写入DB的FRA路径下

通过下述参数修改指定本地存储位置

Alter system set LOG_ARCHIVE_DEST= 'LOCATION=/oradb/arch'

Alter system set LOG_ARCHIVE_dup_DEST= 'LOCATION=/dbback/arch'

或者都是使用

Alter system set LOG_ARCHIVE_DEST_2= 'LOCATION=/oradb/arch'

Alter system set LOG_ARCHIVE_DEST_1= 'LOCATION=/dbback/arch'

如果要将归档日志远程主机位置则需要使用SERVICE关键字

Alter system set LOG_ARCHIVE_DEST_2 = 'SERVICE=standby1'.

建议使用LOG_ARCHIVE_DEST_n来设置数据库的归档日志路径

3.1指定MANDATORY和OPTIONAL

当定义了多个归档路径时候，可以指定一部分目标为必需目标。其他路径为可选的。参数MANDATORY或OPTIONAL关键字可实现上述目的,默认值为OPTIONAL:

LOG_ARCHIVE_DEST_1 = 'LOCATION=/dbback/arch MANDATORY'

LOG_ARCHIVE_DEST_2 = 'LOCATION=/oradb/arch OPTIONAL’

Metalink上有篇文档介绍如何开启和关闭归档模式的，转来看看(英语简单)

How to Turn Archiving ON and OFF in Oracle RDBMS (Doc ID 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.

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:

SQL> Select NAME, CREATED, LOG_MODE,

CHECKPOINT_CHANGE#, ARCHIVE_CHANGE#

from

V$DATABASE

NAME CREATED LOG_MODE CHECKPOINT_CHANGE# ARCHIVE_CHANGE#

--------- --------- ------------ ------------------

---------------

O112 19-MAR-09 NOARCHIVELOG 1438426 135961

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_DEST

LOG_ARCHIVE_FORMAT

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.

SQL> shutdown immediate

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.

SQL> startup

mount

NOTE: If you

are using the Real Application Cluster (RAC), then 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

SQL> 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 list

Database log

modeArchive Mode

Automatic

archivalEnabled

Archive

destinationUSE_DB_RECOVERY_FILE_DEST

Oldest online

log sequence22

Next log

sequence to archive24

Current log

sequence24

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

See the Administration guide & Backup and Recovery

guide for more information about switching the archiving mode when using the

Real Application Cluster (RAC).