The Systems People, Inc.: AWS

Showing posts with label AWS. Show all posts

Thursday, February 14, 2013

Configuring EM for Oracle 11g on AWS

In my previous post creating-oracle-11g-instance-in-aws we explored how to create an AWS EC2 instance from preconfigured Oracle 11g public AMI.

Many months later, after starting a LINUX instance with Oracle 11g configure AMI, I found that EM (enterprise manager) was not configured properly.

In this post I’ll explore and start EM.

After logging into OS using oracle user, I issued the following commands.

Note the two IP addresses 174-129-62-10 (in emctl response) and 54-234-168-130 (at the header). This indicates AMI previous configuration is file is sitting there.

We need to reconfigure EM.

oracle@domU-12-31-39-00-64-53:[/u01/app/oracle/product/11.2.0/db_1]

$ emca -deconfig dbcontrol db -repos drop;

STARTED EMCA at Feb 13, 2013 4:15:31 PM

EM Configuration Assistant, Version 11.2.0.0.2 Production

Enter the following information:

Database SID: odmdb

Listener port number: 1521

Password for SYS user:

Password for SYSMAN user:

Do you wish to continue? [yes(Y)/no(N)]: Y

Feb 13, 2013 4:15:58 PM oracle.sysman.emcp.EMConfig perform

INFO: This operation is being logged at /u01/app/oracle/cfgtoollogs/emca/odmdb/emca_2013_02_13_16_15_30.log.

Feb 13, 2013 4:15:59 PM oracle.sysman.emcp.EMDBPreConfig performDeconfiguration

WARNING: EM is not configured for this database. No EM-specific actions can be performed.

Feb 13, 2013 4:15:59 PM oracle.sysman.emcp.ParamsManager checkListenerStatusForDBControl

WARNING: Error initializing SQL connection. SQL operations cannot be performed

Feb 13, 2013 4:15:59 PM oracle.sysman.emcp.EMReposConfig invoke

INFO: Dropping the EM repository (this may take a while) ...

Feb 13, 2013 4:16:59 PM oracle.sysman.emcp.util.PlatformInterface executeCommand

WARNING: Error executing /u01/app/oracle/product/11.2.0/db_1/sysman/admin/emdrep/bin/RepManager -connect (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=ec2-174-129-62-10.compute-1.amazonaws.com)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=odmdb))) -repos_user SYSMAN -action drop -verbose -output_file /u01/app/oracle/cfgtoollogs/emca/odmdb/emca_repos_drop_2013_02_13_16_15_59.log

Feb 13, 2013 4:16:59 PM oracle.sysman.emcp.EMReposConfig invoke

SEVERE: Error dropping the repository

Feb 13, 2013 4:16:59 PM oracle.sysman.emcp.EMReposConfig invoke

INFO: Refer to the log file at /u01/app/oracle/cfgtoollogs/emca/odmdb/emca_repos_drop_<date>.log for more details.

Feb 13, 2013 4:16:59 PM oracle.sysman.emcp.EMConfig perform

SEVERE: Error dropping the repository

Refer to the log file at /u01/app/oracle/cfgtoollogs/emca/odmdb/emca_2013_02_13_16_15_30.log for more details.

Could not complete the configuration. Refer to the log file at /u01/app/oracle/cfgtoollogs/emca/odmdb/emca_2013_02_13_16_15_30.log for more details.

oracle@domU-12-31-39-00-64-53:[/u01/app/oracle/product/11.2.0/db_1]

This failed, and on checking the log, I realized the SYSMAN password was wrong. So I am going to correct the SYSMAN account (PW reset) and try EMCA again.

oracle@domU-12-31-39-00-64-53:[/u01/app/oracle/product/11.2.0/db_1]

$ emca -deconfig dbcontrol db -repos drop;

STARTED EMCA at Feb 13, 2013 4:23:25 PM

EM Configuration Assistant, Version 11.2.0.0.2 Production

Enter the following information:

Database SID: odmdb

Listener port number: 1521

Password for SYS user:

Password for SYSMAN user:

Do you wish to continue? [yes(Y)/no(N)]: Y

Feb 13, 2013 4:23:48 PM oracle.sysman.emcp.EMConfig perform

