Error ORA-12505 when trying to access a newly installed instance of oracle-11g express

oracle-11g-r2

I encounter problems installing a local instance of oracle express on my Windows 7 – SP1 computer.
I did a basic installation, as administrator (run as 'administrator' on setup.exe).

Then I tried to connect with Oracle Sql Developer with the flollowing parameters :

  • Username : system
  • password : the one I provided during installation
  • hostname : localhost
  • role :sysdba
  • port : 1521 (didn't change the default one)
  • SID : XE.

And I always get :

Statut : échec -Echec du test : Listener refused the connection with the following error:
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

My window user is in ORA_DB group, as well as the administrator user.

When I try to connect as sysdba in SQL command prompt, I got :

ORA-12638: Credential retrieval failed

I also tried to use servicename instead ('orcl'), but it failed with ORA-12514 instead.

My listener.ora looks like this:

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = D:\Programmes\BDD\OracleExpress\app\oracle\product\11.2.0\server)
(PROGRAM = extproc)
)

(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = D:\Programmes\BDD\OracleExpress\app\oracle\product\11.2.0\server)
(PROGRAM = extproc)
)

(SID_DESC =
(SID_NAME = XE)
(ORACLE_HOME = D:\Programmes\BDD\OracleExpress\app\oracle\product\11.2.0\server)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = MACHINE0138.com.company.intra)(PORT = 1521))
)
)

DEFAULT_SERVICE_LISTENER = (XE)

The result of "lsnrctl status"

LSNRCTL for 64-bit Windows: Version 11.2.0.2.0 – Production on 08-F╔VR.-2016 10:40:01

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER

Alias LISTENER

Version TNSLSNR for 64-bit Windows: Version 11.2.0.2.0 – Production

Start Date 08-F╔VR.-2016 10:37:59

Uptime 0 days 0 hr. 2 min. 6 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Default Service XE

Listener Parameter File D:\Programmes\BDD\OracleExpress\app\oracle\product\11.2.0\server\network\admin\listener.ora

Listener Log File D:\Programmes\BDD\OracleExpress\app\oracle\diag\tnslsnr\MACHINE0138\listener\alert\log.xml

Listening Endpoints Summary…

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1ipc)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=MACHINE0138.com.company.intra)(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

And eventually, here's the log.xml file after my attempt :

<msg time='2016-02-08T11:17:55.181+01:00' org_id='oracle' comp_id='tnslsnr'
 type='UNKNOWN' level='16' host_id='MACHINE0138'
 host_addr='fe80::e958:8b9c:be6f:2610%11'>
 <txt>08-FÉVR.-2016 11:17:55 * (CONNECT_DATA=(SID=XE)(CID=(PROGRAM=SQL Developer)(HOST=__jdbc__)(USER=GLE10445))) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=58750)) * establish * XE * 12505
 </txt>
</msg>
<msg time='2016-02-08T11:17:55.185+01:00' org_id='oracle' comp_id='tnslsnr'
 type='UNKNOWN' level='16' host_id='MACHINE0138'
 host_addr='fe80::e958:8b9c:be6f:2610%11'>
 <txt>TNS-12505: TNS:listener does not currently know of SID given in connect descriptor
 </txt>
</msg>

Any tip welcolmed, indeed.
And thanks in advance for reading until this here!

Best Answer

you should create at least one database user that you will use to create database objects. A database user is a type of database object: a user is associated with a database schema, you connect to the database as a database user, and the database user is the owner of any database objects (tables and so on) that you create in the schema associated with the user.

For example, to create a database user named chris(For Example). Follow these steps, using the command line:

Display the SQL command prompt window. For example, on Windows, click Start, then Programs (or All Programs), then Oracle Database 11g Express Edition, and then Run SQL Command Line.

Connect as the SYSTEM user:

Type: connect Enter user-name: system Enter password: Create the user. For example, enter a statement in the following form:

SQL> create user chris identified by <password-for-chris>;

Grant the user the necessary privileges. For example:

SQL> grant CREATE SESSION, ALTER SESSION, CREATE DATABASE LINK, -
  CREATE MATERIALIZED VIEW, CREATE PROCEDURE, CREATE PUBLIC SYNONYM, -
  CREATE ROLE, CREATE SEQUENCE, CREATE SYNONYM, CREATE TABLE, - 
  CREATE TRIGGER, CREATE TYPE, CREATE VIEW, UNLIMITED TABLESPACE -
  to chris;

Optionally, exit SQL*Plus (which also closes the command window):

SQL> exit

SQL Developer as an Alternative for Creating Database Users:

If you have experience with SQL Developer, you can use it instead of the command line to create a database user, as follows:

Create a database connection for the SYSTEM user.

Open that database connection for the SYSTEM user.

Right-click the Other Users node in the Connections navigator under that connection.

Select Create User, and specify the necessary information. (Under System Privileges, grant ALTER SESSION, CREATE SESSION, CREATE DATABASE LINK, CREATE MATERIALIZED VIEW, CREATE PROCEDURE, CREATE PUBLIC SYNONYM, CREATE ROLE, CREATE SEQUENCE, CREATE SYNONYM, CREATE TABLE, CREATE TRIGGER, CREATE TYPE, CREATE VIEW, and UNLIMITED TABLESPACE.)

I am also attaching the screen shot with example , which is given by Oracle BOL for SQL Developer connection.

enter image description here

For your ref Here And Here.

Related Question