archivelog 删除后怎么释放空间 oracle archivelog可以删吗

转载

mob64ca140a59b0 2024-03-13 14:54:22

文章标签 archive database validation oracle sql 文章分类 Hive 大数据

一、删除archivelog方法：

Archivelog并不能直接得从OS层直接物理删除，因为archivelog的相关信息是记录在controlfile中的，当物理删除后不会改变controlfile的设置。并且在查询相关的动态视图(例如v$archived_log)时，该部分日志仍然标注为存在。也就是说Oracle并不认为这些日志被删除了，所以在删除archivelog的时候，需要我们在其他地方做一些设置。

(一)、本地物理删除archivelog文件

RAM>list archivelog all;

根据地址，手动删除archivelog 文件。

(二)、使用RMAN清楚物理删除后的记录

可以使用RMAN来删除archivelog，具体可以按以下步骤操作：

1、物理删除archivelog

2、进入RMAN

3、crosscheck archivelog all;

4、delete expried archivelog all;

这样就在一些Oracle的记录中查不到相应的archivelog记录了。

(三)、直接使用RMAN删除archivelog

其实在RMAN中是可以使用命令直接将ARCHIVELOG删除的，命令如下：

RMAN> DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-7';

表示删除7天以前的所有archivelog，但是这个命令的缺点是无法对archivelog进行细致的划分。

(四)、使用RMAN备份aechivelog后删除

这是一种最为稳妥的方法了，使用RMAN备份archivelog，备份后全部删除

backup format '/u01/arch_%T_%s_%U' archivelog all delete input;

或者

backup database plus archivelog;

注：我在库上设置了Streams后，使用RMAN删除archivelog会导致报错RMAN-08137: WARNING: archive log not deleted as it is still needed. 不知道该如何解决，据说是10.2的一个bug，如果真是bug的话，那只能在OS层设置删除，或用RMAN进行强制删除了。

二、归档日志导致空间被占满解决：

两种方法：
1、配置RMAN自动管理ARCHIVELOG。也可在RMAN中将数据备份到磁带上，然后将过期的ARCHIVELOG删除；
2、可以手工来处理，步骤如下
1）将/oracle下的相关ARCHIVELOG日志文件移到别的文件系统下（保留一段时间的ARCHIVELOG日志即可，其他的可移走，用系统命令mv移走）。然后打包、压缩，备份到介质上，此时可将这些移出的文件删除。注意：别在原来的/oracle打包了，否则空间占满了就有些麻烦了。
2）以oracle用户登录，执行rman target /。如有多个实例此时执行rman target 用户名/密码@实例名，进入rman
3）在rman中执行
RMAN>list archivelog all; /*列出所有的归档日志文件
RMAN>crosscheck archivelog all; /*与物理归档日志文件保持同步，之前移走了一部分文件，因此执行此命令后会在/oracle目录下找不到的归档日志标记为expired
RMAN>list expired archivelog all; /*列出所有expired（过期）的归档日志文件，此时你就可看到移走的归档日志文件均被标记为expired
RMAN>delete expired archivelog all; /*在oracle中删除所有过期的expired文件
RMAN>list archivelog all; /*再列出所有的归档日志文件，就可发现移走的日志文件被删掉了
RMAN>exit /*退出

然后，用#df -v或#dr -k再查一个/oracle文件系统的使用率，应该空间就释放掉了。

注意：为确保安全，建议将移走的归档日志文件备份出来。三、实例： Rman Crosscheck删除失效归档当手工删除了归档日志以后，Rman备份会检测到日志缺失，从而无法进一步继续执行。所以此时需要手工执行crosscheck过程，之后Rman备份可以恢复正常。 1.Crosscheck日志 $ rman target / Recovery Manager: Release 9.2.0.4.0 - 64bit Production Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved. connected to target database: AVATAR2 (DBID=2480694409) RMAN> crosscheck archivelog all; using target database controlfile instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=25 devtype=DISK validation failed for archived log archive log filename=/opt/oracle/oradata/avatar2/archive/1_2714.dbf recid=2702 stamp=545107659 validation failed for archived log archive log filename=/opt/oracle/oradata/avatar2/archive/1_2715.dbf recid=2703 stamp=545108268 ........... validation failed for archived log archive log filename=/opt/oracle/oradata/avatar2/archive/1_2985.dbf recid=2973 stamp=545399327 validation succeeded for archived log archive log filename=/opt/oracle/oradata/avatar2/archive/1_2986.dbf recid=2974 stamp=545400820 validation succeeded for archived log archive log filename=/opt/oracle/oradata/avatar2/archive/1_2987.dbf recid=2975 stamp=545401757 validation succeeded for archived log archive log filename=/opt/oracle/oradata/avatar2/archive/1_2988.dbf recid=2976 stamp=545402716 validation succeeded for archived log archive log filename=/opt/oracle/oradata/avatar2/archive/1_2989.dbf recid=2977 stamp=545403661 validation succeeded for archived log archive log filename=/opt/oracle/oradata/avatar2/archive/1_2990.dbf recid=2978 stamp=545404946 validation succeeded for archived log archive log filename=/opt/oracle/oradata/avatar2/archive/1_2991.dbf recid=2979 stamp=545406220 Crosschecked 278 objects RMAN> 2.使用delete expired archivelog all 命令删除所有过期归档日志: RMAN> delete expired archivelog all; released channel: ORA_DISK_1 allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=12 devtype=DISK List of Archived Log Copies Key Thrd Seq S Low Time Name ------- ---- ------- - --------- ---- 376 1 2714 X 23-NOV-04 =/opt/oracle/oradata/avatar2/archive/1_2714.dbf ..... 3.简要介绍一下report obsolete命令使用report obsolete命令报告过期备份 RMAN> report obsolete; RMAN retention policy will be applied to the command RMAN retention policy is set to redundancy 1 Report of obsolete backups and copies Type Key Completion Time Filename/Handle -------------------- ------ ------------------ -------------------- Backup Set 125 01-NOV-04 Backup Piece 125 01-NOV-04 /data1/oracle/orabak/full_1_541045804 Backup Set 131 04-NOV-04 Backup Piece 131 04-NOV-04 /data1/oracle/orabak/full_AVATAR2_20041104_131 .... Backup Set 173 06-DEC-04 Backup Piece 173 06-DEC-04 /data1/oracle/orabak/full_AVATAR2_20041206_173 Backup Set 179 11-DEC-04 Backup Piece 179 11-DEC-04 /data1/oracle/orabak/arch544588206.arc ..... Backup Piece 189 17-DEC-04 /data1/oracle/orabak/arch545106606.arc Backup Set 190 17-DEC-04 Backup Piece 190 17-DEC-04 /data1/oracle/orabak/arch545106665.arc Backup Set 191 20-DEC-04 Backup Piece 191 20-DEC-04 /data1/oracle/orabak/arch_AVATAR2_20041220_194 Archive Log 2973 20-DEC-04 /opt/oracle/oradata/avatar2/archive/1_2985.dbf Archive Log 2971 20-DEC-04 /opt/oracle/oradata/avatar2/archive/1_2984.dbf ..... Archive Log 2705 17-DEC-04 /opt/oracle/oradata/avatar2/archive/1_2717.dbf Archive Log 2704 17-DEC-04 /opt/oracle/oradata/avatar2/archive/1_2716.dbf Archive Log 2703 17-DEC-04 /opt/oracle/oradata/avatar2/archive/1_2715.dbf Archive Log 2702 17-DEC-04 /opt/oracle/oradata/avatar2/archive/1_2714.dbf 4.使用delete obsolete命令删除过期备份: RMAN> delete obsolete; RMAN retention policy will be applied to the command RMAN retention policy is set to redundancy 1 using channel ORA_DISK_1 Deleting the following obsolete backups and copies: Type Key Completion Time Filename/Handle -------------------- ------ ------------------ -------------------- Backup Set 125 01-NOV-04 Backup Piece 125 01-NOV-04 /data1/oracle/orabak/full_1_541045804 .... Archive Log 2704 17-DEC-04 /opt/oracle/oradata/avatar2/archive/1_2716.dbf Archive Log 2703 17-DEC-04 /opt/oracle/oradata/avatar2/archive/1_2715.dbf Archive Log 2702 17-DEC-04 /opt/oracle/oradata/avatar2/archive/1_2714.dbf Do you really want to delete the above objects (enter YES or NO)? yes deleted backup piece backup piece handle=/data1/oracle/orabak/full_AVATAR2_20041206_173 recid=173 stamp=544156241 ..... deleted archive log archive log filename=/opt/oracle/oradata/avatar2/archive/1_2715.dbf recid=2703 stamp=545108268 deleted archive log archive log filename=/opt/oracle/oradata/avatar2/archive/1_2714.dbf recid=2702 stamp=545107659 Deleted 286 objects RMAN> crosscheck archivelog all; released channel: ORA_DISK_1 allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=19 devtype=DISK specification does not match any archive log in the recovery catalog -The End- 四、参考文档：今天在启动自己windowns下的oracle时，遇到如下的情况：　　Microsoft Windows XP [版本 5.1.2600] 　　(C) 版权所有 1985-2001 Microsoft Corp. 　　D:/Documents and Settings/Administrator>sqlplus /nolog 　　SQLPlus: Release 10.2.0.1.0 - Production on 星期日 11月 9 20:59:04 2008 　　Copyright (c) 1982, 2005, Oracle. All rights reserved. 　　SQL> connect / as sysdba 　　已连接。步骤1：*　　SQL> shutdown immediate 　　ORA-01109: 数据库未打开　　已经卸载数据库。　　ORACLE 例程已经关闭。　　SQL> startup mount; 　　ORACLE 例程已经启动。　　Total System Global Area 1593835520 bytes 　　Fixed Size 1251508 bytes 　　Variable Size 536872780 bytes 　　Database Buffers 1048576000 bytes 　　Redo Buffers 7135232 bytes 　　数据库装载完毕。步骤2：　　SQL> alter database open; 　　alter database open 　　* 　　第 1 行出现错误: 　　ORA-16038: 日志 1 序列号 74 无法归档　　ORA-19809: 超出了恢复文件数的限制　　ORA-00312: 联机日志 1 线程 1: 　　'C:/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/REDO01.LOG' 　　查了一下错误信息　　ORA-19809: limit exceeded for recovery files 　　Cause: The limit for recovery files specified by the DB_RECOVERY_FILE_DEST_SIZE was exceeded. 　　Action: The error is accompanied by 19804. See message 19804 for further details. 解决方法：方法1、　　1）将归档设置到其他目录　　修改alter system set log_archive_dest = 其他路径　　2）转移或者删除闪回恢复区里的归档日志。方法2、也可以使用增大闪回恢复区的方法来解决　　SQL> show parameter db_recovery_file_dest_size 　　NAME TYPE VALUE 　　db_recovery_file_dest_size big integer 2G 　　下面是我的解决该问题的全部过程：(使用方法1来解决的) 　　首先试图只使用CLEAR LOGFILE命令来试图转移或者删除闪回恢复区里的归档日志，文档描述如下：　　Use the CLEAR LOGFILE clause to reinitialize an online redo log, optionally without archiving the redo log. 　　CLEAR LOGFILE is similar to adding and dropping a redo log, except that the statement may be issued even if there 　　are only two logs for the thread and may be issued for the current redo log of a closed thread. 　　You must specify UNARCHIVED if you want to reuse a redo log that was not archived. 　　首先，执行下面的语句看看group1是不是current日志组　　SQL> select group#,sequence#,archived,status from v$log; 　　GROUP# SEQUENCE# ARC STATUS 　　1 74 NO INACTIVE 　　3 75 NO INACTIVE 　　2 76 NO CURRENT 　　SQL> alter database clear logfile group 1; 　　alter database clear logfile group 1 　　* 　　第 1 行出现错误: 　　ORA-00350: 日志 1 (实例 orcl 的日志, 线程 1) 需要归档　　ORA-00312: 联机日志 1 线程 1: 　　'C:/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/REDO01.LOG' 　　总结: 日志文件组的状态　　current: 当前正在使用的工作组　　inactive: 非活动组　　active : 归档还没有完成　　unused : 还没有使用,一般新建的工组组都是这个状态　　视图使用 clear unarchived logfile，使用UNARCHIVED会在重做日志不再需要归档的情况下可以定义UNARCHIVED。 SQL> alter database clear unarchived logfile group 1; 　　数据库已更改。　　SQL> select group#,sequence#,archived,status from v$log; 　　GROUP# SEQUENCE# ARC STATUS 　　1 0 YES UNUSED 　　3 75 NO INACTIVE 　　2 76 NO CURRENT 　　SQL> alter database open; 　　数据库已更改。　　但是，在验证以普通用户重新连接数据库时，报如下错误：　　ORA-00257: archiver error. Connect internal only, until freed. 　　查询资料说如下：　　Oracle docs note this about ORA-00257: 　　ORA-00257: archiver error. Connect internal only, until freed. 　　Cause: The archiver process received an error while trying to archive a redo log. If the problem is not resolved soon, the database will stop executing transactions. 　　The most likely cause of this message is the destination device is out of space to store the redo log file. 　　Action: Check archiver trace file for a detailed description of the problem. Also verify that the device specified in the initialization parameter ARCHIVE_LOG_DEST is 　　set up properly for archiving. 　　ORA-00257 is a common error in Oracle 10g. You will usually see ORA-00257 upon connecting to the database because you have encountered a maximum in the flash recovery 　　are, or db_recovery_file_dest_size . 　　ORA-00257错误是由于回闪区内的空间不够造成的，下面还是需要扩大回闪区的大小　　MetaLink offers a wealth of information concerning the resolution of ORA-00257 in Oracle 10g. First, make sure your automatic archiving is enabled. To check the archive 　　lo made, try: 　　SQL> archive log list; 　　数据库日志模式存档模式　　自动存档启用　　存档终点 USE_DB_RECOVERY_FILE_DEST 　　最早的联机日志序列 75 　　下一个存档日志序列 75 　　当前日志序列 77 　　Now, note thatyou can find archive destinations if you are using a destination of USE_DB_RECOVERY_FILE_DEST by: 　　SQL> show parameter db_recovery_file_dest; 　　NAME TYPE VALUE 　　db_recovery_file_dest string C:/oracle/product/10.2.0/flash_recovery_area 　　db_recovery_file_dest_size big integer 2G 　　The next step in resolving ORA-00257 is to find out what value is being used for db_recovery_file_dest_size, use: 　　SQL> SELECT * FROM V$RECOVERY_FILE_DEST; 　　NAME SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES 　　C:/oracle/product/10.2.0/flash_recovery_area 2147483648 2147201024 0 78 　　You may find that the SPACE_USED is the same as SPACE_LIMIT, if this is the case, to resolve ORA-00257 should be remedied by moving the archive logs to some other destination. 　　You next need to archive the log files by, 　　SQL> alter system archive log all; 　　alter system archive log all 　　* 　　第 1 行出现错误: 　　ORA-16038: 日志 3 序列号 75 无法归档　　ORA-19809: 超出了恢复文件数的限制　　ORA-00312: 联机日志 3 线程 1: 　　'C:/ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/REDO03.LOG' 　　It is important to note that within step five of the ORA-00257 resolution, you may also encounter ORA-16020 in the LOG_ARCHIVE_MIN_SUCCEED_DEST, and you should use the proper 　　archivelog path and use (keeping in mind that you may need to take extra measures if you are using Flash Recovery Area as you will receive more errors if you attempt to use 　　LOG_ARCHIVE_DEST): 　　SQL> alter system set LOG_ARCHIVE_DEST_1 = 'location=C:/oracle/product/10.2.0/archivelogpath reopen'; 　　系统已更改。　　The last step in resolving ORA-00257 is to change the logs for verification using: 　　SQL> alter system switch logfile; 　　alter system switch logfile 　　* 　　第 1 行出现错误: 　　ORA-01109: 数据库未打开　　SQL> alter database open; 　　数据库已更改。　　SQL> alter system switch logfile; 　　系统已更改。

五、附脚本

前两天备库的归档日志尚未应用,结果被脚本给压缩了,导致备库向主库再次请求归档日志时主库的归档日志也被压缩而找不到相应文件,结果报错.因以前的压缩及删除归档的脚本存在过多弊端,故重新写了一个通用版的压缩并删除归档程序.

程序要求: 主库或备库部署相同的脚本,并在磁盘上尽可能多的保留归档日志,在空间紧张时删除最早的日志,达到空间循环使用的目的.

脚本如下:

####################压缩脚本####################
      #!/bin/sh
      #fuction gzip archivelog      #created by qds 20100804
     
      . /oracle9/.profile 2>/dev/null;
     
      ORACLE_SID='para';
      ARCH_HOME='/oracle9/app/oracle/product/9.2.0/dbs/arch';
      ARCH_POSTFIX='arc';
     
      cd /oracle9/maindb/dbshell;
     
      function get_role {
      sqlplus  -S "/as sysdba" > ./log/get_role.txt <<EOF
            set head off
            set echo off
            select database_role from v/$database;
            exit;
      EOF
     
        db_role=`sed '/^$/d' ./log/get_role.txt`
     
        if [ "$db_role" == "PRIMARY" ]; then
           return 1;
        elif [ "$db_role" == "PHYSICAL STANDBY" ]; then
           return 2;
        else
           return 3;
        fi;
      }
     
      if [ `ps -ef |grep gzip_archive.sh|wc -l` -ge 3 ]; then
         echo "the process has been running";
         return;
      fi;
     
      get_role;
     
      myflag=$?;
     
      if [ $myflag -eq 1 ]; then
         echo "this database is primary database";  
         #find $ARCH_HOME/ -name "*.$ARCH_POSTFIX" -type f -cmin +60  -exec ls -l  {} /;
         #find $ARCH_HOME/ -name "*.$ARCH_POSTFIX" -type f -cmin +60  -exec gzip  {} /;
        
         ls  -lt $ARCH_HOME/*.$ARCH_POSTFIX  2>/dev/null|awk '{if(NR>3) print $NF}' >./log/gzip_archive.txt
     
         for i in `cat ./log/gzip_archive.txt`
         do
               echo `ls -l $i`;
               gzip $i;
         done;
      
         return;
      elif [ $myflag -eq 2 ]; then
         echo "this database is standby database";
        
      sqlplus -S "/as sysdba" > ./log/db_archived_log.txt <<EOF
                set head off
                set echo off
                set feedback off
                select name from v/$archived_log where applied='YES' and deleted='NO' and first_time>trunc(sysdate-90);
      EOF
         ls  ${ARCH_HOME}/*.$ARCH_POSTFIX  1> ./log/sys_archived_log.txt 2>/dev/null
        
         for i in `cat ./log/db_archived_log.txt`
         do
             for j in `cat ./log/sys_archived_log.txt`
             do
                 if [ $i == $j ]; then
                       echo `ls -l  $i` ;
                       gzip $i;
                       continue;
                    fi;
             done;
         done;
        
         return;
      else
         echo "WARNING:this database is not the  monitor type";  
         return;
      fi;    ####################删除脚本####################
      #!/bin/sh
      #fuction gzip and delete the archivelogs
      #created by qds 20100803
     
      ARCH_HOME='/oracle9/app/oracle/product/9.2.0/dbs/arch';
      OS_TYPE=`uname`;
      SPACE_LIMIT=60;
      ARCH_POSTFIX='arc.gz';
      DEL_ARC_DAYS=60;
      DEL_ARC_INTERVAL=2;
      SPACE_USED=0;
     
      cd /oracle9/maindb/dbshell;
     
      ARCH_ROOT=`echo "$ARCH_HOME"|awk -F / '{ print "/"$2}'`;
      function get_space_used {
         if [ $OS_TYPE='AIX' ]; then
            SPACE_USED=`df -g |grep $ARCH_ROOT|awk '{print $4}'|sed 's/%//g'`
         elif [ $OS_TYPE='Linux' ];then
            SPACE_USED=`df -h |grep $ARCH_ROOT|awk '{print $5}'|sed 's/%//g'`
         fi;
         echo $SPACE_USED
      }
     
      if [ `ps -ef |grep del_archive.sh|wc -l` -ge 3 ]; then
         echo "the process has been running";
         return;
      fi;
     
      while [ "`get_space_used`" -ge $SPACE_LIMIT ]
      do
         #ls -lrt $ARCH_HOME/*.$ARCH_POSTFIX|awk '{if(NR<10) print $NF}'|xargs rm -f;
         find $ARCH_HOME -name "*$ARCH_POSTFIX"  -type f -ctime +$DEL_ARC_DAYS -exec ls -l {} /;
         find $ARCH_HOME -name "*$ARCH_POSTFIX"  -type f -ctime +$DEL_ARC_DAYS -exec rm  {} /;
         DEL_ARC_DAYS=`expr $DEL_ARC_DAYS - $DEL_ARC_INTERVAL`;
         if [ $DEL_ARC_DAYS -le 1 ]; then
            break;
         fi;
      done;

注:由于Aix5.2中不支持find的cmin查找,故注释掉,使用了ls的方式.

写该脚本时曾参考meco的http://www.easyora.net/blog/dg_scripts_list.html#comment-307脚本