DBPITR with a rman archival backup

One of our customers had this request:
Can you make a backup of my development database that I can use to restore ( the same database ) to a couple of months back in time?
This customer uses Standard Edition [SE] 11gR1 database on Windows, and has a backup regime where tape backups older than 6 weeks(!) are reused for new backups. Online backups to disk are used, but are only useful for a recovery to at most 7 days back in time. My first idea was to just take a cold backup to disk, and use this backup to restore. But this isn’t very elegant, and quite hard to automate. I decided to investigate the 11g possibilities of setting a restorepoint within an archival backup, and using this restorepoint to “flashback” the database back in time.
Note that I can’t use the real flashback technology because Oracle Enterprise Edition [EE] is the only version that supports flashback.

==============
BACKUP
==============
One of the prerequisites for an archival backup is the use of a recovery catalog… well, I am in luck, backups are done using a catalog. And the other is a partition or directory with enough space to store the backup…. no problem here too.
This is the code I use for the archival backup:

::
:: Copyright (c) 2011, AMIS Services. All rights  reserved.
::
:: D:\ORACLE\admin\scripts\dbarchivalbackup.cmd
::
:: NAME
:: dbarchivalbackup.cmd
:: -- Archival backup of the 11g database to disk with rman.
::
:: AUTHOR
:: Harry Dragstra
::
:: DESCRIPTION
:: -- Make an archival backup of the database with rman to  disk.
:: -- This script also generates a restore point, that will be known  
::    within database and catalog.
::
:: PARAMETERS
:: 1. Name of instance <ORACLE_SID>
::    This parameter is mandatory!
::
::  PREREQUISITES
:: -- Windows Operating System.
:: -- Windows directory: F:\bck\<ORACLE_SID>
:: -- Script: set_restorepoint.sql
:: -- Connection with recovery catalog.
::
:: NOTES
:: -- A recovery catalog  is used, and must be online.
:: -- The archival backup to disk won't go through, in case 
::    the recovery catalog cannot be reached.
::
:: EXAMPLE  USE
:: Start the command file with (mandatory) parameter <instance_name>.
:: d:
:: cd ORACLE\admin\scripts
:: dbarchivalbackup.cmd foo

@echo off
REM |----------------------|
REM | -- SET  VARIABLES -- |
REM |----------------------|

set ORACLE_SID=%1

REM !!!!!! ADJUST FROM HERE ONLY, IF NECESSARY[ see NOTES ] !!!!!!!!
REM !!!!!! ADJUST FROM HERE ONLY, IF NECESSARY[ see NOTES ] !!!!!!!!

set ORACLE_HOME=D:\ORACLE\DB\11.1.0
set cmddir=D:\ORACLE\admin\scripts
set logdir=D:\ORACLE\admin\log
set backupdir=F:\bck\%ORACLE_SID%
set dbarchivalbackup=%logdir%\dbarchivalbackup.log

REM !!!!!! ADJUST TILL HERE ONLY, IF NECESSARY[ see NOTES ] !!!!!!!!
REM !!!!!! ADJUST TILL HERE ONLY, IF NECESSARY[ see NOTES ] !!!!!!!!


echo ==================                                     >> %dbarchivalbackup%
echo Start dbarchivalbackup                                 >> %dbarchivalbackup%

REM |--------------|
REM | -- CHECKS -- |
REM |--------------|

REM Check if usage is correct...
if "%ORACLE_SID%"==""  GOTO USAGE
echo You provided instance %ORACLE_SID% as parameter.       >> %dbarchivalbackup%

REM Check if database is up and running...
net start |  find /I "OracleService%ORACLE_SID%";
if not %ERRORLEVEL%==0 GOTO  DBDOWN
echo Database %ORACLE_SID% is up and running.               >> %dbarchivalbackup%

REM |---------------------|
REM | -- SET FILENAMES -- |
REM |---------------------|

set  rmanfile=%cmddir%\dbarchivalbackup_%ORACLE_SID%.rmn
set  rmanlogfile=%logdir%\dbarchivalbackup_%ORACLE_SID%.log
set  conncheckfile=%cmddir%\conncheck_%ORACLE_SID%.rmn
set  connchecklogfile=%logdir%\conncheck_%ORACLE_SID%.log

REM |---------------------|
REM | -- GET VARIABLES -- |
REM |---------------------|

for /f %%i in ('%ORACLE_HOME%/bin/sqlplus -s / as sysdba  @set_restorepoint %logdir%') do @set RESTORE_POINT=%%i
echo Restorepoint:  %RESTORE_POINT%                          >> %dbarchivalbackup%

