Category Archives: Backup and Recovery

Oracle Database Partial Restore

How do you restore a single table from a RMAN backup ?

This procedure details the steps involved in doing a partial restore of a database in order to restore a single table.

Troubleshooting a RMAN restore reporting missing backup pieces from Netbackup media layer

Troubleshooting missing backup pieces during restore:

The purpose of this procedure is to identify all backuppieces needed for a restore, before attempting a restore, or troubleshooting after a RMAN-10035 error during restore. The reason for going down to backup piece level being that just identifying the media is sometimes not enough. Some instances have been found when some backup pieces on a tape are in the catalog and some not. Thus the media/tape is “loaded” but you still don’t get the backup pieces in the restore. This way you can request storage to reload the tape and ensure all the necessary backup pieces are in the catalog before attempting a restore and wasting time with the well known error RMAN-10035 with associated ORA-19511 eg:

Restore DB with a different DB name using RMAN

STEP-BY-STEP : Restore database with a different database name using RMAN

Although there are several other methods to achieve this, this is just one of those methods. This can be done differently based on the setup of your environments, such as your backup methods, location files / file system etc. This can be helpful in case where there is a need for QA/DEV environments to be refreshed with PROD data.

In the below section we will be showing the steps for restoring the database BASIE (source) as BRENT (target) on AIX. You will need successful RMAN backup of the source database to do the restore.

Data Pump Import

Data Pump import of materialized views refresh groups fails with ORA-39083 ORA-23421

Background

You are trying to import schemas from a full Data Pump export using Data Pump import utility. Everything imports successfully with no issues except the materialized views refresh groups which throw errors ora-39083 & ora-23421 as shown below (see Symptoms/Errors).

The import command

impdp system/****** directory=MIGRATION_DIR dumpfile=expdp_FULL_DBNAME.dmp
logfile=impdp_schema_DBNAME.log schemas=MYSCHEMA