Turing's Man Blog

Part 7. Checkpoint for Oracle Database 11gR2 installation. Configuration files, basic tools and common issues

Bookmark and Share

We had a short break after part 6 of this series. Believe it or not, there was a really important reason for me to stop writing for a while – although, this is quite a different story. Now, we're back to finish with our experiments with Oracle Database 11gR2 on Solaris 11.1. What was really nice, during the last two months I received some emails and comments with your suggestions and opinions on this series. Therefore, I would like to thank you all for positive feedback and additional inspiration. Now, we can come back to work.

 

I hope our virtual machines are ready to be run again. Before we begin with additional experiments, it's good to know which Oracle Database 11gR2 configuration files should be double-checked. Usually, if something is wrong with these files, the overall Oracle database installation is not working properly or – for example – a listener or database instance cannot be started. Also, we will present some basic administration tools for Oracle.

First of all, let's check listener.ora file. Please login to the oracle user account and execute the following command:

oracle@turingsman:~$ vi /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora

 

You should see something similar to this:

LISTENER=

  (DESCRIPTION=

    (ADDRESS_LIST=

      (ADDRESS=(PROTOCOL=tcp)(HOST=turingsman)(PORT=1521))

      (ADDRESS=(PROTOCOL=ipc)(KEY=extproc))))

SID_LIST_LISTENER=

  (SID_LIST=

    (SID_DESC=

      (GLOBAL_DBNAME=TURINGSMAN)

      (ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1)

      (SID_NAME=TURINGSMAN))

)

 

Now exit vi editor and execute the following command – to check how tnsnames.ora file is composed:

oracle@turingsman:~$ vi /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora

 

In my case it looks like that:

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

 

TURINGSMAN =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = turingsman)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = TURINGSMAN)

    )

  )

   

LISTENER_TURINGSMAN =

  (ADDRESS = (PROTOCOL = TCP)(HOST = turingsman)(PORT = 1521))

 

Also, always remember about .bash_profile file:

oracle@turingsman:~$ vi .bash_profile

 

In my case it looks like this:

ORACLE_BASE=/u01/app/oracle

ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1

ORACLE_SID=TURINGSMAN

LD_LIBRARY_PATH=$ORACLE_HOME/lib

PATH=$PATH:/usr/local/bin:$ORACLE_HOME/bin

 

export ORACLE_BASE ORACLE_HOME ORACLE_SID LD_LIBRARY_PATH PATH

 

And here is the set of useful commands, that has to be available from the command line, once your Oracle Database 11gR2 is installed and your oracle user shell is properly configured (see .bash_profile file).

 

Tool

Syntax

Purspose

Selected commands (<command>)

Example

lsnrctl

 

 

lsnrctl <command>

This tool is aimed to manage Oracle Database listener process (in short words: without working listener it won't be possible to connect to the Oracle database instance)

Status

Start

Reload

Stop

Version

services

Exit

Quit

Help

lsnrctl reload

sqlplus

sqlplus [[username[/password][@service_name] | / [as {sysdba|sysoper}]] | /nolog]

This tool is required to connect with the Oracle database from command line to execute SQL statements

help

sqlplus /nolog help

 

tnsping

tnsping <service_name> <how_many_times>

This tool helps to check if given Oracle Net service is available for connections. If connection succeeded, tnsping tool will show estimated time required for data transmission between client and server side.

 

tnsping TURINGSMAN 4

trcroute

Trcroute <service_name>

This tool helps to discover the trace of connection between client and server

 

trcroute TURINGSMAN

 

Let's have a short trip through the tools presented above. Imagine we have just logged in to our database server and we want to start all the Oracle Database 11gR2 components by hand. Be sure we are using oracle account – for example:

pwawrzyn@turingsman:~$ su - oracle
Password:
Oracle Corporation SunOS 5.11 11.1 September 2012
-bash-4.1$

 

Then, run sqlplus:

-bash-4.1$ sqlplus /nolog

 

When sqlplus is started, execute the commands presented below (your commands are marked bold):

SQL*Plus: Release 11.2.0.1.0 Production on Sun Mar 10 20:02:10 2013

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

SQL> conn sys as sysdba;
Enter password:
Connected to an idle instance.
SQL> startup
ORACLE instance started.

Total System Global Area 835104768 bytes
Fixed Size 2215624 bytes
Variable Size 562037048 bytes
Database Buffers 268435456 bytes
Redo Buffers 2416640 bytes
Database mounted.
Database opened.
SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION STARTUP_ STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT
----------------- -------- ------------ --- ---------- ------- ---------------
LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO
---------- --- ----------------- ------------------ --------- ---
1 TURINGSMAN
turingsman
11.2.0.1.0 13/03/10 OPEN NO 1 STOPPED
ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO


SQL> quit
Disconnected from Oracle Database 11g Release 11.2.0.1.0 - 64bit Production

 

Then, we should start Oracle listener process (commands to be executed are marked bold):

-bash-4.1$ lsnrctl

LSNRCTL for Solaris: Version 11.2.0.1.0 - Production on 10-MAR-2013 20:22:02

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

Welcome to LSNRCTL, type "help" for information.

 

LSNRCTL> status

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=turingsman)(PORT=1521)))

TNS-12541: TNS:no listener

 TNS-12560: TNS:protocol adapter error

  TNS-00511: No listener

   Solaris Error: 146: Connection refused

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=extproc)))

TNS-12541: TNS:no listener

 TNS-12560: TNS:protocol adapter error

  TNS-00511: No listener

   Solaris Error: 2: No such file or directory

LSNRCTL> start

Starting /u01/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...

 

TNSLSNR for Solaris: Version 11.2.0.1.0 - Production

System parameter file is /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora

Log messages written to /u01/app/oracle/diag/tnslsnr/turingsman/listener/alert/log.xml

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=turingsman)(PORT=1521)))

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=extproc)))

 

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=turingsman)(PORT=1521)))

STATUS of the LISTENER

------------------------

Alias                     LISTENER

Version                   TNSLSNR for Solaris: Version 11.2.0.1.0 - Production

Start Date                10-MAR-2013 20:22:12

Uptime                    0 days 0 hr. 0 min. 0 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora

Listener Log File         /u01/app/oracle/diag/tnslsnr/turingsman/listener/alert/log.xml

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=turingsman)(PORT=1521)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=extproc)))

Services Summary...

Service "TURINGSMAN" has 1 instance(s).

  Instance "TURINGSMAN", status UNKNOWN, has 1 handler(s) for this service...

The command completed successfully

LSNRCTL> reload

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=turingsman)(PORT=1521)))

The command completed successfully

LSNRCTL> quit

 

Now, let's try tnsping – we're working on local machine, so it's not a best example, but anyway:

-bash-4.1$ tnsping TURINGSMAN 4

TNS Ping Utility for Solaris: Version 11.2.0.1.0 - Production on 10-MAR-2013 20:23:03

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

Used parameter files:
/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = turingsman)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = TURINGSMAN)))
OK (20 msec)
OK (0 msec)
OK (0 msec)
OK (0 msec)

 

And finally, trcroute – with the same remark like for tnsping – we're on local machine:

-bash-4.1$ trcroute TURINGSMAN

Trace Route Utility for Solaris: Version 11.2.0.1.0 - Production on 10-MAR-2013 20:24:42

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

Route of TrcRoute:
------------------

Node: Client Time and address of entry into node:
-------------------------------------------------------------
10-MAR-2013 20:24:42 ADDRESS= PROTOCOL=TCP HOST=turingsman PORT=1521

Node: Server Time and address of entry into node:
-------------------------------------------------------------
10-MAR-2013 20:24:42 ADDRESS= PROTOCOL=TCP HOST=turingsman PORT=1521

 

If everything works similar way in your case, we can progress to the part 8 of this series. Finally, to summarize our checkpoint here, we should remember that if something is not working properly, the most common reasons are hidden in:

  • Improperly owned directories of Oracle installation (refer to the previous parts to check the guidelines on chown command). Remember – we're working on oracle user account. Root role is used very rare, mostly for operating system purposes and not the Oracle Database itself – with significant exception during Oracle Universal Installer part, when we have to execute root.sh script from root account.

  • Improperly assigned rights to the Oracle installation directories – please not only check you've applied chown command on all required directories, but also use took care to use chmod command (refer to the part 4).

  • Improperly defined or not set and exported Oracle Database environment variables – refer to the .bash_profile (this part) and double check you have all required variables and correctly defined paths. If yes, but you can't – for example – invoke the tools presented in this part, maybe you should reload .bash_profile or – which is quite common – remember to use BASH (maybe you're using oracle account with different shell, like CSH?).

  • Errors in the configuration files – usually, for basic Oracle Database 11gR2 configuration, we should check the files presented here (listener.ora, tnsnames.ora).

Alright. Now we're ready for part 8.

 

Bookmark and Share

Add comment


Security code
Refresh