CODEWITHSUNDEEP
oracle-database
Lucas Rey
Lucas Rey

Reputation: 467

Oracle change connection identifier

I have 2 Oracle database installed on 2 different server (production and test) but I'm not Oracle expert. In production I can connect succesfully via SQLPLUS using:

sqlplus user/[email protected]

In test system I have to specify different connection string:

sqlplus user/[email protected]:1521/orcl

Whithout the :1521/orcl I got: ORA-01017: invalid username/password; logon denied

For scripting purpose, I would like to use the same connection string but I'm stuck on how to change it. I tried to change the tnsnames.ora as:

ORCLCDB=localhost:1521/orclcdb
bc.name.server.com=
 (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

But still not work. Could someone address me on how I can change the connection string to:

sqlplus user/[email protected]

Thank you Lucas

Upvotes: 0

Views: 673

Answers (1)

Littlefoot
Littlefoot

Reputation: 142705

This is how I do that; see if it helps.

As every Oracle piece of software uses its own TNSNAMES.ORA file (for example, if you installed Oracle database and Developer Suite, you'd have two TNSNAMES.ORA files). In order to be able to connect to different databases, you have to maintain TNSNAMES.ORA in both (or more) directories. To avoid that, create TNS_ADMIN environment variable which points to a directory that contains a single copy of the TNSNAMES.ORA file. Therefore, when I add a new database to connect to, I maintain it only in that TNSNAMES.ORA file. On my computer, it is set to

SQL> $set tns_admin
TNS_ADMIN=C:\oralib

Contents of my SQLNET.ORA file:

SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

Contents of my TNSNAMES.ORA (part of it, though; in reality, I currently have aliases to 56 databases in there):

MY_TEST_DB=
  (DESCRIPTION=
    (ADDRESS=
      (PROTOCOL=TCP)
      (HOST=my_test_host)
      (PORT=1521)
    )
    (CONNECT_DATA=
      (SERVER=dedicated)
      (SID=db_test)
    )
  )
  
MY_PRODUCTION_DB=
  (DESCRIPTION=
    (ADDRESS_LIST=
      (ADDRESS=
        (PROTOCOL=TCP)
        (HOST=my_production_host)
        (PORT=1521)
      )
    )
    (CONNECT_DATA=
      (SID=db_production)
    )
  )

When I'm connecting to these databases, I run (at the operating system command prompt)

sqlplus scott/tiger@my_production_db

or

sqlplus scott/tiger@my_test_db

and everything works just fine.

Upvotes: 1

Related Questions