Recover Databases with Lost or Corrupted Control Files

If a control file has been lost or corrupted, you must first shut down the database and recover the control files before recovering the database. To shut down the database, recover the control files, and then recover the database, follow these steps:

  1. Shut down the database by entering the following command at the SVRMGR or SQL*Plus prompt:

    2. SHUTDOWN;

  2. At the appropriate prompt, start and mount the database and begin the recovery process.
    • At the SVRMGR prompt, enter the following command:

      • CONNECT INTERNAL;
      STARTUP MOUNT;
      RECOVER DATABASE USING BACKUP CONTROLFILE;

    • At the SQL*Plus prompt enter, enter the following command:

      • CONNECT SYSTEM/SYSTEM_PASSWORD AS SYSDBA;
      STARTUP MOUNT;
      RECOVER DATABASE USING BACKUP CONTROLFILE;

  3. Oracle prompts you to enter the log file names. Oracle first looks for the archive log files and automatically supplies the correct names for those that exist. If Oracle cannot find the necessary archive log files, you must manually apply the online redo logs it needs.
  4. When you apply the online redo logs manually, you must supply the full path and file name. If you enter an incorrect log, re-enter the command:

    5. RECOVER DATABASE USING BACKUP CONTROLFILE;

  5. Supply the correct online redo log file name at the prompt. Continue this procedure until Oracle has applied all logs successfully.
  6. Enter the following command at the SVRMGR or SQL*Plus prompt to bring the database back online and reset the logs:

    7. ALTER DATABASE OPEN RESETLOGS;

  7. Go to the directory where the archived redo logs are stored and delete all of the log files.
  8. If any tablespaces are still offline, enter the following command at the SVRMGR or SQL*Plus prompt to bring them back online:

    9. ALTER TABLESPACE TABLESPACE_NAME ONLINE;

  9. If you are using RMAN to recover your complete database with backed up control files, resynchronize the database information in RMAN to reflect the newly recovered database. To resynchronize the database information, follow these steps:
    • Login as oracle.
    • Enter the following command to set oracle SID to the recovered database SID:

      • ORACLE_SID=database SID

    • Enter the following commands to complete the process:

      • rman target dbuser/ dbuserpassword rcvcat catowner/catowner
      password@rman service name
      reset database

    • where:
    • dbuser is the user with dba privileges for the recovered database.
    • dbuserpassword is the dbuser password.
    • catowner is the Oracle user name of the Oracle Recovery Manager catalog owner.
    • rman service name is the name of the service used to access the database where the rman catalog is installed.