Home » RDBMS Server » Networking and Gateways » ORA-12528: TNS:listener: all appropriate instances are blocking new connections
ORA-12528: TNS:listener: all appropriate instances are blocking new connections [message #227165] Tue, 27 March 2007 08:12 Go to next message
ddkdhar
Messages: 68
Registered: February 2007
Member
my databse oracle 10g on xp
iam getting this error ihave no idea about this
ORA-12528: TNS:listener: all appropriate instances are blocking new connections

C:\Documents and Settings\dharanidhar>sqlplus/nolog

SQL*Plus: Release 10.1.0.2.0 - Production on Tue Mar 27 18:25:29 2007

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

SQL> conn sys as sysdba
Enter password:
Connected.
SQL> shutdown
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-12528: TNS:listener: all appropriate instances are blocking new connections
SQL> exit
Disconnected from Personal Oracle Database 10g Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

C:\Documents and Settings\dharanidhar>set oracle_sid =oracle

C:\Documents and Settings\dharanidhar>sqlplus hr/hr

SQL*Plus: Release 10.1.0.2.0 - Production on Tue Mar 27 18:29:29 2007

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

ERROR:
ORA-12560: TNS:protocol adapter error


Enter user-name: hr/hr@oracle
ERROR:
ORA-12528: TNS:listener: all appropriate instances are blocking new connections


Enter user-name:
ERROR:
ORA-12560: TNS:protocol adapter error


SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus

C:\Documents and Settings\dharanidhar>
C:\Documents and Settings\dharanidhar>sqlplus/nolog

SQL*Plus: Release 10.1.0.2.0 - Production on Tue Mar 27 18:33:37 2007

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

SQL> conn sys/oracle@oracle as sysdba
ERROR:
ORA-12528: TNS:listener: all appropriate instances are blocking new connections


SQL> conn sys as sysdba
Enter password:
ERROR:
ORA-12528: TNS:listener: all appropriate instances are blocking new connections


SQL> quit

C:\Documents and Settings\dharanidhar>lsnrctl

LSNRCTL for 32-bit Windows: Version 10.1.0.2.0 - Production on 27-MAR-2007 18:36
:14

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

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> stop
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=karimojj-c876e5)(PORT=15
21)))
The command completed successfully
LSNRCTL> start listener
Starting tnslsnr: please wait...

TNSLSNR for 32-bit Windows: Version 10.1.0.2.0 - Production
System parameter file is G:\oracle\product\10.1.0\db_4\network\admin\listener.or
a
Log messages written to G:\oracle\product\10.1.0\db_4\network\log\listener.log
Trace information written to G:\oracle\product\10.1.0\db_4\network\trace\listene
r.trc
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=karimojj-c876e5)(PORT=15
21)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROCipc)
)(PROTOCOL_STACK=(PRESENTATION=GIOP)(SESSION=RAW)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=karimojj-c876e5)(PORT=15
21)))
STATUS of the LISTENER
------------------------
Alias listener
Version TNSLSNR for 32-bit Windows: Version 10.1.0.2.0 - Produ
ction
Start Date 27-MAR-2007 18:36:24
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level user
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File G:\oracle\product\10.1.0\db_4\network\admin\listener.o
ra
Listener Log File G:\oracle\product\10.1.0\db_4\network\log\listener.log

