Wednesday, March 30, 2011

How to execute sql script against multiple databases?

A friend of mine, ask me that question few days ago... At first moment, I give him some bash script that I have, for connecting to multiple hosts through ssh and than executing some commands, and tell him that he can easily change to connect with sqlplus with some list of connections and than run the sql script.
But, after some time, I tried to find some better way to do this...
After some reading, I found, and off course,  successfully tried this two ways of doing this:

1) EM grid control

In a book "Oracle Enterprise grid control 10g handbook" , in chapter 14 "Database Management in Grid Control", Page 447, we have workshop "run a SQL Script Against Multiple databases", where, in details, is explained how to achieve what we wanted.

preview: Oracle Enterprise grid control 10g handbook

 2) Toad ScriptManager

I will not copy/paste someone else work :-).. Here is a link and detail explanation:

Toad metod 1

3) Toad Project Manager 

And, my favorite, fastest and most elegant way:

Toad metod 2 

So much for this time!

Thursday, March 24, 2011

EM grid control 11g - push agent installation problem

So many databases, so little time! :-)

Recently, I was trying to install em grid agent on target server, but through em grid console, on deploy option, always failed on Preinstall Script..
I tried on another target server, which have same OS (OEL linux), DB versions (, but same thing happened...
I know, of course, that there is another way of installing agent, but I was determined that I can do that install with EM...

After some little digging through logs, I found this:

'/opt/oracle/agent/' is my target agent dir which I specified in em deploy agent form, and I guess that '/agent11g/cfgtoollogs/oui//' is something that deploy script build for install on target... So, how I see, problem is with creating this path and double '//' sign. I tried even without trailing '/' sign on the end of my target dir '/opt/oracle/agent' , but, without success, same thing...

After a 'few short  weeks' comm with Oracle support, they concluded: "Clarified that Push Agent installation was reporting some communication errors may be due to ssh latency and he can install Agent on any server using response file which is simpler and faster"...
Well, I haven't see any comm errors, but...who am I... :-)

So, the workaround is this: to install the Agent using Reponse file by following the document below
When I tried to find additional_agent.rsp file:

The required file (additional_agent.rsp) file is not present in the OMS_Home in your setup.You must download the Agent Software from the location agent_download  and use the additional_agent.rsp file in it for installation.

Here is file, with parameters that I changed:

and than, run installation:

After sucessfull installation, start agent and upload the data on emgrid11g host:

Great, it works, but in EM grid for all db targets there is a message 'Target has encountered metric evaluation errors', for host it is ok. Upload was without errors. Well, now, should only to configure db parameters for dbmsnm user - password :-)

Here we are - working as expected, but, off course, with workaround.. :-)

pssst... I still think that something is wrong with script for deploying agent in em grid,, I don't mean bug, but...:-)

sorry for my english, in a hurry...Cheers!

Tuesday, March 1, 2011

Quick way to prepare environment for DB install

With the emergence of Oracle Linux, I thought that one annoying part of the setup environment to be unnecessary, but ... I was wrong. :-)
We need to edit multiple files, create user, groups, and with each new version of Oracle, this section remains roughly the same.
So over time I made/find/edit/copy/paste/change/supplement a folowing script, it should be run as root, and after that everything is ready for the "runInstaller".
The script in this form meets my needs for fast setting environment, but, of course, everyone can still extend and customize.

Here is a script: