[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 9034
  • Last Modified:

Start multiple oracle databases on the same Oracle server?

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
dttai
Asked:
dttai
  • 6
  • 4
  • 2
  • +1
1 Solution
 
earth man2Commented:
You use script oraenv to set environment variable ORACLE_SID.

This point ORACLE to use a different database, each has its own SID.
0
 
earth man2Commented:
:
#
# 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
 
andertstCommented:
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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
konektorCommented:
$> 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
 
dttaiAuthor Commented:
@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
 
dttaiAuthor Commented:
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
 
andertstCommented:
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
 
earth man2Commented:
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
 
dttaiAuthor Commented:
@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
 
earth man2Commented:
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
 
earth man2Commented:
ORA-01902 SEGMENT keyword expected
    Cause: A keyword is missing.
    Action: Supply the missing keyword.
0
 
dttaiAuthor Commented:
@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
 
earth man2Commented:
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 6
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now