restore a truncated table

Printable View

10-09-2002, 09:52 PM
qzheng

A table was truncated by a batch job and we need to restore the data. We have cold backup and the database is in archive log mode. We don't need any roll forward as the data in the table is stable until the batch job changed it.

We are trying to restore the backup to a test server, open the database, export the table, and import the table to production. Since the table is small and the tablespace where it resides is also small, we want to just restore a partial database.

The question is what files need to be restored from the cold backup? The RBS and TEMP are both very large and we are thinking to skip them and, after the database is open create new RBS and TEMP tablespace. So I guess I need the control files, SYSTEM tablespace file, USER tablespace file, and the affected TABLESPACE.

But how about the online redo log files? Do I need to restore them too? Since the test server mount points are all different, how do I tell the control files that redo log files are changed?

Thanks
10-10-2002, 02:48 AM
rotem_fo

Hi,
You want to perform a user-managed incomplete recovery.
This type of recovery needs all data files to be restored.
You cannot restore only the tablespace where the table resides.
Simply restore all datafiles.
mount the database.
recover the database to a point-in-time using UNTIL TIME.
open the database using RESETLOGS.

If you are using a different controlfile, simply mount the database, rename all the datafiles and redo log files to the correctly restored name and location.

ALTER DATABASE DATAFILE 3 RENAME TO 'new_file_name';
create new redo log files and delete old ones.

Once the database is open, you can export the table.

Cheers,
R.
10-10-2002, 02:51 AM
liyakat

restore a truncated table

hello

u have to create a control file trace, by using the following

sql> alter database backup controlfile to trace;

then open the trace file and modify as per ur needs and take it to the test server and paste it.
this will take care of ur logfiles

as for the temp as rbs u can delete them from the backup control file.

rest all the files u need to restore and also see to it the path for the datafile r alter after u come to the mount stage, i.e.

sql> startup mount

then u need alter the paths of the datafiles, as u have said that the mount points r different.

sql> alter database rename datafile as ;

then u have to recover database until time i.e the time b4 the batch job started , say if job started at 22:00 then u should recover till 21:55 or so.

sql> alter database recover until time '10-oct-2002 21:55:00';

i think this will solve ur prob.

if u have any probs or need clarification mail me at [email protected]

regards

liyakat parkar
10-10-2002, 02:58 AM
jmodic

Qzheng, your scenario should work.

No need for any recovery, no need for any backup controlfile to trace etc, as suggested by some other members.

You have clean cold backup, that is everything you need. Restore everything from your cold backup and you are ready to mount your database.

About redo logs:
When you have database in mount mode (here you will be able to drop the unwanted tablespaces) you will also update the controlfile with the new locations of your restored redo log files and datafiles.

ALTER DATABASE RENAME FILE 'old_redo_log' TO 'new_redo_log;

[Edited by jmodic on 10-10-2002 at 03:05 AM]
10-10-2002, 05:51 AM
nagarjuna

Quote:

Originally posted by qzheng
A table was truncated by a batch job and we need to restore the data. We have cold backup and the database is in archive log mode. We don't need any roll forward as the data in the table is stable until the batch job changed it.

We are trying to restore the backup to a test server, open the database, export the table, and import the table to production. Since the table is small and the tablespace where it resides is also small, we want to just restore a partial database.

The question is what files need to be restored from the cold backup? The RBS and TEMP are both very large and we are thinking to skip them and, after the database is open create new RBS and TEMP tablespace. So I guess I need the control files, SYSTEM tablespace file, USER tablespace file, and the affected TABLESPACE.

But how about the online redo log files? Do I need to restore them too? Since the test server mount points are all different, how do I tell the control files that redo log files are changed?

Thanks

Yep you can do tablespace point in time recovery (TSPITR). you can save lot of time by doing this. Steps are.

1. Find out the name of tablespace on which the truncated object was there. And find out the corresponding datafiles.
2. Restore the system tablespace datafiles and datafiles of interest (the ones you found from step one).
3. Take trace of controlfile from production and edit it to suit the new location and names of datafiles and redologs.
4. open the test db in nomount state and run the controlfile creation script prepared in step 3.
5. in mount state do the following for all those datafiles which you have not restored.

alter database datafiles <> offline drop;

Execute this command for all datafiles that are not in system and tablespace of interest.
6. recover the database untill point in a point in time where your table is with required data.
7. Open the database with resetlogs.

Try this out and let me know the result.
10-10-2002, 06:21 AM
jmodic

Again, I can't understand why everybody is talking about point in time recovery! Original poster clearly stated it doesn't need any recovery:

