Oracle Rant

Cron is the job scheduler in any Linux/Unix system. Most of the time, it is pretty good, but when you need to apply several variables to running a job, it doesn’t quite cut it. If you want a tutorial on Cron, go here. I will just be covering some ways of making cron a little more flexible to help you get the right schedule you need.

If you want to run a job at noon every day. That is pretty simple. If you want to run a job 5 minutes after noon, that is very simple. If you want it to happen on any day of the month, again that works. If you want it on every Wed, again simple.

So what do you do if you need to apply some logic? Let’s say you want a job to only run on the first Saturday of the month, but only after following the first Sunday of the month? So if the first was a Saturday, you would not want the job to run? You might think that setting the day of the month as [2-8] and the Day as a Saturday might work? Wrong, it will do an ‘OR’ operation, not an ‘AND’. Basically it will fire on the 2nd, 3rd, 4th, all the way to the 8th. It will also fire on every Saturday. That is clearly not what you want.

The solution is actually quite simple. You can use the if/then operator of the command line. This is how I accomplished it and it works quite well (and I thought I would share).

0 12 2,3,4,5,6,7,8 * * if [ `date +\%a` = "Sat" ]; then sh /u01/app/oracle/scripts/somescript.sh 1 > /u01/app/oracle/scripts//u01/app/oracle/scripts/logs/somescript.log 2>&1 ; fi

So in the above example, I run this in the 2-8 day frame. It executes every single one of those days at 12 o’clock. I then check to see if the day is a Saturday. If it is, then execute the encapsulated command, if not then don’t run it. So while the job technically still executes everyday, I added some extra logic to enable it to do what I want, which is to only execute the script if that day is a Saturday.

If anyone has any other tips or ideas then please share them.

Windows 7 Release Candidate was publically released on May 5, 2009. This should be the final release before it goes live later this year. I downloaded it, installed it in a VM inside of VirtualBox and tried to install Oracle on it.

There is no Oracle release for Windows 7, so instead I grabbed the Windows 2008 one. The install is pretty straight forward. The only "gotcha" is that if you go to the command prompt, you need to run it as the Administrator, so that all of the Oracle commands will work (lsnrctl, dbca, netca etc…) Really there is not that much to it as it was pretty seemless. So, if you download Windows 7, then you can also download Oracle 11g and start playing with it today!

If you run into any issues, let me know and I’ll see if I can help you out.

I recently came across the http://samiora.blogspot.com blog page.

I found this post from November 18th, 2008 http://samiora.blogspot.com/2008/11/rman-veritas-netbackup-integration.html

Which is word for word of one of my posts (including hyperlinks), which I posted in May 24th, 2007.

http://www.oraclerant.com/?p=7

I’m not really mad, more annoyed that someone would just outright steal content word for word without giving any reference to the original author.

Oh well, I guess they say imitation is the ultimate form of flattery, so I wonder what cut and paste is then ?

Do you want a fast way to install Oracle with a standard tnsnames.ora file? The Oracle Universal Installer has a very fast way of cloning an Oracle home. Let me give you the steps.

1. Archive your current Oracle installation via zip or tar. I tend to archive the directory of the actual home and all files and subdirectories
2. Have your server setup with the required packages for your Oracle version. It also has to be the same OS. A Linux Oracle install will not work for cloning to Solaris.
3. Have the dba and oinstall group created and then the oracle user will need to be created and assigned to both groups. Have oracle own the install directory as well.
4. Create your profile and environment variables. The main ones are ORACLE_BASE and ORACLE_HOME. The Inventory will be created off of your ORACLE_BASE, so set this beforehand if you don’t have an Oracle Inventory.
5. Upload and extract your Oracle archive.
6. Use the Oracle installer to link and setup your Oracle installation.
7. Run orainstRoot.sh and root.sh if required. It will tell you at the end what scripts to run.

Let me give you an overview of step 6 below. Everything else should be self explanatory.

[oracle@server ~]$ $ORACLE_HOME/oui/bin/runInstaller -silent -clone ORACLE_HOME=$ORACLE_HOME ORACLE_HOME_NAME="Ora10gR2Client"
Starting Oracle Universal Installer...

No pre-requisite checks found in oraparam.ini, no system pre-requisite checks will be executed.
Preparing to launch Oracle Universal Installer from /u001/OraInstall2009-01-29_06-54-41PM. Please wait ...[oracle@server ~]$ Oracle Universal Installer, Version 10.2.0.1.0 Production
Copyright (C) 1999, 2005, Oracle. All rights reserved.

You can find a log of this install session at:
 /u001/app/oracle/oraInventory/logs/cloneActions2009-01-29_06-54-41PM.log
.................................................................................................... 100% Done.

Installation in progress (Thu Jan 29 18:54:50 GMT 2009)
........................................................................                                                        71% Done.
Install successful

Linking in progress (Thu Jan 29 18:54:53 GMT 2009)
.                                                                72% Done.
Link successful

Setup in progress (Thu Jan 29 18:55:08 GMT 2009)
..................                                              100% Done.
Setup successful

End of install phases.(Thu Jan 29 18:55:10 GMT 2009)
WARNING:A new inventory has been created in this session. However, it has not yet been registered as the central inventory of this system.
To register the new inventory please run the script '/u001/app/oracle/oraInventory/orainstRoot.sh' with root privileges.
If you do not register the inventory, you may not be able to update or patch the products you installed.

The following configuration scripts
/u001/app/oracle/product/10.2/client/root.sh
need to be executed as root for configuring the system.

The cloning of Ora10gR2Client was successful.
Please check '/u001/app/oracle/oraInventory/logs/cloneActions2009-01-29_06-54-41PM.log' for more details.

[oracle@server ~]$

As you can see, it tells you what scripts to run as root. Also, make sure to update your tnsnames.ora if you need something different that what you archived with.

I found this article below to be quite intriguing. After reading it, I immediately went to metalink to see if it was one of Oracle’s certified configurations. Sadly, I was disappointed when it wasn’t listed. I wanted to be the first DBA to ever run Oracle on Linux that runs on a potato. I can just see it now at Oracle Open World 2010, "Oracle 11gR2 on a RAC of potato clusters. Cheap, clean, and most of all very affordable!"

Next up, Tomatoes! You can scoop the seeds out and make room for more memory and storage

See more below…

Amsterdam, Netherlands – Hackers from the LinuxOnAnything.nl Web site successfully installed Linux on a potato. It’s the first time the operating system has been successfully installed on a root vegetable

“A potato doesn’t have a CPU, memory or storage space, so it was quite a challenge,” said Johan Piest of the Linux on Anything (LOA) group. “Obviously we couldn’t use a large distribution like Fedora or Ubuntu, so we went with Damn Small Linux.”

After weeks of trying the group got a Linux kernel specially modified for a potato loaded, and were able to edit a small text file in vi. Linux was loaded onto the potato using a USB thumb drive and commands were sent in binary to the potato using a set of red and black wires.

The LOA group is a part of a growing group of hackers attempting to get Linux loaded on anything. It started on electronic devices like Gameboys and iPods, but recently groups have taken on tougher challenges like light bulbs and puppies.

The LOA group was in a race with another hacker group, the Stuttering Monarchs, to be the first to bag the potato. “The potato has been the vegetable that everyone has been gunning for, because it’s so versatile like Linux itself. You can boil ‘em, mash ‘em, stick ‘em in a stew,” said Piest. “You’d think we’d get some sort of reward for this, but it’s all about bragging rights for us.”

LOA was the group that first installed Linux on a Shetland pony in 2003, but growing competition from other hacker groups have shut them out in the past five years.

“We were close to being the first with Linux on a cracker, but those jerks from Norway beat us out,” said Piest.

The first vegetable Linux install was on a head of iceberg lettuce by a group of hackers in Turkey.

Many DBAs are familiar with the 10046 event. It’s an excellent way to grab wait events for a specific session that may or may not be performing specific tasks. Some DBAs look at the raw output. Some write their own scripts to aggregate, format and look at it. There is also TKPROF to help you format it. Then, if you really want some detail, you can download the Oracle Trace Analyzer from Metalink (requires setup and configuration) or you could purchase a product like the Hotsos Profiler.

Well, a fellow Oracle DBA who runs a blog at http://oraclue.wordpress.com put up a post about something called OraSRP. It is very detailed, and I thought I would pass it on to any DBA who would like to look at profiling Oracle via 10046 event trace data and get some great analysis on their trace files.

You can even create Google Charts for it. Very cool!

Best of all, It’s FREE!

Enjoy : )

I came across an article on CNN.com titled "Ex-Googlers launch rival search engine." After reading it, I wondered, "is this a Google killer?"

The company is backed by $33 million in venture capital. They claim that they are indexing more than 120 billion web pages which is greater than Google’s 8.2 million (even though Google doesn’t publish how many they scan). Anyways, enough of the marketing muscle. I wanted to give it a try.

