Home » RDBMS Server » Backup & Recovery » Restore table fails (10.2.0.4 on RHEL 5.1)
Restore table fails [message #468472] Fri, 30 July 2010 02:53 Go to next message
NandKumar
Messages: 92
Registered: June 2007
Location: v$hyderabad
Member
Hi Frenz,

I have tried to restore a truncated table from the database using Metalink Note 223543.1 (Third method - restores a subset of database to a different node)

But the recovery has failed and iam unable to open the dummy database. The tablespaces which were restored sum up to 800GB. So its really loss of time when the recovery didnt succeed.

Following is the procedure I had followed and other info:

Need to restore table till: 26 July 2010 06:35 am.

Backup available: RMAN Inc L0 of 23 July and all daily Diff L1 backups plus all the archive log backups.

Have restored control file from autobackcup until time 26 July 2010 06:35 am (but it restored a control file of 26 July 2010 22:50 )

Then I followed the above metalink note. The restore was successful. But the following error can be seen in the recovery section of the same log:

Oracle Error:
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '+OFRA/dummy_db/system01.dbf'


Please suggest me where I went wrong and how to overcome this and open the database.

Do you have any alternate method of performing table restore. Please share with me your links/suggestions. All my backups are available on test box and i have all the archives available as well.


Regards
Nand
Re: Restore table fails [message #468502 is a reply to message #468472] Fri, 30 July 2010 05:46 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10628
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Post the code to used to restore/recover.
>>ORA-01110: data file 1: '+OFRA/dummy_db/system01.dbf'
Are you really using Flashback Functionality or just name the ASM Disk as such?
Re: Restore table fails [message #468526 is a reply to message #468502] Fri, 30 July 2010 09:49 Go to previous messageGo to next message
NandKumar
Messages: 92
Registered: June 2007
Location: v$hyderabad
Member
Hi Mahesh,


No, We are not using Flashback.

Here is the script that was used:

For controlfile:

RMAN> set dbid 2675257952;

executing command: SET DBID
run{
allocate channel t1 type 'SBT_TAPE' parms 'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo_DB001.opt)';
allocate channel t2 type 'SBT_TAPE' parms 'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo_DB001.opt)';
allocate channel t3 type 'SBT_TAPE' parms 'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo_DB001.opt)';
allocate channel t4 type 'SBT_TAPE' parms 'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo_DB001.opt)';
RESTORE CONTROLFILE FROM AUTOBACKUP until time "to_date('2010-07-26-06:35','YYYY-MM-DD-HH24:MI')";
release channel t1;
release channel t2;
release channel t3;
release channel t4;
}


The Controlfile that got restored was of 26-07-2010 22:50.


For DB Restore/Recovery, the script used was:

RMAN> connect target *
2> run
3> {
4>
5> allocate channel t1 type 'SBT_TAPE' parms 'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo_DB001.opt)';
6> allocate channel t2 type 'SBT_TAPE' parms 'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo_DB001.opt)';
7> allocate channel t3 type 'SBT_TAPE' parms 'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo_DB001.opt)';
8>
9> set until time "to_date('2010-07-26-06:35','YYYY-MM-DD-HH24:MI')";
10>
11> set newname for datafile '+DATAGROUP01/DB001/infodata01.dbf' to '+ofra/DUMMY_DB/infodata01.dbf';
12> set newname for datafile '+DATAGROUP01/DB001/infodata02.dbf' to '+ofra/DUMMY_DB/infodata02.dbf';
13> set newname for datafile '+DATAGROUP01/DB001/infodata03.dbf' to '+ofra/DUMMY_DB/infodata03.dbf';
14> set newname for datafile '+DATAGROUP01/DB001/infodata04.dbf' to '+ofra/DUMMY_DB/infodata04.dbf';
15> set newname for datafile '+DATAGROUP01/DB001/infodata05.dbf' to '+ofra/DUMMY_DB/infodata05.dbf';
16> set newname for datafile '+DATAGROUP01/DB001/infodata06.dbf' to '+ofra/DUMMY_DB/infodata06.dbf';
17> set newname for datafile '+DATAGROUP01/DB001/infodata07.dbf' to '+ofra/DUMMY_DB/infodata07.dbf';
18> set newname for datafile '+DATAGROUP01/DB001/infodata08.dbf' to '+ofra/DUMMY_DB/infodata08.dbf';
19> set newname for datafile '+DATAGROUP01/DB001/sysaux01.dbf' to '+ofra/DUMMY_DB/sysaux01.dbf';
20> set newname for datafile '+DATAGROUP01/DB001/system01.dbf' to '+ofra/DUMMY_DB/system01.dbf';
21> set newname for datafile '+DATAGROUP01/DB001/undotbs101.dbf' to '+ofra/DUMMY_DB/undotbs101.dbf';
22> set newname for datafile '+DATAGROUP01/DB001/undotbs102.dbf' to '+ofra/DUMMY_DB/undotbs102.dbf';
23> set newname for datafile '+DATAGROUP01/DB001/undotbs201.dbf' to '+ofra/DUMMY_DB/undotbs201.dbf';
24> set newname for datafile '+DATAGROUP01/DB001/undotbs202.dbf' to '+ofra/DUMMY_DB/undotbs202.dbf';
25> set newname for datafile '+DATAGROUP01/DB001/undotbs301.dbf' to '+ofra/DUMMY_DB/undotbs301.dbf';
26> set newname for datafile '+DATAGROUP01/DB001/undotbs302.dbf' to '+ofra/DUMMY_DB/undotbs302.dbf';
27> set newname for datafile '+DATAGROUP01/DB001/undotbs401.dbf' to '+ofra/DUMMY_DB/undotbs401.dbf';
28> set newname for datafile '+DATAGROUP01/DB001/undotbs402.dbf' to '+ofra/DUMMY_DB/undotbs402.dbf';
29> set newname for datafile '+DATAGROUP01/DB001/undotbs403.dbf' to '+ofra/DUMMY_DB/undotbs403.dbf';
30>
31>
32> restore tablespace system, sysaux, undotbs1, undotbs2, undotbs3, undotbs4, infodata;
33>
34> switch datafile all;
35>
36> sql "alter database datafile 1,2,3,4,5,6,35,36,38,39,40,41,42,43,45,50,51,52,53 online";
37>
38> recover database skip forever tablespace TEMP,USERS,TOOLS;
39>
40> sql "alter database rename file ''+REDOGROUP01/DB001/redo1a.log''  to ''+ofra/DUMMY_DB/redo1a.log'' ";
41> sql "alter database rename file ''+REDOGROUP01/DB001/redo2a.log''  to ''+ofra/DUMMY_DB/redo2a.log'' ";
42> sql "alter database rename file ''+REDOGROUP01/DB001/redo3a.log''  to ''+ofra/DUMMY_DB/redo3a.log'' ";
43> sql "alter database rename file ''+REDOGROUP01/DB001/redo4a.log''  to ''+ofra/DUMMY_DB/redo4a.log'' ";
44> sql "alter database rename file ''+REDOGROUP01/DB001/redo5a.log''  to ''+ofra/DUMMY_DB/redo5a.log'' ";
45> sql "alter database rename file ''+REDOGROUP01/DB001/redo6a.log''  to ''+ofra/DUMMY_DB/redo6a.log'' ";
46> sql "alter database rename file ''+REDOGROUP01/DB001/redo7a.log''  to ''+ofra/DUMMY_DB/redo7a.log'' ";
47> sql "alter database rename file ''+REDOGROUP01/DB001/redo8a.log''  to ''+ofra/DUMMY_DB/redo8a.log'' ";
48> sql "alter database rename file ''+REDOGROUP01/DB001/redo9a.log''  to ''+ofra/DUMMY_DB/redo9a.log'' ";
49> sql "alter database rename file ''+REDOGROUP01/DB001/redo10a.log''  to ''+ofra/DUMMY_DB/redo10a.log'' ";
50> sql "alter database rename file ''+REDOGROUP01/DB001/redo11a.log''  to ''+ofra/DUMMY_DB/redo11a.log'' ";
51> sql "alter database rename file ''+REDOGROUP01/DB001/redo12a.log''  to ''+ofra/DUMMY_DB/redo12a.log'' ";
52> sql "alter database rename file ''+REDOGROUP01/DB001/redo13a.log''  to ''+ofra/DUMMY_DB/redo13a.log'' ";
53> sql "alter database rename file ''+REDOGROUP01/DB001/redo14a.log''  to ''+ofra/DUMMY_DB/redo14a.log'' ";
54> sql "alter database rename file ''+REDOGROUP01/DB001/redo15a.log''  to ''+ofra/DUMMY_DB/redo15a.log'' ";
55> sql "alter database rename file ''+REDOGROUP01/DB001/redo16a.log''  to ''+ofra/DUMMY_DB/redo16a.log'' ";
56> sql "alter database rename file ''+REDOGROUP01/DB001/redo17a.log''  to ''+ofra/DUMMY_DB/redo17a.log'' ";
57> sql "alter database rename file ''+REDOGROUP01/DB001/redo18a.log''  to ''+ofra/DUMMY_DB/redo18a.log'' ";
58> sql "alter database rename file ''+REDOGROUP01/DB001/redo19a.log''  to ''+ofra/DUMMY_DB/redo19a.log'' ";
59> sql "alter database rename file ''+REDOGROUP01/DB001/redo20a.log''  to ''+ofra/DUMMY_DB/redo20a.log'' ";
60>
61>
62> release channel t1;
63> release channel t2;
64> release channel t3;
65> }
66>


Hope this helps.

Regards
Nand
Re: Restore table fails [message #468534 is a reply to message #468526] Fri, 30 July 2010 10:38 Go to previous messageGo to next message
babuknb
Messages: 1731
Registered: December 2005
Location: NJ
Senior Member

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below 

Cause: Media recovery with one of the incomplete recovery options ended without error. However, 
if the ALTER DATABASE OPEN RESETLOGS command were attempted now, it would fail with the specified error. 
The most likely cause of this error is forgetting to restore one or more datafiles from a sufficiently
old backup before executing the incomplete recovery.
 
Action: Rerun the incomplete media recovery using different datafile backups, a different control file
or different stop criteria.

ORA-01152: file string was not restored from a sufficiently old backup 
Cause: An incomplete recovery session was started, but an insufficient number of logs were applied to make 
the database consistent. This file is still in the future of the last log applied. 
The most likely cause of this error is forgetting to restore the file from a backup before doing incomplete recovery.
 
Action: Either apply more logs until the database is consistent or restore the database file from an older
backup and repeat recovery. 
 


Are you forgot to restore any datafile from backup?

Babu
Re: Restore table fails [message #468584 is a reply to message #468472] Fri, 30 July 2010 16:49 Go to previous message
NandKumar
Messages: 92
Registered: June 2007
Location: v$hyderabad
Member
Hi Babu,

In the approach that was followed, all tablespaces except the one in which the table resides and System, Sysaux and Undo tablespaces were deliberatley skipped from getting restored and recovered. Apart from the datafiles of those tablespaces, rest of the datafiles were restored.

Regards
Nand
Previous Topic: Hot backup
Next Topic: Recovery of Deleted datafile
Goto Forum:
  


Current Time: Tue Oct 21 19:39:00 CDT 2014

Total time taken to generate the page: 0.08358 seconds