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

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

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/，如需轉載，請註明出處，否則將追究法律責任。

oracle歸檔模式管理

相關文章