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

    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.