Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2002
    Posts
    1,245

    Unanswered: Unable to connect using sqlplus

    First off, I apologize. I have not thoroughly researched my next question, but it's late, I'm tired and frustrated again.

    Background: I am trying to implement hot backups for an Oracle 8.1.6 database running on a Windows 2000 server (SP4). With hints from many of you and scripts plagiarized from several sites, I got a working model in our development and test servers. After some difficulty (see earlier posting), I got all three instances on our production server up and running in ARCHIVELOG mode.

    However, when I ran the batch file to make the backups, I got an error message indicating that the script was unable to connect to Oracle. The errror was TNSNAMES unable to resolve the service name.

    The batch file has three paramaters: user name, password and SID. The batch file opens up sqlplus and attempts to connect using the parameters passed in. In the test and Dev environments, it worked fine. Only on the production box do I have any errors.

    When I try starting SQLPLUS from the command prompt, and enter the username@SID and password, I also get rejected. However, when I start the SQLPlus application (Start | OraHome 81 | Application Development |SQL Plus) and enter the same information, I am able to connect right away.

    More information:

    I think that the prior DBA changed the domain on the server at one time. Each of the three instances on the server has two TNSNames entries:

    Code:
    InstanceA.subdomain.domain.com =
      (DESCRIPTION =
        (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = TCP)(HOST = SERVERNAME.domain.com)(PORT = 18001))
        )
        (CONNECT_DATA =
          (SERVICE_NAME = InstanceA.subdomain.domain.com)
        )
      )
    
    PInstanceA.subdomain.domain.com =
      (DESCRIPTION =
        (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = TCP)(HOST = SERVERNAME.domain.com)(PORT = 18001))
        )
        (CONNECT_DATA =
          (SERVICE_NAME = InstanceA.subdomain.domain.com)
        )
      )
    Notes:
    1. The FQDN of the server is currently SERVERNAME.domain.com.
    2. Yes, the correct port is 18001.

    I tried making a TNSNAMES entry like the following (essentially copying all the stuff in the parentheses, but changing the service name to exclude the domain quallifers.

    Code:
    InstanceABak
      (DESCRIPTION =
        (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = TCP)(HOST = SERVERNAME.domain.com)(PORT = 18001))
        )
        (CONNECT_DATA =
          (SERVICE_NAME = InstanceA.subdomain.domain.com)
        )
      )
    With this, I was again able to connect using SQL*Plus, but not by using sqlplus from the command line.

    For those of you who have been patiently reading my saga, I'm grateful once again for any insight you may have to offer. I apologize for not putting forth more effort into searching on my own for an answer, but after nearly killing one instance today and fighting through several other issues this week, my brain has pretty much turned to mush.

    Regards,

    hmscott

  2. #2
    Join Date
    Nov 2002
    Posts
    833
    it seems your environment variables are not properly that of whatever reason...

    include in your batch file

    export ORACLE_HOME=yourORCLHOME
    export ORACLE_SID=&SID

  3. #3
    Join Date
    Dec 2002
    Posts
    1,245
    I tried this with no success. Then I took a closer look at the different processes (one running successfully in test and one failing in production):

    In test, the script runs entirely in the same command window. The version of SQLPlus that is echoed to me is 8.1.6.

    In production, when I enter the name of the batch file at the command line (and enter the parameters), it pops up a new command window. This new command window echoes a version of SQL*Plus of 8.0.4.

    Sorry for the stupid question (please remember I didn't install this database server and I'm just picking up the pieces of those who have come and gone before me), but here goes:

    What version of SQL * Plus/Oracle am I running?

    Is there a difference in how to connect to a database when using SQL * Plus 8.0.4?

    Does anyone have the number for psychiatric counseling hotline...?

    Regards,

    hmscott



    Quote Originally Posted by osy45
    it seems your environment variables are not properly that of whatever reason...

    include in your batch file

    export ORACLE_HOME=yourORCLHOME
    export ORACLE_SID=&SID

  4. #4
    Join Date
    Dec 2002
    Posts
    1,245
    I think I finally found my answer. It appears that someone tried to install (or installed Oracle OEM on top of the database. Appearently, it had a different (older) version of SQLPlus. In addition, I'm guessing that it was pointed to a completely different TNSNAMES.ora file.

    Once I fully qualified the name of the executable in the batch file (to the correct version), things ran normally.

    This is not very fun. But I suppose it is interesting...

    hmscott
    Have you hugged your backup today?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •