数据库不能正常关闭的问题
数据库版本为10.2.0.3,shutdown immediate不能正常关闭数据库。
因为要将数据库从非归档模式转为归档模式,所以需要干净的关闭数据库。shutdown abort是不行的。
在shutdown数据库钱已经确定没有大的事务在进行(可以查看v$transaction.USED_UBLK)。
但是shutdown immediate后数据库不能被正常关闭,数据库不停地写redo。
shutdown abort然后再起来后通过logmnr来分析redo文件
PL/SQL procedure successfully completed.
SQL> EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
PL/SQL procedure successfully completed.
查看V$LOGMNR_CONTENTS中的SQL_REDO发现都是delete from col_usage$表的SQL
"NONEQUIJOIN_PREDS" = \'0\' and "RANGE_PREDS" = \'0\' and "LIKE_PREDS" = \'0\' and "NULL_PREDS" = \'1\' and "TIMESTAMP" = TO_DATE(\'14-JUL-0
9\', \'DD-MON-RR\') and ROWID = \'AAAAHZAABAAAE4QACB\';
查看metalink找到一个相关的文章 [ID 332177.1]
Database Shutdown Immediate Takes Forever, Can Only Do Shutdown Abort [ID 332177.1]
Cause
If the number of entries in sys.col_usage$ is large then you are very probably hitting the issue raised in
Bug: 3540022 9.2.0.4.0 RDBMS Base Bug 3221945
Abstract: CLEAN-UP OF ENTRIES IN COL_USAGE$
Base Bug 3221945 9.2.0.3 RDBMS
Abstract: ORA-1631 ON COL_USAGE$
Closed as “Not a Bug”
However, when a table is dropped, the column usage statistics are not dropped. They are left as they are.
When the database is shutdown (in normal mode), then these “orphaned” column usage entries are deleted. The code
which does this gets called only during normal shutdown.
Unless and until the database is shutdown, the col_usage$ table will continue to grow.
Solution
To implement the workaround, please execute the following steps:
1. Periodically (eg once a day) run exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO will clean out redundant col_usage$ entries, and when
you come to shutdown the database you should not have a huge number of entries left to clean up.
查看col_usage$表发现有700万条记录是”orphaned”记录,在DBA_OBJECTS中没有相应的object_id。所以在数据库shutdown immediate时,SMON会逐条的清理这些记录。
在10.2.0.3版本下,还有一个相关的bug:Bug 6147372 COL_USAGE$ may keep growing in size。bug的描述中没有提及具体的原因,但是提到该bug在10.2.0.4中已经被解决。
解决该问题的方法就是通过执行DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO来删除COL_USAGE$中这些”orphaned”的记录。
DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO是在一个大的事务中删除所有记录,所以如果中间被取消需要回滚所有数据。另外执行过程中会阻塞统计信息的收集。
我们可以通过statistics “table scan rows gotten”来监控删除的速度,以及预期所需执行的时间
where a.statistic#=b.statistic# and b.sid=6493 and a.name like \'table scan rows gotten\';
最后经过一小时40分钟将700万条记录删除。删除后shutdown immediate可以正常关闭数据库。
建议继续学习:
扫一扫订阅我的微信号:IT技术博客大学习
- 作者:Eagle Fan 来源: eagle's home
- 标签: redo shutdown 关闭
- 发布时间:2010-11-10 02:18:34
- [66] Oracle MTS模式下 进程地址与会话信
- [66] Go Reflect 性能
- [65] 如何拿下简短的域名
- [59] android 开发入门
- [59] 图书馆的世界纪录
- [59] IOS安全–浅谈关于IOS加固的几种方法
- [58] 【社会化设计】自我(self)部分――欢迎区
- [53] 视觉调整-设计师 vs. 逻辑
- [47] 界面设计速成
- [46] 读书笔记-壹百度:百度十年千倍的29条法则