Kevin Kempf's Blog

December 22, 2010

RMAN Backups – What’s in a name?

Filed under: 11g, RMAN — kkempf @ 11:34 am

Incremental, Level 0, Level 1, Differential, Cumulative

I’ve had some RMAN issues lately as a result of my Advanced Compression bug, and I began to dig through some definitions I’d taken for granted for a long time.  Reading Oracle’s documentation, it appears they’re purposefully confusing backup types.

If you’re like me, an Full backup means back everything up.  An incremental backup means backup everything changed since the last full or incremental, and a differential means backup all changes since the last full.  Based on those assumptions, as far as I can tell from Oracle’s documentation, this table may help you translate. Let’s assume a full database backup runs weekly and would be about 2gb and the nightly unique change is 100mb:

  • a full backup = incremental 0
  • a differential backup = incremental 1 cumulative
  • an incremental backup = incremental 1 (sometimes also called a differential incremental, just to further confuse)
  • a standalone “point in time” full = full
Industry Name Oracle’s Name RMAN Syntax Notes Approx. Backup Size for Example
?Full (Island) Full backup database Cannot be used with any incrementals. 2gb
Full Incremental 0 backup incremental level 0 cumulative database This full can be used with incrementals. 2gb
Differential Incremental 1 Cumulative backup incremental level 1 cumulative database This differential includes all changes since the last full. 100mb + 100mb/day
Incremental Incremental 1 Differential backup incremental level 1 database This incremental includes all changes since the last incremental. 100mb

Incremental vs. Differential

I suppose one last topic would be in order, which would revolve around whether one might use Incremental (Incremental 1 Differential) or Differential backups (Incremental 1 Cumulative).  From a practical standpoint, let’s assume you’re doing a restore via RMAN,

  • Incremental (Incremental 1 Differential)
    • Backups go faster and require less storage
    • Restores require the Incremental 0, plus all Incremental 1 backups, plus archivelogs as applicable (slower)
  • Differential (Incremental 1 Cumulative)
    • Backups go slower and require more storage
    • Restores require the Incremental 0, plus 1 Incremental 1 backup, plus archivelogs as applicable (faster)

Parting Shot

Is it just me, or is Oracle’s naming contrary to industry standards?  Are they purposefully trying to confuse things?  If I went to an IT person who didn’t know Oracle and said I was running an Incremental Differential backup, I believe they’d think I had no concept of backup terminology.

December 21, 2010

Making Oracle RDBMS play nice with 11i

Filed under: 11g, 11i, Oracleisms — kkempf @ 2:25 pm

Fighting with Oracle Support

Determining a Version of the Truth

As often happens, I’m writing this post as a result of getting burned, badly, by the various versions of the truth the world’s slowest support site, My Oracle Support, can provide.  In my case, as a result of applying PSU 11.1.0.7.4 to the database and being a user of advanced compression, I was in a situation where my database, while running, was virtually unrecoverable as the archive logs it was writing were all corrupt.  After resolving the issue (which I’ll detail below) it got me thinking about how, as a DBA, you have to go to so many different, disparate sources within Oracle’s own support site to get a complete answer.

Without thinking about it too hard, I came up with 6 critical, disparate sources of information I need to cross check for my environment.  I stumbled upon the 7th during the course of writing this entry.  For the record, I run RDBMS 11.1 on 64-bit Linux, using Advanced Compression and Dataguard, against an 11i Oracle Applications Environment

  • Interoperability Patches
  • Patch Set Updates (PSUs)
  • Post-PSU Patch Set Overlays
  • Recommended Performance Patches
  • Critical Patches for Advanced Compression
  • Dataguard Recommended Patch Bundle
  • Oracle Recommended Patches – – Oracle Database

Interoperability Patches

Any DBA who has been working in an 11i environment for long is familiar with this one.  It’s a fundamental road map of how to get your database certified against 11i, and should always be your starting point.  In my case, this would be document 452783.1, and it requires 9 RDBMS patches:

Patch Set Updates

Lets say I’ve decided to be a good DBA and apply Oracle RDBMS patch set updates (PSU’s).  These are cumulative, quarterly patch sets described by Oracle as “proactive cumulative patches containing recommended bug fixes”.   I have to admit, I like the concept, except I very naively assumed that these would contain some reasonable approximation of what  I would come up with if I independently researched all the patches I thought I needed.  You can find all of this information under Doc 854428.1, and each PSU conveniently bundles all the RDBMS fixes into one opatch bundle (in the case of 11.1.0.7.4, opatch tells me this bundle contained 408 bug fixes).  In my case, this amounted to only one patch:

Patch Set Update Overlays for 11i

I didn’t know these existed until recently (thanks, Steven), but if you’re running 11i and apply a PSU, you need to go to Doc 1147107.1, and in my case, this amounted to 6 more patches.

Recommended Performance Patches

This is where it gets a lot dicier, and in a hurry.  To be honest, this document is such a hodge-podge it makes the rest of My Oracle Support look downright usable.  This set of patches, outlined in Note 244040.1, are “a list of performance-related patches that Oracle E-Business Suite Development recommends should be applied”.

After scrolling through hundreds of 10g patches, I come to what I believe to be a list of applicable patches, but I’m stumped.  Some are labeled 11.1.0.7, and some 11.1.0.7.0.  I’d be inclined to say that 11.1.0.7.0 is a specific version (as opposed to 11.1.0.7), except the ones listed there say the bugs are fixed in 11.2.  So lets go on the conservative side, and assume I should apply anything with 11.1.0.7 or 11.1.0.7.0, unless I specifically see some contrary information.  I’m not going to get too picky, but it would be nice if 11.1.0.6 patches didn’t follow 11.1.0.7 patches.  Makes the whole process just take longer.

10g and Later Oracle Database (Performance Related)

  • 7533387
  • 8303523
  • 8508056
  • 8580883
  • 8447623
  • 8602840
  • 7522002
  • 7533387
  • 8303523
  • 8800514
  • 9320786
  • 8792821

10g and Later Oracle Database (Not Performance Related) What does this even mean “Not Performance Related”?

  • 7627743
  • 8199107
  • 7639602
  • 8874285
  • 7291739
  • 7684818

Oracle Database 11g Advanced Compression Option

  • 8409203 (this patch, and a half dozen others, says it’s for 11.1.0.7 and “fixed in 11.1.0.7”)
  • 8483871
  • 8608377
  • 8599477
  • 8364676
  • 8857905
  • 8983266
  • 8914197
  • 8876094
  • 8834636
  • 8980613
  • 9166322
  • 9275072
  • 9341448

It’s worth noting that after the RDBMS patches, this document goes on for days with recommended functional 11i patches.  I’m not including them in my list here for two reasons.  First, they were mostly sub 4000000 series patches.  That means they’ve existed for about a thousand years, and you’ve probably already got them by now.  Second, it doesn’t really flow well with this posting, which is specifically addressing RDBMS patching.

List of Critical Patches Required For Oracle 11g Table Compression

Following Note 1061366.1, I find only one (bundle) patch required for Advanced Compression.  Of note is that the applicable patch is dependent upon which version of the Patch Set Update you’re on (11.1.0.7.4 in my case).

Dataguard Recommended Patch Bundle

According to Note 7628357.8 there’s a bundle for Dataguard.  I’m not entirely certain if I’m supposed to apply patch 7676737 or 7628357, so I’ll list them both

  • 7676737
  • 7628357

Oracle Recommended Patches – – Oracle Database

I stumbled on document 756671.1, and noticed it had yet more patches (and some duplicates) to add to the list under the category “EBusiness Suite R11i Certification”

  • 7295298
  • 7253531
  • 6530141

My Advanced Compression Bug

