unable to restore datafile from backup

492049
    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
        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
          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
            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
              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
                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
                  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
                    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
                      Thanks Aron
                      its working after recreating undo ts.
                      • 9. Re: unable to restore datafile from backup
                        753513
                        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.