Extracting DB files from RMAN using DBMS BACKUP RESTORE Pt 2

Carrying on from Part 1 - please see step 4 - 6 below.

Step 4: Check that the backup files have been created successfully.

If you look in the backup location C:\backup\STRMPRIM, you will see the freshly created RMAN

backuppieces as listed above.

Step 5: Simulate complete loss of the database

sqlplus / as sysdba
shutdown immediate;

Move any database files, control files, online and archived redo logs to alternative folders.

Step 6: Recover the database using DBMS_BACKUP_RESTORE

i) Start the database instance.
(If running on Windows check that the windows database service is running first).

sqlplus /nolog
conn / as sysdba
startup nomount

ii) Restore the controlfiles from within SQLPLUS - specifying the relevant backuppiece and the name

of the first controlfile as specified in the initsid.ora CONTROL_FILES parameter.

DECLARE
devtype varchar2(256);
done boolean;
BEGIN
devtype := dbms_backup_restore.DeviceAllocate (type = >’ ‘, ident = >’RESTORE_CFILE’ );
dbms_backup_restore.RestoreSetDatafile;
dbms_backup_restore.RestoreControlfileTo(cfname=>’C:\app\Administrator\oradata\STRMPRIM\data\control01.ctl’);
dbms_backup_restore.RestoreBackupPiece (done=>done, handle =>’C:\BACKUP\STRMPRIM\CONTROLFILE_C-238154463-20090131-00′, params =>null);
dbms_backup_restore.DeviceDeallocate;
END;
/

You should just see the message:

PL/SQL procedure successfully completed.

Then manually copy the restored controlfile to the other locations specified in the CONTROL_FILES

parameter and re-name them accordingly.

e.g. for this database

CONTROL_FILES=control_files=(”C:\app\Administrator\oradata\STRMPRIM\data\control01.ctl”,”C:\app\Administrator\oradata\STRMPRIM\log_a\control02.ctl”, “C:\app\Administrator\oradata\STRMPRIM\log_b\control03.ctl”)

so I need to copy the first controlfile from C:\app\Administrator\oradata\STRMPRIM\data\control01.ctl
to C:\app\Administrator\oradata\STRMPRIM\log_a\control02.ctl and C:\app\Administrator\oradata\STRMPRIM\log_b\control03.ctl
The rman equivalent commands for this would be:

rman target / nocatalog
RMAN>restore controlfile from ‘C:\BACKUP\STRMPRIM\CONTROLFILE_C-238154463-20090131-00′;

rman will automatically create copies of the restored controlfile in the correct locations and with the correct names.

ii) Extract the database datafiles:

