Thursday, December 29, 2011

Recovery: UPDATE without a WHERE clause

We have all come across this at some point.  If you haven't before been so fortunate, be prepared.  This is the type of mistake that creates panic in the end user arena and is often hear all the way up to the corner office.

UPDATE customer_master
SET cust_code = 'ACME123';

Oftentimes an app will NOT have the logic built in to REQUIRE the update to include a filter to restrict the change to specific rows.  If this is the case, it is bound to happen that a data entry clerk, or customer service rep, etc, will forget the WHERE clause.

When this happens, ALL ROWS in the table are updated to the new value.  Some apps DO have a built SAVE function, or the equivalent of a COMMIT, which makes a ROLLBACK impossible.

I was confronted with this problem this past Wednesday, when a user called in a panic to request a restore of one table from the previous night's backup.  Production was at a halt until the data could be fixed.  Upon further questioning...

Do you take exports of your data?
What version of the database?  ....
What type of backups do you have available? ....RMAN ftp'd to another server
Do you have space for a restore?  ....on another server, but we'll have to replace on of the existing dbs

UGH.  These things sometimes make me cringe.  I have enjoyed being spoiled by some of the features in 10-11g and I am very "well adapted" to post 9i RMAN.  I was not looking forward to scripting a restore of this entire 100gb database in order to get back a 64k table from 2 hours ago.

After a little bit of reflection I thought possibly I could use LOGMINER.  Wasn't real excited about that.  A colleague then reminded me of a FLASHBACK related feature that was available in 9i.  Most of the fancy flashback options were introduced in 10g - I had forgotten about this particular feature.

FLASHBACK QUERY was the suggestion.  It uses a simple statement to look at table data at some point in the past IF, and ONLY IF, the before image of the data still resides in the UNDO tablespace.

There is no guarantee in 9i that the data will be available.  It just depends on the amount DML/DDL activity occurring in the database.  The following parameter allows us some control over the retention of this before data:


This is an initialization parameter and is dynamic, meaning it can be altered while the db is open.  The value is in seconds so 10800/60/60 = 3 hours.  This is what I found when I looked at the setting in the database where the data corruption occurred.  Fortunately the customer called me shortly after they realized there was nothing they could do to fix the situation themselves.  The erronenous update occurred roughly 2 hours prior to my involvement.

Realizing that the needed UNDO was not guaranteed, the first thing I did:

ALTER SYSTEM SET undo_retention=86400;

This would give me a 24 hour window in case I needed it.  To be safe, I could have also increased the size of the UNDO tablespace or made sure it was AUTOEXTENSIBLE (dba_data_files view), but I opted to work quickly and trust that I could get the data back in place before it might expire.

The first thing I did, was check to make sure the data was available from 2 hours prior.

SELECT count(*) from customer_master AS OF TIMESTAMP sysdate-2/24;


Had the data already expired, I would have received an ORA-01555: Snapshot too old error.  So I was glad to get a count of rows back.

My next step was to actually fix the corrupt data.  There are a couple different ways I could do this, but since the table was very small, I opted to create a temporary table with the good data as of the previous point in time, and the as a precaution, save off the corrupt table, then replace all the bad rows with the good data.

CREATE TABLE customer_master_good AS
   SELECT * from customer_master AS OF TIMESTAMP sysdate-2/24;

I allowed the customer to check the data and confirm it was good.  Then I proceeded to fix the bad data.

CREATE TABLE customer_master_bad AS
   SELECT * from customer_master;

TRUNCATE TABLE scott.customer_master;

(The truncate or delete is an easy option but can get tricky if there are referential integrity constraints (foreign keys) that point to parent keys in customer_master.  That was not the case here.  If constraints existed, I would have received an error and had to address it by temporarily disabling the constraints.)

INSERT INTO customer_master SELECT * from customer_master_good;

I asked the customer to check the data one last time, and then...


DROP TABLE customer_master_bad;
DROP TABLE customer_master_good;

Problem solved.  Happy user.  ...Production resumed as though nothing had ever happened.  I am sure there were repercussions for the user, but fortunately, this time, the fix was rather simple and painless.  A full database restore would have take hours to script and execute.  Today, I am grateful for FLASHBACK QUERY :).

