oRACLE错误怎么办,Oracle的常见错误及解决办法

ORA-12528:

TNS:listener: all appropriate instances are blocking new connections

ORA-12528问题是因为监听中的服务使用了动态服务,实例虽然启动，但没有注册到监听。实例是通过PMON进程注册到监听上的，而PMON进程需要在MOUNT状态下才会启动。所以造成了上面的错误。

解决这个问题,有三种方法：

1、把监听设置为静态；

2、在tnsnames.ora中追加(UR=A)；

3、重新启动服务；

方法1、通过修改listener.ora的参数,把listener.ora动态注册设置为静态注册,然后重新启动监听

# listener.ora Network Configuration File: $ORACLE_HOME\network\admin\listener.ora

# 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状态,通过修改tnsnames.ora的参数# tnsnames.ora Network Configuration File: $ORACLE_HOME\network\admin\tnsnames.ora

# 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;

http://blog.sina.com.cn/s/blog_636415010100x3lc.html

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启动，可能报错：

[[email protected] ~]$ sqlplus / as sysdba

sqlplus: error while loading shared libraries: libsqlplus.so: cannot open shared object file: No such file or directory

而设置LD_LIBRARY_PATH后，问题解决：

[[email protected] ~]$ export LD_LIBRARY_PATH=$ORACLE_HOME/lib

[[email protected] ~]$ 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>

还有一种类似的错误：

[[email protected] ~]$ 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

[[email protected] ~]$ export LD_LIBRARY_PATH=$ORACLE_HOME/lib

[[email protected] ~]$ 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>

错误的表相虽然不同，但是解决方法是相同的。

http://blog.itpub.net/4227/viewspace-718879/

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/datafiles/control1.ctl‘

ORA-27091: skgfqio: unable to queue I/O

SVR4 Error: 25: Inappropriate ioctl for device

Additional information: 3

说明control1.ctl文件有问题啦！

想把这个文件mv到别的目录做个备份，但I/O error，果然是坏了！

一般控制文件有3个，除非3个都坏了，不然可以用好的替换坏的：

rm control1.ctl

cp control2.ctl control1.ctl

再重新启动Oracle就OK啦！

http://blog.sina.com.cn/s/blog_49c1dffa0100teu5.html

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用户组中

Tips:

Linux/unix下非oracle用户下不建议使用sqlplus "/ as sysdba"登陆，建议使用sqlplus /nolog后使用conn 命令或sqlplus username[@sid]登陆

http://blog.sina.com.cn/s/blog_622a00690100zklx.html

原文：http://www.cnblogs.com/softidea/p/3912369.html