Solved

Start multiple oracle databases on the same Oracle server?

Posted on 2004-08-31
13
8,944 Views
Last Modified: 2012-06-27
Hello all,
I guess this is a simple question, but I am inexperienced with Oracle :).
Problem is I have two Orac databases and want to start both of them at the same time. How can I do that?
OS: linux Redhat
ODBS version: 10.1.0g

This is the procedure I used to start one of them
@sqlplus /nolog
@sqlplus / as sysdba
@startup pfile=<path for pfile>

I've tried to open another terminal to start another pfile for the other db, but it said 'connected'. I guess Oracle serer only allows one instance at a time. I've heard about Grid Control, is that what I need? If yes, please provide me with specific info.
Thanks,
Do
0
Comment
Question by:dttai
  • 6
  • 4
  • 2
  • +1
13 Comments
 
LVL 22

Expert Comment

by:earth man2
ID: 11947273
You use script oraenv to set environment variable ORACLE_SID.

This point ORACLE to use a different database, each has its own SID.
0
 
LVL 22

Expert Comment

by:earth man2
ID: 11947916
:
#
# chkconfig: - 95 35
# description: Starts and stops the Oracle database and listeners
# /etc/rc.d/init.d/oracle
# See how we were called.
ORACLE_HOME=/usr/local/oracle
case "$1" in
    start)
          echo -n "Starting Oracle Databases: "
          echo "----------------------------------------------------" >> /var/log/oracle
          date +"! %T %a %D : Starting Oracle Databases as part of system up." >> /var/log/oracle
          echo "----------------------------------------------------" >> /var/log/oracle
#          echo `expr 1024 \* 1024 \* 1024` > /proc/sys/kernel/shmmax
#          echo 250 32000 100 128 > /proc/sys/kernel/sem

          su - oracle -c $ORACLE_HOME/bin/dbstart >> /var/log/oracle
          echo "Done."
          echo -n "Starting Oracle Listeners: "
          su - oracle -c $ORACLE_HOME/bin/"lsnrctl start" >> /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 -n "Shutting Down Oracle Listeners: "
          echo "----------------------------------------------------" >> /var/log/oracle
          date +"! %T %a %D : Shutting Down Oracle Databases as part of system down." >> /var/log/oracle
          echo "----------------------------------------------------" >> /var/log/oracle
          su - oracle -c $ORACLE_HOME/bin/"lsnrctl stop" >> /var/log/oracle
          echo "Done."
          rm -f /var/lock/subsys/oracle
          echo -n "Shutting Down Oracle Databases: "
          su - oracle -c $ORACLE_HOME/bin/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 -n "Restarting Oracle Databases: "
          echo "----------------------------------------------------" >> /var/log/oracle
          date +"! %T %a %D : Restarting Oracle Databases as part of system up." >> /var/log/oracle
          echo "----------------------------------------------------" >> /var/log/oracle
          su - oracle -c $ORACLE_HOME/bin/dbstop  >> /var/log/oracle
          su - oracle -c $ORACLE_HOME/bin/dbstart >> /var/log/oracle
          echo "Done."
          echo -n "Restarting Oracle Listeners: "
          su - oracle -c $ORACLE_HOME/bin/"lsnrctl stop"  >> /var/log/oracle
          su - oracle -c $ORACLE_HOME/bin/"lsnrctl start" >> /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

# the above script uses dbstart to start all database instances defined in file /etc/oratab
# ie substitute Y for N at end of each line in oratab to control instance startup
# ensure ORACLE_HOME in script agrees to your installation.
#
# to make it run at startup issue following command as root $ chkconfig oracle on
# to test it without shutdown/startup as root $ service oracle stop          $ service oracle start
# PS I did not write this script.  I'm just passing it along.  All credit due to the real author.


0
 
LVL 4

Expert Comment

by:andertst
ID: 11949391
cat /etc/oratab

This will list your oratab file.  The important thing is to make certain that there are 2 (or more) different db names there.  Post the output so we know what database names you are trying to start.

Stephen
0
 
LVL 9

Expert Comment

by:konektor
ID: 11952204
$> export ORACLE_SID=DB1 -- syntax depends on OS and shell - simply set OS variable ...
$> sqlplus /nolog
SQL> conn sys as sysdba
SQL> startup

in some other terminal :

$> export ORACLE_SID=DB2
$> sqlplus /nolog
SQL> conn sys as sysdba
SQL> startup
0
 

Author Comment

by:dttai
ID: 11958325
@andertst:
I check /etc/oratab, and it only lists the default db. I've created two more dbs, but they aren't listed there. Should I edit this file to add these two newly created dbs?
Assume that's done. How can I start multiple db at once?
@earthman2: Sorry, I dont quite understand the script you post
@konektor: um, from what I read then an Oracle book said that when we invoke conn, the currently open db is closed. So that means DB1 is closed then-> we dont have multiple instances open at same time???
Sorry if I misunderstood anything.
0
 

Author Comment

by:dttai
ID: 11958584
Hello all,
Assume we can start multiple dbs. How can I check if they are indeed open?
When I start lsnrctl, and run status, I see some info about instances. Is this the only way to check which dbs is up and running?
Thanks,
Do
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 4

Expert Comment

by:andertst
ID: 11958969
lsnrctl only tells about the status of the listener.

ps -ef | grep pmon

Is one easy way to verify one of the key processes is running.

The db could be hung, so the best way is

sqlplus user/password@db1
select sysdate from dual;

sqlplus user/password@db2
select sysdate from dual;

Stephen
0
 
LVL 22

Accepted Solution

by:
earth man2 earned 100 total points
ID: 11959307
copy that script to /etc/init.d/oracle
change line that defines ORACLE_HOME to point to your Oracle installation
chmod +x /etc/init.d/oracle
chkconfig oracle on
service oracle start
0
 

Author Comment

by:dttai
ID: 11977380
@earthman2
When I run your oracle script, and look at the log file after that, I've got the following output:

/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
----------------------------------------------------
! 16:44:54 Fri 09/03/04 : Starting Oracle Databases as part of system up.
----------------------------------------------------
----------------------------------------------------
! 16:45:36 Fri 09/03/04 : Finished.
----------------------------------------------------
----------------------------------------------------
! 16:50:04 Fri 09/03/04 : Starting Oracle Databases as part of system up.
----------------------------------------------------

SQL*Plus: Release 10.1.0.2.0 - Production on Fri Sep 3 16:50:09 2004

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

SQL> Connected to an idle instance.
SQL> ORACLE instance started.

Total System Global Area  188743680 bytes
Fixed Size                 778036 bytes
Variable Size              162537676 bytes
Database Buffers         25165824 bytes
Redo Buffers                 262144 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced



SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

Database "archive" warm started.

SQL*Plus: Release 10.1.0.2.0 - Production on Fri Sep 3 16:50:22 2004

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

SQL> Connected to an idle instance.
SQL> ORACLE instance started.

Total System Global Area  188743680 bytes
Fixed Size                 778036 bytes
Variable Size              162537676 bytes
Database Buffers         25165824 bytes
Redo Buffers                 262144 bytes
ORA-01013: user requested cancel of current operation



SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

Database "diskdb" warm started.

LSNRCTL for Linux: Version 10.1.0.2.0 - Production on 03-SEP-2004 16:50:36

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

Starting /app/oracle/product/10.1.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 10.1.0.2.0 - Production
System parameter file is /app/oracle/product/10.1.0/db_1/network/admin/listener.ora
Log messages written to /app/oracle/product/10.1.0/db_1/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.1.0.2.0 - Production
Start Date                03-SEP-2004 16:50:36
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /app/oracle/product/10.1.0/db_1/network/admin/listener.ora
Listener Log File         /app/oracle/product/10.1.0/db_1/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
----------------------------------------------------
! 16:50:36 Fri 09/03/04 : Finished.
----------------------------------------------------

/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
P/S: please notice the two erros:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-01013: user requested cancel of current operation
I tried to ignore these error and do
sqlplus /nolog
conn /as sysdba
and get the message "Connected to an idle instance..."
I interpret as no db instance is created at all. Where did I go wrong, do you know?
Thanks,
Do
P/S: I also double the point, and intend to split for assisted answers.
0
 
LVL 22

Expert Comment

by:earth man2
ID: 11977858
Well you have now set up your system to startup multiple databases.  Whether your installation is actually valid that's another question.

Can you start your database manually ?
Can you start the other database instance manually ?

Have you configured enough swap space
Do you have enough RAM - how much ?

What version of  Red Hat ?
Kernel Version ?
0
 
LVL 22

Expert Comment

by:earth man2
ID: 11977882
ORA-01902 SEGMENT keyword expected
    Cause: A keyword is missing.
    Action: Supply the missing keyword.
0
 

Author Comment

by:dttai
ID: 11982619
@earthman2:
I am not so sure what you mean by "whether your installation is actually valid that's another question." Are you implying that the installation of Oracle may have something to do with these errors?
Yes, I've started my databases manually (one at a time).
My two databases are almost empty, so is SWAP space really important then? (I checked System Monitor and it said total SWAP is 1.9GB. Well, is SWAP for oracle is different, then I dont know).
I've got about 1GB of RAM.
version of Red Hat is Enterprise 3.
Kernel version is: 2.4.21-4.EL
Thanks,
Do
0
 
LVL 22

Expert Comment

by:earth man2
ID: 12001413
http://www.puschitz.com/TuningLinuxForOracle.shtml#SizingSwapSpace
You have to tune kernel parameters
ie /proc/sys/kernel/shmmax
SHMALL, SEMMSL, SEMMNI, SEMMNS, SEMOPM

These are documented for one database instance for two instances, well you've got to do some calculations.

Read your kernel documentation in /usr/src/linux/Documentation/vm to read about the best way of structuring your system memory for the initial allocation of Oracle SGA.


Stop Oracle from starting at system boot
chkconfig oracle off

Start database manually as root
service oracle start

If this gets rid of this ORA--01902 then you need to change the script line
# chkconfig: - 95 35

such that oracle is the last service to be started and the first service to be stopped.

I would have thought this error has something to do with a syntax error in the pfile.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now