7 Replies Latest reply on Feb 20, 2011 2:08 PM by Karan

    ORA-01173: data dictionary indicates missing data file from system tablespa

    misterimran
      Hello

      My Database is 11gR1 and Linux is the OS. Thought of posting it as a new thread as things have changed a lot.

      2 of my database file were lost due to hardware failure. (the datafile do not belong to SYSTEM TABLESPACE) and and the second was datafile for undo tablespace.

      I created control files with command not mentioning the lost datafiles:

      CREATE CONTROLFILE REUSE DATABASE "BSSGLB" NORESETLOGS NOARCHIVELOG
      MAXLOGFILES 32
      MAXLOGMEMBERS 5
      MAXDATAFILES 600
      MAXINSTANCES 10
      MAXLOGHISTORY 1168
      LOGFILE
      GROUP 1 '/DB/app/oracle/oradata/bssglb/redo01.log' SIZE 500M,
      GROUP 2 '/DB/app/oracle/oradata/bssglb/redo02.log' SIZE 500M,
      GROUP 3 '/DB/app/oracle/oradata/bssglb/redo03.log' SIZE 500M
      -- STANDBY LOGFILE
      DATAFILE
      '/DB/app/oracle/oradata/bssglb/system01.dbf',
      '/DB/app/oracle/oradata/bssglb/sysaux01.dbf',
      '/DB/app/oracle/oradata/bssglb/users01.dbf',
      '/DB/app/oracle/oradata/bssglb/bss01.dbf',
      '/var/oracle/oradata/bssglb/bss02',
      '/var/oracle/oradata/bssglb/system02.dbf'
      CHARACTER SET US7ASCII
      ;

      I have all the files mentioned in the datafile section of the above command.

      But somehow when i try to open the DB, first it asks for the media recover. When i recover database then it disconnects database saying:

      ERROR at line 1:
      ORA-01092: ORACLE instance terminated. Disconnection forced
      Process ID: 4768
      Session ID: 170 Serial number: 5

      Contents of the alert log file are:

      Beginning crash recovery of 1 threads
      parallel recovery started with 3 processes
      Started redo scan
      Completed redo scan
      1 redo blocks read, 0 data blocks need recovery
      Started redo application at
      Thread 1: logseq 7, block 2, scn 20356182450
      Recovery of Online Redo Log: Thread 1 Group 1 Seq 7 Reading mem 0
      Mem# 0: /DB/app/oracle/oradata/bssglb/redo01.log
      Completed redo application
      Completed crash recovery at
      Thread 1: logseq 7, block 3, scn 20356202453
      0 data blocks read, 0 data blocks written, 1 redo blocks read
      Sun Feb 20 08:16:23 2011
      Thread 1 advanced to log sequence 8
      Thread 1 opened at log sequence 8
      Current log# 2 seq# 8 mem# 0: /DB/app/oracle/oradata/bssglb/redo02.log
      Successful open of redo thread 1
      MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
      Sun Feb 20 08:16:23 2011
      SMON: enabling cache recovery
      Errors in file /Oracle/app/oracle/diag/rdbms/bssglb/bssglb/trace/bssglb_ora_4550.trc:
      ORA-01173: data dictionary indicates missing data file from system tablespace
      Error 1173 happened during db open, shutting down database
      USER (ospid: 4550): terminating the instance due to error 1173
      Sun Feb 20 08:16:25 2011
      Instance terminated by USER, pid = 4550
      ORA-1092 signalled during: alter database open...
      ORA-1092 : opiodr aborting process unknown ospid (4550_182910338848)
      Sun Feb 20 08:16:26 2011
      ORA-1092 : opitsk aborting process

      Guys please help me out of this situation.

      Regards, Imran