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?
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