This discussion is archived
4 Replies Latest reply: May 7, 2012 6:48 AM by UweHesse RSS

expdp data without storage meta_data

user195007 Newbie
Currently Being Moderated
Hi,
I'm working on a process to move database 10.2 from HP-UX with filesystem to Exadata 11.2 with ASM.
We have limited room for downtime on the database and would like to start on Exadata as fresh as possible.
I've just started with writing this process but wanted to check if someone had any ideas or comments on how to speed up the process. Each database on HP-UX will be moved sepratly and it will be one database at a time.
Alternative one:
1. Create ASM and database instance on Exadata
2. Run create script for tablespaces
3. Run create script for users
4. Expdp relevant schemas (not system/sys and statistics)
5. Move the dump files to Exadata (maybe connect the Exadata to our existing SAN)
6. Impdp schemas
7. create statistics
8. Test database
9. Production :-)

Alternative two:
1. Create ASM and database instance on Exadata
2. Run create script for tablespaces
3. Run create script for users
4. export table, index, etc metadata but without storage info (initial extent 4gb on a index and so on) with dbms_metadata
5. export data based on user tables.
6. Configure external tables in new database on Exadata with ASM
6b. insert data with the use of dump files and select against external tables
7. create statistics
8. test database
9. production

Regarding alternativ 1 I do know that it's possible to dump only metadata, but is it possible to dump only metadata but without storage info as described above?
If you have another solution please feel free to add a alternativ 3 and 4 :)
Thanks all.
  • 1. Re: expdp data without storage meta_data
    UweHesse Expert
    Currently Being Moderated
    If you want to have the storage attributes of your segments modified during Data Pump Import, you could do the following:

    1) create a tablespace with your desired storage attributes in the target database
    2) impdp with transform=storage:n and remap_tablespace

    That way, your imported segments can get initial extents of 8m, e.g.
    (4m as initial extent will not give you a 4m initial extent on a tablespace with autoallocate - they will use 64k, 1m, 8m or 64m sized extents instead)

    See here for the documentation of this - it's not Exadata specific:
    http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_import.htm#BEHEDGJJ

    Kind regards
    Uwe Hesse

    "Don't believe it, test it!"
    http://uhesse.com

    Edited by: Uwe Hesse on 07.05.2012 09:07 added the part with autoallocate
  • 2. Re: expdp data without storage meta_data
    933318 Newbie
    Currently Being Moderated
    1. Create ASM and database instance on Exadata
    2. Run create script for tablespaces(Depending on option you choose)
    3. Run create script for users
    4. Source gather statistics
    5. Export stastics
    4. Expdp (You can transportable tablespace(faster) or export of schemas). Try taking the dump on a nfs or any filesystem which can be mounted on both old and new servers.
    5. Move the dump files to Exadata / This time can be saved if you can mount the filesystem on exadata.
    6. Impdp schemas/ Tablespace
    7. Import Statstics
    8. Test database
    9. Production
  • 3. Re: expdp data without storage meta_data
    user195007 Newbie
    Currently Being Moderated
    Thanks Uwe,
    If I'd just like to keep tablespace default what will the command look like?
    impdp ...... transform=storage (withouth anything else)?
    Morten
  • 4. Re: expdp data without storage meta_data
    UweHesse Expert
    Currently Being Moderated
    impdp ... transform=storage:n would import into the same tablespace as in the original DB with the storage attributes derived from the default attributes of that tablespace in the target DB.

    Kind regards
    Uwe Hesse

    "Don't believe it, test it!"
    http://uhesse.com

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points