Re: IMPORT - how many passes?

From: Walt <walt_at_boatnerd.com>
Date: Fri, 07 Nov 2003 09:57:38 -0500
Message-ID: <3FABB2E2.B91348F2@boatnerd.com>

Brian Peasland wrote:
>

>< Much good advice snipped >

Well, you're right that a one-pass full import is the easy way to do it. After a bit of tweaking I've gotten it to go through. Thanks a bunch.

For anybody else who's trying it for the first time, here are my notes on how to do it ("it" being migrating an 8.1.6/WinNT Oracle instance to Oracle 9.2/W2k using exp/imp) :

0) Run a full export from the old database.

Install the Oracle software on the new box, but don't use the install program to create a database, do that as a separate step.
Create the database - I found that the Database Creation Assistant works pretty well, but make sure you choose the "New Database" template rather than the "General Purpose Database" template. The "New Database" option allows you to deselect all the demo databases eliminating much cruft. It also allows you to export the database creation scripts - well worth reading.

2a) Make sure you match the character set of the new database to the old one (you can't change it later, and if they don't match you might get translation errors). You can run "SELECT * from nls_database_parameters" on the old database to find out the character set.

3) Run sqlplus sys as sysdba/pwd and run the catalog.sql script

4) Invoke imp with rows=n show=y logfile=yourlog.txt (login as SYSTEM when prompted).

5) Examine the log generated - the first thing it does is create the tablespaces. You need to copy and paste these tablespace creation statements into a file, and change the syntax to create Locally Managed Tablespaces instead of Dictionary Managed Tablespaces. As an example this:

CREATE TABLESPACE "BAR"
  DATAFILE 'F:\ORACLE\ORADATA\FOO\BAR01.DBF'   SIZE 1250M
  AUTOEXTEND ON NEXT 1048576
  MAXSIZE 4095M DEFAULT
  STORAGE(INITIAL 32768 NEXT 57344 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 10) ONLINE PERMANENT EXTENT MANAGEMENT DICTIONARY turns into this

CREATE TABLESPACE "BAR"
  DATAFILE 'F:\ORACLE\ORADATA\FOO\BAR01.DBF'   SIZE 1250M
  AUTOEXTEND ON NEXT 1048576
  MAXSIZE 4095M
  AUTOALLOCATE
  ONLINE PERMANENT
  EXTENT MANAGEMENT LOCAL; There's new syntax for the temporary tablespaces too.

6) Put these tablespace creation statements into a file and run them from sqlplus as sys as sysdba.

7) Now you're ready to invoke the full import: imp parfile=parfile.txt connecting as system. Where parfile.txt looks like

FULL=y
LOG=imp_5Nov2003_2.txt

show=n
ROWS=y
FILE=expdat.dmp

Feedback=1000

8) There will probably be some errors in the logfile. Put on your thinking cap and deal with them.

The above not an expert's opinion, just a recipe from somebody who just figured out how to do it. Treta it as such, comments, corrections, opinions welcomed.

Thanks to all who helped get me this far.

-- 
//-Walt
// 
//

Received on Fri Nov 07 2003 - 08:57:38 CST