RAC数据库大量加载数据造成归档日志空间满处理

20130409，下午，将近4点领导接到客户电话，数据库报ORA-00257错误，数据库归档空间满了；安排我打车前往客户现场。

这是一个数据仓库类型的RAC数据库，版本10.2.0.4.0，系统为红旗linux 5.5，使用ASM存储

现象：

查看数据库告警日志报错不能归档，归档进程停止；

export ORACLE_SID=+ASM2

asmcmd

ASMCMD>lsdg

ASM 存放归档日志的DG_ARCHIVE空间快满了，1T多空间只剩不到50M;

检查：

查看最近tsm备份日志，没有发现问题，正常备份都成功了。

备份策略为半个月一次全备，其余增量备份，每周删除一次过期备份；

备份脚本如下：

0级备份脚本和1级备份脚本都包含：

backup format 'arch_%t_%s_%p.arc' archivelog all delete input;

交叉检验删除过期废弃的备份集脚本：

RMAN> allocate channel for maintenance device type sbt parms 'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opt)';

2> crosscheck archivelog all;

3> crosscheck backup;

4> delete noprompt obsolete;

5> delete noprompt expired backup;

6> release channel;

分析原因：

之前也出现过归档空间满的情况，是因为虚拟带库空间满导致TSM备份出错从而没有及时将归档日志备份成功并清除掉；

这次是最近一周第三方维护公司人员大量往数据库里加载数据，造成归档空间迅速被占满，还没来得及清理空间数据库就不可用了；

处理方法：

第一种情况

因为虚拟带库空间满，所以计划先备份没有删除的归档日志到本地空间，然后删除释放空间，数据库恢复可用，前提是本地有足够大的空间：

rman target /    

backup as compressed backupset archivelog format ‘/arch/%t_%s_%p.arc’;

归档日志量大，备份很费时间；如果着急恢复可用，可先直接删除部分归档日志使数据库迅速可用，然后考虑对数据库做一个全备份。

第二种情况

常规处理方法，先备份最近部分或全部归档日志然后删除，释放空间数据库恢复可用

如备份5月3日的归档然后删除

backup archivelog time between 'sysdate-7' and 'sysdate-6' delete all input;

客户着急恢复数据库可用，数据加载只进行到一半，后续还有很多数据需要加载。

在asm里，找到相应的归档日志，直接rm –r删除掉了几个以日期命名的归档日志目录

临时删除掉3-7日的归档日志，释放近1T空间，更改定时任务，晚上做一次全备份

export ORACLE_SID=+ASM2;

asmcmd

ASMCMD>lsdg

ASMCMD>cd dg_archive

ASMCMD>cd standby/archivelog/

ASMCMD>du -H *

ASMCMD>rm -r 2013-05-03/*

交叉检验相关信息

rman target / nocatelog;

crosscheck archivelog all;

list expired archivelog all;

delete expired archivelog all;

crosscheck archivelog all;

后续思考：

问题一：

将某一天或某些天的归档日志备份到本地然后删除释放空间？

可以根据日志序号或者日期还有匹配关系来选择备份

BACKUP ARCHIVELOG LIKE '...',

delete archivelog until time 'sysdate-7'

DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-7';

语法：

backup archivelog "all, from, high, like, logseq, low, scn, sequence, time, until"

备份所有的归档日志：

backup archivelog all;

backup archivelog all delete input;backup archivelog delete all input;

备份日志序号31以后的所有归档日志：

backup archivelog from logseq 31;   logseq, scn, sequence, time

备份日志序号为31和32的两个归档日志：

backup archivelog sequence between 31 and 32;

备份最近3天的归档日志

backup archivelog time between 'sysdate-3' and 'sysdate';

backup archivelog from time 'sysdate-3';

根据归档日志命名规范匹配备份：

backup archivelog like '%3%';

问题二：

数据仓库可考虑不启用归档模式？

rman中backup archivelog all delete input和delete all input，还是有区别的，如果你有多个archive_dest的设置，1,2,3,4,5。后者带着all的，能够删除掉所有归档位置的归档日志，而前者不带all的，可能无法删除所有节点的。

4.3.7.1.2 Using BACKUP ARCHIVELOG with DELETE INPUT or DELETE ALL INPUT
You can specify the DELETE INPUT or DELETE ALL INPUT clauses for the BACKUP ARCHIVELOG command to delete archived logs after they are backed up, eliminating the separate step of manually deleting the archived redo logs. With DELETE INPUT, RMAN only deletes the specific copy of the archived redo log chosen for the backup set. With DELETE ALL INPUT, RMAN will delete each backed-up archived redo log file from all log archiving destinations.
For example, assume that you archive to /arc_dest1, /arc_dest2, and /arc_dest3, and you run the following command:
BACKUP DEVICE TYPE sbt
ARCHIVELOG ALL
DELETE ALL INPUT;

In this case RMAN backs up only one copy of each log sequence number in these directories, and then deletes all copies of any log that it backed up from the archiving destinations. If you had specified DELETE INPUT rather than DELETE ALL INPUT, then RMAN would only delete the specific archived redo log files that it backed up (for example, it would delete the archived redo log files in /arc_dest1 if those were the files used as the source of the backup, but it would leave the contents of the /arc_dest2 and /arc_dest3 intact) .
If you issue BACKUP ARCHIVELOG ALL or BACKUP ARCHIVELOG LIKE '...', and there are no archived redo log files to back up, then RMAN does not signal an error.