分享
 
 
 

[错误]分析ora-03113错误!

王朝other·作者佚名  2008-05-31
窄屏简体版  字體: |||超大  

我们在使用Oracle数据库时经常碰到的一个错误,看看下面的分析和解决办法,也许对你很有帮助!

Causes of ORA-3113's

ORA-3113 "end-of-file on communication channel" is a catch-all error message which simply means that the foreground oracle client process (svrmgrl, sqlplus, RMAN, Oracle Forms etc.) has lost contact with its background shadow process.

Unfortunately there are many causes of this. Network glitches were clearly the cause that the Oracle developers had in mind when they phrased the error message, but anything which causes the shadow process to crash will cause this error to be reported.

This message may also be accompanied by other messages, either displayed on the screen or in log files. If the background process which has crashed is one of the essential Oracle servers processes (PMON, SMON, DBWR etc.) you may also get an ORA-1034 ORACLE not available error message. Looking in the alert log or looking through any trace files in your background_dump_dest may help diagnose the problem.

Here are some of the causes of ORA-3113 I've accumulated so far:

In the situations below I have indicated the platform and Oracle version number where I eXPerienced the problem. But it is likely that some of the causes may occur on other platforms and other versions of Oracle as well.

Invalid value for processes parameter in init.ora when starting database

We had a value for processes of 150, which was

higher than that permitted by the values of kernel parameters SEMMSL and SEMMNS

This gave the errors:

ORA-24323: value not allowed

ORA-03113: end-of-file on communication channel

ORA-01034: ORACLE not available

ORA-27101: shared memory realm does not exist

The solution is either to redUCe the value of the init.ora parameter processes or (preferably) increase the values of these kernel parameters (and reboot your server).

(See the Oracle8i Installation Guide Release 8.1.7 for Sun SPARC Solaris manual for information on setting the Solaris shared memory and semaphore kernel parameters correctly.

In particular, SEMMSL must be set to at least 10 + the largest value of processes of any database on the server. SEMMNS is also dependent on the values of the processes parameter for each of your databases.

For other platforms, the procedure for changing kernel parameters may vary so consult the platform-specific documentaion for your plaform on Oracle Technet or on the installation CD.)

Interestingly, this problem occurred during a default installation of Oracle 8.1.7, so it was the installer software that had generated this value for processes, resulting in the installer failing to create an initial database. Note that Oracle (rather unhelpfully) doesn't tell you which parameter has an invalid value, or why it is invalid. Presumably there are other init.ora parameters which will cause similar symptoms if incorrectly set.

(Oracle 8.1.7.0.0 on Solaris)

setuid

bit needs setting on the oracle executable

(Unix only). The symptom for this (at least, in my case) was that SQL*Plus on the server gave an ORA-3113 error when started by any userid other than oracle. The database remained up.

The oracle executable in the $ORACLE_HOME/bin Directory should have the permissions:

-rwsr-sr-x 1 oracle dba 34049236 Oct 11 17:17 oracle

The important features are the ownership of the executable (by oracle) and the setting of the setuid bit (the first s in the permissions string.) This allows the shadow processes to execute as if they were being executed by the oracle userid and therefore allows them to read and write to the database files, which are normally protected with write Access granted to oracle only, and sometimes with read access restricted as well. The setting of the setgid bit (the second s ) is not important.

If the permissions are wrong, they can be corrected with the command:

chmod 7755 $ORACLE_HOME/bin/oracle

Relinking the oracle executable (see below) will also set the permissions correctly. In fact, it is probable that in many cases changing the permissions was all that was required, rather than a full relink.

A possible cause of this problem is using tar or cp or some other unix file-copying utility to move or clone the Oracle installation. These tools will not preserve the setuid permissions on executables unless run as root. (Thanks to Paul

Ingram for his insights into this issue.)

(Oracle 8.1.7.0.0 on Solaris)

Oracle executables need relinking

There are three ways of doing this:

There's an option on the Oracle Installer to relink the executables.

I didn't use this method as I didn't have the installation CDROM to hand.

Enter the following Unix commands:

cd $ORACLE_HOME/rdbms/lib

