Oracle DB - Broken, now Fixed

So I managed to break my Oracle 11g database, and have now fixed it.

The symptom was that, although Oracle was started, it wasn't really started.

I kept getting "Connected to an idle instance" and "ORA-01034: ORACLE not available" as per the following: -

 

sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri Apr 18 19:53:56 2014

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

Connected to an idle instance.

SQL> SELECT owner, table_name FROM dba_tables where owner like 'IBMBUSSP';

SELECT owner, table_name FROM dba_tables where owner like 'IBMBUSSP'

*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0

A quick Google search later, I found this: -

ORA-01034: ORACLE Not Available tips

which said, in part: -

…

ORA-01034 is thrown during the attempt to upgrade because there is an invalid entry in one of the following files:

• /etc/oratab

• /var/opt/oracle/oratab

...

As root, I checked for oratab: -

locate oratab

/etc/oratab
/home/oracle/app/oracle/product/11.2.0/dbhome_1/install/oratab

so I did a quick compare/contrast: -

diff /etc/oratab /home/oracle/app/oracle/product/11.2.0/dbhome_1/install/oratab

1c1
< orcl:/home/oracle/app/oracle/product/11.2.0/dbhome_1/:Y
---
> orcl:/home/oracle/app/oracle/product/11.2.0/dbhome_1:Y

Can you spot the difference ?

Yes, the trailing slash character …

A quick edit ( using vi of course ) of /etc/oratab later, plus a reboot to ensure that Oracle started cleanly and …. c'est voila

sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri Apr 18 19:59:41 2014

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

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

SQL> SELECT owner, table_name FROM dba_tables where owner like 'IBMBUSSP';

OWNER        TABLE_NAME
------------------------------ ------------------------------
IBMBUSSP        NAVNODE_LOD
IBMBUSSP        NAVNODE_DD
IBMBUSSP        NAV_TREE
IBMBUSSP        COMMUNITY_DEF
IBMBUSSP        ACL
IBMBUSSP        SPACENODE
IBMBUSSP        SPACENODE_LOD
…

Do you want to know the worst bit ??

Yes, I hit the same problem back in March 2013: -

Oracle Database - My First Few Baby Steps ....