oracle-databaseoracle21c

cannot connect to pdb in oracle 21c with a regular user


Hi I've recently installed an oracle 21c on Oracle linux 8. and I havent't been able to connect to PDB with a regular(non sysdba user) to pdb . I've create a few users in pdb given them create session,connect rights and still no luck. oracle denies the connect with ora 01017 - invalid username or password so here is my relevant files env

    ORACLE_UNQNAME=debugutf
    ORACLE_SID=debugutf
    ORACLE_BASE=/home/app/oracle
    LANG=en_US.UTF-8
    HISTCONTROL=ignoredups
    DISPLAY=localhost:10.0
    ORACLE_HOME=/home/app/oracle/product/21.0.0/dbhome_1
    HOSTNAME=new_debug.ieml.ru
    PDB_NAME=PDB1
    DATA_DIR=/home/oradata
    S_COLORS=auto
    which_declare=declare -f
    CLASSPATH=/home/app/oracle/product/21.0.0/dbhome_1/jlib:/home/app/oracle/product/21.0.0/dbhome_1/rdbms/jlib
    USER=oracle
    PWD=/home/app/oracle/homes/OraDB21Home1/network/admin
    SSH_ASKPASS=/usr/libexec/openssh/gnome-ssh-askpass
    HOME=/home/oracle
    TMP=/tmp
    XDG_DATA_DIRS=/home/oracle/.local/share/flatpak/exports/share:/var/lib/flatpak/exports/share:/usr/local/share:/usr/share
    NLS_LANG=RUSSIAN_RUSSIA.AL32UTF8
    TMPDIR=/tmp
    ORA_INVENTORY=/home/app/oraInventory
    MAIL=/var/spool/mail/oracle
    SHELL=/bin/bash
    TERM=linux
    SHLVL=1
    ORACLE_HOSTNAME=new_debug.ieml.ru
    LOGNAME=oracle
    ORACLE_PDB_SID=PDB1
    PATH=/home/app/oracle/product/21.0.0/dbhome_1/bin:/usr/sbin:/usr/local/bin:/home/oracle/.local/bin:/home/oracle/bin:/usr/local/bin:/usr/bin:/usr/local/sbin:/usr/sbin
    HISTSIZE=1000
    LESSOPEN=||/usr/bin/lesspipe.sh %s
    BASH_FUNC_which%%=() {  ( alias;
     eval ${which_declare} ) | /usr/bin/which --tty-only --read-alias --read-functions --show-tilde --show-dot $@
    }
    _=/usr/bin/env
    OLDPWD=/home/oracle


[oracle@new_debug admin]$ cat listener.ora
# listener.ora Network Configuration File: /home/app/oracle/homes/OraDB21Home1/network/admin/listener.ora
# Generated by Oracle configuration tools.
  SID_LIST_LISTENER    =
 (SID_LIST =
 (SID_DESC =
 (GLOBAL_DBNAME = pdb1.ieml.ru)
 (ORACLE_HOME = /home/app/oracle/product/21.0.0/dbhome_1)
 (SID_NAME = PDB1 )
 )
 )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST=
        (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
        (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.117)(PORT = 1521))
      )
    )
  )

[oracle@new_debug admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /home/app/oracle/homes/OraDB21Home1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

DEBUGUTF.IEML.RU =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.117)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = debugutf.ieml.ru)
    )
  )


PDB1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.117)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PDB1)
    )
  )


LISTENER =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.117)(PORT = 1521))

[oracle@new_debug admin]$ cat sqlnet.ora
# sqlnet.ora Network Configuration File: /home/app/oracle/homes/OraDB21Home1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT )

SQLNET.ALLOWED_LOGON_VERSION_SERVER = 8
SQLNET.ALLOWED_LOGON_VERSION_CLIENT = 8

[oracle@new_debug admin]$ cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.1.117 new_debug.ieml.ru ieml.ru

[oracle@new_debug admin]$ lsnrctl status

LSNRCTL for Linux: Version 21.0.0.0.0 - Production on 28-ИЮЛ-2024 16:26:11

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

Соединение с (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
СТАТУС ПРОСЛУШИВАТЕЛЯ
------------------
Псевдоним                LISTENER
Версия           TNSLSNR for Linux: Version 21.0.0.0.0 - Production
Начальная Дата             28-ИЮЛ-2024 15:16:32
Время работы              0 дней 1 час. 9 мин. 39 сек
Уровень трассировки            off
Защита          ON: Local OS Authentication
SNMP                      OFF
Файл параметров прослушивателя   /home/app/oracle/homes/OraDB21Home1/network/admin/listener.ora
Журн.файл прослушивателя          /home/app/oracle/diag/tnslsnr/new_debug/listener/alert/log.xml
Сводка прослушивания конечных точек...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.117)(PORT=1521)))
Сводка служб...
Служба "1d65526d54d47c93e0637501a8c05507.ieml.ru" имеет 1 экземпл.
  Экземпляр "debugutf", состояние READY, имеет 2 указат. для данной службы...
Служба "pdb1.ieml.ru" имеет 1 экземпл.
  Экземпляр "debugutf", состояние READY, имеет 2 указат. для данной службы...
Команда выполнена успешно.
sqlplus / as sysdba

SQL*Plus: Release 21.0.0.0.0 - Production on Вс Июл 28 16:27:39 2024
Version 21.3.0.0.0

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


Присоединен к:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0

SQL> show con_name

CON_NAME
------------------------------
PDB1

SQL> create user A3 IDENTIFIED BY A3;

Пользователь создан.

SQL> GRANT CONNECT,CREATE SESSION TO A3;

Привилегии предоставлены.
SQL> EXIT
Отсоединено от Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
[oracle@new_debug admin]$ sqlplus /nolog

SQL*Plus: Release 21.0.0.0.0 - Production on Вс Июл 28 16:32:35 2024
Version 21.3.0.0.0

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

SQL> connect
Введите имя пользователя: A3
Введите пароль:
ERROR:
ORA-01017: неверно имя пользователя/пароль; вход в систему запрещается

[oracle@new_debug admin]$ sqlplus / as sysdba;

SQL*Plus: Release 21.0.0.0.0 - Production on Вс Июл 28 16:35:23 2024
Version 21.3.0.0.0

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


Присоединен к:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0

SQL> show parameter local_listener

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string      (ADDRESS=(PROTOCOL=tcp)(HOST=1
                                                 92.168.1.117)(PORT=1521))
 show parameter password

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile            string      EXCLUSIVE

any ideas?

Solution

  • Thanks to a comment by @p3consulting it turned out to be a service name issue. I had to specify full service name with domain.name for connection to be established.

    CONNECT A3/A3@//192.168.1.117:1521/PDB1.IEML.RU