Remote connection to Oracle instance in AWS

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.

01 oracle@domU-12-31-39-03-BD-92:[/home/oracle]
02 $ pwd
03 /home/oracle
04 oracle@domU-12-31-39-03-BD-92:[/home/oracle]
05 $ id
06 uid=500(oracle) gid=500(oinstall) groups=500(oinstall),501(dba),502(oper),503(asmadmin)
07 oracle@domU-12-31-39-03-BD-92:[/home/oracle]
08 $ ps -ef | grep ora
09 oracle 4923 1 0 Apr23 ? 00:00:00 /u01/app/oracle/product/11.2.0/db_1/bin/tnslsnr LISTENER -inherit
10 oracle 25484 1 0 18:39 ? 00:00:00 ora_w000_odmdb
11 root 25502 14542 0 18:40 ? 00:00:00 sshd: oracle [priv]
12 oracle 25504 25502 0 18:41 ? 00:00:00 sshd: oracle@pts/0
13 oracle 25505 25504 0 18:41 pts/0 00:00:00 -bash
14 oracle 25529 25505 0 18:41 pts/0 00:00:00 ps -ef
15 oracle 25530 25505 0 18:41 pts/0 00:00:00 grep ora
16 oracle 26119 1 0 Apr30 ? 00:00:00 ora_pmon_odmdb
17 oracle 26121 1 0 Apr30 ? 00:00:00 ora_vktm_odmdb
18 oracle 26125 1 0 Apr30 ? 00:00:00 ora_gen0_odmdb
19 oracle 26127 1 0 Apr30 ? 00:00:00 ora_diag_odmdb
20 oracle 26129 1 0 Apr30 ? 00:00:00 ora_dbrm_odmdb
21 oracle 26131 1 0 Apr30 ? 00:00:01 ora_psp0_odmdb
22 oracle 26133 1 0 Apr30 ? 00:00:05 ora_dia0_odmdb
23 oracle 26135 1 0 Apr30 ? 00:00:00 ora_mman_odmdb
24 oracle 26137 1 0 Apr30 ? 00:00:00 ora_dbw0_odmdb
25 oracle 26139 1 0 Apr30 ? 00:00:03 ora_lgwr_odmdb
26 oracle 26141 1 0 Apr30 ? 00:00:00 ora_ckpt_odmdb
27 oracle 26143 1 0 Apr30 ? 00:00:04 ora_smon_odmdb
28 oracle 26145 1 0 Apr30 ? 00:00:00 ora_reco_odmdb
29 oracle 26147 1 0 Apr30 ? 00:00:02 ora_mmon_odmdb
30 oracle 26149 1 0 Apr30 ? 00:00:00 ora_mmnl_odmdb
31 oracle 26151 1 0 Apr30 ? 00:00:00 ora_d000_odmdb
32 oracle 26153 1 0 Apr30 ? 00:00:01 ora_s000_odmdb
33 oracle 26202 1 0 Apr30 ? 00:00:00 ora_qmnc_odmdb
34 oracle 26218 1 0 Apr30 ? 00:00:05 ora_cjq0_odmdb
35 oracle 26226 1 0 Apr30 ? 00:00:00 ora_q000_odmdb
36 oracle 26228 1 0 Apr30 ? 00:00:00 ora_q001_odmdb
37 oracle 26280 1 0 Apr30 ? 00:00:00 ora_smco_odmdb
38 oracle@domU-12-31-39-03-BD-92:[/home/oracle]
39 $

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.

01 $ lsnrctl status
02
03 LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 02-MAY-2012 18:53:14
04
05 Copyright (c) 1991, 2009, Oracle. All rights reserved.
06
07 Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=domU-12-31-39-03-BD-92)(PORT=1521)))
08 STATUS of the LISTENER
09 ------------------------
10 Alias LISTENER
11 Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
12 Start Date 23-APR-2012 22:21:54
13 Uptime 8 days 20 hr. 31 min. 19 sec
14 Trace Level off
15 Security ON: Local OS Authentication
16 SNMP OFF
17 Listener Parameter File /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
18 Listener Log File /u01/app/oracle/diag/tnslsnr/domU-12-31-39-03-BD-92/listener/alert/log.xml
19 Listening Endpoints Summary...
20 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=domU-12-31-39-03-BD-92)(PORT=1521)))
21 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=domU-12-31-39-03-BD-92)(PORT=8080))(Presentation=HTTP)(Session=RAW))
22 Services Summary...
23 Service "odmdb" has 1 instance(s).
24 Instance "odmdb", status READY, has 1 handler(s) for this service...
25 Service "odmdbXDB" has 1 instance(s).
26 Instance "odmdb", status READY, has 1 handler(s) for this service...
27 The command completed successfully
28 oracle@domU-12-31-39-03-BD-92:[/home/oracle]
29 $

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

