Comprehensive data protection for all workloads
Post Reply
cxwsrei
Enthusiast
Posts: 45
Liked: 2 times
Joined: May 22, 2017 1:18 pm
Contact:

Any way to speed up Oracle restore ?

Post by cxwsrei »

I´m testing oracle recovery.
The system is based on Linux (SLES11) and Oracle 12.
I ran a restore of a 500 GB database .
This took about 5 hrs.
It appeared as if ONE stream (only) restores/rewrites the databse files serially.
Is there any way to speed up the restore (parallelism e.g.) ?
jazzoberoi
Enthusiast
Posts: 96
Liked: 23 times
Joined: Oct 08, 2014 9:07 am
Full Name: Jazz Oberoi
Contact:

Re: Any way to speed up Oracle restore ?

Post by jazzoberoi »

I just did my first Oracle DB restore and found that the restore were slow as well. Its almost a blank database of only 10 GB and still took around 20mins restoring.

Will there be any restore speed enhancements in v10 ?
Gostev
Chief Product Officer
Posts: 31460
Liked: 6648 times
Joined: Jan 01, 2006 1:01 am
Location: Baar, Switzerland
Contact:

Re: Any way to speed up Oracle restore ?

Post by Gostev »

Checking with the devs...
A.Stepanov
Lurker
Posts: 1
Liked: never
Joined: Jun 20, 2012 6:12 am
Contact:

Re: Any way to speed up Oracle restore ?

Post by A.Stepanov »

cxwsrei wrote: I ran a restore of a 500 GB database .
How many data files are in your database?
Are they in the file system or ASM?
jazzoberoi
Enthusiast
Posts: 96
Liked: 23 times
Joined: Oct 08, 2014 9:07 am
Full Name: Jazz Oberoi
Contact:

Re: Any way to speed up Oracle restore ?

Post by jazzoberoi »

Hi,

The database files total only 12.5 GB and it took almost 15 mins to do a restore.

Image

Image

The restore target is another VM on a Filesystem.
foggy
Veeam Software
Posts: 21069
Liked: 2115 times
Joined: Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson
Contact:

Re: Any way to speed up Oracle restore ?

Post by foggy »

We're looking into the ability to improve restore using parallel streams, but the smaller the DB is, the tinier effect parallelism will have, since auxiliary operations (such as mount, mode switching through RMAN, etc.) will take time comparable to restore itself. Anyway, logs should tell the exact operations that take long, so I'd appreciate if you open a case with our technical support and share the ID here, so I could point the right people directly to your log files.
Gostev
Chief Product Officer
Posts: 31460
Liked: 6648 times
Joined: Jan 01, 2006 1:01 am
Location: Baar, Switzerland
Contact:

Re: Any way to speed up Oracle restore ?

Post by Gostev »

Circling back on this, according to our own lab testing, with no ASM involved large database file restore speed should be at least 1TB/hr. If your performance is significantly slower than this, there's likely a bottleneck somewhere in the infrastructure - feel free to open a support case for help with troubleshooting. Thanks!
cxwsrei
Enthusiast
Posts: 45
Liked: 2 times
Joined: May 22, 2017 1:18 pm
Contact:

Re: Any way to speed up Oracle restore ?

Post by cxwsrei »

I come back to Gostev's comment from 02 Oct 2018. Meanwhile I had an ticket open related to this issue.
Finally veeam could not help me to improve the speed.
The major reason from my point of view is (STILL) that the oracle restore performs only on one thread (respectively one oracle RMAN channel) only.
There is no parallelism !
This is until now the killjoy on that task.
I don´t know how you achive a TB/hr rate - maybe by the usage of explicit configured infrastructure landscape with extreme huge buffers and the usage of alot of SSDs !?!
But my intention is to optimize this software - IT PROCESSES THE RESTORE (AT LEAST IN MY CASE) ONLY BY ONE THREAD !
Is there no opportunity to force oracle rman parallelism ?
Gostev
Chief Product Officer
Posts: 31460
Liked: 6648 times
Joined: Jan 01, 2006 1:01 am
Location: Baar, Switzerland
Contact:

Re: Any way to speed up Oracle restore ?

Post by Gostev »

Actually, this thread is about restoring OCI-based Oracle backups. We did not have RMAN integration at the time. Perhaps, it would be best to start the dedicated thread on restore performance of RMAN backups. Thanks!
cxwsrei
Enthusiast
Posts: 45
Liked: 2 times
Joined: May 22, 2017 1:18 pm
Contact:

Re: Any way to speed up Oracle restore ?

Post by cxwsrei »

Gostev : I have to insist because the oracle restore is issuing a rman restore (We´re not using the newly published explicit RMAN feature).
This can be seen on the restore logs when extended logging is enabled.
Unfortunately I cannot see (don´t know if there can be also a more detailled logging enabled at backup) how it is done at the backup.
Following an etract of the restore test I´, still performing :

Code: Select all

