今天在重启一个库的时候，由于等了超过半小时，仍然没有完成数据库的close，于是就用shutdown abort命令关闭数据库。但是在起来的时候，发现在alertlog中有大量的SMON的报错，而且还在持续不断的报错出来。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 |
<--- 正常的启动信息 begin here ---> Mon Sep 1 16:32:02 2008 Starting ORACLE instance (normal) LICENSE_MAX_SESSION = 0 LICENSE_SESSIONS_WARNING = 0 SCN scheme 1 Using log_archive_dest parameter default value LICENSE_MAX_USERS = 0 SYS auditing is disabled Starting up ORACLE RDBMS Version: 9.2.0.6.0. System parameters with non-default values: processes = 1200 timed_statistics = TRUE shared_pool_size = 419430400 sga_max_size = 2108652208 large_pool_size = 117440512 java_pool_size = 117440512 spfile = /dev/vg_ora01/rspfile_128m_01 control_files = /dev/vg_ora01/rctrl_128m_01, /dev/vg_ora02/rctrl_128m_02 db_block_size = 8192 db_cache_size = 1258291200 compatible = 9.2.0.0.0 log_archive_start = TRUE log_archive_dest_1 = location=/arch log_archive_format = arch_%t_%s.arc log_buffer = 10485760 db_files = 800 db_file_multiblock_read_count= 16 fast_start_mttr_target = 300 undo_management = AUTO undo_tablespace = UNDOTBS1 undo_suppress_errors = TRUE undo_retention = 10800 remote_login_passwordfile= EXCLUSIVE db_domain = instance_name = gdmocs job_queue_processes = 6 hash_join_enabled = TRUE background_dump_dest = /oracle/app/oracle/admin/gdmocs/bdump user_dump_dest = /oracle/app/oracle/admin/gdmocs/udump core_dump_dest = /oracle/app/oracle/admin/gdmocs/cdump sort_area_size = 524288 db_name = gdmocs open_cursors = 500 star_transformation_enabled= FALSE query_rewrite_enabled = TRUE pga_aggregate_target = 524288000 PMON started with pid=2 DBW0 started with pid=3 LGWR started with pid=4 CKPT started with pid=5 SMON started with pid=6 RECO started with pid=7 CJQ0 started with pid=8 Mon Sep 1 16:32:03 2008 ARCH: STARTING ARCH PROCESSES ARC0 started with pid=9 ARC0: Archival started ARC1 started with pid=10 ARC1: Archival started Mon Sep 1 16:32:03 2008 ARCH: STARTING ARCH PROCESSES COMPLETE Mon Sep 1 16:32:03 2008 ARC1: Thread not mounted Mon Sep 1 16:32:03 2008 ARC0: Thread not mounted Mon Sep 1 16:32:03 2008 ALTER DATABASE MOUNT Mon Sep 1 16:32:07 2008 Successful mount of redo thread 1, with mount id 2193310019 Mon Sep 1 16:32:07 2008 Database mounted in Exclusive Mode. Completed: ALTER DATABASE MOUNT Mon Sep 1 16:32:07 2008 <--- 正常的启动信息 end here ---> <--- 开始打开数据，发现需要做实例恢复 begin here ---> ALTER DATABASE OPEN Mon Sep 1 16:32:08 2008 Beginning crash recovery of 1 threads Mon Sep 1 16:32:08 2008 Started redo scan Mon Sep 1 16:32:09 2008 Completed redo scan 27274 redo blocks read, 46702 data blocks need recovery Mon Sep 1 16:35:20 2008 <--- 开始打开数据，发现需要做实例恢复 end ---> <--- 开始实例恢复 ---> Started recovery at Thread 1: logseq 24462, block 231548, scn 0.0 Mon Sep 1 16:35:20 2008 Recovery of Online Redo Log: Thread 1 Group 1 Seq 24462 Reading mem 0 Mem# 0 errs 0: /dev/vg_ora01/rredo_256m_01 Mem# 1 errs 0: /dev/vg_ora02/rredo_256m_11 Mon Sep 1 16:35:22 2008 Completed redo application Mon Sep 1 16:35:32 2008 Ended recovery at Thread 1: logseq 24462, block 258822, scn 13.3684259989 46702 data blocks read, 46201 data blocks written, 27274 redo blocks read Crash recovery completed successfully Mon Sep 1 16:35:33 2008 LGWR: Primary database is in CLUSTER CONSISTENT mode Thread 1 advanced to log sequence 24463 Thread 1 opened at log sequence 24463 Current log# 3 seq# 24463 mem# 0: /dev/vg_ora01/rredo_256m_03 Current log# 3 seq# 24463 mem# 1: /dev/vg_ora02/rredo_256m_13 Successful open of redo thread 1 Mon Sep 1 16:35:33 2008 <--- 开始前滚 ---> SMON: enabling cache recovery Mon Sep 1 16:35:33 2008 ARC0: Evaluating archive log 1 thread 1 sequence 24462 ARC0: Beginning to archive log 1 thread 1 sequence 24462 Creating archive destination LOG_ARCHIVE_DEST_1: '/arch/arch_1_24462.arc' Mon Sep 1 16:35:34 2008 Successfully onlined Undo Tablespace 1. Mon Sep 1 16:35:34 2008 <--- 开始回滚 ---> SMON: enabling tx recovery Mon Sep 1 16:35:34 2008 Database Characterset is ZHS16GBK Mon Sep 1 16:35:34 2008 <--- 开始出现大量的SMON报错 ---> SMON: about to recover undo segment 60 SMON: mark undo segment 60 as available SMON: about to recover undo segment 60 SMON: mark undo segment 60 as available SMON: about to recover undo segment 60 SMON: mark undo segment 60 as available SMON: about to recover undo segment 60 SMON: mark undo segment 60 as available SMON: about to recover undo segment 60 SMON: mark undo segment 60 as available Mon Sep 1 16:35:35 2008 replication_dependency_tracking turned off (no async multimaster replication found) Mon Sep 1 16:35:35 2008 SMON: about to recover undo segment 60 SMON: mark undo segment 60 as available SMON: about to recover undo segment 60 SMON: mark undo segment 60 as available SMON: about to recover undo segment 60 SMON: mark undo segment 60 as available <--- 实例恢复完成，数据库open ---> Mon Sep 1 16:35:35 2008 Completed: ALTER DATABASE OPEN Mon Sep 1 16:35:35 2008 SMON: about to recover undo segment 60 SMON: mark undo segment 60 as available SMON: about to recover undo segment 60 SMON: mark undo segment 60 as available SMON: about to recover undo segment 60 SMON: mark undo segment 60 as available SMON: about to recover undo segment 60 SMON: mark undo segment 60 as available SMON: about to recover undo segment 60 SMON: mark undo segment 60 as available SMON: about to recover undo segment 60 SMON: mark undo segment 60 as available SMON: about to recover undo segment 60 SMON: mark undo segment 60 as available SMON: about to recover undo segment 60 SMON: mark undo segment 60 as available SMON: about to recover undo segment 60 SMON: mark undo segment 60 as available SMON: about to recover undo segment 60 SMON: mark undo segment 60 as available SMON: about to recover undo segment 60 SMON: mark undo segment 60 as available SMON: about to recover undo segment 60 SMON: mark undo segment 60 as available SMON: about to recover undo segment 60 SMON: mark undo segment 60 as available SMON: about to recover undo segment 60 SMON: mark undo segment 60 as available SMON: about to recover undo segment 60 SMON: mark undo segment 60 as available |
此时，数据库已经open，但是在alertlog中有大量的这样的报错。查询metalink（Note:266159.1）：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
Cause These errors do not indicate rollback segment corruption. Oracle 8i: These messages indicate that there is a problem with the "rollback_segments" parameter in the init.ora. Oracle 9i: Automatic Undo management is being used. When the instance is shutdown, during the next startup instance recovery needs to take place. In AUM we do not have any control over which undo segments will brought online after the instance startup. In case we require any of the offline undo segments for the instance recovery, these messages will appear in alert log. This is not a bug, this is the intended behavior. When SMON finds such offline undo segments with transactions needing recovery ,then it does what is intended to do , ie: perform the transaction recovery in batches of 100 undo records. |
看来并不是undo segment损坏块的问题。用metalink上的方法处理，告警不再出现。
目前数据库已经open，但是还是不敢用当前的undo了，新建unodtbs02到系统默认的undo。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
Solution Oracle 8i: Check that the rollback segment is included in the "rollback_segments" parameter then adding the rollback segment to the parameter. If not, adding the rollback segment and restarting the database will clear up the problem. Oracle 9i: Solution 1: --------------- To stop this messages from appearing you can do the following workaround : sql> alter session set "_smu_debug_mode"=4; sql> alter rollback segment "_SYSSMU11$" online; Where 11 is the number that is appearing in the messages in the alert log. Solution 2: --------------- This is fixed in 10g. With the new feature "Fast Ramp-Up" AUM enhancement. |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SQL> select SEGMENT_NAME,STATUS from dba_rollback_segs where SEGMENT_ID=60; SEGMENT_NAME STATUS ------------------------------ ---------------- _SYSSMU60$ PARTLY AVAILABLE SQL> alter session set "_smu_debug_mode"=4; Session altered. SQL> alter rollback segment "_SYSSMU60$" online; Rollback segment altered. |
alterlog中不再报错：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
$>tail -f alert_gdmocs.log SMON: about to recover undo segment 60 SMON: mark undo segment 60 as available SMON: about to recover undo segment 60 SMON: mark undo segment 60 as available SMON: about to recover undo segment 60 SMON: mark undo segment 60 as available Mon Sep 1 16:48:39 2008 alter rollback segment "_SYSSMU60$" online Mon Sep 1 16:48:39 2008 Completed: alter rollback segment "_SYSSMU60$" online Mon Sep 1 16:52:33 2008 Thread 1 advanced to log sequence 24466 Current log# 1 seq# 24466 mem# 0: /dev/vg_ora01/rredo_256m_01 Current log# 1 seq# 24466 mem# 1: /dev/vg_ora02/rredo_256m_11 Mon Sep 1 16:52:33 2008 ARC0: Evaluating archive log 4 thread 1 sequence 24465 ARC0: Beginning to archive log 4 thread 1 sequence 24465 Creating archive destination LOG_ARCHIVE_DEST_1: '/arch/arch_1_24465.arc' |
（新建undotbs02到系统默认undo过程略）
检查undo segment的状况：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
SQL> select TABLESPACE_NAME,SEGMENT_NAME,status from dba_rollback_segs; TABLESPACE_NAME SEGMENT_NAME STATUS ------------------------------ ------------------------------ ---------------- SYSTEM SYSTEM ONLINE UNDOTBS1 _SYSSMU1$ ONLINE UNDOTBS1 _SYSSMU2$ OFFLINE UNDOTBS1 _SYSSMU3$ OFFLINE …… UNDOTBS1 _SYSSMU11$ OFFLINE UNDOTBS1 _SYSSMU12$ ONLINE UNDOTBS1 _SYSSMU13$ ONLINE UNDOTBS1 _SYSSMU14$ OFFLINE …… UNDOTBS1 _SYSSMU59$ OFFLINE UNDOTBS1 _SYSSMU60$ ONLINE UNDOTBS1 _SYSSMU61$ OFFLINE …… UNDOTBS2 _SYSSMU858$ ONLINE UNDOTBS2 _SYSSMU859$ ONLINE UNDOTBS2 _SYSSMU860$ ONLINE UNDOTBS2 _SYSSMU861$ ONLINE UNDOTBS2 _SYSSMU862$ ONLINE UNDOTBS2 _SYSSMU863$ ONLINE UNDOTBS2 _SYSSMU864$ ONLINE UNDOTBS2 _SYSSMU865$ ONLINE UNDOTBS2 _SYSSMU866$ ONLINE UNDOTBS2 _SYSSMU867$ ONLINE 868 rows selected. |
一条评论
可能之前有个大的DML在rollback.
还好不是undo或者redo什么的corrupt….