Connecting to new Oracle instance in AWS

 

We initiated and opened up AWS linux instance with Oracle 11g database.
In this blog, we’ll explore several errors encountered with Oracle DB instance. Most of these errors (including messages) are due to operator errors.
We’ll connect to AWS instance as oracle user.

 

1 login as: oracle
2 oracle@ec2-107-20-86-85.compute-1.amazonaws.com's password:
3 Last login: Mon Apr 30 17:29:17 2012 from 98.235.186.132
4 oracle@domU-12-31-39-03-BD-92:[/home/oracle]$

The oracle account was enabled for userid/password login into AWS instance.
Once logged in, we attempt starting sqlplus and start the database… sounds reasonable?

01 oracle@domU-12-31-39-03-BD-92:[/home/oracle]
02 $ sqlplus / as sysdba
04 SQL*Plus: Release 11.2.0.1.0 Production on Tue May 1 19:03:18 2012
05
06 Copyright (c) 1982, 2009, Oracle. All rights reserved.
07
08
09 Connected to:
10 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
11 With the Partitioning, OLAP, Data Mining and Real Application Testing options
12
13 SQL> startup
14 ORA-01081: cannot start already-running ORACLE - shut it down first
15 SQL>

Oh! looks like oracle instance is already up and running.
Let us check it out:

1 SQL> select * from dual;
2
3 D
4 -
5 X
6
7 SQL>

Next we’ll try loggin in to Oracle db instance using HR userid.

01 SQL> exit;
02 Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
03 With the Partitioning, OLAP, Data Mining and Real Application Testing options
04 oracle@domU-12-31-39-03-BD-92:[/home/oracle]
05 $ sqlplus hr/hr@orcl
06
07 SQL*Plus: Release 11.2.0.1.0 Production on Tue May 1 19:06:37 2012
08
09 Copyright (c) 1982, 2009, Oracle. All rights reserved.
10
11 ERROR:
12 ORA-12154: TNS:could not resolve the connect identifier specified
13
14
15 Enter user-name:

So, what is going on? DB instance is up, but I cannot login using one of the standard account HR.
ORA-12154 error indicates that TNS could not resolve @orcl identifier. Since I am using @ connection mechanism, client(sqlplus) requires TNSNAMES file to point to the DB instance. We don’t have that file yet.
Previous login attempt with “/ as sysdba” was NOT using TNSNAME file, but only LOCAL connection.
To connect to LOCAL DB instance (which is what we have), we should set ORACLE_SID environment variable to the value of SID. Our instance SID is “orcl“. So let see that…

01 oracle@domU-12-31-39-03-BD-92:[/home/oracle]
02 $ set ORACLE_SID=orcl
03 oracle@domU-12-31-39-03-BD-92:[/home/oracle]
04 $ sqlplus hr/hr
05
06 SQL*Plus: Release 11.2.0.1.0 Production on Tue May 1 19:15:12 2012
07
08 Copyright (c) 1982, 2009, Oracle. All rights reserved.
09
10 ERROR:
11 ORA-28000: the account is locked
12
13 Enter user-name:
14 ERROR:
15 ORA-01017: invalid username/password; logon denied
16 SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
17 oracle@domU-12-31-39-03-BD-92:[/home/oracle]
18 $

That is better… HR user can attempt, but the account is locked. Note we are connecting LOCAL (not using @) as HR user.
We’ll unlock the HR user and retry…

01 SQL> exit;
02 Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
03 With the Partitioning, OLAP, Data Mining and Real Application Testing options
04 oracle@domU-12-31-39-03-BD-92:[/home/oracle]
05 $ sqlplus / as sysdba
06
07 SQL*Plus: Release 11.2.0.1.0 Production on Tue May 1 20:26:32 2012
08
09 Copyright (c) 1982, 2009, Oracle. All rights reserved.
10
11
12 Connected to:
13 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
14 With the Partitioning, OLAP, Data Mining and Real Application Testing options
15
16 SQL> alter user hr account unlock;
17
18 User altered.
19
20 SQL> alter user hr identified by *******;
21
22 User altered.
23
24 SQL> exit;
25 Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
26 With the Partitioning, OLAP, Data Mining and Real Application Testing options
27 oracle@domU-12-31-39-03-BD-92:[/home/oracle]
28 $ sqlplus hr/hr
29
30 SQL*Plus: Release 11.2.0.1.0 Production on Tue May 1 20:27:15 2012
31
32 Copyright (c) 1982, 2009, Oracle. All rights reserved.
33
34
35 Connected to:
36 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
37 With the Partitioning, OLAP, Data Mining and Real Application Testing options
38
39 SQL>

Viola!!!
Note that we had already set the environment variable ORACLE_SID set to orcl; and with HR account unlocked we are now able login to DB with orcale account. how about remote login (using @)

01 SQL> exit;
02 Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
03 With the Partitioning, OLAP, Data Mining and Real Application Testing options
04 oracle@domU-12-31-39-03-BD-92:[/home/oracle]
05 $ sqlplus hr/hr@orcl
06
07 SQL*Plus: Release 11.2.0.1.0 Production on Tue May 1 20:30:48 2012
08
09 Copyright (c) 1982, 2009, Oracle. All rights reserved.
10
11 ERROR:
12 ORA-12154: TNS:could not resolve the connect identifier specified
13
14 oracle@domU-12-31-39-03-BD-92:[/home/oracle]
15 $

UH OH… we still do not have TNS resolver. Basically we do not have a TNSNAMES.ORA file in place, which is required for @ connection (remote connection).
Next blog entry we are going to attempt this.

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