Re: Migrate to locally managed tablespaces

gargaare_at_yahoo.com (farah nur) wrote in message news:<257c0775.0204180351.673558b7_at_posting.google.com>...
> Hello,
>
> I am trying to fully migrate oracle applications tablespaces (apps
> 1155, db 817) to fully Locally managed tablespaces. The database is
> 200 Gig.
>
> Here is what I was planning to do:
>
> -exp schema
> -exp data
>
> -create new database with LMT tbs
>
> -imp schema
> -drop indexes
> -disable primary keys
> -imp data
> -enable primary keys
> -recreate indexes
>
> However, I am finding out since the apps modules are bundled now,
> there thousands of indexes to deal with, which will increase down time
> when dropping and recreating.
>
> Has anyone done a better way?
>
> Thanks

One way to reduce the downtime without going to the trouble of generating all the code would be to separate the table and index reloads via the exp/imp process. You would make two different full exports. The first would have compress=n, constraints=n indexes=n grants=y set while the second would have rows=n. Then you would import only the tables with the first export file and bring the indexes and constraints with the second imp.

If you are willing to do a little more work you should generate the source code for all the objects and redistribute the objects based on size. This will allow you to associate large objects to tablespaces defined with a larger uniform extent size rather than just use autoallocated extents. Then you run the same import above followed by a manual build of the indexes and then the second import to bring the constraints and anything you might have missed.

If you break the first export down into a series of exports using tables= clauses you can then parallelize the process and run several exp/imp jobs at the same time depending on your disk capacity and reduce clock time.

You might want to take the database out of archive mode during the reload as this may prove beneficial performance wise.

HTH -- Mark D Powell -- Received on Thu Apr 18 2002 - 14:10:26 CDT