Shell Script–Applying a command or a SQL script to all databases in the same host

Hello everyone,

This is my first post on the oracle blogs section!

I really hope the posts I will publish here help some of you to have a more dynamic and comfortable work.

This time I'm publishing a simple KSH script that can allow you to apply a single command or a SQL script to all running databases in a single host.

Now let's explain each part:

• Functions
  • Utility Functions: refer to this post to check the utility functions I normally use.
    • You'll see debugPrint and msgPrint in most of my shell scripts. Those are used to give a nice format to the message I'm sending to the console/log.
  • Crawl
    • This is the main function that receives the command/sql file to be run in every instance.
• Main Algorythm
  • for DBNAME in $(ps -ef | grep pmon | cut -d"_" -s -f3 | grep -v ASM)
    • Here, we cycle through all the running instances in the host. Looking for pmon processes and getting only the INSTANCE name from it.
  • crawl "@${2}"
    • In the SQL script mode see how the argument passed to the crawl function is appended to the @ sign, so that we don't have to check for it when running inside the function.
      
  • crawl "${2}"
    • In the command mode we pass the argument inside double quotes so that it is considered a single argument by the function while running.

With some light modifications, we can get this same schema to work with any of the command line utilities from Oracle. But that's a future project of mine. I will try to add support for RMAN in the next few months as my work allows me to.

Hope that some of you find this post useful.

Shell script–Utility functions

Hello fellow DBAs, Oracle and Linux enthusiasts.

Today I'm going to share the script I use to "source" utility functions to the shell scripts I commonly use for my daily DBA tasks.

Let's add some explanation:

• continueQuestion
  • This function is a simple "Continue? (y/n)" question. As this is used a lot in interactive scripts, seemed like a good idea for me to add it to the utility functions script.
• debugPrint
  • For debugging, it adds the timestamp prior to the message. Allows you to keep track of what, where and when something is happening.
• msgPrint
  • Allows you to format in several ways the output that you want to send to console/log file.

I hope you find this useful and makes your job better!

Configuring EM DB Console manually

Welcome, for the DBAs out there, any tool that helps understand the  metrics of the DB performance faster is a helping hand in his daily job, the Enterprise Manager (EM) is no exception to this rule.

For the Oracle Database 10g and over, the EM can be automatically configured during the installation using the Oracle Universal Installer (OUI).  Depending on a variety of circumstances, this installation/configuration may fail, and thus we will have to create, or recreate the EM repository and configuration manually, by using the Enterprise Manager Configuration Assistant.

Contrary to what many of you would expect, the EMCA is not a GUI tool, but a command-line tool and thus, it can take us some time to read the entire list of options and clauses that can be added to the command in order to fulfill the task of configuring the EM and creating the necessary repository within the Oracle instance.

So, here you go.  The basic instructions you’ll need to create or recreate your repository and configure the EM are these:

emca –repos drop

emca –repos create

emca –deconfig dbcontrol db

emca –config dbcontrol db

But be sure to have the complete information of your instance because you will need it to fill the blanks when running these commands.  You’ll need: SID, port, password for sysdba and/or sysoper accounts.

If you are still having trouble, check your time zone settings.  For linux, the TZ environment variable should be set and it must match the line agentTZRegion within the EMD.properties file.

Hope this can help you out there.

All your comments are welcome.