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 mode           Archive Mode

Automatic archival          Enabled

Archive destination         USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     103

Next log sequence to archive   108

Current log sequence        108

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 mode              No Archive Mode

Automatic archival             Disabled

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

Oldest online log sequence     1038

Current log sequence           1040

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 destination    USE_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 mode              Archive Mode

    Automatic archival             Enabled

    Archive destination            USE_DB_RECOVERY_FILE_DEST

    Oldest online log sequence     22

    Next log sequence to archive   24

    Current log sequence           24

 

    

 

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

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/21754115/viewspace-1189321/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/21754115/viewspace-1189321/