Home » RDBMS Server » Backup & Recovery » Restore & Recover Database excluding a tablespace. (11g)
Restore & Recover Database excluding a tablespace. [message #517739] Wed, 27 July 2011 17:30 Go to next message
prax_14
Messages: 64
Registered: July 2008
Member
Friends,

I am doing some test, seeking your expert opinion.

I have a database and have the following backup strategy.

1) Database running in archive log mode.
2) First I backup all the table space excluding one tablespace 'DP_TS_LOB'.
RMAN> CONFIGURE EXCLUDE FOR TABLESPACE 'DP_TS_LOBS';
RMAN> BACKUP DATABASE;

3) I take a separate Backup of DP_TS_LOB tablespace.
RMAN> BACKUP TABLESPACE DP_TS_LOB;

4) I backup all the archived redo logs.
RMAN> SQL 'ALTER SYSTEM ARCHIVE LOG CURRENT';
RMAN> backup archivelog all delete input;


Now I wanted to restore the database from the backup excluding the DP_TS_LOB and bring it up and running. DP_TS_LOB is huge and used only on certain work flows. I don't want the database to be down until we restore DP_TS_LOB. I wanted to restore all the other tablespace and bring the database up operational and restore and recover the DP_TS_LOB tablespace datafile's in background taking the datafiles of DP_TS_LOB tablespace offline.

I tried the following but unsuccessful.

RMAN> restore controlfile to 'g:\ctl_bck\ctl_01' from autobackup;
RMAN> restore spfile to 'g:\ctl_bck\spfile' from autobackup;
RMAN> sql "create PFILE = ''G:\ctl_bck\PFILE'' from SPFILE = ''G:\ctl_bck\SPFILE''";

Update the new parameter with new control file name.
Copied the control file and parameter file to corresponding location.
RMAN> startup nomount pfile=D:\app\diva\product\11.1.0\db_1\database\pfile;
RMAN> restore database skip tablespace 'dp_ts_lob';
RMAN> alter database mount;
RMAN> sql 'alter database datafile 8 offline'; ==> datafile of DP_TS_LOB tablespace.

First I assumed "Recover Database" will only recover online database files. But got the following error.
RMAN> recover database;

Starting recover at 27-JUL-11
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 07/27/2011 18:23:03
RMAN-06094: datafile 8 must be restored

Then I tried recovering data file separately, but same error.

RMAN> recover datafile 1;

Starting recover at 27-JUL-11
using channel ORA_DISK_1

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 07/27/2011 18:24:30
RMAN-06067: RECOVER DATABASE required with a backup or created control file


I know this could be done,but i am missing something. Any help is highly appreciated.

Regards,
Prakash R




Re: Restore & Recover Database excluding a tablespace. [message #517749 is a reply to message #517739] Wed, 27 July 2011 22:15 Go to previous messageGo to next message
mail2subrahmanyam
Messages: 2
Registered: July 2011
Location: Hyderabad
Junior Member
TRY THIS
RMAN> RECOVER DATABASE SKIP TABLESPACE 'DP_TS_LOBS';
Re: Restore & Recover Database excluding a tablespace. [message #517765 is a reply to message #517749] Thu, 28 July 2011 00:54 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do NOT post in UPPER case, Please read OraFAQ Forum Guide.

Regards
Michel

Re: Restore & Recover Database excluding a tablespace. [message #517766 is a reply to message #517739] Thu, 28 July 2011 00:59 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I tried the following but unsuccessful.
RMAN> restore controlfile to 'g:\ctl_bck\ctl_01' from autobackup;


1/ Why do you restore the control file, you can use the current one
2/ Which time has this restored control file? If it is during the "backup database" then it does not know the backup of archived logs.
3/ Recover also needs to specify the exclusion:
recover database skip forever tablespace XXX;

Regards
Michel
Re: Restore & Recover Database excluding a tablespace. [message #517950 is a reply to message #517766] Fri, 29 July 2011 13:14 Go to previous message
prax_14
Messages: 64
Registered: July 2008
Member
Michel Cadot/mail2subrahmanyam,

Thanks guys, recover with skip option worked.

Actually i am testing out different recovery scenarios and that why I tried restoring control file from backup.
Previous Topic: Deffered Segment Management + RMAN Backup
Next Topic: RMAN Optimization Parameter
Goto Forum:
  


Current Time: Thu Mar 28 19:26:29 CDT 2024