10 Replies Latest reply: Jun 18, 2012 7:42 AM by user10088255 RSS

    rman filename for datafile is missing in the control file

    user10088255
      Hi All,

      DB:11.2.0.2.0
      Oracle Apps:12.1.3
      O/S:AIX 6.1 64 bits


      RMAN backup throws the error:

      RMAN-06169: could not read file header for datafile 40 error reason 1
      RMAN-06169: could not read file header for datafile 41 error reason 1
      RMAN-06169: could not read file header for datafile 40 error reason 1
      released channel: oem_disk_backup
      RMAN-00571: ===========================================================
      RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
      RMAN-00571: ===========================================================
      RMAN-03002: failure of backup command at 06/14/2012 07:54:43

      On further investigations:

      FILE_NAME
      --------------------------------------------------------------------------------
      FILE_ID TABLESPACE_NAME STATUS
      ---------- ------------------------------ ---------
      /test/test1/db/tech_st/11.2.0/dbs/MISSING00040
      40 SYSAUX AVAILABLE

      /test/test1/db/tech_st/11.2.0/dbs/MISSING00041
      41 SYSAUX AVAILABLE


      Could we skip the backup of the above missing files using RMAN -> backup database skip inaccessible;

      On further research, this would compromise tablespace SYSAUX recoverability

      Questions:

      1) What caused the above missing files to get created?
      2) What is recommended: Recover the datafiles?

      Thanks for your time!

      Regards,
        • 1. Re: rman filename for datafile is missing in the control file
          Hemant K Chitale
          You've likely done a CREATE CONTROLFILE earlier but not included these files in the list of datafiles. So they got created as "missing".


          Hemant K Chitale
          • 2. Re: rman filename for datafile is missing in the control file
            rajeysh
            try this,

            example:
            SQL> alter database rename file '/u01/app/oracle/product/10.2.0/db_1/dbs/MISSING00005' to '/u01/app/oracle/oradata/rev1/users02.dbf';   
            Database altered.   
            SQL> recover datafile 5;   
            ORA-00279: change 810892 generated at 10/31/2010 16:53:26 needed for thread 1   
            ORA-00289: suggestion :   
            /u01/app/oracle/flash_recovery_area/REV1/archivelog/2010_10_31/o1_mf_1_7_%u_.arcORA-00280: change 810892 for thread 1 is in sequence #7   
            
              
            Specify log: {<RET>=suggested | filename | AUTO | CANCEL}   
            auto   
            Log applied.   
            Media recovery complete.   
            refer:- http://oracleinstance.blogspot.in/2010/10/recover-missing-datafileafter.html

            Edited by: rajeysh on Jun 15, 2012 5:18 PM
            • 3. Re: rman filename for datafile is missing in the control file
              user10088255
              Thanks

              But the missing files are related to SYSAUX tablespace and we could rename the missing files to sysaux04.dbf and sysaux05.dbf after puttiing the database in Mount Mode.

              We created sysaux03.dbf yesterday.

              Then recover datafile 40 and 41?

              Is the above approach ok to proceed?

              Regards,
              • 4. Re: rman filename for datafile is missing in the control file
                CKPT
                user10088255 wrote:
                Thanks

                But the missing files are related to SYSAUX tablespace and we could rename the missing files to sysaux04.dbf and sysaux05.dbf after puttiing the database in Mount Mode.

                We created sysaux03.dbf yesterday.

                Then recover datafile 40 and 41?

                Is the above approach ok to proceed?

                Regards,
                yes if you recover database, these files will be created.

                Below is example:-
                R recover database;
                 
                Starting recover at 21-FEB-12
                using channel ORA_DISK_1
                 
                starting media recovery
                 
                archived log for thread 1 with sequence 18 is already on disk as file C:\ORACLE\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2012_02_21\O1_MF_1_18_7N6MQBWS_.ARC
                archived log for thread 1 with sequence 19 is already on disk as file C:\ORACLE\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2012_02_21\O1_MF_1_19_7N6NXV4M_.ARC
                archived log file name=C:\ORACLE\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2012_02_21\O1_MF_1_18_7N6MQBWS_.ARC thread=1 sequence=18
                "creating datafile file number=7 name=C:\ORACLE\ORADATA\ORCL\USERS03.DBF"
                archived log file name=C:\ORACLE\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2012_02_21\O1_MF_1_18_7N6MQBWS_.ARC thread=1 sequence=18
                archived log file name=C:\ORACLE\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2012_02_21\O1_MF_1_19_7N6NXV4M_.ARC thread=1 sequence=19
                • 5. Re: rman filename for datafile is missing in the control file
                  user10088255
                  Thanks

                  But this is a recently cloned env 2 days back and we do nto have any backups now:

                  RMAN> restore datafile 40 preview;

                  Starting restore at 15-JUN-12
                  using target database control file instead of recovery catalog
                  allocated channel: ORA_DISK_1
                  channel ORA_DISK_1: SID=681 device type=DISK

                  RMAN-00571: ===========================================================
                  RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
                  RMAN-00571: ===========================================================
                  RMAN-03002: failure of restore command at 06/15/2012 09:21:11
                  RMAN-06026: some targets not found - aborting restore
                  RMAN-06023: no backup or copy of datafile 40 found to restore





                  RMAN> restore datafile 41 preview;

                  Starting restore at 15-JUN-12
                  using channel ORA_DISK_1

                  RMAN-00571: ===========================================================
                  RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
                  RMAN-00571: ===========================================================
                  RMAN-03002: failure of restore command at 06/15/2012 09:21:55
                  RMAN-06026: some targets not found - aborting restore
                  RMAN-06023: no backup or copy of datafile 41 found to restore

                  RMAN>



                  What approach is now recommended to proceed and resolve this issue?

                  Thanks,
                  • 6. Re: rman filename for datafile is missing in the control file
                    tychos
                    Hi user10088255,
                    Do you have more information available?
                    Do you have a full backup available of this database?
                    When were the datafiles dropped?
                    Can you post the output of:
                    select created from v$database?
                    select name, CREATION_TIME, CHECKPOINT_TIME from v$datafile where file# = 40;
                    Regards,
                    Tycho
                    • 7. Re: rman filename for datafile is missing in the control file
                      Talip Hakan Ozturk
                      You created datafiles after backup is taken. Your full backup doesn't contain your new datafiles. But archive log files contain all information about new datafiles.

                      In this situation "restore datafile 40 preview;" command doesn't effect. You can recover these datafiles in recovery step as CKPT says.

                      The reason of this error may be
                      1 - filename is MISSINGxx in the control file
                      2 - file is offline
                      3 - file is not verified
                      4 - DBWR could not find the file
                      5 - unable to open file
                      6 - I/O error during read
                      7 - file header is corrupt
                      8 - file is not a datafile
                      9 - file does not belong to this database
                      10 - file number is incorrect
                      12 - wrong file version
                      13 - control file is not current.

                      I think your control file is not current

                      Talip Hakan Ozturk
                      http://taliphakanozturken.wordpress.com/
                      • 8. Re: rman filename for datafile is missing in the control file
                        user10088255
                        SQL> select created from v$database;

                        CREATED
                        ---------
                        13-JUN-12

                        SQL>
                        SQL> select name, CREATION_TIME, CHECKPOINT_TIME from v$datafile where file# = 40;

                        NAME
                        --------------------------------------------------------------------------------
                        CREATION_ CHECKPOIN
                        --------- ---------
                        /test/test1/db/tech_st/11.2.0/dbs/MISSING00040



                        SQL> select name, CREATION_TIME, CHECKPOINT_TIME from v$datafile where file# = 41;

                        NAME
                        --------------------------------------------------------------------------------
                        CREATION_ CHECKPOIN
                        --------- ---------
                        /test/test1/db/tech_st/11.2.0/dbs/MISSING00041


                        Thanks,
                        • 9. Re: rman filename for datafile is missing in the control file
                          tychos
                          Hi,
                          Can you explain what happened with this database on 13-JUN-12? (alert log will have some details)
                          Was it created as a clone and some datafiles were forgotten?
                          If so you have no options other than to do this clone exercise again.
                          Regards,
                          Tycho
                          • 10. Re: rman filename for datafile is missing in the control file
                            user10088255
                            Thanks Tycho and all dear friends, who had taken out their time to help!

                            Clone was redone after all!

                            Regards,