1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

>startup open; throws ORA-12514 after upgrd to 10g

Discussion in 'SQL PL/SQL' started by Popeye.Tom, Jan 12, 2011.

  1. Popeye.Tom

    Popeye.Tom Active Member

    Messages:
    27
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    Left Hand Coast, USA
    Problem resolved!

    Happy New Year to all!

    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
     
  2. Popeye.Tom

    Popeye.Tom Active Member

    Messages:
    27
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    Left Hand Coast, USA
    This is being run on the server, not from a client. ORACLE_HOME and ORACLE_SID variables are set and report proper values.

    Tom
     
  3. Popeye.Tom

    Popeye.Tom Active Member

    Messages:
    27
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    Left Hand Coast, USA
    Resolved: >startup open; throws ORA-12514 after upgrd to 10g

    Resolved. Instead of logging into sql*plus as "system/manager@wind as sysdba" drop the @wind
     
    Sadik likes this.
  4. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    Re: Resolved: >startup open; throws ORA-12514 after upgrd to 10g

    Thanks for sharing the solution for the benefit of others.
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Re: Resolved: >startup open; throws ORA-12514 after upgrd to 10g

    This is because you do not have 'wind' configured in the 10g tnsnames.ora file on the server, while you did have it configured in the 9i tnsnames.ora.


    Configure the db entry in the 10g tnsnames.ora and you should then be able to connect with or without the '@wind' string.