Quote:

Originally posted by qzheng
A table was truncated by a batch job and we need to restore the data. We have cold backup and the database is in archive log mode. We don't need any roll forward as the data in the table is stable until the batch job changed it.

All he needs is to restore the database from cold backup, drop few tablespaces, rename few datafiles/redolog files, open the database and export the truncated table.

No need for any PITR, no need to any "backup controlfile to trace".
10-10-2002, 06:45 AM
nagarjuna

Quote:

Originally posted by jmodic
Again, I can't understand why everybody is talking about point in time recovery! Original poster clearly stated it doesn't need any recovery:

Quote:

Originally posted by qzheng
A table was truncated by a batch job and we need to restore the data. We have cold backup and the database is in archive log mode. We don't need any roll forward as the data in the table is stable until the batch job changed it.

All he needs is to restore the database from cold backup, drop few tablespaces, rename few datafiles/redolog files, open the database and export the truncated table.

No need for any PITR, no need to any "backup controlfile to trace".

ignore that PITR.. what I really wanted to say is 'we can recover only the tablespace of interest'. By doing thise we can avoid the time needed to restore the entire set up of datafiles. Take my scenario.. We have 10 tablespaces each having around 6 datafiles of 5GB size each.. And my object lies in user_data tablespace.. Now the object is droped and I want to recovery the object.. Should I restore all the datafiles (300 GB) and recover or can I use the method I mentioned above??? In my method we need to restore only system tablespace (one 500mb datafile) and user_data tablespace (six 5GB datafiles) and do the recovery.. this saves lot of time..Say 6 Hrs.. hope, you got my point.. :)
10-10-2002, 07:28 AM
jmodic

Quote:

Originally posted by nagarjuna
ignore that PITR.. what I really wanted to say is 'we can recover only the tablespace of interest'. By doing thise we can avoid the time needed to restore the entire set up of datafiles. (...SNIP....) In my method we need to restore only system tablespace (one 500mb datafile) and user_data tablespace (six 5GB datafiles) and do the recovery.. this saves lot of time..Say 6 Hrs.. hope, you got my point.. :)

Yeah, I got your point, but I think you are missing the main point alltogether ;)! We are talking the same thing: restore only the needed tablespaces (SYSTEM, USER) from cold backup - that is all he needs. We agree on that, that is also the scenario the original poster was asking about,and we all confirmed that this scenario is OK. But why would you then perform any recovery???? No need for that, WHY ANY RECOVERY AT ALL???? The database was closed nicely before the cold backup was taken, so all the datafiles and controlfile are consistent!

All he has to do is to mount the database, drop the datafiles that were not restored (RBS, TEMP, ....), rename restored datafiles/redologs (if the paths in controlfile do not corespond to present locations) and open the database to export that table.

No need for any recovery, no need for any controlfile to trace and such ...

P.S.
I indeed made a typo (a mistake, if you want) in my previous posts, stating that "he will have to drop few tablespaces" - it should be "drop few DATAFILES". Maybe this caused some confusion - if so, it was my bad.

P.P.S.
And of course, when I say DROP/RENAME in the context of this discussion, it is ment as SQL command that will update the controlfile, not the physical delete/rename on filesystem.
10-10-2002, 07:37 AM
nagarjuna

ok ok.. got it. :) no trace is required. no media recovery required :)
10-11-2002, 01:31 PM
qzheng

First of all, let me thank you guys for your responses. All of them are good and I feel more intelligent than ever after reading them... :)

jmodic is right that no recovery whatsoever is needed, no backup controlfile to trace.

I don't even need to recreate RBS and TEMP tablespace.

Here is what I did:

We had a clean cold backup on tape.
Basically you need to create a temporary instance to contain the affected table the least of anything else.

1. copy the following files from tape to a different server:
(1)all redo log files.
(2)all control files
(3)the datafile that has the table
(4)system tablespace files
(5)user tablespace files
2. edit the init.ora file to update the control file path, remove the rollback_segments entry (because there is no user created rollback segments, only system rollback segment), and change shared_pool_size, buffers (because the production database is very large so I need to shrink the SGA for this tempoarary database).
3. mount the database
4. offline drop all other datafiles that have nothing to do the affected table.
5. rename redo log files to the current name
6. rename system tablespace file to the current name
7. rename user tablespace file to the current name.
8. open the database
9. export the table
10. import the table

Done!
10-11-2002, 04:35 PM
nagarjuna

Can you come up stats?? Size of datafiles, time taken for restoration, recovery, export, import, hardware configuration, etc. It will be veryyyy veryyyyy usefull :)