This discussion is archived
10 Replies Latest reply: Jun 18, 2012 5:42 AM by user10088255 RSS

rman filename for datafile is missing in the control file

user10088255 Newbie
Currently Being Moderated
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 Oracle ACE
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Thanks Tycho and all dear friends, who had taken out their time to help!

    Clone was redone after all!

    Regards,

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points