Title: Restore Oracle Database without controlfile backup (RMAN nocatalog)

Keyword: DBMS_BACKUP_RESTORE

Scenario:

1. Has a full backup with RMAN (no catalog)
2. controlfiles (including backup copies) were deleted by accident
3. all datafiles were deleted by accident
4. database on archive log mode

Background Knowledge:

Oracle offers a package DBMS_BACKUP_RESTORE oracle 8.1.6 onwards. This package is created with two scripts dbmsbkrs.sql and prvtbkrs.plb.
This package is the interface between oracle database server and OS in dealing with IO between them. Due to its internal link to database files,
it can be used by Recovery Manager (RMAN) to restore database in exceptional case.

Scripts dbmsbkrs.sql and prvtbkrs.plb provide elaborate documents to present the usage of this package. Usually, this package can be used
in NOMOUNT mode.  

Key Parts of document about package DBMS_BACKUP_RESTORE:

_______________________________________________________________________________________

    FUNCTION ¡¡deviceAllocate(
                   type IN varchar2 default NULL
                   ,name IN varchar2 default NULL
                   ,ident IN varchar2 default NULL
                   ,noio IN boolean default FALSE
                   ,params IN varchar2 default NULL )
    RETURN varchar2;

    -- Describe the device to be used for sequential I/O. For device types where
    -- only one process at a time can use a device, this call allocates a device
    -- for exclusive use by this session. The device remains allocated until
    -- deviceDeallocate is called or session termination. The device can be used
    -- both for creating and restoring backups.
    --
    -- Specifying a device allocates a context that exists until the session
    -- terminates or deviceDeallocate is called. Only one device can be specified
    -- at a time for a particular session. Thus deviceDeallocate must be called
    -- before a different device can be specified. This is not a limitation since
    -- a session can only read or write one backup at a time.
    --
    -- The other major effect of allocating a device is to specify the name space
    -- for the backup handles (file names). The handle for a sequential file does
    -- not necessarily define the type of device used to write the file. Thus it
    -- is necessary to specify the device type in order to interpret the file
    -- handle. The NULL device type is defined for all systems. It is the file
    -- system supplied by the operating system. The sequential file handles are
    -- thus normal file names.
    --
    -- A device can be specified either by name or by type.
    -- If the type is specified but not the name, the system picks an
    -- available device of that type.
    -- If the name is specified but not the type, the type is determined
    -- from the device.
    -- If neither the type or the name is given, the backups are files in
    -- the operating system file system.

    -- Note that some types of devices, optical disks for example, can be shared
    -- by many processes, and thus do not really require allocation of the device
    -- itself. However we do need to allocate the context for accessing the
    -- device, and we do need to know the device type for proper interpretation
    -- of the file handle. Thus it is always necessary to make the device
    -- allocation call before making most other calls in this package.
    --
    -- Input parameters:
    -- type
    -- If specified, this gives the type of device to use for sequential
    -- I/O. The allowed types are port specific. For example a port may
    -- support the type "TAPE" which is implemented via the Oracle tape
    -- API. If no type is specified, it may be implied by specifying a
    -- particular device name to allocate. The type should be allowed to
    -- default to NULL if operating system files are to be used.
    --
    -- name
    -- If specified, this names a particular piece of hardware to use for
    -- accessing sequential files. If not specified, any available
    -- device of the correct type will be allocated. If the device cannot
    -- be shared, it is allocated to this session for exclusive use.
    -- The name should be allowed to default to NULL if operating system
    -- files are to be used.
    --
    -- ident
    -- This is the users identifier that he uses to name this device. It
    -- is only used to report the status of this session via
    -- dbms_application_info. This value will be placed in the CLIENT_INFO
    -- column of the V$SESSION table, in the row corresponding to the
    -- session in which the device was allocated. This value can also
    -- be queried with the dbms_application_info.read_client_info procedure.
    --
    -- noio
    -- If TRUE, the device will not be used for doing any I/O. This allows
    -- the specification of a device type for deleting sequential files
    -- without actually allocating a piece of hardware. An allocation for
    -- noio can also be used for issuing device commands. Note that some
    -- commands may actually require a physical device and thus will get
    -- an error if the allocate was done with noio set to TRUE.
    --
    -- params
    -- This string is simply passed to the device allocate OSD. It is
    -- completely port and device specific.
    --
    -- Returns:
    -- It returns a valid device type. This is the type that should be
    -- allocated to access the same sequential files at a later date. Note
    -- that this might not be exactly the same value as the input string.
    -- The allocate OSD may do some translation of the type passed in. The
    -- return value is NULL when using operating system files.


    PROCEDURE restoreControlfileTo(cfname IN varchar2);

    -- This copies the controlfile from the backup set to an operating system
    -- file. If the database is mounted, the name must NOT match any of the
    -- current controlfiles.
    --
    -- Input parameters:
    -- cfname
    -- Name of file to create or overwrite with the controlfile from the
    -- backup set.

    PROCEDURE restoreDataFileTo( dfnumber IN binary_integer
    ,toname IN varchar2 default NULL);
    --
    -- restoreDataFileTo creates the output file from a complete backup in the
    -- backup set.     
