oracle数据库的归档日志的简要学习记录

最新推荐文章于 2024-09-12 17:26:32 发布

#慧#

最新推荐文章于 2024-09-12 17:26:32 发布

阅读量358

点赞数

分类专栏： oracle oracle dg 文章标签：数据库

本文链接：https://blog.csdn.net/weixin_41561862/article/details/104493849

版权

oracle 同时被 2 个专栏收录

74 篇文章 2 订阅

订阅专栏

oracle dg

7 篇文章 0 订阅

订阅专栏

Oracle可以将联机日志文件保存到多个不同的位置，将联机日志转换为归档日志的过程称之为归档。相应的日志被称为归档日志。

（1）非归档模式和归档模式的切换方法：

1.非归档到归档模式
a.一致性关闭数据库(shutdown [immediate | transactional |normal])
b.启动到mount阶段(startup mount)
c.切换到归档模式(alter database archivelog  [manual])
d.切换到open阶段(alter database open)
e.对数据做一个完整备份(full backup)


2.归档模式切换到非归档模式
a.一致性关闭数据库(shutdown [immediate | transactional |normal])
b.启动到mount阶段(startup mount)
c.切换到归档模式(alter database noarchivelog  )
d.切换到open阶段(alter database open)
e.对数据做一个完整备份(full backup)

（2）归档进程的调整

通过设置LOG_ARCHIVE_MAX_PROCESSES 参数，可以指定数据库规定所需初始化的进程数，缺省的归档进程数为2，一般情况下不需要修改该参数，Oracle会根据归档情况自动启动额外的归档进程。也可以动态的增加或减少归档进程数，比如有些业务在月底需要进行大量归档，平时则归档较少，对此可以动态修改，修改方法如下：

ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=3;

（3）归档位置的配置方法

在ORACLE10g和11g版本，ORACLE默认的日志归档路径为闪回恢复区（$ORACLE_BASE/flash_recovery_area）。对于这个路径，ORACLE有一个限制，就是默认只有2G的空间，而且不只是归档日志的默认路径，也是备份文件和闪回日志的默认地址，这样的话归档日志所使用的空间就达不到2G，在没有设置好这个路径大小的情况下，很多系统都遇到过归档日志满而无法归档导致数据库夯住的问题，可以使用下面的SQL语句去查看归档信息。

SQL> ARCHIVE LOG LIST;         --查看日志是否处于归档状态
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     24
Next log sequence to archive   26
Current log sequence           26

查看归档路径是否已经设置

查看归档路径是否已设置，为空值表示未设置。
SHOW PARAMETER ARCHIVE
 NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
archive_lag_target                   integer     0
log_archive_config                   string
log_archive_dest                     string
log_archive_dest_1                   string
log_archive_dest_10                  string
log_archive_dest_2                   string
log_archive_dest_3                   string
log_archive_dest_4                   string
log_archive_dest_5                   string
log_archive_dest_6                   string
log_archive_dest_7                   string

设置归档路径位置的配置方法

a.归档到本机且小于等于两个归档位置
    设置LOG_ARCHIVE_DEST和LOG_ARCHIVE_DUPLEX_DEST参数
    alter system set log_archive_dest = '/u01/app/oracle/archivelog1' scope = spfile;
    alter system set log_archive_duplex_dest = '/u01/app/oracle/archivelog2' scope = spfile;

b.归档到本机或远程主机
    设置LOG_ARCHIVE_DEST_n 参数
    alter system set log_archive_dest_1 = 'LOCATION=/u01/app/oracle/archivelog3';
    alter system set log_archive_dest_2 = 'SERVICE=standby1';
    对于远程归档位置,SERVICE选项需要指定远程数据库的网络服务名(在tnsnames.ora文件中配置)
    LOG_ARCHIVE_DEST_n的两个常用选项
        MANADATORY | OPTIONAL:
            MANADATORY表示归档必须成功复制到目的路径之后，联机重做日志才能被复用，OPTIONAL正好相反
        REOPEN：
            指定归档失败后指定间隔多少时间重试归档，缺省的为300秒。
        示例：
        log_archive_dest_1 = 'LOCATION=/u01/app/oracle/archivelog3 manadatory reopen = 600'

c.两种归档方法异同(前者log_archive_dest、log_archive_duplex_dest ，后者log_archive_dest_n)
    前者最多配置两个归档位置，后者可以配置多个归档位置；
    两者互不兼容，要么使用前者，要么使用后者；
    使用后者归档到本地时需要增加LOCATION选项，备份到远程主机需要使用SERVICE选项；
    但上述两种方式未指定路径时，归档日志位于db_recovery_file_dest 缺省为$ORACLE_BASE/flash_recovey_area

d.归档日志命名格式
    设置LOG_ARCHIVE_FORMAT参数，Unix下的缺省设置为LOG_ARCHIVE_FORMAT=%t_%s_%r.dbf
        %s: 日志序列号:
        %S: 日志序列号(带有前导)
        %t: 重做线程编号.
        %T: 重做线程编号(带有前导)
        %a: 活动ID号
        %d: 数据库ID号
        %r RESETLOGS的ID值.
		
    alter system set log_archive_format = 'arch_%t_%s_%r.arc';

e.控制归档的可用性(使用参数log_archive_dest_state_n)
     alter system set log_archive_dest_state_1 = enable | defer
     enable  -->缺省状态，可以在该归档位置进行归档
     defer   -->禁用该归档位置

归档路径配置及查看归档进程，归档位置状态案例如下：

查看日志是否处于归档状态
SQL> ARCHIVE LOG LIST;         
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     24
Next log sequence to archive   26
Current log sequence           26

查看归档进程是否已启动
SQL> ho ps -ef | grep ora_arc   
oracle    4062     1  0 11:43 ?        00:00:00 ora_arc0_orcl
oracle    4064     1  0 11:43 ?        00:00:00 ora_arc1_orcl


查看归档路径是否已设置，为空值表示未设置
SQL> SHOW PARAMETER ARCHIVE  

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
archive_lag_target                   integer     0
log_archive_config                   string
log_archive_dest                     string
log_archive_dest_1                   string
log_archive_dest_10                  string
log_archive_dest_2                   string
log_archive_dest_3                   string
log_archive_dest_4                   string
log_archive_dest_5                   string


设置归档路径（2个）
SQL> alter system set log_archive_dest = '/u01/app/oracle/archivelog1' scope = spfile;

System altered.

SQL> alter system set log_archive_duplex_dest= '/u01/app/oracle/archivelog2' scope=spfile;

System altered.

SQL> alter system set log_archive_format = 'arch_%t_%s_%r.arc' scope = spfile;

静态参数，需要重新启动数据库才生效，设定完毕后需要重新启动实例。


启动实例后，手动切换一个日志，查看归档日志的信息
alter system switch logfile;

在指定的文件夹archivelog1查看产生的归档日志
SQL> ho ls -l /u01/app/oracle/archivelog1  
total 18224
-rw------- 1 oracle oinstall 18636800 Jul 25 20:39 arch_1_25_724652763.arc


创建新的归档路径
SQL> ho mkdir /u01/app/oracle/archivelog3     

重新配置归档路径的位置
SQL> alter system set log_archive_dest_1 = 'location=/u01/app/oracle/archivelog3' scope = spfile;

关闭实例
SQL> shutdown immediate;      
Database closed.
Database dismounted.
ORACLE instance shut down.

启动实例后，收到错误提示表明两种归档方式不兼容
SQL> startup                  
ORA-16019: cannot use LOG_ARCHIVE_DEST_1 with LOG_ARCHIVE_DEST or LOG_ARCHIVE_DUPLEX_DEST

正确的处理办法，应当先将一种归档方式路径置空，此处是将log_archive_dest置空
SQL> alter system set log_archive_dest = '' scope = spfile;

