When you lost your controlfile backups

While searching through the metalink I saw an article Doc ID: 372996.1 “Using RMAN to Restore and Recover a Database When the Repository and Spfile/Init.ora Files Are Also Lost”. I wondered, how I can do it, but it only references to an internal article DocID 60545.1 “How to Extract Controlfiles, Datafiles, and Archived Logs from RMAN Backupsets.” with an advice of raising a server request.

The only thing I want is just try the case without disturbing Oracle Support , so I asked the help of google and it helped me as it always do.

Now its time to share what I learned, but please do, what Metalink says ” Raise a Service Request with Oracle Support Services“, when you are in this worst scenario.

First of all, I took a database backup of my test server DABAK, while controlfile autobackup option of. As it is written in official documents, RMAN took controlfile and spfile backups automatically because I backup datafile 1 (SYSTEM) .

Here is the backup list.

Then I move the spfileDABAK.ora, initDABAK.ora and forced the database to start without parameter file like below.

After finishing the first part of the recovery, I moved into the second step ,which is restoring controlfile from backup pieces by calling undocumented dbms_restore package in a PL/SQL block.

restore_foundationsql.txt

DECLARE v_dev varchar2(50); — device type allocated for restore v_done boolean; — has the controlfile been fully extracted yet type t_fileTable is table of varchar2(255) index by binary_integer; v_fileTable t_fileTable; — Stores the backuppiece names v_maxPieces number:=1; — Number of backuppieces in backupsetBEGIN — Initialise the filetable & number of backup pieces in the backupset — This section of code MUST be edited to reflect the customer’s available — backupset before the procedure is compiled and run. In this example, the — backupset consists of 4 pieces:v_fileTable(1):=’C:\backup_test\ORA_DF631909845_S144_P1_C1′; –controlfile v_fileTable(2):=‘C:\backup_test\ORA_DF631909818_S143_P1_C1’; –spfile v_maxPieces:=2; —number of backup pieces — Allocate a device. If the backuppiece is on disk, specify type=>null if it is on tape then spesify ‘sbt_tape’ v_dev:=sys.dbms_backup_restore.deviceAllocate(type=>null, ident=>’d1′); — Begin the restore conversation sys.dbms_backup_restore.restoreSetDatafile; — Specify where the controlfile is to be recreated sys.dbms_backup_restore.restoreControlfileTo(cfname=>‘D:\oracle\oradata\DABAK_TEST\control01.ctl’); –ctrlfile sys.dbms_backup_restore.restorespfileto(‘D:\oracle\oradata\DABAK_TEST\spfile’); –spfile location — Restore the controlfile FOR i IN 1..v_maxPieces LOOP sys.dbms_backup_restore.restoreBackupPiece(done=>v_done, handle=>v_fileTable(i), params=>null); IF v_done THEN GOTO all_done; END IF; END LOOP; <<all_done>> — Deallocate the device sys.dbms_backup_restore.deviceDeallocate; END; /

The only things you must change in the script are backup locations and restore locations . (I don’t know the author of the script so I am sorry for not mentioning the author). I changed them and start the script but the error stack below raised. (Script will ask you a value you can just enter 1 and continue)

I ‘m sure that, sp file is in the backup set, so I decided to comment out spfile line of dbms_restore and start the script for only controlfile.

As you see it worked without any error.
After restoring controlfile, I commented out controlfile line and restored the spfile.

Note that, Mine was just a test database and I reached my goal without any support but you must consult Oracle Support before trying this solution !!!!

For further steps of this recovery you can visit the reference site.

References Used:

Advenced Recovery Article