_______________________________________________________________________________

Note: The following steps are simulation the restore scenario, not real problems solving
      steps.
      
Solution of Restore

1. Take full backup with controlfiles

______________________________________________________________________________

  C:WUTemp>rman target /

    Recovery Manager: Release 9.2.0.1.0 - Production.

[url=http://www.oraclepoint.com]NXG51pueUOraclePointoZT8J5fqJ[/url]

    Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.

[url=http://www.oraclepoint.com]DzyFfZmxdOraclePointmTwHgJOa0[/url]

    connected to target database: DEMO (DBID=3272375326)

    RMAN> run {

[url=http://www.oraclepoint.com]5LeP14ixDOraclePointcTtmXjbcF[/url]

    2> allocate channel C1 type disk;
    3> backup full tag 'FullBackup' format 'd:\KDE\%d_%u_%s_%p.dbf' database include current controlfile;
    4> sql ' alter system archive log current';

[url=http://www.oraclepoint.com]1F3UrLGzsOraclePointQEJQ0VuPL[/url]

    5> release channel C1;
    6> }

[url=http://www.oraclepoint.com]nx0qQSa4pOraclePoint0UIELO8g7[/url]

    using target database controlfile instead of recovery catalog
    allocated channel: C1
    channel C1: sid=15 devtype=DISK

[url=http://www.oraclepoint.com]Jmv241BJ1OraclePointW4Fih3hNJ[/url]

    Starting backup at 18-JUL-04
    channel C1: starting full datafile backupset

[url=http://www.oraclepoint.com]1oCgnXosiOraclePoint4pEzyrlqk[/url]

    channel C1: specifying datafile(s) in backupset
    including current SPFILE in backupset
    including current controlfile in backupset

[url=http://www.oraclepoint.com]bruREnXmKOraclePointQKYZACcgq[/url]

    input datafile fno=00001 name=D:\ORACLE\ORADATA\DEMO\SYSTEM01.DBF
    input datafile fno=00002 name=D:\ORACLE\ORADATA\DEMO\UNDOTBS01.DBF
    input datafile fno=00004 name=D:\ORACLE\ORADATA\DEMO\EXAMPLE01.DBF

[url=http://www.oraclepoint.com]zLZXEFc45OraclePointKekv7SfOX[/url]

    input datafile fno=00009 name=D:\ORACLE\ORADATA\DEMO\XDB01.DBF
    input datafile fno=00005 name=D:\ORACLE\ORADATA\DEMO\INDX01.DBF
    input datafile fno=00008 name=D:\ORACLE\ORADATA\DEMO\USERS01.DBF

[url=http://www.oraclepoint.com]Jl0T2JsQKOraclePointvtrci3278[/url]

    input datafile fno=00003 name=D:\ORACLE\ORADATA\DEMO\DRSYS01.DBF
    input datafile fno=00006 name=D:\ORACLE\ORADATA\DEMO\ODM01.DBF
    input datafile fno=00007 name=D:\ORACLE\ORADATA\DEMO\TOOLS01.DBF

[url=http://www.oraclepoint.com]8Y12iVbUROraclePointuzgH9HHS0[/url]

    channel C1: starting piece 1 at 18-JUL-04
    channel C1: finished piece 1 at 18-JUL-04
    piece handle=D:\KDE\DEMO_01FR79OT_1_1.DBF comment=NONE

[url=http://www.oraclepoint.com]RBQbx0IPhOraclePointBHd9E3z6b[/url]

    channel C1: backup set complete, elapsed time: 00:01:17
    Finished backup at 18-JUL-04

[url=http://www.oraclepoint.com]KXvcHl9PjOraclePointGW3Fn5VFr[/url]

    sql statement: alter system archive log current

    released channel: C1   
____________________________________________________________________________

As shown above, our backup pieces contains controlfile. The bold parts above are needed in following operation.

2. Problem Simulation

In order to simulate the problem, the database was shutdown and all controlfiles and datafiles were deleted on purpose.

3. Attempt to start up database

    SQL> startup
    ORACLE instance started.

[url=http://www.oraclepoint.com]lQYe8cFLgOraclePointVMLYXVRU4[/url]

    Total System Global Area 152115804 bytes
    Fixed Size 453212 bytes

[url=http://www.oraclepoint.com]XLgBJMEd0OraclePoint3a9nBPCgM[/url]

    Variable Size 100663296 bytes
    Database Buffers 50331648 bytes
    Redo Buffers 667648 bytes

[url=http://www.oraclepoint.com]emXB9kNW8OraclePointinSbBd9Dm[/url]

    ORA-00205: error in identifying controlfile, check alert log for more info
    
    An error was raised due to the missing of controfiles.
    
4. Restore Controlfiles

    SQL>startup force nomount;

    SQL> DECLARE

[url=http://www.oraclepoint.com]k6gJUMO6YOraclePoint76llfBmCA[/url]

    2 devtype varchar2(256);
    3 done boolean;
    4 BEGIN

[url=http://www.oraclepoint.com]S3a0MzuJaOraclePointnNMfyHB8b[/url]

    5 devtype:=sys.dbms_backup_restore.deviceAllocate(type=>'',ident=>'T1');
    6 sys.dbms_backup_restore.restoreSetDatafile;
    7 sys.dbms_backup_restore.restoreControlfileTo(cfname=>'d:\oracle\Control01.ctl');

[url=http://www.oraclepoint.com]OMWnIROtxOraclePointQW1Rpssp6[/url]

    8 sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'D:\KDE\DEMO_01FR79OT_1_1.DBF', params=>null);
    9 sys.dbms_backup_restore.deviceDeallocate;
    10 END;

[url=http://www.oraclepoint.com]iZF9CSKiGOraclePointRkEsyQFcZ[/url]

    11 /

    PL/SQL procedure successfully completed.
    
    The control file is now restored to destination directory. Among above, what we do is:

    * line 5: allocate a device channel (The type is null because we use os file. It should be "sbt_tape" if we use file form tape.)
    * line 6: start restore
    * line 7: define destination directory for restored controlfile
    * line 8: identify backup piece source for restore
    * line 9: release channel

    The checking testified the controlfile was restored successfully.

    SQL> host dir d:\oracle

[url=http://www.oraclepoint.com]gSLmJqMUNOraclePointaW5l21cmQ[/url]

    Volume in drive D is DATA
    Volume Serial Number is DC79-57F8
    Directory of d:\oracle

[url=http://www.oraclepoint.com]EHlyB03U7OraclePointRQntw76YG[/url]

    07/18/2004 09:08 PM <DIR> .
    07/18/2004 09:08 PM <DIR> ..

[url=http://www.oraclepoint.com]lkazBxunoOraclePointkGR2KYd0a[/url]

    06/08/2004 03:21 PM <DIR> admin
    07/18/2004 09:08 PM 1,871,872 CONTROL01.CTL
    07/16/2004 11:27 AM <DIR> ORA92

[url=http://www.oraclepoint.com]c3uYJAC4xOraclePoint9zMEbxfQy[/url]

    07/18/2004 09:02 PM <DIR> oradata

5. Restore datafiles

    SQL> DECLARE
    2 devtype varchar2(256);

[url=http://www.oraclepoint.com]yzomydy35OraclePointlXLaqM4ny[/url]

    3 done boolean;
    4 BEGIN
    5 devtype:=sys.dbms_backup_restore.deviceAllocate (type=>'',ident=>'t1');

[url=http://www.oraclepoint.com]c3SLfP4jpOraclePointfXcPMS5zH[/url]

    6 sys.dbms_backup_restore.restoreSetDatafile;
    7 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>01,toname=>'
    d:\oracle\oradata\demo\SYSTEM01.DBF');

[url=http://www.oraclepoint.com]Xn0SPXdqEOraclePointpkreCkxNG[/url]

    8 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>02,toname=>'
    d:\oracle\oradata\demo\UNDOTBS01.DBF');
    9 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>03,toname=>'

[url=http://www.oraclepoint.com]DHpsmDL3POraclePointewElKslc9[/url]

    d:\oracle\oradata\demo\DRSYS01.DBF');
    10 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>04,toname=>'
    d:\oracle\oradata\demo\EXAMPLE01.DBF');

[url=http://www.oraclepoint.com]06oDlOuGQOraclePointV4eb8lGqN[/url]

    11 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>05,toname=>'
    d:\oracle\oradata\demo\INDX01.DBF');
    12 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>06,toname=>'

[url=http://www.oraclepoint.com]K2FZW2e9BOraclePointNjeLjZ1mB[/url]

    d:\oracle\oradata\demo\ODM01.DBF');
    13 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>07,toname=>'
    d:\oracle\oradata\demo\TOOLS01.DBF');

[url=http://www.oraclepoint.com]FXHWsB1J0OraclePointbQppahu14[/url]

    14 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>08,toname=>'
    d:\oracle\oradata\demo\USERS01.DBF');
    15 sys.dbms_backup_restore.restoreDatafileTo(dfnumber=>09,toname=>'

[url=http://www.oraclepoint.com]mV2oibIuAOraclePointilnkqAsCt[/url]

    d:\oracle\oradata\demo\XDB01.DBF');
    16 sys.dbms_backup_restore.restoreBackupPiece(done=>done,handle=>'
    D:\KDE\DEMO_01FR79OT_1_1.DBF', params=>null);

[url=http://www.oraclepoint.com]u43tVJYAkOraclePoint6vazYwrDU[/url]

    17 sys.dbms_backup_restore.deviceDeallocate;
    18 END;
    19 /

    PL/SQL procedure successfully completed.
    
    All datafiles were restored successfully. That's good. In this simulation process, we can reference screen output for specific datafile number  
    which is required during restore. In the real case, we will be benefited from keeping backup log of full backup.
    
6. Startup Database

    SQL> startup force mount;

[url=http://www.oraclepoint.com]VjHK6gDmFOraclePointHYy82VIrq[/url]

    ORACLE instance started.

    Total System Global Area 152115804 bytes

[url=http://www.oraclepoint.com]X8hagzBupOraclePointiop80PEKK[/url]

    Fixed Size 453212 bytes
    Variable Size 100663296 bytes
    Database Buffers 50331648 bytes

[url=http://www.oraclepoint.com]lNxqVMfSDOraclePoint2avhxsFOO[/url]

    Redo Buffers 667648 bytes
    Database mounted.
    

[url=http://www.oraclepoint.com]5P0jxoPunOraclePointeyypaBcGW[/url]

    SQL> Recover database using backup controlfile until cancel ;
    ORA-00279: change 243854 generated at 07/18/2004 20:57:03 needed for thread 1
    ORA-00289: suggestion : D:\KDE\ARC00002.001

[url=http://www.oraclepoint.com]ompsdFdB4OraclePointLGHaAWakK[/url]

    ORA-00280: change 243854 for thread 1 is in sequence #2
    Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

[url=http://www.oraclepoint.com]Ez5sTtg9JOraclePointibQ6F4kyX[/url]

    D:\KDE\ARC00002.001
    ORA-00279: change 244089 generated at 07/18/2004 20:58:18 needed for thread 1
    ORA-00289: suggestion : D:\KDE\ARC00003.001

[url=http://www.oraclepoint.com]qRYkdes5fOraclePointFUFPY5JGO[/url]

    ORA-00280: change 244089 for thread 1 is in sequence #3
    ORA-00278: log file 'D:\KDE\ARC00002.001' no longer needed for this recovery

[url=http://www.oraclepoint.com]4mNDLoUGuOraclePointNrjlX108U[/url]

    Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
    cancel
    Media recovery cancelled.

[url=http://www.oraclepoint.com]epO0uiFA7OraclePoint0KCODpbH1[/url]

    SQL> alter database open resetlogs;

[url=http://www.oraclepoint.com]Gb82poaQBOraclePointRP0lupzta[/url]

    Database altered.

    The attempt to recover database using "until cancel" was failed and we issues "open resetlogs", which is only thing we can do in this case.
    
7. Summary

    The database has been restored successfully, but we experienced data loss which must be avoided. This is only a simple show case in using
    DBMS_BACKUP_RESTORE to restore oracle database.

This case study is original published at http://www.dbanotes.net/Oracle/Rman_nocatalog_lost_controlfile_howto.htm in chinese.

Please see more DBMS_BACKUP_RESTORE tips at http://www.dba-oracle.com/p_dbms_backup_restore.htm