Query on Database Export and Import

[Former Member]

Hi Techies,

Currently we are running our Database on Oracle 10g and SAP on4. and OS is HP UX 11.11.

We have a plan to migrate our HW from PA-RISC to Itanium and at the time of Production migration we are planning to use Export and Import method to get free space.

Our plan is as below:

We will not touch the original Production, Just restore the DB into new server.

And post restore we will create the space on new server equallent to our DB size.

Then will perform DB export from the new system to the null space

Then Import the DB into same system.

Here my queries are:

1) Is it possible to export and Import the Database from/to null space?

2) We have 2T size of DB, And good resources like 32G Ram, 12 CPUs etc. How much time can be expected to perform Export and Import?

3) What are the challenges we can expect?

4) Minimum how much of free space we can expect with this option?

Regards,

Nick Loy

[Former Member · Accepted solution]

In your case it could be convenient to perform the export/import in parallel, using the migration monitor.

You start the export on the source (PA-RISC), and parallel to the export, you start the import on the Itanium. This way you use all your resources in the most optimal way.

Your source will stay intact, so in case of a fall-back, you don't loose any time..

With regard to the export/import time, this can only be measured by performing a test export/import. If you have fast storage and a fast network, you should get at least 100GB/hour.

Kind regards,

Mark

[Former Member]

So I can get the free space (Re-usable) with Online re-orgs.

I will try to do DB Export and Import first, If any challenges or delay (downtime) then I will go for Online Re-orgs.

Regards,

Nick Loy

[Former Member]

In case freeing up space is your goal, it might be worthwhile checking the following document: http://www.sdn.sap.com/irj/scn/index?rid=/library/uuid/f0774cd8-03dd-2d10-0897-8ca1ffc388ec

You only have to upgrade to Oracle 11.2

Kind regards,

Mark

[Former Member]

Thanks Micheal for your inputs.

Yes I didn't tell you why I am looking for both the options, The reason is to get free space at file system level.

I know that with Export and Import, definately I will get free space on file systems and I can reuse the space.

But not sure on Re-orgs.

Regards,

Nick Loy

[Former Member]

Ok, then it is best to reorg from old tablespace to a new tablespace and drop the old tablespace as soon as it is empty. This will give you the space back on the filesystem. This means you will need additional space during the reorganisation.

Cheers Michael

[Former Member]

Hi Micheal,

Many thanks for your valuable suggestions.

Need some more inputs on Online Reorgs as DB export and Import may not be possible for me to execute within a minimum downtime.

If I want to do Online reorgs, How can I know the no. of objects need to be re-org'd. And at what basis I need to re-org the Database (Table level or Tablespace level)?

And is there any process to get the list of objects need to be re-org'd (If it is at table level) ?

Is it possible to do an online re-orgs on standby DB?

My plan is :

1) Restore the DB into target server

2) Keep the original source online and do online re-orgs on taget server

3) Post completion apply all the archivelogs

4) Take a minimum downtime window to perform a cutover and release the target server

Regards,

Nick Loy

[Former Member]

If you want to perform an online reorg of your database, you should check the the tool brspace.

This tool provides the possibility to perform a online database reorganization online.

Check this guide: http://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/afc2af2f-0d01-0010-f0ad-dc0e92b3c...

and http://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/70c8f19f-8010-2c10-ac89-8d828039d...

Kind regards,

Mark

Edited by: Mark Dijsselbloem on Nov 29, 2010 8:43 AM

[Former Member]

Well you did not tell us the exact reason to do the reorg. If you still have dictionary managed tablespaces then you will need to reorg the object from those to newly created tablespaces. Besides, if you still have the old tablespace layout (PSAPBTABD etc), then you can consider moving to PSAPSR3...

If you only want to reorg large tables (where data was deleted) then you obviously only have to do these objects. For very large tables > 50g it can make sense to place them in a seperate tablespace as well.

And is there any process to get the list of objects need to be re-org'd (If it is at table level) ?

Please read:

Is it possible to do an online re-orgs on standby DB?

Not possible in my opinion, discussed here lately:

Cheers Michael

[Former Member]

Thanks for your updates Markus,

You can use sockets to export on one system and import it into the second system.

No....I don't want to use 2 systems for this activity, I want to export the database into extra storage space on same server and after that import into same server........Is it possible?

An optimized R3load export/import can be done at roughly 1 TB/hour.

So with test runs I can expect rapid speed in DB export and Import (1T/H)........If I have good system then Database export and Import gets complete within 2 hrs (Database size is 2T).

What about the Online re-org of database? What would be the best way to get free space within minimum downtime?

Regards,

Nick Loy

[Former Member]

So with test runs I can expect rapid speed in DB export and Import (1T/H)........If I have good system then Database export and Import gets complete within 2 hrs (Database size is 2T).

Well 1tb is at the very top of expectations here, you should be careful. I did an export/import of a 1.5tb database of an ERP system lately. We did parallel export (40 processes) / import (20 processes) using distmon, source was HP IA64, target Linux x64_64. The disks were midrange SAN storage systems on both sides. After tuning we managed to do in 6-7hrs.

But in your case, if you only have one system, this could mean you have to drop the source db first and then recreate the target db on the same disks. The creation of the 1-2tb database files alone can take up more than 1 hour , besides that you don't have an easy fallback.

If you have a test system that is comparable from size and hardware perspective, then i suggest you try a test export to get a feeling for it.

What about the Online re-org of database? What would be the best way to get free space within minimum downtime?

Theoretically you should be able to gain more or less the same amount of space doing online reorgs. The advantage is less downtime, the downside is the reorgs will be running over a longer time period and put additional load on the system.

Cheers Michael

[markus_doehr2]

> Here my queries are:

> 1) Is it possible to export and Import the Database from/to null space?

You can use sockets to export on one system and import it into the second system.

> 2) We have 2T size of DB, And good resources like 32G Ram, 12 CPUs etc. How much time can be expected to perform Export and Import?

This depends on a lot of unknown factory like speed of I/O subsystem, number of R3load processes, tables split or not etc. Without a few test runs it can't be said.

> 4) Minimum how much of free space we can expect with this option?

nobody here knows your database and how it's filled - so nobody can tell you numbers.

An optimized R3load export/import can be done at roughly 1 TB/hour.

Markus

[Former Member]

Thanks for your valuable inputs.

Let me get clarified on both the options:

1) If I do a online re-orgs how much of space that I can get in a TB (Approx)

2) If I do R3load, How much of space that I can expect in a TB? and Is it possible to export the DB into null space and import the same into same database?

Regards,

Nick Loy

[Former Member]

In your special case, personally i would not do a full export/import at all. I would take over the original database and then work with online reorgs. This will save you a lot of downtime. If you still have dictionary managed tablespaces (SYSTEM tablespace?), and/or the old tablespace layout this can be corrected too.

Be sure to use the same procedure on the test system. This will minimize ugly surprises.

Off course the R3load approach is a way to go, but requires more testing and as Mark said the downtime can only reduced significantly if you import/export in parallel (using migmon or even distmon) and with optimized table splitting.

Cheers Michael