Toad World® Forums

Running a script on multiple databases


#1

All,

One of my products is installed on 22 databases. Whenever I need to put a change into production, I have to logon to each of these databases, execute the install script, and verify results.
This is all quite time-consuming, and the maintenance window is rather limited.

I’m looking for a solution to automate the install process. That is, the installation is 100% scripted. I’d like to be able to automate the top-level process that executes the install script using a selected list of database connections.
Is there a Toad feature that might help me achieve this goal?

Thanks in advance,
Abe Kornelis


#2

I would look at Script Manager under Utilities -> Script Manager. You can add many databases, spool the output, schedule the run, …


#3

as stated above, I did try the script runner. It could be me, but I seem unable to get it to work.
Or maybe I don’t quite understand how to work with the script manager. That’s also a viable theory :wink:

I created a test script, then tried to run it on just one connection.
Script runner insisted on running it on all connections. So, how does it work?
Where can I find more info to better undrstand what to do and how to make it work for me?

Thanks in advance,
Abe


#4

Not script runner, script manager. You can find it under Utilities -> Script Manager. Once there, press the F1 key for help.


#5

Gregory,

thank you for trying to help me. I’m trying to use script manager. Sorry for the typo in my prior post.

I checked the help file, but failed to find any hint on how to actually execute any script at all.
I checked th eoptions, but found no clue therer either.

I tried right-clicking my test script in het scripts tab, but the pop-up window does not have an execute option.
I also tried right-clicking a connection - same result.
There is a script runner icoon on the script manager icon bar, but you indicated it should not be needed/used.

I’m sorry, I’m still lost.

Abe


#6

If you have your connections set to use and the scripts added, press the Run Selected Scripts button on the toolbar

To schedule them to run at a scheduled time, press the schedule button.

From: kornelis.abe [mailto:bounce-kornelisabe@toadworld.com]

Sent: Tuesday, May 12, 2015 9:57 AM

To: toadoraclebeta@toadworld.com

Subject: RE: [Toad for Oracle - Beta Discussion Forum] Running a script on multiple databases

RE: Running a script on multiple databases

Reply by kornelis.abe

Gregory,

thank you for trying to help me. I’m trying to use script manager. Sorry for the typo in my prior post.

I checked the help file, but failed to find any hint on how to actually execute any script at all.

I checked th eoptions, but found no clue therer either.

I tried right-clicking my test script in het scripts tab, but the pop-up window does not have an execute option.

I also tried right-clicking a connection - same result.

There is a script runner icoon on the script manager icon bar, but you indicated it should not be needed/used.

I’m sorry, I’m still lost.

Abe

===

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - Beta
notifications altogether.

Toad for Oracle - Beta Discussion Forum

Flag
this post as spam/abuse.


#7

Gregory,

I really appreciate you’re trying to help me. Unfortunately, the pictures do not show in my browser (neither IE, nor Google).
Ah - they do appear in this reply panel. Lucky me …

I understand I should set my connections to a “use” status. But the connections panel shows only 3 columns: User, Database, Home.
No “use” column is provided. I went back to Toad 12.6, same thing. I cannot find a way to select which connections from the list need to be activated for executing the script.
I do not see a “use” column in your example either.

Then, if I click the “Schedule button”, Toad insists on scheduling the script on all connections. So still no option to select which connections to use/skip.

It feels like I’m trying to make an incision using a sledge hammer. This may be a fine tool for your job.
but unless I’m missing a very important clue, I’m beginning to think it is not very well aligned with my job.

Thanks again for trying to help me
Abe.


#8

Abe,

You don’t select the connections to use, you simply add only the ones you want to execute them against to the list. Then you add the scripts you want to
execute against those connections. Then you either execute or schedule it.

Under the Script Manager Options -> Connections, you can select save and load from each data file. This allows you to configure a set of connections to operate
on a set of scripts.

From: kornelis.abe [mailto:bounce-kornelisabe@toadworld.com]

Sent: Tuesday, May 12, 2015 10:36 AM

To: toadoraclebeta@toadworld.com

Subject: RE: [Toad for Oracle - Beta Discussion Forum] Running a script on multiple databases

RE: Running a script on multiple databases

Reply by kornelis.abe

Gregory,

I really appreciate you’re trying to help me. Unfortunately, the pictures do not show in my browser (neither IE, nor Google).

Ah - they do appear in this reply panel. Lucky me …

I understand I should set my connections to a “use” status. But the connections panel shows only 3 columns: User, Database, Home.

No “use” column is provided. I went back to Toad 12.6, same thing. I cannot find a way to select which connections from the list need to be activated for executing the script.

I do not see a “use” column in your example either.

Then, if I click the “Schedule button”, Toad insists on scheduling the script on all connections. So still no option to select which connections to use/skip.

It feels like I’m trying to make an incision using a sledge hammer. This may be a fine tool for your job.

but unless I’m missing a very important clue, I’m beginning to think it is not very well aligned with my job.

Thanks again for trying to help me

Abe.

===

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - Beta
notifications altogether.

Toad for Oracle - Beta Discussion Forum

Flag
this post as spam/abuse.


#9

Hi Abe-

In addition to Greg’s recommendation, you can also use the Execute Script Action with a Parameter file to run one or more scripts against many databases.

See Utilities > Automation Designer > DB Misc tab > Execute Script Action.

Once you set up the files to execute in the Action, right-click on it, “Create parameter file.”

Then open the parameter file and make the modifications you need for your connections, e.g.

ConnectionCount=22

Connection_1=BLAH@HAH1

Connection_2=BLAH@HAH2

Connection_3=BLAH@HAH3

Connection_4=BLAH@HAH4

Connection_5=BLAH@HAH5

Connection_6=BLAH@HAH6

Then when you right-click on the Action, choose “Run with parameter file” and choose your parameter file.

You can then do all this from the command-line as well, schedule it, and so on. (Toad.exe -a “your action | your param file”)

Mark

From: kornelis.abe [mailto:bounce-kornelisabe@toadworld.com]

Sent: Tuesday, May 12, 2015 10:36 AM

To: toadoraclebeta@toadworld.com

Subject: RE: [Toad for Oracle - Beta Discussion Forum] Running a script on multiple databases

RE: Running a script on multiple databases

Reply by kornelis.abe

Gregory,

I really appreciate you’re trying to help me. Unfortunately, the pictures do not show in my browser (neither IE, nor Google).

Ah - they do appear in this reply panel. Lucky me …

I understand I should set my connections to a “use” status. But the connections panel shows only 3 columns: User, Database, Home.

No “use” column is provided. I went back to Toad 12.6, same thing. I cannot find a way to select which connections from the list need to be activated for executing the script.

I do not see a “use” column in your example either.

Then, if I click the “Schedule button”, Toad insists on scheduling the script on all connections. So still no option to select which connections to use/skip.

It feels like I’m trying to make an incision using a sledge hammer. This may be a fine tool for your job.

but unless I’m missing a very important clue, I’m beginning to think it is not very well aligned with my job.

Thanks again for trying to help me

Abe.

===

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - Beta
notifications altogether.

Toad for Oracle - Beta Discussion Forum

Flag
this post as spam/abuse.


#10

Gregory and Mark,

I will have to do some more experimenting when time permits.
I’m confident your answers will help me find an appropriate method to further automate at least some of these household chores.

Thanks for your help and advice,
Abe.


#11

what is your picture in a case “doesn’t work”?

You get a bunch of really worth full information and all we get from your “it is not working”.

Send pictures where you are when “it is not working”.

This would be a t least nice respond to all nice people trying to help you, in IMHO, pretty simple problem.


#12

Damir, all,

in my last post, I alluded to a time problem. Actually, they’ve dumped two big projects on my desk…

Anyway, I did try once again.

Issue 1: conenctions are not saved in the sdf file.

Then I click the diskette icon “Save datafile as” to save current settings.
Looking into the saved file I see:
scrptmgr2.png

which is not much of a connection list.
I tried reloading the data file, and indeed the defined connections are not reloaded.

I would like to have an easy way of defining (read: save/reload) lists of applicable connections.
Is that possible with Script Manager?

Issue 2:I cannot add scripts in the lower window, unless the list of connections is empty.
I would expect to be able to change the lsit of scipts whether or not connections are defined for later use.
This was one of the things I could not “get to work”. At least I now know to define the scripts in the lower window part
before defining connections in the upper one.