As hinted at earlier, the bug I was hitting required RDBMS patches 9019113 and 9294495 to resolve the issue.  While un-addressed, the database was writing logs which were corrupt.  This meant that my database was effectively irrecoverable.  If you’re running advanced compression with 11i and apply PSU’s, I would urge you to apply these two patches.  Note that these patches don’t fall under PSU’s or recommended performance patches (advanced compression).  I could see where they wouldn’t be that important.  A recoverable database isn’t that important.  This issue manifested itself in the fact that my physical standby started choking on redo logs after I put in PSU 11.1.0.7.4; otherwise it would have been undetected until I tried to run my next clone.

Adding up the Score

For anyone who made it this far, congratulations!  The point of this exercise is to state, in the most obvious possible terms, how convoluted Oracle’s approach to RDBMS patchsets is.  Would anyone in their right mind pull all 55 patches listed above, and apply them blindly?  Why aren’t these 54 patches (all but the PSU itself) just included in the PSU’s, with the option to not apply them if you’re not an 11i user?  Barring that, why not have an 11i RDBMS PSU which contains these other 54+ patches (I’m sure there would be more, as CRS and RAC seem to add dozens of patches as well).  How long does it take to research this, how do I know how often the documents get updated, and which of these 55 are in conflict with each other?  How do I know I haven’t missed some other, seemingly random note, advising me to apply some other patches?

In the Real World

If you’re like me, you apply what looks relevant, seems safe, has been regression tested, or fixes a bug.  Then you get on a stable version of the RDBMS and you try not to screw with it too much.  The CPU/PSU model really screws that up, and I don’t know exactly how to make it fit in.  It’s certainly not a science so much as an art, at least the way Oracle makes it so hard to keep up with now.

For What It’s Worth

If I were making a Christmas wish list for Oracle Support, it would be simple.  Take all the time and effort you spend using Flash to make your support site so completely unusable, and instead come up with a new tab.  Call it “Compatibility” or “Interoperability” and give me a web page where I can use drop down boxes and check boxes to enter in a version of the database, with whatever options I might be using, and show me every patch I need to look at related to it.  For example, in my case, I’d love to be able to ask, via MOS, “What are the relevant patches for RDBMS 11.1.0.7 with Dataguard and Advanced Compression options, running in split tier configuration on 64-bit Linux against Oracle Applications 11i?”.   One can always hope..

Firing Windows 7 (Again)

Filed under: Linux, Ubuntu, Windows 7 — kkempf @ 10:21 am

It wasn’t my idea

I bought a Dell mini 11 running Windows 7 starter edition about a year ago for home and recently the complaints began rolling in about how slow it was. I witnessed this first hand on vacation, when I tried to use this thing to simply browse the web. For various reasons, it was always frustrating. One time it would insist on restarting to install a security update, or take 10 minutes to shut down because it was “finishing” the update. Another time, it would take minutes to recover from the lid being closed. I believe that on a cold boot after a security patch, from power on to Internet Explorer 8 it would take about 3 days 5 minutes.  This kind of defeats the whole purpose of a lightweight, fast web platform.

Enter Ubuntu Netbook Remix

Ubuntu Unity Desktop

As I sometimes mention, I use Ubuntu as my work platform, so I’m very familiar with it.  Ultra stable, fast bootup, no drama.  It was with some reluctance that I went to install Ubuntu Netbook Edition on the Netbook, as I feared that the inability to install a Windows executable (yes, you can use Wine, but realistically, it’s not a 100% solution) would be a deal breaker.  I’m not the one using this netbook 90% of the time, so it felt like a gamble.  I pulled the install and landed it on a USB stick, kissed Windoze goodbye and ran the installer.  Cake.

Windows 7: You’re Fired

Not My Idea!

After 3 months, I can safely say that Ubuntu is going over great.  It boots in about 30 seconds, and wakes up from sleep unfailingly.  It does exactly what I think a netbook is supposed to do: provide a fast, convenient platform for wireless internet connectivity to do simple tasks like check webmail, IM, and browse the web.  If a netbook can’t do this fast and simple, why not just go get a notebook or a full PC right?  Having fantastic applications available like Chrome, Open Office, Adobe Flash/Reader and Pidgin (IM) makes this experience so much better.  It easily tethers to my Blackberry via Bluetooth, so this makes internet while traveling (or on the road, for that matter, in the passenger seat!) easy.

