goneaway 2008年08月02日
100分在线求更新全文索引中oracle老挂掉的问题。
这个数据库是oracle8.2.0.4上的一个数据库。数据总量比较大。实时入库量也比较大。前段时间t_wap_content表(一个有200多G含blob字段的分区表)中blob字段的全文索引损坏了(原因之前夜有提问,但还是没有找到很确切的原因,见 http://topic.csdn.net/u/20080722/11/56d1241b-e2e9-4eb5-86d4-c3fc66b9b256.html?1507684147 ) 。由于历史和实时数据本身都很重要,只能在不停止数据导入的情况下,在线重建索引。
先 drop index idx_p_WAP_content FORCE; 成功后,在重新创建索引。

CREATE INDEX idx_p_WAP_content on T_WAP_content(DATA) INDEXTYPE IS CTXSYS.CONTEXT
parameters('lexer Miss_VGRAM_LEXER FILTER CTXSYS.INSO_FILTER memory 50m STORAGE MISS.MISSSTORE') ;

结果运行几个小时后,数据库本身就挂了,报 ORA-27101: shared memory realm does not exist。等错误。
重新startup数据库能恢复。
尝试了增加redo log buf,增大了5倍,添加 redo log 的数量,由原来的100M的3个,增加到500M的四个。
把索引创建一次占用的内存也有500M改小到50M。试了好几次,问题依旧。
在网上也查了相关的错误一般都是启动过程中报ORA-27101的错误,像我这样不运行新建索引,就一切正常,一运行创建这个全文索引,几个小时后就做不下去死掉的情况好像没有看到。
很是郁闷,想请大虾们帮帮忙。。。。。。。。。。。。。。。。。。。郁闷中的人等待你的拯救!
下面是oracle日志的摘要:
==========================
alert_missdb.log
==========================
Fri Aug 1 10:27:39 2008
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
SCN scheme 2
Using log_archive_dest parameter default value
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up ORACLE RDBMS Version: 9.2.0.8.0.
System parameters with non-default values:
processes = 150
timed_statistics = TRUE
shared_pool_size = 83886080
large_pool_size = 16777216
java_pool_size = 83886080
control_files = /RaidDisk/oracle/oradata/missdb/control01.ctl, /RaidDisk/oracle/oradata/missdb/control02.ctl, /RaidDisk/oracle/oradata/missdb/control03.ctl
db_block_size = 16384
db_cache_size = 33554432
compatible = 9.2.0.0.0
log_buffer = 5242880
db_file_multiblock_read_count= 16
fast_start_mttr_target = 25
undo_management = AUTO
undo_tablespace = UNDOTBS1
undo_retention = 600
remote_login_passwordfile= EXCLUSIVE
db_domain =
instance_name = missdb
dispatchers = (PROTOCOL=TCP) (SERVICE=missdbXDB)
job_queue_processes = 10
hash_join_enabled = TRUE
background_dump_dest = /RaidDisk/oracle/admin/missdb/bdump
user_dump_dest = /RaidDisk/oracle/admin/missdb/udump
core_dump_dest = /RaidDisk/oracle/admin/missdb/cdump
sort_area_size = 524288
db_name = missdb
open_cursors = 300
star_transformation_enabled= FALSE
query_rewrite_enabled = FALSE
pga_aggregate_target = 25165824
aq_tm_processes = 1
PMON started with pid=2, OS id=30149
DBW0 started with pid=3, OS id=30151
LGWR started with pid=4, OS id=30153
CKPT started with pid=5, OS id=30155
SMON started with pid=6, OS id=30157
RECO started with pid=7, OS id=30159
CJQ0 started with pid=8, OS id=30161
QMN0 started with pid=9, OS id=30163
Fri Aug 1 10:27:40 2008
starting up 1 shared server(s) ...
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
Fri Aug 1 10:27:40 2008
ALTER DATABASE MOUNT
Fri Aug 1 10:27:44 2008
Successful mount of redo thread 1, with mount id 2975754524
Fri Aug 1 10:27:44 2008
Database mounted in Exclusive Mode.
Completed: ALTER DATABASE MOUNT
Fri Aug 1 10:27:44 2008
ALTER DATABASE OPEN
Fri Aug 1 10:27:44 2008
Beginning crash recovery of 1 threads
Fri Aug 1 10:27:44 2008
Started redo scan
Fri Aug 1 10:27:45 2008
Completed redo scan
289491 redo blocks read, 1001 data blocks need recovery
Fri Aug 1 10:27:46 2008
Started recovery at
Thread 1: logseq 13481, block 759083, scn 0.0
Fri Aug 1 10:27:46 2008
Recovery of Online Redo Log: Thread 1 Group 5 Seq 13481 Reading mem 0
Mem# 0 errs 0: /RaidDisk/oracle/oradata/missdb/log5.ora
Fri Aug 1 10:27:47 2008
Completed redo application
Fri Aug 1 10:27:47 2008
Ended recovery at
Thread 1: logseq 13481, block 1048574, scn 0.173225765
1001 data blocks read, 1001 data blocks written, 289491 redo blocks read
Crash recovery completed successfully
Fri Aug 1 10:27:47 2008
Thread 1 advanced to log sequence 13482
Thread 1 opened at log sequence 13482
Current log# 2 seq# 13482 mem# 0: /RaidDisk/oracle/oradata/missdb/log2.ora
Successful open of redo thread 1
Fri Aug 1 10:27:47 2008
SMON: enabling cache recovery
Fri Aug 1 10:27:47 2008
Successfully onlined Undo Tablespace 1.
Fri Aug 1 10:27:47 2008
SMON: enabling tx recovery
Fri Aug 1 10:27:47 2008
Database Characterset is ZHS16CGB231280
replication_dependency_tracking turned off (no async multimaster replication found)
Completed: ALTER DATABASE OPEN
Fri Aug 1 16:12:49 2008
Thread 1 advanced to log sequence 13483
Current log# 3 seq# 13483 mem# 0: /RaidDisk/oracle/oradata/missdb/log6.ora
Fri Aug 1 16:26:22 2008
/* OracleOEM */ ALTER TABLESPACE "MISS"NOLOGGING
Fri Aug 1 16:26:22 2008
Completed: /* OracleOEM */ ALTER TABLESPACE "MISS"NOLOGGING
Fri Aug 1 21:37:32 2008
Thread 1 advanced to log sequence 13484
Current log# 4 seq# 13484 mem# 0: /RaidDisk/oracle/oradata/missdb/log4.ora
Fri Aug 1 23:40:16 2008
RECO: terminating instance due to error 313
Instance terminated by RECO, pid = 30159

==========================
missdb_ora_30167.trc
==========================
Du/RaidDisk/oracle/admin/missdb/bdump/missdb_ora_30167.trc
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
ORACLE_HOME = /RaidDisk/oracle/product/9.2
System name: Linux
Node name: vastdb
Release: 2.6.9-55.ELsmp
Version: #1 SMP Fri Apr 20 17:03:35 EDT 2007
Machine: i686
Instance name: missdb
Redo thread mounted by this instance: 0 <none>
Oracle process number: 0
30167

*** 2008-08-01 23:40:33.939
OPIRIP: Uncaught error 313. Error stack:
ORA-00313: open failed for members of log group of thread

我是在外地现场解决这个问题,已经拖了好几天了,没能搞定,很是痛苦。。。。。。。。希望高手能给指条明路。。。



...全文
310 点赞 收藏 33
写回复
33 条回复

还没有回复,快来抢沙发~

发动态
发帖子
Oracle 高级技术
创建于2007-09-28

2927

社区成员

1.8w+

社区内容

Oracle 高级技术相关讨论专区
社区公告
暂无公告