Trust you are doing well!!!
In this post, I will discussing about critical issue, I faced recently wherein RMAN compressed backup started taking significant time post changing compatible parameter from 22.214.171.124 to 19.0.0. Normally RMAN L0 compressed backup of DB size 70+ TB used to take approx 10 hours to complete. Post parameter change, it started taking around 24+ hours which highly unacceptable.
There were no changes in any RMAN configuration, DB parameter & compression algorithm etc.
No specific warning/errors were seen in alertlog or traces while running backup. Even taking 10046 trace didn’t give much clue about slowness observed. Storage side also looked clean ruling out IO issue.
As we had changed compatible parameter to 19.0.0 there was no straightforward method to change it back to 126.96.36.199.
One more interesting observation was around backup size & compression ratio. Though we didnt perform any RMAN configuration changes, L0 backup completed with higher compression ration & reduced backup size. Please see table given below highlighted entry:
Thought we observed improvement in backup size but backup run time was increased by approx. 2.5 times.
There was somewhat matching bug “Bug 33352740 RMAN: After upgrade to 19.11 high wait event “waiting for ‘RMAN backup & recovery I/O'” which suggested to explicitly set pga_aggregate_limit and pga_aggreagate_target values in DB. We already had these parameters with non-zero value, we ignored safely ignored this bug.
We even changed compression from MEDIUM to BASIC without any significant performance gain in RMAN compressed backup.
During analysis we found one interesting setting:
CONFIGURE COMPRESSION ALGORITHM 'MEDIUM' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ;
Now a big question ==> Can I control RMAN “release/version/compatible” along with compression algorithm???
YES WE CAN!!!
I tried below setting for testing purpose as my earlier compatible was set to ‘188.8.131.52’ & it command executed successfully
CONFIGURE COMPRESSION ALGORITHM 'MEDIUM' AS OF RELEASE '184.108.40.206' OPTIMIZE FOR LOAD TRUE;
Subsequent L0 backups completed successfully within 10 hours & with approx. compression ratio 4.8. You can backups done after 16th May completed within acceptable time.
Hope u will find this post very useful!!!
Categories: 12c, 19c, Administration, Advanced features, backup & recovery, DB parameters, Exadata, Feature, Linux, Monitoring, Operating System, ORA errors, Oracle 18c, Peformance Tuning, RAC, Scripts, Upgrade
Leave a Reply