August 29, 2007

When you lost your controlfile backups

While searching through the metalink I saw an articleDoc 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.

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′; –controlfilev_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’

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 !!!!

That “not found in backup set” error means that you really have to comb through the original backup logs to find out which piece the desired object is in, and only specify those files in the v_fileTable array.

This is obliquely explained toward the end of the note if I recall correctly. It becomes important to understand when you are trying to grab certain archived logs out of many pieces.

Hello Coscan!
nice example! thank you!
but spfile dont restore. i commented controlfile after it restore, but spfile restore fail:
ORA-19687: SPFILE not found in backup set
ORA-06512: at “SYS.X$DBMS_BACKUP_RESTORE”, line 5149
ORA-06512: at line 37 :(

How many backup piece you have Eduard if you have 2 or more then you can try the combination of commenting out. if you have only 1 then there must be something wrong if you have system datafile backed up because the doc says both spfile and controlfiles are backed up with system datafile backup

SQL> @d:\obnova.sql
DECLARE
*
ERROR at line 1:
ORA-19568: a device is already allocated to this session
ORA-06512: at “SYS.X$DBMS_BACKUP_RESTORE”, line 173
ORA-06512: at “SYS.X$DBMS_BACKUP_RESTORE”, line 148
ORA-06512: at line 5