Dear all,
I can't log into a PDB even though it is listed as a service (Service "hrpdb" has 1 instance(s)...) and the number of processes has been increased. The user account is open. All check done and it looks good (tnsping, v$resource_limit...). But if ii try to connect i get either:
- ORA-01017: invalid username/password;
or
- ORA-12520: TNS:listener could not find available handler for requested type of server
Could someone please help? Does anyone have an idea what it could be?
Thanks
Ousseini
##### Here are some details
--> Listener in GRID_HOME
[oracle@oradbhost1 ~]$ env | grep ORA
ORACLE_UNQNAME=CDBRAC
ORACLE_SID=+ASM1
ORACLE_BASE=/u01/app/oracle
ORACLE_HOSTNAME=oradbhost1.localdomain
ORACLE_TERM=xterm
ORACLE_HOME=/u01/app/19.3.0/grid
[oracle@oradbhost1 ~]$
[oracle@oradbhost1 ~]$ ps -ef | grep [p]mon
oracle 9879 1 0 09:36 ? 00:00:00 asm_pmon_+ASM1
oracle 12081 1 0 13:28 ? 00:00:00 ora_pmon_cdbrac1
[oracle@oradbhost1 ~]$
[oracle@oradbhost1 ~]$ ps -ef | grep [l]sn
oracle 10532 1 0 09:36 ? 00:00:03 /u01/app/19.3.0/grid/bin/tnslsnr ASMNET1LSNR_ASM -no_crs_notify -inherit
oracle 10827 1 0 09:36 ? 00:00:00 /u01/app/19.3.0/grid/bin/tnslsnr LISTENER_SCAN3 -no_crs_notify -inherit
oracle 10841 1 0 09:36 ? 00:00:00 /u01/app/19.3.0/grid/bin/tnslsnr LISTENER_SCAN2 -no_crs_notify -inherit
[oracle@oradbhost1 ~]$
[oracle@oradbhost1 ~]$ lsnrctl status LISTENER_SCAN3 | grep -i hrpdb
Service "hrpdb" has 1 instance(s).
[oracle@oradbhost1 ~]$
[oracle@oradbhost1 ~]$ lsnrctl status LISTENER_SCAN3
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 09-JUN-2023 15:45:20
Copyright (c) 1991, 2023, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN3)))
STATUS of the LISTENER
------------------------
Alias LISTENER_SCAN3
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 09-JUN-2023 09:36:46
Uptime 0 days 6 hr. 8 min. 33 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/19.3.0/grid/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/oradbhost1/listener_scan3/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN3)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.104)(PORT=1521)))
Services Summary...
.....
Service "hrpdb" has 1 instance(s).
Instance "cdbrac1", status READY, has 1 handler(s) for this service...
.....
--> Listener services lists hrpdb service also O.K.
oracle@oradbhost1 ~]$ lsnrctl services LISTENER_SCAN3
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 09-JUN-2023 18:16:52
Copyright (c) 1991, 2023, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN3)))
Services Summary...
......
Service "hrpdb" has 1 instance(s).
Instance "cdbrac1", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:blocked
REMOTE SERVER
(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.111)(PORT=1521))
......
→ TNSPING works fine
oracle@oradbhost1 ~]$ tnsping hrpdb
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 09-JUN-2023 18:21:58
Copyright (c) 1997, 2023, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oradbhost-scan)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = hrpdb)))
OK (0 msec)
[oracle@oradbhost1 ~]$
SYS@cdbrac1:CDB$ROOT> !tnsping hrpdb
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 09-JUN-2023 15:45:43
Copyright (c) 1997, 2023, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oradbhost-scan)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = hrpdb)))
OK (0 msec)
@:_container_name> conn / as sysdba
Connected.
SYS@cdbrac1:CDB$ROOT>
SYS@cdbrac1:CDB$ROOT> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB READ WRITE NO
4 HRPDB READ WRITE NO
5 OEPDB READ WRITE NO
6 SHPDB READ WRITE NO
SYS@cdbrac1:CDB$ROOT>
SYS@cdbrac1:CDB$ROOT> show parameter processes
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes integer 1
db_writer_processes integer 1
gcs_server_processes integer 2
global_txn_processes integer 1
job_queue_processes integer 80
log_archive_max_processes integer 4
processes integer 500
SYS@cdbrac1:CDB$ROOT>
SYS@cdbrac1:CDB$ROOT> select current_utilization, limit_value ,resource_name
2 from v$resource_limit
3 where resource_name IN ('sessions','processes');
CURRENT_UTILIZATION LIMIT_VALUE RESOURCE_NAME
------------------- -------------------- --------------
97 500 processes
114 772 sessions
SYS@cdbrac1:CDB$ROOT> show con_name
CON_NAME
------------------------------
CDB$ROOT
SYS@cdbrac1:CDB$ROOT> col username format a25
SYS@cdbrac1:CDB$ROOT> col name format a15
SYS@cdbrac1:CDB$ROOT> col pdb format a15
SYS@cdbrac1:CDB$ROOT>
SYS@cdbrac1:CDB$ROOT> SELECT name, pdb FROM v$services ORDER BY name;
NAME PDB
--------------- ---------------
SYS$BACKGROUND CDB$ROOT
SYS$USERS CDB$ROOT
cdbrac CDB$ROOT
cdbracXDB CDB$ROOT
hrpdb HRPDB
oepdb OEPDB
pdb PDB
shpdb SHPDB
8 rows selected.
SYS@cdbrac1:CDB$ROOT> alter session set container = hrpdb;
Session altered.
SYS@cdbrac1:CDB$ROOT> show con_name
CON_NAME
------------------------------
HRPDB
SYS@cdbrac1:CDB$ROOT> select username, account_status from dba_users where username like 'HR%';
USERNAME ACCOUNT_STATUS
--------------- --------------
HR OPEN
HRADMIN OPEN
SYS@cdbrac1:CDB$ROOT> alter user hr identified by hr;
User altered.
SYS@cdbrac1:CDB$ROOT> conn hr/hr
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
@:CDB$ROOT>
@:CDB$ROOT> conn / as sysdba
Connected.
SYS@cdbrac1:CDB$ROOT> show con_name
CON_NAME
------------------------------
CDB$ROOT
SYS@cdbrac1:CDB$ROOT> alter session set container = hrpdb;
Session altered.
SYS@cdbrac1:CDB$ROOT> show con_name
CON_NAME
------------------------------
HRPDB
SYS@cdbrac1:CDB$ROOT> conn hr/hr@hrpdb
ERROR:
ORA-12520: TNS:listener could not find available handler for requested type of server
Warning: You are no longer connected to ORACLE.
@:CDB$ROOT> exit
[oracle@oradbhost1 ~]$