Local User Login Error with PDB in Oracle 19c Database -- Error ORA-01017: invalid username/password; logon denied

Getting below error while connecting local user with PDB database 19c .

ORA-01017: invalid username/password; logon denied

• Make sure you have PDB instance entry in tnsnames.ora 
• Listener should be up and running also PDB database service should be register with listener 

 

Query to check service status

SQL>

SQL> SELECT name, pdb FROM   v$services ORDER BY name;

NAME                           PDB

------------------------------ ------------------------------

SYS$BACKGROUND                 CDB$ROOT

SYS$USERS                      CDB$ROOT

sg                             CDB$ROOT

sgXDB                          CDB$ROOT

sg_pdb1                        SG_PDB1

SQL>

SQL>

[oracle@  admin]$ sqlplus test/test@sg_pdb1

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Nov 24 09:17:30 2020

Version 19.3.0.0.0

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

ERROR:

ORA-01017: invalid username/password; logon denied

Enter user-name:

ERROR:

ORA-01017: invalid username/password; logon denied

Enter user-name:

ERROR:

ORA-01017: invalid username/password; logon denied

SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus

[oracle@admin]$

[oracle@admin]$ vi sqlnet.ora

[oracle@admin]$

[oracle@admin]$

[oracle@admin]$ sqlplus test/test@sg_pdb1

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Nov 24 09:17:46 2020

Version 19.3.0.0.0

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

Last Successful login time: Tue Nov 24 2020 09:16:12 +00:00

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL>

SQL>

SQL> exit

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

[oracle@admin]$

[oracle@admin]$

[oracle@admin]$ cat sqlnet.ora

# sqlnet.ora Network Configuration File: /u01/app/oracle/product/19.0.0/network/admin/sqlnet.ora

# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

SQLNET.ALLOWED_LOGON_VERSION_SERVER=11

[oracle@admin]$

[oracle@admin]$

Wait Event : Log File Sync

 What Exactly Is A 'Log File Sync' Wait event?

------------------------------------------------

Log file sync wait event, which in many cases is caused by poor storage performance.

user session issuing a commit command must wait until the LGWR (Log Writer) process writes the log entries associated with the user transaction to the log file on the disk.

Oracle must commit the transaction’s entries to disk (because it is a persistent layer) before acknowledging the transaction commit.

 

At commit time, a process creates a redo record (containing commit opcodes) and copies that redo record into the log buffer. Then, that process signals LGWR to write the contents of log buffer. LGWR writes from the log buffer to the log file and signals user process back completing a commit. A commit is considered successful after the LGWR write is successful.

 

 

A commit is not complete until LGWR writes the log buffers, including the commit redo records to the log files. In a nutshell, after posting LGWR to write, user or background processes wait for LGWR to signal back with a 1-second timeout. The User process charges this wait time as a ‘log file sync’ event.

 

The log file sync wait event represents the time the session is waiting for the log buffers to be written to disk.

 

For example, the following user transaction consists of Insert, Select and Update statements, and completes with a commit:

User Transaction:

Insert…

Select…

Update…

Commit…

Root Causes for 'Log File Sync' Waits

---------------------------------------------------

LGWR is unable to complete writes fast enough for one of the following reasons:

Disk I/O performance to log files is not good.

LGWR is starving for CPU resource. If the server is very busy, LGWR can starve for CPU too. 

LGWR is unable to complete writes fast enough due to file system or Unix buffer cache limitations.

Guidelines For Resolving 'Log File Sync' Waits

----------------------------------------------

If CPU starvation is an issue, then reducing CPU starvation is the correct step to resolve it.

If commit rate is higher, then decreasing commits is correct step but, in a few cases, if that is not possible, increasing the priority of LGWR (using nice) or increasing the priority class of LGWR to RT might provide some relief.

Solid State Disk devices also can be used if the redo size is extreme. in some case , it is also preferable to decrease redo size.

check redo switch per hours , normally it should be 4-5 switch per hours , Sometimes redo logs are very less in size , Oracle recommends, maximum 8 switch per hour .It may be change as per environment to environment  . 

User commit %  and user rollback % can be seen from AWR report which could help to troubleshoot the issue . 

If Redo logs are undersized or huge size both are not good , so tune properly. 

Test in Non-Prod before do any changes in production .