谨慎的使用shutdown abort

· 作者 小荷||【转载时请务必以超链接形式标明文章原始出处和作者信息】
· 永久链接: http://www.oracleblog.cn/working-case/not-use-shutdownabort/

今天在重启一个库的时候，由于等了超过半小时，仍然没有完成数据库的close，于是就用shutdown abort命令关闭数据库。但是在起来的时候，发现在alertlog中有大量的SMON的报错，而且还在持续不断的报错出来。

<--- 正常的启动信息 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）：

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。

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.
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中不再报错：

$>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的状况：

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.

· 【文章发布信息】发表于： 2008-09-03 @ 13:05:41 · ||分类： ..experience, Working case

1 条评论 »

  1. 玉面飞龙 于 2008-09-03 @ 22:06:15 留言 ：

    可能之前有个大的DML在rollback.

    还好不是undo或者redo什么的corrupt….

RSS 为此帖反馈评论 · 反向跟踪 网站

留条评论