OraFAQ Forum: Backup & Recovery » Restore table fails

Home » RDBMS Server » Backup & Recovery » Restore table fails (10.2.0.4 on RHEL 5.1)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Restore table fails [message #468472]

Fri, 30 July 2010 02:53

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

Report message to a moderator

Re: Restore table fails [message #468502 is a reply to message #468472]

Fri, 30 July 2010 05:46

Mahesh Rajendran
Messages: 10708
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?

Report message to a moderator

Re: Restore table fails [message #468526 is a reply to message #468502]

Fri, 30 July 2010 09:49

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

Report message to a moderator

Re: Restore table fails [message #468534 is a reply to message #468526]

Fri, 30 July 2010 10:38

babuknb
Messages: 1736
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

Report message to a moderator

Re: Restore table fails [message #468584 is a reply to message #468472]

Fri, 30 July 2010 16:49

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

Report message to a moderator

Previous Topic:	Hot backup
Next Topic:	Recovery of Deleted datafile

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Dec 22 13:47:04 CST 2024