INFO: This operation is being logged at /u01/app/oracle/cfgtoollogs/emca/odmdb/emca_2013_02_13_16_23_24.log.

Feb 13, 2013 4:23:49 PM oracle.sysman.emcp.EMDBPreConfig performDeconfiguration

WARNING: EM is not configured for this database. No EM-specific actions can be performed.

Feb 13, 2013 4:24:49 PM oracle.sysman.emcp.EMConfig perform

SEVERE: Listener is not up or database service is not registered with it. Start the Listener and register database service and run EM Configuration Assistant again .

Refer to the log file at /u01/app/oracle/cfgtoollogs/emca/odmdb/emca_2013_02_13_16_23_24.log for more details.

Could not complete the configuration. Refer to the log file at /u01/app/oracle/cfgtoollogs/emca/odmdb/emca_2013_02_13_16_23_24.log for more details.

oracle@domU-12-31-39-00-64-53:[/u01/app/oracle/product/11.2.0/db_1]

However this time we get the message that DB is not registered with Listener. Let’s check.

$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 13-FEB-2013 16:27:32

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

STATUS of the LISTENER

------------------------

Alias LISTENER

Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production

Start Date 13-FEB-2013 11:14:39

Uptime 0 days 5 hr. 12 min. 52 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-00-64-53/listener/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=domU-12-31-39-00-64-53)(PORT=1521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=domU-12-31-39-00-64-53)(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-00-64-53:[/u01/app/oracle/product/11.2.0/db_1]

Well Listener is up and servicing the database. So we are going to create the EM repository.

Note: You’ll need the DB password for SYS, DBSNMP and SYSMAN users.

If you continue this you’ll get an error that SYSMAN already exists. We should have used recreate command on emca

Also in case where AMI was created with ORACLE_HOSTNAME and other parameters set to original instance value, please make sure that those values are changed to reflect the current instance values.

In this instance the .bash_profile had set the HOSTNAME to old value. I corrected that.

oracle@domU-12-31-39-00-64-53:[/home/oracle]

$ emca -config dbcontrol db -repos recreate;

STARTED EMCA at Feb 13, 2013 5:04:48 PM

EM Configuration Assistant, Version 11.2.0.0.2 Production

Enter the following information:

Database SID: odmdb

Listener port number: 1521

Listener ORACLE_HOME [ /u01/app/oracle/product/11.2.0/db_1 ]:

Password for SYS user:

Password for DBSNMP user:

Password for SYSMAN user:

Email address for notifications (optional):

Outgoing Mail (SMTP) server for notifications (optional):

-----------------------------------------------------------------

You have specified the following settings

Database ORACLE_HOME ................ /u01/app/oracle/product/11.2.0/db_1

Local hostname ................ ec2-54-234-168-130.compute-1.amazonaws.com

Listener ORACLE_HOME ................ /u01/app/oracle/product/11.2.0/db_1

Listener port number ................ 1521

Database SID ................ odmdb

Email address for notifications ...............

Outgoing Mail (SMTP) server for notifications ...............

-----------------------------------------------------------------

Do you wish to continue? [yes(Y)/no(N)]: Y

Feb 13, 2013 5:05:30 PM oracle.sysman.emcp.EMConfig perform

INFO: This operation is being logged at /u01/app/oracle/cfgtoollogs/emca/odmdb/emca_2013_02_13_17_04_47.log.

Feb 13, 2013 5:05:32 PM oracle.sysman.emcp.EMReposConfig invoke

INFO: Dropping the EM repository (this may take a while) ...

Feb 13, 2013 5:09:02 PM oracle.sysman.emcp.EMReposConfig invoke

INFO: Repository successfully dropped

Feb 13, 2013 5:09:03 PM oracle.sysman.emcp.EMReposConfig createRepository

INFO: Creating the EM repository (this may take a while) ...

Feb 13, 2013 5:23:18 PM oracle.sysman.emcp.EMReposConfig invoke

INFO: Repository successfully created

Feb 13, 2013 5:23:26 PM oracle.sysman.emcp.EMReposConfig uploadConfigDataToRepository

INFO: Uploading configuration data to EM repository (this may take a while) ...

Feb 13, 2013 5:26:33 PM oracle.sysman.emcp.EMReposConfig invoke

INFO: Uploaded configuration data successfully

Feb 13, 2013 5:26:38 PM oracle.sysman.emcp.util.DBControlUtil configureSoftwareLib

INFO: Software library configured successfully.

Feb 13, 2013 5:26:38 PM oracle.sysman.emcp.EMDBPostConfig configureSoftwareLibrary

INFO: Deploying Provisioning archives ...

Feb 13, 2013 5:27:46 PM oracle.sysman.emcp.EMDBPostConfig configureSoftwareLibrary

INFO: Provisioning archives deployed successfully.

Feb 13, 2013 5:27:46 PM oracle.sysman.emcp.util.DBControlUtil secureDBConsole

INFO: Securing Database Control (this may take a while) ...

Feb 13, 2013 5:28:56 PM oracle.sysman.emcp.util.DBControlUtil secureDBConsole

INFO: Database Control secured successfully.

Feb 13, 2013 5:28:56 PM oracle.sysman.emcp.util.DBControlUtil startOMS

INFO: Starting Database Control (this may take a while) ...

Feb 13, 2013 5:31:25 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration

INFO: Database Control started successfully

Feb 13, 2013 5:31:25 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration

INFO: >>>>>>>>>>> The Database Control URL is https://ec2-54-234-168-130.compute-1.amazonaws.com:1158/em <<<<<<<<<<<

Feb 13, 2013 5:31:46 PM oracle.sysman.emcp.EMDBPostConfig invoke

WARNING:

************************ WARNING ************************

Management Repository has been placed in secure mode wherein Enterprise Manager data will be encrypted. The encryption key has been placed in the file: /u01/app/oracle/product/11.2.0/db_1/ec2-54-234-168-130.compute-1.amazonaws.com_odmdb/sysman/config/emkey.ora. Please ensure this file is backed up as the encrypted data will become unusable if this file is lost.

***********************************************************

Enterprise Manager configuration completed successfully

FINISHED EMCA at Feb 13, 2013 5:31:46 PM

oracle@domU-12-31-39-00-64-53:[/home/oracle]

Now we can use the URL to access the EM from web browser.

That is it. Give it try. Good Luck.

Note with this EC2 instance is Oracle Database is configured to continuously perform work, behind the scenes. So even if you are not actively working with this instance, there are IO and CPU cycles being consumed and will incur some cost. Specifically with this instance if you keep the instance running in default configuration without accessing the instance at all you’ll incur about $40.00 (Estimated) cost per month.

You can create a custom AMI or a SPOT instance to lower the cost.

Saturday, August 18, 2012

Creating an Oracle 11g instance in AWS

This document assumes, that you already have an AWS account and you are able to login using any standard net browser. After logging into AWS (Amazon Web Services) EC2 site, click on Launch Instance button.
NOTE: Starting this instance using this documentation will incur some monthly financial cost.

We'll user the instance ami-117b9778. We can narrow down the earch by typing in some key words. I used Oracle.

We’ll use a specific instance: ami-117b9778 instance store.
We’ll create a “small instance”.
No specific preference for zone.

In the next screen we’ll take default for all values.
Specifically note if you use monitoring, additional charges may apply.
Click on Continue…

At this point, we have chosen our AMI.

We’ll add one “tag information” for our instance…

We are ready to create KEY Pair for our instance. The Key-Pair is used for loggin into our instance with root access. With this set up, we’ll disable userid/password login to root and root access login will only be allowed using PKI key pair.
Note: we’ll be using Putty-SSH telnet to log into this instance as root. We’ll later create additional user-id which will have password enabled.
NOTE: This step is important, as we’ll create key for server and client. Save the client file safely in a directory and note the location/filename.

Give the key-pair a unique name, click on Create & Download your Key Pair button.
Note: You could proceed without key creation and create one later time.
You may be asked to save the file.

I clicked on Save As and chose a specific local location to save the file.
Note that the file type downloaded is PEM.

We have to choose a Security Group. We’ll go with default. We can change the values like port and accessibility later (or you can create a new security group).
We’ll go with default and click on continue…

Review the summary…
You have an opportunity to change your settings however you can also change many of these setting later.
We’ll click on Launch button

This step will take few minutes to launch the instance.

Click on Close to create an Oracle 11g instance. You’ll be returned to EC2 home.
Note: In the My Resources section, both Key Pair and Security Group has been updated.

Click on Instances on left panel to go to your instance page.
Note the instance is in pending state and status is initializing.
This may take some time. Just have some patience… It’ll come through :-)

