Oracle expdp tablespace mode

In my first expdp article, I have explained how to use expdp as sysdba and also other options such as nohup, excluding schemas, compression etc.  In this article, I will give you an example of expdp in tablespace mode. Traditionally, when one wants to take a schema backup, one uses expdp always as it is a logical backup.  For tablespace backups, most of the times DBAs take physical backups of tablespaces instead of using expdp.

However expdp too provides the tablespace option.  Infact, several tablespaces can be backed up at once using expdp. Also one can combine other options such as parallel,compression etc.  The below command is one such example.

expdp TABLESPACES=FINTS_DATA,HRTS_DATA dumpfile=DATA_PUMP_DIR:ts_dumps_%U.dmp LOGFILE=DATA_PUMP_DIR:ts_dumps_log.log    parallel=2    filesize=20G   compression=ALL

In the above example, expdp takes a backup of the contents of FINTS_DATA and HRTS_DATA. The expdp also runs quickly because of the parallel=2 option (provided there are more CPUs available in the database server). 

Furthermore, the filesize option above specifies that each dumpfile generated should not be more than 20G in size.  Oracle will create a second dumpfile if the first dumpfile's size already reaches the 20G threshold.  The %U in the dumpfile name guarantees uniqueness in the name.  

The compression option is a new feature in Oracle 11g. It compresses the output dumpfile by almost a ratio of 1:10 approximately. This is a great feature if the tablespaces have bigger sizes and one wants to save space in the DATA_PUMP_DIR directory for their exported dumpfiles.  Also, during a subsequent impdp process, Oracle automatically decompresses these dumpfiles during the data load, requiring no additional impdp option.  

RMAN Uncatalog using DBMS_BACKUP_RESTORE

Introduction

Most of the Oracle DBAs just rely on the command line RMAN interface ( $ORACLE_HOME/bin/rman ) to perform the rman backup and recovery commands. Every rman backup file (backup piece) generated is also called a backup piece handle. Some of the rman operations can be performed via the little documented DBMS_BACKUP_RESTORE package also and it turns out to be a quite useful in some cases such as when one wants  to uncatalog many RMAN backup pieces at once.

Problem

The other day I was transferring my RMAN backup pieces from one server to another server to create a clone database using rman restore and recovery commands. Due to some transfer issue, all the backup pieces (files) generated on a given day were corrupt during the transfer. Because Oracle Flash Recovery Area (FRA) was used, all the backup pieces were transferred to the FRA area. Some of you already know that FRA has a nicely laid out directory structure with one of the subdirectories containing the date the backup piece was generated.

For e.g /backup/flash_recovery_area/APPSDB/backupset/2011_02_15/o1_mf****_.bkp. Note the date 2011_02_15 in the directory path.

I already cataloged these backup pieces together using the catalog start with '/backup/flash_recovery_area/APPSDB/backupset/2011_02_15/' noprompt command.

Because these files were corrupted during the transfer process, the rman recovery failed. I fixed the problem using the below approach.

1) spool uncatalog_files.sql

2) set lines 200

3)

select 

'exec sys.dbms_backup_restore.deleteBackupPiece('||recid||','||stamp||','''||handle||''','||set_stamp||','||set_count||','||piece#||');'
from v$backup_piece 

where handle like '%2011_02_15%';

4) exit

5) Now run the uncatalog_file.sql file

The above dbms_backup_restore.deleteBackupPiece procedure removes the backup metadata about these corrupted files and also physically removes these files from the disk. Now transfer the backup pieces again from the source server to the destination server, catalog them again and retry your recovery. This time it must be successful.

Conclusion

One can also use the CHANGE backuppiece '#backup piece file name#' UNCATALOG command. But if there are dozens of backup pieces to be uncataloged all at once, the above dynamic generation of the uncatalog commands using dbms_backup_restore.deleteBackupPiece and v$backup_piece can be used.