2 zhaohong1205 zhaohong1205 于 2014.10.29 13:57 提问

oracle 11g ORA-00445:每个月数据库都会死,pl/sql登录不上

每个月数据库都会无缘无故的死掉,重启数据库又正常了,下面是alert_orcl.log里面的日志,window服务器内存8G ,oracle版本11gR2,这个是什么原因导致的呢
Tue Oct 28 20:18:25 2014
Errors in file d:\oracle\diag\rdbms\orcl\orcl\trace\orcl_cjq0_2628.trc (incident=65106):
ORA-00445: background process "J000" did not start after 120 seconds
Incident details in: d:\oracle\diag\rdbms\orcl\orcl\incident\incdir_65106\orcl_cjq0_2628_i65106.trc
kkjcre1p: unable to spawn jobq slave process
Errors in file d:\oracle\diag\rdbms\orcl\orcl\trace\orcl_cjq0_2628.trc:
Tue Oct 28 20:18:28 2014
Trace dumping is performing id=[cdmp_20141028201828]
Tue Oct 28 20:20:16 2014
Thread 1 cannot allocate new log, sequence 6830
Private strand flush not complete
Current log# 1 seq# 6829 mem# 0: D:\ORACLE\ORADATA\orcl\REDO01.LOG
Thread 1 advanced to log sequence 6830 (LGWR switch)
Current log# 2 seq# 6830 mem# 0: D:\ORACLE\ORADATA\orcl\REDO02.LOG
下面是重启的参数:
Starting up:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options.
Using parameter settings in server-side spfile D:\ORACLE\PRODUCT\11.2.0\DBHOME_1\DATABASE\SPFILEorcl.ORA
System parameters with non-default values:
processes = 1000
sga_max_size = 3104M
memory_target = 3504M
memory_max_target = 3504M
control_files = "D:\ORACLE\ORADATA\orcl\CONTROL01.CTL"
control_files = "D:\ORACLE\FLASH_RECOVERY_AREA\orcl\CONTROL02.CTL"
db_block_size = 8192
compatible = "11.2.0.0.0"
db_recovery_file_dest = "D:\oracle\flash_recovery_area"
db_recovery_file_dest_size= 3912M
undo_tablespace = "UNDOTBS1"
remote_login_passwordfile= "EXCLUSIVE"
db_domain = ""
dispatchers = "(PROTOCOL=TCP) (SERVICE=orclXDB)"
audit_file_dest = "D:\ORACLE\ADMIN\orcl\ADUMP"
audit_trail = "DB"
db_name = "orcl"
open_cursors = 300
diagnostic_dest = "D:\ORACLE"
Tue Oct 28 20:50:40 2014

2个回答

lzp_lrp
lzp_lrp   Ds   Rxr 2014.12.28 18:35

Anything that can go wrong will go wrong. :“凡事只要有可能出错,那就一定会出错。”

墨菲定律再次被验证,早上数据库宕机,日志如下:

Dump continued from file: /opt/ora11g/diag/rdbms/prodb/ORABJ/trace/ORABJ_cjq0_4189.trc

ORA-00445: background process "J000" did not start after 120 seconds

*** 2013-07-10 09:13:24.852

*** SESSION ID:(618.5) 2013-07-10 09:13:24.852

*** CLIENT ID:() 2013-07-10 09:13:24.852

*** SERVICE NAME:(SYS$BACKGROUND) 2013-07-10 09:13:24.852

*** MODULE NAME:() 2013-07-10 09:13:24.852

*** ACTION NAME:() 2013-07-10 09:13:24.852

Dump continued from file: /opt/ora11g/diag/rdbms/prodb/ORABJ/trace/ORABJ_cjq0_4189.trc

ORA-00445: background process "J000" did not start after 120 seconds

【ID 1379200.1】中对这个错误的描述:

What does this message mean ?

The message indicates that we failed to spawn a new process at the Operating System level to serve the request. There are various causes for this issue. This typically occurs when there is a shortage or misconfiguration in Operating System Resources, and thereby the problem should be investigated from an OS perspective. However there are a few causes related to the Oracle Database as well.

The default 120 seconds (after which Oracle times out) can be extended dynamically (without a database restart) by setting the following event:

$ sqlplus / as sysdba

