9 Replies Latest reply: Feb 18, 2010 8:57 AM by 753513

unable to restore datafile from backup

492049 Mar 20, 2006 6:10 AM

hi,
I have taken complete database backup using RMAN. When i try to restore datafile 2 which is already there in the backup, RMAN says it cannot find that datafile.

I have snapshot of whole thing below.

************************************************************************************************

-bash-2.05b$ rman target /

Recovery Manager: Release 9.2.0.4.0 - Production

Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.

connected to target database: VEN (DBID=2559410986)

RMAN> restore datafile 2;

Starting restore at 20-MAR-06

using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=10 devtype=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 03/20/2006 17:21:01
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 2 found to restore

RMAN> list backup;

List of Backup Sets
===================

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
6 Full 704K DISK 00:00:00 20-MAR-06
BP Key: 6 Status: AVAILABLE Tag: TAG20060320T162917
Piece Name: /u01/orahome/dbs/06heerat_1_1
List of Datafiles in backup set 6
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
3 Full 67610 20-MAR-06 /u01/ven/data/users01.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
7 Full 76K DISK 00:00:01 20-MAR-06
BP Key: 7 Status: AVAILABLE Tag: TAG20060320T163521
Piece Name: /u01/orahome/dbs/07heerm9_1_1
List of Datafiles in backup set 7
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
4 Full 67807 20-MAR-06 /u01/ven/data/o1_mf_users_21x31qdk_.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
8 Full 156M DISK 00:00:45 20-MAR-06
BP Key: 8 Status: AVAILABLE Tag: TAG20060320T163805
Piece Name: /u01/orahome/dbs/08heerrd_1_1
SPFILE Included: Modification time: 14-MAR-06
List of Datafiles in backup set 8
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 67895 20-MAR-06 /u01/ven/data/system01.dbf
2 Full 67895 20-MAR-06 /u01/ven/data/o1_mf_sys_undo_21f30cyo_.dbf
3 Full 67895 20-MAR-06 /u01/ven/data/users01.dbf
4 Full 67895 20-MAR-06 /u01/ven/data/o1_mf_users_21x31qdk_.dbf

RMAN>

**********************************************************************************************************

I have checked physical backup file It is there.

-bash-2.05b$ ls -ltr /u01/ven/data/o1_mf_users_21x31qdk_.dbf
-rw-r----- 1 oracle dba 104859648 Mar 20 16:53 /u01/ven/data/o1_mf_users_21x31qdk_.dbf

And the database is in MOUNT state.

SQL> select name,open_mode from v$database;

NAME OPEN_MODE
--------- ----------
VEN MOUNTED

Every thing seems fine.but i am unable to restore datafile 3.
Even if i try to restore other files, RMAN is saying that it is unable to find....
Why this is happening...?
Please do give you opinions.

Thanks.

1. Re: unable to restore datafile from backup

475667 Mar 20, 2006 10:39 AM (in response to 492049)

Hi

Every thing seems fine.but i am unable to restore datafile 3.

RMAN> restore datafile 2;

You are restoring datafile 2 and not datafile 3!

Bye, Aron
Like Show 0 Likes (0)

Actions
2. Re: unable to restore datafile from backup

492049 Mar 20, 2006 11:33 PM (in response to 475667)

Hi

Sorry,It was a typing mistake.
I am unable to restore datafile 2
when i say

RMAN> restore datafile 2;

Starting restore at 21-MAR-06

using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 03/21/2006 11:02:15
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 2 found to restore

Not only that datafile, i am unable to restore any of the datafiles.

RMAN> restore database;

Starting restore at 21-MAR-06

using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 03/21/2006 11:04:07
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 4 found to restore
RMAN-06023: no backup or copy of datafile 3 found to restore
RMAN-06023: no backup or copy of datafile 2 found to restore
RMAN-06023: no backup or copy of datafile 1 found to restore

I have delete all the archive logs in the database,After taking the backup,But Rman has record all the archivelogs.does not having archive logs effect restoration.
Like Show 0 Likes (0)

Actions
3. Re: unable to restore datafile from backup

475667 Mar 21, 2006 2:39 AM (in response to 492049)

Hi

I will suggest you some steps so that you are sure that the backups really exists.

1. crosscheck backup of datafile 2; (or others) and check if they are AVAILABLE.

2. list expired backup of datafile 2;

3. list backup of datafile 2 summary;.

If you see that you have problems with the results you can try to execute a restore of the full database, not only a datafile (restore database;), and see if you still have problems.

