Saturday, August 18, 2012

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.
 
1login as: oracle
2oracle@ec2-107-20-86-85.compute-1.amazonaws.com's password:
3Last login: Mon Apr 30 17:29:17 2012 from 98.235.186.132
4oracle@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?
01oracle@domU-12-31-39-03-BD-92:[/home/oracle]
02$ sqlplus / as sysdba
04SQL*Plus: Release 11.2.0.1.0 Production on Tue May 1 19:03:18 2012
05
06Copyright (c) 1982, 2009, Oracle. All rights reserved.
07
08
09Connected to:
10Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
11With the Partitioning, OLAP, Data Mining and Real Application Testing options
12
13SQL> startup
14ORA-01081: cannot start already-running ORACLE - shut it down first
15SQL>
Oh! looks like oracle instance is already up and running.
Let us check it out:
1SQL> select * from dual;
2
3D
4-
5X
6
7SQL>
Next we'll try loggin in to Oracle db instance using HR userid.
01SQL> exit;
02Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
03With the Partitioning, OLAP, Data Mining and Real Application Testing options
04oracle@domU-12-31-39-03-BD-92:[/home/oracle]
05$ sqlplus hr/hr@orcl
06
07SQL*Plus: Release 11.2.0.1.0 Production on Tue May 1 19:06:37 2012
08
09Copyright (c) 1982, 2009, Oracle. All rights reserved.
10
11ERROR:
12ORA-12154: TNS:could not resolve the connect identifier specified
13
14
15Enter 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...
01oracle@domU-12-31-39-03-BD-92:[/home/oracle]
02$ set ORACLE_SID=orcl
03oracle@domU-12-31-39-03-BD-92:[/home/oracle]
04$ sqlplus hr/hr
05
06SQL*Plus: Release 11.2.0.1.0 Production on Tue May 1 19:15:12 2012
07
08Copyright (c) 1982, 2009, Oracle. All rights reserved.
09
10ERROR:
11ORA-28000: the account is locked
12
13Enter user-name:
14ERROR:
15ORA-01017: invalid username/password; logon denied
16SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
17oracle@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...
01SQL> exit;
02Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
03With the Partitioning, OLAP, Data Mining and Real Application Testing options
04oracle@domU-12-31-39-03-BD-92:[/home/oracle]
05$ sqlplus / as sysdba
06
07SQL*Plus: Release 11.2.0.1.0 Production on Tue May 1 20:26:32 2012
08
09Copyright (c) 1982, 2009, Oracle. All rights reserved.
10
11
12Connected to:
13Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
14With the Partitioning, OLAP, Data Mining and Real Application Testing options
15
16SQL> alter user hr account unlock;
17
18User altered.
19
20SQL> alter user hr identified by *******;
21
22User altered.
23
24SQL> exit;
25Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
26With the Partitioning, OLAP, Data Mining and Real Application Testing options
27oracle@domU-12-31-39-03-BD-92:[/home/oracle]
28$ sqlplus hr/hr
29
30SQL*Plus: Release 11.2.0.1.0 Production on Tue May 1 20:27:15 2012
31
32Copyright (c) 1982, 2009, Oracle. All rights reserved.
33
34
35Connected to:
36Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
37With the Partitioning, OLAP, Data Mining and Real Application Testing options
38
39SQL>
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 @)
01SQL> exit;
02Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
03With the Partitioning, OLAP, Data Mining and Real Application Testing options
04oracle@domU-12-31-39-03-BD-92:[/home/oracle]
05$ sqlplus hr/hr@orcl
06
07SQL*Plus: Release 11.2.0.1.0 Production on Tue May 1 20:30:48 2012
08
09Copyright (c) 1982, 2009, Oracle. All rights reserved.
10
11ERROR:
12ORA-12154: TNS:could not resolve the connect identifier specified
13
14oracle@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.
Posted by
Labels: ,

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)