This discussion is archived
8 Replies Latest reply: Dec 28, 2010 11:14 PM by pj*433620*ng RSS

ORA-01111 name for data file 10 is unknown

pj*433620*ng Newbie
Currently Being Moderated
Hi,
I created two new tablespaces (11 and 12) with 1 datafile in each of them in my primary database,
I forgot to set the parameter in standby server standby_file_management=AUTO,
the standby server auto create the tablespace 11 with datafile in D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00010,
although there is no such file 'UNNAMED00010' in D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\.
in the v$tablespace and v$datafile, I can see that the entry had been created in control file in standby server.
Below is the listing of datafile in my standby server,

TS# name
----- --------------------------------------------------------
0 G:\OFSDB\OFS4\ORADATA\OFS4\SYSTEM01.DBF
1 G:\OFSDB\OFS4\ORADATA\OFS4\UNDOTBS01.DBF
2 G:\OFSDB\OFS4\ORADATA\OFS4\SYSAUX01.DBF
4 G:\OFSDB\OFS4\ORADATA\OFS4\USERS01.DBF
6 G:\OFSDB\OFS4\ORADATA\OFS4\DATA_SE.ORA
7 G:\OFSDB\OFS4\ORADATA\OFS4\INDEX_SE
8 G:\OFSDB\OFS4\ORADATA\OFS4\FLOW_1.DBF
9 G:\OFSDB\OFS4\ORADATA\OFS4\CJHIT01.DBF
10 G:\OFSDB\OFS4\ORADATA\OFS4\DBMON_TS.DBF
11 D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00010

Anyone know how to correct this problem?

Thanks
  • 1. Re: ORA-01111 name for data file 10 is unknown
    731759 Pro
    Currently Being Moderated
    What is value of the parameter STANDBY_FILE_MANAGEMENT ?

    Set the parameter value to AUTO,

    You have to use this at standby,

    alter database create datafile 'D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00010' as 'D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\original_name.dbf' ;

    Thanks
  • 2. Re: ORA-01111 name for data file 10 is unknown
    mseberg Guru
    Currently Being Moderated
    You could rebuild standby from Primary. I would probably set standby_file_management=AUTO first.

    If you do backups on the Primary then RMAN dup is fairly easy, and if you use the same directory structure on the standby it is really easy.

    Step 1.

    On the primary

    alter system set log_archive_dest_state_2=defer;

    Step 2

    Create a temp init file for the standby

    Set the following:

    db_name, control_files, db_recovery_file_dest, db_recovery_file_dest_size, compatible,
    db_block_size, undo_tablespace, undo_management, undo_retention


    Step 3

    Backup Primary

    RMAN RUN {
    allocate channel d1 type disk;
    backup format '/u01/backups/PRIMARY/df_t%t_s%s_p%p' database;
    sql 'alter system archive log current';
    backup format '/u01/backups/PRIMARY/al_t%t_s%s_p%p' archivelog all;
    backup current controlfile for standby format '/u01/backups/PRIMARY/sb_t%t_s%s_p%p';
    release channel d1;
    }


    Step 4 Copy backup to standby

    Consider any cleanup of old files on the standby now since they probably are worthless


    Step 5 Move current archive

    Move the current archive (todays) log backups to the target server.

    A list backup may show only the ones you need.

    RMAN> list backup;

    Step 6 Startup the standby

    SQL> startup nomount pfile='/u01/app/oracle/admin/PRIMARY/pfile/init.ora'

    (With the INIT file you created in Step 2)

    Start as your Primary DB NAME


    Step 7 Issue Duplicate command

    rman target sys/password@PRIMARY auxiliary /

    RMAN> run {
    allocate channel C1 device type disk;
    allocate auxiliary channel C2 device type disk;
    duplicate target database for standby nofilenamecheck;
    }


    When this completes

    Make sure you shutdown the database and then switch the ORACLE_SID=STANDBY


    Step 8 Copy init.ora from primary host

    (Or if you have a good PFILE version on the standby correct and make a new spfile)

    Step 9 Start redo apply

    On the Primary

    SQL> alter system set log_archive_dest_state_2=enable;

    Start Redo apply

    On the standby database, to start redo apply:

    SQL>startup mount


    SQL> alter database recover managed standby database disconnect from session;

    Database altered.
  • 3. Re: ORA-01111 name for data file 10 is unknown
    mseberg Guru
    Currently Being Moderated
    The closest Oracle note I found for this is 756440.1

    If it helps I did not set standby_file_management=AUTO on an early Data Guard DB, needless to say I have not forgot it since.

    MS
  • 4. Re: ORA-01111 name for data file 10 is unknown
    pj*433620*ng Newbie
    Currently Being Moderated
    Hi, Cj
    The standby_file_management is manual, i.e. the data file was not created properly in the standby database,
    that's why I am finding the solution for this.

    I tried alter database create datafile 'D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00010' as 'D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\original_name.dbf' ;
    but error occur since the file D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00010 does not exist.

    Thanks for your reply.


    vincent
  • 5. Re: ORA-01111 name for data file 10 is unknown
    pj*433620*ng Newbie
    Currently Being Moderated
    Hi, mseberg
    Recreate the standby database is the last resort I going to use.
    Just want to know if there any way to rectify the problem.

    Thanks

    Vincent
  • 6. Re: ORA-01111 name for data file 10 is unknown
    mseberg Guru
    Currently Being Moderated
    Understood. I sure don't know of a way. (Kind of figured, that why I chased on metalink)

    You could cancel the recovery on the standby and open the database and then try to create the files there.

    In a perfect world I would like to test this first.

    Regards

    MS


    In theory this should work:

    On Standby:
    Alter system set standby_file_managment=manual
    rm this datafile from filesystem
    alter database create datafile ‘path’ as ‘orginalpath’ [size];
    alter system set standby_file_managment=auto
    alter database recover managed standby database disconnect;






    This post has some promise:

    http://www.dbvisit.com/forums/showthread.php?t=190

    Edited by: mseberg on Dec 28, 2010 6:25 AM

    http://download.oracle.com/docs/cd/B19306_01/server.102/b14239/manage_ps.htm

    See : 8.3.1.2 When STANDBY_FILE_MANAGEMENT Is Set to MANUAL

    Edited by: mseberg on Dec 28, 2010 6:29 AM
  • 7. Re: ORA-01111 name for data file 10 is unknown
    731759 Pro
    Currently Being Moderated
    An another way try this ,

    1. Put the tablespace which is holding the unnamed datafile in begin backup mode.

    SQL> alter tablespace mytbs begin backup;

    2. Copy (OS copy) the missing datafile to standby datafiles location.

    3. Issue the end backup command.

    SQL> alter tablespace mytbs end backup;

    4. Create a controlfile for standby database using,

    SQL> alter database create standby controlfile as '/D:/oracle/stdcontrol.ctl';

    5. Move this alos to standby server.

    At standby,

    1. cancel the recovery mode.
    2. shut down and change the controlfile.
    3. startup the standby db in mount stage.
    4. recover standby database.

    Thanks
  • 8. Re: ORA-01111 name for data file 10 is unknown
    pj*433620*ng Newbie
    Currently Being Moderated
    Hi, Cj
    It's work!! Thanks for your suggestion.

    Vincent

Legend

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