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.
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 — Specify where the controlfile is to be recreated sys.dbms_backup_restore.restorespfileto(‘D:\oracle\oradata\DABAK_TEST\spfile’); –spfile location — Restore the controlfile <<all_done>> 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:
Great example. I read up here and there regarding using this kind of restore, despite not documented, so I appreciate the effort taken to demonstrate it in one example. As said, nice post.
Comment by Marco Gralike — August 30, 2007 @ 12:00 am
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.
Comment by joel garry — September 11, 2007 @ 11:53 pm
Thank you for the information Joel. I didtn’t have chance o see the note so I couldn’t clarify the problem
Comment by coskan — September 12, 2007 @ 7:49 am
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
😦
Comment by Eduard — September 17, 2007 @ 8:38 am
Hi Eduard
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
Comment by coskan — September 17, 2007 @ 9:53 am
Hi again Coscan!
I have 5 backup piece.
yes i try it. but this dont help.
Thank you anyway!
Comment by Eduard — September 17, 2007 @ 5:23 pm
Try to restore from the pieces one by one I am sure that one of them has the spfile backup
good luck
Comment by coskan — September 17, 2007 @ 7:55 pm
Hello Coscan!
can you help with me ?
i can not restore control file
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
DECLARE
devtype varchar2(256);
done boolean;
BEGIN
devtype := dbms_backup_restore.deviceallocate(‘sbt_tape’, params=>’ ‘);
dbms_backup_restore.restoresetdatafile;
dbms_backup_restore.restorecontrolfileto(‘d:\control’);
dbms_backup_restore.restorebackuppiece(‘BALSCAN_BACKUP:#1296:BALSCAN_BACKUP:38_654527195:RMAN INCRMTAL LVL 0 ORCL_1210590367’,done=>done);
END;
Comment by Pavel — May 12, 2008 @ 12:25 pm
hi ,plz help me out
i have lost my control file n i have taken the backup,
so how i can start my dat base
Comment by waseem — July 19, 2008 @ 7:42 pm
hi ,plz help me out
i have lost my control file n i have not taken the backup,
so how i can start my dat base
Comment by waseem — July 19, 2008 @ 7:43 pm