This was a super easy example.  I have extensive experience with alternative recovery methods, flashback and otherwise, so please feel free to post questions.

Email to a friend:

Friday, December 23, 2011

And then there was a blog...

It was a bit of a challenge to set this all up, and I am quite proud so far, even though it contains very little meaningful content as yet.  The template made the basic format easy, and I tweaked the colors to stick with my theme.

If you're curious... "Foobla" comes from my beginnings as a DBA and then later as an Oracle instructor.  It seemed common to refer to "test" objects with these nonsense names.  I became very accustomed to demo'ing using these terms.  For example: CREATE TABLE foo AS SELECT * FROM bla.  I guess it would be the equivalent of CREATE TABLE t1 or t2 etc. I am not sure how much of this "foo" and "bla" I invented vs what I stumbled across in other peoples' examples, but I do know I have created a gazillion objects by those names.

So, I got the basic theme and layout in place.   I am sure I spent WAY too much time on it considering it does not have to be pretty to be useful.  Okay.

Then of course I went all geek on it and wanted tabs across the top with categories that will make searching the blog posts simple.  I added some basic Oracle topic areas, and a special tab for scripts.  I have a thousand of them... This may have been great in theory, but it is quite tedious to load them all as each requires its own static page.  Hopefully I can be diligent in uploading over time.

On that note, regarding the scripts you will find here...  I cannot claim full authorship in most cases.  I have plucked much of my scripting from other sites and other people.  It is difficult to know which ones are original vs which ones are copied or a mixture of my work and someone else's. I will just leave it at that.  I won't claim to be the genius behind them, and I will hope no one gets too upset with me if they find parts of something they authored here on my site without proper credit given.  I will give credit when possible.

The same is true for many of my how-to's.  In this digital age, I think many of us "google it" and hope we don't have to re-invent the wheel.  That's the whole point isn't it... sharing information?  Again, I will give credit where possible.

Someone, somewhere in a far off place, at some time in the (hopefully) not so distant future, will appreciate the knowledge share that is foobla.

I welcome any and all comments and criticisms, so have at it.

Thanks for reading.

Changing ownership of Grid Control jobs without recreating them

I suppose I have to start somewhere.  I have an overwhelming amount of "material" for this blog stored in various files on various computers.  I think to get over the hump of getting started, I will just post what I am working on now.

This problem has come up for me numerous times so I do believe it is worthy of a post.

Ever have a a change in your DBA staff or a consultant come and go, only to discover the jobs they so diligently created in YOUR Grid Control were created under the alias of their GC Administrator user account?  Or you may discover all of the custom Notification Rules are owned by this former employee or contractor.

It is important to remember, too, that regulatory compliance depends upon restricted administrative access to the database environment.  If an auditor finds "users," who are no longer employed by your organization, but have admin rights, he is going to ding you for sure.  In my experience, the claim "But we changed the password," however presented, is NOT a good enough justification.  It is inarguably a risk and auditors should know that..

After doing a little research online...

What I found is a reference to a metalink doc:  729822.1

Let's take a look.

1.  Currently it is not possible to change ownership of jobs in grid control.
Following internal unpublished enhancement request has been created for the same:
Unpublished bug 6679968 - CHANGE OWNERSHIP OF EM JOBS

Wonderful.  Fortunately, the article does go on to describe a workaround.

...and as it turns out we don't have a problem afterall.

When you delete the Administrator or Super Administrator, you are prompted to re-assign the object owned by the user being deleted.

Log into Grid Control as a user with Admin privs and go to "Setup" in the top right corner.

Choose "Administrators" from the left side menu.

Select the user you wish to remove.

Click on Delete.

You will be prompted for a user to whom you wish to reassign the owner's objects.  There is an option at that prompt to view the objects if you wish.

Click "Ok" when you are ready to delete the user.

Your request is processed and you are then returned to the screen listing all administrators.  You will notice that the user you just deleted will have a status of "(Deleting)" next to the name.  This should clear after a few minutes and a refresh of the screen.

Much easier than what I expected.

Thanks for reading.

Email to a friend: