Thinking Out Loud

July 17, 2021

RMAN Backup Tuning – MAXOPENFILES

Filed under: 19c,RMAN — mdinh @ 5:03 pm

Currently, database backup is performed using RMAN from shell script.

The shell script has has 2,347 lines. Does anyone KISS anymore?

Anyhow, I recalled very early in my career when someone coined the phrased, “Doing It The Dinh’s Way”.

Basically, it’s not effective nor efficient to debug and troubleshoot shell script.

Client provided the following info and off I went.

Database backup changed from filesperset 4 to filesperset 1.

Rollback change and still facing performance issues.

Level 1 increment backup with filesperset = 4 still running > 24 hours – killed.

Can you try a few things not using the rman script in crontab, but using your own rman command?

Request was to disable block change tracking – DONE.

Database size is 272G with the following datafiles.

SQL> select file_id, file_name, round(bytes/1024/1024/1024) gb from dba_data_files order by 1 asc;

 FILE_ID FILE_NAME                                                          GB
-------- ------------------------------------------------------------ --------
       1 +DATA_XXXDWP01/XXXdwp01/datafile/system.256.964878417               1
       2 +DATA_XXXDWP01/XXXdwp01/datafile/sysaux.257.964878419               4
       3 +DATA_XXXDWP01/XXXdwp01/datafile/undotbs1.258.964878421            21
       4 +DATA_XXXDWP01/XXXdwp01/datafile/gg_tbs.261.964878453               5
       5 +DATA_XXXDWP01/XXXdwp01/datafile/undotbs2.262.964878457            19
       6 +DATA_XXXDWP01/XXXdwp01/datafile/users.264.965655143               31
       7 +DATA_XXXDWP01/XXXdwp01/datafile/users.267.1020339295              32
       8 +DATA_XXXDWP01/XXXdwp01/datafile/dev_users.268.1027263011           0
       9 +DATA_XXXDWP01/XXXdwp01/datafile/dev_users.269.1027263013           0
      10 +DATA_XXXDWP01/XXXdwp01/datafile/dev_users.270.1027263013           0
      11 +DATA_XXXDWP01/XXXdwp01/datafile/dev_users.271.1027263013           0
      12 +DATA_XXXDWP01/XXXdwp01/datafile/dev_users.272.1027263015           0
      13 +DATA_XXXDWP01/XXXdwp01/datafile/users.274.1046508647              32
      14 +DATA_XXXDWP01/XXXdwp01/datafile/users.275.1056870315              32
      15 +DATA_XXXDWP01/XXXdwp01/datafile/users.276.1066387693              32
      16 +DATA_XXXDWP01/XXXdwp01/datafile/users.277.1066387695              32
      17 +DATA_XXXDWP01/XXXdwp01/datafile/users.278.1066387697              32

17 rows selected.

SQL>

Backup completed in 3.25 hours using MAXPIECESIZE 2G MAXOPENFILES 1.

Believe further improvements by increasing MAXPIECESIZE to 4G; however, client wanted to leave as is.

Backupset size is 24G.

I believe performance may be related to Pure Storage Deduplication; however, there’s no more time nor different destination to test with.

MAXOPENFILES ≤ 4
Each buffer = 1MB, total buffer size for channel is up to 16MB

MAXOPENFILES = 1 => 16 buffers/file, 1 MB/buffer = 16 MB/file
Optimal for ASM or striped system

Recovery Manager: Release 19.0.0.0.0 - Production on Fri Jul 16 11:08:18 2021 Version 19.8.0.0.0

RMAN> connect target;
2> set echo on
3> spool log to backup.log
4> show all;
5> run {
6> allocate channel c1 device type disk format '/backup/XXXDWP01/%d_%I_%T_%U_%s' MAXPIECESIZE 2G MAXOPENFILES 1;
7> allocate channel c2 device type disk format '/backup/XXXDWP01/%d_%I_%T_%U_%s' MAXPIECESIZE 2G MAXOPENFILES 1;
8> allocate channel c3 device type disk format '/backup/XXXDWP01/%d_%I_%T_%U_%s' MAXPIECESIZE 2G MAXOPENFILES 1;
9> allocate channel c4 device type disk format '/backup/XXXDWP01/%d_%I_%T_%U_%s' MAXPIECESIZE 2G MAXOPENFILES 1;
10> backup incremental level 1 filesperset 1 database;
11> }
12> exit

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Create a free website or blog at WordPress.com.

%d bloggers like this: