年底已经过了,还是腾不出时间来做几个库的维护,昨天有个库的速度明显减慢了,不得不抽出时间先对该问题进行解决,根据开发人员的反馈,数据库插入速度的减慢造成了应用队列的快速膨胀,根据反馈的数据库表,进行了分析,发现是由于分区没有维护了,于是进行了如下维护。
1、处理方法
将原来的上限分区表进行分割,分割表空间截止日期为2012年1月31号,2012年1月之后的数据都放在表空间SEC06里
2、处理:分割表分区
SQL>ALTER TABLE ISGIS.GIS_WEICHAI_DATA_1S
SPLIT PARTITION SEC_MAX AT
(TO_DATE('2012-01-31 23:59:59', 'SYYYY-MM-DD HH24:MI:SS'))
INTO (PARTITION SEC05 TABLESPACE SEC_MAX,
PARTITION SEC06 TABLESPACE SEC_MAX);
3、问题
这个过程运行了一个晚上,还是没有处理掉,看到今天早上的监控邮件里出现了ALERT日志错误,先是警告,后是错误,如下:
< ORA-19815: WARNING: db_recovery_file_dest_size of 10737418240 bytes is 100.00% used, and has 0 remaining bytes available.
< ORA-19809: limit exceeded for recovery files
< ORA-19804: cannot reclaim 43566080 bytes disk space from 10737418240 limit
< ORA-19815: WARNING: db_recovery_file_dest_size of 10737418240 bytes is 100.00% used, and has 0 remaining bytes available.
< ORA-19815: WARNING: db_recovery_file_dest_size of 10737418240 bytes is 100.00% used, and has 0 remaining bytes available
< ORA-19809: limit exceeded for recovery files
< ORA-19804: cannot reclaim 43566080 bytes disk space from 10737418240 limit
< ORA-19815: WARNING: db_recovery_file_dest_size of 10737418240 bytes is 100.00% used, and has 0 remaining bytes available.
< ORA-19815: WARNING: db_recovery_file_dest_size of 10737418240 bytes is 100.00% used, and has 0 remaining bytes available
4、问题处理
我的分区是分在2012年1月31号,现在的车辆秒级数据还没有录入进来,肯定是很快进行分区分割的。于是经查是由于上限分区里有异常数据,造成了恢复空间的膨胀。
处理1:首先加大了恢复区大小
SQL> show parameter db_recovery_file_dest_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest_size big integer 10G
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest_size big integer 10G
SQL> show parameter db_recovery_file_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /usr/app/db-server/ora_base/fl
ash_recovery_area
db_recovery_file_dest_size big integer 10G
SQL> !
[oracle@localhost ~]$ df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda1 16G 485M 15G 4% /
none 2.0G 0 2.0G 0% /dev/shm
/dev/sda3 279G 117M 264G 1% /home
/dev/sdb1 917G 503G 368G 58% /sdb/opt
/dev/sdc1 917G 476G 395G 55% /sdc/opt
/dev/sdd1 917G 109M 871G 1% /sdd/opt
/dev/sda6 450G 413G 15G 97% /usr
/dev/sda5 159G 393M 150G 1% /var
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /usr/app/db-server/ora_base/fl
ash_recovery_area
db_recovery_file_dest_size big integer 10G
SQL> !
[oracle@localhost ~]$ df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda1 16G 485M 15G 4% /
none 2.0G 0 2.0G 0% /dev/shm
/dev/sda3 279G 117M 264G 1% /home
/dev/sdb1 917G 503G 368G 58% /sdb/opt
/dev/sdc1 917G 476G 395G 55% /sdc/opt
/dev/sdd1 917G 109M 871G 1% /sdd/opt
/dev/sda6 450G 413G 15G 97% /usr
/dev/sda5 159G 393M 150G 1% /var
看来恢复区所在的空间不多了,先坐下转移恢复区操作:
SQL>alter system set db_recovery_file_dest='/sdd/opt/flash_recovery_area' scope=both;
同时增大恢复区为20G:
SQL>alter system set db_recovery_file_dest_size=20G scope=both;
于是进行数据删除,写了个分批删除SQL脚本用CRONTAB执行。