归档日志管理

数据环境： SQL select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production PL/SQL Release 11.2.0.1.0 - Production CO

数据环境：

SQL> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production PL/SQL Release 11.2.0.1.0 - Production CORE 11.2.0.1.0 Production TNS for Linux: Version 11.2.0.1.0 - Production NLSRTL Version 11.2.0.1.0 - Production 

1. 归档日志概念

什么是归档重做日志：An archived redo log file is a copy of one of the filled members of a redo log group. It includes the redo entries and the unique log sequence number of the identical member of the redo log group. For example, if you are multiplexing your redo log, and if group 1 contains identical member files a_log1 andb_log1, then the archiver process (ARC<em>n</em>) will archive one of these member files. Should a_log1 become corrupted, then ARC<em>n</em> can still archive the identical b_log1. The archived redo log contains a copy of every group created since you enabled archiving.归档重做日志：就是在归档模式下，对非活动重做日志进行备份。有后台ARCn进程进行归档备份

[oracle@localhost orcl]$ ps -ef|grep arcoracle 3835 1 0 01:26 ? 00:00:01 ora_arc0_orcloracle 3837 1 0 01:26 ? 00:00:01 ora_arc1_orcloracle 3839 1 0 01:26 ? 00:00:01 ora_arc2_orcloracle 3841 1 0 01:26 ? 00:00:00 ora_arc3_orcl

2.  归档和非归档模式切换

模式切换需要在mount状态才可切换

SQL> startup mount;

SQL> archive log list;

Database log mode	       Archive Mode

Automatic archival	       Enabled

Archive destination	       USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     92

Next log sequence to archive   94

Current log sequence	       94

归档模式切换到非归档模式：

SQL> alter database noarchivelog;
Database altered.

SQL> archive log list;

Database log mode	       No Archive Mode

Automatic archival	       Disabled

Archive destination	       USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     92

Current log sequence	       94

非归档模式切换到归档模式:

SQL> alter database archivelog;

Database altered.

SQL> archive log list;

Database log mode	       Archive Mode

Automatic archival	       Enabled

Archive destination	       USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     93

Next log sequence to archive   95

Current log sequence	       95

如果非归档模式切换到归档模式，报ORA-00265: instance recovery required, cannot set ARCHIVELOG mode

SQL> alter database archivelog;

alter database archivelog

*

ERROR at line 1:

ORA-00265: instance recovery required, cannot set ARCHIVELOG mode

错误原因：在非归档模式下，直接用shutdown abort关闭数据，导致ora-00265错误

解决方法：把数据启动到open状态，然后shutdown immediate关闭数据，重新把数据库启动mount状态

3.  配置归档进程

oracle 11g 默认归档进程是4

SQL> show parameter log_archive_max_processes;

NAME				     TYPE	 VALUE

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

log_archive_max_processes	     integer	 4

<strong>后台进程：</strong>

[oracle@localhost ~]$ ps -ef|grep arc

oracle    9164     1  0 17:12 ?        00:00:00 ora_arc0_orcl

oracle    9166     1  1 17:12 ?        00:00:00 ora_arc1_orcl

oracle    9168     1  1 17:12 ?        00:00:00 ora_arc2_orcl

oracle    9170     1  1 17:12 ?        00:00:00 ora_arc3_orcl

修改归档进程数量：

<strong>例如开启10个归档进程</strong>

SQL> alter system set log_archive_max_processes=10;

System altered.

SQL> show parameter log_archive_max_processes;

NAME				     TYPE	 VALUE

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

log_archive_max_processes	     integer	 10

[oracle@localhost ~]$ ps -ef|grep arc

oracle    9164     1  0 17:12 ?        00:00:00 ora_arc0_orcl

oracle    9166     1  0 17:12 ?        00:00:00 ora_arc1_orcl

oracle    9168     1  0 17:12 ?        00:00:00 ora_arc2_orcl

oracle    9170     1  0 17:12 ?        00:00:00 ora_arc3_orcl

oracle    9198     1  0 17:16 ?        00:00:00 ora_arc4_orcl

oracle    9200     1  0 17:16 ?        00:00:00 ora_arc5_orcl

oracle    9202     1  0 17:16 ?        00:00:00 ora_arc6_orcl

oracle    9204     1  0 17:16 ?        00:00:00 ora_arc7_orcl

oracle    9206     1  0 17:16 ?        00:00:00 ora_arc8_orcl

oracle    9208     1  0 17:16 ?        00:00:00 ora_arc9_orcl

4 配置归档文件格式

默认格式：%t_%s_%r.arc

SQL> show parameter log_archive_format;

NAME				     TYPE	 VALUE

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

log_archive_format		     string	 %t_%s_%r.dbf

修改归档文件格式，修改完成以后需要重启数据库才会生效

SQL> alter system set log_archive_format=&#39;%t_%s_%r.arc&#39; <strong>scope=spfile;
</strong>

