for e.g. database unable up cause of datafile corruption

SQL> startup
ORACLE instance started.

Total System Global Area 1610612736 bytes
Fixed Size 2177912 bytes
Variable Size 396149896 bytes
Database Buffers 1207959552 bytes
Redo Buffers 4325376 bytes
Database mounted.
ORA-01113: file 2 needs media recovery
ORA-01110: data file 2: '/oradata/oracle/ts/undotbs01.

commonly the database unable be startup if UNDO or SYSTEM table space need recovery, if these happen we need correct temporary workaround to handle this, all workaround for each tablespace datafile is similar

do the recovery

SQL> recover database;
ORA-00279: change 7516226638 generated at 05/05/2008 12:32:11
needed for thread 1
ORA-00289: suggestion : /oradata/oracle/ts/arc/1_42_653916655.dbf
ORA-00280: change 7516226638 for thread 1 is in sequence #42

Specify log: {ret=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00308: cannot open archived log
‘/oradata/oracle/ts/arc/1_42_653916655.dbf’
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3

Open database

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 2 needs media recovery
ORA-01110: data file 2: '/oradata/oracle/ts/undotbs01.dbf'

this condition force us to append archivelog or logfile consist appropriate sequence, if there not available… database unable up till to ever

but we have a temporary solution, below :

  • Edit Init file
    Remark parameter undo_management dan undo_tablespace
    add this parameter :
    UNDO_MANAGEMENT=MANUAL
    _ALLOW_RESETLOGS_CORRUPTION = TRUE
    _ALLOW_ERROR_SIMULATION = TRUE
    _CORRUPTED_ROLLBACK_SEGMENTS =(_SYSSMU1,_SYSSMU2,_SYSSMU3, ...)
  • get value _CORRUPTED_ROLLBACK_SEGMENTS from [OS] the command cd system_datafile_directory
    strings system01.dbf | grep _SYSSMU | cut -d $ -f 1 | sort -u
  • shutdown database , and turn on with that initfile already edited. Pfile must be wrote in startup command .
    SQL> startup mount
    pfile='/data1/oracle/product/10.2.0/dbs/initts.ora';
    ORACLE instance started.
    Total System Global Area 1610612736 bytes
    Fixed Size 2177912 bytes
    Variable Size 396149896 bytes
    Database Buffers 1207959552 bytes
    Redo Buffers 4325376 bytes
    Database mounted.
  • Recover database until cancel
    SQL> recover database until cancel;
    ORA-00279: change 7516226638 generated at 05/05/2008 12:32:11
    needed for thread 1
    ORA-00289: suggestion :
    /oradata/oracle/ts/arc/1_42_653916655.dbf
    ORA-00280: change 7516226638 for thread 1 is in sequence #42    Specify log: {ret=suggested | filename | AUTO | CANCEL}
    CANCEL
    ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
    ORA-01194: file 1 needs more recovery to be consistent
    ORA-01110: data file 1: ‘/oradata/oracle/ts/system01.dbf’
    ORA-01112: media recovery not started
  • Open database
    SQL> ALTER DATABASE OPEN RESETLOGS;
    Database altered.

after the database up, backup important data using exp/imp

You Should Also Check Out This Post:

More Active Posts: