Tuesday, April 27, 2010

2010 Oracle VM, part 2, OEM database

First, create a linked clone of the OEL5.5 VM created in part one; call it something not stupid - like OEL5.5 OEMGRID. Quick note: you might need to bump the memory of this virtual machine to at least 1.5GB else the Oracle Database installer will complain; that did not happen the first time I did this but the second time I was about 3K (yes, 3 measly F'ing K) short of the minimum.

Start the cloned VM and log in as root.

I had the luxury of having a domain name and static IP address so I used the following tool to configure my network and then edited /etc/hosts:

system-config-network-tui
vi /etc/hosts
10.9.8.7 grouch1.grouch.net grouch1
:x

I would reboot now so the rest can be handled through ssh and cut and paste (unless you like typing).

Oracle Database Preinstallation Requirements

Most of the chapter two Preinstallation Requirements have been set by installing .rpm package oracle-validated (part one). The oracle-validated .rpm installs all Oracle Database dependancies and also modifies /etc/sysctl.conf, /etc/security/limits.conf, /etc/modprobe.conf and /boot/grub/menu.lst to include kernel parameters. It also creates some of the users and groups. Login as root and execute this to finish up and add an administrative user:

/usr/sbin/groupadd oper
/usr/sbin/groupadd oracle
/usr/sbin/usermod -g oinstall -G dba,oper,oracle oracle
passwd oracle
/usr/sbin/useradd grouchy
/usr/sbin/usermod -g grouchy -G dba,wheel grouchy
passwd grouchy

Next step is to create the directories where software will be installed, for database files and for recovery files (note: in real life production land these could be on separate disks or one gigundous LUN, but we are happy fun test land so who cares)

mkdir -p /u01/app/oracle
mkdir -p /u02/oradata
mkdir -p /u03/recovery_area
chown -R oracle:oinstall /u01/app/oracle /u02/oradata /u03/recovery_area
chmod -R 775 /u01/app/oracle /u02/oradata /u03/recovery_area

A reboot is not required but why not (what better way to test what you screwed up). Now might also be a good time to take a snapshot in VMWare.

Oracle Grid Infrastructure

The OEM VM will not be clustered and will not be using Automatic Storage Management (ASM) so the Grid Infrastructure for standalone server and Oracle Cluster Synchronization Services (CSS) are not required.

Installing Oracle Database

Login to the VM as root. Connect the database .iso image as a DVD or mount the DVD and connect that to the VM. If empty directory /media/cdrom does not exist then make it. As root, mount the CD (mount -t iso9660 -o ro /dev/cdrom /media/cdrom).

I use PuTTY with X11 forwarding enabled in my Ubuntu workstation (that I am running VMWare Workstation on) to connect to the VM as user oracle and do the installation.

First, edit ~/.bash_profile and append the following:

umask 022
ORACLE_BASE=/u01/app/oracle
ORACLE_SID=orcl
export PATH
export ORACLE_BASE ORACLE_SID
unset ORACLE_HOME
unset TNS_ADMIN

Do a quick . .bash_profile to execute that script. Next, create some directories in the oracle user home (personal preference):

for i in backup crap expimp junk log run save scripts sql temp
do
mkdir ~/$i
done
cd ~/crap

Start installing:

  1. /media/cdrom/runInstaller
  2. Skipped security updates; Yes
  3. Installation Option; Install database software only
  4. Grid Options; Single instance database installation
  5. Product Languages
  6. Database Edition; Enterprise Edition
  7. Installation Location; Oracle Base: /u01/app/oracle Software Location: /u01/app/oracle/product/11.2.0/dbhome_1
  8. Create Inventory; /home/oracle/oraInventory; *1
  9. Operating System Groups;
  10. Prerequisite Checks; all passed
  11. Summary;
  12. Install Product;
  13. Finish

*1 pure laziness on my part because the preinstallation instructions never made nor setup privileges on the default /u01/app/oraInventory but now that I think about it the inventory directory should be outside of the whole OFA structure.

Oracle Database Postinstallation Tasks

I did not do anything here.

Go to Tahiti and check out the installation guide for more information

Updates:

06/22/2010:
Corrected errors, added smart-ass comments, etc. blah blah blah...

Monday, April 26, 2010

My 2010 Oracle VM project, part one

As I look back through my blog there have been numerous ambitious attempts to do this but all have stopped before they got started. Pretty sad. Such is life, especially when you yearn to have one instead of fiddling around with software when you should be doing anything but.

Here are the constants:

  • VMWare Workstation 7 on Ubuntu 10.04 64 bit
  • Oracle Enterprise Linux 5 Release 5 32 bit

Here are my goals:

  • One VM with OEL, OEM GRID 11.1, Database 11.2 (EM/RMAN repository)
  • One VM with Database 11.2 using ASM

The first step is to create a VM I can use as a template for my two goal VMs.

Create template OEL5.5 VM:

  1. Create a VM with at least 1GB of memory (2GB if feasible) and one 20GB (to 40GB) virtual disk (bridged network, uncheck automatically connect new USB device, do not connect sound card and printer at power on)
  2. Power on the VM with the Oracle Enterprise Linux 5 Release 5 mounted as an .iso (or physical DVD)
  3. Press ENTER at the "boot:" prompt, next, skip, next
  4. Choose language and keyboard
  5. OEL partitions everything perfectly, no need to review or modify (/boot with LVM with swap and / volumes)
  6. Next, GRUB, DHCP, time zone (uncheck System clock uses UTC)
  7. Set root password
  8. Select Customize now. Next.
  9. Desktop Environments. Uncheck GNOME
  10. Applications. Uncheck all (except maybe Text Based Internet)
  11. Base System. Uncheck Dialup Networking Support and X Window
  12. Base System. Check System Tools; click Optional packages, check oracle-validated
  13. Next, next, installing packages, reboot.
  14. Highlight networking, then run tool
  15. Set Firewall and SELinux both to Disabled
  16. Next
  17. OS installation complete. Login. User root.

Install VMWare tools:

  1. VM: Install VMWare tools (will prompt you to mount something)
  2. mkdir /media/cdrom
  3. mount -o ro /dev/cdrom /media/cdrom
  4. cd
  5. tar -xzf /media/cdrom/VM*tar.gz
  6. cd vmware-tools-distrib
  7. ./vmware-install.pl
  8. I kept defaults for everything; installation unmounts the VMWare tools
  9. cd
  10. rm -fr vmware-tools-distrib
  11. shutdown -h now

Take a snapshot of the OEL5.5 virtual machine. This snapshot will be used in the cloning process.

In the next parts I will use this template VM to create the OEM GRID and Database VMs

Updates:

06/22/2010:
Now on Ubuntu 10.04

Tuesday, April 20, 2010

The Oracle CBO statistical time bomb

Ever have a query or process that runs fine for years and years and years but then one day just slows to a crawl, going from seconds to hours in execution time? If so, you have run into what I call the Oracle CBO statistical time bomb.

Oracle 10g/11g has a daily job that queries for "stale" and missing optimizer statistics on Oracle table/index objects. Those optimizer statistics are used by the optimizer when choosing the best access plan, join types, index and table scans to use for a SQL query. The default for "stale" is if 10 percent of the data have changed (static in 10g, can be changed in 11g).

Let's assume we have this really, really big invoice table that grows at a constant rate over time. This table has an index on the financial quarter. Each quarter roughly 25,000 new rows are inserted, and then updated 3-5 times each (as status changes, etc. whatever), and some idiot needs to clean up a couple hundred bogus rows each month. Here is the table for 2010:

Financial    Total  Insert  Update  Delete  Changed  Percent
Quarter Rows Rows Rows Rows Rows Changed
2010 Q1 1,000,000 25,000 80,000 169 105,169 %10.51 - daily job gathers statistics
2010 Q2 1,024,836 25,000 80,000 169 105,169 %10.26 - daily job gathers statistics
2010 Q3 1,049,672 25,000 80,000 169 105,169 %10.01 - daily job gathers statistics
2010 Q4 1,074,508 25,000 80,000 169 105,169 %09.78 - not stale yet, still have 2010 Q3 statistics

Without current statistics, the Oracle cost-based optimizer thinks one index and access method would be more efficient than a different index and access method (when it really, really, really is not).

I'll need an Oracle Guru to correct me but here is what happens in 2010 Q4:

Your super-optimized, meticulously coded query, queries the invoices by quarter and by another column existing in a massive subquery.

For 2010 Q1,Q2,Q3 the CBO says there are 25,000 rows reading the quarter index and likely less rows using an index on another column (customer number perhaps) if it does the massive subquery first. The CBO got it right and the massive subquery executes first then reads the table via index on the other column.

For 2010 Q4 the CBO says there are no/few rows for Q4 (when there are really 25,000) so read the invoices using the quarter index and then do a nested loop each time on the massive subquery because the cost of the read by quarter index times the cost of the massive subquery is less than the plan it took above (when it really is not). Instead of doing that nested loop on a couple rows (yawn) it does that nested loop massive subquery 25,000 times. Yikes.

What to do? Plan ahead.

Go to Tahiti and research DBMS_STATS in the Oracle Database PL/SQL Packages and Types Reference. Know your tables and diffuse the time bomb before it goes off. Let the daily job handle the stale statistics. Tables that contain an index on a period of time (quarter, year, etc.) or code based on a period of time (i.e. the Spring 2010 line of Mountain Dew with Ginseng and Xanax) should be researched as candidates for statistics gathering outside of Oracle's daily job. Gather table statistics before the big processing periods.

References

Oracle and SSIS; ORA-00903 during verification

Can you tell I have been suppressing my pain and hate up into a nice little ball, instead of talking about my feelings while laying in some lush, peaceful green meadow, sipping Chamomile tea and popping anti-depressants like candy.

This issue is one of those classic, what the **** is going on here errors.

Here is what I am suffering with:

  • Windows XP and Windows Server 2003
  • SQL Server 2008 Integration Services
  • Native OLE DB\Oracle Provider for OLE DB, Oracle 11g R1 or Oracle 10g
  • OLE DB Source (Oracle) using a query.

The Problem

Getting ORA-00903 table or view not found during execution on verification step on Data Flow OLE DB Oracle source; even though at design time (in the OLE DB source editor dialog) the SQL parses correctly AND pulls back test data in the source AND the query runs successfully everywhere else using the same credentials.

The Solution

Drink.

Smoke.

Break something.

Then:

  • in Oracle, CREATE a VIEW for the query
  • if the query contains a user function you will also need to in Oracle, GRANT EXECUTE ON schema.function TO view_schema_owner WITH GRANT OPTION
  • in Oracle, GRANT SELECT on VIEW to the USER used by SSIS (if necessary)
  • in SSIS, use the Advanced Editor on the OLD DB source; Component Properties; AccessMode = OpenRowset, OpenRowset = SCHEMA.view (standard editor will not pull up the view and you are limited to a drop-down)

There that was painless, right?

I have been told that shutting down Visual Studio sometimes helps (it did not). Shutting down Visual Studio works if you switch databases in the connection manager and then get ORA-00903 table or view not found in the OLE DB source. One co-worker stated he rebooted his machine and then things worked (it did not).

I'm sure I was doing something stupid like trying to use a subquery or SYSDATE but WTF; it parsed, it pulled back test data, it works everywhere but the verification step when debugging it.

****!

Oracle and SSIS; SQL tasks and procedures

I am posting today to hopefully prevent others from the hell I have been through. I really have no idea why I continue to do this as my psyche at this point is so eroded that I should be deriving pleasure from the suffering of others like some serial killer in the making.

Getting Oracle and Microsoft to play nice is like trying to get Sean Hannity and Nancy Pelosi to share a quiet evening together making babies while discussing health care and what to spend their money on. If that reference is dated then consider the choice of wiping with toilet paper or the large grain sandpaper and then using the sandpaper; or perhaps shoving a sea urchin up your nose then pulling it out the other nostril.

Here is what I am suffering with:

  • Windows XP and Windows Server 2003
  • SQL Server 2008 Integration Services
  • Native OLE DB\Oracle Provider for OLE DB, Oracle 11g R1 or Oracle 10g
  • Execute SQL Task trying to execute a PROCEDURE or PACKAGE.PROCEDURE that has input or output parameters

Connection Manager

The only non-default property is RetainSameConnection set to True (connections to Oracle are expensive). Personally, I see no need for resource pooling but that is another post.

Execute SQL Task

General

ConnectionType
OLE DB
SQLSourceType
SQLStatement:
{call your_stupid_package.dumb_ass_procedure(?,?)}
or
BEGIN your_stupid_package.dumb_ass_procedure(?,?); END;
IsQueryStoredProcedure
False (usually greyed out, but can be set via Properties, but has no bearing on anything)

Parameter Mapping; Add each parameter:

Variable Name
whatever User::variable you have defined already
Direction
Input or Output
Data Type
VARCHAR (I have not been able to get anything else to work yet)
Parameter Name
the left to right sequence number of the parameter starting at zero (so, if you only have one the parameter name is 0)
Parameter Size
the size of the variable

Conclusion

I would cite references but that vast majority of searches dead-end with single posts in forums where some poor slob went begging for help and found none; or sometimes found a reply from another kindred spirit also suffering through the same agony. Those who did have solutions were not passing parameters or were using an Expression to build the query string with parameters (doing their own SQL injection; which also does not help those using output parameters).

The above works, with a couple warnings, but works.