Issue 3: I cannot start script manager, until I’ve opened at least one session to a database.
My plan was to use script manager to log on, execute scripts, log off.
It seems it has not been designed to work this way. Can I achieve this at all?

Some additional background info: I’ve got one hour a week for applying maintenance.
With the UTL_KRG schema being installed on ever more databases, the time window is getting small-ish.
So I need to find a way to speed up the implementation/upgrade process.
It also means I have four more or less fixed lists of connections: 1 for DEV, TEST, ACCeptance, PROD each.

Kind regards,
Abe Kornelis.


#13

Hi,

Just in case you find it useful, I’ve uploaded my own launcher (both for .sh and .sql scripts) at www.unix.com/…/256021-windowss-batch-launcher-oracle-sql-linux-sh-scripts-available-here.html

Besides launcher.cmd, you need to create the files:

launcher-databases.txt (with a list of the tnsnames to use, for .sql scripts)

launcher-servers.txt (with a list of dns/IP of the servers to use, for .sh scripts)

Regards,


#14

Fr3dy,

This is very close to what I was looking for. I’ve done some testing and I’m convinced I can tweak it to do what I need it to do. it’s simply Great!

Thanks a lot!

Abe


#15

Fr3dy,

Thanks once more for your suggested solution.

For the sake of completeness, below you’ll find the version I have crafted.

Kind regards,
Abe Kornelis.

@ECHO OFF

rem Script Launcher by Fr3dY v1.5
rem ##############################
rem Version History:
rem 1.5c (Abe Kornelis) determine path for PLINK only if .sh script requested
rem 1.5b (Abe Kornelis) added support for optional parms to indicate the script and db/server filenames
rem 1.5a (Abe Kornelis) adapted to Kramp environment, added showlog routine
rem 1.5 - Error messages are saved in the log file (instead stdout)
rem 1.4 - Misc. fixes
rem 1.3 - Merged with ‘server launcher’, now accepts both SQL and SHELL SCRIPTS
rem 1.2 - Interactive prompt to show the file on screen
rem 1.1 - No need to add ‘quit;’ or ‘exit;’ in the .sql file anymore
rem Fixed sqlplus waiting for username/password if the first attempt was unsuccessful
rem Log file is generated automatically, including date and time in name
rem Misc. fixes
rem 1.0 - Initial Version

:SETUP
set listfile=
set scriptfile=
if %1. EQU . goto MAIN
if exist %1.txt set listfile=%1
if exist %1.sql set scriptfile=%1
if exist %1.sh set scriptfile=%1
exho l=%listfile%. s=%scriptfile%.

if %2. EQU . goto MAIN
if exist %2.txt set listfile=%2
if exist %2.sql set scriptfile=%2
if exist %2.sh set scriptfile=%2

:MAIN
if %1. EQU . echo Optionally, you can enter as parameters the name of a script file and/or a targets file (both without extension)
::List with TNS NAMES
if defined listfile (if exist %listfile%.txt (set dbservers=%listfile%.txt) else (set dbservers=launcher-databases.txt)) else (set dbservers=launcher-databases.txt)
::List with LINUX SERVERS
if defined listfile (if exist %listfile%.txt (set linuxservers=%listfile%.txt) else (set linuxservers=launcher-databases.txt)) else (set linuxservers=launcher-databases.txt)

set dt=%DATE:~9,4%%DATE:~6,2%%DATE:~3,2%__%TIME:~0,2%%TIME:~3,2%%TIME:~6,2%
set dt=%dt: =0%

if defined scriptfile (
if exist %scriptfile%.sql (
set servers=%dbservers%
set extension=sql
set mode=DB
) else (
set servers=%linuxservers%
set extension=sh
set mode=OS
)
goto CHECKSERVERS
)
echo Choose launcher mode:
echo 1) Database scripts (.sql files)
echo 2) Shell scripts (.sh files)
set /p launchermode="Enter desired mode: "
echo.

if %launchermode%==1 (
set extension=sql
set servers=%dbservers%
set mode=DB
) else (
if %launchermode%==2 (
set extension=sh
set servers=%linuxservers%
set mode=OS
) else (echo “Incorrect value, exiting…” & goto :END)
)

:CHECKSERVERS
if exist %servers% (
goto :LISTFILES
) else echo FILE %servers% NOT FOUND, ABORTING & goto :END

:LISTFILES
if defined scriptfile set file=%scriptfile%& goto confirmexec
echo Listing *.%extension% files…
echo.
dir /on /b *.%extension%
echo.

set /p file=Name of the file to be launched (without extension)?

if exist %file%.%extension% (
goto :CONFIRMSHOW
) else echo FILE %file%.%extension% NOT FOUND, ABORTING & goto :END

:CONFIRMSHOW
echo.
set /p confirm=Show the script %file%.%extension% on screen now? (Y/N)
if /I %confirm%==y (
goto :SHOWSCRIPT
) else goto :CONFIRMEXEC
echo.

:SHOWSCRIPT
echo.
echo Content of %file%.%extension%
echo ######################
type %file%.%extension%
echo.
echo ######################
echo.

:CONFIRMEXEC
set /p confirm=Are you sure you want to execute %file%.%extension% against each %mode% in %servers%? (Y/N)
if /I %confirm%==y (
set /p user=%mode% username?
goto :HInput
) else echo ABORTED & goto :END
echo.
Goto :END

:HInput
::Hidden.cmd
::Tom Lavedas, 02/05/2013, 02/20/2013
::Carlos, 02/22/2013
::https://groups.google.com/forum/#!topic/alt.msdos.batch.nt/f7mb_f99lYI
::Version 3.0
SetLocal DisableDelayedExpansion
echo.
Echo Enter password:
Set “Line=”
Rem Save 0x08 character in BS variable
For /F %%# In (
‘“Prompt;$H&For %%# in (1) Do Rem”’
) Do Set “BS=%%#”

:HILoop
Set “Key=”
For /F “delims=” %%# In (
‘Xcopy /L /W “%~f0” “%~f0” 2^>Nul’
) Do If Not Defined Key Set “Key=%%#”
Set “Key=%Key:~-1%”
SetLocal EnableDelayedExpansion
If Not Defined Key echo. & Goto :HIEnd
If %BS%==^%Key% (Set /P “=%BS% %BS%” <Nul
Set “Key=”
If Defined Line Set “Line=!Line:~0,-1!”
) Else Set /P “=*” <Nul
If Not Defined Line (EndLocal &Set “Line=%Key%”
) Else For /F delims^=^ eol^= %%# In (
“!Line!”) Do EndLocal &Set “Line=%%#%Key%”
Goto :HILoop

:HIEnd
if %mode%==DB (
goto :EXECDB
) else goto :EXECLINUX

:EXECDB
FOR /f %%A IN (%servers%) DO CALL ECHO DATABASE: %%A & ECHO DATABASE: %%A >> $logs%file%%dt%.log & sqlplus -S -L %user%/!Line!@%%A < %file%.%extension% >> $logs%file%%dt%.log
goto :SHOWLOG

:EXECLINUX
::Path of PLINK (default)
set PLINK=“C:\Program Files (x86)\PuTTY\plink.exe”
:CHECKPLINK
if exist “%PLINK%” goto EXECLINUX2
set /p “PLINK=%PLINK% not found. Enter path to plink.exe "
if “%PLINK%”. EQU . echo ABORTED & goto END
:: if path only was entered, add plink.exe
if exist “%PLINK%”\plink.exe set PLINK=”%PLINK%"\plink.exe
goto CHECKPLINK

:EXECLINUX2
FOR /f %%A IN (%servers%) DO CALL ECHO SERVER: %%A & ECHO SERVER: %%A >> $logs%file%%dt%.log & echo y | %PLINK% %user%@%%A -pw !Line! “exit” & %PLINK% %user%@%%A -pw !Line! -batch -m %file%.%extension% >> $logs%file%%dt%.log 2>>&1 & echo. >> $logs%file%_%dt%.log
goto :SHOWLOG

:SHOWLOG
echo.
if exist $logs%file%%dt%.log (echo Output saved to $logs%file%%dt%.log) else (echo No log file created)
set /p confirm=Show log file? (Y/N)
if /I %confirm% NEQ y goto END
echo.
echo Content of $logs%file%%dt%.log
echo ######################
type $logs%file%
%dt%.log
echo.
echo ######################
echo.
:END


#16

Great! I’ll update it right now, I’m glad you found it useful.

Bye!