RMAN backup

[mooks]

Thanks Hrishy! I just wanted to confirm that I am doing the recovery the right way... Will raise a tar if problems persist.

Thanks a lot for all the replies

[hrishy]

Hi Mooks

You are welcome.Would be glad to see if you can reproduce the problem by going through this whole excersise again.

It would be great if you reproduced it and posted the results here

regards
Hrishy

[mooks]

Hi

I have logged a tar in metalink and the response i got was that i am not able to recover the tablespace that I have dropped from RMAN. :(

I can only recover the dropped tablespace from the following :

1. clone the database
2. export the objects in the dropped tablespace and import it in live instance

Therefore i think RMAN should not be the only form of backup. It cannot recover everything! Cheers!

[hrishy]

Hi Mooks

I for the curiosity tested this and was able to sucessfully restore the dropped tablespace .Using the steps that i mentioned above. I am not sure why metalink guys told you recovering a dropped tablespace is not possible.

My rman script was

RMAN> run {
2> set until time "to_date('08-07-2005 18:58:00','dd-mm-yyyy hh24:mi:ss')";
3> restore database;
4> recover database;
5> sql "ALTER DATABASE OPEN RESETLOGS";
6> }

The to_date was used so rman can restore the old controlfile.

and here is the output after the database was open

SQL> select * from v$tablespace;

       TS# NAME                           INC
---------- ------------------------------ ---
         0 SYSTEM                         YES
         1 UNDOTBS                        YES
         2 TOOLS                          YES
         4 TEMP1                          YES
         5 USERS                          YES

There is another of dropped tablespace recovery its called TSPITR.Look up for that in the docs.

I am not sure why metalink guys told you this is not possible.

I would be travelling and may not have acesses to oracle for sometime in case you dont get updates to this thread.

regards
Hrishy

[mooks]

Hi

Thanks for testing it out. Did you dropped the datafile or the tablespace from the database or just phyically from the server? I hope that we did not have any miscommunication!

I know that I can recover tablespace or datafiles dropped phyically from the server in RMAN. However, I am trying to recover the tablespace from the command "drop tablespace test" which the answer I got is not possible unless i clone the db or import the tablespace.

I think the answers they gave are true, but i am actually looking for a solution within RMAN recovery, which i was told by the metalink guys that it is not possible for dropped tablespace.

I did read up about TSPITR, but as my database is running on standard edition, therefore this feature is not supported :(

Maybe you can share with me how did u create and drop the tablespace please? Have a nice trip Hrishy and hope to hear from you soon! Cheers!

[hrishy]

Hi Mooks

I dropped the tablespace using

drop tablespace tablespace_name including contents option

and then i deleted the datafile from the os level.

afterwards i just deleted all my datafiles at the os level and tried restoring the database using the script that was pasted above and did a incomplete recovery with the rman script above and i was able to get my tablespace back.

regards
Hrishy

[mooks]

Hi

I tried the whole exercise again. This time the tablespace was restored at db level only! The only difference from the previous recovery was that I have removed the datafile from OS level this time. I only used the drop tablespace command previously.

When I tried to access the data in the table I got the following error:

ORA-01110: data file 11: '/oracle/app/product/9204/dbs/MISSING00011'

There is a missing datafile from the OS level, it was not restored. How about yours? Did you manage to get all your data and datafiles restored??

Thanks!

[hrishy]

Hi Mooks

I am using 9.2.0.4 and in that version i had deleted the datafile at the os level and the tablespace and the datafile was restored.

regards
Hrishy

[mooks]

Hi

I am using 9.2.0.4 too... but i really do not know what went wrong for me that I am unable to recover the datafiles at OS level only...

Anyway... I appreciate your kind attention and help for this thread

[mooks]

Hi

I have finally recovered the dropped tablespace I am posting the full solution here and hope that someone will find it useful

1. Drop tablespace test including contents;

2. Shutdown and startup mount

3. Restore controlfile
run {
set until time "to_date('25-07-2005 17:22:00','dd-mm-yyyy hh24:mi:ss')";
restore controlfile to '/tmp/control01.ctl' ; }

4. Shutdown and copy all backup control file to actual location

5. startup mount and starts recovery

run {
set until time "to_date('25-07-2005 17:22:00','dd-mm-yyyy hh24:mi:ss')";
restore database;
recover database;
sql "ALTER DATABASE OPEN RESETLOGS"; }

You have successfully recovered from your dropped tablespace! Case closed

Thanks hrishy for all your contributions! I will get back to the metalink guy with the solution!