Backup and Recovery with 11g Database Control

Leave a comment

11G Oracle Enterprise Manager Database Control now has an extensive GUI based interface to RMAN. You can view RMAN backups, run RMAN backups, check on status of jobs, schedule RMAN jobs, all within the OEM db control GUI.

But wait, there’s more!

There is also something called Data Recovery Advisor (DRA), which will AUTOMATICALLY detect database failures that require recovery, and will suggest multiple recovery approaches. It can then automatically create the needed RMAN script and then run it for you if you wish! Of course, you need to be in archive log mode and you need to have a viable RMAN backup complete.

This tool (DRA) can also be accessed from the RMAN prompt with commands like LIST FAILURE; ADVISE FAILURE, REPAIR DATABASE;

PLEASE NOTE: OEM 11g Database Control is different than OEM grid control. Highest version OEM grid control is Database control is the standalone version of OEM grid control. The features discussed here are only available in the 11G standalone database control, not the 10g grid control. Fortunately, you can have both OEM 10g grid control monitoring your 11g database PLUS run the 11g database control. At least it worked for me. I’d like to double-check this with Oracle.

How do you get OEM 11g database control working? Use the emca tool.
If you don’t have it working already, these 10g instructions work for 11g:
Recreate Database Control Repository, by Jeff Hunter (thanks Jeff!)
You might hit this problem, which I did in my test db:
Meta link doc id (733710.1) EMCA on 11g Db Fails With: Listener is Not Up

How do you stop and start database control?
emctl start dbconsole
emctl stop dbconsole
emctl status dbconsole

How do you access database control once running?

How do you see DRA within OEM 11g Database Control?
Once you are in 11g Database Control, go to the “Availability” link to see all the backup and recovery options.
DRA can be accessed via the Availability : Perform Recovery : Oracle Advised Recovery section
It can also be launched from “Advisor Central”, a link found in the “Related Links” section on the bottom of many pages.

More links on DRA
Tim Hall’s great DRA review
Using DRA from 2 Day DBA guide
Repairing Data Corruptions from DBA guide

Deferrable Foreign Keys, Sequences Gone Astray

Leave a comment

When the developers for applications have moved on to other things, troubleshooting issues that arise during production support can get interesting. Here are two recent production issues with resolutions that required adjusting Oracle components and not application code.

Issue One: A developer unfamiliar with the app added a new feature so that a new child table got added to a parent table that had several existing child tables. Interestingly the child table foreign keys linked back to a unique constraint, not a primary key. The unique constraint was on a field WIDGET_NAME. Widget names sometimes change, so the app had a procedure to change this value. It invoked a stored procedure that updated the parent and child tables accordingly.

The new child table got added with a foreign key constraint. All appeared well, but it turned out the “change widget name” feature broke. This had not been regression tested. Turns out the foreign key on the new child table was not created with the clause INITIALLY DEFERRABLE. This foreign key clause cannot be changed after the fact, so it needed to be dropped and recreated. And the stored procedure needed an extra line to update the data in the new child table. After that, all worked fine.

Issue Two: Inserts suddenly stopped working on, you guessed it, the widget table. Turns out the primary key on the widget table on a number field usually populated by a sequence generator in a INSERT trigger. I say usually because the trigger would accept an INSERT with a supplied value as well. Well, somebody supplied a value that ended up colliding with values output by the sequence generator. I ended up running a PL/SQL block to move the sequence to a higher setting. I did it the brute force way, like this:

i number;
curr number;
t number;
        select widget_id_seq.nextval into curr from dual;
        for i in curr..200000 loop
                select asset_id_seq.nextval into t from dual;
        end loop;

I see Dan Morgan has a nifty reset sequence routine, but I didn’t get a chance to try it out.

Bottom Line: Triggers and non-default clauses in contraints can create situations developers might not expect when they encounter for the first time an existing database that nobody has touched for awhile!