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.
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_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’
— Begin the restore conversation
— Specify where the controlfile is to be recreated
sys.dbms_backup_restore.restorespfileto(‘D:\oracle\oradata\DABAK_TEST\spfile’); –spfile location
— Restore the controlfile
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)
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.