The Systems People Inc.

The Systems People Inc.

Customer Focused Service since 1993

Remote connection to Oracle instance in AWS

Last blog entry we connected successfully to Oracle instance locally. Now we'll create the TNSNAMES.ORA entry and connect using remote connection. There are many components that come in to play when connecting to Oracle instance using remote mechanism.

  1. Identify the instance SID
  2. Ensure LISTENER services are up and running
  3. The "client side" TNSNAMES.ORA file exists.
  4. The TNSNAMES.ORA file has appropriate entries for the instance that you are trying to connect
  5. Unix/Linux (OS) environment is rightly pointing to correct TNSNAMES.ORA file. This is achieved by using TNS_ADMIN environment variable.

If any of these components are not propoerly configured then Oracle will retrun different messages. Unfortunately, for a beginner this becomes complex. I'll attempt to cover this, so it might help others.

Note: There ae tons of documentations, websites, blogs, and forums that address this.

SID of the Oracle instance:

normally this will be known or DBA can provide this to you. However, looking at the processes (default) running you can infer what the SID is. In Oracle 11g instance in AWS, we see the following.

oracle@domU-12-31-39-03-BD-92:[/home/oracle]
$ pwd
/home/oracle
oracle@domU-12-31-39-03-BD-92:[/home/oracle]
$ id
uid=500(oracle) gid=500(oinstall) groups=500(oinstall),501(dba),502(oper),503(asmadmin)
oracle@domU-12-31-39-03-BD-92:[/home/oracle]
$ ps -ef | grep ora
oracle    4923     1  0 Apr23 ?        00:00:00 /u01/app/oracle/product/11.2.0/db_1/bin/tnslsnr LISTENER -inherit
oracle   25484     1  0 18:39 ?        00:00:00 ora_w000_odmdb
root     25502 14542  0 18:40 ?        00:00:00 sshd: oracle [priv]
oracle   25504 25502  0 18:41 ?        00:00:00 sshd: oracle@pts/0
oracle   25505 25504  0 18:41 pts/0    00:00:00 -bash
oracle   25529 25505  0 18:41 pts/0    00:00:00 ps -ef
oracle   25530 25505  0 18:41 pts/0    00:00:00 grep ora
oracle   26119     1  0 Apr30 ?        00:00:00 ora_pmon_odmdb
oracle   26121     1  0 Apr30 ?        00:00:00 ora_vktm_odmdb
oracle   26125     1  0 Apr30 ?        00:00:00 ora_gen0_odmdb
oracle   26127     1  0 Apr30 ?        00:00:00 ora_diag_odmdb
oracle   26129     1  0 Apr30 ?        00:00:00 ora_dbrm_odmdb
oracle   26131     1  0 Apr30 ?        00:00:01 ora_psp0_odmdb
oracle   26133     1  0 Apr30 ?        00:00:05 ora_dia0_odmdb
oracle   26135     1  0 Apr30 ?        00:00:00 ora_mman_odmdb
oracle   26137     1  0 Apr30 ?        00:00:00 ora_dbw0_odmdb
oracle   26139     1  0 Apr30 ?        00:00:03 ora_lgwr_odmdb
oracle   26141     1  0 Apr30 ?        00:00:00 ora_ckpt_odmdb
oracle   26143     1  0 Apr30 ?        00:00:04 ora_smon_odmdb
oracle   26145     1  0 Apr30 ?        00:00:00 ora_reco_odmdb
oracle   26147     1  0 Apr30 ?        00:00:02 ora_mmon_odmdb
oracle   26149     1  0 Apr30 ?        00:00:00 ora_mmnl_odmdb
oracle   26151     1  0 Apr30 ?        00:00:00 ora_d000_odmdb
oracle   26153     1  0 Apr30 ?        00:00:01 ora_s000_odmdb
oracle   26202     1  0 Apr30 ?        00:00:00 ora_qmnc_odmdb
oracle   26218     1  0 Apr30 ?        00:00:05 ora_cjq0_odmdb
oracle   26226     1  0 Apr30 ?        00:00:00 ora_q000_odmdb
oracle   26228     1  0 Apr30 ?        00:00:00 ora_q001_odmdb
oracle   26280     1  0 Apr30 ?        00:00:00 ora_smco_odmdb
oracle@domU-12-31-39-03-BD-92:[/home/oracle]
$

this command list the OS processes running. Note: odmdb is the Oracle instance SID. This is by default (could have been changed in your specific situation).

At this point we are login to AWS Linux instance as oracle (os user). By default listener for our Oracle instance odmdb is up and running. Let us ensure that.

$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 02-MAY-2012 18:53:14

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=domU-12-31-39-03-BD-92)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                23-APR-2012 22:21:54
Uptime                    8 days 20 hr. 31 min. 19 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/domU-12-31-39-03-BD-92/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=domU-12-31-39-03-BD-92)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=domU-12-31-39-03-BD-92)(PORT=8080))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "odmdb" has 1 instance(s).
  Instance "odmdb", status READY, has 1 handler(s) for this service...
Service "odmdbXDB" has 1 instance(s).
  Instance "odmdb", status READY, has 1 handler(s) for this service...
The command completed successfully
oracle@domU-12-31-39-03-BD-92:[/home/oracle]
$

With this a current state, let us try connecting to our Oracle instance (odmdb) using HR as Oracle user.

$ sqlplus hr/hr@odmdb

SQL*Plus: Release 11.2.0.1.0 Production on Wed May 2 18:55:06 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified


Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied


Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied


SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
oracle@domU-12-31-39-03-BD-92:[/home/oracle]

sqlplus will attempt to connect (and prompt) 3 times. In future I'll truncate this to first attempt.

We got ORA-012154:  TNS:could not resolve the connect identifier specified

Unfortunately, this message could come for several different reasons (root causes). Basicaly, it means that the connect identifier (odmdb) we specified is not resolvable using current TNSNAMES.ORA specification.

This could be because of several reasons, like:

  1. TNSNAMES.ORA file is not accessible. (does not exist).
  2. The SID specified in the file is not matching (wrong) etc.

Let us now ensure TNSNAMES.ORA file exists (and it has proper entry for odmdb). The file did not eixst in any directory within the path; however it does (sample) in default location. The sample file has no entry for our Oracle instance (odmdb).

oracle@domU-12-31-39-03-BD-92:[/home/oracle]
$ pwd
/home/oracle
oracle@domU-12-31-39-03-BD-92:[/home/oracle]
$ which tnsnames.ora
/usr/bin/which: no tnsnames.ora in (/u01/app/oracle/product/11.2.0/db_1/bin:/u01/app/oracle/product/11.2.0/db_1/jdk/bin:/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/home/oracle/bin)
oracle@domU-12-31-39-03-BD-92:[/home/oracle]
$ !cd
cd /u01/app/oracle/product/11.2.0/db_1/network/admin/samples
oracle@domU-12-31-39-03-BD-92:[/u01/app/oracle/product/11.2.0/db_1/network/admin/samples]
$ ls
listener.ora  sqlnet.ora  tnsnames.ora  tnsnames.ora.orig
oracle@domU-12-31-39-03-BD-92:[/u01/app/oracle/product/11.2.0/db_1/network/admin/samples]
$

Let us add an entry for odmdb.

# The following is the general syntax for any entry in
# a tnsnames.ora file. There could be several such entries
# tailored to the user's needs.

odmdb =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = domU-12-31-39-03-BD-92)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = odmdb)
    )
  )

If this entry is not accurate, we'll get the same error as before. We'll explore this later.

This is valid entry.

Now, let us try connecting again.

$ sqlplus hr/hr@odmdb

SQL*Plus: Release 11.2.0.1.0 Production on Wed May 2 19:21:38 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

Oh well, May be this TNSNAMES.ORA file is not in effect? That is correct. The os variable TNS_ADMIN value points to the directory where effective TNSNAMES.ORA file could be found. Let us check the value in our environment.

oracle@domU-12-31-39-03-BD-92:[/u01/app/oracle/product/11.2.0/db_1/network/admin/samples]
$ env | grep TNS
oracle@domU-12-31-39-03-BD-92:[/u01/app/oracle/product/11.2.0/db_1/network/admin/samples]

Uh Oh... The value is not set. Let us set it  and then try again. We'll use export command (since we are in bash)

oracle@domU-12-31-39-03-BD-92:[/u01/app/oracle/product/11.2.0/db_1/network/admin/samples]
$ export TNS_ADMIN=/u01/app/oracle/product/11.2.0/db_1/network/admin/samples
oracle@domU-12-31-39-03-BD-92:[/u01/app/oracle/product/11.2.0/db_1/network/admin/samples]
$ env | grep TNS
TNS_ADMIN=/u01/app/oracle/product/11.2.0/db_1/network/admin/samples
oracle@domU-12-31-39-03-BD-92:[/u01/app/oracle/product/11.2.0/db_1/network/admin/samples]
$ sqlplus hr/hr@odmdb

SQL*Plus: Release 11.2.0.1.0 Production on Wed May 2 19:37:53 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> exit;
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
oracle@domU-12-31-39-03-BD-92:[/u01/app/oracle/product/11.2.0/db_1/network/admin/samples]
$
oracle@domU-12-31-39-03-BD-92:[/u01/app/oracle/product/11.2.0/db_1/network/admin/samples]
$

VOILA!!!

The connection using "remote" mechanism is successful. 

Normally, we can set up the TNS_ADMIN value in our .profile script.

In our next blog we'll attempt some basic work in Oracle instance (using sqlplus).

 Blog Stats

  • Total posts(18)
  • Total comments(0)

Forgot your password?