System altered.

此处是将log_archive_duplex_dest置空
SQL> alter system set log_archive_duplex_dest = '' scope = spfile;

System altered.

接下来再设定log_archive_dest_1
SQL> alter system set log_archive_dest_1 = 'location=/u01/app/oracle/archivelog3' scope = spfile;

System altered.

设定完毕后需要重新启动实例，此时系统能正常启动。


查看归档目的地的相关状态信息
set line 300
col dest_name format a20
col destination format a30
select dest_name,status,archiver,destination,log_sequence,reopen_secs,transmit_mode,process from v$archive_dest where status='VALID';


归档日志相关视图
v$archived_log         -->从控制文件中获得归档的相关信息
v$archive_dest         -->归档路径及状态
v$log_history          -->控制文件中日志的历史信息
v$database             -->查看数据库是否处于归档状态
v$archive_processes    -->归档相关的后台进程信息


set line 500
col name format a80
Select  name,sequence#,registrar,standby_dest,archived,status from v$archived_log;

（4）查看每天归档日志的大小和个数等情况

set line 200 pagesize 9999 long 5000
column thread# format 999999 heading 'Thread#'
column day   format a10     heading 'Day/Time'
column H00   format 999     heading '00'
column H01   format 999     heading '01'
column H02   format 999     heading '02'
column H03   format 999     heading '03'
column H04   format 999     heading '04'
column H05   format 999     heading '05'
column H06   format 999     heading '06'
column H07   format 999     heading '07'
column H08   format 999     heading '08'
column H09   format 999     heading '09'
column H10   format 999     heading '10'
column H11   format 999     heading '11'
column H12   format 999     heading '12'
column H13   format 999     heading '13'
column H14   format 999     heading '14'
column H15   format 999     heading '15'
column H16   format 999     heading '16'
column H17   format 999     heading '17'
column H18   format 999     heading '18'
column H19   format 999     heading '19'
column H20   format 999     heading '20'
column H21   format 999     heading '21'
column H22   format 999     heading '22'
column H23   format 999     heading '23'
column total format 999,999 heading 'Total'
select thread#,to_char (first_time, 'YYYY-MM-DD') day,
         sum (decode (to_char (first_time, 'HH24'), '00', 1, 0)) H00,
         sum (decode (to_char (first_time, 'HH24'), '01', 1, 0)) H01,
         sum (decode (to_char (first_time, 'HH24'), '02', 1, 0)) H02,
         sum (decode (to_char (first_time, 'HH24'), '03', 1, 0)) H03,
         sum (decode (to_char (first_time, 'HH24'), '04', 1, 0)) H04,
         sum (decode (to_char (first_time, 'HH24'), '05', 1, 0)) H05,
         sum (decode (to_char (first_time, 'HH24'), '06', 1, 0)) H06,
         sum (decode (to_char (first_time, 'HH24'), '07', 1, 0)) H07,
         sum (decode (to_char (first_time, 'HH24'), '08', 1, 0)) H08,
         sum (decode (to_char (first_time, 'HH24'), '09', 1, 0)) H09,
         sum (decode (to_char (first_time, 'HH24'), '10', 1, 0)) H10,
         sum (decode (to_char (first_time, 'HH24'), '11', 1, 0)) H11,
         sum (decode (to_char (first_time, 'HH24'), '12', 1, 0)) H12,
         sum (decode (to_char (first_time, 'HH24'), '13', 1, 0)) H13,
         sum (decode (to_char (first_time, 'HH24'), '14', 1, 0)) H14,
         sum (decode (to_char (first_time, 'HH24'), '15', 1, 0)) H15,
         sum (decode (to_char (first_time, 'HH24'), '16', 1, 0)) H16,
         sum (decode (to_char (first_time, 'HH24'), '17', 1, 0)) H17,
         sum (decode (to_char (first_time, 'HH24'), '18', 1, 0)) H18,
         sum (decode (to_char (first_time, 'HH24'), '19', 1, 0)) H19,
         sum (decode (to_char (first_time, 'HH24'), '20', 1, 0)) H20,
         sum (decode (to_char (first_time, 'HH24'), '21', 1, 0)) H21,
         sum (decode (to_char (first_time, 'HH24'), '22', 1, 0)) H22,
         sum (decode (to_char (first_time, 'HH24'), '23', 1, 0)) H23,
         count (*) total
    from v$log_history a
   where first_time >= sysdate - 30