Once your instance is instantiated, you find the following in your EC2 home.
Note the instance is in running state and status is green check.

Note: The name of the instance is empty. You can edit the name to something that you can recognize, by clicking on the field.

Now our next step is to connect to our instance.
To connect to our newly created AWS instance, we are going to use PUTTY-SSH telnet session.
You can download putty-ssh telnet client from here. I downloaded putty and puttygen from source forge.

PUTTYGEN will be used to generate private key from the PEM file that was downloaded from the KEY-PAIR of aws instance.
Generate Private Key using PUTTYGEN
Run PUTTYGEN by double clicking on the application

Click on LOAD… Note file type of PPK!!!!

Navigate to the directory where the PEM file was saved and select the PEM files (using all file types).

Open the PEM file.

DONOT Click on Generate: this allows you to move cursor over the BLANK space to generate randomized key, this will not work with your instance.
JUST click on SAVE PRIVATE KEY and Save Private Key, ENSURE to change the file type to ppk.

Close the PUTTYGEN application.

Connect to our AWS instance

Now we’ll use the PUTTY (telnet) application to connect to AWS instance.
When you open PUTTY application, you get the following dialog.

From the AWs instance definition (property) use Public DNS address

In our case the value is ec2-107-20-86-85.compute-1.amazonaws.com

Browse to the PPK file that was saved from PUTTYGEN.

Save PUTTY configuration and Open

Type in root and press ENTER

The Oracle 11g instance on AWS is READY to be configured.

Configure Oracle 11g instance

Ready for Oracle configuration…
The password and verification was entered and installation complete.

Check, if Oracle processes are running…

Check ID / GRP ???

1root@domU-12-31-39-03-BD-92:[/u01/app/oracle/product/11.2.0/db_1/bin]$ id

2uid=0(root) gid=0(root) groups=0(root),1(bin),2(daemon),3(sys),4(adm),6(disk),10(wheel)

3root@domU-12-31-39-03-BD-92:[/u01/app/oracle/product/11.2.0/db_1/bin]$ id oracle

4uid=500(oracle) gid=500(oinstall) groups=500(oinstall),501(dba),502(oper),503(asmadmin)

5root@domU-12-31-39-03-BD-92:[/u01/app/oracle/product/11.2.0/db_1/bin]$ groups

6root bin daemon sys adm disk wheel

7root@domU-12-31-39-03-BD-92:[/u01/app/oracle/product/11.2.0/db_1/bin]$

Logged in as ROOT to the linux instance, we’ll attempt to log into database instance.
Just to make sure the database is there, configured and started. We’ll use sqlplus.

1$ ./sqlplus

2Error 6 initializing SQL*Plus

3SP2-0667: Message file sp1.msb not found

4SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory

SP2-0750 indicates we have not set ORACLE_HOME value. We’ll do that and try again.

01root@domU-12-31-39-03-BD-92:[/u01/app/oracle/product/11.2.0/db_1/bin]$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1/

02

03root@domU-12-31-39-03-BD-92:[/u01/app/oracle/product/11.2.0/db_1/bin]$ ./sqlplus

04

05SQL*Plus: Release 11.2.0.1.0 Production on Mon Apr 23 22:43:39 2012

06

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

08

09Enter user-name: oracle

10Enter password:

11

12ERROR:

13ORA-12162: TNS:net service name is incorrectly specified

ORA-12162 indicates we have not set ORACLE_SID value. We’ll do that and try again.

01root@domU-12-31-39-03-BD-92:[/u01/app/oracle/product/11.2.0/db_1/bin]$ export ORACLE_SID=orcl

02root@domU-12-31-39-03-BD-92:[/u01/app/oracle/product/11.2.0/db_1/bin]$ ./sqlplus