01 $ sqlplus hr/hr@odmdb
02
03 SQL*Plus: Release 11.2.0.1.0 Production on Wed May 2 18:55:06 2012
04
05 Copyright (c) 1982, 2009, Oracle. All rights reserved.
06
07 ERROR:
08 ORA-12154: TNS:could not resolve the connect identifier specified
09
10
11 Enter user-name:
12 ERROR:
13 ORA-01017: invalid username/password; logon denied
14
15
16 Enter user-name:
17 ERROR:
18 ORA-01017: invalid username/password; logon denied
19
20
21 SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
22 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).

01 oracle@domU-12-31-39-03-BD-92:[/home/oracle]
02 $ pwd
03 /home/oracle
04 oracle@domU-12-31-39-03-BD-92:[/home/oracle]
05 $ which tnsnames.ora
06 /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)
07 oracle@domU-12-31-39-03-BD-92:[/home/oracle]
08 $ !cd
09 cd /u01/app/oracle/product/11.2.0/db_1/network/admin/samples
10 oracle@domU-12-31-39-03-BD-92:[/u01/app/oracle/product/11.2.0/db_1/network/admin/samples]
11 $ ls
12 listener.ora sqlnet.ora tnsnames.ora tnsnames.ora.orig
13 oracle@domU-12-31-39-03-BD-92:[/u01/app/oracle/product/11.2.0/db_1/network/admin/samples]
14 $

Let us add an entry for odmdb.

01 # The following is the general syntax for any entry in
02 # a tnsnames.ora file. There could be several such entries
03 # tailored to the user's needs.
04
05 odmdb =
06 (DESCRIPTION =
07 (ADDRESS_LIST =
08 (ADDRESS = (PROTOCOL = TCP)(HOST = domU-12-31-39-03-BD-92)(PORT = 1521))
09 )
10 (CONNECT_DATA =
11 (SERVER = DEDICATED)
12 (SERVICE_NAME = odmdb)
13 )
14 )

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.

1 $ sqlplus hr/hr@odmdb
2
3 SQL*Plus: Release 11.2.0.1.0 Production on Wed May 2 19:21:38 2012
4
5 Copyright (c) 1982, 2009, Oracle. All rights reserved.
6
7 ERROR:
8 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.

1 oracle@domU-12-31-39-03-BD-92:[/u01/app/oracle/product/11.2.0/db_1/network/admin/samples]
2 $ env | grep TNS
3 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)

01 oracle@domU-12-31-39-03-BD-92:[/u01/app/oracle/product/11.2.0/db_1/network/admin/samples]
02 $ export TNS_ADMIN=/u01/app/oracle/product/11.2.0/db_1/network/admin/samples
03 oracle@domU-12-31-39-03-BD-92:[/u01/app/oracle/product/11.2.0/db_1/network/admin/samples]
04 $ env | grep TNS
05 TNS_ADMIN=/u01/app/oracle/product/11.2.0/db_1/network/admin/samples
06 oracle@domU-12-31-39-03-BD-92:[/u01/app/oracle/product/11.2.0/db_1/network/admin/samples]
07 $ sqlplus hr/hr@odmdb
08
09 SQL*Plus: Release 11.2.0.1.0 Production on Wed May 2 19:37:53 2012
10
11 Copyright (c) 1982, 2009, Oracle. All rights reserved.
12
13
14 Connected to:
15 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
16 With the Partitioning, OLAP, Data Mining and Real Application Testing options
17
18 SQL> exit;
19 Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
20 With the Partitioning, OLAP, Data Mining and Real Application Testing options
21 oracle@domU-12-31-39-03-BD-92:[/u01/app/oracle/product/11.2.0/db_1/network/admin/samples]
22 $
23 oracle@domU-12-31-39-03-BD-92:[/u01/app/oracle/product/11.2.0/db_1/network/admin/samples]
24 $

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).

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s