dttai
asked on
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
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
:
#
# 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/ora
case "$1" in
start)
echo -n "Starting Oracle Databases: "
echo "-------------------------
date +"! %T %a %D : Starting Oracle Databases as part of system up." >> /var/log/oracle
echo "-------------------------
# 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 "-------------------------
date +"! %T %a %D : Finished." >> /var/log/oracle
echo "-------------------------
touch /var/lock/subsys/oracle
;;
stop)
echo -n "Shutting Down Oracle Listeners: "
echo "-------------------------
date +"! %T %a %D : Shutting Down Oracle Databases as part of system down." >> /var/log/oracle
echo "-------------------------
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 "-------------------------
date +"! %T %a %D : Finished." >> /var/log/oracle
echo "-------------------------
;;
restart)
echo -n "Restarting Oracle Databases: "
echo "-------------------------
date +"! %T %a %D : Restarting Oracle Databases as part of system up." >> /var/log/oracle
echo "-------------------------
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 "-------------------------
date +"! %T %a %D : Finished." >> /var/log/oracle
echo "-------------------------
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.
#
# 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/ora
case "$1" in
start)
echo -n "Starting Oracle Databases: "
echo "-------------------------
date +"! %T %a %D : Starting Oracle Databases as part of system up." >> /var/log/oracle
echo "-------------------------
# 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 "-------------------------
date +"! %T %a %D : Finished." >> /var/log/oracle
echo "-------------------------
touch /var/lock/subsys/oracle
;;
stop)
echo -n "Shutting Down Oracle Listeners: "
echo "-------------------------
date +"! %T %a %D : Shutting Down Oracle Databases as part of system down." >> /var/log/oracle
echo "-------------------------
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 "-------------------------
date +"! %T %a %D : Finished." >> /var/log/oracle
echo "-------------------------
;;
restart)
echo -n "Restarting Oracle Databases: "
echo "-------------------------
date +"! %T %a %D : Restarting Oracle Databases as part of system up." >> /var/log/oracle
echo "-------------------------
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 "-------------------------
date +"! %T %a %D : Finished." >> /var/log/oracle
echo "-------------------------
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.
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
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
$> 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
$> 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
ASKER
@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.
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.
ASKER
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
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
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@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
TNSLSNR for Linux: Version 10.1.0.2.0 - Production
System parameter file is /app/oracle/product/10.1.0
Log messages written to /app/oracle/product/10.1.0
Listening on: (DESCRIPTION=(ADDRESS=(PRO
Listening on: (DESCRIPTION=(ADDRESS=(PRO
Connecting to (DESCRIPTION=(ADDRESS=(PRO
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
Listener Log File /app/oracle/product/10.1.0
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PRO
(DESCRIPTION=(ADDRESS=(PRO
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.
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
TNSLSNR for Linux: Version 10.1.0.2.0 - Production
System parameter file is /app/oracle/product/10.1.0
Log messages written to /app/oracle/product/10.1.0
Listening on: (DESCRIPTION=(ADDRESS=(PRO
Listening on: (DESCRIPTION=(ADDRESS=(PRO
Connecting to (DESCRIPTION=(ADDRESS=(PRO
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
Listener Log File /app/oracle/product/10.1.0
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PRO
(DESCRIPTION=(ADDRESS=(PRO
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.
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 ?
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 ?
ORA-01902 SEGMENT keyword expected
Cause: A keyword is missing.
Action: Supply the missing keyword.
Cause: A keyword is missing.
Action: Supply the missing keyword.
ASKER
@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
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
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/Documentati
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.
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/Documentati
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.
This point ORACLE to use a different database, each has its own SID.