System altered.

log_archive_format参数说明:

(<strong>归档文件格式必须包含以下几个参数：</strong><strong>%s, %t and %r,否则启动数据库会报错</strong>

SQL> startup open;

ORA-19905: log_archive_format must contain %s, %t and %r

）

%s log sequence number

%S log sequence number, zero filled

%t thread number

%T thread number, zero filled

%a activation ID

%d database ID

%r resetlogs ID that ensures unique names are constructed for the archived log files across multiple incarnations of the database

5. 修改归档文件目录

归档日志存默认路径：受DB_RECOVERY_FILE_DEST参数控制

SQL> select dest_id,dest_name,destination from v$archive_dest;

   DEST_ID DEST_NAME		DESTINATION

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

	 1 LOG_ARCHIVE_DEST_1	USE_<strong>DB_RECOVERY_FILE_DEST</strong>

SQL> show parameter db_recovery_file_dest;

NAME				     TYPE	 VALUE

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

<strong>db_recovery_file_dest	</strong>	     string	 /home/oracle/app/oracle/flash_

						 recovery_area

语法：alter system set log_archive_dest_n='location=文件路径' scope=spfile

说明：a.文件存放路径必须<strong>本地需要用location指定，远程需要service指定</strong>，另外重启数据库才会生效。

配置远程归档位置时,SERVICE选项需要指定远程数据库的网络服务名(在tnsnames.ora文件中配置)

           b. 初始化参数LOG_ARCHIVE_DEST_n 不能与初始化参数LOG_ARCHIVE_DEST和LOG_ARCHIVE_DUPLEX_DEST同时使用.

      (oracle官方文档说明：如果是企业，将不在推荐使用log_archive_duplex_dest参数

                 If you are using Oracle Enterprise Edition, this parameter is deprecated in favor of the LOG_ARCHIVE_DEST_n parameters.

               If Oracle Enterprise Edition is not installed or it is installed but you have not specified any LOG_ARCHIVE_DEST_n parameters, this parameter is valid)

     c.LOG_ARCHIVE_DEST_n参数       

         OPTIONAL:该选项是默认选项.使用该选项时,无论归档是否成功,都可以覆盖重做日志.
         MANDATORY:强制归档.使用该选项时,只有在归档成功之后,重做日志才能被覆盖.
         REOPEN:该属性用于指定重新归档的时间间隔,默认值为300秒,必须跟在MANDATORY后.

SQL> alter system set log_archive_dest_1=&#39;<strong>location=</strong>/home/oracle/app/oracle&#39; <strong>scope=spfile;</strong>

System altered

SQL>  select dest_id,dest_name,destination from v$archive_dest;

   DEST_ID DEST_NAME		DESTINATION

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

	 1 LOG_ARCHIVE_DEST_1	<strong>/home/oracle/app/oracle</strong>

SQL> alter system archive log  current;
（手动强制归档）

System altered.

[oracle@localhost oracle]$ ls -ls<strong> /home/oracle/app/oracle
</strong>

总计 244

208 -rw-r----- 1 oracle oinstall 207872 12-10 18:26 4bc445e1_1_102_818781763.arc

归档日志已经在相应的目录下生成

知识扩展：

ALTER SYSTEM SWITCH LOGFILE 是强制日志切换，不一定就归档当前的重做日志文件（若自动归档打开，就归档前的重做日志，若自动归档没有打开，就不归档当前重做日志。）

ALTER SYSTEM ARCHIVE LOG CURRENT 是归档当前的重做日志文件，如果自动归档有没有打开，将报ORA-00258错误。

alter system archive log all;--归档所有已填满的联机日志（只能在非归档模式下起作用，如果在归档模式下运行，会报ORA-00271: there are no logs that need archiving）

<strong>主要的区别在于</strong>

ALTER SYSTEM SWITCH LOGFILE 对单实例数据库或RAC中的当前实例执行日志切换。

ALTER SYSTEM ARCHIVE LOG CURRENT 会对数据库中的所有实例执行日志切换

<strong>设置：log_archive_duplex_dest参数</strong>

SQL> alter system set log_archive_duplex_dest='/home/oracle/app/oracle/archiveduplex' scope=spfile;

<strong>取消归档文件路径</strong>

alter system set log_archive_dest_n='' scope=spfile

alter system set log_archive_duplex_dest='' scope=spfile

<strong>禁用某个归档日志路径</strong>

语法：

alter system set LOG_ARCHIVE_DEST_STATE_n = { enable | defer | alternate }

SQL> alter system set log_archive_dest_state_2=defer;--禁用

5. 归档涉及到视图

v$archive_dest：查询归档所在的目录（重要字段是：dest_id , dest_name , destination）

v$loghist：显示日志历史信息

v$archive_processes：归档进程信息

v$archived_log：显示归档信息（重要字段：name, sequence#, first_change#）