make -f ins_rdbms.mk install

This relinked about a dozen executables, including $ORACLE_HOME/bin/oracle, and solved the problem. (Interestingly, the sqlplus executable was not relinked.)

Enter the following Unix commands:

cd $ORACLE_HOME/bin

./relink all

Alternatively, given that it would seem necessary to only relink the oracle executable, ./relink oracle should be sufficient.

./relink without any parameters gives a list of possible targets for relinking.

Before embarking on a relinking exercise, it may first be worth checking whether the real cause of the problem is simply incorrect file permissions (see above).

(Oracle 8.1.7.0.0 on Solaris)

shared_pool_size too small.

We had it set to 100Mb. Increasing it to 200Mb solved the problem. (Yes, I know 200Mb is probably still too small for good performance.)

(Oracle 8.1.6.0.0 on Solaris)

timed_statistics=true.

Oracle Support could not provide a solution to this one: they just advised me to run my database with timed_statistics=false. Doing this did indeed

cure a lot of ORA-3113's I was getting when querying data-dictionary tables.

(Oracle 8.1.6.0.0 on Solaris)

An insert into a table which grew into an additional extent allocated in a datafile which was over 2Gb in size.

This caused the database to crash. I can't remember whether it was the action of growing the datafile which generated the ORA-3113, or whether it was the action of extending the table into the area above the 2Gb limit which caused it.

We then got ORA-3113's when trying to restart the database. I think what happened was that each time we brought it up it tried to re-apply the redo logs to bring the database into a consistent state before opening it. But this caused an insert above the 2Gb limit again and another ORA-3113 crash. I can't remember how I got round the problem. Probably "recover until cancel".

(Oracle 8.1.6.0.0 on Solaris)

Create table as select .... where there was an invalid procedure or function called from a trigger defined on the table we were selecting from.

I can't specify this in any more detail: we used the $ORACLE_HOME/rdbms/admin/utlrp.sql script to recompile all invalid packages, procedures and functions. This solved the problem, but I don't know which of the procedures or functions was the original cause of the problem.

(Oracle 8.1.6.0.0 on Solaris)

Make use of Oracle Support / Metalink

If you have a look at Metalink you'll find quite a lot of problems related to ORA-3113, with lots of diverse causes. You will probably need Oracle Support's help to solve most ORA-3113's

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
2023年上半年GDP全球前十五强
 百态   2023-10-24
美众议院议长启动对拜登的弹劾调查
 百态   2023-09-13
上海、济南、武汉等多地出现不明坠落物
 探索   2023-09-06
印度或要将国名改为“巴拉特”
 百态   2023-09-06
男子为女友送行,买票不登机被捕
 百态   2023-08-20
手机地震预警功能怎么开?
 干货   2023-08-06
女子4年卖2套房花700多万做美容:不但没变美脸,面部还出现变形
 百态   2023-08-04
住户一楼被水淹 还冲来8头猪
 百态   2023-07-31
女子体内爬出大量瓜子状活虫
 百态   2023-07-25
地球连续35年收到神秘规律性信号,网友:不要回答!
 探索   2023-07-21
全球镓价格本周大涨27%
 探索   2023-07-09
钱都流向了那些不缺钱的人,苦都留给了能吃苦的人
 探索   2023-07-02
倩女手游刀客魅者强控制(强混乱强眩晕强睡眠)和对应控制抗性的关系
 百态   2020-08-20
美国5月9日最新疫情:美国确诊人数突破131万
 百态   2020-05-09
荷兰政府宣布将集体辞职
 干货   2020-04-30
倩女幽魂手游师徒任务情义春秋猜成语答案逍遥观:鹏程万里
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案神机营:射石饮羽
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案昆仑山:拔刀相助
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案天工阁:鬼斧神工
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案丝路古道:单枪匹马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:与虎谋皮
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:李代桃僵
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:指鹿为马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:小鸟依人
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:千金买邻
 干货   2019-11-12
 
推荐阅读
 
 
 
>>返回首頁<<
 
靜靜地坐在廢墟上,四周的荒凉一望無際,忽然覺得,淒涼也很美
© 2005- 王朝網路 版權所有