The Systems People Inc.

The Systems People Inc.

Customer Focused Service since 1993

Experiment with 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.

 

login as: oracle
oracle@ec2-107-20-86-85.compute-1.amazonaws.com's password:
Last login: Mon Apr 30 17:29:17 2012 from 98.235.186.132
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?

oracle@domU-12-31-39-03-BD-92:[/home/oracle]
$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue May 1 19:03:18 2012

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> startup
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL>

Oh! looks like oracle instance is already up and running.

Let us check it out:

SQL> select * from dual;

D
-
X

SQL>

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

SQL> exit;
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
oracle@domU-12-31-39-03-BD-92:[/home/oracle]
$ sqlplus hr/hr@orcl

SQL*Plus: Release 11.2.0.1.0 Production on Tue May 1 19:06:37 2012

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

ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified


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

oracle@domU-12-31-39-03-BD-92:[/home/oracle]
$ set ORACLE_SID=orcl
oracle@domU-12-31-39-03-BD-92:[/home/oracle]
$ sqlplus hr/hr

SQL*Plus: Release 11.2.0.1.0 Production on Tue May 1 19:15:12 2012

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

ERROR:
ORA-28000: the account is locked

Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied
SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus
oracle@domU-12-31-39-03-BD-92:[/home/oracle]
$

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

SQL> exit;
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
oracle@domU-12-31-39-03-BD-92:[/home/oracle]
$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue May 1 20:26:32 2012

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter user hr account unlock;

User altered.

SQL> alter user hr identified by *******;

User altered.

SQL> exit;
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
oracle@domU-12-31-39-03-BD-92:[/home/oracle]
$ sqlplus hr/hr

SQL*Plus: Release 11.2.0.1.0 Production on Tue May 1 20:27:15 2012

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

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

SQL> exit;
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
oracle@domU-12-31-39-03-BD-92:[/home/oracle]
$ sqlplus hr/hr@orcl

SQL*Plus: Release 11.2.0.1.0 Production on Tue May 1 20:30:48 2012

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

ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

oracle@domU-12-31-39-03-BD-92:[/home/oracle]
$

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.

 Blog Stats

  • Total posts(18)
  • Total comments(0)

Forgot your password?