Backup Of A Pregrown Empty Db

Version: 11.6
OS: Linux CentOS7

Can we take a backup of a pregrown empty db? Below are the steps are I tried but couldn't achieve what I want.

1. Created an empty DB (Type II Storage Area with ~70 areas)
2. Applied DF
3. Binary load of 963 tables
4. Ran Index Build (now DB size is 1.5 TB - .dn files are grown)
5. procopy empty8 on the newly created db

I can copy these .dn files to the new location and run the 'prostrct builddb' to get the pregrown db structure set before a binary load but not able to achieve this using a probkup.

I tried 2 different methods but ended up invain;
1. I tried doing a probkup after step 5 but only empty db got backed up and not with the pregrown data extents
2. I tried loading the DF after step 5 so that the structure will be in place and thought may be probkup will backup the empty pregrown extents but it didn't

Is there a way to achieve this using probkup? Or should I be depending only on OS Copy and prostruct builddb to achieve this?
 

TomBascom

Curmudgeon
Why not do a probkup after step #2?

Restore that backup as step #5 and you will be in position to repeat 3 & 4 with minimal effort.
 
Why not do a probkup after step #2?

@ step 2 the db may be hardly 25 MB.

The exercise am trying to do is not for repeating on the same db.

Let's say for example, I will perform step 1 to 5 on /netappxyz/dev/db/xyz.db (data files will be under /netappxyz/dev/datafiles/*.dn) and take a backup after step 5 to restore and build a pregrown empty db say under a different location /netappxyz/dev1/db/xyz.db (data files will be under /netappxyz/dev1/datafiles/*.dn) so that I can do a binary load with new set of dump files (which will have little more data than one that I used for xyz.db).

Another place where it will be useful for me is, let's say I have to build a dev db of size 1.5 TB from the prod hotspare backup. If I have the data extents pregrown the restore takes 6 hrs whereas if I restore without the extents being pregrown it takes around 13 hrs. Currently I am moving the .dn & .b1 files of hotspare to dev, then do a prostrct builddb and then procopy empty8 before starting my actual restore. OS copy takes 1.5 hrs and restore takes another 6 hrs, so overall in 7.5 hrs I will be able to get this done whereas if I restore without a pregrown db it takes 13 hrs.

So was thinking if I can backup an empty pregrown db which will actually have the data extents grown but pretty much no data in it; such a db should be able be backed up and restored faster. As per my understanding probkup is data driven backup and not file driven backup - so not sure if this is possible. I would like to get it confirmed. Please correct me if am wrong.
 

TomBascom

Curmudgeon
Yes, but at step 2 you have the proper .df. Which saves some time vs using "empty" and then loading the .df.

To get the pre-grown variable extents the first time you will need to either copy them and do prostrct repair (or buildbb) or do a prostrct create if the extents are all variable. If they are fixed extents then prorest will extend them (depending a bit on if there is a .st file and what it has to say).

You should only need to do this the first time you initialize a db on new storage.

I often do this kind of thing when testing dump & load routines. So far as I know there is no shortcut for that initialization step. But after that it's pretty easy to work from a backup made at step #2.
 
We use all Variable Extents and loading the DF hardly takes < 10 mins on netapp and ~1 min on localdisk. Prod will be running on Pure Array which will be faster and can expect it to be somewhere between 1 to 10 mins. So in my case taking a backup after step 2 is not that worthy.

You should only need to do this the first time you initialize a db on new storage.

That's how I expect it to be with little bit of struggles for the first time setup but next time pretty much I should have the data extents grown to more than 90% of actual data. But in few cases I see they ask me to create QA environment in a different location to the one they were using initially, same goes with PM environment. Here it's all config file driven, I have to build under a different location and just change the config file entries to get this working; so wanted to explore the options we have for this kind of scenario.
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
Have you tried creating your dummy db by making each large area one large fixed extent plus a variable? Once you do your load/idxbuild once, you have an idea of how large each area should be to hold the requisite data. On 11.6 (or any 11.3+), prostrct create should be fairly efficient as it now uses buffered I/O. It should be a lot faster than growing the extents by actually loading data and rebuilding indexes and then procopying empty8 overtop of it.
 
I haven't tried growing .dn files using Fixed Extent. I have made a note of how large each area can grow and have it handy. If I have understood in a right way, your suggestion is to have 1 fixed extent and 1 variable extent per area and the fixed extent will be of the size that I have noted down from my previous binary load/index build. Is that right?

I had a similar discussion before with the team here and they were adamant to go with only variable extents. How about growing the extents as fixed (if it's faster), then wipe out the structure with empty8 and create a new structure with all variable extents? - please bear with me, Rob; as I will be facing similar questions and wanted to be prepared.

My assumption for growing the db with binary load is not just for growing extents but thought it also contributes to the fragmentation factor and actual load will be faster. Is that not the case?
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
If I have understood in a right way, your suggestion is to have 1 fixed extent and 1 variable extent per area and the fixed extent will be of the size that I have noted down from my previous binary load/index build. Is that right?
Yes.

I had a similar discussion before with the team here and they were adamant to go with only variable extents. How about growing the extents as fixed (if it's faster), then wipe out the structure with empty8 and create a new structure with all variable extents? - please bear with me, Rob; as I will be facing similar questions and wanted to be prepared.
You can always change the number of extents in an area, e.g. moving to all-variable structure, with a prorest with a different structure file, as long as the number and configuration (RPB/BPC) of areas remains constant. That said, can the team explain what it is they are adamant about? If you load data that populates the fixed extent in an area and then the area grows into the variable extent soon afterward, how is that different from writing data to the variable extent from the start?

My assumption for growing the db with binary load is not just for growing extents but thought it also contributes to the fragmentation factor and actual load will be faster. Is that not the case?
Record fragmentation will be minimal after a load and will slowly grow over time. I'm not aware of any difference in fragmentation of binary loading into a small variable structure versus loading into a pre-grown structure.
 
That said, can the team explain what it is they are adamant about?

I am sure there wont be any proper explanation but unless I could show them concrete evidence of gain in time or performance they wouldn't like to change something that works 15 years this way - got used to hearing this... :)

If you load data that populates the fixed extent in an area and then the area grows into the variable extent soon afterward, how is that different from writing data to the variable extent from the start?

Very true. I will grow .dn files with fixed extents and see how long prostrct create takes and will complete a binary load/index cycle to capture the total time - will keep you posted with the time differences between different approaches.
 

Rob Fitzpatrick

ProgressTalk.com Sponsor
I am sure there wont be any proper explanation but unless I could show them concrete evidence of gain in time or performance they wouldn't like to change something that works 15 years this way - got used to hearing this... :)
In this particular case you have two options. One is to use an all-variable structure, grow it by spending hours loading data and building indexes, then wipe it out (via empty8) and reload your schema, and finally load your data and build indexes. The other is to use the fixed-extent approach, load schema, and build indexes. So the concrete gain of the latter approach is that you do load/build only once, not twice.

If you have available time and disk space, you can always create the large database in advance, somewhere else (on the same platform). Then when the time comes you can prorest or OScopy/prostrct rebuild to put the files in place, procopy empty8 and load schema. I'd say your options depend on your circumstances: what you need to do and when, how much time you have to do it, how much available disk space you have for copies & backups, how much I/O bandwidth for large file copies, etc.
 

TomBascom

Curmudgeon
There may also be some trick with a NAS snapshot available since you seem to have a netapp "filer" in play.
 
Thanks @Rob Fitzpatrick - I just started creating a db with fixed extents; will do the binary load/index build and will come up with a comparison chart.

Thanks @TomBascom - I agree, Snapshot is a good option. We were using ZFS snapshot on Sun Solaris for application that was running on 9.1E version which we are not using anymore. On the current Linux CentOS7 we don't have proper mount yet where we could move around the snapshots. As I mentioned on one of the old thread we wanted to eventually move towards snapshot but I can't go snapshot way right now.
 
Top