20071110 one host with two oracle databases - plembo/onemoretech GitHub Wiki

title: One host with two Oracle databases link: https://onemoretech.wordpress.com/2007/11/10/one-host-with-two-oracle-databases/ author: lembobro description: post_id: 613 created: 2007/11/10 08:48:38 created_gmt: 2007/11/10 08:48:38 comment_status: open post_name: one-host-with-two-oracle-databases status: publish post_type: post

One host with two Oracle databases

So I know this must be child’s play to any Oracle DBA’s out there, but it’s all new to me.

I’ve got a test box, actually a CentOS Linux (Red Hat Enterprise clone) virtual machine, that’s loaded up with Oracle Identity Management and SOA Suite. My next project is to install and test Oracle Identity Manager, which also needs its own Oracle database. Although I briefly considered using the Metadata Repository behind OID for this, I decided to instead follow the explicit instructions in the OIM install guide and set up a separate one.

Following the specs given in the install guide I installed a copy of Oracle Database 10g Release 2 (10.2.0.1) and then ran the dbca (Database Creation Assistant) to create a new database. So far so good.

Once the database was created, the next thing to do was to get a listener configured for it so that local and remote users (applications) could connect to it. While it is possible to reconfigure an existing listener (the one for my infra stack) to do this, I decided to run a separate listener for this new database.

When running lsnrctl, it will connect to the default listener unless instructed to connect to another by name. In order to run a second listener, I needed to define it in an $ORACLE_HOME/network/admin/listener.ora file, which would also require corresponding tnsnames.ora and sqlnet.ora files in the same location.

My new listener.ora file looked like this:

# listener.ora
LISTENER1 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = myhost.mydomain.com)(PORT = 1522))
    )
  )
	
SID_LIST_LISTENER1 =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = newdb.mydomain.com)
      (ORACLE_HOME = /u01/app/oracle/product/db1)
      (SID_NAME = newdb)
    )
  )

My new tnsnames.ora looked like this:

#tnsnames.ora
NEWDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = myhost.mydomain.com)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = newdb.mydomain.com)
    )
  )

Finally, my sqlnet.ora file was like this:

# sqlnet.ora
NAMES.DIRECTORY_PATH= (TNSNAMES)

Before going any further I did a export ORACLE_SID=newdb so to set my environment properly (I had already set ORACLE_HOME to the proper path).

By doing a lsnrctl stop LISTENER1 and then a lsnrctl start LISTENER1 and then restarting the database (this allows it to register with the listener afresh), I was able to make the database available to local and remote clients. I tested this with sqlplus, connecting with a sqlplus SYSTEM/password@newdb.

The next order of business was to get the Enterprise Manager console working.

Of course it wasn’t working. What did you think, this stuff happens automagically?

As it was, the EM console was configured to look for the database on the default port 1521, so I had to reconfigure it to instead connect on port 1522. I did this with the following command:

[oracle@uranus bin]$ emca -config dbcontrol db -PORT 1522

and then followed the prompts to complete the task. Here’s the console output:

STARTED EMCA at Nov 8, 2007 10:31:52 PM
EM Configuration Assistant, Version 10.2.0.1.0 Production

Enter the following information:
Database SID: newsb
Database Control is already configured for the database oim1
You have chosen to configure Database Control for managing the database newdb
This will remove the existing configuration and the default settings and perform a fresh configuration
Do you wish to continue? [yes(Y)/no(N)]: y
Password for SYS user:
Password for DBSNMP user:
Password for SYSMAN user:
Email address for notifications (optional):
Outgoing Mail (SMTP) server for notifications (optional):
—————————————————————–

You have specified the following settings

Database ORACLE_HOME ……………. /u01/app/oracle/product/db1

Database hostname ……………. myhost.mydomain.com
Listener port number ……………. 1522
Database SID ……………. newdb
Email address for notifications ……………
Outgoing Mail (SMTP) server for notifications ……………

—————————————————————–
Do you wish to continue? [yes(Y)/no(N)]: y
Nov 8, 2007 10:32:27 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /u01/app/oracle/product/db1/cfgtoollogs/emca/newdb/emca_2007-11-08_10-31-52-PM.log.
Nov 8, 2007 10:32:30 PM oracle.sysman.emcp.DatabaseChecks performReposChecks
WARNING: ’shared_pool_size’ must be greater than or equal to 80 MB.
Nov 8, 2007 10:32:31 PM oracle.sysman.emcp.util.DBControlUtil stopOMS
INFO: Stopping Database Control (this may take a while) …
Nov 8, 2007 10:32:43 PM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) …
Nov 8, 2007 10:34:36 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
Nov 8, 2007 10:34:37 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>>>>>>> The Database Control URL is http://myhost.mydomain.com:1158/em <<<<<<<<<<<
Enterprise Manager configuration completed successfully
FINISHED EMCA at Nov 8, 2007 10:34:37 PM

That was it.