Re: Interesting (painful) Migration Problem (Nov 7.2.2.4.10 -> NT 7.3.3.0.0)

Hi Nassif,

Looks like it is time to reevaluate your migration strategy.

The last time I migrated anything on an NT I did the following.

1. Run export with the options that do nothing more than produce the DDL.
2. Create a script to select * from each of the existing tables.
3. Create a description file and then use your editor to create SQL*loader control files for each of the tables being migrated.
4. Edit the exp/imp file to reflect the new tablespaces.
5. Run the script to select all of the data from the original database.
6. Create the new database with the appropriate tablespaces and datafiles.
7. Run SQL*Loader and populate the database.
8. Viola!

This isn't nearly as difficult as you might think. Step by step here is what to do.

1. Run exp using a parameter file with options grants=Y, indexes=Y, rows=N, constraints=Y, log=Y, full, file=<file_name>
2. To create a script is a two step operation, first you create a list of tables and then you edit a copy of it to convert each line into a SELECT * FROM table_name statement.

to create it you want the following for each table:

SPOOL <table_name>.sql
SELECT owner||'.'||table_name FROM ALL_TABLES; SPOOL OFF If you are fortunate to have vi/vim/elvis available the following command will build the selects:

    :%s/.*/SPOOL '&.sql'^MSELECT * FROM &;^MSPOOL OFF/

3. To create the description file edit another copy of the file created in step 2. using vi etc:

        :%s/.*/SELECT '&' FROM DUAL^M\/^M DESC &/

Now copy the script in order to provide the basis for your control file and edit it to create one control file for each table. For whatever it is worth my control files look like:

	LOAD DATA
	INFILE <file_name>
	BADFILE 'extents.bad'
	INSERT INTO TABLE <table_name>
	FIELDS TERMINATED BY '|'
	TRAILING NULLCOLS
	(
		column_one,
		column_two,
		.
		.
		.
		column n)

4. Back to the trusty editor to do any reorganization that you want by modifying the exp file

5. Getting the data out is a simple two step process:

1. set colsep |
2. Open an msdos command window and run the command SQLPLUS <userid>/<password> start <script_name_from_step_2>
3. Use the DDL you edited in step 4 to create the new database
4. Load each of the tables using SQL*Loader from the MS/DOS window using the command

        SQLLDR <user_name>/<password> control=<table_name>.ctl for each table. Needless to say you
should build a script to do this.

Regards

Jerry         

nassif_at_mndsprin6.c0m wrote:
>
> Helmut Hahn <Helmut.Hahn_at_bitoek.uni-bayreuth.de> wrote:
>
> >nassif_at_mndsprng.cm wrote:
> >> hi,
> >>
> >> We've got an interesting and painful migration in progress.
 <Sniperoo>
> >> Problems: The import program will try to create sys volumes instead
> >> of lettered drives, so we can't just import it (plus we need to fix
> >> some structural issues....)
> >
> >You could edit your dump file (with TextPad) and alter the datafile
> >locations to full Pathnames (D:\ORANT\DATABASE\INSTSYSTEM01.ORA). Thus
> >your files are created where you want.
>
> I tried to edit that 1.6 Gigabyte dump file with TextPad,
> WordPad andNotePad.
>
> TextPad wouldn't recognize it at text and wouldn't
> open it...
>
> WordPad and NotePad, after running the system out of
> virtual memory (even after i tried to bump the pagefile to
> 1.5 G on top of the 568M of chip-ram) produced no better
> results...
>
> Are there any other editors out there that can handle that
> kind of file (on NT, mind you)?
>
> Anything other techniques I should try with Word- Text- or Note- pad?
>
> >Helmut
>
> Thanks!
> Nassif!!
 

-- 
Jerry Gitomer         	ICT Group
jgitomer_at_ictgroup.com	Langhorne PA
jgitomer_at_p3.net 	Opinions are mine not those of ICT Group