Troubleshooting after Creating Control Files errors - Oracle Control Files

Q.  Which errors can be encountered by Troubleshooting after Creating Control Files after issuing the CREATE CONTROLFILE statement?
- Published on 30 Jul 15

a. Checking for Missing or Extra Files
b. Handling Errors During CREATE CONTROLFILE
c. Both A & B
d. None of the above

ANSWER: Both A & B
 

    Discussion

  • Nirja Shah   -Posted on 28 Aug 15
    - After issuing the CREATE CONTROLFILE statement, you may encounter some errors.

    - The most common control file errors are:

    1. Checking for missing or extra files
    - After creating a new control file and using it to open the database, check the alert file to see if Oracle has detected inconsistencies between the data dictionary and the control file, such as a datafile that the data dictionary includes but the control file does not list.
    - If a datafile exists in the data dictionary but not in the new control file, Oracle creates a placeholder entry in the control file under the name MISSINGnnnn (where nnnn is the file number in decimal).
    - MISSINGnnnn is flagged in the control file as being offline and requiring media recovery.
    - The actual datafile corresponding to MISSINGnnnn can be made accessible by renaming MISSINGnnnn so that it points to the datafile only if the datafile was read-only or offline normal.
    - If, on the other hand, MISSINGnnnn corresponds to a datafile that was not read-only or offline normal, then the rename operation cannot be used to make the datafile accessible, because the datafile requires media recovery that is precluded by the results of RESETLOGS.
    - In this case, you must drop the tablespace containing the datafile.
    - In contrast, if a datafile indicated in the control file is not present in the data dictionary, Oracle removes references to it from the new control file.
    - In both cases, Oracle includes an explanatory message in the alert.log file to let you know what was found.

    2. Handling Errors During CREATE CONTROLFILE
    - If Oracle sends you an error (usually error ORA-01173, ORA-01176, ORA-01177, ORA-01215, or ORA-01216) when you attempt to mount and open the database after creating a new control file, the most likely cause is that you omitted a file from the CREATE CONTROLFILE statement or included one that should not have been listed.
    - In this case, you should restore the files you backed up and repeat the procedure using the correct filenames.

Post your comment / Share knowledge


Enter the code shown above:

(Note: If you cannot read the numbers in the above image, reload the page to generate a new one.)