Oracle DBA Blog
HOW TO FIX TNS RELATED ERRORS
PROBLEM STATEMENT
After creating a new user and assigning him to create session privilege so that he will be able to connect to the pluggable database, I noticed that the user could not remotely connect to the database. The user gets the below error when attempting to connect remotely;
sqlplus ARLNORLDT/Arlnorld@PDB1
SQL*Plus: Release 19.0.0.0.0 – Production on Mon May 30 06:34:56 2022 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved.
ERROR: ORA-12154: TNS: could not resolve the connect identifier specified ORA-12154:
TNS: could not resolve the connect identifier specified
Cause:
TNS can’t identify the targeted database because it is not registered to it. This means that both the address and the service name of the specified database are not found in tnsnames.ora file. The specified pluggable database (PDB1) and its dependencies are missing inside tnsnames.ora as shown below.
oracle@ip-172-31-90-162.ec2.internal(cdb19c): cat tnsnames.ora # tnsnames.ora Network Configuration File: /u01/app/oracle/product/19.3.0/dbhome_1/network/a # Generated by Oracle configuration tools.
CDB19C =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = TCP)(HOST = ip-172-31-90-162.ec2.internal)(PORT = 1521)
)
)
(CONNECT_DATA = (SERVICE_NAME = cdb19c)
)
)
Solution:
Use either ‘netca’ or ‘netmgr’ to assign the specified database to tnsnames.ora. You will need the:
oracle@ip-172-31-90-162.ec2.internal(cdb19c):
- the public IP address of the database,
- database name (when prompted to enter service name and general-purpose name or global name) and
- port number.
After adding the database to tns, you should be able to see it inside the file as follows;
oracle@ip-172-31-90-162.ec2.internal(cdb19c): cat tnsnames.ora # tnsnames.ora
Network Configuration File: /u01/app/oracle/product/19.3.0/dbhome_1/network/a # Generated by Oracle configuration tools.
CDB19C =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = ip-172-31-90-162.ec2.internal)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = cdb19c)
)
)
PDB1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)
(HOST = ec2-54-165-163-27.compute- 1.amazonaws.com)(PORT = 1 )
(CONNECT_DATA =
(SERVICE_NAME = pdb1)
)
)
Now connect as sysdba and open the pluggable database(s)sqlplus sys / as sysdba
SQL> alter pluggable database all open; Pluggable database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
Exit and now connect to the desired pluggable database as follows:
oracle@ip-172-31-90-162.ec2.internal(cdb19c): sqlplus ARLNORLDT/Arlnorld@PDB1
You may encounter certain errors such as invalid username/passwd; logon denied.
oracle@ip-172-31-90-162.ec2.internal(cdb19c): sqlplus ARLNORLDT/Arlnorld@PDB1 SQL*Plus: Release 19.0.0.0.0 – Production on Mon May 30 06:40:18 2022 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved.
ERROR: ORA-01017: invalid username/password; logon denied
Do not panic, connect again as sysdba to the container database and then connect to the targeted PDB and change the password as follows:
oracle@ip-172-31-90-162.ec2.internal(cdb19c): sqlplus / as sysdba
##Connect to desired pluggable database
SQL> alter session set container=pdb1;
##Change password
SQL> alter user ARLNORLDT identified by abc123;
Connect to the database remotely without further error(s):
oracle@ip-172-31-90-162.ec2.internal(cdb19c): sqlplus ARLNORLDT/abc123@PDB1
SQL*Plus: Release 19.0.0.0.0 – Production on Mon May 30 06:42:44 2022 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 – Production Version 19.3.0.0.0
MIGRATION OF DATA FROM ACCESS TO POSTGRESQL AND ORACLE
Software
- SQLIE
- POSTGRES
- ORACLE
Database is successfully migrated into the targeted server (PostgresSQL)
Leave a Reply