Point in time Recovery of full database

[V.Tamilvanan]

Hi,
I have a oracle 8i running in archivelog mode. Today a hour back around 11:30 am I accidently ran a SQL script which screwed all my data on the database.
I have a rman Level0 backup of yesterday night 8 pm and the last archive was yesterday night 8 pm only after that the system didn't archive anything.
Right now the database is running with corrupted data. I need to recover the database(roll forward) to the time around 11:00 am today(the time before I ran the script).
Can somebody help me.
TIA
-Tamil

[Yogeeraj_1]

Hi,

You should recover the tables that has been accidentally been modified, as follows:

If possible, keep the database that experienced the user error online and available for normal use. Back up all datafiles of the existing database in case an error is made during the remaining steps of this procedure.

1. Restore a database backup to an alternative location, then perform incomplete recovery using a restored backup control file to the point just before the table was dropped.

2. Export the lost data from the temporary, restored version of the database using the Oracle utility Export. In this case, export the affected tables.

3. Rename all the affacted table in Production.

4. Import the exported data into the permanent copy of the database using the Oracle Import utility.

5. Delete the files of the temporary, reconstructed copy of the database to conserve space.


This is the easiest way.

tell me if feasible, otherwise we can consider other option.

All the best.

Yogeeraj

[Yogeeraj_1]

hi again,

another recovery option we can consider is Tablespace Point in Time Recovery (TSPITR).

Attached a note from metalink

Good luck
Yogeeraj

[Brian Crabtree]

You will want to run something like the following from rman.

run {
allocate channel t1 type 'sbt_tape';
allocate channel t2 type 'sbt_tape';
allocate channel t3 type 'sbt_tape';
allocate channel t4 type 'sbt_tape';
set until time '2003-04-08:11:30:00';
restore database;
recover database;
}

It would be better if you have a backup controlfile not associated with rman. If you dont, then I will try to find the commands to recover the controlfile.

Brian

[Indira Aramandla · Accepted solution]

Hi,

yes you can recover the database to a point in time (which is 11 AM 09-APR-03.

1.It depends on how many tables data was corrupted with you script that you executed.

2. Cant the database be used if the other tables are fine and only a small group is effected.

3. How much down time can you efford.

- if the answer for question 1 and 2 is yes and you cannot efford a long down time then
restore into a test/dev server. Once you have restored on the test/dev server, recover the database untill 11:00 am 09-APR-03. Then startup the database. Export those tables data that you know are corrupted. Then on the other server where you d=table data is corrupted, truncate them, and import the exported data. This way there is no down time and you ahve the data of the corrupted tables as on 11:00 am.

On the other hand if answers for question 1 & 2 are NO and you do not have a choice for down time then if you are sure your last night backup was successfull, then you can restore into the current database server.

To restore the database.

1. Shutdown the database.
2. Remove the dbf, rdo and ctl files form the database (SID) datafiles directory.
3. Restore the dbf, rdo, ctl files.
4. Restore the archive logs from last night 8:00 pm and you will have the current days archve logs on the disk itself.
5. After the databse has been restored from the hotbackup of 8:00 pm last night,
6. recover it until 11:00 am this morning.

7. SVRMGR> connect internal
8. startup mount
9. recover database using backup controlfile until time '2003-04-09:11:00:00';
10. This will prompt you for the archive logs and you can enter the archive log file anme along with the path, or press enter if the log file in there in the requested path.

make sure you specify the logs until 11:00 am.
11. at the end you will see
Log applied.
Media recovery complete.

12. alter database open resetlogs;

13. verify the following.
select from v$recoverlog and v$recover_file to make sure there is not files for recovery.

14. then shutdown and if there is time take a cold backup after recovery and then startup the database.

[Tim Sanko]

First Panic and get it over with.

shutdown oracle

srvmrgl> connect internal;
shutdown

Second remove all database and control files etc, but make certain you leave the archive logs.

then restart the database in restricted mode


svrmgrl >recover database using backup controlfile until time '2003-04-09:11:00:00'

You should get a message that says something like recovery complete.

I recommend full backups with down databases. as it makes this a simple recovery. If you used a hot backup tool, there may be other issues.

If time duration of a backup is a concern for a database being down, I can recommend several other strategies.

I am an EMC bigot. Their timefinder product on a symm makes these little oopsies less demanding and critical.

If you had split the BCVs
I suppose you could always write your own for massive mirroring and splitting of disks etc, but EMC has a package that runs on a really fast array.

[Shannon Petry]

Just wanted to bring up an important point for future scripting.

*caugh* Test Server *caugh*

Have your company budget for one if you dont have one, and never run untested scripts on production.

Regards,
Shannon

[V.Tamilvanan]

Hi all,
Thanks to everyone who helped me.
As Indira told I have moved all the corrupted database,control,redolog files to a different directory. Then I restored the database from the previous day Rman level0 backup and recovered the database using until time option. Now my database is running OK.
Thanks to everyone who responded me.
Thanks & regards,
V.Tamilvanan.