1、概念

歸檔是將聯(lián)機(jī)重做日志以文件的形式保存到硬盤(pán)，聯(lián)機(jī)日志歸檔的前提條件是數(shù)據(jù)庫(kù)要處于歸檔模式。當(dāng)數(shù)據(jù)庫(kù)處于 ARCHIVELOG 模式并進(jìn)行日志切換時(shí)，后臺(tái)進(jìn)程 ARCH 會(huì)將聯(lián)機(jī)重做日志的內(nèi)容保存到歸檔日志中，當(dāng)數(shù)據(jù)庫(kù)出現(xiàn)介質(zhì)故障時(shí)，使用數(shù)據(jù)文件備份、歸檔日志和聯(lián)機(jī)重做日志可以完全恢復(fù)數(shù)據(jù)庫(kù)到正常狀態(tài)。

Oracle 數(shù)據(jù)庫(kù)設(shè)置為歸檔模式之后，如果歸檔空間寫(xiě)滿(mǎn)，數(shù)據(jù)庫(kù)的 redo 文件不能歸檔，會(huì)出現(xiàn)數(shù)據(jù)庫(kù)掛起的問(wèn)題，導(dǎo)致 數(shù)據(jù)庫(kù)無(wú)法使用。

2、查看數(shù)據(jù)庫(kù)是否為歸檔模式

SQL> archive log list;Database log mode Archive ModeAutomatic archival EnabledArchive destination /app/archiveOldest online log sequence 30Next log sequence to archive 34Current log sequence 34SQL> select log_mode from v$database;LOG_MODE------------------------------------ARCHIVELOG

3、打開(kāi)或關(guān)閉歸檔模式

如果需要打開(kāi)或關(guān)閉歸檔模式，在數(shù)據(jù)庫(kù)處于 mount 狀態(tài)時(shí)使用如下命令：

3.1 打開(kāi)歸檔模式

alter database archivelog;

3.2 關(guān)閉歸檔模式

alter database noarchivelog;

4、配置歸檔的位置

當(dāng)數(shù)據(jù)庫(kù)處于歸檔模式時(shí)，進(jìn)行日志切換時(shí)后臺(tái)進(jìn)程將自動(dòng)生成歸檔日志。

4.1 查看歸檔進(jìn)程數(shù)

初始化參數(shù) LOG_ARCHIVE_MAX_PROCESSES 用于指定最大歸檔進(jìn)程個(gè)數(shù)。通過(guò)改變?cè)摮跏蓟瘏?shù)的取值，可以動(dòng)態(tài)地增加或減少歸檔進(jìn)程的個(gè)數(shù)。

SQL> show parameter LOG_ARCHIVE_MAX_PROCESSES;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_max_processes            integer     4

4.2 配置歸檔的位置

可以使用 LOG_ARCHIVE_DEST_n 配置多個(gè)歸檔位置：該參數(shù)最多可以指定 10 個(gè)歸檔位置。格式如下：

ALTER SYSTEM SET log_archive_dest_1 = 'location = 路徑1';
ALTER SYSTEM SET log_archive_dest_2 = 'location = 路徑2';
.....

4.2.1 查看歸檔文件

SQL> select sequence#, name from v$archived_log;
SEQUENCE#  NAME
---------- --------------------------------------------------------------------------------
34 /app/archive/orcl_62725153_0000000034_1_1125947150.dbf

可知?dú)w檔的文件位置 /app/archive/

4.2.2 修改歸檔日志的位置為：/home/oracle/archivelog

SQL> alter system set log_archive_dest_1 = 'location=/app/archive' scope = both sid='*';
SQL> archive log list;
SQL> alter system switch logfile;
SQL> select sequence#, name from v$archived_log;
SQL> select sequence#, name from v$archived_log;
SEQUENCE#  NAME
---------- --------------------------------------------------------------------------------
34 /app/archive/orcl_62725153_0000000034_1_1125947150.dbf
35 /app/archive/orcl_62725153_0000000035_1_1125947150.dbf

5、配置歸檔日志文件的名稱(chēng)格式

初始化參數(shù) LOG_ARCHIVE_FORMAT 用于指定歸檔日志文件的名稱(chēng)格式。設(shè)置該初始化參數(shù)時(shí)，可以指定以下匹配符：

（1）%s：日志序列號(hào)；

（2）%S：日志序列號(hào)（帶有前導(dǎo) 0）；

（3）%t：線(xiàn)程編號(hào)；

（4）%T：線(xiàn)程編號(hào)（帶有前導(dǎo) 0）；

（5）%a：活動(dòng) ID 號(hào)；

（6）%d：數(shù)據(jù)庫(kù) ID 號(hào)；

（7）%r：RESETLOGS的ID值。

歸檔日志的文件名稱(chēng)格式設(shè)置如下：

SQL> alter system set LOG_ARCHIVE_FORMAT = 'orcl_%d_%S_%t_%r.dbf' scope = spfile sid='*';

重啟數(shù)據(jù)庫(kù)使設(shè)置生效

SQL> shutdown immediate
SQL> startup

查看參數(shù) LOG_ARCHIVE_FORMAT 的值：

SQL> show parameter LOG_ARCHIVE_FORMAT
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_format                   string      orcl_%d_%S_%t_%r.dbf

查看歸檔日志：

SQL> select sequence#, name from v$archived_log;

6、刪除歸檔日志

6.1 手工刪除歸檔日志文件

