Re: Import problems when defragmenting a database

In article <387e3953.27707408_at_news.risq.qc.ca>,   gdallair_at_criq.qc.ca (Guy Dallaire) wrote:
> Hi,
>
> Last time I defragged one of the databases here, I had a lot of
> problems while re-importing my full datasase export.
>
> I proceeded by:
>
> a) Taking a full export of the database to be defragged
> b) Take a cold backup of the database files just in case
> c) Drop and recreate the database + tablespaces (At the same time,
> move some stuff around to better distribute I/Os)
> d) Reimport
>
> I stumbled accross many problems
>
> a) I had NOT recreated the users before doing the import. Assuming
> import would create them
>
> This seemed to cause problems. For example, some users owned database
> links in the database, when they got recreated, the import would not
> be able to re-create the database links because the users where not
> granted the "create database link" privilege (It had been granted
> temporarily only to create the database link in the past and revoked
> immediatly after)
>
> This lead to a myriad of other problems. Those users had synonyms that
> pointed to tables accross the databse links, those synonyms where
> invalid because the db link were not recreated. The users also had
> packages and procedures and views that used those synonyms and the
> packages etc were therefore invalid
>
> Well, the import was a total mess.
>
> Now, I have to defrag a new database and I'm wondering what to do to
> totally eliminate any pitfalls.
>
> Before I restart the import, I think I should first recreate:
>
> a) The tablespaces
> b) The users
> c) Grant ALL provileges to the users DURING the import (Grant DBA role
> ?)
> d) import
> e) Revoke DBA from all users (except those that already had it in the
> fist place)
>
> Is this a good way to do it ? Any other advice ? Does anyone know of a
> script that would create a script to recreate my users (with the
> correct passwords) in the new database ?
>
> What about a script to recreate the tablespaces ?
>
> Thanks
>

If you do not re-create the database then you do not need to recreate your users, and unless you need to move tablespaces files around there is no need to recreate them either.

Make your full export. Break the constraints. Truncate the tables. Drop the tables. Grant all owners necessary privileges. Import.

If you miss a grant and any objects because of it you can do a second import with rows=n and ignore=y to fix the problem.

--
Mark D. Powell -- The only advice that counts is the advice that  you follow so follow your own advice --

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Jan 13 2000 - 17:46:22 CST