Bye, Aron
Like Show 0 Likes (0)

Actions
4. Re: unable to restore datafile from backup

492049 Mar 21, 2006 3:18 AM (in response to 475667)

I have fallowed steps you have told and putting snapshot below.Full database restore also not working.

******************************************************************************
RMAN> crosscheck backup of datafile 2;

using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=13 devtype=DISK
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u01/orahome/dbs/08heerrd_1_1 recid=8 stamp=585592685
Crosschecked 1 objects

RMAN> list expired backup of datafile 2;

RMAN> list backup of datafile 2 summary;

List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Tag
------- -- -- - ----------- --------------- ------- ------- ---
8 B F A DISK 20-MAR-06 1 1 TAG20060320T163805\

RMAN> restore database;

Starting restore at 21-MAR-06

using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 03/21/2006 14:50:07
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 4 found to restore
RMAN-06023: no backup or copy of datafile 2 found to restore
RMAN-06023: no backup or copy of datafile 1 found to restore
Like Show 0 Likes (0)

Actions
5. Re: unable to restore datafile from backup

475667 Mar 21, 2006 4:27 AM (in response to 492049)

Quite strange!

Try that:

RMAN> list backup of database summary;

List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Tag
------- -- -- - ----------- ------------------- ------- ------- ---
3706 B F A DISK 14-02-2006 19:45:04 1 2 TAG20060214T194418
3732 B F A DISK 14-02-2006 19:47:39 1 2 TAG20060214T194654
3757 B 1 A DISK 21-02-2006 17:46:49 1 2 TAG20060221T174604

RMAN> restore database from tag=TAG20060221T174604;

Starting restore at 21-03-2006 11:26:29
...
Finished restore at 21-03-2006 11:26:37

Bye, Aron
Like Show 0 Likes (0)

Actions
6. Re: unable to restore datafile from backup

492049 Mar 21, 2006 9:27 AM (in response to 475667)

thanks for your response Aron.

I have tried to restore from tag...Its not working.The same set of errors it is showing.

RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 4 found to restore
RMAN-06023: no backup or copy of datafile 2 found to restore
RMAN-06023: no backup or copy of datafile 1 found to restore

I initially wanted to recover lost SYS_UNDOTS datafile (was practicing that scenario).
To do that, i have deleted datafile of SYS_UNDOTS table space after taking database backup with RMAN. After that, I could able to shutdown and startup my database without any problem.

Below is the list of datafiles after startup.
SQL> /
FILE_NAME FILE_ID TABLESPACE_NAME
-------------------------------------------------- ---------- ------------------------------
/u01/ven/data/system01.dbf 1 SYSTEM
/u01/ven/data/o1_mf_sys_undo_21f30cyo_.dbf 2 SYS_UNDOTS
/u01/ven/data/users01.dbf 3 USERS
/u01/ven/data/o1_mf_users_21x31qdk_.dbf 4 USERS

Actually datafile 2 is not present at that location (i have deleted it after taking database backup, as i told you before) , but control file is not able to recognise that the file is missing.
I came to know from v$transaction (XIDUSN = 0) that it is using SYSTEM Rollback segment.

****************************************************************************************************************

I have deleted existing backup and tried to take backup again using RMAN...its showing errors, as fallows.

RMAN> backup database;

Starting backup at 21-MAR-06
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 03/21/2006 20:01:53
RMAN-06056: could not access datafile 2

RMAN's restore proceure is any way connected with UNDO tablespace...?

Please excuse me if i am bothering you too much.
Like Show 0 Likes (0)

Actions
7. Re: unable to restore datafile from backup

475667 Mar 21, 2006 9:34 AM (in response to 492049)

I have deleted existing backup and tried to take backup again using RMAN...its showing errors, as fallows.

OK, if you deleted old backup you are not able to restore the database.

What do you mean with:RMAN's restore proceure is any way connected with UNDO tablespace...?

To execute a backup I suggest you to recreate the UNDO tablespace manually.

Bye, Aron
Like Show 0 Likes (0)

Actions
8. Re: unable to restore datafile from backup

492049 Mar 22, 2006 5:28 AM (in response to 475667)

Thanks Aron
its working after recreating undo ts.
Like Show 0 Likes (0)

Actions
9. Re: unable to restore datafile from backup

753513 Feb 18, 2010 8:57 AM (in response to 492049)

I also had same problem..

I drived me crazzy!!

I shut abort target database;
startup target database;

rerun duplicate comand .... worked very well!!

strange solution.
Like Show 0 Likes (0)

Actions

Go to original post