03

04SQL*Plus: Release 11.2.0.1.0 Production on Mon Apr 23 22:44:37 2012

05

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

07

08Enter user-name: oracle

09Enter password:

10

11ERROR:

12ORA-01034: ORACLE not available

13ORA-27101: shared memory realm does not exist

14Linux Error: 2: No such file or directory

15Process ID: 0

16Session ID: 0 Serial number: 0

Login was successful (in a way) however ORA-01034 indicates that ORACLE database instance is not up and running. At this point we can connect to ORCL instance in NOLOG mode.

1root@domU-12-31-39-03-BD-92:[/u01/app/oracle/product/11.2.0/db_1/bin]

2$ ./sqlplus /nolog

3

4SQL*Plus: Release 11.2.0.1.0 Production on Mon Apr 23 22:46:15 2012

5

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

7

8SQL>

Nice… We are now connected to ORCL instance of the database. However, the DB instance is not up and providing any service. We’ll try to start the database instance.

1SQL> startup

2

3ORA-01031: insufficient privileges

4

5SQL> exit;

6

7root@domU-12-31-39-03-BD-92:[/u01/app/oracle/product/11.2.0/db_1/bin

The database exists, but we are unable to start the database as root is NOT a member of dba or oper groups. Only members of these groups are authorized (ORA-01031) to start or stop a DB instance.
In the next blog entry, I’ll continue with configuring the database instance and starting the database.
However, before we do that we should also create a non-root user at the OS level and open up the LINUX instance for password login.

We'll continue and connect and work with this DB instance in the following BLOG entry.
http://tsp-inc.blogspot.com/2012/08/experiment-with-new-oracle-instance-in.html

How to log into AWS instance using password authentication

By default SSHD in linux instances in AWS are started with password authentication set to NO. For good resaons. Clear text password authentication is NOT as secured.
However for development instances or sandboxes, it might provide a "convenience" that may be worth.
To enable password authentication to AWS linux instances, modify parameter PasswordAuthentication parameter in file sshd_config and then restart the sshd process.
For example...

1root@domU-12-31-39-00-7C-94:[/etc/ssh]

2$ pwd

3/etc/ssh

4root@domU-12-31-39-00-7C-94:[/etc/ssh]

5$

6$ vi sshd_config

In the VI editor...

1# To disable tunneled clear text passwords, change to no here!

2PasswordAuthentication yes # <<<< we enabled this!!!!

3#PermitEmptyPasswords no

4# Changed to no per AWS

5#PasswordAuthentication no

Now restart SSHD process.

01$ sshd

02sshd re-exec requires execution with an absolute path

03root@domU-12-31-39-00-7C-94:[/etc/ssh]

04$ which sshd

05/usr/sbin/sshd

06root@domU-12-31-39-00-7C-94:[/etc/ssh]

07$

08root@domU-12-31-39-00-7C-94:[/etc/ssh]

09$ service /usr/sbin/sshd restart

10/usr/sbin/sshd: unrecognized service

11root@domU-12-31-39-00-7C-94:[/etc/ssh]

12$ service sshd restart

13Stopping sshd:                                             [  OK  ]

14Starting sshd:                                             [  OK  ]

15root@domU-12-31-39-00-7C-94:[/etc/ssh]

16$

Now SSHD process is started with password authentication enabled. If we remove the key from the putty profle and open a telnet connection, we'll be prompted for userid and password.
We should be able use a valid linux user with password to login.

1login as: oracle

2oracle@ec2-23-22-205-159.compute-1.amazonaws.com's password:

3oracle@domU-12-31-39-00-7C-94:[/home/oracle]

4$

5oracle@domU-12-31-39-00-7C-94:[/home/oracle]

6$

7oracle@domU-12-31-39-00-7C-94:[/home/oracle]

8$

That's it. Be careful when using this technique.

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.

Identify the instance SID
Ensure LISTENER services are up and running
The "client side" TNSNAMES.ORA file exists.
The TNSNAMES.ORA file has appropriate entries for the instance that you are trying to connect
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:

TNSNAMES.ORA file is not accessible. (does not exist).
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).