9 Replies Latest reply on May 28, 2008 6:39 AM by 26741

    ora-01122 ora-01100 ora-01200

      Just got to a new job.

      I try to open the database and it give me these error

      ORA-01122: database file 46 failed verification check
      ORA-01110: data file 46: '...../xxxx_index_01.dbf'
      ORA-01200: actual file size of 2316800 is smaller than correct size of 2355200

      Here is the problem.

      No backup & NOARCHIVELOG mode

      I try recreate the controlfile and reload it and still get the same error.

      What can I do to fix this problem?

      Thank You in advance.
        • 1. Re: ora-01122 ora-01100 ora-01200
          F.Munoz Alvarez

          Looks like you have a datafile corrupt. This is a production enviroment? No "BACKUP" of any type? (cold,rman,export,etc)

          Please can you give us more information regarding this problem, and also OS, and version of Oracle RDBMS?


          Francisco Munoz ALvarez
          • 2. Re: ora-01122 ora-01100 ora-01200
            Hi Francisco Munoz ALvarez,

            I am running and Sun solaris.

            This is a production enviroment.

            No backups anything....

            Thank you,

            • 3. Re: ora-01122 ora-01100 ora-01200
              F.Munoz Alvarez

              Can you try to mount the database, put offline that datafile, and see if you can open your database?

              This is a index tablespace, that mean you can drop it and recreate all the indexes withou loose your information. Something like this:

              SQL> STARTUP
              ORACLE instance started.

              Total System Global Area 79691776 bytes
              Fixed Size 777516 bytes
              Variable Size 70263508 bytes
              Database Buffers 8388608 bytes
              Redo Buffers 262144 bytes
              Database mounted.
              ORA-01122: database file 4 failed verification check
              ORA-01110: data file 4: '/dbprd/oracle/rman/rman_users_01.dbf'
              ORA-01251: Unknown File Header Version read for file number 4

              SQL> ALTER DATABASE DATAFILE '/dbprd/oracle/rman/rman_users_01.dbf'

              Database altered.


              Database altered.


              I hope this help you,


              Francisco Munoz Alvarez
              • 4. Re: ora-01122 ora-01100 ora-01200
                After I get the database open. How do I fix the files?

                Thank You
                • 5. Re: ora-01122 ora-01100 ora-01200
                  F.Munoz Alvarez
                  You can't fix the files. is this a index tablespaces, right?
                  If this is the case, please recreate the indexes again in a new tablespace or rebuild them, I'm sending you the scripts for both situations.

                  -- script to recreate the indexes.

                  my_owner varchar2(30) := UPPER('&owner');
                  my_name varchar2(30) := UPPER('&tabname');

                       cursor colcur (ind_in varchar2)
                  select * from dba_ind_columns
                       where table_owner=my_owner and table_name=my_name
                  and index_name=ind_in
                  order by column_position;
                  cursor indcur is
                       select * from dba_indEXES
                       where table_owner=my_owner and table_name=my_name;
                  createstring varchar2(4000);
                       for indrow in indcur loop
                       createstring := 'CREATE';
                  if indrow.uniqueness = 'UNIQUE' then
                       createstring := createstring || ' UNIQUE';
                  end if;
                  createstring := createstring || ' INDEX ' || indrow.index_name || ' ON '
                       || indrow.table_name || ' (';

                  for colrow in colcur (indrow.index_name) loop
                                 createstring := createstring || colrow.column_name || ',';
                  end loop; --colcur

                  createstring := rtrim(createstring,',');
                  createstring := createstring || ') ';
                  createstring := createstring || ' TABLESPACE ' || indrow.tablespace_name || ';';

                            dbms_output.put_line ( createstring );
                  dbms_output.put_line (' ' );
                  end loop; --indcur


                  ---script to rebuild the indexes in a new tablespace

                  SELECT 'alter index '
                  || index_name
                  || ' rebuild online tablespace new_indx_tablespace;'
                  FROM DBA_INDEXES
                  WHERE tablespace_name = 'OLD_DEAD_TABLESPACE';


                  Francisco Munoz Alvarez
                  • 6. Re: ora-01122 ora-01100 ora-01200
                    F.Munoz Alvarez
                    HI user637596,

                    Also take a look in this note:



                    Francisco Munoz Alvarez
                    • 7. Re: ora-01122 ora-01100 ora-01200
                      Thank You.

                      What happen if my tablesapce have mult datafiles and only 5 out 20 datafiles is bad. Should I still run your scripts to recreate the index?
                      • 8. Re: ora-01122 ora-01100 ora-01200
                        F.Munoz Alvarez
                        You can try to detect the indexes that are using that datafiles, drop them and recreate again in a new tablespace. If you can't do that, your only option is recreate all them in a new tablespace and drop the old one.


                        Francisco Munoz Alvarez
                        • 9. Re: ora-01122 ora-01100 ora-01200
                          Duplicate thread which said that 12 datafiles had this error

                          Rebuild datafile

                          Can you find out when and why so many datafiles got "corrupt"ed ?