OracleDB入门2：归档模式

前言

         某天，与ORACLE服务器对接的一个应用系统（ORACLE 客户端）突然不能用，起初还以为是有人恶意删文件，后来才发现是乌龙一场。原因是ORACLE服务器端磁盘爆满，online redo log无法完成归档，因而无法处理新的连接。从而，由问题引申出本文的内容：ORACLE日志模式。

概念

         前文《OracleDB入门1：体系结构--1存储体系》中对ORACLE的物理存储体系有了大概的介绍，其中最重要文件有以下几种：参数文件（pfile、spfile），控制文件（control file）、数据文件（datafile）、联机重做日志文件（online redo log file）、归档日志文件（archive redo log file）。

联机重做日志online redo log

以下是ORACLE官方文档对online redo log的解释：

     The most crucial structure for recovery operations is the redo log, which consists of two or more preallocated files that store all changes made to the database as they occur. Every instance of an Oracle Database has an associated redo log to protect the database in case of an instance failure。

         ORACLE恢复操作中最重要的结构为：redo log（重做日志）。它包含两个或多个预分配的文件用于存储所有导致数据库修改的事件。每一个Oracle数据库实例有一个关联的redo log以便保护数据库实例崩溃。

Redo log files are filled with redo records.

    重做日志文件记载着重做记录；

A redo record, also called a redo entry, is made up of a group of change vectors, each of which is a description of a change made to a single block in the database.

一个重做记录（重做项）由一组变更向量组成，每个变更向量描述一个导致数据库块改变的变更事件。

Redo records are buffered in a circular fashion in the redo log buffer of the SGA and are written to one of the redo log files by the Log Writer (LGWR) database background process. Whenever a transaction is committed, LGWR writes the transaction redo records from the redo log buffer of the SGA to a redo log file, and assigns a system change number (SCN) to identify the redo records for each committed transaction. Only when all redo records associated with a given transaction are safely on disk in the online logs is the user process notified that the transaction has been committed.

重做记录以循环模式缓存在重做缓冲区中（redo log buffer），由后台进程LGWR负责将缓存区内容写进磁盘文件。当一个事务提交，LGWR进程将事务重做记录写进重做日志，并赋予一个SCN（system change number，系统变更号）以作区分。仅当ORACLE认为事务对应的重做记录被安全的写进磁盘后，才会通知事务已全部commit完成。

归档重做日志archive redo log

         前文说过，ORACLE预分配两个或多个文件用于记录重做记录（redo record），当某个onlie redo log file写满后，切换到另外一个继续写。此时就引申出NOARCHIVELOG/ARCHIVELOG mode的概念：

         若数据库处于非归档模式NOARCHIVELOG mode, ORACLE依次写满所有的online log file后，重新循环覆盖写online log file。示意图如下：

         若数据库处于ARCHIVELOG mode，某个重做日志写满后，由后台进程ARCn负责将重做日志文件归档，仅当ARCn进程确保online redo log归档成功，此online redo log才能再次被写。

相关操作

查看归档模式

SYS@T24APDB> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     204
Next log sequence to archive   205
Current log sequence           205

查看online log file（v$logfile）

SYS@T24APDB> SELECT member FROM v$logfile;

MEMBER
--------------------------------------------------------------------------------
/data/T24APDB/online/redolog11.dbf
/data/T24APDB/online/redolog13.dbf
/data/T24APDB/online/redolog15.dbf
/data/T24APDB/online/redolog17.dbf
/data/T24APDB/online/redolog19.dbf
/data/T24APDB/online/redolog20.dbf
/data/T24APDB/online/redolog22.dbf
/data/T24APDB/online/redolog23.dbf

8 rows selected.

切换归档模式

 

SYS@T24APDB> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SYS@T24APDB> startup mount;
ORACLE instance started.
Total System Global Area 3305111552 bytes
Fixed Size                  2929792 bytes
Variable Size            1912605568 bytes
Database Buffers         1375731712 bytes
Redo Buffers               13844480 bytes
Database mounted.

SYS@T24APDB> alter database archivelog;
Database altered.

SYS@T24APDB> alter database open;
Database altered.

SYS@T24APDB> alter database close;
Database altered.

SYS@T24APDB> alter database noarchivelog;
Database altered.

SYS@T24APDB> alter database open;
alter database open
*
ERROR at line 1:
ORA-16196: database has been previously opened and closed
#####解释：数据库close后不能重新open，只能shutdown 后再startup open；

SYS@T24APDB> startup open;
ORA-01081: cannot start already-running ORACLE - shut it down first
SYS@T24APDB> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.

SYS@T24APDB> startup open;
ORACLE instance started.
Total System Global Area 3305111552 bytes
Fixed Size                  2929792 bytes
Variable Size            1912605568 bytes
Database Buffers         1375731712 bytes
Redo Buffers               13844480 bytes
Database mounted.
Database opened.

SYS@T24APDB> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     204
Current log sequence           205
SYS@T24APDB>

说明：

1. 之所以做一堆shutdown、startup操作，是为了确保数据库处于非open状态—也就是说，仅当数据库处于非open(如mount)状态时才允许修改归档模式；
2. archive log list;用于显示归档模式。其中Archive destination为归档目的路径的参数，后续可以用show parameter查看；

查看归档路径

SYS@T24APDB> show parameter DB_RECOVERY_FILE_DEST;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /data/fast_recovery_area
db_recovery_file_dest_size           big integer 100G
SYS@T24APDB>

总结

         Oracle庞大的知识体系，边学边干，边干边学。这不，今天又掌握一点：归档模式。