Cannot connect to oracle database with SID

2
votes

I am completely new with oracle db set up. That's why I've downloaded and run following oracle VM. For my project specific purposes I did few steps to have table space and user/scheme with appropriate permissions like this:

  1. create tablespace MYTABLESPACE datafile 'linux/path/MYTABLESPACE.DBF' size 4096m autoextend on next 512m maxsize 8192m;
  2. create user MYUSER identified by MYUSER default tablespace MYTABLESPACE;
  3. grant connect, resource, unlimited tablespace, select any dictionary to MYUSER;

There are default configurations files which are stored within ${ORACLE_HOME}/network/admin

listener.ora

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orclcdb)
      (SID_NAME = orclcdb)
      (ORACLE_HOME = /u01/app/oracle/product/version/db_1)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
    )
  )

#HOSTNAME by pluggable not working rstriction or configuration error.
DEFAULT_SERVICE_LISTENER = (orclcdb)

tnsnames.ora

ORCLCDB=localhost:1521/orclcdb ORCL=  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )   )

With configurations mentioned above I am unable to connect with newly created user using SID, please see table below

password

Following error is received in such case:

[72000][1017] ORA-01017: invalid username/password; logon denied

Can someone please clarify what is missed in configuration because SID connection is requirement for legacy application?

2
oracle
Is the ORCL service actually for the CDB, or is it a PDB? I'm not sure you can connect to an 12c+ DB using SID - why does the legacy application have to use a SID? - Alex Poole
@AlexPoole I've just checked, it looks like CDB is used by default for the VM. Regarding application we have legacy connector which cannot be modified. I've tried with service name, unfortunately it does not work. - fashuser
I just simulated a strange behaviour, When I create user by system user logged in with service name, the user is able to login by using service name, but failed using SID. When I create user by system user logged in with SID, the user is able to login by using SID, but failed using service name. - RAY

2 Answers

3
votes

By default, you can't connect to a PDB using the SID. You have to enable the USE_SID_AS_SERVICE_listener parameter for it to work (where "listener" is the name of your listener). See this example, and the docs. Since your listener is named "LISTENER", you should be able to add this line to the end of your listener.ora:

USE_SID_AS_SERVICE_LISTENER=on
0
votes

The case in the question is logically correct and nothing wrong, you are not supposed to create same normal user for both SID and service, because they are two independent instance.
(Unless you are totally sure want to have two identical username to perform different tasks on two DBs)

I checked the DB installation for DB files, there are two independent DB file groups located in two seperate directories, one for DB Instance(ORACLESID), one for DB Service(ORACLEPDB).

In my Oracle DB installation,

Service name is:
"ORACLEPDB",

SID is:
"ORACLESID"

below is my file structure:

bash-4.2# pwd
/opt/oracle/oradata/ORACLESID
bash-4.2#  ls -hal      
total 2.5G
drwxr-x--- 1 oracle oinstall 4.0K Jan 30 02:17 .
drwxr-xr-x 1 oracle dba      4.0K Jan 30 02:26 ..
drwxr-x--- 1 oracle oinstall 4.0K Jan 30 02:26 ORACLEPDB
-rw-r----- 1 oracle oinstall  18M Jan 30 12:47 control01.ctl
-rw-r----- 1 oracle oinstall  18M Jan 30 02:53 control02.ctl
drwxr-x--- 1 oracle oinstall 4.0K Jan 30 02:19 pdbseed
-rw-r----- 1 oracle oinstall 201M Jan 30 12:47 redo01.log
-rw-r----- 1 oracle oinstall 201M Jan 30 12:41 redo02.log
-rw-r----- 1 oracle oinstall 201M Jan 30 12:41 redo03.log
-rw-r----- 1 oracle oinstall 531M Jan 30 12:46 sysaux01.dbf
-rw-r----- 1 oracle oinstall 911M Jan 30 12:46 system01.dbf
-rw-r----- 1 oracle oinstall 129M Jan 30 12:23 temp01.dbf
-rw-r----- 1 oracle oinstall 341M Jan 30 12:46 undotbs01.dbf
-rw-r----- 1 oracle oinstall 5.1M Jan 30 12:41 users01.dbf


bash-4.2# cd ORACLEPDB/
bash-4.2# pwd
/opt/oracle/oradata/ORACLESID/ORACLEPDB
bash-4.2# ls -hal
total 742M
drwxr-x--- 1 oracle oinstall 4.0K Jan 30 02:26 .
drwxr-x--- 1 oracle oinstall 4.0K Jan 30 02:17 ..
-rw-r----- 1 oracle oinstall 331M Jan 30 12:46 sysaux01.dbf
-rw-r----- 1 oracle oinstall 271M Jan 30 12:47 system01.dbf
-rw-r----- 1 oracle oinstall  37M Jan 30 02:54 temp01.dbf
-rw-r----- 1 oracle oinstall 101M Jan 30 12:46 undotbs01.dbf
-rw-r----- 1 oracle oinstall 5.1M Jan 30 12:41 users01.dbf

To validate this assumption, I created users as below:
"SIDUSER" on connection using "ORACLESID" enter image description here "PDBUSER" on connection using "ORACLEPDB" enter image description here

So after BOTH users are created on SID based connection and Service based connection by "SYSTEM" as SYSDBA user, here is what happen in the "all_users" table:

On SID based connection, PDBUSER does NOT exist:

 select * from all_users where username like '%USER';
 select * from global_name;

enter image description here

On Service name based connection, SIDUSER does NOT exist:

enter image description here

It concludes that even you enable the config :
USE_SID_AS_SERVICE_LISTENER=on

The user connecting with SID and Servicename, even with same username and password, is working on different database instance, the changes made by the user on either one DB will not be seen on another.