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

I am just starting to find my feet with Oracle DB, having installed Oracle 11g R2: -

-rw-r--r--@  1 hayd  staff  1239269270 22 Mar 10:16 linux.x64_11gR2_database_1of2.zip
-rw-r--r--@  1 hayd  staff  1111416131 22 Mar 10:15 linux.x64_11gR2_database_2of2.zip

Now I knew that I had to set two environment-specific variables, using ~/.bashrc ( in my case ): -

export ORACLE_HOME=~/app/orauser/product/11.2.0/dbhome_1/
export ORACLE_SIDE=orcl

and yet I kept seeing: -

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

when I attempted to access the database using a command such as: -

$ORACLE_HOME/bin/sqlplus

or: -

$ORACLE_HOME/bin/sqlplus "/as sysdba"

even though I knew that the database was up and running: -

$ORACLE_HOME/bin/tnsping localhost

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 25-MAR-2013 15:59:37

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

Used parameter files:
/home/orauser/app/orauser/product/11.2.0/dbhome_1/network/admin/sqlnet.ora

Used EZCONNECT adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521)))
OK (0 msec)

Can you spot where I went wrong ?

The clue is in the .bashrc script: -

export ORACLE_HOME=~/app/orauser/product/11.2.0/dbhome_1/
export ORACLE_SIDE=orcl

For some reason, ORACLE_SIDE isn't quite as powerful as ORACLE_SID :-)

Once I fixed it and restarted my shell, I was in like Flynn: -

$ORACLE_HOME/bin/sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon Mar 25 16:37:42 2013

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

Connected to an idle instance.

SQL>

However, it wasn't all plain sailing - firstly, I was seeing: -

"ORA-27301: OS failure message: No space left on device"

in /home/orauser/app/orauser/product/11.2.0/dbhome_1/startup.log after starting Oracle: -

$ORACLE_HOME/bin/dbstart $ORACLE_HOME

That was relatively easily fixed, following this blog post: -

Ahmed Abd El Latif DBA Blog

Solution:

As root user, edit the /etc/sysctl.conf file and edit the kernel parameters

# semaphores: semmsl, semmns, semopm, semmni
kernel.sem = 250 32000 100 128

and then run this command

/sbin/sysctl -p

which did the job nicely :-)

Sadly, I'm still seeing: -

ORA-01034: ORACLE not available

i.e.

$ORACLE_HOME/bin/sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon Mar 25 16:37:42 2013

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

Connected to an idle instance.

SQL> SELECT name, db_unique_name FROM v$database;

SELECT name, db_unique_name FROM v$database
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0

The trick appeared to be in the message above: -

Connected to an idle instance.

From further Googling, I further adapted my .bashrc to include: -

export ORACLE_HOME=~/app/orauser/product/11.2.0/dbhome_1
export ORACLE_SID=orcl
export ORACLE_HOME_LISTNER=/home/orauser/app/orauser/product/11.2.0/dbhome_1/bin/tnslsnr
export PATH=$PATH:$ORACLE_HOME/bin

and restarted my shell.

This time, it all worked perfectly: -

sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon Mar 25 17:47:27 2013

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 name, db_unique_name FROM v$database;

NAME   DB_UNIQUE_NAME
--------- ------------------------------
ORCL   orcl

SQL> 

So, in conclusion, I now have a working Oracle environment.

PS I also worked out how to start the Enterprise Manager (EM): -

/home/orauser/app/orauser/product/11.2.0/dbhome_1/bin/emctl start dbconsole

Oracle Enterprise Manager 11g Database Control Release 11.2.0.1.0
Copyright (c) 1996, 2009 Oracle Corporation.  All rights reserved.
https://localhost:1158/em/console/aboutApplication
Starting Oracle Enterprise Manager 11g Database Control ...... started.
------------------------------------------------------------------
Logs are generated in directory /home/orauser/app/orauser/product/11.2.0/dbhome_1/localhost_orcl/sysman/log 

*UPDATE*

Thanks to this post: -

Auto Start/Shutdown Oracle Database 11g R2 on Linux

for an excellent start/stop/restart script: -

#!/bin/sh
#
# /etc/rc.d/init.d/oracle
# Description: Starts and stops the Oracle database, listeners and Enterprise Manager
# See how we were called.
case "$1" in
start)
echo "Starting Oracle"
echo "—————————————————-" >> /var/log/oracle
date +"! %T %a %D : Starting Oracle Databases as part of system up." >> /var/log/oracle
echo "—————————————————-" >> /var/log/oracle
echo -n "Starting Oracle Databases: "
su - orauser -c dbstart >> /var/log/oracle
echo "Done."
echo -n "Starting Oracle Listeners: "
su - orauser -c "lsnrctl start" >> /var/log/oracle
echo "Done."
echo -n "Starting Oracle Enterprise Manager: "
su - orauser -c "emctl start dbconsole" >> /var/log/oracle
echo "Done."
echo ""
echo "—————————————————-" >> /var/log/oracle
date +"! %T %a %D : Finished." >> /var/log/oracle
echo "—————————————————-" >> /var/log/oracle
touch /var/lock/subsys/oracle
;;
stop)
echo "Shutting Down Oracle"
echo "—————————————————-" >> /var/log/oracle
date +"! %T %a %D : Shutting Down Oracle Databases as part of system down." >> /var/log/oracle
echo "—————————————————-" >> /var/log/oracle
echo -n "Shutting Down Oracle Enterprise Manager: "
su - orauser -c "emctl stop dbconsole" >> /var/log/oracle
echo "Done."
echo -n "Shutting Down Oracle Listeners: "
su - orauser -c "lsnrctl stop" >> /var/log/oracle
echo "Done."
rm -f /var/lock/subsys/oracle
echo -n "Shutting Down Oracle Databases: "
su - orauser -c dbshut >> /var/log/oracle
echo "Done."
echo ""
echo "—————————————————-" >> /var/log/oracle
date +"! %T %a %D : Finished." >> /var/log/oracle
echo "—————————————————-" >> /var/log/oracle
;;
restart)
echo "Restarting Oracle"
echo "—————————————————-" >> /var/log/oracle
date +"! %T %a %D : Restarting Oracle Databases as part of system up." >> /var/log/oracle
echo "—————————————————-" >> /var/log/oracle
echo -n "Restarting Oracle Databases: "
su - orauser -c dbshut >> /var/log/oracle
su - orauser -c dbstart >> /var/log/oracle
echo "Done."
echo -n "Restarting Oracle Listeners: "
su - orauser -c "lsnrctl stop" >> /var/log/oracle
su - orauser -c "lsnrctl start" >> /var/log/oracle
echo "Done."
echo -n "Restarting Oracle Enterprise Manager: "
su - orauser -c "emctl stop dbconsole" >> /var/log/oracle
su - orauser -c "emctl start dbconsole" >> /var/log/oracle
echo "Done."
echo ""
echo "—————————————————-" >> /var/log/oracle
date +"! %T %a %D : Finished." >> /var/log/oracle
echo "—————————————————-" >> /var/log/oracle
touch /var/lock/subsys/oracle
;;
*)
echo "Usage: oracle {start|stop|restart}"
exit 1
esac