Re: Advice on migrating from 8.0.5 to 8.1.7 on Windows

John wrote:

> I currently have a Windows NT 4.0 SP4 box with Oracle 8.0.5 on it.
> Our 8.0.5 has custom tablespaces and folder structures for the
> tablespace files, which is why I want to upgrade to Windows 2000 SP3
> with Oracle 8.1.7. Here is what I'm doing now in my planning. Let me
> know if this sounds ok:
>
> 1. When installing Oracle I choose 8.0.5 compability and tell it not
> to create a database.
>
> 2. Against current production 8.0.5 database move it into restricted
> session mode.
>
> 3. In DOS do a full dump of the database.
>
> 4. Put the database back out of restricted session.
>
> 5. Against production in SQL Plus save to a sql text file the output
> of a statement that creates a bunch of create user with the username
> identified by the password out of the current database as the oracle
> 8.0.5 and 8.1.7 databases have the users in different tablespaces and
> importing seems to fail if I do not recreate them manually first.
>
> I edit the text file that is created in step 5 and remove the creation
> of sys, system, and dbsnmp.
>
> 6. I run the Database Configuration Assistant with compability of
> 8.0.5 and enable archive loging.
>
> 7. After done I up the max_enabled roles as I have a lot of roles in
> my system.
>
> 8. Edit the sqlnet.ora and change SQLNET.AUTHENTICATION_SERVICES=
> (NTS) to SQLNET.AUTHENTICATION_SERVICES= (NONE). Seems Oracle won't
> start on boot if it's set to NTS.
>
> 9. Restart the server.
>
> 10. Run the user sql script that was created in step 5 against
> production to create all user accounts.
>
> 11. Run an imp against the dump file using indexfile to get a text
> output of the dump.
>
> 12. Remove all the rem words from the indexfile, remove lines starting
> with ... (these were the row counts), replace references to 8.0.5
> tablespaces with the new ones.
>
> 13. Log into the 8.1.7 database with svrmgrl as system and run the
> indexfile against it.
>
> 14. Run an imp against the original 8.0.5 dump file on 8.1.7 setting
> ignore=y.
>
> 15. Recreate and recompile any failed functions, packages, package
> bodies, and procedures.

Not knowing how large it is my thought would be that you have overcomplicated things. Why not:

1. Create 8.1.7.4 database with SYSTEM, RBS, and TEMP tablespaces
2. Create tablespaces required for your application.
3. Create users required for your application.
4. Add procedures, functions, triggers, and packages from source code.
5. Create a database link.
6. Move the data across the link.

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)