Skip to content
Home » Oracle Database » When Almost Everything of a Database is Missing

When Almost Everything of a Database is Missing

I must assume that your Oracle software installed on OS is ready or not damaged before proceeding, otherwise, you should fix the software problem first.

Three major steps to recover a severely damaged database which lost almost everything:

  • Restore spfile.
  • Restore controlfile.
  • Restore and recover database.

Here are the detailed steps:

  1. Startup to nomount forcibly.
  2. [oracle@primary01 ~]$ rman target /
    ...
    connected to target database (not started)

    RMAN> startup nomount force;

    startup failed: ORA-01078: failure in processing system parameters
    ORA-01565: error in identifying file '+DATA/compdb/spfileprimdb.ora'
    ORA-17503: ksfdopn:2 Failed to open file +DATA/compdb/spfileprimdb.ora
    ORA-15056: additional error message
    ORA-17503: ksfdopn:DGOpenFile05 Failed to open file +DATA/compdb/spfileprimdb.ora
    ORA-17503: ksfdopn:2 Failed to open file +DATA/compdb/spfileprimdb.ora
    ORA-15173: entry 'spfileprimdb.ora' does not exist in directory 'compdb'
    ORA-06512: at line 4

    starting Oracle instance without parameter file for retrieval of spfile
    Oracle instance started

    Total System Global Area     158662656 bytes

    Fixed Size                     2211448 bytes
    Variable Size                 96469384 bytes
    Database Buffers              54525952 bytes
    Redo Buffers                   5455872 bytes
  3. Restore spfile from an autobackup or a backup piece.
  4. RMAN> restore spfile from '+data/primdb/autobackup/2012_12_22/s_802730287.978.802730291';

    Starting restore at 09-JAN-13
    using target database control file instead of recovery catalog
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: SID=25 device type=DISK

    channel ORA_DISK_1: restoring spfile from AUTOBACKUP +data/primdb/autobackup/2012_12_22/s_802730287.978.802730291
    channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
    Finished restore at 09-JAN-13
  5. Restart the database to nomount with the restored spfile.
  6. RMAN> shutdown immediate;

    Oracle instance shut down

    RMAN> startup nomount;

    connected to target database (not started)
    Oracle instance started

    Total System Global Area     839282688 bytes

    Fixed Size                     2217992 bytes
    Variable Size                792725496 bytes
    Database Buffers              41943040 bytes
    Redo Buffers                   2396160 bytes
  7. Restore controlfile from an autobackup or an external file.
  8. RMAN> restore controlfile from '+data/primdb/autobackup/2012_12_22/s_802730287.978.802730291';

    Starting restore at 09-JAN-13
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: SID=34 instance=primdb1 device type=DISK

    channel ORA_DISK_1: restoring control file
    channel ORA_DISK_1: restore complete, elapsed time: 00:00:09
    output file name=+DATA/primdb/controlfile/current.901.804270805
    Finished restore at 09-JAN-13
  9. Alter the database to mount state with the restored controlfile.
  10. RMAN> alter database mount;

    database mounted
  11. Restore the database.
  12. RMAN> restore database;

    Starting restore at 09-JAN-13
    Starting implicit crosscheck backup at 09-JAN-13
    allocated channel: ORA_DISK_1
    Crosschecked 18 objects
    Finished implicit crosscheck backup at 09-JAN-13

    Starting implicit crosscheck copy at 09-JAN-13
    using channel ORA_DISK_1
    Crosschecked 4 objects
    Finished implicit crosscheck copy at 09-JAN-13

    searching for all files in the recovery area
    cataloging files...
    cataloging done

    List of Cataloged Files
    =======================
    File Name: +data/PRIMDB/AUTOBACKUP/2012_12_22/s_802730287.978.802730291
    File Name: +data/PRIMDB/ARCHIVELOG/2013_01_09/thread_1_seq_257.985.804267587
    File Name: +data/PRIMDB/ARCHIVELOG/2013_01_09/thread_1_seq_258.982.804267595
    ...
    File Name: +data/PRIMDB/ARCHIVELOG/2012_09_25/thread_1_seq_52.286.794943881
    File Name: +data/PRIMDB/ARCHIVELOG/2012_09_25/thread_2_seq_47.287.794944085

    using channel ORA_DISK_1

    channel ORA_DISK_1: starting datafile backup set restore
    channel ORA_DISK_1: specifying datafile(s) to restore from backup set
    channel ORA_DISK_1: restoring datafile 00001 to +DATA/primdb/datafile/system.897.797943475
    channel ORA_DISK_1: restoring datafile 00002 to +DATA/primdb/datafile/sysaux.898.797943569
    channel ORA_DISK_1: restoring datafile 00003 to +DATA/primdb/datafile/undotbs1.899.797943687
    channel ORA_DISK_1: restoring datafile 00004 to +DATA/primdb/datafile/users.902.797943753
    channel ORA_DISK_1: restoring datafile 00005 to +DATA/primdb/datafile/example.900.797943711
    channel ORA_DISK_1: restoring datafile 00006 to +DATA/primdb/datafile/undotbs2.901.797943737
    channel ORA_DISK_1: reading from backup piece +DATA/primdb/backupset/2012_12_17/nnndf0_tag20121217t113005_0.1094.802265461
    channel ORA_DISK_1: piece handle=+DATA/primdb/backupset/2012_12_17/nnndf0_tag20121217t113005_0.1094.802265461 tag=TAG20121217T113005
    channel ORA_DISK_1: restored backup piece 1
    channel ORA_DISK_1: restore complete, elapsed time: 00:04:16
    Finished restore at 09-JAN-13
  13. Recover the database. Let RMAN go as far as he could.
  14. RMAN> recover database;

    Starting recover at 09-JAN-13
    using channel ORA_DISK_1

    starting media recovery

    archived log for thread 1 with sequence 75 is already on disk as file +DATA/primdb/archivelog/2012_12_20/thread_1_seq_75.1096.802524917
    archived log for thread 1 with sequence 76 is already on disk as file +DATA/primdb/archivelog/2012_12_20/thread_1_seq_76.1099.802524949
    ...
    hread_2_seq_254.998.804270043
    archived log for thread 2 with sequence 255 is already on disk as file +DATA/primdb/archivelog/2013_01_09/thread_2_seq_255.997.804270051
    archived log for thread 2 with sequence 256 is already on disk as file +DATA/primdb/onlinelog/group_4.906.797943793
    archived log file name=+DATA/primdb/archivelog/2012_12_20/thread_2_seq_70.1097.802524925 thread=2 sequence=70
    archived log file name=+DATA/primdb/archivelog/2012_12_20/thread_1_seq_75.1096.802524917 thread=1 sequence=75
    ...
    archived log file name=+DATA/primdb/archivelog/2013_01_09/thread_2_seq_255.997.804270051 thread=2 sequence=                                     255
    archived log file name=+DATA/primdb/onlinelog/group_4.906.797943793 thread=2 sequence=256
    Finished recover at 09-JAN-13

    RMAN>
  15. Open the database with resetlogs.
  16. RMAN> alter database open resetlogs;

    database opened

More related posts that you may be interested in:

Leave a Reply

Your email address will not be published. Required fields are marked *