Then extract the datafiles, one at a time, to their required location. Ensure that you specify the correct backuppiece. (If you have backed up a large database, then the datafiles may be split across several backuppieces, but the RMAN logfile will indicate which files are in which backuppieces.

DECLARE
devtype varchar2(256);
done boolean;
BEGIN
devtype := dbms_backup_restore.DeviceAllocate (type=>'’, ident=> ‘RESTORE_DBFILE’);
dbms_backup_restore.RestoreSetDatafile;
dbms_backup_restore.RestoreDatafileTo (dfnumber=> 1, toname =>’C:\app\Administrator\oradata\STRMPRIM\data\STRMPRIM_SYSTEM_01.DBF’);
dbms_backup_restore.RestoreBackupPiece (done=>done, handle =>’C:\BACKUP\STRMPRIM\STRMPRIM_0CK66HUF_1_1′, params => null );
dbms_backup_restore.DeviceDeallocate;
END;
/

Repeat the above for each database file, amending the number of the datafile, the name of the

backuppiece and the name of the datafile accordingly. You’ll see that the files have been extracted successfully.

DECLARE
devtype varchar2(256);
done boolean;
BEGIN
devtype := dbms_backup_restore.DeviceAllocate (type =>’, ident => ‘RESTORE_DBFILE’);
dbms_backup_restore.RestoreSetDatafile;
dbms_backup_restore.RestoreDatafileTo (dfnumber =>6, toname =>’C:\app\Administrator\oradata\STRMPRIM\data\STRMPRIM_LOGMINER_01.DBF’);
dbms_backup_restore.RestoreBackupPiece (done=>done, handle =>’C:\BACKUP\STRMPRIM\STRMPRIM_0CK66HUF_1_1′, params =>null);
dbms_backup_restore. DeviceDeallocate;
END;
/

As we’re carrying out a restore here, the datafiles have been restored to the same

name and location as the original files, but if you were creating a clone,

you could obviously restore them to a different location and also re-name the files if required.

The equivalent rman commands to restore the datafiles would be:
rman target / nocatalog
RMAN> sql ‘alter database mount’;
RMAN> run

{
restore datafile 1 from tag=TAG20090131T125126;
restore datafile 2 from tag=TAG20090131T125126;
restore datafile 3 from tag=TAG20090131T125126;
restore datafile 4 from tag=TAG20090131T125126;
restore datafile 5 from tag=TAG20090131T125126;
restore datafile 6 from tag=TAG20090131T125126;
}

Notice that with RMAN, you can’t specify the name of the backuppiece - you need to specify the TAG.

If you try to run a command such as

RMAN>restore datafile 1 from ‘C:\ BACKUP\ STRMPRIM\ STRMPRIM_ 0CK66HUF_1_1′;

Then the command fails with the error: RMAN-06509: only SPFILE or control file can be restored from AUTOBACKUP

iii) You then need to restore the archived redo logs.

Ensure that you use the very first archivelog sequence number, at the logswitch before the hot backup starts,

as listed at the start of the backup and then the last number at the end of the backup

(i.e. the one from the logswitch at the end of the hot backup).

e.g. in this example, the first archived redo log backup is shown in the logfile as:

input archived log thread=1 sequence=4 RECID=62 STAMP=677595080
and the last one is shown as:
input archived log thread=1 sequence=5 RECID=64 STAMP=677595104

Therefore we need to restore archived redo logs 4 to 5, as these are the first and last archived redo logs that were included in the backup.

Amend the name of the backppiece and archivelog as required, until you’ve restored all the files.

DECLARE
devtype varchar2(256);
done boolean;
BEGIN
devtype := dbms_backup_restore.DeviceAllocate (type=>'’, ident =>’ RESTORE_ARCHLOG’);
dbms_backup_restore.RestoreSetArchivedLog (destination=>’C:\Archive\STRMPRIM\’);
dbms_backup_restore.RestoreArchivedLog (thread=>1, sequence=>4);
dbms_backup_restore.RestoreBackupPiece (done =>done, handle =>’C:\BACKUP\STRMPRIM\STRMPRIM_0BK66HU9_1_1′, params =>null);
dbms_backup_restore.DeviceDeallocate;
END;
/

DECLARE
devtype varchar2(256);
done boolean;
BEGIN
devtype := dbms_backup_restore.DeviceAllocate (type=>'’, ident =>’ RESTORE_ARCHLOG’);
dbms_backup_restore.RestoreSetArchivedLog (destination=>’C: \Archive\STRMPRIM\’);
dbms_backup_restore.RestoreArchivedLog (thread=>1, sequence=>5);
dbms_backup_restore.RestoreBackupPiece (done =>done, handle =>’C:\BACKUP\STRMPRIM\STRMPRIM_0DK66HV1_1_1′, params =>null);
dbms_backup_restore.DeviceDeallocate;
END;
/

RMAN commands to complete these same steps would be:

rman target / nocatalog

RMAN>restore archivelog logseq=4 from tag=TAG20090131T125120;
RMAN>restore archivelog logseq=5 from tag=TAG20090131T125145;

If you’re restoring lots of archived redo logs from the same backuppiece then you can use a command like

RMAN>restore archivelog logseq=1 until logseq=5 from tag=TAG20090131T125145;

RMAN restores the archived redo logs to the default destination,

which is the flash recovery area and re-names them as oracle managed files.

(e.g. Archived redo log 5 is restored as

C:\app\Administrator\flash_recovery_area\STRMPRIM\ARCHIVELOG\2009_01_31\O1_MF_1_5_4R998TG5_.ARC )

(iv) Recover the database

sqlplus / as sysdba
SQL>alter database mount;    (If you’ve used RMAN to restore the database files, then the database will already be mounted).
SQL>recover database using backup controlfile until cancel;

You’ll then see the message below:

ORA-00279: change 240056 generated at 01/31/2009 12:51:29 needed for thread 1
ORA-00289: suggestion :
C:\APP\ADMINISTRATOR\FLASH_RECOVERY_AREA\STRMPRIM\ARCHIVELOG\ 2009_01_31\O1_MF_1_5_4R998TG5_.ARC
ORA-00280: change 240056 for thread 1 is in sequence #5

Specify log: {=suggested | filename | AUTO | CANCEL}

In a normal recovery situation, you could just type AUTO or hit Return and the database would use files that are stored within the flash recovery area for the restore. For the purposes of this demonstration, we’re going to assume that these files no longer exist on disk either, so we’ll manually type in the names of the archived redo logs required for the restore:

C:\Archive\STRMPRIM\STRMPRIM_00005_0677525203_001.ARC

Then hit the return key.

If restoring using the RMAN commands, then RMAN will have placed the restored archived redo logs in the flash recovery area and re-named them as Oracle managed files. Just hit the return key to apply them as required.
Repeat until you have applied all of the information from the required archived redo logs. (You’ll still receive prompts to apply logs, even though you have applied all the archived redo logs that were restored. So in this scenario I was
prompted if I wanted to apply logsequence 6 - even though this archived redo logfile had never been created or backed up by the database).

Then just type: CANCEL

You’ll see the message:  Media recovery cancelled.

SQL>Alter database open resetlogs;

You’ll see the message: Database altered.

Your online redo logs will have been re-created and the database will now be available for use.

(v)  To quickly check that the database is available, you can run the following queries:
select * from v$recover_file; - This should return no rows, which indicates that no files need to be recovered.

select distinct status from v$datafile; - This should return only the values ONLINE and SYSTEM,

which shows you that all datafiles are online and ready for use.

select open_mode from v$database; - This should return “READ WRITE”, indicating that the database is open for read and write operations.

Refer to Metalink and the Oracle documentation for more detailed information on the topics discussed in this blog.

Disclaimer: When carrying out any backup and recovery operations against an Oracle database, you are advised to raise a call with Oracle support. The example shown below is not suitable for all environments and should only be used if you fully understand the implications of using this method.  The author accepts no responsibility for any data loss that may occur as the result of using these procedures. The database and data used was created for my own testing database, so any resemblances to any other database or data is entirely coincidental.

Any feedback or comments are welcome for this series

One Response to “Extracting DB files from RMAN using DBMS BACKUP RESTORE Pt 2”

  1. Extracting DB files from RMAN using DBMS BACKUP RESTORE Pt 2 | Canning IT LTD Says:

    […] Extracting DB files from RMAN using DBMS BACKUP RESTORE Pt 2 […]

Leave a Reply

You must be logged in to post a comment.


Acyclovir Cheap Phentermine Phentermine Buy Prozac Buy Cialis Buy Ultram Buy Lipitor Carisoprodol Buy Lexapro Buy Zyrtec Hydrocodone Generic Viagra Tenuate Buy Lortab Lortab Buy Levitra Buy Vicodin Buy Norco Lorazepam Buy Oxycontin Lexapro Vicodin Buy Norco Buy Levitra Buy Diazepam Buy Line Xanax Buy Viagra Zyban Buy Lorazepam Buy Biaxin Ativan Effexor Buy Carisoprodol Buy Lortab Buy Vicodin Online Buy Lexapro Buy Vicodin Buy Alprazolam Buy Darvocet Buy Zocor Buy Nexium Buy Glucophage Buy Xanax On Line Ultracet Buy Didrex Buy Tramadol Online Norvasc Buy Lorazepam Buy Ativan Tramadol Tramadol Online Bupropion Buy Norco Biaxin Ultram Prozac Glucophage Valium Online Buy Generic Viagra Oxycontin Buy Viagra Online Norco Phentermine Online Buy Lexapro Zyban Buy Zyban Bupropion Buy Alprazolam Lorazepam Buy Soma Valium Online Hydrocodone Viagra Online Buy Lexapro Buy Ephedrine Buy Diazepam Buy Tenuate Paxil Buy Viagra Online Celexa Buy Percocet