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!