This discussion is archived
9 Replies Latest reply: May 27, 2008 11:39 PM by 26741 RSS

ora-01122 ora-01100 ora-01200

640599 Newbie
Currently Being Moderated
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 Oracle ACE Director
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    After I get the database open. How do I fix the files?

    Thank You
  • 5. Re: ora-01122 ora-01100 ora-01200
    639907 Oracle ACE Director
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 ?