[oracle@node1 archive]$ cd /app/archive/
[oracle@node1 archive]$ ll -r
總用量 4516
-rw-r----- 1 oracle oinstall   10752 2月   2 10:54 orcl_62725153_0000000036_1_1125947150.dbf
-rw-r----- 1 oracle oinstall   54784 2月   2 10:51 orcl_62725153_0000000035_1_1125947150.dbf
-rw-r----- 1 oracle oinstall 4551680 2月   2 10:46 orcl_62725153_0000000034_1_1125947150.dbf
[oracle@node1 archive]$ rm -rf orcl_62725153_0000000034_1_1125947150.dbf
[oracle@node1 archive]$ ll
總用量 68
-rw-r----- 1 oracle oinstall 54784 2月   2 10:51 orcl_62725153_0000000035_1_1125947150.dbf
-rw-r----- 1 oracle oinstall 10752 2月   2 10:54 orcl_62725153_0000000036_1_1125947150.dbf

查看歸檔日志：

SQL> select sequence#, name from v$archived_log;
SEQUENCE#  NAME
---------- --------------------------------------------------------------------------------
34 /app/archive/orcl_62725153_0000000034_1_1125947150.dbf
35 /app/archive/orcl_62725153_0000000035_1_1125947150.dbf
36 /app/archive/orcl_62725153_0000000036_1_1125947150.dbf

雖然物理文件已經(jīng)刪除，但是歸檔日志文件的信息還在。

6.2 用 RMAN 刪除數(shù)據(jù)庫(kù)記錄的歸檔列表信息

[oracle@node1 ~]$ rman target /

RMAN> list archivelog all;

List of Archived Log Copies for database with db_unique_name ORCL
=====================================================================

Key     Thrd Seq     S Low Time 
------- ---- ------- - ---------
36      1    34      X 02-FEB-23
        Name: /app/archive/orcl_62725153_0000000034_1_1125947150.dbf

37      1    35      A 02-FEB-23
        Name: /app/archive/orcl_62725153_0000000035_1_1125947150.dbf

38      1    36      A 02-FEB-23
        Name: /app/archive/orcl_62725153_0000000036_1_1125947150.dbf


RMAN> crosscheck archivelog all;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1996 device type=DISK
validation failed for archived log
archived log file name=/app/archive/orcl_62725153_0000000034_1_1125947150.dbf RECID=36 STAMP=1127731566
validation succeeded for archived log
archived log file name=/app/archive/orcl_62725153_0000000035_1_1125947150.dbf RECID=37 STAMP=1127731881
validation succeeded for archived log
archived log file name=/app/archive/orcl_62725153_0000000036_1_1125947150.dbf RECID=38 STAMP=1127732067
Crosschecked 3 objects

RMAN> delete expired archivelog all;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1996 device type=DISK
List of Archived Log Copies for database with db_unique_name ORCL
=====================================================================

Key     Thrd Seq     S Low Time 
------- ---- ------- - ---------
36      1    34      X 02-FEB-23
        Name: /app/archive/orcl_62725153_0000000034_1_1125947150.dbf


Do you really want to delete the above objects (enter YES or NO)? yes
deleted archived log
archived log file name=/app/archive/orcl_62725153_0000000034_1_1125947150.dbf RECID=36 STAMP=1127731566
Deleted 1 EXPIRED objects
RMAN> select sequence#, name from v$archived_log;select sequence#, name from v$archived_log;
SEQUENCE#  NAME
---------- --------------------------------------------------------------------------------
34 
35 /app/archive/orcl_62725153_0000000035_1_1125947150.dbf
36 /app/archive/orcl_62725153_0000000036_1_1125947150.dbf

6.3 清除 v$archived_log 視圖中的過(guò)期信息

使用 RMAN 命令刪除歸檔后，v$archived_log 視圖中的 name 列為空，但其他列的信息仍然存在。出現(xiàn)這種現(xiàn)象的原因是因?yàn)槭褂?RMAN 命令在刪除歸檔日志的時(shí)候不能夠清除控制文件中的內(nèi)容。文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-516418.html

6.3.1 清除控制文件中關(guān)于 v$archived_log 的信息

SQL> execute sys.dbms_backup_restore.resetCfileSection(11);
PL/SQL procedure successfully completed.

6.3.2 查詢(xún)發(fā)現(xiàn) v$archived_log 視圖中的信息全部被清除了

SQL> select sequence#, name from v$archived_log;
 SEQUENCE# NAME
---------- --------------------------------------------------------------------------------

6.3.3 將未過(guò)期的歸檔文件信息重新注冊(cè)到控制文件中

RMAN> catalog start with '/app/archive/';

using target database control file instead of recovery catalog
searching for all files that match the pattern /app/archive/

List of Files Unknown to the Database
=====================================
File Name: /app/archive/orcl_62725153_0000000035_1_1125947150.dbf
File Name: /app/archive/orcl_62725153_0000000036_1_1125947150.dbf

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /app/archive/orcl_62725153_0000000035_1_1125947150.dbf
File Name: /app/archive/orcl_62725153_0000000036_1_1125947150.dbf
RMAN> select sequence#, name from v$archived_log;
 SEQUENCE# NAME
---------- --------------------------------------------------------------------------------
        35 /app/archive/orcl_62725153_0000000035_1_1125947150.dbf
        36 /app/archive/orcl_62725153_0000000036_1_1125947150.dbf