-
Hi
I have tested the codes too but it doesn't seem to be able to recover the database (including the tablespace which I have dropped). Why? :confused:
Code :
create tablespace test
datafile '/oracle/oradata/test01.dbf'
size 2M reuse;
create user ora_user
identified by ora_user
default tablespace test
temporary tablespace temp
quota unlimited on test;
# Login as ora_user and create a table students and insert data into table students.
# RMAN backup
run
{
allocate channel ch1 type disk;
sql 'alter system archive log current';
backup format '/backup/orcl/%d_t%t_s%s_p%p'
filesperset=4
database;
release channel ch1;
}
RMAN > list backup;
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 4072944 06-JUL-05 /oracle/oradata/system01.dbf
8 Full 4072944 06-JUL-05 /oracle/oradata/tools01.dbf
12 Full 4072944 06-JUL-05 /oracle/oradata/test01.dbf
# Drop tablespace
drop tablespace test including contents;
# RMAN Recovery
shutdown immediate;
startup mount;
# time is set to after rman backup and before tablespace dropped
RMAN>
run {
set until time "to_date('06-07-2005 16:50:00','dd-mm-yyyy hh24:mi:ss')";
restore database;
recover database;
}
RMAN > list backup;
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 4072944 06-JUL-05 /oracle/oradata/system01.dbf
8 Full 4072944 06-JUL-05 /oracle/oradata/tools01.dbf
12 Full 4072944 06-JUL-05
Why is the RMAN list empty for the deleted tablespace? That's the reason why there is nothing to reover from? :rolleyes:
-
Hi
You need to get the old controlfile back
RMAN >STARTUP FORCE NOMOUNT;
RMAN>
run {
set until time "to_date('06-07-2005 16:50:00','dd-mm-yyyy hh24:mi:ss')";
restore controlfile to /tmp/control01.ctl ;
restore controlfile from /tmp/control01.ctl ;
alter database mount;
restore database;
recover database;
}
alter database open resetlogs.
The other way is you do a Tablespace point in time recovery and then transport the tablespace from the auxillary database and plug it into your target.
I am afraid the documentation is wrong :-)
regards
Hrishy
-
Hi
I have tested the codes and encountering errors :(
run {
restore controlfile from autobackup;
alter database mount;
set until time "to_date('06-07-2005 16:50:00','dd-mm-yyyy hh24:mi:ss')";
restore database;
recover database;
}
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database recover logfile '/oracle/oradata/redo01.log'
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to controlfile by media recovery
ORA-01110: data file 11: '/oracle/oradata/test'
-
Hi
I am not sure here but can you please try this (i am speculating hmm feels like i am a Investment banker by uttering that word)
RMAN>
run {
set until time "to_date('06-07-2005 16:50:00','dd-mm-yyyy hh24:mi:ss')";
restore controlfile to /tmp/control01.ctl;
restore controlfile from /tmp/control01.ctl ;
alter database mount;
restore database;
recover database;
}
alter database open resetlogs
I need to know the ouput of what happens ?
I am suspecting that the controlfile restored did not have the entry for dropped tablespace.So you need to restore a sufficiently old controlfile.
regards
Hrishy
-
Hi
I am just evaluating RMAN before implementation, so I am still learning. :o Is RMAN commonly used?
My controlfile is autobackup and the SET TIME has to be used after the db is mounted or opened. Therefore I don't think that the codes work... :p
-
Hi
RMAN has become quite popular after 9i.we use RMAN one many of our databases.You need to invest some time and skills in learning it but its easy and not that complicated esp in 9i.
can you please try this
RMAN>
run {
set until time "to_date('06-07-2005 16:50:00','dd-mm-yyyy hh24:mi:ss')";
restore controlfile to /tmp/control01.ctl;
restore controlfile from /tmp/control01.ctl ;
alter database mount;
restore database;
recover database;
}
alter database open resetlogs
regards
Hrishy
-
Hi...
I have tested the codes and it doesn't work for me.
1. my controlfile is autobackup
2. set time can only be used after db is mounted
Do you have the codes to backup controlfile manually? :rolleyes:
-
Hi Mooks
You need to read more of the docs :-)
RMAN always includes the control file in a backup operation that includes file 1, i.e the system datafile :-)
set time should be used before the db is mounted in your case so RMAN restores a older controlfile.
You can test your scenario bye turning the auto backup of the controlfile off.
regards
Hrishy
-
Hi Hrishy :cool:
I am encountering the following errors when I tried to execute the codes.
RMAN-03002: failure of set command at 07/08/2005
ORA-01507: database not mounted
-
try mounting the database then