Saturday, May 22, 2010

Flashback Query Usage Example

Flashback Query

In my earlier blog, I explained about Flashback Database Feature usage. In this blog I wanted to give an example of a Flashback Query usage.

I received a call one Friday afternoon from one my client DBAs, just before I was getting ready to leave my office. He was extremely nervous on the phone and could not speak clearly. I said "oh no.. don't worry" and then asked him "What happened?".

He just realized that he had just dropped bunch of rows from a critical table in production. He wanted to know how long it will take to restore the database. I asked him if he knew when he performed this operation. He said he didn't know just not in the mindset to guess either. I asked him if the previous RMAN backups were good, he said yes. But he deleted bunch of archivelog files this morning. So point in time recovery just before the delete operation was out of question.

I asked him if the database had undo_retention set. He said yes with 24 hour retention. I asked him the name of the table and told him not to call me or IM me next 10 min.

Here are the steps I followed. I just estimated 3 hours and started querying from the table from last three hours. First to see how many rows were there around 3:00 P.M on Feb 1st.

SELECT count(*) FROM schema_name.card_accounts AS OF TIMESTAMP TO_TIMESTAMP('01-FEB-10 03:00:00 PM','DD-MON-YY HH:MI:SS PM')
/
20004

Ok there were 20004 records at 3:00 P.M. It only had 2 records now at 4:30 P.M. I have used dummy schema_name so you won't guess my client.

Clearly the rows were deleted between 4:00 P.M and 4:30 P.M. But it is a wide window.

SO let's query again

SELECT count(*) FROM schema_name.card_accounts AS OF TIMESTAMP TO_TIMESTAMP('01-FEB-10 03:30:00 PM','DD-MON-YY HH:MI:SS PM')
/
20004

SELECT count(*) FROM schema_name.card_accounts AS OF TIMESTAMP TO_TIMESTAMP('01-FEB-10 04:00:00 PM','DD-MON-YY HH:MI:SS PM')
/
20004

SELECT count(*) FROM schma_name.card_accounts AS OF TIMESTAMP TO_TIMESTAMP('01-FEB-10 04:30:00 PM','DD-MON-YY HH:MI:SS PM')
/
2

Alright, so the rows got deleted between 4:00 P.M and 4:30 P.M. There my phone rings again. I picked up and asked him "Hey Mike, does 20004 sounds good?". "Yeah that is what I had before" Mike replied.

I inserted those rows to a temporary table.

insert into card_accounts_tmp2 select * from schema_name.card_accounts AS OF TIMESTAMP TO_TIMESTAMP('01-FEB-10 03:00:00 PM','DD-MON-YY HH:MI:SS PM')
/
20004 rows created.

Ok go ahead and check that temporary table to see if you have everything you need! He said "No". OH, yeah I forgot to "commit".


I issued commit.

SQL> commit;

Commit complete

Ok, "Now?" I asked. He typed carelessly and said "Wow! yes they are all there! You are my hero!" , slowly raising his voice.

Now I queried the table again and found 8 rows. oh yeah, the users are in and inserting rows into that table.

Mike said that was a easy fix, he can just merge those two tables and insert back.

Ok, Problem Solved and Over! So was my Friday evening! :-(

Tuesday, May 18, 2010

How to resolve ORA-01547 errors during RMAN restore

How to resolve ORA-01547 errors during RMAN restore

You just ran a RMAN duplicate script to clone a production database. After restoring datafiles for almost 6 hours, your duplicate script failed with the following errors towards the end.

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/oradata/REP/system01.dbf'

released channel: ch1
released channel: ch2

RMAN-00571: ========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ==============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 05/17/2010 14:14:10
RMAN-03015: error occurred in stored script Memory Script
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of log thread 1 seq 11366 lowscn 74424083 found to restore
RMAN-06025: no backup of log thread 1 seq 11365 lowscn 74420409 found to restore

RMAN>


As per Oracle Support document number 274118.1, you copied the archivelog files for seq nos 11366 and 11365 to the auxiliary database server, and issued recover command.

Now oracle says it needs a log seq# 10955 to be restored and recovered. Well seq# 10278 is
from last week's LEVEL 0 backup. At this point, the only solution is to

login to target using rman and backup archivelog files to the same backup destination where
you restored the rest of the rman backups, that is accessible to the auxilairy database.

I issued the following command on the target database instance.

rman > backup archivelog all not backedup to disk;

This way only archivelog files that are not backed up are backed up, to reduce time.

Now go back to the duplicate script via RMAN and rerun it.

Now RMAN in oracle 10g would let you restart a failed RMAN duplicate script and will start recovering files from it left.

You will notice RMAN starts recovering from earlier sequence numbers 10955


starting media recovery

channel ch2: starting archive log restore to default destination
channel ch1: starting archive log restore to default destination
channel ch2: restoring archive log
archive log thread=1 sequence=10955
channel ch2: reading from backup piece /backups/ROD/ROD_20100514_s2494_p1_arc
channel ch1: restoring archive log
archive log thread=1 sequence=10953
channel ch1: restoring archive log
archive log thread=1 sequence=10954
channel ch1: reading from backup piece /backups/ROD/ROD_20100514_s2492_p1_arc
channel ch2: restored backup piece 1


After that you can open the database in "resetlogs" mode without any problem.

RMAN duplicate works to find the latest log file from controlfile if nocatalog is used. If it can't find it on the backup directory, it spits out a message immediately.

At that point, you can backup all the archivelog files and rerun the duplicate.

To avoid this issue, run duplicate command with "set until time" if possible.