STEPS FOR ORACLE CONTROFILE COMPLETE RECOVERY
SQL> alter database backup controlfile to trace;
The above command may not work sometimes, in which case we need to use already taken trace file during backup. This command will generate a controlfile script in udump in the form of trace file.
SQL> shutdown immediate
[[email protected] ~]$ gotoudump location and copy the first create controlfile script to a file called control.sql
SQL> startup nomount
SQL> alter database open;
Note: After creating control files using above procedure, there will be no SCN in that. So server process will write the latest SCN to control files in this situation by taking info from datafile header.
Note: we will perform until time recovery in case we lost a single table and need to recover it. But to do this we need to have approval from all the users in the database.