Oracle的常见错误及解决办法
ORA-12528:
TNS:listener: all appropriate instances are blocking new connections
ORA-12528问题是因为监听中的服务使⽤了动态服务，实例虽然启动，但没有注册到监听。实例是通过PMON进程注册到监听上的，⽽PMON进程需要在MOUNT状态下才会启动。所以造成了上⾯的错误。
解决这个问题，有三种⽅法：
1、把监听设置为静态；
2、在a中追加(UR=A)；
3、重新启动服务；
⽅法1、通过修改a的参数，把a动态注册设置为静态注册，然后重新启动监听
# a Network Configuration File: $ORACLE_HOME\network\a
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = [IP])(PORT = 1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = [DBNAME])
(ORACLE_HOME = [$ORACLE_HOME])
(SID_NAME = [SID])
)
)
静态注册的风险：如果在instance运⾏中，lisener重新启动，就不到instance了。静态注册需要先启动lisener，再启动instance。且静态模式下，lisener status显⽰的是unknown
⽅法2、启动到nomount状态，通过修改a的参数
# a Network Configuration File: $ORACLE_HOME\network\a
# Generated by Oracle configuration tools.
SYK =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = [IP])(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = SYK)
(UR=A)
)
)
然后连接上数据库
SQL>alter database mount;
SQL>alter database open;
⽅法3、重启ORACLE或者重启ORACLE服务
在oracle帐户下依序执⾏如下命令：
sqlplus / as sysdba;//在其它帐户(如root)下执⾏可能会报错（ORA-01031）因为这些帐户没有在dba组中
shutdown immediate;
startup;
oracle实例名: error while loading shared libraries: libskgxp10.so: cannot open shared object file: No such file or directory
是环境变量LD_LIBRARY_PATH的问题。
在10g以后，⼀般情况下环境变量中没有必要设置LD_LIBRARY_PATH，但是⼀旦将ORACLE_HOME迁移到其他⽬录，则环境变量中还需要添加这个变量。Linux和Unix⽀持TAR⽅式迁移ORACLE_HOME，如果有需要将ORACLE_HOME放到其他路径下，那么⼀般都会使⽤tar的⽅式将整个路径拷贝到⽬标⽬录。但是迁移后，如果直接尝试sqlplus启动，可能报错：
[orat3@hpserver2 ~]$ sqlplus / as sysdba
sqlplus: error while loading shared libraries: libsqlplus.so: cannot open shared object file: No such file or directory
⽽设置LD_LIBRARY_PATH后，问题解决：
[orat3@hpserver2 ~]$ export LD_LIBRARY_PATH=$ORACLE_HOME/lib
[orat3@hpserver2 ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Sun Mar 18 16:10:57 2012
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
还有⼀种类似的错误：
[orat0@hpserver2 ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.5.0 - Production on Sun Mar 18 16:12:03 2012
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
oracleorcl10g: error while loading shared libraries: libskgxp10.so: cannot open shared object file: No such file or directory
ERROR:
ORA-12547: TNS:lost contact
Enter user-name:
oracleorcl10g: error while loading shared libraries: libskgxp10.so: cannot open shared object file: No such file or directory
ERROR:
ORA-12547: TNS:lost contact
Enter user-name:
oracleorcl10g: error while loading shared libraries: libskgxp10.so: cannot open shared object file: No such file or directory
ERROR:
ORA-12547: TNS:lost contact
SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
[orat0@hpserver2 ~]$ export LD_LIBRARY_PATH=$ORACLE_HOME/lib
[orat0@hpserver2 ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.5.0 - Production on Sun Mar 18 16:12:18 2012
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
错误的表相虽然不同，但是解决⽅法是相同的。
ORA-00204:ORA-00202:ORA-27091
Oracle数据库启动报如下错误：
ORA-00204: error in reading (block 3, # blocks 8) of controlfile
ORA-00202: controlfile:'/app/oracle/OraHome1/database/l'
ORA-27091: skgfqio: unable to queue I/O
SVR4 Error: 25: Inappropriate ioctl for device
Additional information: 3
说明l⽂件有问题啦！
想把这个⽂件mv到别的⽬录做个备份，但I/O error，果然是坏了！
⼀般控制⽂件有3个，除⾮3个都坏了，不然可以⽤好的替换坏的：
l
l
再重新启动Oracle就OK啦！
ORA-01031: insufficient privileges
出错的场景：
（1）⽤sqlplus "/ as sysdba"登陆
（2）登陆成功后在执⾏相关操作
第⼆种情况没什么说的，没权限，赋权即可
第⼀情况的解决办法：
要sqlplus "/ as sysdba"进⾏登陆必须满⾜如下条件：
（1）linux/unix下有环境变量ORACLE_SID，windows不要求
（2）配置环境变量ORACLE_HOME
（3）linux/unix下$ORACLE_HOME/bin/oracle⽂件在u,g下有s权限（让⾮oracle⽤户可以拥有相当于oracle帐户的，赋值⽅法chmod u+s,g+s $ORACLE_HOME/bin/oracle）（4）执⾏此操作的⽤户必须在dba⽤户组中
（5）a⽂件必须⽀持sqlplus /as sysdba，否则会报如下错误：
Tips:
Linux/unix下⾮oracle⽤户下不建议使⽤sqlplus "/ as sysdba"登陆，建议使⽤sqlplus /nolog后使⽤conn 命令或sqlplus username[@sid]登陆
1、在windows下，SQLNET.AUTHENTICATION_SERVICES必须设置为NTS或者ALL才能使⽤OS认证；不设置或者设置为其他任何值都不能使⽤OS认证。
<Windows>
SQLNET.AUTHENTICATION_SERVICES= (NTS) 基于操作系统验证；
SQLNET.AUTHENTICATION_SERVICES= (NONE) 基于Oracle密码⽂件验证
SQLNET.AUTHENTICATION_SERVICES= (NONE,NTS) ⼆者并存，注意是半⾓，否则不识别
2、在linux下，在SQLNET.AUTHENTICATION_SERVICES的值设置为ALL，或者不设置的情况下，OS验证才能成功；设置为其他任何值都不能使⽤OS认证。
<Unix/Linux>
默认情况下Unix/Linux下的a⽂件是没有SQLNET.AUTHENTICATION_SERVICES参数的，
此时是操作系统验证和Oracle密码验证并存，加上SQLNET.AUTHENTICATION_SERVICES这个参
数后，不管SQLNET.AUTHENTICATION_SERVICES设置为NONE还是NTS还是(NONE,NTS)，都是
基于Oracle密码验证。
The information in this document applies to:
Oracle Net Services - Version: 10.1.0.3
This problem can occur on any platform.
Symptoms
The listener fails to start with the following errors:
TNS-12537: TNS:connection closed
TNS-12560: TNS:protocol adapter error
TNS-00507: Connection closed
Linux Error: 29: Illegal seek
Generic to Unix platforms.
出现下图错误是因为/etc/hosts中没有配置localhost的映射
出现下图错误是因为
（情况1）/etc/hosts中的localhost映射配置错误
（情况2）$ORACLE_HOME/network/a中HOST的值配置错误
Cause
Wrong syntax in hosts file and also due to the the tnslsnr process was enhanced in
10.1.0.3 to support FAN(Fast Application Notification) via
ONS (Oracle Notification Services). This new code opens a socket open on localhost.
Therefore"localhost" should be defined on the system.
This new code opens a socket open on localhost. Therefore "localhost" should be defined on the
system.
Fix
Change /etc/hosts file to include
127.0.0.1 localhost.localdomain localhost
sqlplus / as sysdba 与 sqlplus "/ as sysdba" 的区别是什么？
10G的sqlplus稍微改进了⼀点，
下⾯是摘抄10G新特性的⼀段：
必须使⽤引号吗？为什么，不！
在 Oracle9i 中取消了对内部登录的⽀持之后，全世界许多 DBA 表⽰反对：他们应当如何在命令⾏上输⼊ SYS 的⼝令并保持安全性？嗯，答案是在操作系统提⽰符中使⽤引号：sqlplus "/ as sysdba"
引号的使⽤令⼈遗憾，但还是被⼤家所接受（虽然有些怨⾔）。在 Oracle Database 10g 中不需要这样了。现在您可以在 OS 命令提⽰符下，输⼊以下命令，不需要引号sqlplus / as sysdba
作为 SYSDBA 登录。这种改进不仅意味着您少输了两个字符，还有⼀些额外的好处，例如在 Unix 之类的操作系统中不需要 escape 字符。
如果⼀个Oracle帐户为dba，则可以在sqlplus中使⽤conn user@⽹络服务名 as sysdba
Net Configuration Assistant
ORA-12533
是a配置错误
在oracle帐户下使⽤$ORACLE_HOME/bin/dbstart启动oracle实例时，⼀种错误的解决办法：
/etc/oratab⽂件解析：
[oracle@node1 ~]$ vi /etc/oratab
# This file is used by ORACLE utilities. It is created by root.sh
# and updated by the Database Configuration Assistant when creating
# a database.
# This file is used by ORACLE utilities. It is created by root.sh
# and updated by the Database Configuration Assistant when creating
# a database.
# A colon, ':', is used as the field terminator. A new line terminates
# the entry. Lines beginning with a pound sign, '#', are comments.
#
# Entries are of the form:
# $ORACLE_SID:$ORACLE_HOME:<N|Y>
#
# The first and second fields are the system identifier and home
# directory of the database respectively. The third filed indicates
# to the dbstart utility that the database should , "Y", or should not,
# "N", be brought up at system boot time.
#
# Multiple entries with the same $ORACLE_SID are not allowed.
#
oracle登录命令orcl:/u01/app/oracle/product/10.2.0/db_1:N
最后的N或者Y表⽰是否允许dbstart来启动数据库，如果为N则实例不能通过dbstart启动，表现为sqlplus连接实例时显⽰Connected to an idle instance.
如果为Y则实例可以通过dbstart启动，sqlplus连接后数据库即为open状态。
[oracle@localhost bin]$ vi dbstart
#!/bin/sh
#
# $Id: dbstart.sh 22-may-2008.05:05:45 arogers Exp $
# Copyright (c) 1991, 2008, Oracle. All rights reserved.
#
>>>>>>>
#
# usage: dbstart $ORACLE_HOME
#
# This script is used to start ORACLE from /etc/rc(.local).
# It should ONLY be executed as part of the system boot procedure.
#
# This script will start all databases listed in the oratab file
# whose third field is a "Y". If the third field is set to "Y" and
# there is no ORACLE_SID for an entry (the first field is a *),
# then this script will ignore that entry.
在linux系统下，⽤root⽤户采⽤dbstart脚本直接启动oracle服务或监听
第⼀个问题：启动服务的同时没有启动监听
[root@localhost ~]# su - oracle -c "dbstart"
ORACLE_HOME_LISTNER is not SET, unable to auto-start Oracle Net Listener
Usage: /oradata/oracle/112/bin/dbstart ORACLE_HOME
Processing Database instance "orcl": log file /oradata/oracle/112/startup.log
ORACLE_HOME_LISTNER is not SET, unable to auto-start Oracle Net Listener这个提⽰说明
启动oracle服务的同时没有启动oracle监听服务，因此如果想在启动oracle服务的同时i启动监听
就需要修改dbstart，将ORACLE_HOME_LISTNER值设为$ORACLE_HOME(原来是为$1)
[root@localhost ~]# vi /oradata/oracle/112/bin/dbstart
注：dbshut⼀样的道理，如果想关闭数据库服务的同时关闭监听服务也是将dbshut⽂件中的ORACLE_HOME_LISTNER值设为$ORACLE_HOME
3.赋予⾃启动脚本执⾏权限
chmod 775 /etc/init.d/orcl
4. 增加配置服务
[root@localhost ~]# /sbin/chkconfig --add orcl
orcl 服务不⽀持 chkconfig
“服务不⽀持 chkconfig”：——这个提⽰是因为oracle⾃启动脚本前⾯必须要有以下这两⾏
#chkconfig: 2345 00 01
#description:oracle 11g service
当然如果不采⽤这个命令进⾏配置服务，也可以⼿动⽅式创建⽂件链接来完成，如下所⽰：
⽂件链接
ln -s /etc/init.d/orcl /etc/rc.d/rc2.d/S99orcl
ln -s /etc/init.d/orcl /etc/rc.d/rc3.d/S99orcl
ln -s /etc/init.d/orcl /etc/rc.d/rc4.d/S99orcl
ln -s /etc/init.d/orcl /etc/rc.d/rc5.d/S99orcl
ln -s /etc/init.d/orcl /etc/rc.d/rc0.d/K01orcl
ln -s /etc/init.d/orcl /etc/rc.d/rc1.d/K01orcl
ln -s /etc/init.d/orcl /etc/rc.d/rc6.d/K01orcl
wwwblogs/lanzi/archive/2012/04/18/2456022.html
wwwblogs/hibernate315/archive/2012/01/30/2398869.html
ORA-12518:
原因：
SID_LIST_LISTENER=(
(SID_DESC=
(SID_NAME=实际的$ORACLE_SID)
(ORACLE_HOME=实际的$ORACLE_HOME)
(PROGRAM=实际的$ORACLE_SID)//此处配置错误，实际使⽤的实例不需要配置这个，这⾏去掉或改为(global_dbname=实际的$ORACLE_SID)即可
)
关于a配置内容：
Oracle⽂档上是这样写的
The LISTENER entry defines the listening protocol address for a listener named LISTENER,
and the SID_LIST_LISTENER entry provides information about the services statically supported by the listener LISTENER.
Example 10-1 a File
LISTENER= (DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=sales-server)(PORT=1521))
(ADDRESS=(PROTOCOL=ipc)(KEY=extproc)
)))
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(SID_NAME=plsextproc)
(ORACLE_HOME=/oracle10g)
(PROGRAM=extproc)))“
the SID_LIST_LISTENER entry provides information about the services statically supported by the listener LISTENER”这句话要如何理解呢？
感觉上Listener是提供了“protocol address”
然后SID_LIST_LISTENER提供了在“protocol address”上跑的具体应⽤。这样的理解对吗？
TNS-01155: 在 LISTENER.ORA 中指定的 SID_LIST_LISTENER 参数不正确
近⽇在配置Oracle 10G的流复制环境时，遇到⼀个问题，关闭数据库（shutdown immediate）后，通过SQL Plus连接数据库:conn sys/his@orc0 as sysdba，出现如下错误：ORA-12514: TNS: 监听程序当前⽆法识别连接描述符中请求的服务
通过重启服务的⽅式启动数据库，再次连接却能成功登录，也就是说在关闭数据库状态下⽆法连接服务器。
开始以为是系统环境变量Oracle_SID的配置问题，因为机器有多个实例，⼀阵折腾后还是不能连接。后来查资料得知：
Oracle9i以后，后台进程PMON⾃动在中注册在系统参数SERVICE_NAMES中定义的服务名，SERVICE_NAMES默认为DB_NAME+DOMAIN_NAME。监听配置⽂
件a中可以不必指定监听的服务名。但是，当数据库处于关闭状态下PMON进程没有启动，也就不会⾃动注册监听的实例名，所以使⽤sqlplus sys/his@orc0 as sysdba 会出现ORA-12514错误。
如果在a⽂件中指定监听的实例名，则即使数据库处于关闭状态，仍然可以连接。
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = G:/oracle/product/10.2.0/db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = ORCL)
(ORACLE_HOME = G:/oracle/product/10.2.0/db_1)
(SID_NAME = ORCL)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = zyk)(PORT = 1521))
)
)
以上粗体部件为增加的内容，修改后重启监听服务后即可。
附：通过在lsnrctl中输⼊set display verbose，然后再通过命令service查看，服务状态为READY表⽰PMON⾃动注册的服务名，⽽UNKNOWN则表⽰该服务是⼿⼯在LISTENER.ORA中配置的数据库服务。
1、在CMD中启动SQLPLUS；⽤sqlplus /as sysdba登录，提⽰
连接到:Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
2.执⾏ALTER USER acutal_username ACCOUNT UNLOCK；解锁
3.更改sys 和system 设置⽤户名、密码；
SQL> alter acutal_username sys identified by acutal_password;
ORA-12547: TNS: 丢失连接
查明原因：
[oracle@localhost admin]$ a
# a Network Configuration File: /oracle/app/oracle/product/11.2.0/dbhome_1/network/a
# Generated by Oracle configuration tools.
TCP.VALIDNODE_CHECKING = YES
SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER= (MD5)
SQLNET.CRYPTO_SEED = 'OracleEncryption'
SQLNET.ENCRYPTION_TYPES_SERVER= (AES256)
TCP.INVITED_NODES= (ip1,ip2,ip3,ip4,ip5)
ADR_BASE = /oracle/app/oracle
SQLNET.EXPIRE_TIME=10
可以看出，正是由于存在tcp.validnode_checking=yes才去检查tcp.invited_nodes定义的节点，将客户端ip地址加⼊该⽂件中即可。
Oracle修改表中记录时出现record is locked by another user的问题
在操作表时没有commit，导致表被锁，只要执⾏下⾯两⾏语句，就可以了将⾏锁解锁了。
1.Select t
2.username,t2.sid,t2.serial#,t2.logon_time from v$locked_object t1,v$session t2 where t1.session_id=t2.sid;
-- 查看被锁表的sid 和 serial#
2.alter system kill session 'sid, serial#';
--kill 锁，例如输⼊ alter system kill session '140, 25';
执⾏完以上两步后，就可以编辑数据了
使⽤alter database 移动数据⽂件时，在执⾏完alter database rename <source file path> to <des file path>命令之后，再试图打开数据库:alter database open。报错：
ORA-01113:⽂件7需要介质恢复
ORA-01110：数据⽂件7：’E:ORACLE/PRODUCT/10.2.0/ORADATA/ORCL/TEST01.DBF’。
这是由于数据库认为这个数据⽂件遭到破坏了，需要使⽤recover命令通过备份、⽇志信息来恢复。数据库的备份恢复是个⽐较复杂的问题，但是这个实例的解决办法还是⽐较简单的。
Linux位数查看办法：
(1)
终端输⼊:
file /sbin/init
如显⽰：
/sbin/init: ELF 32-bit LSB executable, Intel 80386, version 1 (SYSV), for GNU/Linux 2.6.9, dynamically linked (uses shared libs), for GNU/Linux 2.6.9, stripped
即是32位的 linux, 如是64位的, 显⽰的是 64-bit
(2)
终端输⼊:
uname -a
如显⽰:
Linux redhat-tj 2.6.18-128.el5 #1 SMP Wed Jan 21 10:44:23 EST 2009 i686 i686 i386 GNU/Linux
则是32位
(3)
终端输⼊:
getconf WORD_BIT
如显⽰:
32
则是32位的linux
安装 oracle 时出现如下错误：
[oracle@database]$ ./runInstaller
Starting Oracle
Checking Temp space: must be greater than 80 MB. Actual 1184 MB Passed
Checking swap space: must be greater than 150 MB. Actual 3027 MB Passed
Checking monitor: must be configured to display at least 256 colors
>>> Could not execute auto check for display colors using command /usr/bin/xdpyinfo. Check if the DISPLAY variable is set. Failed <<<<
Some requirement checks failed. You must fulfill these requirements before
continuing with the installation,at which time they will be rechecked.
Continue? (y/n) [n]
解决办法(1)：
注销登录⽤户，使⽤oracle安装⽤户重新登录系统即可解决，⽐如之前已经创建好的安装⽤户oracle，使⽤oracle⽤户登录系统就可以解决问题
解决办法(2)：
在root⽤户下执⾏xhost +，然后切换到oracle帐户下，执⾏安装程序即可
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
RHEL5+Oracle10g安装(单机)
« 上一篇
ORACLE启动切换实例命令
下一篇 »
发表评论