That Other Notebook in the House

As proof of adoption of Ubuntu, now all I ever hear about is how annoying the Windoze 7 (Home Premium) notebook is.  It’s always wanting to update something (Windows, Acrobat, Java, McAfee, Office) and it runs like molasses.  This is a fresh, 64-bit install on a dual core Dell with 4gb of RAM.  The truth is that Windows 7 is a fine operating system, but really only enjoyable on premium hardware.

December 7, 2010

A couple of important changes I stumbled across

Filed under: 11g, 11i — kkempf @ 10:20 am

RDBMS patchsets are now installsets

Oracle quietly released 11.2.0.2 last month, and I thought I’d try the upgrade this week.  What I didn’t expect to find was that Oracle had significantly changed the rules about how these minor upgrades work.  In a nutshell, there’s no more incremental patches for these upgrades; you have to go out and pull the whole enchilada every time now.  The way I see it, there’s pro’s and con’s to this.

  • New installs are much simpler, just install the version you want
  • Existing installs get to pull a ton of install CD’s which they don’t need.  In this case, 4.8gb

In the past, it was a couple hundred mb I think.  While this change is fine, I suppose, I do far more upgrades than fresh installs these days, so in the end it just means a lot more chewing up the internet link at work.  What would make this convenient, I think, would be a single file to download, instead of having to pull 7, so I could just fire it up and forget about it for the day.  7 downloads would heavily impact our internet connection, 1 does not.  Anyone listening at Oracle?

Incidentally, the install patch number is 10098816.

A warning regarding PSU’s and 11i

During my November 2010 maintenance window, I was finally able to install the 11.1.0.7.4 PSU (July 2010) into my production ERP environment.  In short order, I ran into some rather serious performance issues where the optimizer was making really bad cost estimates revolving around a select which involved 3 unions.  Admittedly, it was never a query I was proud to have running, but some consultant developed it and it worked, and I was disinclined to mess with it for those two reasons.  I ended up spending a significant amount of time tuning the query to get it working well again.  While never a bad exercise, I wonder now if I’d run into a known issue which would have been fixed had I known about Note ID 1147107.1 In short, this note details follow up RDBMS patches which need to be applied after running a PSU against an 11i/R12 database.

Thanks once again to Steven Chan for providing this nugget of information.  I honestly don’t know how I’d ever even know about these kinds of details.  I was blissfully unaware that there were known 11i/PSU issues which required additional patching (6 patches for 11.1.0.7.4).  At the moment I’m inclined to leave things alone, but next time I’ll know to add this note to my list before regression testing a PSU.

December 3, 2010

Discoverer 11g with 11i: Upgrading from 11.1.1.2 to 11.1.1.3

Filed under: 11i, Discoverer — kkempf @ 3:15 pm

conFusion strikes again

I had previously posted regarding my struggles with installing, configuring, starting and stopping Discoverer 11.1.1.2 (Discoverer 11g) against an 11i environment.  Well at least Oracle is consistent.  If you can even figure out how to actually install it, the newest version of Discoverer (11.1.1.3) has all the same problems as the prior version: Webcache, startup scripts, etc.  Bundled with a convoluted upgrade only version of 11.1.1.3 and WLS restrictions as to which version can run on which and you get: conFusion Apps!

For no particular reason…

I decided to try the latest and greatest version of Fusion for my Discoverer setup.  Really, I figured that since I hadn’t installed it in production yet, it might buy me a little more support time somewhere down the road if I could sneak in a minor upgrade.  I had to assume that a X.X.X.2 to X.X.X.3 upgrade was minor, right?  Regardless, my situation was this:  I had Discoverer 11.1.1.2 installed and working with Weblogic server 10.3.2, and had previously posted here about the trials and tribulations of getting there.

The initial hurdle

For this upgrade was figuring out how to get there.  The first step is the Weblogic server.  There is no apparent “upgrade” button on the install.  I would have preferred to upgrade in place, from 10.3.2 (which is only certified against 11.1.1.2) to 10.3.3 (which is only certified against 11.1.1.3).  The problem is that the Fusion Middleware (FMW, which contains the Discoverer install) for 11.1.1.3 is not a full install, it’s an upgrade.  One might be inclined to think you would upgrade WLS to 10.3.3 and upgrade 11.1.1.2 to 11.1.1.3 and be done.  I had to open an SR to get the right order.  Effectively, WLS 10.3.3 needs to be in its own home, then you install 11.1.1.2 without the configuration option (software install only).  Then you install 11.1.1.3.  Then you run $MIDDLEWARE_HOME/bin/config.sh to configure discoverer.  Then you pick up the pieces and fix it….

The cleanest solution I could find

In my case, I just deleted or uninstalled everything.  At first, I tried to do a clean uninstall, first by running runinstaller.sh -deinstall (only Oracle would come up with syntax like this) and also uninstall.sh from the WLS 10.3.2 home.  This left a mess in the inventory, so in the end I just did a big rm -rf on the command line and “fixed” the oracle inventory to remove any references to the Fusion homes.

  1. Clean install of WLS 10.3.3 uncheck and don’t run the quickstart when it’s done
  2. Install FMW 11.1.1.2 software only install
  3. Install FMW 11.1.1.3
  4. Run the Repository Creation Utility (RCU)
  5. Run $MIDDLEWARE_HOME/as_1/bin/config.sh to complete the configuration
  6. Fix the webcache bug (doc 1113163.1) “Failed to assign port 8090: Address family not supported by protocol”if you hit it:
    1. Edit the file $MIDDLEWARE_HOME/asinst_1/config/WebCache/webcache1/webcache.xml
    2. Add <IPV6 ENABLED=”NO”/> tag below the </MULTIPORT> tag
  7. I also hit a common webcache problem this time, where the webcache wouldn’t start even after the #6 steup: webcached permissions hadn’t been set right:
    1. Navigate to $MIDDLEWARE_HOME/as_1/webcache/bin
    2. As root, run webcache_setuser.sh setroot (username) where username is the user who installed the webcache
    3. Permissions should look like this when you’re done: -rwsr-x— 1 root    dba 6796777 Dec  3 11:32 webcached
  8. Copy/link the approriate (8.0.6 home for 11i installs) tnsnames. ora file to 2 locations with a command like this
    1. cd $MIDDLEWARE_HOME/asinst_1/config
    2. ln -s $8.0.6_HOME/network/admin/SID_host/tnsnames.ora
    3. cd $MIDDLEWARE_HOME/as_1/network/admin
    4. ln -s $8.0.6_HOME/network/admin/SID_host/tnsnames.ora

For whatever reason, the default port for Discoverer changed from 8090 to 9001:

Discoverer Plus: http://hostname:9001/discoverer/plus

Discoverer Viewer: http://hostname:9001/discoverer/viewer

Weblogic Server Administration: http://hostname:7001/console

Weblogic Marketing Propoganda: http://hostname:7001

For ease of reference, here’s the variables referenced most often in Fusion installs:

MIDDLEWARE_HOME=Middleware Install Location (xxx/xxx/xxx/Middleware)
DOMAIN_HOME=$MIDDLEWARE_HOME/user_projects/domains/ClassicDomain
WL_HOME=$MIDDLEWARE_HOME/wlserver_10.3
ORACLE_HOME=Middleware/as_1
ORACLE_INSTANCE=Middleware/asinst_1

Just in case you were wondering…

Yes, it’s running fine in Google chrome, though totally unsupported.   Let’s get on the ball and certify the fastest growing browser on the planet, Oracle!

Screenshots

End Game, after quite a fight!

WLS 10.3.3, showing the admin server and discoverer services running

Create a free website or blog at WordPress.com.