Saturday, August 18, 2012

Remote connection to Oracle instance in AWS

Remote connection to Oracle instance in AWS

2012-05-02 18:41:58.0
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.
01oracle@domU-12-31-39-03-BD-92:[/home/oracle]
02$ pwd
03/home/oracle
04oracle@domU-12-31-39-03-BD-92:[/home/oracle]
05$ id
06uid=500(oracle) gid=500(oinstall) groups=500(oinstall),501(dba),502(oper),503(asmadmin)
07oracle@domU-12-31-39-03-BD-92:[/home/oracle]
08$ ps -ef | grep ora
09oracle 4923 1 0 Apr23 ? 00:00:00 /u01/app/oracle/product/11.2.0/db_1/bin/tnslsnr LISTENER -inherit
10oracle 25484 1 0 18:39 ? 00:00:00 ora_w000_odmdb
11root 25502 14542 0 18:40 ? 00:00:00 sshd: oracle [priv]
12oracle 25504 25502 0 18:41 ? 00:00:00 sshd: oracle@pts/0
13oracle 25505 25504 0 18:41 pts/0 00:00:00 -bash
14oracle 25529 25505 0 18:41 pts/0 00:00:00 ps -ef
15oracle 25530 25505 0 18:41 pts/0 00:00:00 grep ora
16oracle 26119 1 0 Apr30 ? 00:00:00 ora_pmon_odmdb
17oracle 26121 1 0 Apr30 ? 00:00:00 ora_vktm_odmdb
18oracle 26125 1 0 Apr30 ? 00:00:00 ora_gen0_odmdb
19oracle 26127 1 0 Apr30 ? 00:00:00 ora_diag_odmdb
20oracle 26129 1 0 Apr30 ? 00:00:00 ora_dbrm_odmdb
21oracle 26131 1 0 Apr30 ? 00:00:01 ora_psp0_odmdb
22oracle 26133 1 0 Apr30 ? 00:00:05 ora_dia0_odmdb
23oracle 26135 1 0 Apr30 ? 00:00:00 ora_mman_odmdb
24oracle 26137 1 0 Apr30 ? 00:00:00 ora_dbw0_odmdb
25oracle 26139 1 0 Apr30 ? 00:00:03 ora_lgwr_odmdb
26oracle 26141 1 0 Apr30 ? 00:00:00 ora_ckpt_odmdb
27oracle 26143 1 0 Apr30 ? 00:00:04 ora_smon_odmdb
28oracle 26145 1 0 Apr30 ? 00:00:00 ora_reco_odmdb
29oracle 26147 1 0 Apr30 ? 00:00:02 ora_mmon_odmdb
30oracle 26149 1 0 Apr30 ? 00:00:00 ora_mmnl_odmdb
31oracle 26151 1 0 Apr30 ? 00:00:00 ora_d000_odmdb
32oracle 26153 1 0 Apr30 ? 00:00:01 ora_s000_odmdb
33oracle 26202 1 0 Apr30 ? 00:00:00 ora_qmnc_odmdb
34oracle 26218 1 0 Apr30 ? 00:00:05 ora_cjq0_odmdb
35oracle 26226 1 0 Apr30 ? 00:00:00 ora_q000_odmdb
36oracle 26228 1 0 Apr30 ? 00:00:00 ora_q001_odmdb
37oracle 26280 1 0 Apr30 ? 00:00:00 ora_smco_odmdb
38oracle@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
03LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 02-MAY-2012 18:53:14
04
05Copyright (c) 1991, 2009, Oracle. All rights reserved.
06
07Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=domU-12-31-39-03-BD-92)(PORT=1521)))
08STATUS of the LISTENER
09------------------------
10Alias LISTENER
11Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
12Start Date 23-APR-2012 22:21:54
13Uptime 8 days 20 hr. 31 min. 19 sec
14Trace Level off
15Security ON: Local OS Authentication
16SNMP OFF
17Listener Parameter File /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
18Listener Log File /u01/app/oracle/diag/tnslsnr/domU-12-31-39-03-BD-92/listener/alert/log.xml
19Listening 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))
22Services Summary...
23Service "odmdb" has 1 instance(s).
24 Instance "odmdb", status READY, has 1 handler(s) for this service...
25Service "odmdbXDB" has 1 instance(s).
26 Instance "odmdb", status READY, has 1 handler(s) for this service...
27The command completed successfully
28oracle@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
03SQL*Plus: Release 11.2.0.1.0 Production on Wed May 2 18:55:06 2012
04
05Copyright (c) 1982, 2009, Oracle. All rights reserved.
06
07ERROR:
08ORA-12154: TNS:could not resolve the connect identifier specified
09
10
11Enter user-name:
12ERROR:
13ORA-01017: invalid username/password; logon denied
14
15
16Enter user-name:
17ERROR:
18ORA-01017: invalid username/password; logon denied
19
20
21SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
22oracle@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).
01oracle@domU-12-31-39-03-BD-92:[/home/oracle]
02$ pwd
03/home/oracle
04oracle@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)
07oracle@domU-12-31-39-03-BD-92:[/home/oracle]
08$ !cd
09cd /u01/app/oracle/product/11.2.0/db_1/network/admin/samples
10oracle@domU-12-31-39-03-BD-92:[/u01/app/oracle/product/11.2.0/db_1/network/admin/samples]
11$ ls
12listener.ora sqlnet.ora tnsnames.ora tnsnames.ora.orig
13oracle@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
05odmdb =
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
3SQL*Plus: Release 11.2.0.1.0 Production on Wed May 2 19:21:38 2012
4
5Copyright (c) 1982, 2009, Oracle. All rights reserved.
6
7ERROR:
8ORA-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.
1oracle@domU-12-31-39-03-BD-92:[/u01/app/oracle/product/11.2.0/db_1/network/admin/samples]
2$ env | grep TNS
3oracle@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)
01oracle@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
03oracle@domU-12-31-39-03-BD-92:[/u01/app/oracle/product/11.2.0/db_1/network/admin/samples]
04$ env | grep TNS
05TNS_ADMIN=/u01/app/oracle/product/11.2.0/db_1/network/admin/samples
06oracle@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
09SQL*Plus: Release 11.2.0.1.0 Production on Wed May 2 19:37:53 2012
10
11Copyright (c) 1982, 2009, Oracle. All rights reserved.
12
13
14Connected to:
15Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
16With the Partitioning, OLAP, Data Mining and Real Application Testing options
17
18SQL> exit;
19Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
20With the Partitioning, OLAP, Data Mining and Real Application Testing options
21oracle@domU-12-31-39-03-BD-92:[/u01/app/oracle/product/11.2.0/db_1/network/admin/samples]
22$
23oracle@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).
Posted by
Labels: ,

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)