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!

Shell script–Quickly check Oracle listeners

Hello everyone,

Today I would like to share something with you. In my day to day work, I have encountered more than one place where DEV environments are spread through only some hosts. This normally leads to creating several databases on the same host and to create several listeners to be able to split communication through several ports.

Having this, after OS patches or any OS related activity that would require the database software to be shutdown, it's an usual case that people forget to start listeners.  For this I have this useful script:

Enjoy.

Hope this helps in making your job easier.

Configure Solaris 10 DNS client

Let’s get started!

I just got my brand new Solaris 10 server, but somehow although I have a DNS server in my domain, it cannot resolve any names given.  What’s wrong?

We have to first check the existence of some files and the status of the DNS client service.

So, first thing to do:  check the service

# svcs -a | grep dns

disabled     XX:XX:XX    svc:/network/dns/server:default

disabled     XX:XX:XX    svc:/network/dns/client:default

Then, let’s check the files.

Does the resolver configuration file exists? Try to see its contents with the following command.

# cat /etc/resolv.conf

cat: cannot open /etc/resolv.conf

Let’s then, create the file and edit it to add our name servers.

# touch /etc/resolv.conf

# vi /etc/resolv.conf

Add some lines with the following format

domain somedomain.com

nameserver 10.11.12.13

nameserver 14.15.16.17

nameserver 18.19.20.21

search firstdomain.com,seconddomain.com,thirddomain.com

where domain is the domain to look for as default, nameserver is the IP of each name server we have (up to three) and search is the domains that must be added as sufix to the name to look for if none was given.

That’s it with the resolver.  Now, we should go check the naming service switch file, /etc/nsswitch.conf

If you look for some nssswitch file in the /etc directory you’ll find that there are several templates already configured for your use. So backup your current file and replace it with the nsswitch.dns file provided.

# cp /etc/nsswitch.conf /etc/nsswitch.orig

# cp /etc/nsswitch.dns /etc/nsswitch.conf

Once you’re done.  Enable the dns client service and check again its status

# svcadm enable /network/dns/client

# svcs -a | grep dns

disabled    XX:XX:XX    svc:/network/dns/server:default

online      XX:XX:XX    svc:/network/dns/client:default

And welcome to the naming world, Solaris 10.

Hope this helps some of you.