Overview
In order to fix that, we have to:
- Mount the database (if it is down, although shouldn't be)
- Clear the logfile groups
- Open the database normally
Mount
Now, firstly, to mount the database we have to have:
- Parameter file(pfile or SPFILE)
- Control file (Binary, NOT SQL commands)
If we have these two files, we can mount ANY database regardless if we have or don't have: datafiles, redo log files or whatever. S
SQL> startup mount; ORACLE instance started. Total System Global Area 1.0689E+10 bytes Fixed Size 2237776 bytes Variable Size 8220838576 bytes Database Buffers 2449473536 bytes Redo Buffers 16924672 bytes Database mounted.
Clear log files
Once we have the database mounted, we can check if all the files are correct and the header isn't corrupted:
NAME STATUS FILE# CONTROL_FILE_SCN DATAFILE_SCN DATAFILE_STATUS ------------------------------------------------------------ -------- ----- ---------------- ---------------- ---------------- /dbLFALP/system01.dbf SYSTEM 1 14894573171135 14894573171135 Startup Normal /dbLFALP/sysaux01.dbf ONLINE 2 14894573171135 14894573171135 Startup Normal /dbLFALP/tb_crew_large-02.dbf ONLINE 3 14894573171135 14894573171135 Startup Normal /dbLFALP/users01.dbf ONLINE 4 14894573171135 14894573171135 Startup Normal /dbLFALP/ix_crew_large-01.dbf ONLINE 5 14894573171135 14894573171135 Startup Normal /dbLFALP/ix_crew_medium-01.dbf ONLINE 6 14894573171135 14894573171135 Startup Normal /dbLFALP/ix_crew_small-01.dbf ONLINE 7 14894573171135 14894573171135 Startup Normal /dbLFALP/ix_crl_large-01.dbf ONLINE 8 14894573171135 14894573171135 Startup Normal /dbLFALP/ix_crl_medium-01.dbf ONLINE 9 14894573171135 14894573171135 Startup Normal /dbLFALP/ix_crl_small-01.dbf ONLINE 10 14894573171135 14894573171135 Startup Normal /dbLFALP/ix_mpp_large-01.dbf ONLINE 11 14894573171135 14894573171135 Startup Normal NAME STATUS FILE# CONTROL_FILE_SCN DATAFILE_SCN DATAFILE_STATUS ------------------------------------------------------------ -------- ----- ---------------- ---------------- ---------------- /dbLFALP/ix_mpp_medium-01.dbf ONLINE 12 14894573171135 14894573171135 Startup Normal /dbLFALP/ix_mpp_small-01.dbf ONLINE 13 14894573171135 14894573171135 Startup Normal /dbLFALP/ix_ops_large-01.dbf ONLINE 14 14894573171135 14894573171135 Startup Normal /dbLFALP/ix_ops_medium-01.dbf ONLINE 15 14894573171135 14894573171135 Startup Normal /dbLFALP/i00-01.dbf ONLINE 16 14894573171135 14894573171135 Startup Normal /dbLFALP/tb_base-01.dbf ONLINE 17 14894573171135 14894573171135 Startup Normal /dbLFALP/tb_crew_large-01.dbf ONLINE 18 14894573171135 14894573171135 Startup Normal /dbLFALP/tb_crew_medium-01.dbf ONLINE 19 14894573171135 14894573171135 Startup Normal /dbLFALP/tb_crew_small-01.dbf ONLINE 20 14894573171135 14894573171135 Startup Normal /dbLFALP/tb_crl_large-01.dbf ONLINE 21 14894573171135 14894573171135 Startup Normal /dbLFALP/tb_crl_medium-01.dbf ONLINE 22 14894573171135 14894573171135 Startup Normal NAME STATUS FILE# CONTROL_FILE_SCN DATAFILE_SCN DATAFILE_STATUS ------------------------------------------------------------ -------- ----- ---------------- ---------------- ---------------- /dbLFALP/tb_crl_small-01.dbf ONLINE 23 14894573171135 14894573171135 Startup Normal /dbLFALP/tb_mpp_large-01.dbf ONLINE 24 14894573171135 14894573171135 Startup Normal /dbLFALP/tb_mpp_medium-01.dbf ONLINE 25 14894573171135 14894573171135 Startup Normal /dbLFALP/tb_mpp_small-01.dbf ONLINE 26 14894573171135 14894573171135 Startup Normal /dbLFALP/tb_ops_large-01.dbf ONLINE 27 14894573171135 14894573171135 Startup Normal /dbLFALP/tb_ops_medium-01.dbf ONLINE 28 14894573171135 14894573171135 Startup Normal /dbLFALP/tb_ops_small-01.dbf ONLINE 29 14894573171135 14894573171135 Startup Normal /dbLFALP/tools-01.dbf ONLINE 30 14894573171135 14894573171135 Startup Normal /dbLFALP/t00-01.dbf ONLINE 31 14894573171135 14894573171135 Startup Normal /dbLFALP/ix_ops_small-01.dbf ONLINE 32 14894573171135 14894573171135 Startup Normal /dbLFALP/undo_01.dbf ONLINE 33 14894573171135 14894573171135 Startup Normal
So once they have been fixed, we can proceed with the clearing:
SQL> alter database clear logfile group 1; Database altered. SQL> alter database clear logfile group 3; Database altered. SQL> alter database open; Database altered.
Monitor the alert log, just to be sure that all is fine. Once the database opens, please switch couple times to guarantee consistency and that all mechanisms are working.
SQL> alter system switch logfile; System altered. SQL> / System altered. SQL> / System altered.