1

I started with Oracle DB. On Windows Server 2003 I installed Oracle Database 10g Express Edition and then I also installed PL SQL Developer.

I tried log on via PL SQL Developer to XE database.

I use this credentials:

  • Username: system
  • Password: password is correct :)
  • Database: XE

I got this error: ORA-12528. So I google it and tried this solution: http://www.dba-oracle.com/sf_ora_12528_tns_listener_all_appropriate_instances_are_blocking_new_connection_tips_bc1.htm:

SQL> conn system as sysdba
Enter password:
Connected.

SQL> $ lsnrctl


LSNRCTL> stop
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))
The command completed successfully

and again start listener

LSNRCTL> start
Starting tnslsnr: please wait...

TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Production
System parameter file is C:\oraclexe\app\oracle\product\10.2.0\server\network\ad
min\listener.ora
Log messages written to C:\oraclexe\app\oracle\product\10.2.0\server\network\log
\listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC_FOR
_XEipc)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=jan)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Produ
ction
Start Date                24-J┌N-2011 19:14:14
Uptime                    0 days 0 hr. 0 min. 1 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Default Service           XE
Listener Parameter File   C:\oraclexe\app\oracle\product\10.2.0\server\network\a
dmin\listener.ora
Listener Log File         C:\oraclexe\app\oracle\product\10.2.0\server\network\l
og\listener.log
Listening Endpoints Summary...
 (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC_FOR_XEipc)))
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=jan)(PORT=1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
 Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
 Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Then I tried again connect to XE database via PL SQL Developer but I got error ORA 12514.

I checked the listener log file (listener.log), here is it output:

System parameter file is C:\oraclexe\app\oracle\product\10.2.0\server\network\admin\listener.ora
Log messages written to C:\oraclexe\app\oracle\product\10.2.0\server\network\log\listener.log
Trace information written to C:\oraclexe\app\oracle\product\10.2.0\server\network\trace\listener.trc
Trace level is currently 0

Started with pid=2964
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC_FOR_XEipc)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=jan)(PORT=1521)))
Listener completed notification to CRS on start

TIMESTAMP * CONNECT DATA [* PROTOCOL INFO] * EVENT [* SID] * RETURN CODE
24-JÚN-2011 19:17:52 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=)(USER=Administrator))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=169869568)) * status * 0
24-JÚN-2011 19:18:19 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=XE)(CID=(PROGRAM=C:\Program Files\PLSQL Developer\plsqldev.exe)(HOST=JAN)(USER=Administrator))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.5.100)(PORT=2907)) * establish * XE * 12514
TNS-12514: TNS:listener does not currently know of service requested in connect descriptor
24-JÚN-2011 19:18:48 * service_register * xe * 0

Here I put configs.

tnsname.ora

XE =
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = jan)(PORT = 1521))
   (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SERVICE_NAME = XE)
   )
 )
EXTPROC_CONNECTION_DATA =
 (DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
   )
   (CONNECT_DATA =
     (SID = PLSExtProc)
     (PRESENTATION = RO)
   )
 )
ORACLR_CONNECTION_DATA =
 (DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
   )
   (CONNECT_DATA =
     (SID = CLRExtProc)
     (PRESENTATION = RO)
   )
 )

listener.ora

SID_LIST_LISTENER =
 (SID_LIST =
   (SID_DESC =
     (SID_NAME = PLSExtProc)
     (ORACLE_HOME = C:\oraclexe\app\oracle\product\10.2.0\server)
     (PROGRAM = extproc)
   )
   (SID_DESC =
     (SID_NAME = CLRExtProc)
     (ORACLE_HOME = C:\oraclexe\app\oracle\product\10.2.0\server)
     (PROGRAM = extproc)
   )
 )

LISTENER =
 (DESCRIPTION_LIST =
   (DESCRIPTION =
     (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
     (ADDRESS = (PROTOCOL = TCP)(HOST = jan)(PORT = 1521))
   )
 )

DEFAULT_SERVICE_LISTENER = (XE)

I tried made a change in tsname.ora and change SERVICE_NAME to SID

XE =
 (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = jan)(PORT = 1521))
   (CONNECT_DATA =
     (SERVER = DEDICATED)
     (SID = XE)
   )
 )

But I got error:

**ORA-12505** TNS: listener does not currently know of SID given in connect descriptor

I remove change (SID = XE => SERVICE_NAME=XE) in tsname.ora and finaly I checked status of listener with command.

LSNRCTL> stat

Result is here:

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Produ
ction
Start Date                24-J┌N-2011 19:30:31
Uptime                    0 days 0 hr. 5 min. 57 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Default Service           XE
Listener Parameter File   C:\oraclexe\app\oracle\product\10.2.0\server\network\a
dmin\listener.ora
Listener Log File         C:\oraclexe\app\oracle\product\10.2.0\server\network\l
og\listener.log
Listening Endpoints Summary...
 (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC_FOR_XEipc)))
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=jan)(PORT=1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
 Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
 Instance "PLSExtProc", status UNKNOWN, 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...
Service "xe" has 1 instance(s).
 Instance "xe", status BLOCKED, has 1 handler(s) for this service...
The command completed successfully

I tried restarted OracleServiceXE (Control panel > Adminstrative Toolls > Services>)


Last attempt to solve this problem was to connect to XE via cmd.

SQL> conn system/jano@XE
ERROR:
ORA-12528: TNS:listener: all appropriate instances are blocking new connections

Nothing works. I can not connect to XE.

This is my first time when I deal with Oracle DB. What is root of problem and how can solve this issue.

Thank you for cooperation.

1 Answer 1

1

once disable your all network connections then restart your PC/Server and then try to log in. if it is successful enable your all network connections.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .