SQL101 Class Session 2

Session 2 of the SQL101 class this Friday…. here are the slides.  I’m actually early for a change!



Comparing exp with expdp + compression=all

What a difference between EXP and EXPDP with compression as seen below:

Just a quick note on the difference between running old school EXP and the somewhat newer EXPDP with COMPRESSION=ALL turned on…. if using COMPRESSION=ALL, it’s only available in 11g EE and you must pay the licensing fee for Advanced Compression to use “ALL” with the COMPRESSION parameter.

1st, I run the exp on a single mount point with the max recordlength=65535 and direct=y… this exp ran for 71 minutes and consumed roughly 45GB:

-rw-r–r– 1 oracle oinstall 45556327575 Oct 30 11:48 full_exp.dmp

The 2nd run was with expdp over 3 mount points and compression=all… it ran for 41 minutes and consumed roughly 8+GB:

-rw-r—– 1 oracle asmadmin 2147483648 Oct 30 13:54 tstht90_a01.dmp

-rw-r—– 1 oracle asmadmin 2147483648 Oct 30 13:54 tstht90_a02.dmp

-rw-r—– 1 oracle asmadmin 2147483648 Oct 30 13:55 tstht90_b01.dmp

-rw-r—– 1 oracle asmadmin 1443262464 Oct 30 14:08 tstht90_b02.dmp

-rw-r—– 1 oracle asmadmin  516943872 Oct 30 14:08 tstht90_c01.dmp

-rw-r—– 1 oracle asmadmin 1042018304 Oct 30 14:08 tstht90_c02.dmp

Continue reading

Scripting a People Soft clone with RMAN

I’ve done this with E-Biz so I thought I’d do it with People Soft.  Now before you go off trying this script, TEST IT FIRST!  If it blows up your database, you don’t know me!  Also, this will not work unless you’re using a catalog/repository for RMAN

This script is really made up of 3 parts:

1.the addition of some code in your RMAN backup script (assuming you already have one that is) that builds an RMAN run module for the clone build, cleans up the previous days SCP and then SCP’s your rman backups and run modules to the clone box


2.the main cloning script.  the script assumes you’re using ASM and you may need to fiddle with the script to fit your ASM structure, or if not using ASM, just comment it out, and


3.the ASM cleanup script (if you need it).


Now I’m sure there are a million ways to get this done and this may not be the most efficient…. but it works.  As you can see from the main cloning script, we don’t do a lot of post-clone stuff with the people soft clones as we normally leave that up to the app developers.

If you see any glaring errors, please let me know.

scheduler unavailable at SYS.DBMS_ISCHED

This is one of those scenarios where you get down in the weeds trying to fix a problem and can’t see the forest for the trees.  Sometimes I see the problem and get right intto the weeds without taking a step back and looking at a simpler view.  This is one of those problems.

Ever seen this when submitting a job (either via OEM or from the command line)?

java.sql.SQLException: ORA-27492: unable to run job “SYS.GATHERSCHEMASTATS_JOEBOB”: scheduler unavailable ORA-06512: at “SYS.DBMS_ISCHED”, line 185 ORA-06512: at “SYS.DBMS_SCHEDULER”, line 486 ORA-06512: at line 2

This was running, or trying to run, against a database I’ve had little interaction with and is more of a backend for a 3rd party appliance.  I dug and dug and was really scratching my head…. opened an SR with MOS.  It took him about two minutes to figure it out because he didn’t get down into the weeds…. the answer?

JOB_QUEUE_PROCESSES.  yep.  set to ZERO.  Not too many scheduler jobs are going to run with that setting.

RAC node startup problems after RHEL patch

ENV: Red Hat Enterprise Linux Server release 5.8 (Tikanga), EE

As most organizations, we have maintenance, and our maintance occurred this last Saturday which usually entails applying RHEL OS patches to our RAC clusters.  We don’t usually encounter problems…. but we did this time.  Now, normally (actually always) we don’t relink any of our oracle/clusterware after applying OS patches… that apparently will get you into trouble.

After applying the latest round of RHEL patches on the 1st node of our test environment, the CRS wouldn’t come up…. the only things you saw running when doing a ps was

ohasd.bin reboot, init.ohasd run, cssdmonitor, & orarootagent.bin

when checking the crs, you’d see this:

Continue reading

Using RMAN to unregister database


So every night, as part of my RMAN backups, I sync the controlfiles with the catalog after the backup is done, which by the way, is much faster than connecting to the catalog “while” the backup is being done.

Anyway, I now have two incarnations of yesterday’s clone in the catalog which can play havoc with my scripts when I query the catalog for RMAN backup information.  We need to get rid of the old incarnation.

Using the following method will not work on and after the example, I’ll show you what does work.

Continue reading

RMAN clone errors

ENV:, RHEL 5.8, Dell PowerEdge R710

(RMAN-06136, ORA-03113, RMAN-10041, RMAN-10024)

running a clone on our DEVHT PSoft environment…. and getting this:


sql statement: alter system set  db_name =  ”DEVHT90” comment= ”Reset to original value by RMAN” scope=spfile

 sql statement: alter system reset  db_unique_name scope=spfile

 Oracle instance shut down

RMAN-00571: ==========================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS 

RMAN-00571: ==========================

RMAN-03002: failure of Duplicate Db command at 10/16/2012 07:36:58

RMAN-05501: aborting duplication of target database

RMAN-03015: error occurred in stored script Memory Script

RMAN-06136: ORACLE error from auxiliary database: ORA-03113: end-of-file on communication channel

Continue reading