技术头条 - 一个快速在微博传播文章的方式     搜索本站
您现在的位置：首页 --> Oracle --> 数据库不能正常关闭的问题

数据库不能正常关闭的问题

浏览:1653次  出处信息

    数据库版本为10.2.0.3，shutdown immediate不能正常关闭数据库。

    因为要将数据库从非归档模式转为归档模式，所以需要干净的关闭数据库。shutdown abort是不行的。

    在shutdown数据库钱已经确定没有大的事务在进行（可以查看v$transaction.USED_UBLK)。

    但是shutdown immediate后数据库不能被正常关闭，数据库不停地写redo。

    shutdown abort然后再起来后通过logmnr来分析redo文件

SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LOGFILENAME => \'/oracle/logs/log1.dbf\',OPTIONS => DBMS_LOGMNR.NEW);

    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

delete from "SYS"."COL_USAGE$" where "OBJ#" = \'376767\' and "INTCOL#" = \'1\' and "EQUALITY_PREDS" = \'0\' and "EQUIJOIN_PREDS" = \'0\' and

    "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”来监控删除的速度，以及预期所需执行的时间

select a.name,b.value from v$sesstat b,v$statname a

    where a.statistic#=b.statistic# and b.sid=6493 and a.name like \'table scan rows gotten\';

    最后经过一小时40分钟将700万条记录删除。删除后shutdown immediate可以正常关闭数据库。

建议继续学习：

  1. 如何在关闭某个内核模块    (阅读：2281)
  2. 为什么 script 标签不能写成自关闭形式    (阅读：2240)
QQ技术交流群：445447336，欢迎加入！
扫一扫订阅我的微信号：IT技术博客大学习
<< 前一篇：Latch free竞争 - 最近的SAP测试项目小记
后一篇：Oracle数据库恢复:存储故障导致的数据损坏 >>
文章信息
© 2009 - 2024 by blogread.cn 微博：@IT技术博客大学习

京ICP备15002552号-1