Removing Control file from Oracle Database 11g

Removing a control file from a database in Oracle 11g is a delicate operation because control files are critical for the database to function properly. The control file contains metadata about the database's structure, including data files, log files, and backup information. Before proceeding with the removal of a control file, it's important to follow a safe procedure to avoid corrupting or damaging the database. Below are the steps you would follow to remove a control file from an Oracle 11g database:
Steps to Remove a Control File from Oracle 11g:

1. Identify the Control Files:
   • First, check the current control files in use by the database.
   • You can do this by running the following query:
     

     SELECT name FROM v$controlfile;
     

   • This will show the paths of the control files that the database is using.
2. Shut Down the Database:
   • You need to shut down the database instance to safely remove a control file. This can be done using the following command:
     

     SHUTDOWN IMMEDIATE;
     

   • This ensures that the database is in a consistent state before removing any control files.
3. Modify the init.ora or spfile (if using SPFILE):
   • You must remove the reference to the control file you want to delete from the initialization parameter file (init.ora) or the server parameter file (spfile).
   • If using an init.ora file, edit the file and remove the reference to the control file you want to remove. For example:
     

     control_files = (/u01/oracle/oradata/db/control01.ctl, /u01/oracle/oradata/db/control02.ctl)
     

     
     Remove the unwanted control file from this line.
   • If using an spfile, update the parameter using the following SQL command:
     

     ALTER SYSTEM SET control_files = '/path/to/remaining/control01.ctl', '/path/to/remaining/control02.ctl' SCOPE=SPFILE;
     

4. Physically Remove the Control File:
   • Once the control file is removed from the init.ora or spfile, you can safely delete the file from the operating system.
   • For example, on Linux/UNIX, you would run a command like:
     

     rm /u01/oracle/oradata/db/control02.ctl
     

5. Start the Database:
   • After updating the configuration and removing the physical control file, restart the database:
     

     STARTUP;
     

   • Oracle will now start with the remaining control files.
6. Verify the Changes:
   • After the database has started, verify that the control file you removed is no longer listed in the system by running the following query again:
     

     SELECT name FROM v$controlfile;
     

Important Notes:

• Multiple Control Files: Oracle databases are usually configured with multiple control files (for redundancy). You should always have at least one control file in place for the database to function. Removing all control files or reducing the number of control files below one will prevent the database from starting.
• Backup: Before removing any control file, it is critical to take a backup of the control files and the database in case something goes wrong.
• Check Control File Multiplexing: Ensure that you have a backup or multiplexing strategy in place before removing any control files. Control files should be mirrored across different disk locations for redundancy.

Conclusion: Removing a control file from an Oracle 11g database involves updating the database parameter files (either `init.ora` or `spfile`), physically removing the control file from the operating system, and restarting the database to ensure the configuration changes are applied correctly. Always ensure that you have multiple copies of control files for safety and backup purposes.

If you have more control file copies than you need, you may want to drop one.To remove a control file from your database, you can't just delete the file while the database is running. Instead, you should follow these steps:

1. Identify the control file to be removed.
2. Make certain that you will have at least two copies left after deleting the file.
3. Shut down the database.
4. Delete the file.
5. Edit the database's initialization parameter file, find the control_files entry, and remove the name of the file that you just deleted from the list of control files for the database.
6. Restart the database.

The key thing to understand here is that the initialization file points to all the control files for a database . Oracle reads the initialization file whenever you start an instance, and opens all the control files in the list. The following control_files entry lists three files:

control_files = (e:\oracle\oradata\COIN\control01.ctl,

f:\oracle\oradata\COIN\control02.ctl,

g:\oracle\oradata\COIN\control03.ctl)

To delete a control file, you must remove it from the list. You can do that only when the database is completely shut down.
You will learn how to add control files to your database in the next lesson.