Restoring the Oracle database

If you are reading this, there is high chance that someone has been breathing behind your back while you were restoring a database or mildly harassing you over the phone with the only question: when do you think that the database will be open for WebLogic connections?

Indeed when? There is no way to say precisely. You can guess based on the time the backup had taken in average but that is just an estimate. After the restore, there might be a nasty long sequence of archivelog files that has to be applied (with the hope that none is missing) and then you can open with resetlogs. Unless you hit a “feature” or two.

There are 5 major reasons why RMAN restore performance may be poor:

1. Missing statistics: Before a restore even begins the the control file is queried, datafile headers are read, media managers are initialised, the catalog is resynced, PL/SQL is generated and compiled, RMAN metadata either in the control file or the catalog is queried. All this incurs I/O against the control file, the datafile headers and the recovery catalog.

Make sure that the catalog database is analyzed and reorganized on regular basis. It also requires regular maintenance: crosscheck backup, etc.

Generate statistics on the fixed objects, with the GATHER_FIXED_OBJECTS_STATS procedure. This should be done when the database has been running for awhile with generic workload, so the information in the fixed objects reflects a reasonable state of the database load.

Gather the statistics with the following command:

exec dbms_stats.gather_fixed_objects_stats;

Believe it or now, often adding this to the RMAN script might help for both faster backup and restore:

alter session set optimizer_mode=RULE;

2. The backup is hardware multiplexed: all channels write to a single tape device. If the media manager does not support multiplexing only one backuppiece can be returned at a time. Considering the fact that each backuppiece is multiplexed with the rest of the others, this restore would require as many scans as the number of channels allocated of at most the whole backup size each (with a tape rewind after each scan).

3. Individual files or tablespaces are being restored: A restore of a single file needs a scan of potentially the whole database backup depending on where the header of the file being restored is positioned on the tape. The worst situation is when single file is being restored so that the file’s block header is the last block written to the backuppiece.

4. A different number of channels is used for restore compared to backup: The number of channels to be allocated should be equal to the number of physical tape drives. Try to do a couple of things:

Set enough big large pool: set LARGE_POOL_SIZE to at least 256M
Enable the backup_tape_io_slaves by setting BACKUP_TAPE_IO_SLAVES = true

However, some media managers will allow hardware multiplexing to a single tape and are able to parallelize the restore (in the media manager layer) such that the backuppieces are returned from a single tape to multiple channels in parallel. In such a situation it is possible to use an unpublished SET PARALLELMEDIARESTORE OFF command to make RMAN ignore this check.

5. The RMAN relation with bugs is not a new one. The “mend-it-or-end-it” principle does not play any role here. 5 years ago, Jaffar posted on his blog Known RMAN Performance Problems. It is worth checking again the MOS note 247611.1

Some of the bugs will never be probably fixed as you can see that they may get, and are closed (or suspended) because of vendor OS problems, problem cannot be replicated, closed as duplicate, information not avaiable, etc.

Or how about those bugs with status 92: closed, not a bug. For example bug 11835641 is actually not a bug? Right.

Julian is the Global Database Lead of Accenture. His primary responsibility is managing and leading the Global Oracle Technology Practice which includes Autonomous Cloud, IaaS, PaaS, Database Services, Engineered Systems, Java, Middleware, Security and all other areas falling under Oracle Technology. He is also the Accenture-Enkitec Group Managing Director for ... Continue reading →