ORA-12528: TNS:listener: all appropriate instances are blocking new connections

Installation and support for Oracle DB on CentOS.
TechMonkey
Posts: 2
Joined: 2010/06/22 14:39:57
Contact:

ORA-12528: TNS:listener: all appropriate instances are blocking new connections

Postby TechMonkey » 2010/06/22 15:56:10

Installing Oracle XE 10.2.0.4 on CentOS 5.4. (VM running on ESX vSphere 4.0, cloned from existing VM. 2G RAM, 2G swap.)

Problem: Can't connect to DB.

[font=Courier][root@spacewalk ~]# sqlplus system@xe
SQL*Plus: Release 10.2.0.4.0 - Production on Tue Jun 22 08:07:50 2010 Copyright (c) 1982, 2007, Oracle. All Rights Reserved. Enter password: ERROR: ORA-12528: TNS:listener: all appropriate instances are blocking new connections

[root@spacewalk spaceinstall]# /etc/init.d/oracle-xe restart
Shutting down Oracle Database 10g Express Edition Instance.
Stopping Oracle Net Listener.

Starting Oracle Net Listener.
Starting Oracle Database 10g Express Edition Instance.

[root@spacewalk spaceinstall]# /etc/init.d/oracle-xe status

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 22-JUN-2010 08:24:36

Copyright (c) 1991, 2005, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date 21-JUN-2010 21:58:49
Uptime 0 days 0 hr. 0 min. 9 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Default Service XE
Listener Parameter File /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/network/admin/listener.ora
Listener Log File /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC_FOR_XE)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=spacewalk.mydomain.tld)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "XE" has 1 instance(s).
Instance "XE", status BLOCKED, has 1 handler(s) for this service...
Service "XE_XPT" has 1 instance(s).
Instance "XE", status BLOCKED, has 1 handler(s) for this service...
The command completed successfully
[root@spacewalk spaceinstall]#

[/font]

How I got here:

Used guide at http://wiki.centos.org/HowTos/PackageMa ... /Spacewalk as that is why I'm installing it.

Not knowing any better, I first installed the 11.x versions, no configuration, then saw I could still get the referenced 10.2.x. Oracle docs say rpm -e will completely remove all settings, which is what I wanted. Installed 10.2.x, everything looked fine until I got to the testing oracle part:

Realized that the old hostname was still in defined in "localhosts", fixed that. The GUI start/stop tools gave errors about root not being in DBA group, so added it to said group.

I've looked at oracle-forums for related errors, and so tried defining ORACLE_BASE (/usr/lib/oracle/xe/app/oracle/), ORACLE_HOME(/usr/lib/oracle/xe/app/oracle/product/10.2.0/) and ORACLE_SID (XE). I've tried starting the DB under the 'oracle' user. Quadruple-checked /etc/tnsnames.ora. Cross-referenced https://fedorahosted.org/spacewalk/wiki/HowToInstall.

I'm completely new to Oracle, so any info I've missed, or hints on where to start would be appreciated.

It would be really nice to get this working, as the patch management is one of the main barriers to us adopting CentOS vs. Redhat. (We're a small school.)

TechMonkey
Posts: 2
Joined: 2010/06/22 14:39:57
Contact:

Re: ORA-12528: TNS:listener: all appropriate instances are blocking new connections

Postby TechMonkey » 2010/06/22 16:11:49

Additional info:


[font=Courier]

[root@spacewalk server]# /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/lsnrctl status

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 22-JUN-2010 08:57:28

Copyright (c) 1991, 2005, Oracle. All rights reserved.

Message 1053 not found; No message file for product=network, facility=TNSMessage 1020 not found; No message file for product=network, facility=TNSMessage 1021 not found; No message file for product=network, facility=TNSMessage 1022 not found; No message file for product=network, facility=TNSMessage 1023 not found; No message file for product=network, facility=TNSMessage 1026 not found; No message file for product=network, facility=TNSMessage 1034 not found; No message file for product=network, facility=TNSMessage 1024 not found; No message file for product=network, facility=TNSMessage 1025 not found; No message file for product=network, facility=TNSMessage 1040 not found; No message file for product=network, facility=TNSMessage 1422 not found; No message file for product=network, facility=TNSMessage 1033 not found; No message file for product=network, facility=TNSMessage 1028 not found; No message file for product=network, facility=TNSMessage 1415 not found; No message file for product=network, facility=TNS Message 1050 not found; No message file for product=network, facility=TNS Message 1050 not found; No message file for product=network, facility=TNS Message 1029 not found; No message file for product=network, facility=TNSMessage 1411 not found; No message file for product=network, facility=TNS
Message 1408 not found; No message file for product=network, facility=TNS
Message 1411 not found; No message file for product=network, facility=TNS
Message 1408 not found; No message file for product=network, facility=TNS
Message 1411 not found; No message file for product=network, facility=TNS
Message 1408 not found; No message file for product=network, facility=TNS
Message 1052 not found; No message file for product=network, facility=TNS[
[/font]

yyagol
Posts: 1015
Joined: 2006/06/10 18:27:44
Location: 32 4′N 34 47′E
Contact:

ORA-12528: TNS:listener: all appropriate instances are block

Postby yyagol » 2010/06/27 05:50:29

Hi ,

please check that you /etc/hosts is correctly set for the domain
spacewalk.mydomain.tld

and that iptables is open for loopback interface and for local IP on port 1521
also check that /etc/oratab in with Y on the instance so it would start up when starting the oracle

also do the following steps as oracle user

Code: Select all

$ lsnrctl
LSNRCTL> status

you should see the object that the listener is bind to , if you dont , then restart the listener
by running stop/start and then restart the database .

Code: Select all

LSNRCTL>stop
LSNRCTL>start
LSNRCTL>exit
$sqlplus / as sysdba
SQL> startup