RMAN backup

Hi

Hmm can you just substitute the set until date format temporarily with SCN values and check if we can restore the control files.

(note for restoring this way you have a catalog configured)

somethin like

set until sequence

regards
Hrishy

Hi

I tested this and it works for me.I do not have a catalog so i have configured a snapshot controlfile to on.

I first did a backup of my database and then tried restoring the controlfile.

Code:

---

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



executing command: SET until clause

Starting restore at 08-JUL-05

using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: restoring controlfile
output filename=/tmp/control01.ctl
channel ORA_DISK_1: restored backup piece 1
piece handle=/xxxxx/app/oracle/product/9.2.0.4.0/dbs/c-3446257643-20050708-01 tag=null params=NUL
L
channel ORA_DISK_1: restore complete
Finished restore at 08-JUL-05

---

cd /tmp
ls -ltr

-rw-r----- 1 oracle dba 6676480 Jul 8 18:59 control01.ctl

regards
Hrishy

Hi

The controlfile is autobackup or manually? :rolleyes:

Hi

I am not using a catalog .So for me to restore the controlfile i am using a autobackup.

regards
Hrishy

Hi

I am not using a catalog too. I have autobackup the controlfile too, but i am still encountering error... :confused:

I have listed the backup to ensure that the controlfile has been backup:

Starting Control File and SPFILE Autobackup at 14-JUL-05
piece handle=/oracle/app/product/9204/dbs/c-2272111974-20050714-02 comment=NONE
Finished Control File and SPFILE Autobackup at 14-JUL-05

Then I tried to restore the controlfile and I got the following error:
RMAN-06026: some targets not found - aborting restore
RMAN-06024: no backup or copy of the controlfile found to restore

Any help please??

Hi

Can you try this whole excersise again.

create a tablespace

Please take a complete backup of your database with the controlfiel backup set to auto

drop the tablespace

and now restore the controlfile which was backed up above.

I am asking you this becoz atleast this time its saying the controlfile was not found.

regards
Hrishy

Hi Hrishy

It was my mistake for the error as the controlfile cannot be overwritten which resulted in the error message the controlfile was not found! :o

Anyway I have restored the controlfile and overwrite my current control file. I brought down the db and startup mount. I did a restore and recover database before opening the database with resetlogs. I can see from the messages that my deleted tablespace has been restored.

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00011 to /oracle/oradata/test.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/oracle/t563728056_s127_p1 tag=TAG20050715T150736 params=NULL
channel ORA_DISK_1: restore complete
Finished restore at 15-JUL-05

However when i did a query on dba_tablespaces, I did not see the tablespace that I have deleted. Why? :confused: :confused:

Hi Mooks

what about v$datafile does that show you the file associated with the tablespace.

what about v$tablespace

regards
Hrishy

Hi Hrishy

I have checked those tables too, they do not reflect my dropped tablespace has been restored. :confused:

Hi Mooks

Well i suggest that you carry out this test once again a fresh and if you are able to reproduce this then i assume its a bug and probably time to raise a TAR with oracle :-)

regards
Hrishy

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

Thanks a lot for all the replies :D

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

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! :D Cheers!

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

Code:

---

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

Code:

---

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

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? :p Have a nice trip Hrishy and hope to hear from you soon! Cheers!

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

Hi

I tried the whole exercise again. This time the tablespace was restored at db level only! :cool: 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?? :o

Thanks! :D

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

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... :confused:

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

Hi

I have finally recovered the dropped tablespace :D 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 :cool:

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

Hi

Just wundering what metalink guy had to say about all this ?

:-) enjoy and dont forget to booz now you are the RMAN expert ;-)

regards
Hrishy