I did some comparative search results between Google and Cuil. I found that Google blows cuil away; however, your comparisons may differ.

Some examples:

I searched for an Oracle 600 error. Google found information, Cuil said no results found.

I decided something basic, so I searched for ".NET". Google brought up a list of results for Microsoft. Cuil said, results not found.

So I did a search of "cuil" on Cuil and guess what? It’s not self-aware! It can’t even find itself, so what makes me think it is capable of finding what I am searching for? This is not even a Google Killer. It’s a Cuil killer. It’s pretty bad when it kills itself.

So I tried a simple search. I searched for trees. BINGO! Cuil has plenty of information about trees. The problem with cuil is I can’t really use it, at least not yet. It has a long way to go to catch up with even Microsoft Live Search, much less, Google.

They spent $33 Million to build this? Alta-Vista was better back in the day. The thing with Google, is it was working well before I ever heard of it. It spread via word-of-mouth, and it is synonymous with web search. Cuil would have been better off waiting until the search technology was well refined.

Have you tried Cuil.com? What do you think?

Inquiring mind(s) want to know : )

I wrote previously about integrating RMAN with Veritas NetBackup. The reason for doing so was so that I could easily verify that the backup went to tape and for recovery to be simpler. By integrating, I no longer needed to contact my NetBackup admin should I quickly need to recover my database. Also, as long as NetBackup was installed on multiple servers, I could easily clone/duplicate/restore my databases on any number of servers where it was installed. I am also using a recovery catalog to help simplify things. Without a recovery catalog, you will need to restore your control file which will have information about your backups, spfile, and previous control file backups prior to being backed up itself.

A couple of things to note. I only have 1 NetBackup policy of Oracle type assigned to this server by my Veritas NetBackup admin. Also, that policy is set to infinite retention so that RMAN controls when it wants to delete expired / obsolete backups by allocating a channel "for maintenance device type ‘SBT_TAPE’. A good practice is working with your NetBackup admin in order to configure your policy. You could also define multiple policies so he can more easily identify which backups to send offsite. The only problem with this approach, and it’s very minor at that, is that you will have to explicitly define your Oracle type NetBackup Policy when you do your backups. Read the rest of this entry »

I just wanted to share an experience that I had with Oracle ASM that made my life a breeze. ASM gives the DBA more control and flexibility in dealing with storage. It even has some excellent performance benefits.

Well, I would also like to add to the list that it also proactively cures headaches and stress! This is why. We have an IBM Shark that is going off of lease. We needed to move the database to Hitachi storage. The problem was we needed to do it fast and we couldn’t get the application owners to give us a change window in the alloted time frame. The most they would give us was 2 hours and this was a multi-terabyte database. It wasn’t happening!

So how did Oracle ASM save me? Simple! I just added the new disks to my existing diskgroup and let ASM automatically rebalance the disks. I let this job run overnight with virtually no impact to application performance. I could have done it with a higher rebalancing factor but that had a greater chance to impact the application. Instead, I just did it with a power of 1 which was the default.

Once the job finished rebalancing, I then moved to drop the old disks. Please make sure you add more than enough space in new disks to allow you to remove the old ones. With the disks being dropped, Oracle moved all extents off those disks and neatly rebalanced it on the ones that were left. The job took a day to complete and after ASM was done with them, the Unix SA and SAN admin went ahead and removed them, all the while the application maintained 100% uptime and solid performance.

ASM has it’s limitations such as having to use RMAN to do backups and many of your shell scripts won’t work against it without being rewritten to use sqlplus instead or RMAN, but even RMAN is an improvement over traditional backups. I wouldn’t even call this a limitation but a strength. Oh well, I’ll just stick with ASM on this one. Definitely take the time to get up to speed on ASM! It’s a lifesaver.
 

I came across Tyler Muth’s blog via Tom Kyte’s blog earlier this week. While he doesn’t have that many posts, his blog has powerful content. I also love the layout of his blog. In particular, I was fascinated with the way he was formatting his code snippets and wanted to add this functionality to my blog.

I came across a Wordpress plugin by the name of "syntaxhighlighter". I also found out how to add this functionality to blogger blogs as well.

Here is an example of a code snippet with this plugin.

CREATE TABLE TEST
   (ACCOUNT NUMBER(16,0),
    FIRST_NAME VARCHAR2(20)
   ); 

And there you go. I might need to tweak the formatting to highlight words a certain color but it shouldn’t be too hard. It also looks like orana.info picked up my post as I was testing this plugin, so the post was incomplete at the time.