Solved

Start all the Listeners and databases on maintenance day

Posted on 2014-07-27
9
313 Views
Last Modified: 2014-08-12
My server has more than 30 databases on One single server. It is Oracle 11g rel 2 ( Non-RAC)
Each database is having its own listener( Never seen such a client before).
The maintenance day is a nightmare as I have to stop all the listeners one by one and each database and bring that up one by one after the maintenance is over.

I need something so that I can bring up all the listeners and the databases in one setting or script.
What is the best approach to achieve this task.
Experts ! I need help on this.
0
Comment
Question by:Oranew
9 Comments
 
LVL 12

Accepted Solution

by:
praveencpk earned 500 total points
ID: 40222942
you can write the startup and shutdown scripts which includes all your 30 database and listeners in it as shown in the example below for same oracle home.

startup.sh
#!/bin/bash

export TMP=/tmp
export TMPDIR=$TMP
export PATH=/usr/sbin:/usr/local/bin:$PATH
export ORACLE_HOME=<your_home>
export ORACLE_SID=<your_sid1>
# Start Listener1
lsnrctl start Listener1

# Start the first Database
sqlplus / as sysdba << EOF
STARTUP;
EXIT;
EOF

export ORACLE_SID=<your_sid2>
# Start Listener2
lsnrctl start Listener2

# Start the first Database
sqlplus / as sysdba << EOF
STARTUP;
EXIT;
EOF
...
..
..

and the shutdown script
#!/bin/bash

export TMP=/tmp
export TMPDIR=$TMP
export PATH=/usr/sbin:/usr/local/bin:$PATH
export ORACLE_HOME=<your_home>
export ORACLE_SID=<your_sid1>

# Stop the Database1
sqlplus / as sysdba << EOF
SHUTDOWN IMMEDIATE;
EXIT;
EOF

# Stop Listener1
lsnrctl stop Listener1

export ORACLE_SID=<your_sid2>

# Stop the Database2
sqlplus / as sysdba << EOF
SHUTDOWN IMMEDIATE;
EXIT;
EOF

# Stop Listener2
lsnrctl stop Listener2
....
..
..
.
0
 
LVL 4

Expert Comment

by:rastoi
ID: 40223003
Hi Praveencpk.
I bet, some "for" with listener/databases in extra file would be more elegant , even better would be let the script autodetect all the instances.
@ Oranew - the script above asumes UX like OS. Can you tell us your OS type and version?
0
 

Author Comment

by:Oranew
ID: 40223334
Sorry for not updating with the os version. It is Solaris 10
0
 
LVL 34

Expert Comment

by:johnsone
ID: 40223792
Assuming that the oratab file is set up correctly, you could use that to determine the instances on the system and which ones to start up.

However, it would be almost impossible to detect the listener names.

Then what if you upgrade some and not all databases.  You would be potentially running multiple ORACL_HOMEs, which the script would have to account for.  I would think that a combination of the oratab file and the oraenv script would get you the right environments set up for each database, but the listeners would still present a problem.
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:rastoi
ID: 40223828
I'm just windows guy, but assuming, that like on windows listener binary can be identified by name in list of running processes and mapped back to daemon using it.
So - enumerate listeners this way, save list, stop and after maintenance start listeners according saved list can be functional aproach.
And similar can be used to identify DBs too. It will grant, that after maintenance you will be starting only those which were running prior it.
0
 
LVL 34

Expert Comment

by:johnsone
ID: 40223842
While it would be possible to identify running listeners from a process list, there is no way to determine which ORACLE_HOME they are running from on a UNIX type OS.  I would assume that Windows is the same way.  If you are running multiple homes, then you need to use the correct version of lsnrctl to shut it down.

Then what if there is a database or listener that isn't running when you collect the list at shutdown?  How does that get restarted?
0
 
LVL 4

Expert Comment

by:rastoi
ID: 40223899
@jonsone
a) no, on windows I can query running process for full path of binary and as well calling command including its parameters, so i.e. command:
Get-WmiObject win32_process -filter "name like 'oracle%'" |select commandline
will return
 <full_path>\oracle.exe <SID>
b) If it was not running prior maintenance start, I expect it was intentional and starting it after maintenance can cause more harm then let if off like was prior system maintenance.
0
 
LVL 34

Expert Comment

by:johnsone
ID: 40223913
This is a real scenario that we see all the time.

Database1, listener down, database up.  Doing major structural changes and/or data migration before maintenance that affects all database on system.
Database2, running normally.

Now, when I go to do the full system maintenance, I don't want to have to bring the listener up for database1, just so it can be successfully upgraded.  Since the database is listed as a database that should be up (the oratab file has a flag for that), the listener should be started as well.

Full path of executable running can usually be found on UNIX systems, but varies greatly between OS and OS version.  Sometimes it is easy and sometimes nearly impossible.  That only gets me the full path of the executable.  I still need to set the rest of the environment.

Possibly use a script to hard code the start of the listeners and then use the dbstart script.  That should get all the databases at once if you want to rely on Oracle's script.
0
 
LVL 4

Expert Comment

by:rastoi
ID: 40224043
it is on Oranew to choose or specify his way. My initial entry was reffering to "hardcoded" list of DBs and their listeners, the autodetect part was just on the top of it.
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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Single ERP VS muttiple Application or Systems 6 62
Migrate database to ASM disks. 1 39
grouping on time windows 6 42
Web Service from a stored procdure oracle 10 49
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…
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to take different types of Oracle backups using RMAN.

910 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

19 Experts available now in Live!

Get 1:1 Help Now