RE: Tablespace recover from RMAN cold backup.

From: Kenneth Naim <kennethnaim_at_gmail.com>
Date: Tue, 21 Aug 2012 06:30:18 -0400
Message-ID: <014001cd7f87$f63f0490$e2bd0db0$_at_gmail.com>

A tablespace cannot be recovered directly from an intentional drop of a tablespace. If the datafile AMT_1.dbf was earased or corrupted then the tablespace can be recovered but once it is dropped from the database, the database doesn't know anything about it. If the database is in archive log mode you could recover the database to a point in time before the drop assuming you know when that happened and you have a backup from before that point in time and all the archive logs between the backup and the point in time in which you want to recover.

Ken

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Nisha Mohan
Sent: Tuesday, August 21, 2012 5:44 AM
To: oracle-l_at_freelists.org
Subject: Tablespace recover from RMAN cold backup.

Hi,
I am new to RMAN .Kindly help me with the following issue. I have taken RMAN cold backup using following procedure. $rman target /

RMAN> backup database;

RMAN> show all;

RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default CONFIGURE BACKUP OPTIMIZATION OFF; # default CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE MAXSETSIZE TO UNLIMITED; # default CONFIGURE ENCRYPTION FOR DATABASE OFF; # default CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/oracle/ora10gr4/dbs/snapcf_DBTEST.f'; # default

After taking the above backup,I tried to delete a tablespace for recovering it from RMAN backup as follows.

SQL> select count(*) from AMT;
  COUNT(*)

---

       563

 SQL> select TABLESPACE_NAME from dba_tables where table_name='AMT'; TABLESPACE_NAME

---

AMT_1 SQL> select FILE_NAME from dba_data_files where SQL> TABLESPACE_NAME='ACCT_DETAILS_3';
FILE_NAME

---

---

/data/DBTEST//DATA_FILES/AMT_1.dbf

SQL> drop tablespace AMT_1 INCLUDING CONTENTS; Tablespace dropped.

Now I tried to recover database as follows.

RMAN> alter database mount;
RMAN> set dbid&0103938;
RMAN> startup mount;
RMAN> restore database;

I could not see the datafile AMT_1.dbf in the datafile list shown by 'restore database' command.

RMAN> restore tablespace AMT_1;

Starting restore at 20-AUG-12
using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid�3 devtype=DISK

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 08/20/2012 14:43:14

RMAN-20202: tablespace not found in the recovery catalog RMAN-06019: could not translate tablespace name " AMT_1"

Kindly let me know what am I missing.

Thanks and Regards,
Nisha Mohan.A

• CAUTION - Disclaimer ***************** This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely for the use of the addressee(s). If you are not the intended recipient, please notify the sender by e-mail and delete the original message. Further, you are not to copy, disclose, or distribute this e-mail or its contents to any other person and any such actions are unlawful. This e-mail may contain viruses. Infosys has taken every reasonable precaution to minimize this risk, but is not liable for any damage you may sustain as a result of any virus in this e-mail. You should carry out your own virus checks before opening the e-mail or attachment. Infosys reserves the right to monitor and review the content of all messages sent to or from this e-mail address. Messages sent to or from this e-mail address may be stored on the Infosys e-mail system. ***INFOSYS******** End of Disclaimer ********INFOSYS***

--
http://www.freelists.org/webpage/oracle-l





-----


Checked by AVG - www.avg.com
Version: 2012.0.2197 / Virus Database: 2437/5211 - Release Date: 08/20/12



-----


Checked by AVG - www.avg.com
Version: 2012.0.2197 / Virus Database: 2437/5211 - Release Date: 08/20/12

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Aug 21 2012 - 05:30:18 CDT