OraFAQ Forum: Server Administration » cannot startup database from sqlplus client

Home » RDBMS Server » Server Administration » cannot startup database from sqlplus client (unix, oracle 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

cannot startup database from sqlplus client [message #296567]

Mon, 28 January 2008 05:03

prajjwal
Messages: 28
Registered: January 2008
Location: kolkata

Junior Member

Sir,

I logged in into sqlplus client as sysdba. (usernm/pwd@service as sysdba)

On shutdown and startup I get the error ORA-12514. Why so?

just see the following

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

....by the way, I can shutdown and startup from the server it gives no error.

After a faulty shutdown from sqlplus client, I can see that the instance is actually not properly shutdown. The database is properly closed and dismounted. From the server when I log in with ( / as sysdba)

SQL> shutdown immediate
ORA-01507: database not mounted

ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 264241152 bytes
Fixed Size 1261224 bytes
Variable Size 218104152 bytes
Database Buffers 37748736 bytes
Redo Buffers 7127040 bytes
Database mounted.
Database opened.

....why this difference (between client login and server login)??

Report message to a moderator

Re: cannot startup database from sqlplus client [message #296584 is a reply to message #296567]

Mon, 28 January 2008 05:56

rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India

Senior Member

Quote:

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
Cause: The listener received a request to establish a connection to a database or other service. The connect descriptor received by the listener specified a service name for a service (usually a database service) that either has not yet dynamically registered with the listener or has not been statically configured for the listener. This may be a temporary condition such as after the listener has started, but before the database instance has registered with the listener.
Action: - Wait a moment and try to connect a second time.

- Check which services are currently known by the listener by executing: lsnrctl services <listener name>

- Check that the SERVICE_NAME parameter in the connect descriptor of the net service name used specifies a service known by the listener.

- If an easy connect naming connect identifier was used, check that the service name specified is a service known by the listener.

- Check for an event in the listener.log file.

Rajuvan.

Report message to a moderator

Re: cannot startup database from sqlplus client [message #296590 is a reply to message #296567]

Mon, 28 January 2008 06:15

varu123
Messages: 754
Registered: October 2007

Senior Member

the client needs address of the server.
That address is specified in the tnsnames.ora file of the client.

In the same way,the srever needs to know what clients are giving requests.
So the client information is stored in listener.ora file of the srever.

In your case,you need to modify your listner.ora file on the server with the information of the client.

Report message to a moderator

Re: cannot startup database from sqlplus client [message #296597 is a reply to message #296567]

Mon, 28 January 2008 06:33

prajjwal
Messages: 28
Registered: January 2008
Location: kolkata

Junior Member

Thanks.. but the mystery is not yet solved Sad

I can log in into the instance kol_test and work with the database cleanly using the oracle client.. it means the listener is up, no doubt!!.. when I use

UNIX_PROMPT> lsnrctl services LISTENER

I get as one of the entries,

Service "kol_test" has 1 instance(s).
Instance "kol_test", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:1 refused:0 state:ready
LOCAL SERVER

My tnsname.ora has the entry

kt =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = xx.xxx.xxx.xx)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = kol_test)
)
)

So, why is the problem only for shutdown and startup commands??

Report message to a moderator

Re: cannot startup database from sqlplus client [message #296607 is a reply to message #296597]

Mon, 28 January 2008 06:43

Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE

Senior Member

OP,
It is bug in 10g.
it is becuase of dynamic registration of listener.
it is only happen when we use "tns entry" with "sys as sysdba" to connect to database.

you can avoid to change listener registration type from dynamic to static.
like below
http://dbataj.blogspot.com/2007/02/ora-12514.html

And it is not with client side even if we connect on Server with "tns entry" with sysdba we also getting same error message during "startup".

Regards
Taj

Report message to a moderator

Re: cannot startup database from sqlplus client [message #296734 is a reply to message #296567]

Mon, 28 January 2008 21:54

Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...

Senior Member

Yes , in dbstart script listener location is hard coded. I raised this issue a few months ago.

Report message to a moderator

Re: cannot startup database from sqlplus client [message #296811 is a reply to message #296567]

Tue, 29 January 2008 02:35

prajjwal
Messages: 28
Registered: January 2008
Location: kolkata

Junior Member

thanks to all of you..

special thanks to Mohammad Taj... your blog really helped!

Report message to a moderator

Previous Topic:	Metrics "Database Time Spent Waiting (%)" is at 30.09256 for event class "Commit"
Next Topic:	Metrics "Database Time Spent Waiting (%)" is at 49.71505 for event class "Network&quo

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Sep 17 20:02:02 CDT 2024