Listener Trace File G:\oracle\product\10.1.0\db_4\network\trace\listener.t
rc
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=karimojj-c876e5)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROCipc))(PROTOCOL_S
TACK=(PRESENTATION=GIOP)(SESSION=RAW)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
LSNRCTL>

---------------------------------------------------------------

C:\Documents and Settings\dharanidhar>sqlplus/nolog

SQL*Plus: Release 10.1.0.2.0 - Production on Tue Mar 27 18:36:58 2007

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

SQL> connect / as sysdba
ERROR:
ORA-12560: TNS:protocol adapter error


SQL> conn hr/hr@oracle
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor


SQL> conn sys as sysdba
Enter password:
ERROR:
ORA-12528: TNS:listener: all appropriate instances are blocking new connections

Report message to a moderator

Re: ORA-12528: TNS:listener: all appropriate instances are blocking new connections [message #227173 is a reply to message #227165] Tue, 27 March 2007 08:17 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member
Googled.....can you verify

Quote:
ORA-12528: TNS:listener: all appropriate instances are blocking new connections
This is a new Oracle 10g error code, which is normally visible during instance startup.

If this error message is still displayed a few minutes after trying to start the instance, check if there is no problem starting up the instance.


Quote:

This is a known problem with Oracle 10g where a SHUTDOWN IMMEDIATE is followed by a STARTUP MOUNT. Perform another SHUTDOWN on the database and then issue a STARTUP. You should be able to connect to the database.

[Updated on: Tue, 27 March 2007 08:17]

Report message to a moderator

Re: ORA-12528: TNS:listener: all appropriate instances are blocking new connections [message #227176 is a reply to message #227165] Tue, 27 March 2007 08:22 Go to previous messageGo to next message
ddkdhar
Messages: 68
Registered: February 2007
Member
C:\Documents and Settings\dharanidhar>sqlplus/nolog

SQL*Plus: Release 10.1.0.2.0 - Production on Tue Mar 27 18:49:19 2007

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

SQL> shutdown immediate
ORA-12560: TNS:protocol adapter error
SQL> conn sys as sysdba
Enter password:
ERROR:
ORA-12528: TNS:listener: all appropriate instances are blocking new connections


SQL> conn sys/oracle as sysdba
ERROR:
ORA-12560: TNS:protocol adapter error


SQL> conn sys/oracle@oracle as sysdba
ERROR:
ORA-12528: TNS:listener: all appropriate instances are blocking new connections

how to login and perform shutdown and start up database
i have no idea

Report message to a moderator

Re: ORA-12528: TNS:listener: all appropriate instances are blocking new connections [message #227184 is a reply to message #227176] Tue, 27 March 2007 08:28 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member
Hi,
ORA-12560: TNS:protocol adapter error

Verify your oracle service is running...
ORA-12528: TNS:listener: all appropriate instances are blocking new connections

Known issue with 10g...
you should try without TNS_ENTRY...
set oracle_sid=<sid>
sqlplus sys as sysdba
password : ***********


http://dbataj.blogspot.com/2007/02/ora-12528-tnslistener-all-appropriate.html


regards
Taj

Report message to a moderator

Re: ORA-12528: TNS:listener: all appropriate instances are blocking new connections [message #227209 is a reply to message #227165] Tue, 27 March 2007 09:31 Go to previous messageGo to next message
ddkdhar
Messages: 68
Registered: February 2007
Member
thanks taj
but again i have got another error
SQL> select status from v$instance;

STATUS
------------
STARTED

SQL> alter database mount;

Database altered.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01172: recovery of thread 1 stuck at block 89 of file 2
ORA-01151: use media recovery to recover block, restore backup if needed

[Updated on: Tue, 27 March 2007 09:48]

Report message to a moderator

Re: ORA-12528: TNS:listener: all appropriate instances are blocking new connections [message #227211 is a reply to message #227165] Tue, 27 March 2007 09:41 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
If you can't 'ORA-01151: use media recovery to recover block, restore backup if needed', then you could always reformat the disk & start over.

Is there any addition information in the alert log file?

[Updated on: Tue, 27 March 2007 09:41] by Moderator

Report message to a moderator

Re: ORA-12528: TNS:listener: all appropriate instances are blocking new connections [message #227215 is a reply to message #227165] Tue, 27 March 2007 09:49 Go to previous messageGo to next message
ddkdhar
Messages: 68
Registered: February 2007
Member
thank you
what is alert log file
where can i locate

[Updated on: Tue, 27 March 2007 09:50]

Report message to a moderator

Re: ORA-12528: TNS:listener: all appropriate instances are blocking new connections [message #227216 is a reply to message #227165] Tue, 27 March 2007 09:54 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>where can i locate
If you don't know where it is on YOUR computer, why do you expect me to know where it is?
Use Windoze SEARCH capabilty

Report message to a moderator

Re: ORA-12528: TNS:listener: all appropriate instances are blocking new connections [message #227221 is a reply to message #227165] Tue, 27 March 2007 10:09 Go to previous messageGo to next message
ddkdhar
Messages: 68
Registered: February 2007
Member
after searching only i have asked above one.n even in windows desktop search also showing no results
thank you 
search is complete .there are no results to dispaly

Report message to a moderator

Re: ORA-12528: TNS:listener: all appropriate instances are blocking new connections [message #227222 is a reply to message #227221] Tue, 27 March 2007 10:13 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member
Typically located in $ORACLE_BASE/admin/bdump/ALERT<SID>.LOG


regards
Taj

Report message to a moderator

Re: ORA-12528: TNS:listener: all appropriate instances are blocking new connections [message #227223 is a reply to message #227165] Tue, 27 March 2007 10:14 Go to previous messageGo to next message
ddkdhar
Messages: 68
Registered: February 2007
Member
in this location there is no alert log file

$ORACLE_HOME/RDBMS/trace directory.
is there any name for alert log ( there are many trace files in this location)

Report message to a moderator

Re: ORA-12528: TNS:listener: all appropriate instances are blocking new connections [message #227226 is a reply to message #227165] Tue, 27 March 2007 10:15 Go to previous messageGo to next message
ddkdhar
Messages: 68
Registered: February 2007
Member
taj
no files in bdump
G:\oracle\product\10.1.0\db_4\admin\sample\bdump

Report message to a moderator

Re: ORA-12528: TNS:listener: all appropriate instances are blocking new connections [message #227227 is a reply to message #227226] Tue, 27 March 2007 10:18 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member
May be some delete your alert<sid>.log file... it will automatically created.
for that you should shutdown your database and startup again..


then alert<sid>.log file created and we know more information about your problem.

and you should go through below link
http://download-uk.oracle.com/docs/cd/B14117_01/server.101/b10739/toc.htm


hope this helps
Taj

Report message to a moderator

Re: ORA-12528: TNS:listener: all appropriate instances are blocking new connections [message #227229 is a reply to message #227165] Tue, 27 March 2007 10:19 Go to previous messageGo to next message
ddkdhar
Messages: 68
Registered: February 2007
Member
ok i got it

Report message to a moderator

Re: ORA-12528: TNS:listener: all appropriate instances are blocking new connections [message #308576 is a reply to message #227165] Mon, 24 March 2008 23:32 Go to previous messageGo to next message
den2005
Messages: 1
Registered: March 2008
Location: Manila
Junior Member
Hi everybody,

I am new here and just started learning Oracle, I have same error, yesterday I was able to connect to this database and created many tables, now this error appears, Oracle is installed locally in my laptop...Should I also post the alert<databasename>.log?

Report message to a moderator

Re: ORA-12528: TNS:listener: all appropriate instances are blocking new connections [message #308577 is a reply to message #227165] Mon, 24 March 2008 23:37 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Too bad you did not read & follow the posting guideline
http://www.orafaq.com/forum/t/88153/0/

Report message to a moderator

Previous Topic: Client Tracking in Database
Next Topic: Many connections established at a time
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Fri Apr 19 14:18:00 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.