6/27/2019 2:42:24 PM   18 (29236)     Executing [10]: select totalwork, sofar, message from v$session_longops where sofar != totalwork and opname='RMAN: aggregate input';...
6/27/2019 2:42:24 PM   18 (29236)       SQL> 
6/27/2019 2:42:24 PM    6 (9144) Executing RMAN script: RUN {
CATALOG DATAFILECOPY '/tmp/Veeam.Mount.FS.ff7b9653-ed57-439a-ba32-687905130d08/dewes0163_5-flat.vmdk_0/system_1/system.data1';
.
.
CATALOG DATAFILECOPY '/tmp/Veeam.Mount.FS.ff7b9653-ed57-439a-ba32-687905130d08/dewes0163_5-flat.vmdk_0/sr3_34/sr3.data34';
allocate channel vc0 device type disk;
SET NEWNAME FOR DATAFILE '/oracle/P60/sapdata1/system_1/system.data1' TO '/oracle/P60/sapdata1/system_1/system.data1';
.
.
SET NEWNAME FOR TEMPFILE '/oracle/P60/sapdata1/temp_3/temp.data3' TO '/oracle/P60/sapdata1/temp_3/temp.data3';
RESTORE DATABASE;
SWITCH DATAFILE ALL;
SWITCH TEMPFILE ALL;
}
This is oracle rman, isn´t it ?

I enabled oracle RMAN on this machine to use 3 channels for parallelism.
In this log file you can also see that the rman is in the beginning opening these 3 channles but not using them because there is no related backup !?!
Andreas Neufert
VP, Product Management
Posts: 6707
Liked: 1401 times
Joined: May 04, 2011 8:36 am
Full Name: Andreas Neufert
Location: Germany
Contact:

Re: Any way to speed up Oracle restore ?

Post by Andreas Neufert »

RMAN is used only as helper for executing specific tasks, but the real restore is done by our datamovers and RMAN channel configuration has no effect on our image based backup Oracle restores.

Can you please share a support case number which include your restore logs?
cxwsrei
Enthusiast
Posts: 45
Liked: 2 times
Joined: May 22, 2017 1:18 pm
Contact:

Re: Any way to speed up Oracle restore ?

Post by cxwsrei »

Dear Andreas
thank you for the feedback.
The case I had opened before was/is 03540431.

If veeam allows me to open a new case (or re-open the previous case I opened to that topic - of course I like to provide you with the most recent logs).
But corresponding to your statement : you say "RMAN is used only as helper" - but as I issued already via my statement from 1:31 pm veeam issues the COMPLETE restore command via RMAN.
This is for my understanding a littel more than only a "helper" functionality - and corresponding to this I do not understand on that point what "image based backup" you´re talking about !?
To me it looks like that the complete database backup is executed via the oracle rman ionterface (it´s definitely nothing about OCI).
Anyway - this is arguing for nothing because I think it´s on that point a question of understanding and of course you know your software better than me.

Finally : it is simply desired to enable anyhow a speedup of that process (not only by buying a ghigh performance infrastructure) but by optimizing configuration settings of the used software component.
Maybe it´s possible - maybe it´s not (at least not on that release) .
Andreas Neufert
VP, Product Management
Posts: 6707
Liked: 1401 times
Joined: May 04, 2011 8:36 am
Full Name: Andreas Neufert
Location: Germany
Contact:

Re: Any way to speed up Oracle restore ?

Post by Andreas Neufert »

Let me please check your old case.

There is a good document that include in Appendix B the way how we restore the database and in what cases we use RMAN.
https://www.veeam.com/wp-sap-oracle-dep ... guide.html

Anyway will look into your case and come back here (need some time).
cxwsrei
Enthusiast
Posts: 45
Liked: 2 times
Joined: May 22, 2017 1:18 pm
Contact:

Re: Any way to speed up Oracle restore ?

Post by cxwsrei »

Andreas
thank you for the link - I know the document already.
Don´t spend much time on the ticket that has been opened - finally I closed it because there has been no results/no useful hints how to improve the restore speeed anyhow.

In the end my current question (proposal/request to improve the product) is the following :
I issued on my test system the following :
RMAN> CONFIGURE DEFAULT DEVICE TYPE TO disk;
RMAN> CONFIGURE DEVICE TYPE sbt PARALLELISM 3;

As a result I see in the log file of the oracle restore (by veeam) the following :

database name is "P60" and DBID is 1153063992

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=131 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=163 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=195 device type=DISK
specification does not match any backup in the repository

released channel: ORA_DISK_1
released channel: ORA_DISK_2
released channel: ORA_DISK_3
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=131 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=163 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=195 device type=DISK
specification does not match any datafile copy in the repository

Unfortunately it closes the channels instantly because there is (obviously) nothing that matches.
But seems logically to me because ther is no rman catalog used.

Later veeam backup calls rman - but using only ONE channel (vc0 !!) - as following :

