Oracle Rant

Like most Oracle shops, we use Grid Control to monitor and give insight into our DB environments. The Grid Control repository is loaded with lots of useful information which sometimes is not always evident or accessible via the Grid Control GUI. Some of us have even attempted to reverse engineer the GC schema to try and answer some questions that we have. This is a collection of scripts/queries I use against the GC repository to get some answers I am looking for. If you have some scripts/pointers, then please share them .

Run the following query for current metrics. This shows what thresholds Alerts will be triggered for (Alerts not email/pager notification – that is part of notification rules).

SELECT a.target_name, a.target_type, a.metric_name, a.metric_column,
       DECODE(TRIM(a.warning_threshold), '',
       DECODE(TRIM(a.critical_threshold), '', 'DISABLED', 'ENABLED'), 'ENABLED') STATUS ,
       a.warning_operator, a.warning_threshold, a.critical_operator,
       a.critical_threshold,
       REPLACE(REGEXP_SUBSTR(b.schedule_ex, '"[^"]*"'), '"', '') FREQUENCY,
       NVL(REPLACE(REPLACE(REGEXP_SUBSTR(b.schedule_ex, 'T="[^"]*"'), 'T=', ''),'"', ''), 'Minutes') FREQUENCY_UNIT,
       c.frequency_code, a.occurence_count, a.warning_count,
       a.critical_count, c.is_enabled
FROM MGMT$METRIC_COLLECTION a, SYSMAN.MGMT_METRIC_COLLECTIONS b, sysman.MGMT$TARGET_METRIC_COLLECTIONS c
WHERE a.target_guid = b.target_guid
  and a.target_guid = c.target_guid
  and b.metric_guid = c.metric_guid
  and a.metric_name = c.metric_name
ORDER BY target_name, target_type, a.metric_name;

Next, Notification Methods need to be queried to determine which alerts Oracle sends our emails for.

SELECT RULE_NAME, OWNER, TARGET_TYPE, TARGET_NAME, TARGET_GUID,
                 METRIC_NAME, METRIC_COLUMN, KEY_VALUE, KEY_PART_1, KEY_PART_2,
                 KEY_PART_3, KEY_PART_4, KEY_PART_5, WANT_CLEARS, WANT_WARNINGS,
                 WANT_CRITICAL_ALERTS, WANT_TARGET_UP, WANT_TARGET_DOWN,
                 WANT_TARGET_UNREACHABLE_START, WANT_TARGET_UNREACHABLE_END,
                 WANT_TARGET_METRIC_ERR_START, WANT_TARGET_METRIC_ERR_END,
                 WANT_TARGET_BLACKOUT_START, WANT_TARGET_BLACKOUT_END,
                 WANT_POLICY_CLEARS, WANT_POLICY_VIOLATIONS,
                 WANT_WARNING_JOB_SUCCEEDED, WANT_WARNING_JOB_PROBLEMS,
                 WANT_CRITICAL_JOB_SUCCEEDED, WANT_CRITICAL_JOB_PROBLEMS,
                 WANT_POLICY_JOB_SUCCEEDED, WANT_POLICY_JOB_PROBLEMS, IGNORE_RCA
FROM SYSMAN.MGMT_NOTIFY_RULE_CONFIGS;

This final Query will combine the 2 to show alerts and if they will be notified on.

Read the rest of this entry »

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.

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 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.

Lewis Cunningham tagged me. I have to tell 8 things about me that most people don’t know!

1. I am a native Floridian and was born here! (We’re an endangered species).
2. I have a pile of books in my room that I intend to read but haven’t (trying hard not to look at them).
3. I used to be a DJ, still have lots of vinyl (mainly house, trance, and electronica).
4. I’ve been driving the same car for 12 years! (new cars look nice… not having a car payment looks even better).
5. My very first job out of High School was working in a warehouse. (what a coincidence, so was Lewis’s!, after 6 months, I decided it wasn’t my thing).
6. I used to be scared to fly, now it doesn’t bother me. (except the 9 hour flights to Europe for Disaster Recovery!.. but they do feed us well).
7. Heights usually do not scare me, but recently I went atop the Eiffel Tower back in November and wanted to immediately go back down! It was raining and very windy in the pitch darkness of night.
8. When we were kids (younger brother and I), we decided to skateboard off the roof of our 2 story house while using sheets as parachutes. I told him if he went first, so would I. After brother went , I decided it wasn’t a very good idea! hehe (Dad came home and punished us after the neighbor called him! It wasn’t pretty.)

I will add people after I tag them. In the meantime, I just wanted to get this up!

As a best practice, I don’t like logging in as SYS or SYSTEM unless I have to. I also don’t like remote OS authentication. If I connect remote, I want to use a password. If I am on the box however, I like to just connect using OS authentication (not providing a username and / or password). Now I could also give this internal user access to connect over the network, but well, I am not going to go there. The purpose of this post is to show you what I did to set this up on Windows. On Linux or Unix, it is quite easy, on Windows there is a gotcha.

First, to use OS Authentication for Oracle logins, you need to understand a parameter.

SQL> show parameter authen

NAME                    TYPE        VALUE
----------------------- ----------- -------------
os_authent_prefix       string      OPS$
remote_os_authent       boolean     FALSE

The os_authent_prefix could really be set to anything you want, but I like the default. The other parameter is set to FALSE because I don’t want to trust other operating systems authentication.

Next, you need to create the user. How do you find out who that user is? Simple, run this query after you login locally.

SQL> select UPPER(sys_context('userenv','os_user')) from dual;

UPPER(SYS_CONTEXT('USERENV','OS_USER'))
-------------------------------------------------------------------
MACHINENAME\TOM

Now that I have this information, I can easily create the user and grant him the privilege he needs.

SQL> create user "OPS$MACHINENAME\TOM" identified externally;

SQL>

As you can see, Oracle prefixed my login with OPS$ that I defined in os_authent_prefix. I also had to use the “\” because Oracle sees my login as “MACHINENAME\TOM” and not just Tom. The gotcha is that you cannot create a user with the “\” without using double quotes. If you were a Windows Domain user account then you would use “DOMAINNAME\USERNAME”.

I hope that someone finds this little tip useful in case they want to setup OS Authentication on Windows : ).

I want to apologize to all my readers. I know I started up posting a bit but it kind of dropped off in the last month. I have so much to blog about. I have been focusing on Grid Control monitoring lately, shell scripting, and database performance tuning. The goal will be to average one post a day on something I did or something I learned. I figure this will consume about 30 minutes of my time per day which equates to 3.5 hours a week. We’ll see how it goes : ).