REM |--------------------|
REM | -- START SCRIPT -- |
REM |--------------------|

echo Database %ORACLE_SID% will be back upped.               >> %dbarchivalbackup%
echo Creating catalog check login script......               >> %dbarchivalbackup%
REM |-----------------------------------------|
REM |  CREATE CHECK LOGIN INTO CATALOG SCRIPT |
REM |-----------------------------------------|
echo exit;                                                    > %conncheckfile%

REM Check if rman catalog is online and can be reached...
%ORACLE_HOME%\bin\rman catalog rman/****@oem_11g cmdfile=%conncheckfile% log=%connchecklogfile%
if not %ERRORLEVEL%==0 GOTO NOCATALOG
echo Rman catalog can be reached.                            >> %dbarchivalbackup%
echo Creating rman backup script......                       >> %dbarchivalbackup%

REM  ======================================================
REM  ==== START  CREATING RMAN SCRIPT 2B EXECUTED ====
REM  ======================================================

echo run {                                                    > %rmanfile%
echo allocate channel d1  device type disk;                  >> %rmanfile%
echo sql "alter database backup controlfile to trace as ''%backupdir%/%RESTORE_POINT%_BACKUP_CONTROLFILE.sql'' reuse noresetlogs"; >> %rmanfile%
echo sql "create pfile=''%backupdir%\%RESTORE_POINT%_PFILE.ora'' from spfile"; >> %rmanfile%
echo backup database                                         >> %rmanfile%
echo format '%backupdir%\%%U'                                >> %rmanfile%
echo tag %RESTORE_POINT%                                     >> %rmanfile%
echo keep until time 'sysdate+365'                           >> %rmanfile%
echo restore point %RESTORE_POINT%;                          >> %rmanfile%
echo release channel d1;                                     >> %rmanfile%
echo }                                                       >> %rmanfile%
echo.                                                        >> %rmanfile%

REM  ====================================================
REM ==== END CREATING  RMAN SCRIPT 2B EXECUTED ====
REM  ====================================================

echo Rman backup script created                              >> %dbarchivalbackup%

REM =============================
REM ==== START  EXECUTE ====
REM =============================
echo Backup database with connection to catalog              >> %dbarchivalbackup%
echo Backup database with rman                               >> %dbarchivalbackup%
%ORACLE_HOME%\bin\rman target / catalog rman/****@oem_11g cmdfile=%rmanfile% log=%rmanlogfile%
REM ============================
REM ==== END  EXECUTE ====
REM ============================

REM |-------------------|
REM | -- END SCRIPT --  |
REM |-------------------|

GOTO END

REM |-------------------------------|
REM | -- PROVIDE  CHECK MESSAGES -- |
REM |-------------------------------|
:DBDOWN
echo.                                                        >> %dbarchivalbackup%
echo  Database %1 is not up and running.                     >> %dbarchivalbackup%
echo ****** Backup cannot continue...                        >> %dbarchivalbackup%
echo ****** Startup the database before you                  >> %dbarchivalbackup%
echo        try this backup again.                           >> %dbarchivalbackup%
echo.                                                        >> %dbarchivalbackup%
GOTO END
:NOCATALOG
echo.                                                        >> %dbarchivalbackup%
echo The rman catalog cannot be reached.                     >> %dbarchivalbackup%
echo ****** Backup cannot continue...                        >> %dbarchivalbackup%
echo ****** Check the reachability of the catalog before you >> %dbarchivalbackup%
echo        try this backup again.                           >> %dbarchivalbackup%
echo.                                                        >> %dbarchivalbackup%
GOTO END
:USAGE
echo.                                                        >> %dbarchivalbackup%
echo Usage: dbarchivalbackup.cmd sid                         >> %dbarchivalbackup%
echo ****** Backup cannot continue...                        >> %dbarchivalbackup%
echo ****** Provide a valid instance name when you           >> %dbarchivalbackup%
echo        try this backup again.                           >> %dbarchivalbackup%
echo.                                                        >> %dbarchivalbackup%
GOTO END
:END
echo End dbarchivalbackup                                    >> %dbarchivalbackup%
echo ==================                                      >> %dbarchivalbackup%

REM |-------------------|
REM | -- END OF FILE -- |
REM |-------------------|

You may have noticed that a sql script ( set_restorepoint.sql ) is called by the backup code.. here it is:

rem
rem set_restorepoint.sql
rem
rem
rem  Copyright (c) 2011, AMIS Services. All rights reserved.
rem
rem  NAME
rem set_restorepoint.sql
rem
rem AUTHOR
rem Harry  Dragstra
rem
rem DESCRIPTION
rem This script gets instance name and  date from the database in this format:
rem <ORACLE_SID>YYYYMMDDHH24MISS
rem
rem NOTES
rem None
rem
rem PARAMETERS
rem Log  directory
rem

set pagesize 0 feedback off verify off heading off
define logdir=&1

spool &&logdir\set_restorepoint.log  append
select  upper(instance_name)||to_char(sysdate,'YYYYMMDDHH24MISS')
from  v$instance;

spool off
exit;

==============
RESTORE
==============
The restore of an archival backup is usually done with the rman duplicate database command, having a connection to the original target database and the catalog, where the duplicate command creates a new database ( on the same or different host ) using the archival backup. While here the backupped spfile is used as the spfile of the new database, I ran into some difficulty doing the same when restoring to a running database that already started with a spfile, or even with a closed database. With the target database closed it proved to be impossible to set a restorepoint within my rman restore script ( and you really need this restorepoint to restore the spfile! ), and with the target database in nomount mode, while a restorepoint can now be set, the restore of the spfile of a database that’s started with a spfile cannot be done.
So I ended up with backing up the spfile as pfile, as an add-on to the archival backup – check out the backup code I provided before -, and using this pfile to restore the original spfile, as part of the rman restore/recovery.
This is the code I use for the restore of an archival backup:

::
:: Copyright (c) 2011, AMIS Services. All rights  reserved.
::
:: D:\ORACLE\admin\scripts\dbarchivalrestore.cmd
::
:: NAME
:: dbarchivalrestore.cmd
:: -- Restore an archival backup of a 11g database using a restorepoint.
::
:: AUTHOR
:: Harry Dragstra
::
:: DESCRIPTION
:: -- Restore an archival backup of the  database.
:: -- This script uses a restore point, set within an archival  backup.
::
:: PARAMETERS
:: 1. Name of instance <ORACLE_SID>
::    This parameter is mandatory!
:: 2. Name of the restorepoint <restorepoint> 
::    This parameter is mandatory!
::
::  PREREQUISITES
:: -- Windows Operating System.
:: -- Windows directory: F:\bck\<ORACLE_SID>
:: -- Script: chk_restorepoint.sql
:: -- Script: get_spfilecopyname.sql
:: -- Script: spfilerestore.sql
:: -- Connection with recovery catalog.
:: -- Valid restorepoint.
::
::  NOTES
:: -- A recovery catalog and restorepoint are used.
:: -- The restore won't go through, in case the recovery catalog is unreachable.
:: -- The restore won't go through, in case the restorepoint is not valid.
:: -- Restorepoints are known within the database, and can be queried:
:: -- a --  SQL> select * from v$restore_point;
:: -- b --  %ORACLE_HOME%\bin\rman target / catalog rman/****@oem_11g
:: --       RMAN> list restore point all;
::
:: EXAMPLE USE
:: Start the command file with (mandatory) parameters <instance_name> and <restore point>
:: d:
:: cd ORACLE\admin\scripts
:: dbarchivalrestore.cmd foo foo20110318085556
::
@echo off
REM |----------------------|
REM | -- SET VARIABLES --  |
REM |----------------------|

set ORACLE_SID=%1
set RESTORE_POINT=%2

REM !!!!!! ADJUST FROM HERE ONLY, IF NECESSARY[ see NOTES ] !!!!!!!!
REM !!!!!! ADJUST FROM HERE ONLY, IF NECESSARY[ see NOTES ] !!!!!!!!

set ORACLE_HOME=D:\ORACLE\DB\11.1.0
set cmddir=D:\ORACLE\admin\scripts
set logdir=D:\ORACLE\admin\log
set backupdir=F:\bck\%ORACLE_SID%
set bckpfile=%backupdir%\%RESTORE_POINT%_PFILE.ora
set dbarchivalrestore=%logdir%\dbarchivalrestore.log

REM !!!!!! ADJUST TILL HERE ONLY, IF NECESSARY [ see NOTES ] !!!!!!!!
REM !!!!!! ADJUST TILL HERE ONLY, IF NECESSARY [ see NOTES ] !!!!!!!!

echo ==================                                     >> %dbarchivalrestore%
echo Start dbarchivalrestore                                >> %dbarchivalrestore%

REM |--------------|
REM | -- CHECKS -- |
REM |--------------|

REM Check if usage is correct...
if "%ORACLE_SID%"==""  GOTO USAGE
if "%RESTORE_POINT%"=="" GOTO USAGE
echo You provided instance  %ORACLE_SID% as parameter.      >> %dbarchivalrestore%
echo You provided restorepoint %RESTORE_POINT% as parameter.>> %dbarchivalrestore%

REM Check if database is up and running...
net start |  find /I "OracleService%ORACLE_SID%"
if not %ERRORLEVEL%==0 GOTO  DBDOWN
echo Database %ORACLE_SID% is up and running.                >> %dbarchivalrestore%

REM Check if restorepoint is valid...
for /f %%i in ('%ORACLE_HOME%/bin/sqlplus -s / as sysdba @chk_restorepoint %logdir% %RESTORE_POINT%') do @set RESTOREPOINTVALID=%%i
if not %RESTOREPOINTVALID%==1  GOTO RPINVALID
echo Restorepoint %RESTORE_POINT% is valid.                  >> %dbarchivalrestore%

REM |----------------------|
REM | -- SET FILENAMES --  |
REM |----------------------|

set  rmanfile=%cmddir%\dbarchivalrestore_%ORACLE_SID%.rmn
set  rmanlogfile=%logdir%\dbarchivalrestore_%ORACLE_SID%.log
set  conncheckfile=%cmddir%\conncheck_%ORACLE_SID%.rmn
set  connchecklogfile=%logdir%\conncheck_%ORACLE_SID%.log

REM |----------------------|
REM | -- GET VARIABLES --  |
REM |----------------------|

for /f %%i in ('%ORACLE_HOME%/bin/sqlplus -s / as sysdba  @get_spfilecopyname %logdir%') do @set SPFILE_COPY=%%i
echo Spfile copy: %SPFILE_COPY%                             >> %dbarchivalrestore%

REM |---------------------|
REM | -- START SCRIPT --  |
REM |---------------------|

echo Database %ORACLE_SID% will be restored.                >> %dbarchivalrestore%
echo Creating catalog check login script......              >> %dbarchivalrestore%
REM |-----------------------------------------|
REM |  CREATE CHECK LOGIN INTO CATALOG SCRIPT |
REM |-----------------------------------------|
echo exit;                                                   > %conncheckfile%

REM Check if rman catalog is online and can be  reached...
%ORACLE_HOME%\bin\rman catalog rman/****@oem_11g cmdfile=%conncheckfile% log=%connchecklogfile%
if not %ERRORLEVEL%==0 GOTO NOCATALOG
echo Rman catalog can be reached.                           >> %dbarchivalrestore%
echo Creating rman restore script......                     >> %dbarchivalrestore%

REM  ======================================================
REM  ==== START  CREATING RMAN SCRIPT 2B EXECUTED ====
REM  ======================================================

echo run {                                                   > %rmanfile%
echo allocate channel d1  device type disk;                 >> %rmanfile%
echo set until restore point %RESTORE_POINT%;               >> %rmanfile%
echo restore controlfile;                                   >> %rmanfile%
echo alter database mount;                                  >> %rmanfile%
echo restore  database;                                     >> %rmanfile%
echo recover database;                                      >> %rmanfile%
echo alter database open resetlogs;                         >> %rmanfile%
echo release channel d1;                                    >> %rmanfile%
echo }                                                      >> %rmanfile%
echo.                                                       >> %rmanfile%

REM  ====================================================
REM  ==== END CREATING  RMAN SCRIPT 2B EXECUTED ====
REM  ====================================================

echo Rman backup script created                             >> %dbarchivalrestore%

REM =============================
REM ==== START  EXECUTE ====
REM =============================
echo Copy the existing spfile first...                      >> %dbarchivalrestore%
copy /y %ORACLE_HOME%\database\SPFILE%ORACLE_SID%.ORA %ORACLE_HOME%\database\%SPFILE_COPY%;

echo Restore ( create spfile from pfile ) with pfile from backup directory >> %dbarchivalrestore%
%ORACLE_HOME%/bin/sqlplus -s / as sysdba @spfilerestore %logdir% %bckpfile%

echo Restore database with connection to catalog            >> %dbarchivalrestore%
echo Restore database with rman                             >> %dbarchivalrestore%
%ORACLE_HOME%\bin\rman target / catalog rman/****@oem_11g cmdfile=%rmanfile% log=%rmanlogfile%
REM ============================
REM ==== END  EXECUTE ====
REM ============================

REM |-------------------|
REM | -- END SCRIPT --  |
REM |-------------------|

GOTO END

REM |-------------------------------|
REM | -- PROVIDE  CHECK MESSAGES -- |
REM |-------------------------------|
:DBDOWN
echo.                                                        >> %dbarchivalrestore%
echo  Database %1 is not up and running.                     >> %dbarchivalrestore%
echo ****** Restore cannot continue...                       >> %dbarchivalrestore%
echo ****** Startup the database before you                  >> %dbarchivalrestore%
echo        try this restore again.                          >> %dbarchivalrestore%
echo.                                                        >> %dbarchivalrestore%
GOTO END
:RPINVALID
echo.                                                        >> %dbarchivalrestore%
echo Restorepoint %2 is  not valid.                          >> %dbarchivalrestore%
echo ****** Restore cannot  continue...                      >> %dbarchivalrestore%
echo ****** Provide a valid restorepoint before you          >> %dbarchivalrestore%
echo        try this restore again.                          >> %dbarchivalrestore%
echo.                                                        >> %dbarchivalrestore%
GOTO END
:NOCATALOG
echo.                                                        >> %dbarchivalrestore%
echo The rman catalog  cannot be reached.                    >> %dbarchivalrestore%
echo ****** Restore cannot continue...                       >> %dbarchivalrestore%
echo ****** Check the reachability of the catalog before you >> %dbarchivalrestore%
echo        try this restore again.                          >> %dbarchivalrestore%
echo.                                                        >> %dbarchivalrestore%
GOTO END
:USAGE
echo.                                                        >> %dbarchivalrestore%
echo Usage:  dbarchivalrestore.cmd sid restorepoint          >> %dbarchivalrestore%
echo  ****** Restore cannot continue...                      >> %dbarchivalrestore%
echo ******  Provide a valid instance name and               >> %dbarchivalrestore%
echo         restorepoint when you try this backup again.    >> %dbarchivalrestore%
echo.                                                        >> %dbarchivalrestore%
GOTO  END
:END
echo End dbarchivalrestore                                   >> %dbarchivalrestore%
echo ==================                                      >> %dbarchivalrestore%

REM |-------------------|
REM | -- END OF FILE -- |
REM |-------------------|

The 3 sql’s called by the restore:

rem
rem spfilerestore.sql
rem
rem
rem  Copyright (c) 2011, AMIS Services. All rights reserved.
rem
rem  NAME
rem spfilerestore.sql
rem
rem AUTHOR
rem Harry  Dragstra
rem
rem DESCRIPTION
rem This script restores the spfile from  the backup pfile
rem
rem NOTES
rem None
rem
rem PARAMETERS
rem 1. Log directory
rem 2. Full path + name of backupped pfile
rem

set pagesize 0 feedback off verify off heading off
define logdir=&1
define pfile=&2

spool &&logdir\spfilerestore.log append

shutdown immediate
create spfile from  pfile='&&pfile';
startup nomount;

spool off
exit;

rem
rem get_spfilecopyname.sql
rem
rem
rem  Copyright (c) 2011, AMIS Services. All rights reserved.
rem
rem  NAME
rem get_spfilecopyname.sql
rem
rem AUTHOR
rem Harry  Dragstra
rem
rem DESCRIPTION
rem This script gets instance name and  date from the database in this format:
rem <ORACLE_SID>YYYYMMDDHH24MISS
rem
rem NOTES
rem None
rem
rem PARAMETERS
rem Log  directory
rem

set pagesize 0 feedback off verify off heading off
define logdir=&1

spool &&logdir\set_spfilecopyname.log  append
select  'SPFILE'||upper(instance_name)||'.ora.'||to_char(sysdate,'YYYYMMDDHH24MISS')
from  v$instance;

spool off
exit;

rem
rem chk_restorepoint.sql
rem
rem
rem  Copyright (c) 2011, AMIS Services. All rights reserved.
rem
rem  NAME
rem chk_restorepoint.sql
rem
rem AUTHOR
rem Harry  Dragstra
rem
rem DESCRIPTION
rem This script checks if a given restore  point is valid.
rem
rem NOTES
rem None
rem
rem PARAMETERS
rem 1. Log directory
rem 2. Restore point
rem

set pagesize 0 feedback  off verify off heading off
define logdir=&1
define restorepoint=&2

spool &&logdir\chk_restorepoint.log append
select count(*) cnt
from v$restore_point
where name =  trim(upper('&&restorepoint'));

spool off
exit;

The setup of these two files( dbarchivalbackup.cmd, dbarchivalrestore.cmd ), and the 4 sql files called, saves quite some time doing cold backups and cold restores. And by automating + standardizing this archival backup and restore there is less risk of the mistakes every dba makes, once in a while.