HOWTO - Improve RMAN restore database performance by using Multi-Instance Database Restore (Doc ID 2710709.1)

Last updated on JUNE 13, 2023

Applies to:

Oracle Database - Enterprise Edition - Version 19.6.0.0.0 and later
Information in this document applies to any platform.

Goal

Restoring a very large database (VLDB) in the 100TB+ range is not the same as restoring a database of 10TB of less. For non-VLDB cases, default restore operations can typically restore a database in couple hours. However for VLDB cases, rehearsing and tuning restore and recovery operations are required to avoid long restore and recovery times of days or even weeks in the worse case. To reduce the time taken to restore VLDB databases, restore operation should span multiple or all nodes in a cluster. Additionally, restoring a VLDB database should be rehearsed and automated so DBA team is ready when the disaster arises.

This MOS Note provides a step by step guide to restoring and recovering a database with the Zero Data Loss Recovery Appliance (ZDLRA) using multiple nodes in a cluster, optionally for the purpose of instantiating a physical standby database. The steps are the same for restoring from other media types but the examples will be different.

Database configuration information for this example recovery

The procedure below references a fictitious database called DWDB (Data Warehouse Database) which has a DB_UNIQUE_NAME of DWDBPROD.

The Primary Database Unique Name is dwdbprod and the SID has a prefix of dwdbp and has instances dwdbp1 & dwdbp2
The Standby Database Unique Name is dwdbstby and the SID has a prefix of dwdbs and has instances dwdbs1, dwdbs2, dwdbs3, dwdbs4, dwdbs5, dwdbs6, dwdbs7 & dwdbs8

Install fix for Bug 29555105 "DBNEWID IS VERY SLOW IN 18C [and 19C]" prior to running these steps if you are creating a new copy of the database

Test Case

The size of the database was reduced but the mixture of BIGFILE and SMALLFILE tablespaces as well as ENCRYPTED and UNENCRYPTED datafiles was taken from a real database. Each datafile was populated with RANDOM data.

Benefits of Multi-Instance Database Restore

Leveraging multiple instances during the restore operation versus just increasing RMAN channels, significantly reduces the time as shown in the table below. The RMAN Restore Script in this test created a total of 64 RMAN channels since the goal is to restore the database in the shortest amount of time. The destination cluster was an Exadata X5-2 High Capacity rack with 8 Compute Nodes. When all 8 Compute Nodes were used for the restore, 8 RMAN channels were assigned to each node in the cluster. Compute node and RMAN channel variations are shown in the table below along with the impact on database restore time.

The average length of time to perform the database restore was