Coskan’s Approach to Oracle

August 29, 2007

When you lost your controlfile backups

Filed under: Backup/Recovery — coskan @ 6:20 pm

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) .

backup1.jpg

Here is the backup list.

backup11.jpg

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

backuo2.jpg

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)

backup5.jpg
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.

backup7.jpg

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

backup8.jpg

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

About these ads

10 Comments »

  1. 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

  2. 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

  3. 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

  4. 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

  5. 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

  6. 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

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

  8. 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

  9. 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

  10. 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


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Silver is the New Black Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 203 other followers

%d bloggers like this: