My Oracle Support Banner

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

  Number of Compute Nodes

RMAN Channels per Node

  Average Elapsed Restore Time(hr:mi:ss)

1 64 7:55:40
2 32 3:55:27
4 16 2:03:01
8 8 1:16:12

 With single or subset of Exadata DB nodes, the throughput bottleneck in these tests was the Database Server's 10Gigabit network.

 

Solution

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Goal
 Test Case
 Verifying Network Bandwidth
 Benefits of Multi-Instance Database Restore
Solution
 Initial Cluster Setup
 1. Information Needed from Source Database / Cluster
 2. Prepare the Destination Host(s) / Cluster
 Database Restore and Recovery
 1. Verify Database is Recoverable
 2. Database Restore / Instantiation on Destination Cluster  
 2.1 Restore the SPFILE onto the first node of the cluster from the Recovery Appliance as follows
 2.2 Create the CRS resources to manage the first node of the cluster on the destination host
 2.3. Start the instance and prepare the database for the controlfile restore
 2.4. Restore the Destination Database Control File
 2.5. Start the Oracle Database on Multiple Nodes in Preparation for Database Restore
 2.6. Clear the Online and Standby Log Files to avoid Alert Log Messages
 2.7. Restore the Database using Multiple Nodes
 Scenario 1: Completing a production database restore
 1. Activating the restore production database
 Scenario 2: Completing a dev / test database duplication
 1. Activating the restored dev/test database
 Scenario 3: Completing a Data Guard Instantiation Restore
 1. Configuring Data Guard using Data Guard Broker 
 Recovery Scenarios
 1. Recovering from failed database restore
 2. Recovering a Physical Standby Database when Managed Recovery Process (MRP) puts the standby database into a sibling branch
References

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.