精华内容
下载资源
问答
  • EOF crosscheck archivelog all;delete noprompt expired archivelog all;delete noprompt force archivelog until time ‘sysdate -2’; ——-删除两天前的archivelogexit;EOF2.手动执行清除日志[oracle@108 ~]$ ...
  • 最近在因归档日志暴增,使用delete archivelog all貌似无法清除所有的归档日志,究竟是什么原因呢?本文将为您解答,需要的朋友可以参考下
  • Oracle在开启了归档模式后,会在指定的archive目录下产生很多的archivelog文件,而且默认是不会定期清除的,时间长久了,该文件夹会占用很大的空间。那么如何定期正确删除archivelog文件呢?今天小编给大家介绍下
  • ORA-00257Oracle释放archivelog空间 主要分两步处理:第一步删除物理文件,注意删除的是早期日志;第二步释放逻辑空间,登录RAMN,释放逻辑空间。
  • 今天在一个数据库上,删除归档的时候,发现使用命令delete archivelog all completed before 'sysdate-7';并不能删除掉7天前的归档。比如1月份的归档还是存在的。 使用catalog start with,将归档重新注册后,还是...

    语句:

    delete archivelog all completed before 'sysdate-7';
    delete archivelog until time 'sysdate-7' ; 

    今天在一个数据库上,删除归档的时候,发现使用命令delete archivelog all completed before 'sysdate-7';并不能删除掉7天前的归档。比如1月份的归档还是存在的。

    使用catalog start with,将归档重新注册后,还是删除不掉 。

    但是,使用delete archivelog until time 'sysdate-7' ;命令,就可以删除掉。

    查询了这两个语句的区别。带complete的语句,主要是删除已备份过的归档,没有备份备份的归档,不会被删除。而第二个语句, 则会删除掉归档,无论备份与否。

    参考文档:

    https://docs.oracle.com/database/121/RCMRF/rcmsubcl002.htm#RCMRF106

    https://docs.oracle.com/database/121/RCMRF/rcmsubcl003.htm#RCMRF112

    Complete Steps To Delete Archivelogs Using The Rman Utility (Doc ID 794383.1)

    -- 第一篇文档:

    archivelogRecordSpecifier

    Purpose

    Use the archivelogRecordSpecifier subclause to specify a set of archived redo log files for use in RMAN operations.

    Syntax

    archivelogRecordSpecifier::=

    Description of GUID-EB77C428-351C-4C86-94DB-045EA7FAEAEF-print.eps follows
    Description of the illustration GUID-EB77C428-351C-4C86-94DB-045EA7FAEAEF-print.eps

    archlogRange::=

    Description of GUID-C5D12267-E268-488C-97D7-EB2173DF83AB-print.eps follows
    Description of the illustration GUID-C5D12267-E268-488C-97D7-EB2173DF83AB-print.eps

     

    RMAN queries the V$ARCHIVED_LOG or RC_ARCHIVED_LOG view to determine which logs to include in the range. When you specify a time, SCN, or restore point, RMAN determines the range according to the contents of the archived redo log files, not when the logs were created or backed up. When you specify the range by log sequence number, then RMAN uses the sequence number to determine the range.

    -- 第二篇文档

    completedTimeSpec

    Purpose

    Use the completedTimeSpec subclause to specify when a backup or copy completed.

    Usage Notes

    All date strings must be either:

    • Formatted according to the Global Technology date format specification currently in effect.

    • Created by a SQL expression that returns a DATE value, as in the following examples:

      • 'SYSDATE-30'

      • TO_DATE('09/30/2013 08:00:00','MM/DD/YYYY HH24:MI:SS')

      The TO_DATE function specifies dates independently of the current Global Technology environment variable settings.

    Syntax

    completedTimeSpec::=

    Description of GUID-55694344-D45C-4A1E-A833-7DC6FF0D1E37-print.eps follows
    Description of the illustration GUID-55694344-D45C-4A1E-A833-7DC6FF0D1E37-print.eps

    Semantics

    Syntax ElementDescription

    AFTER 'date_string'

    Specifies the time after which the backup was completed (see Example 4-7).

    BEFORE 'date_string'

    Specifies the time before which the backup was completed (see Example 4-9).

    BETWEEN 'date_string' AND 'date_string'

    Specifies a time range during which the backup was completed (see Example 4-8). BETWEEN 'date1' AND 'date2' equals AFTER 'date1' BEFORE 'date2'.

     

    -- 第三篇文档,MOS上的文档

     其中MOS上有一段是这样说的,“DO NOT DELETE ANY ARCHIVELOGS  unless you are certain they have already been backed up.” 从另一方面,也说明了Oracle在删除归档的时候,建议是先备份文档。所以这个MOS中,oracle删除归档,默认已经做了备份了。所以使用的是带有complete语句进行删除的。

    If you have a flash recovery area (FRA) configured for your archivelogs, then you will need to use RMAN to clear these archivelogs regularly otherwise you will run out of space in the FRA.  Only RMAN will maintain the space management metrics for the FRA.

    This note deals with the following question:

    How do you delete archivelogs using RMAN which are still within the retention period.

    DO NOT DELETE ANY ARCHIVELOGS  unless you are certain they have already been backed up.

     

    END

     

     

    展开全文
  • delete archivelog all 无法彻底删除归档日志
                   

        最近在因归档日志暴增,使用delete archivelog all貌似无法清除所有的归档日志,到底是什么原因呢?

    1、演示环境SQL> select * from v$version where rownum<2;BANNER----------------------------------------------------------------Oracle Database 10g Release 10.2.0.3.0 - 64bit ProductionSQL> select inst_id,instance_name from gv$instance; -->两节点RAC   INST_ID INSTANCE_NAME---------- ----------------         1 GOBO4A         2 GOBO4BSQL> show parameter db_recovery   -->+REV,使用了ASM 存储方式NAME                                 TYPE        VALUE------------------------------------ ----------- -------------db_recovery_file_dest                string      +REVdb_recovery_file_dest_size           big integer 1G     SQL> select flashback_on from v$database;  -->数据库未开启闪回特性,也就是说尽管指定了闪回区,未启用闪回特性                                           -->相应的,归档日志充满整个闪回区时,闪回区空间并不会被重用FLASHBACK_ON------------------NO2、查看及清除现有的归档日志文件    oracle@bo2dbp:~> export ORACLE_SID=+ASM1oracle@bo2dbp:~> asmcmdASMCMD> cd +REV/GOBO4/ARCHIVELOGASMCMD> ls2012_10_08/....arch_795194241_1_10.arcarch_795194241_1_100.arc....oracle@bo2dbp:~> export ORACLE_SID=GOBO4Aoracle@bo2dbp:~> rman target /Recovery Manager: Release 10.2.0.3.0 - Production on Thu Nov 29 16:23:15 2012Copyright (c) 1982, 2005, Oracle.  All rights reserved.connected to target database: GOBO4 (DBID=921286879)#下面通过使用rman backup archivelog方式来删除所有的归档日志文件RMAN> backup format '/install_source/rman_bak/arch_%d_%U'2> archivelog all delete input;Starting backup at 29-NOV-12current log archivedusing target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: sid=1058 instance=GOBO4A devtype=DISKchannel ORA_DISK_1: starting archive log backupsetchannel ORA_DISK_1: specifying archive log(s) in backup setinput archive log thread=1 sequence=139 recid=214 stamp=797450261input archive log thread=1 sequence=140 recid=215 stamp=797450292input archive log thread=1 sequence=141 recid=216 stamp=797450308input archive log thread=1 sequence=142 recid=218 stamp=797450347input archive log thread=1 sequence=143 recid=219 stamp=797450372input archive log thread=1 sequence=144 recid=220 stamp=797450409channel ORA_DISK_1: starting piece 1 at 29-NOV-12channel ORA_DISK_1: finished piece 1 at 29-NOV-12piece handle=/install_source/rman_bak/arch_GOBO4_1dnrhkn4_1_1 tag=TAG20121129T162806 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:02:15channel ORA_DISK_1: deleting archive log(s)archive log filename=+REV/gobo4/archivelog/arch_795194241_1_139.arc recid=214 stamp=797450261archive log filename=+REV/gobo4/archivelog/arch_795194241_1_140.arc recid=215 stamp=797450292archive log filename=+REV/gobo4/archivelog/arch_795194241_1_141.arc recid=216 stamp=797450308........piece handle=/install_source/rman_bak/arch_GOBO4_1hnrhli2_1_1 tag=TAG20121129T162806 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:09channel ORA_DISK_1: deleting archive log(s)archive log filename=+REV/gobo4/archivelog/arch_795194241_2_141.arc recid=427 stamp=800547491archive log filename=+REV/gobo4/archivelog/arch_795194241_2_142.arc recid=429 stamp=800549193archive log filename=+REV/gobo4/archivelog/arch_795194241_2_143.arc recid=433 stamp=800578944archive log filename=+REV/gobo4/archivelog/arch_795194241_2_144.arc recid=437 stamp=800641679Finished backup at 29-NOV-12#再次查看依然有很多归档日志文件存在,而且都是10月23日之前的ASMCMD> pwd+REV/GOBO4/ARCHIVELOGASMCMD> ls2012_09_30/2012_10_09/2012_10_10/2012_10_11/2012_10_12/2012_10_13/2012_10_14/2012_10_15/2012_10_16/2012_10_17/2012_10_18/2012_10_22/2012_10_23/arch_795194241_1_100.arcarch_795194241_1_101.arcarch_795194241_1_102.arc............#再次删除日志文件,来个更狠的命令,直接delete所有的archivelog,最近新增的一个archivelog被删除RMAN> delete noprompt archivelog all;released channel: ORA_DISK_1allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=1081 instance=GOBO4A devtype=DISKList of Archived Log CopiesKey     Thrd Seq     S Low Time  Name------- ---- ------- - --------- ----453     1    294     A 29-NOV-12 +REV/gobo4/archivelog/arch_795194241_1_294.arcdeleted archive logarchive log filename=+REV/gobo4/archivelog/arch_795194241_1_294.arc recid=453 stamp=800662185Deleted 1 objects# 上面输出的结果只有一个归档日志被删除,何以故?# 这个我们的分析一下delete noprompt archivelog all以及备份归档日志时使用的 delete input# 回顾一下Oracle控制文件以及Oracle RMAN的的备份恢复的原理。# 我们知道,Oracle 控制文件里边记录了数据库的名字,id,创建的时间戳....一大堆的信息,当然也有不可少的归档信息以及备份信息。# 如果不知道控制文件有什么? 那就参考:Oracle 控制文件,文章尾部有给出链接。# 其次,Oracle RMAN的备份恢复的所有信息都依赖于两个东东,要么是控制文件,要么是恢复目录(catalog)。# 因为所有的备份与恢复信息都会依据备份是的方式存储到这两个位置。# 理所当然的是,对这两个东东里的备份集,镜像副本,归档日志,等等所有能备份的对象的任意操作,首先会参考这些对象的记录的信息。# 其次是当被记录的对象发生变化时做相应的更新。3、深度分析无法清除的原因#先来看看gv$archived_log,如果是单实例使用v$archived_log#从下面的查询可知,又有两个新的归档日志产生,一个从第一个instance产生,一个从第二个instance产生。SQL> select name,status,count(*) from gv$archived_log group by name,status;NAME                                               S   COUNT(*)-------------------------------------------------- - ----------                                                   D        444+REV/gobo4/archivelog/arch_795194241_1_295.arc     A          2+REV/gobo4/archivelog/arch_795194241_2_150.arc     A          2# 从上面的查询可知,当前的两个节点其归档日志只有2个,其余的444个其名字都是NULL值。# 看看关于视图v$archived_log中NAME列的解释# Archived log file name. If set to NULL, either the log file was cleared before it was archived or an RMAN backup command#  with the "delete input" option was executed to back up archivelog all (RMAN> backup archivelog all delete input;). # 上面的这段话表明当前的这些日志文件要么被手动清除,要么被rman的delete input选项清除。# 其次status列的D字段也表明了这些个名字为空的归档日志已经被Deleted.也就是说有444个归档日志已经被删除了。# 再次尝试删除归档日志,尾数为295和150的归档日志也被删除RMAN> delete noprompt archivelog all;released channel: ORA_DISK_1allocated channel: ORA_DISK_1channel ORA_DISK_1: sid=1081 instance=GOBO4A devtype=DISKList of Archived Log CopiesKey     Thrd Seq     S Low Time  Name------- ---- ------- - --------- ----454     1    295     A 29-NOV-12 +REV/gobo4/archivelog/arch_795194241_1_295.arc455     2    150     A 29-NOV-12 +REV/gobo4/archivelog/arch_795194241_2_150.arcdeleted archive logarchive log filename=+REV/gobo4/archivelog/arch_795194241_1_295.arc recid=454 stamp=800712037deleted archive logarchive log filename=+REV/gobo4/archivelog/arch_795194241_2_150.arc recid=455 stamp=800712038Deleted 2 objects# 查询gv$archived_log视图,表明所有现有的archivelog都已经被删除SQL> select name,status,count(*) from gv$archived_log group by name,status;NAME                                               S   COUNT(*)-------------------------------------------------- - ----------                                                   D        448# 在asmcmd命令下也无法找到我们刚刚删除的归档日志文件ASMCMD> pwd+REV/GOBO4/ARCHIVELOGASMCMD> ls -l arch_795194241_1_295.arcasmcmd: entry 'arch_795194241_1_295.arc' does not exist in directory '+REV/GOBO4/ARCHIVELOG/'ASMCMD> ls -l arch_795194241_2_150.arcasmcmd: entry 'arch_795194241_2_150.arc' does not exist in directory '+REV/GOBO4/ARCHIVELOG/'# 在A节点上再次切换一次SQL> alter system switch logfile;System altered.SQL> select inst_id,name,count(*) from gv$archived_log group by inst_id,name;   INST_ID NAME                                                 COUNT(*)---------- -------------------------------------------------- ----------         2                                                           223         1 +REV/gobo4/archivelog/arch_795194241_1_296.arc              1         2 +REV/gobo4/archivelog/arch_795194241_1_296.arc              1         1                                                           223         --上面的查询可以看到当前的一个归档日志arch_795194241_1_296.arc基于Inst_id为1的有1个,而基于Inst_id为2的也有一个--而直接查询v$archived_log时只有1个当前的归档日志,实际上arch_795194241_1_296.arc文件是由第一个instance产生的。--数字296之前的1即可以表明为第一个instance产生的。SQL> select name from v$archived_log where name='+REV/gobo4/archivelog/arch_795194241_1_296.arc';NAME--------------------------------------------------+REV/gobo4/archivelog/arch_795194241_1_296.arc# 关于这个地方个人认为这个应该是用于做恢复时用的。# RAC数据库在恢复时,无论多个少节点,只有所有的归档日志的集合才能完成地表述数据库的变迁。# 此时,无论从哪个节点上看,或者说做无论从哪个节点恢复,都可以看到该归档日志。# 而具体是哪个instance产生则由'%t'重做线程编号来判断。#下面再来看看控制文件SQL> select * from gv$controlfile_record_section where type='ARCHIVED LOG';   INST_ID TYPE                         RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID---------- ---------------------------- ----------- ------------- ------------ ----------- ---------- ----------         1 ARCHIVED LOG                         584           224          224         149        148        456         2 ARCHIVED LOG                         584           224          224         149        148        456# RECORDS_TOTAL:Number of records allocated for the section# 列RECORDS_TOTAL表明为当前TYPE分配的可存储的总数,在两个instance上都为224条# 从最近一次切换日志的查询结果可知,被删除的有223条,新增的一条为arch_795194241_1_296.arc,总条数为224条。# 如果下次日志切换再增加一条往哪里放呢?那些已经超出缺省保留期的归档日志被覆盖,即被重用。# 用户在控制文件中保存ARCHIVED LOG部分的保留时间由谁来决定呢,参数control_file_record_keep_time,缺省为7天# 这意味着7天前的归档日志和备份信息可能在控制文件中已经不存在了SQL> show parameter control_file_record_keep_time NAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------control_file_record_keep_time        integer     7SQL> select count (*) from v$archived_log;  COUNT(*)----------       224# Author : Robinson# Blog : http://blog.csdn.net/robinson_0612SQL> alter session set nls_date_format='yyyymmdd hh24:mi:ss';Session altered.# 下面的查询正好表明为什么2012_10_23和之前的日志为什么没有被删除# 因为20121023 18:04:53之后的归档日志已经被覆盖了,所以使用delete archivelog all时是根本无法清除之前的日志的,无能为力阿。# 对于rman下的delete archivelog all方式不会删除控制文件中对应的归档日志信息,但在控制文件中设置delete状态,# 即v$archived_log视图的status列为deletedSQL> select min (FIRST_TIME), min (COMPLETION_TIME), max (FIRST_TIME), max (COMPLETION_TIME) from  2  v$archived_log;MIN(FIRST_TIME)   MIN(COMPLETION_TI MAX(FIRST_TIME)   MAX(COMPLETION_TI----------------- ----------------- ----------------- -----------------20121023 18:03:12 20121023 18:04:53 20121130 12:00:26 20121130 12:14:51SQL> select min (FIRST_TIME), min (COMPLETION_TIME), max (FIRST_TIME), max (COMPLETION_TIME) from  2  gv$archived_log;MIN(FIRST_TIME)   MIN(COMPLETION_TI MAX(FIRST_TIME)   MAX(COMPLETION_TI----------------- ----------------- ----------------- -----------------20121023 18:03:12 20121023 18:04:53 20121130 12:00:26 20121130 12:14:51   # 既然这般,如何是好啊?# 那就直接在asmcmd命令行下删除吧。一顿狂删 rm -rf 2012_09_30/ # 莫急,莫急,一不小心删完了,我晕,ORA-00254/ORA-15173 Archive_log Directory On Asm Being Deleted 在等候阿。       


    小结
    a、delete archivelog all将会毫无保留的删除所有的归档日志(在控制文件中有相应记录的)
    b、归档日志的信息被记录在控制文件之中,其生存期和可保留的总数也受到控制文件创建初以及参数control_file_record_keep_time限制
    c、对于那些已经在控制文件中被覆盖的归档日志,该方式不起作用,使用backup archivelog all delete input同样不起作用
    d、注意backup archivelog all时delete input与delete all input有些差异,前者删除仅仅被备份过的归档日志,而后者则对于多个归档位置
      下的所有归档日志全部删除。
    e、视图v$archived_log或gv$archived_log提供了归档日志的相关详细信息
    f、建议备份归档日志后再删除。注,RAC+ASM下切不可使得archivedlog文件夹为空,否则,整个文件夹连同上级空目录会被删除

    本文涉及到的一些参考文章:
        Oracle 控制文件(CONTROLFILE)
        Oracle 归档日志
        参数CONTROL_FILE_RECORD_KEEP_TIME和MAXLOGHISOTRY
        使用 ASMCMD 工具管理ASM目录及文件
        ORA-00254/ORA-15173 Archive_log Directory On Asm Being Deleted

     

    更多参考:

    有关Oracle RAC请参考
         使用crs_setperm修改RAC资源的所有者及权限
         使用crs_profile管理RAC资源配置文件
         RAC 数据库的启动与关闭
         再说 Oracle RAC services
         Services in Oracle Database 10g
         Migrate datbase from single instance to Oracle RAC
         Oracle RAC 连接到指定实例
         Oracle RAC 负载均衡测试(结合服务器端与客户端)
         Oracle RAC 服务器端连接负载均衡(Load Balance)
         Oracle RAC 客户端连接负载均衡(Load Balance)
         ORACLE RAC 下非缺省端口监听配置(listener.ora tnsnames.ora)
         ORACLE RAC 监听配置 (listener.ora tnsnames.ora)
         配置 RAC 负载均衡与故障转移
         CRS-1006 , CRS-0215 故障一例 
         基于Linux (RHEL 5.5) 安装Oracle 10g RAC
         使用 runcluvfy 校验Oracle RAC安装环境

    有关Oracle 网络配置相关基础以及概念性的问题请参考:
         配置非默认端口的动态服务注册
         配置sqlnet.ora限制IP访问Oracle
         Oracle 监听器日志配置与管理
         设置 Oracle 监听器密码(LISTENER)
         配置ORACLE 客户端连接到数据库

    有关基于用户管理的备份和备份恢复的概念请参考
         Oracle 冷备份
         Oracle 热备份
         Oracle 备份恢复概念
         Oracle 实例恢复
         Oracle 基于用户管理恢复的处理
         SYSTEM 表空间管理及备份恢复
         SYSAUX表空间管理及恢复
         Oracle 基于备份控制文件的恢复(unsing backup controlfile)

    有关RMAN的备份恢复与管理请参考
         RMAN 概述及其体系结构
         RMAN 配置、监控与管理
         RMAN 备份详解
         RMAN 还原与恢复
         RMAN catalog 的创建和使用
         基于catalog 创建RMAN存储脚本
         基于catalog 的RMAN 备份与恢复
         RMAN 备份路径困惑
         使用RMAN实现异机备份恢复(WIN平台)
         使用RMAN迁移文件系统数据库到ASM
         linux 下RMAN备份shell脚本
         使用RMAN迁移数据库到异机

    有关ORACLE体系结构请参考
         Oracle 表空间与数据文件
         Oracle 密码文件
         Oracle 参数文件
         Oracle 联机重做日志文件(ONLINE LOG FILE)
         Oracle 控制文件(CONTROLFILE)
         Oracle 归档日志
         Oracle 回滚(ROLLBACK)和撤销(UNDO)
         Oracle 数据库实例启动关闭过程
         Oracle 10g SGA 的自动化管理
         Oracle 实例和Oracle数据库(Oracle体系结构) 

               

    再分享一下我老师大神的人工智能教程吧。零基础!通俗易懂!风趣幽默!还带黄段子!希望你也加入到我们人工智能的队伍中来!https://blog.csdn.net/jiangjunshow

    展开全文
  • Archivelog是Oracle备份还原策略的重要组成元素,不完全备份+连续的归档日志可以让我们将数据库恢复到发生故障点,实现数据的无损失恢复。但是,现实生活中archive log给没有经验的运维人员也带来了不少的问题,归档...

    RMAN是Oracle推出的官方备份还原工具。经过几个大版本的发展,RMAN已经支持多种备份介质和恢复策略的主要工具,也是业界普遍认可是Oracle备份还原官方策略。

    Archivelog是Oracle备份还原策略的重要组成元素,不完全备份+连续的归档日志可以让我们将数据库恢复到发生故障点,实现数据的无损失恢复。但是,现实生活中archive log给没有经验的运维人员也带来了不少的问题,归档空间占满引起Hang住、瞬间归档日志过多生成引起问题等。一些前辈也在不断强调“归档模式不美好”。

    在RMAN工作参数中,针对archive log,是可以设置专门的删除策略(Deletion)。在实践领域中,已经备份过或者确保安全传输的归档日志,其实就可以删除了,特别是在有限的Fast Recovery Area管理模式下。对于自动删除archive log的策略,比较常见的是applied to standby和shipped to standby,也就是Data Guard场景下。

    本篇介绍简单的backed up参数使用情况,并通过一系列实验去研究该参数影响下Oracle和RMAN的工作行为特性。

    1、基本参数和实验环境

    笔者使用Oracle 11gR2进行测试,具体版本编号为11.2.0.4。

    SQL> select * from v$version;

    BANNER

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

    Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

    PL/SQL Release 11.2.0.3.0 - Production

    CORE    11.2.0.3.0    Production

    TNS for Linux: Version 11.2.0.3.0 - Production

    NLSRTL Version 11.2.0.3.0 – Production

    默认情况下,archivelog deletion policy参数为NONE。

    RMAN> show all;     

    RMAN configuration parameters for database with db_unique_name XXXXDB are:

    CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default

    (篇幅原因,有省略……)

    CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

    该参数常见的集中取值如下:

    configure archivelog deletion policy to backed up 2 times to sbt;

    configure archivelog deletion policy to backed up 1 times to device type disk;

    configure archivelog deletion policy to applied on standby;  --DG专用

    configure archivelog deletion policy to shipped on standby;  --DG专用

    configure archivelog deletion policy clear;

    研究archivelog行为最好的工具视图是v$archived_log。很多DBA喜欢从操作系统层面删除归档日志,但是这种方式是不会直接被Oracle控制文件认可,所以建议使用RMAN或者官方工具来做。

    --已归档未删除日志

    SQL> select count(*) from v$archived_log where archived='YES' and deleted='NO';

      COUNT(*)

    ----------

            13

    2、第一轮备份测试实验

    首先我们修改archivelog deletion policy参数,设置为“两次备份后即可以删除”。

    RMAN> configure archivelog deletion policy to backed up 2 times to device type disk;

    new RMAN configuration parameters:

    CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 2 TIMES TO DISK;

    new RMAN configuration parameters are successfully stored

    手工备份数据库和归档日志,不进行删除动作。

    RMAN> backup database plus archivelog;

    Starting backup at 21-SEP-15

    current log archived

    allocated channel: ORA_DISK_1

    channel ORA_DISK_1: SID=16 device type=DISK

    channel ORA_DISK_1: starting archived log backup set

    channel ORA_DISK_1: specifying archived log(s) in backup set

    input archived log thread=1 sequence=100 RECID=12 STAMP=890690423

    input archived log thread=1 sequence=101 RECID=13 STAMP=890712061

    input archived log thread=1 sequence=102 RECID=14 STAMP=890727732

    input archived log thread=1 sequence=103 RECID=15 STAMP=890776815

    input archived log thread=1 sequence=104 RECID=16 STAMP=890776833

    input archived log thread=1 sequence=105 RECID=17 STAMP=890805616

    input archived log thread=1 sequence=106 RECID=18 STAMP=890814181

    input archived log thread=1 sequence=107 RECID=19 STAMP=890820201

    input archived log thread=1 sequence=108 RECID=20 STAMP=890859629

    input archived log thread=1 sequence=109 RECID=21 STAMP=890892046

    input archived log thread=1 sequence=110 RECID=22 STAMP=890900632

    input archived log thread=1 sequence=111 RECID=23 STAMP=890906655

    input archived log thread=1 sequence=112 RECID=24 STAMP=890942416

    input archived log thread=1 sequence=113 RECID=25 STAMP=890990204

    channel ORA_DISK_1: starting piece 1 at 21-SEP-15

    channel ORA_DISK_1: finished piece 1 at 21-SEP-15

    piece handle=/u01/app/oracle/fast_recovery_area/XXXXDB/backupset/2015_09_21/o1_mf_annnn_TAG20150921T091644_bzypmwty_.bkp tag=TAG20150921T091644 

    (篇幅原因,省略部分……)

    Finished Control File and SPFILE Autobackup at 21-SEP-15

    此时,归档日志被备份,并且没有删除。

    --多出来的两个是由于进行备份时候自动会有switch log

    SQL> select count(*) from v$archived_log where archived='YES' and deleted='NO';

      COUNT(*)

    ----------

            15

    下面进行第二次实验。

    RMAN> backup database plus archivelog;

    Starting backup at 21-SEP-15

    current log archived

    using channel ORA_DISK_1

    channel ORA_DISK_1: starting archived log backup set

    channel ORA_DISK_1: specifying archived log(s) in backup set

    input archived log thread=1 sequence=100 RECID=12 STAMP=890690423

    input archived log thread=1 sequence=101 RECID=13 STAMP=890712061

    input archived log thread=1 sequence=102 RECID=14 STAMP=890727732

    input archived log thread=1 sequence=103 RECID=15 STAMP=890776815

    input archived log thread=1 sequence=104 RECID=16 STAMP=890776833

    input archived log thread=1 sequence=105 RECID=17 STAMP=890805616

    input archived log thread=1 sequence=106 RECID=18 STAMP=890814181

    input archived log thread=1 sequence=107 RECID=19 STAMP=890820201

    input archived log thread=1 sequence=108 RECID=20 STAMP=890859629

    input archived log thread=1 sequence=109 RECID=21 STAMP=890892046

    input archived log thread=1 sequence=110 RECID=22 STAMP=890900632

    input archived log thread=1 sequence=111 RECID=23 STAMP=890906655

    input archived log thread=1 sequence=112 RECID=24 STAMP=890942416

    input archived log thread=1 sequence=113 RECID=25 STAMP=890990204

    input archived log thread=1 sequence=114 RECID=26 STAMP=890990263

    input archived log thread=1 sequence=115 RECID=27 STAMP=890990391

    channel ORA_DISK_1: starting piece 1 at 21-SEP-15

    channel ORA_DISK_1: finished piece 1 at 21-SEP-15

    piece handle=/u01/app/oracle/fast_recovery_area/XXXXDB/backupset/2015_09_21/o1_mf_annnn_TAG20150921T091951_bzypsqj3_.bkp tag=TAG20150921T091951 

    (篇幅原因,有省略……)

    Finished Control File and SPFILE Autobackup at 21-SEP-15

    第二次备份,之前备份过的日志还出现在自动备份的列表中。但是,在第二次备份的时候,已经备份过两次(deletion policy)的日志并没有自动删除。

    SQL> select count(*) from v$archived_log where archived='YES' and deleted='NO';

      COUNT(*)

    ----------

            17

    归档日志还在fast recovery area中。

    [oracle@Databaseintrawebpro fast_recovery_area]$ du -h

    19M    ./XXXXDB/autobackup/2015_09_21

    9.4M    ./XXXXDB/autobackup/2015_09_17

    29M    ./XXXXDB/autobackup

    151M    ./XXXXDB/onlinelog

    6.0G    ./XXXXDB/backupset/2015_09_21

    108K    ./XXXXDB/backupset/2015_09_17

    6.0G    ./XXXXDB/backupset

    125M    ./XXXXDB/archivelog/2015_09_19

    27M    ./XXXXDB/archivelog/2015_09_21

    4.0K    ./XXXXDB/archivelog/2015_09_15

    127M    ./XXXXDB/archivelog/2015_09_18

    121M    ./XXXXDB/archivelog/2015_09_20

    4.0K    ./XXXXDB/archivelog/2015_09_16

    32M    ./XXXXDB/archivelog/2015_09_17

    431M    ./XXXXDB/archivelog

    9.4M    ./XXXXDB/controlfile

    6.6G    ./XXXXDB

    6.6G    .

    此时,归档日志和备份次数,在v$archived_log中可以方便的找出来。

    SQL> alter system switch logfile;

    System altered

    SQL> select count(*) from v$archived_log where archived='YES' and deleted='NO';

      COUNT(*)

    ----------

            18

    --注意这些已经备份过两次的recid编号

    SQL> select recid, sequence#, archived, deleted, backup_count from v$archived_log where backup_count>1;

        RECID  SEQUENCE# ARCHIVED DELETED BACKUP_COUNT

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

            12        100 YES      NO                2

            13        101 YES      NO                2

            14        102 YES      NO                2

            15        103 YES      NO                2

            16        104 YES      NO                2

            17        105 YES      NO                2

            18        106 YES      NO                2

            19        107 YES      NO                2

            20        108 YES      NO                2

            21        109 YES      NO                2

            22        110 YES      NO                2

            23        111 YES      NO                2

            24        112 YES      NO                2

            25        113 YES      NO                2

            26        114 YES      NO                2

    15 rows selected

    进行第三次备份。

    RMAN> backup database plus archivelog;

    Starting backup at 21-SEP-15

    current log archived

    allocated channel: ORA_DISK_1

    channel ORA_DISK_1: SID=498 device type=DISK

    skipping archived logs of thread 1 from sequence 100 to 114; already backed up

    channel ORA_DISK_1: starting archived log backup set

    channel ORA_DISK_1: specifying archived log(s) in backup set

    input archived log thread=1 sequence=115 RECID=27 STAMP=890990391

    input archived log thread=1 sequence=116 RECID=28 STAMP=890990481

    input archived log thread=1 sequence=117 RECID=29 STAMP=890990667

    input archived log thread=1 sequence=118 RECID=30 STAMP=890993128

    channel ORA_DISK_1: starting piece 1 at 21-SEP-15

    channel ORA_DISK_1: finished piece 1 at 21-SEP-15

    piece 

    (篇幅原因,有省略…….)

    Finished Control File and SPFILE Autobackup at 21-SEP-15

    注意:备份过两次的日志,没有出现在RMAN自动备份的列表中。这里我们定义到了删除策略的一个行为:当满足删除条件的时候,归档日志是不会进入备份集合列表的。

    归档日志信息:

    SQL> select recid, sequence#, archived, deleted, backup_count from v$archived_log where backup_count>1;

        RECID  SEQUENCE# ARCHIVED DELETED BACKUP_COUNT

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

            12        100 YES      YES                2

            13        101 YES      YES                2

            14        102 YES      YES                2

            15        103 YES      YES                2

            16        104 YES      YES                2

            17        105 YES      YES                2

            18        106 YES      YES                2

            19        107 YES      YES                2

            20        108 YES      YES                2

            21        109 YES      YES                2

            22        110 YES      YES                2

            23        111 YES      YES                2

            24        112 YES      YES                2

            25        113 YES      YES                2

            26        114 YES      NO                2

            27        115 YES      NO                2

            28        116 YES      NO                2

    17 rows selected

    注意:一部分归档日志被删除,但是并没有所有上次备份过两次的日志都删除掉了,比如recid=26的日志。此时,备份fast recovery area空间情况发生了变化。

    [oracle@Databaseintrawebpro fast_recovery_area]$ du -h

    29M    ./XXXXDB/autobackup/2015_09_21

    4.0K    ./XXXXDB/autobackup/2015_09_17

    29M    ./XXXXDB/autobackup

    151M    ./XXXXDB/onlinelog

    5.5G    ./XXXXDB/backupset/2015_09_21

    4.0K    ./XXXXDB/backupset/2015_09_17

    5.5G    ./XXXXDB/backupset

    4.0K    ./XXXXDB/archivelog/2015_09_19

    2.5M    ./XXXXDB/archivelog/2015_09_21

    4.0K    ./XXXXDB/archivelog/2015_09_15

    4.0K    ./XXXXDB/archivelog/2015_09_18

    4.0K    ./XXXXDB/archivelog/2015_09_20

    4.0K    ./XXXXDB/archivelog/2015_09_16

    4.0K    ./XXXXDB/archivelog/2015_09_17

    2.6M    ./XXXXDB/archivelog

    9.4M    ./XXXXDB/controlfile

    5.7G    ./XXXXDB

    5.7G    .

    在alert log中,我们看到了Oracle自动删除的动作。

    Mon Sep 21 09:24:27 2015

    Expanded controlfile section 11 from 28 to 56 records

    Requested to grow by 28 records; added 1 blocks of records

    Archived Log entry 29 added for thread 1 sequence 117 ID 0x774e158c dest 1:

    Mon Sep 21 10:05:28 2015

    ALTER SYSTEM ARCHIVE LOG

    Mon Sep 21 10:05:28 2015

    Thread 1 advanced to log sequence 119 (LGWR switch)

      Current log# 2 seq# 119 mem# 0: /u01/app/oracle/oradata/XXXXDB/onlinelog/o1_mf_2_bxzzjj5w_.log

      Current log# 2 seq# 119 mem# 1: /u01/app/oracle/fast_recovery_area/XXXXDB/onlinelog/o1_mf_2_bxzzjj80_.log

    Archived Log entry 30 added for thread 1 sequence 118 ID 0x774e158c dest 1:

    Mon Sep 21 10:05:47 2015

    Deleted Oracle managed file /u01/app/oracle/fast_recovery_area/XXXXDB/backupset/2015_09_17/o1_mf_annnn_TAG20150917T195557_bzoblfck_.bkp

    Deleted Oracle managed file /u01/app/oracle/fast_recovery_area/XXXXDB/archivelog/2015_09_17/o1_mf_1_100_bzokvqj0_.arc

    Deleted Oracle managed file /u01/app/oracle/fast_recovery_area/XXXXDB/autobackup/2015_09_17/o1_mf_s_890682958_bzoblglw_.bkp

    Deleted Oracle managed file /u01/app/oracle/fast_recovery_area/XXXXDB/archivelog/2015_09_18/o1_mf_1_101_bzp6zx31_.arc

    Deleted Oracle managed file /u01/app/oracle/fast_recovery_area/XXXXDB/archivelog/2015_09_18/o1_mf_1_102_bzpp9nln_.arc

    Deleted Oracle managed file /u01/app/oracle/fast_recovery_area/XXXXDB/archivelog/2015_09_18/o1_mf_1_103_bzr67h1h_.arc

    Deleted Oracle managed file /u01/app/oracle/fast_recovery_area/XXXXDB/archivelog/2015_09_18/o1_mf_1_104_bzr6812s_.arc

    Deleted Oracle managed file /u01/app/oracle/fast_recovery_area/XXXXDB/archivelog/2015_09_19/o1_mf_1_105_bzs2cj5y_.arc

    Deleted Oracle managed file /u01/app/oracle/fast_recovery_area/XXXXDB/archivelog/2015_09_19/o1_mf_1_106_bzsbq54p_.arc

    Deleted Oracle managed file /u01/app/oracle/fast_recovery_area/XXXXDB/archivelog/2015_09_19/o1_mf_1_107_bzsjm99v_.arc

    Deleted Oracle managed file /u01/app/oracle/fast_recovery_area/XXXXDB/archivelog/2015_09_19/o1_mf_1_108_bztq3f2v_.arc

    Deleted Oracle managed file /u01/app/oracle/fast_recovery_area/XXXXDB/archivelog/2015_09_20/o1_mf_1_109_bzvprgf1_.arc

    Deleted Oracle managed file /u01/app/oracle/fast_recovery_area/XXXXDB/archivelog/2015_09_20/o1_mf_1_110_bzvz4rj7_.arc

    Deleted Oracle managed file /u01/app/oracle/fast_recovery_area/XXXXDB/archivelog/2015_09_20/o1_mf_1_111_bzw50zmb_.arc

    Deleted Oracle managed file /u01/app/oracle/fast_recovery_area/XXXXDB/archivelog/2015_09_20/o1_mf_1_112_bzx7yj9g_.arc

    Deleted Oracle managed file /u01/app/oracle/fast_recovery_area/XXXXDB/archivelog/2015_09_21/o1_mf_1_113_bzypmw8c_.arc

    Deleted Oracle managed file /u01/app/oracle/fast_recovery_area/XXXXDB/backupset/2015_09_21/o1_mf_annnn_TAG20150921T091644_bzypmwty_.bkp

    Mon Sep 21 10:05:58 2015

    Deleted Oracle managed file /u01/app/oracle/fast_recovery_area/XXXXDB/backupset/2015_09_21/o1_mf_nnndf_TAG20150921T091647_bzypn055_.bkp

    Mon Sep 21 10:06:15 2015

    ALTER SYSTEM ARCHIVE LOG

    Mon Sep 21 10:06:15 2015

    Thread 1 advanced to log sequence 120 (LGWR switch)

      Current log# 3 seq# 120 mem# 0: /u01/app/oracle/oradata/XXXXDB/onlinelog/o1_mf_3_bxzzjl0z_.log

      Current log# 3 seq# 120 mem# 1: /u01/app/oracle/fast_recovery_area/XXXXDB/onlinelog/o1_mf_3_bxzzjl35_.log

    Archived Log entry 31 added for thread 1 sequence 119 ID 0x774e158c dest 1:

    日志里面,Oracle不仅仅删除了部分备份过两次的日志,而且删除了一个已经obsolete的备份集合。这个体现了一个特性:当fast recovery area空间紧张的时候,obsolete和符合删除deletion policy的归档日志会被自动删除。

    3、归档日志删除实验

    下面继续进行第四次备份动作。

    RMAN> backup database plus archivelog;

    Starting backup at 21-SEP-15

    current log archived

    allocated channel: ORA_DISK_1

    channel ORA_DISK_1: SID=592 device type=DISK

    skipping archived logs of thread 1 from sequence 114 to 116; already backed up

    channel ORA_DISK_1: starting archived log backup set

    channel ORA_DISK_1: specifying archived log(s) in backup set

    input archived log thread=1 sequence=117 RECID=29 STAMP=890990667

    input archived log thread=1 sequence=118 RECID=30 STAMP=890993128

    input archived log thread=1 sequence=119 RECID=31 STAMP=890993175

    input archived log thread=1 sequence=120 RECID=32 STAMP=890994130

    channel ORA_DISK_1: starting piece 1 at 21-SEP-15

    channel ORA_DISK_1: finished piece 1 at 21-SEP-15

    (篇幅原因,有省略……)

    handle=/u01/app/Oracle/fast_recovery_area/CHINAREDB/autobackup/2015_09_21/o1_mf_s_890994178_bzytj261_.bkp comment=NONE

    Finished Control File and SPFILE Autobackup at 21-SEP-15

    根据删除策略,备份过两次的日志,是不会继续备份的。

    SQL> select recid, sequence#, archived, deleted, backup_count from v$archived_log where backup_count>1;

        RECID  SEQUENCE# ARCHIVED DELETED BACKUP_COUNT

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

    (篇幅原因,有省略……)

            23        111 YES      YES                2

            24        112 YES      YES                2

            25        113 YES      YES                2

            26        114 YES      YES                2

            27        115 YES      YES                2

            28        116 YES      NO                2

            29        117 YES      NO                2

            30        118 YES      NO                2

            31        119 YES      NO                2

    20 rows selected

    连续两次切换动作。

    SQL> alter system switch logfile;

    System altered

    SQL> alter system switch logfile;

    System altered

    SQL> select recid, sequence#, archived, deleted, backup_count from v$archived_log where recid>18;

        RECID  SEQUENCE# ARCHIVED DELETED BACKUP_COUNT

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

     (篇幅原因,有省略……)

            26        114 YES      YES                2

            27        115 YES      YES                2

            28        116 YES      NO                2

            29        117 YES      NO                2

            30        118 YES      NO                2

            31        119 YES      NO                2

            32        120 YES      NO                1

            33        121 YES      NO                1

            34        122 YES      NO                0

            35        123 YES      NO                0

    17 rows selected

    在RMAN里面,可以使用archive log all进行直接的删除动作。

    RMAN> delete archivelog all;

    released channel: ORA_DISK_1

    allocated channel: ORA_DISK_1

    channel ORA_DISK_1: SID=592 device type=DISK

    RMAN-08138: WARNING: archived log not deleted - must create more backups

    archived log file name=/u01/app/oracle/fast_recovery_area/CHINAREDB/archivelog/2015_09_21/o1_mf_1_120_bzytgl7c_.arc thread=1 sequence=120

    RMAN-08138: WARNING: archived log not deleted - must create more backups

    archived log file name=/u01/app/oracle/fast_recovery_area/CHINAREDB/archivelog/2015_09_21/o1_mf_1_121_bzytj0wf_.arc thread=1 sequence=121

    RMAN-08138: WARNING: archived log not deleted - must create more backups

    archived log file name=/u01/app/oracle/fast_recovery_area/CHINAREDB/archivelog/2015_09_21/o1_mf_1_122_bzytlmsc_.arc thread=1 sequence=122

    RMAN-08138: WARNING: archived log not deleted - must create more backups

    archived log file name=/u01/app/oracle/fast_recovery_area/CHINAREDB/archivelog/2015_09_21/o1_mf_1_123_bzytlpv0_.arc thread=1 sequence=123

    List of Archived Log Copies for database with db_unique_name CHINAREDB

    =====================================================================

    Key    Thrd Seq    S Low Time 

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

    28      1    116    A 21-SEP-15

            Name: /u01/app/oracle/fast_recovery_area/CHINAREDB/archivelog/2015_09_21/o1_mf_1_116_bzypwksv_.arc

    29      1    117    A 21-SEP-15

            Name: /u01/app/oracle/fast_recovery_area/CHINAREDB/archivelog/2015_09_21/o1_mf_1_117_bzyq2cc6_.arc

    30      1    118    A 21-SEP-15

            Name: /u01/app/oracle/fast_recovery_area/CHINAREDB/archivelog/2015_09_21/o1_mf_1_118_bzysh8hg_.arc

    31      1    119    A 21-SEP-15

            Name: /u01/app/oracle/fast_recovery_area/CHINAREDB/archivelog/2015_09_21/o1_mf_1_119_bzysjq6c_.arc

    Do you really want to delete the above objects (enter YES or NO)? yes

    deleted archived log

    archived log file name=/u01/app/oracle/fast_recovery_area/CHINAREDB/archivelog/2015_09_21/o1_mf_1_116_bzypwksv_.arc RECID=28 STAMP=890990481

    deleted archived log

    archived log file name=/u01/app/oracle/fast_recovery_area/CHINAREDB/archivelog/2015_09_21/o1_mf_1_117_bzyq2cc6_.arc RECID=29 STAMP=890990667

    deleted archived log

    archived log file name=/u01/app/oracle/fast_recovery_area/CHINAREDB/archivelog/2015_09_21/o1_mf_1_118_bzysh8hg_.arc RECID=30 STAMP=890993128

    deleted archived log

    archived log file name=/u01/app/oracle/fast_recovery_area/CHINAREDB/archivelog/2015_09_21/o1_mf_1_119_bzysjq6c_.arc RECID=31 STAMP=890993175

    Deleted 4 objects

    从recid=28开始,没有被删除的日志一共有8个。从RMAN里面,本次只删除了备份过两次的归档日志。备份次数低于两次的日志,RMAN是拒绝进行删除的。这个是deletion policy的另一个特性:想删除,也不能轻易删除。

    4、Obsolete

    RMAN引入了很多概念,obsolete、expired是初学者们经常纠结的话题。简单的说,obsolete是依据备份保留策略而言的,如果不满足保留策略的备份集合,就认为是obsolete性质。备份集合是否obsolete,是不需要额外的操作的(如crosscheck),从控制文件中就可以定义得知。

    而expired反映了备份集合、归档日志在文件系统与控制文件的不匹配。如果我们“背着”Oracle删除了文件系统中的对象,Oracle是不会自己知道的,是需要手工进行的crosscheck才能知道。所谓的删除expired,删除的也是控制文件中的信息记录。

    在deletion policy控制下,我们又有了一个删除定义维度。原则上,是不会影响obsolete删除的。

    RMAN> show all;

    RMAN configuration parameters for database with db_unique_name CHINAREDB are:

    CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default

    CONFIGURE BACKUP OPTIMIZATION OFF; # default

    RMAN> delete obsolete;

    RMAN retention policy will be applied to the command

    RMAN retention policy is set to redundancy 1

    allocated channel: ORA_DISK_1

    channel ORA_DISK_1: SID=592 device type=DISK

    Deleting the following obsolete backups and copies:

    Type                Key    Completion Time    Filename/Handle

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

    Backup Set          15    21-SEP-15         

      Backup Piece      15    21-SEP-15          /u01/app/oracle/fast_recovery_area/CHINAREDB/backupset/2015_09_21/o1_mf_annnn_TAG20150921T092121_bzypwkxf_.bkp

    Backup Set          16    21-SEP-15         

      Backup Piece      16    21-SEP-15          /u01/app/oracle/fast_recovery_area/CHINAREDB/autobackup/2015_09_21/o1_mf_s_890990482_bzypwm2p_.bkp

    Backup Set          17    21-SEP-15         

      Backup Piece      17    21-SEP-15          /u01/app/oracle/fast_recovery_area/CHINAREDB/backupset/2015_09_21/o1_mf_annnn_TAG20150921T100528_bzysh8xg_.bkp

    Backup Set          18    21-SEP-15         

      Backup Piece      18    21-SEP-15          /u01/app/oracle/fast_recovery_area/CHINAREDB/backupset/2015_09_21/o1_mf_nnndf_TAG20150921T100529_bzyshbbp_.bkp

    Backup Set          19    21-SEP-15         

      Backup Piece      19    21-SEP-15          /u01/app/oracle/fast_recovery_area/CHINAREDB/backupset/2015_09_21/o1_mf_annnn_TAG20150921T100615_bzysjq9x_.bkp

    Backup Set          20    21-SEP-15         

      Backup Piece      20    21-SEP-15          /u01/app/oracle/fast_recovery_area/CHINAREDB/autobackup/2015_09_21/o1_mf_s_890993176_bzysjrgn_.bkp

    Archive Log          32    21-SEP-15          /u01/app/oracle/fast_recovery_area/CHINAREDB/archivelog/2015_09_21/o1_mf_1_120_bzytgl7c_.arc

    Backup Set          21    21-SEP-15         

      Backup Piece      21    21-SEP-15          /u01/app/oracle/fast_recovery_area/CHINAREDB/backupset/2015_09_21/o1_mf_annnn_TAG20150921T102210_bzytglmn_.bkp

    (篇幅原因,有删除……)

    Deleted 8 objects

    5、delete force archivelog

    重新设置deletion policy参数。

    RMAN> configure archivelog deletion policy to backed up 1 times to device type disk;

    new RMAN configuration parameters:

    CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO DISK;

    new RMAN configuration parameters are successfully stored

    切换日志,提供归档用于使用。

    SQL> alter system switch logfile;

    System altered

    SQL> alter system switch logfile;

    System altered

    SQL> alter system switch logfile;

    System altered

    SQL> select recid, sequence#, archived, deleted, backup_count from v$archived_log;

        RECID  SEQUENCE# ARCHIVED DELETED BACKUP_COUNT

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

            1        25 YES      NO                0

            2        26 YES      NO                0

            3        27 YES      NO                0

            4        28 YES      NO                0

            5        29 YES      NO                0

    进行第一次备份。

    RMAN> backup database plus archivelog;

    Starting backup at 21-SEP-15

    current log archived

    allocated channel: ORA_DISK_1

    channel ORA_DISK_1: SID=479 device type=DISK

    channel ORA_DISK_1: starting archived log backup set

    channel ORA_DISK_1: specifying archived log(s) in backup set

    input archived log thread=1 sequence=25 RECID=1 STAMP=891026065

    input archived log thread=1 sequence=26 RECID=2 STAMP=891026104

    input archived log thread=1 sequence=27 RECID=3 STAMP=891026115

    input archived log thread=1 sequence=28 RECID=4 STAMP=891026116

    input archived log thread=1 sequence=29 RECID=5 STAMP=891026117

    input archived log thread=1 sequence=30 RECID=6 STAMP=891026158

    channel ORA_DISK_1: starting piece 1 at 21-SEP-15

    channel ORA_DISK_1: finished piece 1 at 21-SEP-15

    piece 

    (篇幅原因,有省略……)

    channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02

    Finished backup at 21-SEP-15

    备份后,日志情况。

    SQL> select recid, sequence#, archived, deleted, backup_count from v$archived_log;

        RECID  SEQUENCE# ARCHIVED DELETED BACKUP_COUNT

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

            1        25 YES      NO                1

            2        26 YES      NO                1

            3        27 YES      NO                1

            4        28 YES      NO                1

            5        29 YES      NO                1

            6        30 YES      NO                1

            7        31 YES      NO                1

    7 rows selected

    经过系列备份实验后,日志情况如下:

    SQL> alter system switch logfile;

    System altered

    SQL> select recid, sequence#, archived, deleted, backup_count from v$archived_log;

        RECID  SEQUENCE# ARCHIVED DELETED BACKUP_COUNT

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

            1        25 YES      YES                1

            2        26 YES      YES                1

            3        27 YES      YES                1

            4        28 YES      YES                1

            5        29 YES      YES                1

            6        30 YES      YES                1

            7        31 YES      YES                1

            8        32 YES      YES                1

            9        33 YES      YES                1

            10        34 YES      YES                1

            11        35 YES      YES                1

            12        36 YES      NO                1

            13        37 YES      NO                1

            14        38 YES      NO                0

    14 rows selected

    使用archive log delete all,可以将满足删除条件的日志删除。

    RMAN> delete archivelog all;

    allocated channel: ORA_DISK_1

    channel ORA_DISK_1: SID=479 device type=DISK

    RMAN-08138: WARNING: archived log not deleted - must create more backups

    archived log file name=/u01/app/oracle/fast_recovery_area/SICSDB/archivelog/2015_09_21/o1_mf_1_38_c000yd44_.arc thread=1 sequence=38

    List of Archived Log Copies for database with db_unique_name SICSDB

    =====================================================================

    Key    Thrd Seq    S Low Time 

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

    12      1    36      A 21-SEP-15

            Name: /u01/app/oracle/fast_recovery_area/SICSDB/archivelog/2015_09_21/o1_mf_1_36_bzzw2jco_.arc

    13      1    37      A 21-SEP-15

            Name: /u01/app/oracle/fast_recovery_area/SICSDB/archivelog/2015_09_21/o1_mf_1_37_c000f3nv_.arc

    Do you really want to delete the above objects (enter YES or NO)? yes

    deleted archived log

    archived log file name=/u01/app/oracle/fast_recovery_area/SICSDB/archivelog/2015_09_21/o1_mf_1_36_bzzw2jco_.arc RECID=12 STAMP=891028560

    deleted archived log

    archived log file name=/u01/app/oracle/fast_recovery_area/SICSDB/archivelog/2015_09_21/o1_mf_1_37_c000f3nv_.arc RECID=13 STAMP=891032995

    Deleted 2 objects

    只有两个日志满足条件,被自动删除。

    SQL> select recid, sequence#, archived, deleted, backup_count from v$archived_log;

        RECID  SEQUENCE# ARCHIVED DELETED BACKUP_COUNT

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

            1        25 YES      YES                1

            2        26 YES      YES                1

            3        27 YES      YES                1

            4        28 YES      YES                1

            5        29 YES      YES                1

            6        30 YES      YES                1

            7        31 YES      YES                1

            8        32 YES      YES                1

            9        33 YES      YES                1

            10        34 YES      YES                1

            11        35 YES      YES                1

            12        36 YES      YES                1

            13        37 YES      YES                1

            14        38 YES      NO                0

            15        39 YES      NO                0

    15 rows selected

    但是,使用force语句,可以忽略掉delete policy设置。

    RMAN> delete force archivelog all;

    released channel: ORA_DISK_1

    allocated channel: ORA_DISK_1

    channel ORA_DISK_1: SID=479 device type=DISK

    List of Archived Log Copies for database with db_unique_name SICSDB

    =====================================================================

    Key    Thrd Seq    S Low Time 

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

    14      1    38      A 21-SEP-15

            Name: /u01/app/oracle/fast_recovery_area/SICSDB/archivelog/2015_09_21/o1_mf_1_38_c000yd44_.arc

    15      1    39      A 21-SEP-15

            Name: /u01/app/oracle/fast_recovery_area/SICSDB/archivelog/2015_09_21/o1_mf_1_39_c0012x9g_.arc

    Do you really want to delete the above objects (enter YES or NO)? yes

    deleted archived log

    archived log file name=/u01/app/oracle/fast_recovery_area/SICSDB/archivelog/2015_09_21/o1_mf_1_38_c000yd44_.arc RECID=14 STAMP=891033548

    deleted archived log

    archived log file name=/u01/app/oracle/fast_recovery_area/SICSDB/archivelog/2015_09_21/o1_mf_1_39_c0012x9g_.arc RECID=15 STAMP=891033693

    Deleted 2 objects

    RMAN> 

    SQL> select recid, sequence#, archived, deleted, backup_count from v$archived_log;

        RECID  SEQUENCE# ARCHIVED DELETED BACKUP_COUNT

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

            1        25 YES      YES                1

            2        26 YES      YES                1

            3        27 YES      YES                1

            4        28 YES      YES                1

            5        29 YES      YES                1

            6        30 YES      YES                1

            7        31 YES      YES                1

            8        32 YES      YES                1

            9        33 YES      YES                1

            10        34 YES      YES                1

            11        35 YES      YES                1

            12        36 YES      YES                1

            13        37 YES      YES                1

            14        38 YES      YES                0

            15        39 YES      YES                0

    15 rows selected

    6、结论

    RMAN中对于archive log删除策略的参数,是十分方便使用的。

    本文引自:https://www.linuxidc.com/Linux/2015-10/124126p2.htm

    展开全文
  • 今天碰到了一个怪问题,明明archivelog 备份成功,可是crosscheck的时候去显示 对归档日志的验证失败,到网上搜了一下,发现是NLS_LANG的问题,set NLS_LANG=AMERICAN_AMERICA.ZH...

    今天碰到了一个怪问题,明明archivelog 备份成功,可是crosscheck的时候去显示 对归档日志的验证失败,到网上搜了一下,发现是NLS_LANG的问题,set NLS_LANG=AMERICAN_AMERICA.ZHS16GBK 即可。

    tahiti关于crosscheck 的说明

    Purpose

    To verify the status of backups and copies recorded in the RMAN repository against media such as disk or tape. The CROSSCHECK command only processes files created on the same device type as the channels running the crosscheck.

    Status of RMAN Backups

    The CROSSCHECK command checks only objects marked AVAILABLE or EXPIRED by examining the files on disk for DISK channels or by querying the media manager for sbt channels. Table 2-2 describes the meaning of each status.

    Table 2-2 Meaning of Crosscheck Status

    StatusDescription

    EXPIRED

    Object is not found either in file system (for DISK) or in the media manager (for sbt). Note that for a backup set to be EXPIRED, all backup pieces in the set must be EXPIRED.

    Note: EXPIRED does not mean the same as OBSOLETE.

    个人注释:obsolete:当备份或者副本根据保存策略而被丢弃的时候,就会被标记为该状态。
    expired:使用crosscheck对备份进行校验,当备份或者副本被存储在rman目录中,但是并没有物理存在于备份介质上时,就会被标记为该状态

    AVAILABLE

    Object is available for use by RMAN. For a backup set to be AVAILABLE, all backup pieces in the set must have the status AVAILABLE.

    UNAVAILABLE

    Object is not available for use by RMAN. For a backup set to be UNAVAILABLE, all backup pieces in the set must have the status UNAVAILABLE.

    The CROSSCHECK command does not delete any files that it is unable to find, but updates their repository records to EXPIRED. Then, you can run DELETE EXPIRED to remove the repository records for all expired files as well as any existing physical files whose records show the status EXPIRED.

    If some backup pieces or copies were erroneously marked as EXPIRED, for example, because the media manager was misconfigured, then after ensuring that the files really do exist in the media manager, run the CROSSCHECK BACKUP command again to restore those files to AVAILABLE status.

    网上关于这个问题的原文如下


    RMAN的怪现象
    ===========================================================

    环境:9201 on windows2k
    情况:更改了log_archive_dest_1后,crosscheck archivelog all;就会报告失败,可实际上是成功的,只不过是显示错误.

    真弄不明白为什么?下面是全过程.


    1 归档路径是默认位置:
    SQL> show parameter log_archive_dest_1

    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    log_archive_dest_1 string LOCATION=D:oracleoradatates
    tarchive
    log_archive_dest_10 string

    2 此时有一个归档日志:
    SQL> select count(1) from v$archived_log;

    COUNT(1)
    ----------
    1

    3 更改归档路径:
    SQL> alter system set log_archive_dest_1='LOCATION=D:oracleoradatatestarchiv
    e1' scope=both;

    系统已更改。

    4 插入数据使数据库归档:
    SQL> insert into test select * from test;

    已创建24660行。

    SQL> commit;

    提交完成。

    SQL> select count(1) from v$archived_log;

    COUNT(1)
    ----------
    1

    SQL> insert into test select * from test;

    已创建49320行。

    SQL> select count(1) from v$archived_log;

    COUNT(1)
    ----------
    2

    SQL> commit;

    提交完成。

    5 在rman中用crosscheck检查归档日志,2个归档日志都是失败的:
    RMAN> crosscheck archivelog all;

    释放的通道: ORA_DISK_1
    分配的通道: ORA_DISK_1
    通道 ORA_DISK_1: sid=14 devtype=DISK
    对归档日志的验证失败
    存档日志文件名 =D:ORACLEORADATATESTARCHIVE1_47.DBF 记录 ID=1 时间戳 =572866
    683
    对归档日志的验证失败
    存档日志文件名 =D:ORACLEORADATATESTARCHIVE11_48.DBF 记录 ID=2 时间戳 =57286
    6931
    已交叉检验的 2 对象

    6 试着同步一下,看行不行,结果不行,crosscheck还是失败:
    RMAN> resync catalog;

    正在启动全部恢复目录的 resync
    完成全部 resync

    RMAN> crosscheck archivelog all;

    释放的通道: ORA_DISK_1
    分配的通道: ORA_DISK_1
    通道 ORA_DISK_1: sid=14 devtype=DISK
    对归档日志的验证失败
    存档日志文件名 =D:ORACLEORADATATESTARCHIVE1_47.DBF 记录 ID=1 时间戳 =572866
    683
    对归档日志的验证失败
    存档日志文件名 =D:ORACLEORADATATESTARCHIVE11_48.DBF 记录 ID=2 时间戳 =57286
    6931
    已交叉检验的 2 对象

    7 用list expired看看是否有失效的archive log,证明没有失效的archive log:
    RMAN> list expired archivelog all;

    说明与恢复目录中的任何存档日志均不匹配

    8 更改语言环境试试,结果再次crosscheck,2个archive log 都成功了:
    RMAN> exit


    恢复管理器完成。

    C:>set nls_lang=american_america.zhs16gbk

    C:>rman catalog rman/rman@safe target /

    Recovery Manager: Release 9.2.0.1.0 - Production

    Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.

    connected to target database: TEST (DBID=1870953724)
    connected to recovery catalog database

    RMAN> crosscheck archivelog all;

    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: sid=9 devtype=DISK
    validation succeeded for archived log
    archive log filename=D:ORACLEORADATATESTARCHIVE1_47.DBF recid=1 stamp=57286
    6683
    validation succeeded for archived log
    archive log filename=D:ORACLEORADATATESTARCHIVE11_48.DBF recid=2 stamp=5728
    66931
    Crosschecked 2 objects

    9 不解,难道是bug?

    10 进一步验证是语言显示的错误:
    还是中文的环境,看备份archvielog是否成功:
    RMAN> crosscheck archivelog all;

    分配的通道: ORA_DISK_1
    通道 ORA_DISK_1: sid=16 devtype=DISK
    对归档日志的验证失败
    存档日志文件名 =D:ORACLEORADATATESTARCHIVE1_47.DBF 记录
    683
    对归档日志的验证失败
    存档日志文件名 =D:ORACLEORADATATESTARCHIVE11_48.DBF 记
    6931
    已交叉检验的 2 对象


    RMAN> backup archivelog all format 'd:archive_bak%T.bak';

    启动 backup 于 28-10月-05
    当前日志已存档
    使用通道 ORA_DISK_1
    通道 ORA_DISK_1: 正在启动存档日志备份集
    通道 ORA_DISK_1: 正在指定备份集中的存档日志
    输入存档日志线程 =1 序列 =47 记录 ID=1 时间戳=572866683
    输入存档日志线程 =1 序列 =48 记录 ID=2 时间戳=572866931
    输入存档日志线程 =1 序列 =49 记录 ID=3 时间戳=572868387
    通道 ORA_DISK_1: 正在启动段 1 于 28-10月-05
    通道 ORA_DISK_1: 已完成段 1 于 28-10月-05
    段 handle=D:ARCHIVE_BAK20051028.BAK comment=NONE
    通道 ORA_DISK_1: 备份集已完成, 经过时间:00:00:02
    完成 backup 于 28-10月-05

    RMAN> list backup;


    备份集列表
    ===================

    BS 关键字 大小 设备类型占用时间 完成时间
    ------- ---------- ----------- ------------ ----------
    616 12M DISK 00:00:02 28-10月-05
    BP 关键字: 617 状态: AVAILABLE 标记:TAG20051028T100627
    段名:D:ARCHIVE_BAK20051028.BAK

    备份集 616 中的已存档日志列表
    Thrd Seq 低 SCN 短时间 下一个 SCN 下一次
    ---- ------- ---------- ---------- ---------- ---------
    1 47 48051 27-10月-05 68045 28-10月-05
    1 48 68045 28-10月-05 68167 28-10月-05
    1 49 68167 28-10月-05 68664 28-10月-05

    结果是成功的.

    11 删除归档路径中的归档,然后从备份中恢复.
    RMAN> crosscheck archivelog all;

    released channel: ORA_DISK_1
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: sid=9 devtype=DISK
    validation failed for archived log
    archive log filename=D:ORACLEORADATATESTARCHIVE1_47.DBF recid=1 stamp=57286
    6683
    validation failed for archived log
    archive log filename=D:ORACLEORADATATESTARCHIVE11_48.DBF recid=2 stamp=5728
    66931
    validation failed for archived log
    archive log filename=D:ORACLEORADATATESTARCHIVE11_49.DBF recid=3 stamp=5728
    68387
    Crosschecked 3 objects


    RMAN> list expired archivelog all;


    List of Archived Log Copies
    Key Thrd Seq S Low Time Name
    ------- ---- ------- - --------- ----
    602 1 47 X 27-OCT-05 D:ORACLEORADATATESTARCHIVE1_47.DBF
    606 1 48 X 28-OCT-05 D:ORACLEORADATATESTARCHIVE11_48.DBF
    614 1 49 X 28-OCT-05 D:ORACLEORADATATESTARCHIVE11_49.DBF

    RMAN> restore archivelog all;

    Starting restore at 28-OCT-05

    using channel ORA_DISK_1
    channel ORA_DISK_1: starting archive log restore to default destination
    channel ORA_DISK_1: restoring archive log
    archive log thread=1 sequence=47
    channel ORA_DISK_1: restoring archive log
    archive log thread=1 sequence=48
    channel ORA_DISK_1: restoring archive log
    archive log thread=1 sequence=49
    channel ORA_DISK_1: restored backup piece 1
    piece handle=D:ARCHIVE_BAK20051028.BAK tag=TAG20051028T100627 params=NULL
    channel ORA_DISK_1: restore complete
    Finished restore at 28-OCT-05

    恢复成功了.说明了那个"失败"就是rman的语言显示问题.

    来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/1698901/viewspace-103236/,如需转载,请注明出处,否则将追究法律责任。

    转载于:http://blog.itpub.net/1698901/viewspace-103236/

    展开全文
  • rman 还原归档日志 restore archivelog
  • archivelog相关知识总结

    2020-02-23 19:20:37
    Archivelog并不能直接得从OS层直接物理删除,因为archivelog的相关信息是记录在controlfile中的,当物理删除后不会改变controlfile的设置。并且在查询相关的动态视图(例如v$archived_log)时,该部分日志仍然标注为...
  • 试试在12.1~19c的RAC连续运行两次 backup archivelog like ‘+%’ not backed up 1 times; 看第二次有没有归档日志 检查归档日志 [oracle@rac1 ~]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production...
  • list archivelog all 是列出控制文件中记录的未被rman delete 命令删除的手工删除的日志文件,如果手工删除之后使用list命令对应的日志文件的status为标记为X! 模拟删除日志yangdb_1_192_762800109.log  oracle@...
  • oracle 12c 启用 archivelog 模式

    千次阅读 2019-01-25 11:33:07
    线上的 oracle 数据库必须启用 archivelog 模式,否则毫无安全性可言. archivelog 对数据库的恢复至关重要,决不能丢失.因为你不清楚数据库什么时候需要进行恢复操作,莫要在需要恢复时发现无法恢复. dbf 方式启用 ...
  • Oracle Archivelog模式

    千次阅读 2018-10-03 20:45:02
    置于ARCHIVELOG模式的数据库可以对联机重做日志进行归档,联机重做日志是循环覆盖的,归档可以长时间保存重做日志。 1.查看数据库是否处于ARCHIVRLOG模式 SQL&amp;gt;ARCHIVE LOG LIST; 2.启动或关闭...
  • 如何删除Archivelog 当ORACLE 归档日志满了后,将无法正常登入ORACLE,需要删除一部分归档日志才能正常登入ORACLE。 一、首先删除归档日志物理文件,归档日志一般都是位于archive目录...
  • 备份归档日志方式: 单独备份归档日志:backup archivelog all 在执行备库时一起备份归档日志:backup database plus archivelog; 这两种方式有什么区别呢? 运行backup archivelog all 命令时执行的步骤: 1.alter ...
  • crosscheck archivelog all; 验证的是DB的归档日志即log_archive_dest参数指定位置的文件,当手工删除了归档日志以后,Rman备份会检测到日志缺失,从而无法进一步继续执行。所以此时需要手工执...
  • del_archivelog

    2018-08-02 22:20:00
    #!/usr/bin/env bash # # INTRO : The script for delete physical standby applied archivelog. # Please set ur environment variables before use it. # Please e...
  • 如何正确地删除ArchivelogArchivelog并不能直接得从OS层直接物理删除,因为archivelog的相关信息是记录在controlfile中的,当物理删除后不会改变controlfile的设置。并且在查询相关的动态视图(例如v$archived_...
  • show ARCHIVELOG DELETION POLICY ; RMAN configuration parameters for database with db_unique_name ORCL are: CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default SQL> set linesize 200; select ...
  • 归档模式下的数据库恢复数据的时候还需要将归档日志内容应用到数据上面 1.确认数据库为归档模式 select log_mode from v$database; 2.通过rman备份USERS表空间 ...allocate channel ch_1 type disk;...
  • 最近在因归档日志暴增,使用delete archivelog all貌似无法清除所有的归档日志,到底是什么原因呢? 1. 1、演示环境 2. SQL> select * from v$version where r...
  • Backup database plus archivelog会备份归档日志Backup database plus archivelog delete input;会备份归档日志并且会在备份结束后删除默认目录下的归档日志、datafile copy还有backup set。适用于单个归档dest路径...
  • oracle 删除 archivelog 的方法

    千次阅读 2017-10-15 17:06:20
    使用rm命令后,此时在os上文件是被删除了,但是archivelog信息还是记录在controlfile里,需要使用rman清除一下。$ rman target / RMAN&gt; crosscheck archivelog all; RMAN&gt; list e
  • backup archivelog all 和plus archivelog

    千次阅读 2016-05-14 17:00:02
    OLTP系统的生产库一般都是打开归档模式,当CUD操作频繁并且时常使用大批量处理时,每天的归档日志也是超级大的,如果... 单独备份归档日志:backup archivelog all  在执行备库时一起备份归档日志:backup datab
  • V$BACKUP_ARCHIVELOG_DETAILS

    2019-07-13 08:38:44
    V$BACKUP_ARCHIVELOG_DETAILS V$BACKUP_ARCHIVELOG_DETAILScontains information about all restorable archive logs. It will include all archived logs backed up in a backup set or proxy copies. Column ....
  • [20150902]rman的list archivelog命令.txt --昨天同事要查询2015/8/13号日志,要确定需要检查日志的范围: RMAN> list archivelog all completed between '2015-08-13' and '2015-08-13 18:00:00...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 32,318
精华内容 12,927
关键字:

archivelog