8 Replies Latest reply on Dec 29, 2010 7:14 AM by pjsiong-JavaNet

    ORA-01111 name for data file 10 is unknown

      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
      ----- --------------------------------------------------------

      Anyone know how to correct this problem?

        • 1. Re: ORA-01111 name for data file 10 is unknown
          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' ;

          • 2. Re: ORA-01111 name for data file 10 is unknown
            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
              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.

              • 4. Re: ORA-01111 name for data file 10 is unknown
                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.

                • 5. Re: ORA-01111 name for data file 10 is unknown
                  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.


                  • 6. Re: ORA-01111 name for data file 10 is unknown
                    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.



                    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:


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


                    See : 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
                      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.

                      • 8. Re: ORA-01111 name for data file 10 is unknown
                        Hi, Cj
                        It's work!! Thanks for your suggestion.