This page describes the process of creating/importing multiple Oracle databases on a dual-homed machine. The database is accessed from both interfaces and a new Oracle listener is configured.

Exporting an Oracle database:

For creating a dump off all the contents of a database the exp script can be used ($ORACLE_HOME/bin/exp). It uses sqlplus for connecting to the database and dumping the contents so a valid username/password is needed. Also the user must have enough permissions. The usage is (in interactive mode):

        exp username@database

On lxplus the exp tool is not in the $PATH by default. For accessing it, the environment must be updated using the setOraEnv.sh script (path???) or the ORACLE_HOME variable must be manualy set (e.g. export ORACLE_HOME=/afs/cern.ch/project/oracle/linux64/10202) and exp used with the full path (e.g. /afs/cern.ch/project/oracle/linux64/10202/bin/exp).

Note: The exporting user might not have enough priviledges for exporting all the informations about the available users/profiles. In this case when the database is imported care must be taken to recreate the user, priviledges and profiles again. For example when exporting the lhcb_integration database the creation of all users can not be exported (most probably because the profiles of the users can not be accessed) but the grant statements of the users can be.

Importing an Oracle database:

• I. Install Oracle Database using the java installer tools provided.

• II. In case of using ASM for the database (if not skip to step III):
  • i) Create/configure raw devices needed by the ASM filesystem.

• • ii) Using dbca configure the ASM storage using the available devices.

Problem: the devices assigned to ASM need to be owned by oracle:oinstall but are recreated by root after each reboot. Currently the permissions must be reset after each reboot!

After running the dbca tools the configuration files listener.ora and tnsnames.ora should contain entries like:

      LISTENER_+ASM =
           (ADDRESS_LIST =
                   (ADDRESS = (PROTOCOL = TCP)(HOST = lbora01.cern.ch)(PORT = 1525))
                   (ADDRESS = (PROTOCOL = TCP)(HOST = lbora01.cern.ch)(PORT = 1522))
                   (ADDRESS = (PROTOCOL = TCP)(HOST = lbora01.cern.ch)(PORT = 1521))
                   (ADDRESS = (PROTOCOL = TCP)(HOST = lbora01.cern.ch)(PORT = 1523))
                   (ADDRESS = (PROTOCOL = TCP)(HOST = lbora01.cern.ch)(PORT = 1524))
           )

If you want the connection to the ASM database to be on a specific port the entries in the files must be modified to the appropiate values. Also if one needs access from the local machine be specifing the database name (e.g. sqlplus sys@+ASM as sysdba), then another entry must be added into the tnsnames.ora file. For example:

             +ASM =
                  (DESCRIPTION =
                            (ADDRESS = (PROTOCOL = TCP)(HOST = ora01.lbdaq.cern.ch)(PORT = 1525))
                            (CONNECT_DATA =
                                     (SERVER = DEDICATED)
                                     (SERVICE_NAME = +ASM)
                            )
                )

Note: Do not enter passwords containg spaces, tabs or any other whitespace character for the ASM management password! For more details about configuring the listener see Configuring a New Listener and Configuring EM.

• III.
  • i) Create database using the dbca tool choosing ASM if neccesary. For avoiding confusions all database names should be upper case.
  • ii) Configure listener
  • iii) Configure EM for the new database

• IV. If needed, recreate user, permission, profiles prior to importing the database.

• V. Run the import script ($ORACLE_HOME/bin/imp) using the dump file of the database (.dmp). For running in interactive mode:

        imp username@new_database

Usually for avoiding any right restrictions the username can be sys. The command:

        imp sys@<new_database> as sysdba 

will NOT work from the first try, because as sysdba is not a valid 'imp' script option. However the connection to the database is created and at the second retry, by entering the username "sys as sysdba", it will be passed directly to sqlplus. For example:

        [ora01] /home/oracle > imp sys@CALODB as sysdba
        LRM-00108: invalid positional parameter value 'as'
        IMP-00022: failed to process parameters, type 'IMP HELP=Y' for help
        IMP-00000: Import terminated unsuccessfully

