9 Replies Latest reply: May 28, 2008 1:39 AM by 26741 RSS

    ora-01122 ora-01100 ora-01200

    640599
      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
          639907
          Dear,

          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?

          Regards,

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

            I am running 10.2.0.1.0 and Sun solaris.

            This is a production enviroment.

            No backups anything....

            Thank you,

            D
            • 3. Re: ora-01122 ora-01100 ora-01200
              639907
              HI,

              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>
              SQL> ALTER DATABASE DATAFILE '/dbprd/oracle/rman/rman_users_01.dbf'
              OFFLINE;

              Database altered.

              SQL>
              SQL> ALTER DATABASE OPEN;

              Database altered.

              SQL>


              I hope this help you,

              Regards,

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

                Thank You
                • 5. Re: ora-01122 ora-01100 ora-01200
                  639907
                  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.

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

                       cursor colcur (ind_in varchar2)
                       is
                  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);
                  begin
                       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

                  end;

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


                  Regards,

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

                    Also take a look in this note:

                    http://cursos.atica.um.es/DBA10g1/anexos/COPIAS%20SEGURIDAD-RECUPERACION/Nota_198640.1_RECUPERAR%20FICHEROS%20VARIOS%20ESCENARIOS.pdf

                    Regards,

                    Francisco Munoz Alvarez
                    • 7. Re: ora-01122 ora-01100 ora-01200
                      640599
                      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
                        639907
                        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.

                        Regards,

                        Francisco Munoz Alvarez
                        • 9. Re: ora-01122 ora-01100 ora-01200
                          26741
                          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 ?