This discussion is archived
9 Replies Latest reply: Feb 18, 2010 6:57 AM by 753513 RSS

unable to restore datafile from backup

492049 Newbie
Currently Being Moderated
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 Newbie
    Currently Being Moderated
    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
  • 2. Re: unable to restore datafile from backup
    492049 Newbie
    Currently Being Moderated
    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.
  • 3. Re: unable to restore datafile from backup
    475667 Newbie
    Currently Being Moderated
    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
  • 4. Re: unable to restore datafile from backup
    492049 Newbie
    Currently Being Moderated
    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
  • 5. Re: unable to restore datafile from backup
    475667 Newbie
    Currently Being Moderated
    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
  • 6. Re: unable to restore datafile from backup
    492049 Newbie
    Currently Being Moderated
    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.
  • 7. Re: unable to restore datafile from backup
    475667 Newbie
    Currently Being Moderated
    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
  • 8. Re: unable to restore datafile from backup
    492049 Newbie
    Currently Being Moderated
    Thanks Aron
    its working after recreating undo ts.
  • 9. Re: unable to restore datafile from backup
    753513 Newbie
    Currently Being Moderated
    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.