Home » RDBMS Server » Server Administration » >startup open after shutdown throws ORA-12514 error (Oracle database, 10.2.0.5.0, Wind. Serv 2003 32-bit)
icon5.gif  >startup open after shutdown throws ORA-12514 error [message #488996] Wed, 12 January 2011 15:35 Go to next message
PopeyeTom
Messages: 3
Registered: January 2011
Location: Left Hand Coast, USA
Junior Member
Hi all,

I searched the knowledge base here and in other forums and haven't found the answer. There was a similar condition presented earlier, but it turned out the resolution was to run the >shutdown/>startup on the server rather than from a client. Not applicable in my case!

Shutdown and startup called used to work with 9i, now startup fails in 10g. ORACLE_HOME and ORACLE_SID are set and report correct values. What is different in 10g that the script (shown below) now throws a TNS listener error after shutdown? All help is appreciated...sorry about the length Embarassed

I successfully upgraded from 9i to 10g (10.2.0.5.0) on Windows Server 2003, 32-bit last weekend. Now our backup_script.bat and oracle_stop_start.sql throw "ORA-12514: TNS:listener does not currently know of service requested in the connect descriptor"

This batch file and sql script predate me and worked with 9i for years. Now the startup command in the oracle_stop_start throws this error after executing shutdown. The backup batch file calls:

sqlplus "system/manager@wind as sysdba" @D:\ptc\windchill\backups\oracle_stop_start

The entire oracle_stop_start.sql is as follows:

shutdown immediate;
startup open;
quit

What is different in 10g that this now doesn't work? Shutdown works, but it will not startup due to the listener error. I have made the backups work by commenting out the call to sqlplus and I'm getting good backup dumps.

In my virtual image of the server I have found that I can duplicate the error after >shutdown. I have found that if I exit sql*plus and go back in as follows I can get the database instance to restart from the command line:

c:>sqlplus /nolog
sql>connect sys/* as sysdba
sql>startup open wind;

That does start, mount and open the database instance.

Is there a better way to be making sure that everything is stopped before executing the >exp command to create a backup dump? I will look into and test datapump export/import and RMAN for creating the backups, after I get this working again.

Any thoughts on why this worked in sql*plus in 9i but not in 10g? Can I make TNS Listener smarter? Change or add variables to the startup command?

Thank you for all help and suggestions!

Tom

Report message to a moderator

Re: >startup open after shutdown throws ORA-12514 error [message #488997 is a reply to message #488996] Wed, 12 January 2011 15:48 Go to previous messageGo to next message
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
Senior Member
The difference is that your 10g listener is not configured with a static registration for your instance. So, either add a section SID_LIST_LISTENER to your listener.ora file (look at your 9i listener.ora file for an example) or - and probably a better solution - run your script like this:

sqlplus "system/manager as sysdba" @D:\ptc\windchill\backups\oracle_stop_start

The @wind bit attempts to use a listener that knows nothing about the instance because it is shut down and therefore dynamically de-registered.

Report message to a moderator

Re: >startup open after shutdown throws ORA-12514 error [message #489006 is a reply to message #488996] Wed, 12 January 2011 17:48 Go to previous message
PopeyeTom
Messages: 3
Registered: January 2011
Location: Left Hand Coast, USA
Junior Member
Thank you John, you are exactly correct on the modified syntax & dropping the @wind. I have tested on the virtual and edited the backup_script.bat. I really appreciate the time you took to reply.

The Johns take the prize here and at forums.oracle.com where another John came up with the same solution!

Have a great evening,
Tom

Report message to a moderator

Previous Topic: installation issue
Next Topic: UTL_FILE_DIR in 10g
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sun Dec 01 13:21:15 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.