Re: Upgrade Oracle 9i to 10g from 4096 to 8192 db block size

On Dec 5, 4:55 pm, ddf <orat..._at_msn.com> wrote:
> On Dec 5, 3:39 pm, Kaka <kaka...._at_gmail.com> wrote:
>
>
>
>
>
> > I tried to exp/imp Create your 10.2.0.3 database with an 8k block
> > size
> > and import your 9.2 data.
>
> > and during the imp i got these error mesgs:
>
> > ORA-29339: tablespace block size 4096 does not match configured block
> > sizes
>
> > And
>
> > there are many roles, privs, username, tablespaces not created with
> > the exp/imp.  I already specified the exp/imp option with full=y
>
> > Below is my exp and imp script:
>
> > exp script --
>
> > nohup exp user/passwd buffer=5000000 log=fulexp.log file=exp_pipe
> > full=y &
>
> > imp script --
>
> > nohup imp \'user/passwd AS SYSDBA\' full=y file=imp_pipe
> > log=imp_fin1.log buffer=5000000 ignore=y &
>
> > any advise?  thanks!
>
> I expected that you'd create your tablespaces BEFORE starting the
> import; do that, and specify ignore=y as an imp parameter and you
> shouldn't have these problems.
>
> David Fitzjarrell- Hide quoted text -
>
> - Show quoted text -

I agree with David: precreate your tablespaces. This allows you to change the number and size of files under the tablespace, convert the tabelspaces to being locally managed if the old db is still using dictionary managment, and gives you the option of setting ASSM on or off as you have determined how you want to use this feature.

The ignore=y option would only be necessary if you pre-create tables/ indexes which is another idea to consider. When you migrate a database via export/import you have an opportunity to relocate objects for space management or other purposes as part of the import. Just pre-create the table where you want it and use ignore=y.

I would also pre-create the owning users and grant necessary system privileges prior to running the import. Nothing should be lost but a quick and easy way to make sure nothing is lost is to make a full=y rows=n export and then after your primary import(s) are done running an import of the no data exprot. We have used tables= exports to move data and then the full import to bring packages, procedures, public synonyms, etc ....

A simple group by owner, object_type on dba_objects taken before the export and after the import will give you a high level confirmation that you have not lost/missed any object. A full listing ordered by owner, object_type, object_name will make finding any lost object easy should there be a problem. Reviewing you current system for any home grown routines created under user SYS or that are currently INVALID would be a good idea. Any object missing is likely due to one of the preceeding conditions.

HTH -- Mark D Powell -- Received on Sat Dec 06 2008 - 09:10:49 CST