My Blog

September 23, 2008

Processes to solve problem of ora-12514(sometimes comes out as ora-12523)

Filed under: Oracle — by enotebook @ 5:28 pm

Just finished the Oracle 10g RAC installation on linux with using VMware server, everything runs smoothly without testing connection to the instance on first node(rac1). Details like blowing.
$ sqlplus [email=system@devdb1]system@devdb1
SQL*Plus: Release 10.2.0.1.0 – Production on Tue Sep 23 15:14:30 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter password:
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor

Enter user-name:
It raised an error when trying to connect devdb1(instance on first node named rac1), enven i was allowed to connect devdb2 and devdb. crs_stat -t was issued:
$ crs_stat -t
Name Type Target State Host
————————————————————
ora…..crm.cs application ONLINE ONLINE rac1
ora….db1.srv application ONLINE ONLINE rac2
ora.devdb.db application ONLINE ONLINE rac1
ora….b1.inst application ONLINE ONLINE rac1
ora….b2.inst application ONLINE ONLINE rac2
ora….SM1.asm application ONLINE ONLINE rac1
ora….C1.lsnr application ONLINE ONLINE rac1
ora.rac1.gsd application ONLINE ONLINE rac1
ora.rac1.ons application ONLINE ONLINE rac1
ora.rac1.vip application ONLINE ONLINE rac1
ora….SM2.asm application ONLINE ONLINE rac2
ora….C2.lsnr application ONLINE ONLINE rac2
ora.rac2.gsd application ONLINE ONLINE rac2
ora.rac2.ons application ONLINE ONLINE rac2
ora.rac2.vip application ONLINE ONLINE rac2
$
Every resource is running fine. Then try to check the status of listener.

$ lsnrctl status
LSNRCTL for Linux: Version 10.2.0.1.0 – Production on 23-SEP-2008 15:15:30
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
TNS-12541: TNS:no listener
TNS-12560: TNS:protocol adapter error
TNS-00511: No listener
Linux Error: 111: Connection refused
$
Outputs like above showing us the listener does not even ‘trurely started'(started incorrectly). Tried to restart it and re-check the status.

$ crs_stop ora.rac1.LISTENER_RAC1.lsnr
Attempting to stop `ora.rac1.LISTENER_RAC1.lsnr` on member `rac1`
Stop of `ora.rac1.LISTENER_RAC1.lsnr` on member `rac1` succeeded.
$ crs_start ora.rac1.LISTENER_RAC1.lsnr
Attempting to start `ora.rac1.LISTENER_RAC1.lsnr` on member `rac1`
Start of `ora.rac1.LISTENER_RAC1.lsnr` on member `rac1` succeeded.

The outputs are same with above while excuting command of lsnrctl status. Tried to compare the content of listener.ora in rac1 with rac2 and found that HOST string is not same within rac2, HOST = 172.16.113.32 in rac2, but HOST = 127.0.0.1 in rac1, i guess it causes the problem. Modify it and make it sync with rac2.

$ more listener.ora
# listener.ora.rac1 Network Configuration File: /u01/app/oracle/product/10g/db/network/admin/listener.ora.rac1
# Generated by Oracle configuration tools.
LISTENER_RAC1 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip.andy.com)(PORT = 1521)(IP = FIRST))
(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521)(IP = FIRST))
)
)
SID_LIST_LISTENER_RAC1 =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/10g/db)
(PROGRAM = extproc)
)
)
$ vi listener.ora

Restart listener on rac1.
$ crs_stop ora.rac1.LISTENER_RAC1.lsnr
Attempting to stop `ora.rac1.LISTENER_RAC1.lsnr` on member `rac1`
Stop of `ora.rac1.LISTENER_RAC1.lsnr` on member `rac1` succeeded.
$ crs_start ora.rac1.LISTENER_RAC1.lsnr
Attempting to start `ora.rac1.LISTENER_RAC1.lsnr` on member `rac1`
Start of `ora.rac1.LISTENER_RAC1.lsnr` on member `rac1` succeeded.

Check the status.
$ lsnrctl status
LSNRCTL for Linux: Version 10.2.0.1.0 – Production on 23-SEP-2008 15:18:07
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
————————
Alias LISTENER_RAC1
Version TNSLSNR for Linux: Version 10.2.0.1.0 – Production
Start Date 23-SEP-2008 15:18:01
Uptime 0 days 0 hr. 0 min. 5 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/10g/db/network/admin/listener.ora
Listener Log File /u01/app/oracle/product/10g/db/network/log/listener_rac1.log
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.16.113.131)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.16.113.31)(PORT=1521)))
Services Summary…
Service “PLSExtProc” has 1 instance(s).
Instance “PLSExtProc”, status UNKNOWN, has 1 handler(s) for this service…
The command completed successfully
$

Then connect to devdb1.
$ sqlplus [email=system@devdb1]system@devdb1
SQL*Plus: Release 10.2.0.1.0 – Production on Tue Sep 23 15:29:19 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
SQL>

It works!
Done.

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: