Introduction to new Oracle users and developers at CERN

Are a new Oracle database user or developer at CERN? Please read on.
First of all, welcome to Oracle support services! The Physics Databases team is reachable at phydb.support@cern.ch to help you. Below you will find a few references that we recommend to bring your development with CERN DB services up to speed: selected references to Oracle documentation, and how to connect to Oracle.

Links to Documentation

Oracle software comes together with extensive documentation. In case of RDBMS 10gR2 there are ~50k pages of documentation. It is well structured: there are books for both DBAs and developers.

For developers we recommend:

  • Application Developer's Guide - Fundamentals
  • Concepts
  • SQL Reference
  • PL/SQL User's Guide and Reference

Where to find Oracle documentation

Connect to Oracle DBs

There are several ways to connect to a database. The basic way is using SQLPlus, which is an application that comes with Oracle client installation and is also available from lxplus at CERN.

Basic concepts to know:

  • local_name - local name/db alias describing how to connect to database. Also referenced as the connect descriptor.
  • host - name of node(s) hosting the database
  • service_name - for clustered DBs, a service can be provided by one or more physical nodes

Using local naming (db aliases):

You have a environment variable $TNS_ADMIN pointing to a directory. In that directory you have a file tnsnames.ora with several entries in the format:
<local_name> = (DESCRIPTION=
                 (ADDRESS=(PROTOCOL=tcp)(HOST=<host1>) PORT=1521))
                 (ADDRESS=(PROTOCOL=tcp)(HOST=<host2>) PORT=1521))
                 (CONNECT_DATA= (SERVICE_NAME=<service_name>)))

You can connect by using:

   sqlplus user/password@<local_name>

The local_name can be different in any machine, as the name says, is a local name. You have to check in the $TNS_ADMIN/tnsnames.ora file of the local machine you are connecting from.

NOTE: local naming (tnsnames.ora) is the recommended method to connect to RACs

Using easy connect:

You don't need to have neither $TNS_ADMIN or tnsnames.ora file. However this only works if you use SQLPlus version >= 10.x . Another disavantage is that you cannot specify more than one host name (which can exist in case of Oracle RACs/clusters)

You can connect by using:

   sqlplus user/password@//<host>:<port>/<service_name>

More advices


This topic: PSSGroup > PhysicsDatabasesSection > BasicConceptsReference
Topic revision: r5 - 2007-11-23 - LucaCanali
 
This site is powered by the TWiki collaboration platform Powered by PerlCopyright & 2008-2020 by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
or Ideas, requests, problems regarding TWiki? use Discourse or Send feedback