归档日志管理

最新推荐文章于 2022-09-21 12:12:58 发布

我是小小飞鸟

最新推荐文章于 2022-09-21 12:12:58 发布

阅读量1.3k

点赞数

分类专栏： Oracle数据库文章标签： oracle archive log 归档日志 redo 重做日志

本文链接：https://blog.csdn.net/life_dba/article/details/17847493

版权

Oracle数据库专栏收录该内容

12 篇文章 0 订阅

订阅专栏

相关文章： redo文件管理 http://blog.csdn.net/life_dba/article/details/17841045

数据环境：

   
   
    
    

   
   
   
   
    
    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
    
     and
    
    b_log1
    
    , then the archiver process (ARC
    
    n
    
    ) will archive one of these member files. Should 
    
    a_log1
    
     become corrupted, then ARC
    
    n
    
     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 arc
     
     
     
     
      
      oracle 3835 1 0 01:26 ? 00:00:01 ora_arc0_orcl
     
     
     
     
      
      oracle 3837 1 0 01:26 ? 00:00:01 ora_arc1_orcl
     
     
     
     
      
      oracle 3839 1 0 01:26 ? 00:00:01 ora_arc2_orcl
     
     
     
     
      
      oracle 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
后台进程：
[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
修改归档进程数量：
例如开启10个归档进程
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='%t_%s_%r.arc' scope=spfile;

System altered.

log_archive_format参数说明:
(归档文件格式必须包含以下几个参数：%s, %t and %r,否则启动数据库会报错
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_DB_RECOVERY_FILE_DEST
SQL> show parameter db_recovery_file_dest;

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest		     string	 /home/oracle/app/oracle/flash_
						 recovery_area
语法：alter system set log_archive_dest_n='location=文件路径' scope=spfile
说明：a.文件存放路径必须本地需要用location指定，远程需要service指定，另外重启数据库才会生效。
       配置远程归档位置时,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='location=/home/oracle/app/oracle' scope=spfile;
System altered
SQL>  select dest_id,dest_name,destination from v$archive_dest;
   DEST_ID DEST_NAME		DESTINATION
---------- -------------------- --------------------------------------------------
	 1 LOG_ARCHIVE_DEST_1	/home/oracle/app/oracle
SQL> alter system archive log  current;
（手动强制归档）
System altered.
[oracle@localhost oracle]$ ls -ls /home/oracle/app/oracle
总计 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）
主要的区别在于
ALTER SYSTEM SWITCH LOGFILE 对单实例数据库或RAC中的当前实例执行日志切换。
ALTER SYSTEM ARCHIVE LOG CURRENT 会对数据库中的所有实例执行日志切换
设置：log_archive_duplex_dest参数

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

取消归档文件路径

alter system set log_archive_dest_n='' scope=spfile

alter system set log_archive_duplex_dest='' scope=spfile
禁用某个归档日志路径
语法：
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#）
  个人博客：【梦&dream】http://blog.csdn.net/life_dba