group by thread#,to_char (first_time, 'YYYY-MM-DD')
order by thread#,to_char (first_time, 'YYYY-MM-DD');

（5）查看备份集中的日志文件信息

select bs.recid bs_key,
         decode (backup_type,'L', 'Archived Redo Logs','D', 'Datafile Full Backup','I', 'Incremental Backup') backup_type,
         device_type,
         decode (bs.controlfile_included, 'NO', '-', bs.controlfile_included) controlfile_included,
         nvl (sp.spfile_included, '-') spfile_included,
         bs.incremental_level,
         bs.pieces,
         to_char (bs.start_time, 'YYYY-MM-DD HH24:MI:SS') start_time,
         to_char (bs.completion_time, 'YYYY-MM-DD HH24:MI:SS') completion_time,
         bs.elapsed_seconds,
         bp.tag,
         bs.block_size,
         bs.keep,
         to_char (bs.keep_until, 'YYYY-MM-DD HH24:MI:SS') keep_until,
         bs.keep_options
    from v$backup_set bs,
         (select distinct set_stamp,
                          set_count,
                          tag,
                          device_type
            from v$backup_piece
           where status in ('A', 'X')) bp,
         (select distinct set_stamp, set_count, 'YES' spfile_included
            from v$backup_spfile) sp
   where     bs.set_stamp = bp.set_stamp
         and bs.set_count = bp.set_count
         and bs.set_stamp = sp.set_stamp(+)
         and bs.set_count = sp.set_count(+)
         and sysdate - bs.start_time <= 30
order by bs.recid;

（6）归档日志的删除

查看归档日志列表
list archivelog all; 
列出所有失效的归档日志
list expired archivelog all;
校验日志的可用性
crosscheck archivelog all;
删除log sequence20480之前的所有归档日志
delete noprompt archivelog until sequence 20480;
删除一天前的所有归档日志文件
delete noprompt archivelog all completed before 'sysdate-2';
删除系统时间7天以前的归档日志
delete noprompt archivelog all completed before 'sysdate-7';    
删除所有的归档日志
delete noprompt archivelog all;

（7）data guard环境下归档日志删除可能出现的问题和解决方法

如果是dg 主备，在主库上定期删除归档会报如下错误：
RMAN-08120: WARNING: archived log not deleted, not yet applied by standby

第一，查看主库和备库现在应用日志的情况，得到应用的日志序列号

查看主库应用日志信息：
SYS@testdb> select GROUP# ,THREAD# , SEQUENCE#  from v$log;
    GROUP#    THREAD#  SEQUENCE#
---------- ---------- ----------
         1	    1	   20507
         2	    1	   20508
         3	    1	   20509
         5	    1	   20510
         6	    1	   20511


查看备库的应用情况：
select THREAD# , SEQUENCE#,APPLIED  from v$archived_log;

第二，修改主库的rman配置参数：
默认的参数如下：
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;

修改为：
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;

第三：执行删除归档文件命令：
校验日志的可用性
crosscheck archivelog all;

删除log sequence33535之前的所有归档日志；
序列号可以由上面查询到的数据减去8到10个左右
delete noprompt archivelog until sequence 33535;
如果加上force参数，不需要前面的第一二步骤的设置即可执行删除归档的操作。
delete force noprompt archivelog until sequence 33535;     
或者指定线程号进行归档日志的删除
delete force noprompt archivelog until sequence 33535thread 1;