Extracting DB files from RMAN using DBMS BACKUP RESTORE - Pt1
There are various articles and blogs available which discuss using DBMS_BACKUP_RESTORE to extract database files from RMAN backuppieces for restores or creating clones or standby databases.
Often they just provide examples on usage, without putting the examples within a given context and they don’t usually compare the commands to those provided by RMAN. I thought it might be useful to use the commands in a recovery scenario using an 11g R1 database and also show the equivalent RMAN commands for the tasks.
As with many Oracle tools, there are lots of options available with RMAN, so I’ve just tried to pick the options that most closely resemble the work being carried out using DBMS_BACKUP_RESTORE.
Step 1: Log into RMAN and configure backup options
Before we can attempt a database restore, we need to take a backup. Amend the details below to fit your own environment and operating system.
The database is in archivelog mode, so I’m able to take a hot backup (i.e. one where the database remains open whilst the backup takes place).
I’m not using an RMAN catalog for this database, just the information in the database controlfile.
set ORACLE_HOME=C:\app\Administrator\product\11.1.0\db_1
set ORACLE_SID=STRMPRIM
rman target / nocatalog
Once logged into RMAN: I then configured the following options. (Make a note of your existing settings before making any changes, by using the SHOW ALL; command, in case you want to change them back again afterwards).
set the backup to automatically include a backup of the controlfile and spfile
RMAN>CONFIGURE CONTROLFILE AUTOBACKUP ON;
Configure the format for controlfile backuppieces
RMAN>CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘C:\backup\STRMPRIM\Controlfile_%F’;
Set the default device type to be disk
RMAN>CONFIGURE DEFAULT DEVICE TYPE TO DISK;
Configure the location for the disk channel to backup the data.
RMAN>CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT ‘C:\backup\STRMPRIM\STRMPRIM_%U’;
Step 2: Take the RMAN backup
To save on disk space, I’ve asked RMAN to backup the database and archived redo logs to a compressed backupset.
RMAN>BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG;
RMAN>exit
Step 3: Take a copy of the output from the RMAN backup
If you check the logfile from the RMAN backup, you can see which database files went to which RMAN backuppieces.
Starting backup at 31-JAN-09
current log archived
…
channel ORA_DISK_1: starting compressed archived log backup set
…
input archived log thread=1 sequence=4 RECID=62 STAMP=677595080
…
channel ORA_DISK_1: finished piece 1 at 31-JAN-09
piece handle=C:\BACKUP\STRMPRIM\STRMPRIM_0BK66HU9_1_1 tag=TAG20090131T125120 comment=NONE
–The line above shows that the archived redo logs went to the backuppiece C:\BACKUP\STRMPRIM\STRMPRIM_0BK66HU9_1_1
…
channel ORA_DISK_1: starting compressed full datafile backup set
…
input datafile file number=00001 name=C:\APP\ADMINISTRATOR\ORADATA\STRMPRIM\DATA\STRMPRIM_SYSTEM_01.DBF
…
piece handle=C:\BACKUP\STRMPRIM\STRMPRIM_0CK66HUF_1_1 tag=TAG20090131T125126 comment=NONE
–The line above shows that the datafiles went to the backuppiece C:\BACKUP\STRMPRIM\STRMPRIM_0CK66HUF_1_1
Any tempfiles are not backed up by RMAN, to save on disk space. The tempfiles will be automatically re-created when you open the database.
…
channel ORA_DISK_1: starting compressed archived log backup set
…
input archived log thread=1 sequence=5 RECID=64 STAMP=677595104
…
piece handle=C:\BACKUP\STRMPRIM\STRMPRIM_0DK66HV1_1_1 tag=TAG20090131T125145 comment=NONE
–The line above shows that the archived logs that were backed following the final log switch after the datafiles had been backed up are included within the backuppiece C:\BACKUP\STRMPRIM\STRMPRIM_0DK66HV1_1_1
…
Starting Control File Autobackup at 31-JAN-09
piece handle=C:\BACKUP\STRMPRIM\CONTROLFILE_C-238154463-20090131-00 comment=NONE
–The line above shows that the controlfile backup is stored within the backuppiece C:\BACKUP\STRMPRIM\CONTROLFILE_C-238154463-20090131-00
(If the database was using an spfile, then it would also have been included in this backuppiece).
The next installment will include Step 4: Check that the backup files have been created successfully & Step 5: Simulate complete loss of the database