Does not work. But the command sequence:

        [ora01] /home/oracle > imp sys@MY_DB
        Import: Release 10.2.0.1.0 - Production on Wed Apr 4 10:43:52 2007
        Copyright (c) 1982, 2005, Oracle.  All rights reserved.

        Password: ********

        IMP-00058: ORACLE error 28009 encountered
        ORA-28009: connection as SYS should be as SYSDBA or SYSOPER
        
        Username: sys as sysdba
        Password: ********

        Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
        With the Partitioning, OLAP and Data Mining options

        Import file: expdat.dmp >

works.

Note: In case the same DB is imported again avoidance of row duplication is not guaranteed in case the database constraints are not correctly set up or various triggers exist!

Note: Unfortunately there is no possibility to drop the whole database (tables, users, sequences, profiles, etc) with out dropping the tablespaces. Everything must be done by hand or scripted using the information from the oracle data dictionary (use the script $ORACLE_HOME/sql_scripts/drop_all_objects.sql for this).

Configuring a new listener:

• I.Server side configuration: For each new database a new listener can be configured or the database name can be register with an already existing listener. Choosing the first possibility gives the possibility of better separating the management of the two databases (mostly because of the management of access control). A new listener can be created by adding the configuration lines into the listener.ora file ($ORACLE_HOME/network/admin/listener.ora). For example:

        SID_LIST_LISTENER_CALODB =
                (SID_LIST =
                (SID_DESC =    
                        (ORACLE_HOME = /u01/app/oracle/ora10)
                        (SID_NAME = CALODB)
                )
          )
          LISTENER_CALODB =
                (DESCRIPTION_LIST =
                (DESCRIPTION =
                        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC4))
                        (ADDRESS = (PROTOCOL = TCP)(PORT = 1524))
                 )
          )

Afterwards the listener is controlled using lsnrctrl. For example:

        lsnrctrl stop LISTENER_CALODB
        lsnrctrl start LISTENER_CALODB
        lsnrctrl reload LISTENER_CALODB
        lsnrctrl status LISTENER_CALODB

• II. Client side configuration: The client can connect to the new database using the full identifier string in the form: /@:/ The database can be added to the tnsnames.ora and a connect string in the form @ can be used. Example of entry in tnsnames.ora:

           CALODB =
                  (DESCRIPTION =
                          (ADDRESS = (PROTOCOL = TCP)(HOST = ora01.lbdaq.cern.ch)(PORT = 1524))
                          (CONNECT_DATA =
                                   (SERVER = DEDICATED)
                                   (SERVICE_NAME = CALODB)
                           )
                   )
          LISTENER_CALODB =
                  (ADDRESS_LIST =
                          (ADDRESS = (PROTOCOL = TCP)(HOST = ora01.lbdaq.cern.ch)(PORT = 1524))
                  )
  

Configuring Enterprise Manager (EM):

The Enterprise Manager application can be controlled using the emctl and emca control tools. In the simple case where just one database is present the EM can be started by:

        export ORACLE_SID=db_name && emctl start dbconsole.

Note: The EM is installed at database creation time and unfortunatelly the hostname is hardcoded inside some configuration paths. If the hostname changes (or in a dual-home environment the primary host name is changed) the EM will fail to start. The "easy" sollution to this issue is soft linking the new search paths to the old directories.

In the general case when multiple listeners and databases are present each EM must be configured to: - Start on a specific port (otherwise the port number depends on the order the EM's are started up). - Connect to the listener on a specific port (different from the default 1521).

The emca tool can be used for this. For example:

        emca -reconfig ports -PORT 1524 -DBCONTROL_HTTP_PORT 5504 

will configure the new EM to accept HTTP connections on port 5504 and connect to the listener on 1524. Unfortunatelly only the first connection to the database is created on port 1524 (the rest on 1521) so the configuration file emoms.properties must be edited by hand. The usual file path is:

       $ORACLE_HOME/hostname_dbname/sysman/config/emd.properties

Configuring ISqlPlus:

Similar approach as with EM. Edit configuration files to change the default HTTPD port and Listener.

-- RaduStoica - 04 Apr 2007