corrupted indexes

Aman....

misterimran wrote:
Dear all,

My Oracle server was shutdown due to long power failure. Later on when the electricity came i restarted the server database was open. I could connect.
When i start the listener it does start but gives a message "The listener support no service"

You need to wait for some time to let PMON connect with the listener. If you are not willing to wait, issue this command sitting inside the db,

sql> alter system register

This would register your db with the listener immediately,

HTH
Aman....

sb92075

No Operating System name or version
No Oracle version number (from v$version).
No error message (not your interpretation of the actual full and complete message).
No help is possible at this time.

misterimran

I was sure it would work but it didn't it still says "The listener support no service"
And application connecting says "TNS No Listener"

regards, Imran

misterimran

Sorry, Operating system is Linux. Oracle Database is 11gR1

Imran

Aman....

Show us that the listener is up using

Lsnrctl status

Also show the status using

lsnrctl status

What's there in the listener.ora ?

Aman....

misterimran

Listener Status

STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.1.0.6.0 - Production
Start Date 07-JUL-2009 09:45:14
Uptime 0 days 0 hr. 3 min. 58 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /Oracle/app/oracle/product/11.1.0/db_1/network/admin/listener.ora
Listener Log File /Oracle/app/oracle/product/11.1.0/db_1/log/diag/tnslsnr/bss-ora-dev/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully

This is in listener.ora

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)

hostname = bss-ora-dev

Regards, Imran

sb92075

Similarly the applications connecting database like Raptor they say " The network adapter could not establish the connection"

What is Raptor?
It would be nice if you posted actual Oracle (ORA-* or TNS-*) error message
Does Raptor reside on DB server system or remote system?

Was all OK prior to power failure?

Post last 10 - 20 lines from listener.log file on DB server

misterimran

Everything was OK be4 the power failure.

Raptor reside on another system (Raptor just says The network adaptor could not establish the connection, no error code.)

Application connecting to database, are giving error "ORA-12541:TNS no listener"

These are the lines from listener.log

Tue Jul 07 08:56:29 2009
Create Relation ADR_CONTROL
Create Relation INC_METER_IMPT_DEF
Create Relation INC_METER_PK_IMPTS
System parameter file is /Oracle/app/oracle/product/11.1.0/db_1/network/admin/listener.ora
Log messages written to /Oracle/app/oracle/product/11.1.0/db_1/log/diag/tnslsnr/bss-ora-dev/listener/alert/log.xml
Trace information written to /Oracle/app/oracle/product/11.1.0/db_1/log/diag/tnslsnr/bss-ora-dev/listener/trace/ora_2774_4115060960.trc
Trace level is currently 0

Started with pid=2774
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Listener completed notification to CRS on start

TIMESTAMP * CONNECT DATA [* PROTOCOL INFO] * EVENT [* SID] * RETURN CODE
WARNING: Subscription for node down event still pending
07-JUL-2009 08:56:30 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=bss-ora-dev)(USER=oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=185599488)) * status * 0
Tue Jul 07 09:05:53 2009
07-JUL-2009 09:05:53 * ping * 0
07-JUL-2009 09:05:55 * ping * 0
Tue Jul 07 09:07:37 2009
07-JUL-2009 09:07:37 * ping * 0
Tue Jul 07 09:11:25 2009
No longer listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
No longer listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Listener completed notification to CRS on stop
07-JUL-2009 09:11:25 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=bss-ora-dev)(USER=oracle))(COMMAND=stop)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=185599488)) * stop * 0
Tue Jul 07 09:11:27 2009
System parameter file is /Oracle/app/oracle/product/11.1.0/db_1/network/admin/listener.ora
Log messages written to /Oracle/app/oracle/product/11.1.0/db_1/log/diag/tnslsnr/bss-ora-dev/listener/alert/log.xml
Trace information written to /Oracle/app/oracle/product/11.1.0/db_1/log/diag/tnslsnr/bss-ora-dev/listener/trace/ora_3823_4115060960.trc
Trace level is currently 0

Started with pid=3823
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Listener completed notification to CRS on start

TIMESTAMP * CONNECT DATA [* PROTOCOL INFO] * EVENT [* SID] * RETURN CODE
WARNING: Subscription for node down event still pending
07-JUL-2009 09:11:27 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=bss-ora-dev)(USER=oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=185599488)) * status * 0
Tue Jul 07 09:11:54 2009
07-JUL-2009 09:11:54 * ping * 0
Tue Jul 07 09:16:22 2009
WARNING: Subscription for node down event still pending
07-JUL-2009 09:16:22 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=bss-ora-dev)(USER=oracle))(COMMAND=status)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=185599488)) * status * 0
Tue Jul 07 09:26:07 2009
No longer listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
No longer listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Listener completed notification to CRS on stop
07-JUL-2009 09:26:07 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=bss-ora-dev)(USER=oracle))(COMMAND=stop)(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=185599488)) * stop * 0
Tue Jul 07 09:26:15 2009
System parameter file is /Oracle/app/oracle/product/11.1.0/db_1/network/admin/listener.ora
Log messages written to /Oracle/app/oracle/product/11.1.0/db_1/log/diag/tnslsnr/bss-ora-dev/listener/alert/log.xml
Trace information written to /Oracle/app/oracle/product/11.1.0/db_1/log/diag/tnslsnr/bss-ora-dev/listener/trace/ora_4972_4115060960.trc
Trace level is currently 0

Regards, Imran

sb92075

Post contents of tnsnames.ora from system with Raptor.

misterimran

hostname=192.168.0.200
port=1521
sid=bssdevpl

Same settings were working before.

sb92075

hostname=192.168.0.200
port=1521
sid=bssdevpl

Same settings were working before

So you say.

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))

Since listener is listening on 127.0.0.1, it won't acknowledge request to 192.168.0.200

lnsrctl stop
rename listener.ora on DB server
lsnrctl start

now try again from client

misterimran

When i change the IP in listener.ora to 192.168.0.200 and rename the file to listener1.ora and start listener on db server it says ...

TNSLSNR for Linux: Version 11.1.0.6.0 - Production
Log messages written to /Oracle/app/oracle/product/11.1.0/db_1/log/diag/tnslsnr/bss-ora-dev/listener/alert/log.xml
Error listening on: (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
TNS-12545: Connect failed because target host or object does not exist
TNS-12560: TNS:protocol adapter error
TNS-00515: Connect failed because target host or object does not exist
Listener failed to start. See the error message(s) above...

On clients the error message is:

ORA-12541: TNS:no listener

However when i rename the file again from listener1.ora to listener.ora IP is still 192.168.0.200
On DB server the error is the same The listener supports no service

and on Clients the error changes to

[ORA-12514: TNS:listener could not resolve SERVICE_NAME given in connect descriptor

I hope it gives you an idea what is happening.

Thanks, Imran

Aman....

No its just making things more comlex. Post exactly what your listener and tnsnames.ora look files look alike over here using { code } tag( without space) .

You are doing some serious mishap with your files, why the host is blank here,

Error listening on: (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

HTH
Aman....

misterimran

Ok here these are the contents of listener.ora

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = bss-ora-dev.beaconhouse.edu.pk)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)

These are the contents of tnsnames.ora

BSSDEVPL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = bss-ora-dev.beaconhouse.edu.pk)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = bssdevpl.beaconhouse.edu.pk)
)
)

hostname commands returns bss-ora-dev

when i check environment variables ORACLE_SID and ORACLE_HOME are set.

The problem is when i start listener on Server... It says Listener support no service

This error is not letting clients connects the database error on clients is "ORA-12514: TNS:listener could not resolve SERVICE_NAME given in connect descriptor ]"

Hope this clears the situation.

Thanks, Imran

Aman....

Yes its much better now. So now, did you try configuring the database for static registration with the listener if the automatic is not working?

Use net manager to configure the listener with your db, restart the listener and than post the output.

HTH
Aman....

389403

Dear friend, it is very simple,
but I don't understand how it was work befor Your power off???

Juast put to Your listener.ora information about services

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = <DBMANE>.<DBDOMAIN>)
(SID_NAME = <SID>)
(ORACLE_HOME = <home dir>)
)

enjoy

Good Luck!

misterimran

I have change the bss-ora-dev.beaconhouse.edu.pk to IP and problem is still the same.

Have register the database. Tried to re-create the service but no success.

What exactly should I do to add service to listener?

Kindly help

misterimran

This is listener log.xml

<msg time='2009-07-07T08:56:29.630+05:00' org_id='oracle' comp_id='tnslsnr'
msg_id='dbgrmmdcrf_create_relation_full:1695:1503427940' type='NOTIFICATION' group='destroy'
level='16' host_id='bss-ora-dev' host_addr='UNKNOWN'
version='1'>
<txt>Create Relation ADR_CONTROL
</txt>
</msg>

Aman....

What exactly should I do to add service to listener?

Didn't I just say, fire up Net manager and just follow the steps over there.

HTH
Aman....

389403

Add this to Your listener.ora file

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = <DBMANE>.<DBDOMAIN>)
(SID_NAME = <SID>)
(ORACLE_HOME = <home dir>)
)

Did you do that? If You add it to listener.ora Your problem will be solved!

misterimran

Dear Aychin,

This is what i get when i start litener after adding these lines

LSNRCTL> start
Starting /Oracle/app/oracle/product/11.1.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.1.0.6.0 - Production
System parameter file is /Oracle/app/oracle/product/11.1.0/db_1/network/admin/listener.ora
Log messages written to /Oracle/app/oracle/product/11.1.0/db_1/log/diag/tnslsnr/bss-ora-dev/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.200)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
TNS-01155: Incorrectly specified SID_LIST_LISTENER parameter in LISTENER.ORA
NL-00303: syntax error in NV string

Listener failed to start. See the error message(s) above...

I have re-configured, delete and then re-create listener.

Problem remains the same :(

389403

Copy paste here all contents of Your listener.ora file with newly added lines.

misterimran

select global_name from global_name returns

BSSDEVPL

Contents of listener.ora after adding lines is:

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.200)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = BSSDEVPL)
(SID_NAME = BSSDEVPL)
(ORACLE_HOME = /Oracle/app/oracle/product/11.1.0/db_1)
)