6/27/2019 2:42:24 PM 6 (9144) Executing RMAN script: RUN {
CATALOG DATAFILECOPY '/tmp/Veeam.Mount.FS.ff7b9653-ed57-439a-ba32-687905130d08/dewes0163_5-flat.vmdk_0/system_1/system.data1';
.
.
CATALOG DATAFILECOPY '/tmp/Veeam.Mount.FS.ff7b9653-ed57-439a-ba32-687905130d08/dewes0163_5-flat.vmdk_0/sr3_34/sr3.data34';
allocate channel vc0 device type disk;
SET NEWNAME FOR DATAFILE '/oracle/P60/sapdata1/system_1/system.data1' TO '/oracle/P60/sapdata1/system_1/system.data1';
.
.
SET NEWNAME FOR TEMPFILE '/oracle/P60/sapdata1/temp_3/temp.data3' TO '/oracle/P60/sapdata1/temp_3/temp.data3';
RESTORE DATABASE;
SWITCH DATAFILE ALL;
SWITCH TEMPFILE ALL;
}

Then the restore is processed by only ONE thread / only ONE channel.
IN our case this takes about 2.5 (best results until now) to 3.5 hours (DB size to be restored : about 550 GB).
This means we achive only a restore rate of about 200 - 250 GB / hour !
Of course this would be much higher when parallelism would be available anyhow.
And consequently the future view is bad, because when the size of the DB grows up to one TB this means that a oracle DB restore via veeam will take 5 - 7 hours.
I like Gostev - I´m a big fan of his weekly publications but when he wrote 1TB/hr should be (easily) achivable - of course I felt challenged.
Last but not least - we really had two restore cases in the last 2 years and the restore all the time took too long from our point of view.

So : I don´t know how veeam is creating the backups to become oracle rman compatible (as discussed rman is used in restore cases) - but it would be really helpful to enable parallel processing,
especially for all that like to backup BIG oracle databases (larger 1/2 TB as in our case).
I agree to foggy´s comment (in th blog, from August 2017) that parallelism does not make much sense when only small DBs are backup but in the case of big DBs it makes sense - a lot !
cxwsrei
Enthusiast
Posts: 45
Liked: 2 times
Joined: May 22, 2017 1:18 pm
Contact:

Re: Any way to speed up Oracle restore ?

Post by cxwsrei »

correction to my previous issue :

1) I issued on my test system the following :
RMAN> CONFIGURE DEFAULT DEVICE TYPE TO disk;
RMAN> CONFIGURE DEVICE TYPE disk PARALLELISM 3; # disk, not sbt !!

2) I wrote : "So : I don´t know how veeam is creating the backups to become oracle rman compatible " -> I checked the log again -> conclusion : veeam is not doing anything at the backup to make the backups "rman-compatible" - all is done at the restore -> in this case veeam registers the files via CATALOG DATAFILECOPY commands.

Conclusion on my side : as far as I can see I cannot do anything to "enable" parallel processing -> this would only be available via the RESTORE DATABASE comand that is issued by veeam´s restore process.

Nevertheless : desireable.
Andreas Neufert
VP, Product Management
Posts: 6707
Liked: 1401 times
Joined: May 04, 2011 8:36 am
Full Name: Andreas Neufert
Location: Germany
Contact:

Re: Any way to speed up Oracle restore ?

Post by Andreas Neufert »

I agree it is desireable. Will check with DEV if it is technical possible and if we can add it in one of the next versions. Based on the rules given by our top management, we can not comment further about upcoming release planning. So, there will be no additional feedback by design of our processes.
Andreas Neufert
VP, Product Management
Posts: 6707
Liked: 1401 times
Joined: May 04, 2011 8:36 am
Full Name: Andreas Neufert
Location: Germany
Contact:

Re: Any way to speed up Oracle restore ?

Post by Andreas Neufert » 2 people like this post

I have checked with our R&D teams and there is a way by adding a specific XML file to use multiple channels at the copy process. It is not enabled by default as other areas will be the bottleneck.
If you want to test it, please open a support case and let me know the number here. I will update the case with the needed infromation.
cxwsrei
Enthusiast
Posts: 45
Liked: 2 times
Joined: May 22, 2017 1:18 pm
Contact:

Re: Any way to speed up Oracle restore ?

Post by cxwsrei »

Hi Andreas

of course I like to know more about this option.
I opened the following case : 03649944
Andreas Neufert
VP, Product Management
Posts: 6707
Liked: 1401 times
Joined: May 04, 2011 8:36 am
Full Name: Andreas Neufert
Location: Germany
Contact:

Re: Any way to speed up Oracle restore ?

Post by Andreas Neufert »

Added the inforamtion to the support case. It will be shared with you soon.
Matthias.Mehrtens
Veeam Software
Posts: 117
Liked: 26 times
Joined: Nov 02, 2017 11:56 am
Full Name: Matthias Mehrtens
Contact:

Re: Any way to speed up Oracle restore ?

Post by Matthias.Mehrtens »

Is there a similar option to control/increase number of channels when restoring Oracle DBs via Enterprise Manager?
Andreas Neufert
VP, Product Management
Posts: 6707
Liked: 1401 times
Joined: May 04, 2011 8:36 am
Full Name: Andreas Neufert
Location: Germany
Contact:

Re: Any way to speed up Oracle restore ?

Post by Andreas Neufert »

Mathias please reach out to me directly to discuss the situatuion (image or RMAN)
Post Reply

Who is online

Users browsing this forum: No registered users and 235 guests