Home » Infrastructure » Linux » ORA-12528: TNS:listener: all appropriate instances are blocking new connections (Oracle XE 10.2.0.4 on CentOS 5.4.)
icon4.gif  ORA-12528: TNS:listener: all appropriate instances are blocking new connections [message #462064] Tue, 22 June 2010 11:22 Go to next message
TechMonkey
Messages: 14
Registered: June 2010
Junior Member
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.

(Yes, I've searched hither and yon, which is where I found many of the troubleshooting steps, but this is the first time I've knowingly breathed the same air as an Oracle DB, and so have no idea how to proceed.)
[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]# 



How I got here:

Used guide at (Can'tPostLinksYet)wiki.centos.org/HowTos/PackageManagement/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 (CantPostLinksYet)fedorahosted.org/spacewalk/wiki/HowToInstall.


Next Steps:
No idea. 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.)
Re: ORA-12528: TNS:listener: all appropriate instances are blocking new connections [message #462065 is a reply to message #462064] Tue, 22 June 2010 11:23 Go to previous messageGo to next message
TechMonkey
Messages: 14
Registered: June 2010
Junior Member
Don't know if this is useful:

[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
Re: ORA-12528: TNS:listener: all appropriate instances are blocking new connections [message #462067 is a reply to message #462065] Tue, 22 June 2010 11:38 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
For the first issue,
Most probably the database is not yet started properly
or
The default name of listener (which is "listener") has been changed.
Try to start the DB manually.
export ORACLE_SID=yourSid
export ORACLE_HOME=/yourPath
export PATH=$PATH:$ORACLE_HOME/bin
sqlplus / as sysdba
once inside oracle prompt
shutdown abort
startup

for the second issue, seems your PATH is not properly defined.
Re: ORA-12528: TNS:listener: all appropriate instances are blocking new connections [message #462068 is a reply to message #462067] Tue, 22 June 2010 11:43 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>[root@spacewalk server]#

should NOT be using "root" OS user!
which OS user owns Oracle software directory tree?
Re: ORA-12528: TNS:listener: all appropriate instances are blocking new connections [message #462076 is a reply to message #462068] Tue, 22 June 2010 12:29 Go to previous messageGo to next message
TechMonkey
Messages: 14
Registered: June 2010
Junior Member
Thanks for trying to help.

I was using root because none of the guides, including "Oracle® Database Express Edition Installation Guide 10g Release 2 (10.2) for Linux" (Can'tPostLinksYet-www.oracle.com/technology/software/products/database/xe/files/install.102/b25144/toc.htm) say differently. In fact, the install guide starts off: "To install Oracle Database XE Server:
Log on to your computer with root permissions."


Everything under /usr/lib/oracle/xe/ appears to be owned by user "oracle", group "dba".

Now. SU'ing as oracle, setting the env variables gets me a bit further. (I extrapolated from several posts that home should actually be /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/, before it was up one level)


-bash-3.2$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Jun 22 09:51:16 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORA-44410: XE edition single instance violation error


Well, that makes no sense, I just shut it down, how can there be another instance?!?

So I tried again.

SQL> shutdown abort
ORACLE instance shut down.
SQL> startup


Ooh, looks good!

SQL> quit
Disconnected
-bash-3.2$ sqlplus system@xe

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Jun 22 10:03:17 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

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


Well...PHHHTTT!


And what was that about a second issue? I very much appreciate the help, please remember: I understand general concepts (I know what a path is, how to set an environment variable, etc) but I don't know specifics on Oracle. If you say "Check the status of the database" you're going to have to tell me how or point to instructions.
Re: ORA-12528: TNS:listener: all appropriate instances are blocking new connections [message #462078 is a reply to message #462076] Tue, 22 June 2010 12:31 Go to previous messageGo to next message
TechMonkey
Messages: 14
Registered: June 2010
Junior Member
Oh, I see what you meant about "second problem", yes ORACLE_HOME was wrong. Now I'm pretty much back where I started:

-bash-3.2$ /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 10:18:51

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                22-JUN-2010 08:24:26
Uptime                    0 days 1 hr. 54 min. 25 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.simpsonu.edu)(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


Should I close this and make a new thread?

Also, the password right now is simple, just upper and lower-case, no symbols or anything.

[Updated on: Tue, 22 June 2010 12:39]

Report message to a moderator

Re: ORA-12528: TNS:listener: all appropriate instances are blocking new connections [message #462080 is a reply to message #462076] Tue, 22 June 2010 12:40 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>Well, that makes no sense, I just shut it down, how can there be another instance?!?
Just another oracle quirk.
Did you set your ORACLE_SID properly?
See the following session. ORACLE_SID is crap without any associated database and non-existing instance (idle instance).

oracle@xxx#export ORACLE_SID=crap
oracle@xxx#sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jun 22 13:35:07 2010

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

Connected to an idle instance.

SQL> shutdown abort
ORACLE instance shut down.

set the proper ORACLE_SID
shutdown database.
start the database.

use lsnrctl to stop and start the listener.

Re: ORA-12528: TNS:listener: all appropriate instances are blocking new connections [message #462082 is a reply to message #462080] Tue, 22 June 2010 12:54 Go to previous messageGo to next message
TechMonkey
Messages: 14
Registered: June 2010
Junior Member
Once again, please see wiki.centos.org/HowTos/PackageManagement/Spacewalk#head-a3fb371d3baa89d6356e2e6ca0b4f73f93009638 and fedorahosted.org/spacewalk/wiki/OracleXeSetup for what I "know".

That is all that I know. I set ORACLE_SID to XE. (Is that the same as service-name in tnsnames?) How would I discover what the SID should be? How would I use lsnrctrl to stop and start the listener? Is the listener the same or separate from starting/stopping the database instance? Is it the same as /etc/init.d/oracle-xe stop/start?
Re: ORA-12528: TNS:listener: all appropriate instances are blocking new connections [message #462086 is a reply to message #462082] Tue, 22 June 2010 13:02 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
open up terminal window while logged onto OS as user "oracle" (or which ever user owns Oracle software directory)
COPY the lines below

date
env | sort
id
lsnrctl status
lsnrctl service
uname -a
cat /etc/hosts
ls -l $ORACLE_HOME/dbs
date

PASTE the lines above into terminal window
COPY commands & results, then PASTE all back here
Re: ORA-12528: TNS:listener: all appropriate instances are blocking new connections [message #462088 is a reply to message #462082] Tue, 22 June 2010 13:05 Go to previous messageGo to next message
mkounalis
Messages: 147
Registered: October 2009
Location: Dallas, TX
Senior Member
A few comments:

"ps -ef | grep pmon" I believe should tell you the running instance names. Should see something like pmon_XE in your case.

CentOS is NOT a certified OS to run Oracle on top of - you might be dealing with an incompatibility.

You should do EVERYTHING as the oracle user - and that use should have ORACLE_HOME set to the directory where the oracle binaries are stored. Then you set ORACLE_SID to the name of the instance.

Next you should append your PATH to PATH=$PATH:$ORACLE_HOME/bin

ps -ef | grep tns should yield the listener name.

lsnrctl status LISTENER_<listener_name>

Write up on Oracle XE: http://www.oracle.com/technology/pub/articles/cunningham-database-xe.html

All oracle documentation can b found at docs.oracle.com - some people refer to this as tahiti.oracle.com
Re: ORA-12528: TNS:listener: all appropriate instances are blocking new connections [message #462090 is a reply to message #462086] Tue, 22 June 2010 13:20 Go to previous messageGo to next message
TechMonkey
Messages: 14
Registered: June 2010
Junior Member
(Domain name replaced w/ "spacewalk.mydomain.tld" but it is correct.

-bash-3.2$ date
Tue Jun 22 11:15:11 PDT 2010
-bash-3.2$ env | sort
_=/bin/env
CVS_RSH=ssh
DISPLAY=:0.0
G_BROKEN_FILENAMES=1
HISTSIZE=1000
HOME=/usr/lib/oracle/xe
HOSTNAME=spacewalk.mydomain.tld
INPUTRC=/etc/inputrc
LANG=en_US.UTF-8
LESSOPEN=|/usr/bin/lesspipe.sh %s
LOGNAME=oracle
LS_COLORS=no=00:fi=00:di=00;34:ln=00;36:pi=40;33:so=00;35:bd=40;33;01:cd=40;33;01:or=01;05;37;41:mi=01;05;37;41:ex=00;32:*.cmd=00;32:*.exe=00;32:*.com=00;32:*.btm=00;32:*.bat=00;32:*.sh=00;32:*.csh=00;32:*.tar=00;31:*.tgz=00;31:*.arj=00;31:*.taz=00;31:*.lzh=00;31:*.zip=00;31:*.z=00;31:*.Z=00;31:*.gz=00;31:*.bz2=00;31:*.bz=00;31:*.tz=00;31:*.rpm=00;31:*.cpio=00;31:*.jpg=00;35:*.gif=00;35:*.bmp=00;35:*.xbm=00;35:*.xpm=00;35:*.png=00;35:*.tif=00;35:
MAIL=/var/spool/mail/oracle
NXDIR=/usr/NX
ORACLE_BASE=/usr/lib/oracle/xe/app/oracle/
ORACLE_HOME=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/
ORACLE_SID=XE
PATH=/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/usr/NX/bin:/usr/lib/oracle/xe/app/oracle/product/10.2.0/server//bin
PWD=/usr/lib/oracle/xe
SHELL=/bin/bash
SHLVL=1
SSH_ASKPASS=/usr/libexec/openssh/gnome-ssh-askpass
TERM=xterm
USER=oracle
-bash-3.2$ id
uid=102(oracle) gid=104(dba) groups=104(dba) context=root:system_r:unconfined_t:SystemLow-SystemHigh
-bash-3.2$ lsnrctl status

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 22-JUN-2010 11:15:11

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                22-JUN-2010 10:43:04
Uptime                    0 days 0 hr. 32 min. 7 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
-bash-3.2$ lsnrctl service

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 22-JUN-2010 11:15:11

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))
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 SERVER
Service "XE" has 1 instance(s).
  Instance "XE", status BLOCKED, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "XE_XPT" has 1 instance(s).
  Instance "XE", status BLOCKED, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
The command completed successfully
-bash-3.2$ uname -a
Linux spacewalk.mydomain.tld 2.6.18-164.15.1.el5 #1 SMP Wed Mar 17 11:37:14 EDT 2010 i686 athlon i386 GNU/Linux
-bash-3.2$ cat /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1               spacewalk.mydomain.tld localhost.localdomain localhost
-bash-3.2$ ls -l $ORACLE_HOME/dbs
total 56
-rw-r--r-- 1 oracle dba  336 Jun 22 10:05 alert_XE.log
-rw-rw---- 1 oracle dba 1544 Jun 21 19:21 hc_XE.dat
-r-xr-xr-x 1 oracle dba 8385 Jan 30  2006 init.ora
-rw-rw---- 1 oracle dba   24 Jun 21 19:48 lkXE
-rw-r----- 1 oracle dba 1536 Jun 21 19:21 orapwXE
-rw-r----- 1 oracle dba 2560 Jun 22 10:43 spfileXE.ora
Re: ORA-12528: TNS:listener: all appropriate instances are blocking new connections [message #462093 is a reply to message #462090] Tue, 22 June 2010 13:26 Go to previous messageGo to next message
TechMonkey
Messages: 14
Registered: June 2010
Junior Member
-bash-3.2$ ps -ef | grep pmon
oracle    3500     1  0 10:43 ?        00:00:00 xe_pmon_XE


So is the instance "xe_pmon_XE" or just "XE"?


-bash-3.2$  ps -ef | grep tns
oracle    3495     1  0 10:43 ?        00:00:00 /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/tnslsnr LISTENER -inherit


So the listener is just named "LISTENER" in this case?
Re: ORA-12528: TNS:listener: all appropriate instances are blocking new connections [message #462094 is a reply to message #462093] Tue, 22 June 2010 13:30 Go to previous messageGo to next message
mkounalis
Messages: 147
Registered: October 2009
Location: Dallas, TX
Senior Member
'cat' the contents of "-rw-r--r-- 1 oracle dba 336 Jun 22 10:05 alert_XE.log" and paste it here.
Re: ORA-12528: TNS:listener: all appropriate instances are blocking new connections [message #462095 is a reply to message #462094] Tue, 22 June 2010 13:33 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
127.0.0.1 spacewalk.mydomain.tld localhost.localdomain localhost

above means this system can only talk to itself.
No remote client can connect to this DB
Re: ORA-12528: TNS:listener: all appropriate instances are blocking new connections [message #462096 is a reply to message #462095] Tue, 22 June 2010 13:42 Go to previous messageGo to next message
TechMonkey
Messages: 14
Registered: June 2010
Junior Member
alert_XE just has multiple instances of "Shutting down instance (abort)"

P.S. I'm terribly sorry about the width of the posted codebox. Apparently you can't edit posts after there's been a reply?
Re: ORA-12528: TNS:listener: all appropriate instances are blocking new connections [message #462097 is a reply to message #462095] Tue, 22 June 2010 13:45 Go to previous messageGo to next message
mkounalis
Messages: 147
Registered: October 2009
Location: Dallas, TX
Senior Member
tail -f that alert log file - make sure that you have all the proper environment variables set - oracle home, oracle sid, etc - 'sqlplus / as sysdba' - 'startup'. The alert log
should spew messages about the instance starting up.
Re: ORA-12528: TNS:listener: all appropriate instances are blocking new connections [message #462098 is a reply to message #462096] Tue, 22 June 2010 13:50 Go to previous messageGo to next message
TechMonkey
Messages: 14
Registered: June 2010
Junior Member
One step forward! (http://dbataj.blogspot.com/2007/02/ora-12528-tnslistener-all-appropriate.html)
ORA-12528: TNS:listener: all appropriate instances are blocking new connections
It is known issue with ORACLE 10G.

Suggestion: Don't Use "@(tns_entry string)" with userid/pwd.
just export or set ORACLE_SID and connect WITHOUT "@"tns_entry.


And one step back? How do I "un-idle" an instance?

-bash-3.2$ sqlplus sys as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Jun 22 11:45:14 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Enter password: 
Connected to an idle instance.

SQL> create user spacewalk identified by spacewalk default tablespace users;
*
ERROR at line 1:
ORA-01034: ORACLE not available







Re: ORA-12528: TNS:listener: all appropriate instances are blocking new connections [message #462099 is a reply to message #462098] Tue, 22 June 2010 13:54 Go to previous messageGo to next message
mkounalis
Messages: 147
Registered: October 2009
Location: Dallas, TX
Senior Member
An 'idle' instance is one that isn't running. Either the instance really isn't up and running, or you don't have ORACLE_SID set properly.
Re: ORA-12528: TNS:listener: all appropriate instances are blocking new connections [message #462101 is a reply to message #462099] Tue, 22 June 2010 13:55 Go to previous messageGo to next message
mkounalis
Messages: 147
Registered: October 2009
Location: Dallas, TX
Senior Member
Again, 'ps -ef | grep pmon' will show you if the instance is running. If you see pmon_XE then 'something' is running - but it could be in a stuck state if your alert log
just says 'shuting down'.
Re: ORA-12528: TNS:listener: all appropriate instances are blocking new connections [message #462102 is a reply to message #462098] Tue, 22 June 2010 13:56 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
IDLE instance means, your instance is not yet started.
The above solution may not be workable for you.
Above assumes, you are not going to use any native tns connectivity (even within the same local server).
From your Wiki notes, seems the application will use an tnsentry.
Re: ORA-12528: TNS:listener: all appropriate instances are blocking new connections [message #462103 is a reply to message #462095] Tue, 22 June 2010 14:05 Go to previous messageGo to next message
TechMonkey
Messages: 14
Registered: June 2010
Junior Member
BlackSwan wrote on Tue, 22 June 2010 11:33
127.0.0.1 spacewalk.mydomain.tld localhost.localdomain localhost

above means this system can only talk to itself.
No remote client can connect to this DB



Do you mean oracle client or any IP? Not that it matters: It's been awhile since I've had to really muck about with host files, but I'm pretty sure that just says "map my hostname, and localhost, to 127.0.0.1". Nothing to do with other hosts connecting in.
Re: ORA-12528: TNS:listener: all appropriate instances are blocking new connections [message #462105 is a reply to message #462103] Tue, 22 June 2010 14:13 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
client s/w which resides on same system as DB will be able to use SQL*Net to connect to DB.
No client which exists/resides on a different system will be able to connect to this DB.
127.0.0.1 is ALWAYS "localhost"
Re: ORA-12528: TNS:listener: all appropriate instances are blocking new connections [message #462108 is a reply to message #462088] Tue, 22 June 2010 14:23 Go to previous messageGo to next message
TechMonkey
Messages: 14
Registered: June 2010
Junior Member
mkounalis wrote on Tue, 22 June 2010 11:05
A few comments:

CentOS is NOT a certified OS to run Oracle on top of - you might be dealing with an incompatibility.


Not certified/supported, but since it's 98% RHEL, and there are several guides/blog posts stating that it worked for other people (with the exact same versions) I doubt this is the problem.

Quote:

You should do EVERYTHING as the oracle user - and that use should have ORACLE_HOME set to the directory where the oracle binaries are stored. Then you set ORACLE_SID to the name of the instance.

Next you should append your PATH to PATH=$PATH:$ORACLE_HOME/bin


Thanks, as you can see, I've figured that out, mostly from reading other posts in response to people having similar(ish) problems. What I can't understand is how come none of the guides or even the official docs say this? Sure, it explains how it's needed for remote clients, says *nothing* about needing it for local setup/testing.

http://www.oracle.com/pls/xe102/homepage
Re: ORA-12528: TNS:listener: all appropriate instances are blocking new connections [message #462109 is a reply to message #462102] Tue, 22 June 2010 14:27 Go to previous messageGo to next message
TechMonkey
Messages: 14
Registered: June 2010
Junior Member
Mahesh Rajendran wrote on Tue, 22 June 2010 11:56
IDLE instance means, your instance is not yet started.
The above solution may not be workable for you.
Above assumes, you are not going to use any native tns connectivity (even within the same local server).
From your Wiki notes, seems the application will use an tnsentry.


Yup, but

1: How am I supposed to start this instance?

2: I have no idea what tns connectivity is, or what that implies, or where this tnsentry might be.

Again, *NONE* of the guides, including the official install docs, mention any of this.
Re: ORA-12528: TNS:listener: all appropriate instances are blocking new connections [message #462110 is a reply to message #462109] Tue, 22 June 2010 14:27 Go to previous messageGo to next message
TechMonkey
Messages: 14
Registered: June 2010
Junior Member
-bash-3.2$ lsnrctl status LISTENER

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 22-JUN-2010 12:33:32

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                22-JUN-2010 10:43:04
Uptime                    0 days 1 hr. 50 min. 28 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.simpsonu.edu)(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
-bash-3.2$ lsnrctl restart LISTENER

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 22-JUN-2010 12:33:45

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

NL-00853: undefined command "restart".  Try "help"
-bash-3.2$ lsnrctl stop LISTENER

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 22-JUN-2010 12:33:55

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))
The command completed successfully
-bash-3.2$ lsnrctl start LISTENER

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 22-JUN-2010 12:34:04

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

Starting /usr/lib/oracle/xe/app/oracle/product/10.2.0/server//bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 10.2.0.1.0 - Production
System parameter file is /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/network/admin/listener.ora
Log messages written to /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC_FOR_XE)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=spacewalk.simpsonu.edu)(PORT=1521)))

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                22-JUN-2010 12:34:04
Uptime                    0 days 0 hr. 0 min. 0 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.simpsonu.edu)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
-bash-3.2$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Jun 22 12:34:26 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to an idle instance.

(AlertXE only shows when I abort quit, nothing else. Is there another log I should be looking at? tail -f /var/log/messages doesn't show anything...)


Well, I think I'm gonna go get lunch, you've all been helpful, or at least tried.

Unless anyone has any other ideas, when I get back I think I'll just start a new VM from scratch...

[Updated on: Tue, 22 June 2010 14:39]

Report message to a moderator

Re: ORA-12528: TNS:listener: all appropriate instances are blocking new connections [message #462111 is a reply to message #462110] Tue, 22 June 2010 14:43 Go to previous messageGo to next message
mkounalis
Messages: 147
Registered: October 2009
Location: Dallas, TX
Senior Member
You 'startup' an instance by making sure your ORACLE_HOME is set properly, and your ORACLE_SID is set properly.
Then you get into sqlplus by 'sqlplus / as sysdba'
should say connected to an idle instance.
Then you type in 'startup'.
Tailing the alert log for the instance will show your the instance starting up, as well as any issues encountered (if any) while starting up - yielding clues to why it won't
startup. That is why I asked you to do this above: "tail -f that alert log file - make sure that you have all the proper environment variables set - oracle home, oracle sid, etc - 'sqlplus / as sysdba' - 'startup'. The alert log
should spew messages about the instance starting up."

I am not sure which guides you are reading - this is pretty standard stuff. This isn't part of 'installing' the oracle binaries, but rather 'administrating' an oracle
instance.

Just because you see out there that people are running Oracle on CentOS doesn't mean you won't have any issues - Oracle doesn't test at all with CentOS so there is no
guarantee at all that you aren't hitting an issue caused by something in the OS. CentOS is almost an exact duplicate of RedHat - but as you correctly pointed out - not 100%.
Still - I don't think the issues you are hitting are related to the OS version - but I wanted to make sure you knew that Oracle products are not meant to run on CentOS and
as such you might want to consider an OS that is Oracle certified.
Re: ORA-12528: TNS:listener: all appropriate instances are blocking new connections [message #462113 is a reply to message #462109] Tue, 22 June 2010 14:46 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>2: I have no idea what tns connectivity is, or what that implies, or where this tnsentry might be.
-- Using a tns entry to identify the database to connect to
-- somedatabase is an entry in tnsnames.ora file
sqlplus user/pass@somedatabase

-- without tns entry, set ORACLE_SID to identify the database to connect to
export ORACLE_SID=somedatabase
sqlplus user/pass



>>1: How am I supposed to start this instance?
Excuse us for repeating the same thing, you have to set your ORACLE_SID and do it as you have done above.

Why not just restart the server or VM.
See how it goes? If nothing comes good,
Login as oracle.
export ORACLE_SID=XE
set your PATH etc.
start database
start listener.

>>Again, *NONE* of the guides, including the official install docs, mention any of this.
I agree it will take a while to get used with oracle docs, but trust us, it is written all over.


>>-bash-3.2$ lsnrctl start LISTENER
This assumes you have an entry in listener.ora file called LISTENER.
Check the file.
As shown in output, no instance that is associated with this LISTENER is available now.
so you have to start the database and try
lsnrctl stop
lsnrctl start.


to start the database.
export ORACLE_SID=XE
sqlplus / as sysdba
startup




Re: ORA-12528: TNS:listener: all appropriate instances are blocking new connections [message #462121 is a reply to message #462113] Tue, 22 June 2010 15:50 Go to previous messageGo to next message
mkounalis
Messages: 147
Registered: October 2009
Location: Dallas, TX
Senior Member
FYI - the documentation for XE does go into detail about everything we have explained above . . . .

http://download.oracle.com/docs/cd/B25329_01/doc/admin.102/b25107/startup.htm#CHDIHEFC

Re: ORA-12528: TNS:listener: all appropriate instances are blocking new connections [message #462383 is a reply to message #462064] Wed, 23 June 2010 19:13 Go to previous messageGo to next message
TechMonkey
Messages: 14
Registered: June 2010
Junior Member
FWIW, I started over from a fresh CentOS5 vm, using the same guides and everything worked perfectly.

And I never once had to set an environment variable or log in as "oracle". Razz
Re: ORA-12528: TNS:listener: all appropriate instances are blocking new connections [message #462695 is a reply to message #462383] Fri, 25 June 2010 13:09 Go to previous message
mkounalis
Messages: 147
Registered: October 2009
Location: Dallas, TX
Senior Member
TechMonkey wrote on Wed, 23 June 2010 19:13
FWIW, I started over from a fresh CentOS5 vm, using the same guides and everything worked perfectly.

And I never once had to set an environment variable or log in as "oracle". Razz


That is great - but what happens if something goes awry in your Oracle instance? Just because you got it to startup, doesn't mean you are in the clear. Oracle databases do need routine maintenance - I would highly suggest you still figure out how to use the command-line utilities to login to your instance as well as where the log files are. Good luck!
Previous Topic: MAX SGA Limit on Linux 32-bit
Next Topic: grub loading stage2 error
Goto Forum:
  


Current Time: Thu Mar 28 07:42:49 CDT 2024