4 Replies Latest reply: May 7, 2012 8:48 AM by UweHesse RSS

    expdp data without storage meta_data

    user195007
      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
          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
            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
              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
                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