alter system set events '10281 trace name context forever, level xxx';

-- where xxxxxx is the number of seconds to timeout at.

eg: alter system set events '10281 trace name context forever, level 300';

的确我们的硬件是有些问题,每隔半年就要重启一次,否则就会操作系统就会hang住。前几

次到半年人工重启了机器,没造成事故。这次过了半年,由于停机申请还在走审批流程,可

是机器等不到那一天了,于是最后一根稻草压垮了它。庆幸的是在我休假前它挂了,如果我

在火车上它挂掉了,后果就……

按照文档【ID 1379200.1】中所说的检查了操作系统的参数设置,发现有些参数设置有问题。

但这些参数的调整需要经过严格的测试和验证,才能在生产上进行。况且这篇文章最后的

更新日期是2013-5-13,说明Oracle也是刚刚发现这个问题不久,所以它推荐的方法也不能

轻易的尝试。

  1. kernel.randomize_va_space

Issues caused by the Linux feature Address Space Layout Randomization (ASLR

This problem is reported in Redhat 5 and Oracle 11.2.0.2. You can verify whether ASLR is being used as follows:

/sbin/sysctl -a | grep randomize

kernel.randomize_va_space = 1

If the parameter is set to any value other than 0 then ASLR is in use. Refer the document for details:

Note 1345364.1: ORA-00445: Background Process "xxxx" Did Not Start After 120 Seconds

The solution will be to disable ASLR

  1. Setting PGA_AGGREGATE_TARGET=TRUE

The parameter pga_aggregate_target is a numeric value not a boolean value and therefore must be set to a number for it to function correctly. By specifying it to a text string, we will try to convert it to a meaningful value but which may be insufficient for your environment

Solution: Properly set PGA_AGGREGATE_TARGET to a numeric value.

  1. Setting the PRE_PAGE_SGA to TRUE or Altering SGA_SIZE with PRE_PAGE_SGA set to TRUE

PRE_PAGE_SGA instructs Oracle to read the entire SGA into active memory at instance startup. Operating system page table entries are then prebuilt for each page of the SGA. This setting can increase the amount of time necessary for instance startup, but it is likely to decrease the amount of time necessary for Oracle to reach its full performance capacity after startup. PRE_PAGE_SGA can increase the process startup duration, because every process that starts must access every page in the SGA, this can cause the PMON process to take longer to start and exceed the timeout which is by default 120 seconds causing the instance startup to fail.

Setting PRE_PAGE_SGA to TRUE can increase the process startup duration, because every process that starts must access every page in the SGA, however overhead can be significant if your system frequently creates and destroys processes by, for example, continually logging on and logging off.

Check whether PRE_PAGE_SGA is set to TRUE

--OR--

Verify the generate trace for the occurance of function: ksmprepage()

Solution: Setting PRE_PAGE_SGA to FALSE will avoid this code executing so pages are only touched as needed rather than touching every single page when the process starts. This can avoid or minimize the problem from occuring however the underlying cause is still an Operating System resource shortage

参考文档:

  1.   Troubleshooting Guide (TSG) - ksvcreate: Process(xxxx) creation failed / ORA-00445: background process "xxxx" did not start after n seconds [ID 1379200.1]
    
  2.   Bug 9871302 - Windows: Cannot make new connection to database on Windows platforms with TNS-12560 [ID 9871302.8]
    
  3.   ORA-00445: Background Process "xxxx" Did Not Start After 120 Seconds [ID 1345364.1]
    
lzp_lrp
lzp_lrp   Ds   Rxr 2014.12.28 18:34
Csdn user default icon
上传中...
上传图片
插入图片
准确详细的回答,更有利于被提问者采纳,从而获得C币。复制、灌水、广告等回答会被删除,是时候展现真正的技术了!
其他相关推荐
PL/SQL登录oracle数据库,无法登录解决方法
今天遇到了一个问题,学生在使用PL/SQL在进行Oralce数据查询时,查询结果比较多,将PL/SQL强制关闭,今天再使用PL/SQL时,输入用户名和密码后,点击登录,就一直处于加载状态,点击此界面时,弹出窗体,让你关闭PL/SQL。服务和监听都正常启动,而且可以直接登录到SQLPLUS中,进行相应的操作。          在查看他的目录后,发现有两个PL/SQL,而且每个安装的信息不完整,将
安装上oracle11g客户端后,PL/SQL连接不上
 1、问题描述1:原来机子上装了 Oracle 客户端程序,用pl/sql能正常访问远程数据库,而昨天我装了Oracle服务端后用pl/sql就访问不了,报错:ORA-12154:TNS:无法解析指定的连接标识符;      问题描述2:安装上oracle11g客户端后,PL/SQL连接不上 2、网上找了很多 都没有找到解决的方案,觉得应该是新装的Oracle服务器和客户端有什么冲突,我
ORACLE11G中PLSQL中文显示乱码、Linux下sqlplus查询中文乱码
问题描述: 本地是win7操作系统,cmd里面sqlplus进去连接oracle数据库,中文可以正常显示,但是plsql连接oracle数据库,中文显示乱码,还有xshell远程连接oracle服务器登录进去使用sqlplus登录也显示中文乱码。 1、检查windows7下面cmd里面sqlplus中oracle server端的字符集。 打开cmd,用sqlplus登录,输入下面S
plsql developer 连接oracle 11g数据库错误代码ora12557解决办法
说明:ora12557报错的原因自己感觉是重装oracle之前没有把原先的oracle卸载干净,navicat 可以连接 oracle,但是pl/sql developer 始终报错ora12557。 解决办法: 上网搜instantclient_11_2,下载解压之后,在pl/sql developer首选项->连接—>将Oracle主目录设置为D:\instantclient_
用PL/SQL Developer(远程)连接Oracle 11g时报错“ORA-12541: TNS: 无监听程序”,解决办法
方法/步骤 1 从开始菜单中打开“Oracle Net Configuration Assistance”,选择“监听程序配置”,如下图所示,点击下一步。 步骤阅读 2 选择“重新配置”,如下图所示,点击下一步。 步骤阅读 3 选择监听程序,如下图所示,选择协议,一般默认就好,点击下一步。 步骤阅读 步骤阅读
ORACLE11g关于用户密码过期问题的解决
oracle11g,会遇到sqlplus登陆oracle数据库时提示“口令将过期 ”等情况。 解决方案: 按照如下步骤进行操作: 1、查看用户proifle,一般是default: sql>Select username,PROFILE FROM dba_users; 2、查看概要文件(如default)的密码有效期: sql>Select * FROM dba
pl/sql developer 无法连接本地Oracle(注意是本地oracle)
本来挺简单的事情,会由于客观环境的原因让问题变得很复杂和无法理解 如果本地的oracle无法用pl/sql developer  连接上,大多是因为本地oracle混乱,各种客户端,各种服务器版本造成的,develop不知道加载哪个oracle。 解决方法是通过修改develper的配置来解决,如图: 第一步:打开developer的登陆界面,点击“取消”按钮,进入未登陆界面
PL/SQL Developer连接本地Oracle 11g 64位数据库
今天在虚拟机里安装了一个Oracle 11g 64位版本,在用PLSQL Developer连接时,发现竟然没有数据库连接TNS。以为是PLSQL Developer没有识别到Oracle Hom和OCI Libaray 。 就以非登录模式进入PLSQL Developer,在Oracle Home设置下面没有可选项,OCI Libaray可以选到。于是手动指定Oracle Home路径,选择识别
解决sql plus能连接上本地库但用pl/sql developer却连接不上的问题
【吐个槽先】:oracle的维护真是老大难的问题!于我而言   今天用PL/SQL Developer启动本地库时,报错ORA-12514:TNS:监听程序当前无法识别连接描述符中请求的服务。。想着自己之前也遇过类似问题,并且在本分类里面有过解决方案。但是尝试用sql plus连接本地库时,发现竟然能成功。。——觉得特诡异。   网上寻找解决办法,首选是在无登录状态下修改:Tools->p
【oracle】plsql连接oracle(localhost连接成功,ip连接提示无监听)问题
解决plsql 连接oracle问题。 本机localhost,127可通,输入 其他ip(包括本机ip)不通,提示无监听错误!同时其他机器也无法连接本机数据库 1. 问题   环境是oracle11gr2,本地用plsql还是toad连接都没有任何问题,而用别的机器远程连接就提示 无监听程序等错误。         2. 原因  oracle安装路径 --- 例如: F:\app\A