Hi All , This blogging only for sharing knowledge. As what troubleshooting i have faced and solution i have found i m sharing the same.
11g
11g Database : TESTUPGR
Listener Name : listener11g
Service name : testupgr
Port:1522
C:\app\varunyadav\product\11.2.0\dbhome_1\BIN>set ORACLE_SID=testupgr
C:\app\varunyadav\product\11.2.0\dbhome_1\BIN>set ORACLE_HOME=C:\app\varunyadav\product\11.2.0\dbhome_1
12c
12c Database : TEST
Listener Name : listener 12c
Service Name : test
Port : 1521
C:\app\varunyadav\product\12.1.0\dbhome_1\BIN>set ORACLE_SID=test
C:\app\varunyadav\product\12.1.0\dbhome_1\BIN>set ORACLE_HOME=C:\app\varunyadav\product\12.1.0\dbhome_1
I have already installed oracle 12c stand alone database on window machine. listener already created. and able to connect db remotely as well.
Actually i was upgrading 11.2.0.1.0 to 11.2.0.4.0 . 11g software and database created successfully. But during listener configuration. services of 11g database not showing when typing command.
lsnrctl status listener11g . Instead of showing, 11g services were showing or pointing towards on 12c listener.
By default PMON process pointing towards 1521 port so 11g services showing on 12c listener.
As we have 1522 port on 11g, We have to set local_listener on 11g parameter and register the database using sql prompt.
After setting the paramater value 11g services showing on lsnrctl status listener11g and 12c listener status earlier showing 11g services got disabled.
Please find troubleshooting steps below :
#################################################################################
C:\Windows\system32>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Thu Jan 10 13:30:09 2019
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options.
#################################################################################
C:\Windows\system32>sqlplus sys/system123@testupgr as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Thu Jan 10 13:30:39 2019
Copyright (c) 1982, 2010, Oracle. All rights reserved.
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor
C:\Windows\system32>
#################################################################################
C:\Windows\system32>lsnrctl status listener11g
LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 10-JAN-2019 13:30:22
Copyright (c) 1991, 2010, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=VARUNY)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias LISTENER11G
Version TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Production
Start Date 10-JAN-2019 13:30:02
Uptime 0 days 0 hr. 0 min. 19 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File C:\app\varunyadav\product\11.2.0\dbhome_1\network\admin\listener.ora
Listener Log File c:\app\varunyadav\diag\tnslsnr\VARUNY\listener11g\alert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=VARUNY)(PORT=1522)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1522ipc)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
#################################################################
C:\app\varunyadav\product\12.1.0\dbhome_1\BIN>lsnrctl status listener
LSNRCTL for 64-bit Windows: Version 12.1.0.2.0 - Production on 10-JAN-2019 19:03:15
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=VARUNY)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 64-bit Windows: Version 12.1.0.2.0 - Production
Start Date 10-JAN-2019 19:01:06
Uptime 0 days 0 hr. 2 min. 9 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File C:\app\varunyadav\product\12.1.0\dbhome_1\network\admin\listener.ora
Listener Log File C:\app\varunyadav\diag\tnslsnr\VARUNY\listener\alert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=2762VARUNY1198L.mind.motherson.com)(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 "pdb1" has 1 instance(s).
Instance "test", status READY, has 1 handler(s) for this service...
Service "pdb2" has 1 instance(s).
Instance "test", status READY, has 1 handler(s) for this service...
Service "test" has 1 instance(s).
Instance "test", status READY, has 1 handler(s) for this service...
Service "testXDB" has 1 instance(s).
Instance "test", status READY, has 1 handler(s) for this service...
Service "testupgr" has 1 instance(s).
Instance "testupgr", status READY, has 1 handler(s) for this service...
Service "testupgrXDB" has 1 instance(s).
Instance "testupgr", status READY, has 1 handler(s) for this service...
The command completed successfully
################################################################################
C:\app\varunyadav\product\11.2.0\dbhome_1\BIN>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Thu Jan 10 19:06:06 2019
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> sho parameter listener
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
listener_networks string
local_listener string
remote_listener string
SQL> alter system set local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=VARUNY)(PORT=1522))' scope=both;
System altered.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
###############################################################################
C:\app\varunyadav\product\11.2.0\dbhome_1\BIN>lsnrctl stop listener11g
LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 10-JAN-2019 19:07:41
Copyright (c) 1991, 2010, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=VARUNY1198L)(PORT=1522)))
The command completed successfully
C:\app\varunyadav\product\11.2.0\dbhome_1\BIN>lsnrctl start listener11g
LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 10-JAN-2019 19:07:45
Copyright (c) 1991, 2010, Oracle. All rights reserved.
Starting tnslsnr: please wait...
TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Production
System parameter file is C:\app\varunyadav\product\11.2.0\dbhome_1\network\admin\listener.ora
Log messages written to c:\app\varunyadav\diag\tnslsnr\VARUNY\listener11g\alert\log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=VARUNY)(PORT=1522)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1522ipc)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=VARUNY)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias listener11g
Version TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Production
Start Date 10-JAN-2019 19:07:48
Uptime 0 days 0 hr. 0 min. 3 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File C:\app\varunyadav\product\11.2.0\dbhome_1\network\admin\listener.ora
Listener Log File c:\app\varunyadav\diag\tnslsnr\VARUNY\listener11g\alert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=2762VARUNY1198L.mind.motherson.com)(PORT=1522)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1522ipc)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
##########################################################################
C:\app\varunyadav\product\11.2.0\dbhome_1\BIN>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Thu Jan 10 19:07:54 2019
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> alter system register;
System altered.
SQL> sho parameter listener
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
listener_networks string
local_listener string (ADDRESS=(PROTOCOL=TCP)(HOST=2
762VARUNY1198L)(PORT=1522))
remote_listener string
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
11g
11g Database : TESTUPGR
Listener Name : listener11g
Service name : testupgr
Port:1522
C:\app\varunyadav\product\11.2.0\dbhome_1\BIN>set ORACLE_SID=testupgr
C:\app\varunyadav\product\11.2.0\dbhome_1\BIN>set ORACLE_HOME=C:\app\varunyadav\product\11.2.0\dbhome_1
12c
12c Database : TEST
Listener Name : listener 12c
Service Name : test
Port : 1521
C:\app\varunyadav\product\12.1.0\dbhome_1\BIN>set ORACLE_SID=test
C:\app\varunyadav\product\12.1.0\dbhome_1\BIN>set ORACLE_HOME=C:\app\varunyadav\product\12.1.0\dbhome_1
I have already installed oracle 12c stand alone database on window machine. listener already created. and able to connect db remotely as well.
Actually i was upgrading 11.2.0.1.0 to 11.2.0.4.0 . 11g software and database created successfully. But during listener configuration. services of 11g database not showing when typing command.
lsnrctl status listener11g . Instead of showing, 11g services were showing or pointing towards on 12c listener.
By default PMON process pointing towards 1521 port so 11g services showing on 12c listener.
As we have 1522 port on 11g, We have to set local_listener on 11g parameter and register the database using sql prompt.
After setting the paramater value 11g services showing on lsnrctl status listener11g and 12c listener status earlier showing 11g services got disabled.
Please find troubleshooting steps below :
#################################################################################
- I have made local connection to 11g database and able to connect remotely.
C:\Windows\system32>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Thu Jan 10 13:30:09 2019
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options.
#################################################################################
- While connecting using service got error below.
C:\Windows\system32>sqlplus sys/system123@testupgr as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Thu Jan 10 13:30:39 2019
Copyright (c) 1982, 2010, Oracle. All rights reserved.
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor
C:\Windows\system32>
#################################################################################
- While checking listener service on 11g not showing services.
C:\Windows\system32>lsnrctl status listener11g
LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 10-JAN-2019 13:30:22
Copyright (c) 1991, 2010, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=VARUNY)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias LISTENER11G
Version TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Production
Start Date 10-JAN-2019 13:30:02
Uptime 0 days 0 hr. 0 min. 19 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File C:\app\varunyadav\product\11.2.0\dbhome_1\network\admin\listener.ora
Listener Log File c:\app\varunyadav\diag\tnslsnr\VARUNY\listener11g\alert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=VARUNY)(PORT=1522)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1522ipc)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
#################################################################
- On 12c it is showing 11g services , As by default PMON service point towards 1521 port.
C:\app\varunyadav\product\12.1.0\dbhome_1\BIN>lsnrctl status listener
LSNRCTL for 64-bit Windows: Version 12.1.0.2.0 - Production on 10-JAN-2019 19:03:15
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=VARUNY)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 64-bit Windows: Version 12.1.0.2.0 - Production
Start Date 10-JAN-2019 19:01:06
Uptime 0 days 0 hr. 2 min. 9 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File C:\app\varunyadav\product\12.1.0\dbhome_1\network\admin\listener.ora
Listener Log File C:\app\varunyadav\diag\tnslsnr\VARUNY\listener\alert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=2762VARUNY1198L.mind.motherson.com)(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 "pdb1" has 1 instance(s).
Instance "test", status READY, has 1 handler(s) for this service...
Service "pdb2" has 1 instance(s).
Instance "test", status READY, has 1 handler(s) for this service...
Service "test" has 1 instance(s).
Instance "test", status READY, has 1 handler(s) for this service...
Service "testXDB" has 1 instance(s).
Instance "test", status READY, has 1 handler(s) for this service...
Service "testupgr" has 1 instance(s).
Instance "testupgr", status READY, has 1 handler(s) for this service...
Service "testupgrXDB" has 1 instance(s).
Instance "testupgr", status READY, has 1 handler(s) for this service...
The command completed successfully
################################################################################
- We need to set local listener on 11g and register database later.
C:\app\varunyadav\product\11.2.0\dbhome_1\BIN>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Thu Jan 10 19:06:06 2019
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> sho parameter listener
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
listener_networks string
local_listener string
remote_listener string
SQL> alter system set local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=VARUNY)(PORT=1522))' scope=both;
System altered.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
###############################################################################
- Stop and start the listener on 11g
C:\app\varunyadav\product\11.2.0\dbhome_1\BIN>lsnrctl stop listener11g
LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 10-JAN-2019 19:07:41
Copyright (c) 1991, 2010, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=VARUNY1198L)(PORT=1522)))
The command completed successfully
C:\app\varunyadav\product\11.2.0\dbhome_1\BIN>lsnrctl start listener11g
LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 10-JAN-2019 19:07:45
Copyright (c) 1991, 2010, Oracle. All rights reserved.
Starting tnslsnr: please wait...
TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Production
System parameter file is C:\app\varunyadav\product\11.2.0\dbhome_1\network\admin\listener.ora
Log messages written to c:\app\varunyadav\diag\tnslsnr\VARUNY\listener11g\alert\log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=VARUNY)(PORT=1522)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1522ipc)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=VARUNY)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias listener11g
Version TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Production
Start Date 10-JAN-2019 19:07:48
Uptime 0 days 0 hr. 0 min. 3 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File C:\app\varunyadav\product\11.2.0\dbhome_1\network\admin\listener.ora
Listener Log File c:\app\varunyadav\diag\tnslsnr\VARUNY\listener11g\alert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=2762VARUNY1198L.mind.motherson.com)(PORT=1522)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1522ipc)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
##########################################################################
- As you can set above still services are not showing up , So we have to register database as well.
C:\app\varunyadav\product\11.2.0\dbhome_1\BIN>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Thu Jan 10 19:07:54 2019
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> alter system register;
System altered.
SQL> sho parameter listener
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
listener_networks string
local_listener string (ADDRESS=(PROTOCOL=TCP)(HOST=2
762VARUNY1198L)(PORT=1522))
remote_listener string
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
###############################################################################
- Check the listener services
C:\app\varunyadav\product\11.2.0\dbhome_1\BIN>lsnrctl status listener11g
LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 10-JAN-2019 19:08:34
Copyright (c) 1991, 2010, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=VARUNY)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias listener11g
Version TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Production
Start Date 10-JAN-2019 19:07:48
Uptime 0 days 0 hr. 0 min. 48 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File C:\app\varunyadav\product\11.2.0\dbhome_1\network\admin\listener.ora
Listener Log File c:\app\varunyadav\diag\tnslsnr\VARUNY\listener11g\alert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=2762VARUNY1198L.mind.motherson.com)(PORT=1522)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1522ipc)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "testupgr" has 1 instance(s).
Instance "testupgr", status READY, has 1 handler(s) for this service...
Service "testupgrXDB" has 1 instance(s).
Instance "testupgr", status READY, has 1 handler(s) for this service...
The command completed successfully
C:\app\varunyadav\product\11.2.0\dbhome_1\BIN>
#################################################################################
- Able to connect 11g database service remotely using service entry
C:\app\varunyadav\product\11.2.0\dbhome_1\BIN>sqlplus sys/system123@testupgr as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Thu Jan 10 19:08:13 2019
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
################### Thank you ####################################################
No comments:
Post a Comment