389403

Sorry there must be one more round bracket at the end

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = BSSDEVPL)
(SID_NAME = BSSDEVPL)
(ORACLE_HOME = /Oracle/app/oracle/product/11.1.0/db_1)
))

Try this, update, then reload listener, must work now.

misterimran

Its the same

LSNRCTL> start
Starting /Oracle/app/oracle/product/11.1.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.1.0.6.0 - Production
System parameter file is /Oracle/app/oracle/product/11.1.0/db_1/network/admin/listener.ora
Log messages written to /Oracle/app/oracle/product/11.1.0/db_1/log/diag/tnslsnr/bss-ora-dev/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.200)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
TNS-01155: Incorrectly specified SID_LIST_LISTENER parameter in LISTENER.ORA
NL-00303: syntax error in NV string

Listener failed to start. See the error message(s) above...

These are the file contents

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.200)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = BSSDEVPL)
(SID_NAME = BSSDEVPL)
(ORACLE_HOME = /Oracle/app/oracle/product/11.1.0/db_1)
))

389403

OK,

If You will not use external procedures then remove the line:

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

And it will work,

If You will use it then add new SID_DESC for extproc:

SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=BSSDEVPL)
(ORACLE_HOME=/Oracle/app/oracle/product/11.1.0/db_1)
(SID_NAME=BSSDEVPL))
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /Oracle/app/oracle/product/11.1.0/db_1)
(PROGRAM = extproc))
)

Gooood Luck

misterimran

Sorry to disturb you again n again but this is what listener start says:

LSNRCTL> start
Starting /Oracle/app/oracle/product/11.1.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.1.0.6.0 - Production
System parameter file is /Oracle/app/oracle/product/11.1.0/db_1/network/admin/listener.ora
Log messages written to /Oracle/app/oracle/product/11.1.0/db_1/log/diag/tnslsnr/bss-ora-dev/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.200)(PORT=1521)))
TNS-01155: Incorrectly specified SID_LIST_LISTENER parameter in LISTENER.ORA
NL-00303: syntax error in NV string

Listener failed to start. See the error message(s) above...

This is listener.ora file:

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.200)(PORT = 1521))
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = BSSDEVPL)
(SID_NAME = BSSDEVPL)
(ORACLE_HOME = /Oracle/app/oracle/product/11.1.0/db_1)
))

389403

Ok, it is something interesting, only one thing that I can suggest You now is to:
lsnrctl stop
rm listener.ora
vi listener.ora

insert here

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.200)(PORT = 1521))
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = BSSDEVPL)
(SID_NAME = BSSDEVPL)
(ORACLE_HOME = /Oracle/app/oracle/product/11.1.0/db_1)
)
)

save the file

lsnrctl start

P.S.: may your file is corrupt that is why we recreate this

Aman....

Misterimran,

I guess this is the third time I am saying, why not to use the Net Manager tool and configure the listener from it? What's the need to muck around with the listener.ora directly when you can always safely modify it with the tool? You are doing mistakes while editing it manually and the best way to stop it is using the tool IMHO.
>
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = /u01/app/oracle/product/11.1.0/db_1)
(SID_NAME = ORCL)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = XXXXXXXXX)(PORT = 1521))
)
)
>

I have attached a listener.ora that I have created on my machine using net manager, try to put the enteries into it , restart the listener and see if it works for you or not.
HTH
Aman....

Edited by: Aman.... on Jul 7, 2009 3:37 PM
added listener entry

misterimran

Oh My GOD!

I think this have worked because when i start listener it says:

LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.1.0.6.0 - Production
Start Date 07-JUL-2009 16:19:51
Uptime 0 days 0 hr. 3 min. 52 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /Oracle/app/oracle/product/11.1.0/db_1/network/admin/listener.ora
Listener Log File /Oracle/app/oracle/product/11.1.0/db_1/log/diag/tnslsnr/bss-ora-dev/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.200)(PORT=1521)))
Services Summary...
Service "BSSDEVPL" has 1 instance(s).
Instance "BSSDEVPL", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

There is some problems with BSSDEVPL ... though this is the sid, database name and also the global_name....

Any idea why this error have errased now.

Clients connecting this database server, now says: ORA-01034: ORACLE not available ORA-27101: shared memory realm does not exist

I think we are very close to the solution.

Thanks

misterimran

For convinience this is in tnsnames.ora

BSSDEVPL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.200)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = bssdevpl)
)
)

389403

Did You connect from remote or same machine?

misterimran

From remote server.

Bit its working now :) I restarted listner error was the same but it got connected.

Thank you very much Aychin and Amman. I am thankful to you.

Regards, Imran

663459

You can look at the post [Listener Supports no service|http://arjudba.blogspot.com/2009/02/listener-supports-no-services.html]
It solved my problem.

Aman....

My name is Aman dude :) .

Get me a Devil'sO Own coffee whenever you are going to see me :) .

Close the thread,if its done.

Aman....

Oracle Database Discussions

corrupted indexes

Comments

Post Details