When listener is not aware of your database, you might come across the below error when trying to connect to a database.
PS C:\Users\parvi> sqlplus sys@practice as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jan 7 21:16:26 2025
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Enter password:
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified
You'll need to make your instance known to listener in this case, there are multiple ways to do this, but I'll specify the 2 easiest ones which will help you fix this error.
1) Using Net Manager.
Look for Net Manager in your Oracle Software. Easiest way to do it to go to Start > All Programs > Oracle Binaries (example: Oracle-Ora19DBHome1)
Once Net Manager is opened, follow the below steps:
Expand the left options and select the listener. Then select "Database Services" in the drop down.
Note: Maximize Net Manager is drop down is not properly visible.
.png)
Click on "Add Database" and enter details as shown
.png)
Click on "Save Network Configuration" which will save changes to your network files inside your ORACLE_HOME
.png)
Reload listener & verify if the Listener is now listening to that instance.
PS C:\Users\parvi> lsnrctl reload LSNRCTL for 64-bit Windows: Version 19.0.0.0.0 - Production on 07-JAN-2025 21:42:46 Copyright (c) 1991, 2019, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=DemonoftheFall)(PORT=1521))) The command completed successfully PS C:\Users\parvi> lsnrctl status LSNRCTL for 64-bit Windows: Version 19.0.0.0.0 - Production on 07-JAN-2025 21:54:49 Copyright (c) 1991, 2019, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=DemonoftheFall)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for 64-bit Windows: Version 19.0.0.0.0 - Production Start Date 07-JAN-2025 21:48:30 Uptime 0 days 0 hr. 6 min. 19 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File C:\app\oracle\product\19.3.0\dbhome_1\network\admin\listener.ora Listener Log File C:\app\oracle\product\19.3.0\dbhome_1\log\diag\tnslsnr\DemonoftheFall\listener\alert\log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=DemonoftheFall)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc))) Services Summary... Service "CLRExtProc" has 1 instance(s). Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service... Service "cdb1" has 1 instance(s). Instance "cdb1", status UNKNOWN, has 1 handler(s) for this service... Service "cdb2" has 1 instance(s). Instance "cdb2", status UNKNOWN, has 1 handler(s) for this service... Service "practice" has 2 instance(s). Instance "practice", status UNKNOWN, has 1 handler(s) for this service... Instance "practice", status READY, has 1 handler(s) for this service... Service "practiceXDB" has 1 instance(s). Instance "practice", status READY, has 1 handler(s) for this service... The command completed successfully
Your DB instance should show READY, even if not, try connecting to your instance to see if any error is left. Sometimes listener might take 1 login to update the status.
PS C:\Users\parvi> sqlplus sys@practice as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jan 7 22:02:04 2025 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Enter password: Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 SQL> select name, open_mode from V$database; NAME OPEN_MODE --------- -------------------- PRACTICE READ WRITE
2) Manually updating the listener.ora & tnsnames.ora
Here I'll add COPYDB (another database) to guide you.
Go to location : %ORACLE_HOME%/network/admin
Open listener.ora and add entries as highlighted below for your missing instance
# listener.ora Network Configuration File: C:\app\oracle\product\19.3.0\dbhome_1\NETWORK\ADMIN\listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = CLRExtProc)
(ORACLE_HOME = C:\app\oracle\product\19.3.0\dbhome_1)
(PROGRAM = extproc)
(ENVS = "EXTPROC_DLLS=ONLY:C:\app\oracle\product\19.3.0\dbhome_1\bin\oraclr19.dll")
)
(SID_DESC =
(GLOBAL_DBNAME = cdb1)
(ORACLE_HOME = C:\app\oracle\product\19.3.0\dbhome_1)
(SID_NAME = cdb1)
)
(SID_DESC =
(GLOBAL_DBNAME = cdb2)
(ORACLE_HOME = C:\app\oracle\product\19.3.0\dbhome_1)
(SID_NAME = cdb2)
)
(SID_DESC =
(GLOBAL_DBNAME = practice)
(ORACLE_HOME = C:\app\oracle\product\19.3.0\dbhome_1)
(SID_NAME = practice) )
(SID_DESC =
(GLOBAL_DBNAME = copydb)
(ORACLE_HOME = C:\app\oracle\product\19.3.0\dbhome_1)
(SID_NAME = copydb)
)
)
Similarly open tnsnames.ora and add entries as highlighted for your missing instance.
# tnsnames.ora Network Configuration File: C:\app\oracle\product\19.3.0\dbhome_1\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.
LISTENER_CDB2 =
(ADDRESS = (PROTOCOL = TCP)(HOST = DemonoftheFall)(PORT = 1521))
LISTENER_CDB1 =
(ADDRESS = (PROTOCOL = TCP)(HOST = DemonoftheFall)(PORT = 1521))
LISTENER_PRACTICE =
(ADDRESS = (PROTOCOL = TCP)(HOST = DemonoftheFall)(PORT = 1521))
LISTENER_COPYDB =
(ADDRESS = (PROTOCOL = TCP)(HOST = DemonoftheFall)(PORT = 1521))
CDB1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = DemonoftheFall)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = cdb1)
)
)
CDB2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = DemonoftheFall)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = cdb2)
)
)
ORACLR_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
(CONNECT_DATA =
(SID = CLRExtProc)
(PRESENTATION = RO)
)
)
PRACTICE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = DemonoftheFall)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = practice)
)
)
COPYDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = DemonoftheFall)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = copydb)
)
)
Reload listener & connect to your database.
PS C:\Users\parvi> lsnrctl reload LSNRCTL for 64-bit Windows: Version 19.0.0.0.0 - Production on 07-JAN-2025 22:12:01 Copyright (c) 1991, 2019, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=DemonoftheFall)(PORT=1521))) The command completed successfully PS C:\Users\parvi> lsnrctl status LSNRCTL for 64-bit Windows: Version 19.0.0.0.0 - Production on 07-JAN-2025 22:12:07 Copyright (c) 1991, 2019, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=DemonoftheFall)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for 64-bit Windows: Version 19.0.0.0.0 - Production Start Date 07-JAN-2025 22:09:48 Uptime 0 days 0 hr. 2 min. 22 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File C:\app\oracle\product\19.3.0\dbhome_1\network\admin\listener.ora Listener Log File C:\app\oracle\product\19.3.0\dbhome_1\log\diag\tnslsnr\DemonoftheFall\listener\alert\log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=DemonoftheFall)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc))) Services Summary... Service "CLRExtProc" has 1 instance(s). Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service... Service "cdb1" has 1 instance(s). Instance "cdb1", status UNKNOWN, has 1 handler(s) for this service... Service "cdb2" has 1 instance(s). Instance "cdb2", status UNKNOWN, has 1 handler(s) for this service... Service "copydb" has 1 instance(s). Instance "copydb", status UNKNOWN, has 1 handler(s) for this service... Service "practice" has 2 instance(s). Instance "practice", status UNKNOWN, has 1 handler(s) for this service... Instance "practice", status READY, has 1 handler(s) for this service... Service "practiceXDB" has 1 instance(s). Instance "practice", status READY, has 1 handler(s) for this service... The command completed successfully PS C:\Users\parvi> sqlplus sys@copydb as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jan 7 22:12:16 2025 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Enter password: Last Successful login time: Mon Jan 06 2025 22:23:54 +05:30 Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 SQL> select name, open_mode from V$database; NAME OPEN_MODE --------- -------------------- COPYDB READ WRITE
I hope this helps !!
No comments:
Post a Comment