oracle常见故障.docx

关于ORA-01034和ORA-27101的一种解决方法1 先看oracle的监听和oracle的服务是否都启动了。启动oracle监听：cmd的命令行窗口下，输入lsnrctl start，回车即启动监听。2 查看oracle的sid叫什么，比如创建数据库的时候，实例名叫“abc”，那么先手工设置一下oralce的sid，cmd命令窗口中，set ORACLE_SID=abc3 再输入sqlplus /nolog,回车再输入 conn / as sysdba;回车4 再输入startup，回车.这步是启动oracle服务。如果startup启动被告知已经启动了，可以先输入shutdown immediate；等shutdown结束之后，再输入startup。5 过几秒钟等命令运行完成，就能连接了。这个时候，可以输入select * from user_tables;测试一下，看是否有查询结果。6 出现ORA-01034和ORA-27101的原因是多方面的：主要是oracle当前的服务不可用，shared memory realm does not exist，是因为oracle没有启动或没有正常启动，共享内存并没有分配给当前实例.所以，通过设置实例名，再用操作系统身份验证的方式，启动数据库。这样数据库就正常启动了，就不会报ORA-01034和ORA-27101两个启动异常了。-今天在测试库上遇到了ORA-27101的错误，当通过客户端连接到db时返回如下：oraclebluerin admin$ sqlplus system/oracletestSQL*Plus: Release .0 - Production on Tue Aug 10 11:05:04 2010Copyright (c) 1982, 2007, Oracle. All rights reserved.ERROR:ORA-01034: ORACLE not availableORA-27101: shared memory realm does not existLinux-x86_64 Error: 2: No such file or directoryProcess ID: 0Session ID: 0 Serial number: 0对于这个问题第一印象，可能会觉得没有足够的内存空间，来创建共享内存段1.首先检查内核参数设置以及alert.log 文件fs.file-max = 6553600kernel.shmall = 2097152kernel.shmmax = 2147483648kernel.shmmni = 4096kernel.sem = 250 32000 100 128net.ipv4.ip_local_port_range = 9000 65500net.core.rmem_default = 4194304net.core.rmem_max = 4194304net.core.wmem_default = 262144net.core.wmem_max = 1048576查看后都没有问题，主机有足够的内存，数据库也已经在启动状态：SQL select open_mode from v$database;OPEN_MODE-READ WRITE2.之后发现在报错之后，继续输入用户名和密码确可以登陆Enter user-name: systemEnter password:Connected to:Oracle Database 11g Enterprise Edition Release .0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL3.查看sqlnet.log日志oraclebluerin log$ pwd/u01/app/product/11.1.0/db_1/network/logoraclebluerin log$ cat sqlnet.logDirectory does not exist for read/write /u01/app/product/11.1.0/db_1/log /u01/app/product/11.1.0/db_1/log/diag/clients*Fatal NI connect error 12541, connecting to:(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=34)(PORT=1521)(CONNECT_DATA=(SERVICE_NAME=Oracle8)(CID=(PROGRAM=javalocalhost)(HOST=localhost)(USER=oracle)VERSION INFORMATION:TNS for Linux: Version .0 - ProductionTCP/IP NT Protocol Adapter for Linux: Version .0 - ProductionTime: 05-AUG-2010 18:30:51Tracing not turned on.Tns error struct:ns main err code: 12541TNS-12541: TNS:no listenerns secondary err code: 12560nt main err code: 511TNS-00511: No listenernt secondary err code: 111nt OS err code: 0*提示找不到listener.4.检查listener是否配置正确oraclebluerin alert$ cat /u01/app/product/11.1.0/db_1/network/admin/listener.ora# listener.ora Network Configuration File: /u01/app/product/11.1.0/db_1/network/admin/listener.ora# Generated by Oracle configuration tools.SID_LIST_LISTENER =(SID_LIST =(SID_DESC =(ORACLE_HOME = /u01/app/product/11.1.0/db_1)(SID_NAME = siebtest)LISTENER =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 34)(PORT = 1521)oraclebluerin $ tnsping testUsed parameter files:/u01/app/product/11.1.0/db_1/network/admin/sqlnet.oraUsed TNSNAMES adapter to resolve the aliasAttempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 34)(PORT = 1521) (CONNECT_DATA = (SERVICE_NAME = siebtest)OK (0 msec)listener 配置没有问题。5.由于实例已经在启动的状态，这时候考虑到实例注册的问题SQL show parameter local_listenerNAME TYPE VALUE- - -local_listener string发现local_listener没有值。6.设置local_listener参数SQL alter system set local_listener=(ADDRESS =(PROTOCOL=TCP)(HOST=34)(PORT=1521)(SID=siebtest);System altered.SQL alter system register;System altered.7.再次登陆成功oraclebluerin alert$ sqlplus system/oracletestSQL*Plus: Release .0 - Production on Tue Aug 10 12:51:11 2010Copyright (c) 1982, 2007, Oracle. All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release .0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL总结：如果LOCAL_LISTENER丢失，会导致自动实例注册失败，数据库实例不会识别Listener,当Listener连接数据库实例的时候，由于Listener没有注册，导致了 ORA-27101: shared memory realm does not exist.Oracle启动监听报错：The listener supports no services解决$ lsnrctl start报错提示:The listener supports no servicesThe command completed successfully问题原因：数据库实例没注册到listener解决方法：在listener.ora里面添加了一行SID_LIST_LISTENER =(SID_LIST = (SID_DESC = (GLOBAL_DBNAME = orcl) (SID_NAME = orcl) )Oracle10g - 改变监听端口，导致控制台不可用切记：监听ip设为主机名，不然可能会出问题1.修改perties属性文件中端口信息$ORACLE_HOME/$HOSTNAME_$ORACLE_SID/sysman/config/perties/db/oracle/product/11.2/db_1/ASUS_orcl/sysman/config这个文件中有两行含有1521端口号的信息，将他们统统的修改为1526oracle.sysman.eml.mntr.emdRepPort=1521oracle.sysman.eml.mntr.emdRepConnectDescriptor=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=testdb)(PORT=1521)(CONNECT_DATA=(SERVICE_NAME=ora10g)2.修改targets.xml配置文件中端口信息$ORACLE_HOME/$HOSTNAME_$ORACLE_SID/sysman/emd/targets.xml这个文件中的1521端口号的信息统统的修改为15263.重启EMemctl stop dbconsoleemctl start dbconsole4.验证EM登陆在本地浏览器中输入如下的URLhttp:/localhost:1158/em/看到登陆界面了，OK，搞定。Oracle 学习笔记：Oracle10g监听端口改变后DB Control无法使用的解决方法修改前的listener.oraSID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = D:oracleproduct) (PROGRAM = extproc) ) )LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = logicgate)(PORT = 1521) ) (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0) ) )修改后的listener.oraSID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = D:oracleproduct) (PROGRAM = extproc) ) (SID_DESC = (GLOBAL_DBNAME = epcit) (ORACLE_HOME = d:oracleproduct) (SID_NAME = epcit) ) )LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = logicgate)(PORT = 1523) ) (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0) ) )监听端口从1521变为1523。由于oracle实例的动态注册只能在默认端口1521上运行，端口改变后我这里使用了静态注册。重新启动监听器：D:lsnrctl stopD:lsnrctl startD:lsnrctl servicesConnecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=logicgate)(PORT=1523)Services Summary.Service PLSExtProc has 1 instance(s). Instance PLSExtProc, status UNKNOWN, has 1 handler(s) for this service. Handler(s): DEDICATED established:0 refused:0 LOCAL SERVERService epcit has 1 instance(s). Instance epcit, status UNKNOWN, has 1 handler(s) for this service. Handler(s): DEDICATED esta