这个数据库是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 我是在外地现场解决这个问题,已经拖了好几天了,没能搞定,很是痛苦。。希望高手能给指条明路
先 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 我是在外地现场解决这个问题,已经拖了好几天了,没能搞定,很是痛苦。。希望高手能给指条明路
1.说数据库是8204,没有听说有这个版本哦.
2.alert日志是oracle9208的.
你的表有200g大小,而数据库配置的db_cache才32M,shared_pool才80M,pga 25M,如果不出问题倒是不正常,出问题就正常了.哈哈.先修改db_cache_size/shared_pool_size/pga_aggregate_target 之后,再试试吧.
sga_max_size=5g
db_cache_size=4g
shared_pool_size=500m
pga_aggregate_target=1g
向大家多多学习,特别是想oracledbalgtu 多学习。我想先按oracledbalgtu的改,然后
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') ;
中用memory 50m 还是用memory 500m,
改了后还会不会挂机呢,很担心。
200分给出来,大家还请多赐教。
你可以先看看MAX_INDEX_MEMORY 是多大,再设置memory。
暂时把
db_cache=320M,shared_pool=800M,pga 100M
不知道行不行,现在用top查memory使用了一半。等会不知道会涨到多少。
参考如下:
kernel.sem = 250 32000 100 128
kernel.shmmax = 2147483648
kernel.shmall = 2097152你们这系统没啥说的了,唉。。
kernel.shmmax = 2147483648
kernel.shmall = 2097152
自然就不能使用上面给出的参数了。
根据你们的实际内存剩于情况改吧。
/RaidDisk/oracle/admin/missdb/bdump/missdb_s000_15164.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: 1
Oracle process number: 10
Unix process pid: 15164, image: oracle@vastdb (S000)*** 2008-08-06 04:11:40.067
OPIRIP: Uncaught error 313. Error stack:
ORA-00313: open failed for members of log group of thread
ORA-00313: open failed for members of log group of thread
如果没有限制,则使用上述oracledbalgtu 的配置没有问题。不能起来我怀疑有这个限制的。
2、db_cache=320M,shared_pool=800M,pga 100M
这样配置不太合理的。如果是SGA 存在1.7G限制,可以参考:
db_cache=1000M,shared_pool=280M,pga 400M
如果没有限制,则使用上述oracledbalgtu 的配置没有问题。不能起来我怀疑有这个限制的。
2、db_cache=320M,shared_pool=800M,pga 100M
这样配置不太合理的。如果是SGA 存在1.7G限制,可以参考:
db_cache=1000M,shared_pool=280M,pga 400M
看着帖子挺热闹。
8g的内存,分配给oracle的近1%。
150个连接数,却有200多g的数据。
难道是持续运行了n年的老服务器?
老服务器却又能扩充如此大的内存,如此大的硬盘,比较奇怪。建议LZ把你机器的操作系统,硬件配置都说出来
connect /as sysdba;
lsnrctl stop;
--修改参数并重起
sqlplus /nolog;
sql> connect /as sysdba;
sql> create pfile='/tmp/pfile_0819.ora' from spfile;
sql> alter system set cursor_sharing = similar scope=spfile;
sql> alter system set shared_pool_size= 300m scope=spfile;
sql> alter system set shared_pool_reserved_size= 30m scope=spfile;
sql> alter system set java_pool_size=16m scope=spfile;
sql> alter system set large_pool_size=64m scope=spfile;
sql> alter system set pga_aggregate_target=150m scope=spfile;
sql> alter system set sga_max_size= 1400m scope=spfile;
sql> shutdown immediate;
--启动数据库
sql> startup;
--启动侦听
lsnrctl start;
另外,内存8G对2T的数据是否够用。如果要配置6T的数据库内存配比推荐又是多少?
cpu现在是单个四核的E5405不知道够用否,需要多cpu吗,是否需要使用7系列的cpu
NAME BYTES RES
-------------------------------- ---------- ---
Fixed SGA Size 2026064 No
Redo Buffers 14749696 No
Buffer Cache Size 3925868544 Yes
Shared Pool Size 218103808 Yes
Large Pool Size 16777216 Yes
Java Pool Size 16777216 Yes
Streams Pool Size 0 Yes
Granule Size 16777216 No
Maximum SGA Size 4194304000 No
Startup overhead in Shared Pool 83886080 No
Free SGA Memory Available 0
但检索速度依然很慢.各位大佬看看是否有不合理的地方.