?
Solved

Import - Am I doing something wrong?

Posted on 2003-03-05
10
Medium Priority
?
774 Views
Last Modified: 2012-05-04
I have exported a database from an instance on 7.3.4 and moved the export file on to the new server, running version 8.1.6. I have created the database using the following syntax:
____________________________________________________
CREATE DATABASE "PROD"
    MAXLOGFILES 32
    MAXLOGMEMBERS 2
    MAXDATAFILES 512
    MAXINSTANCES 8
    MAXLOGHISTORY 800
LOGFILE
  GROUP 1 (
    '/u01/oradata/PROD/redoPROD01a.log',
    '/u02/oradata/PROD/redoPROD01b.log'
  ) SIZE 60M,
  GROUP 2 (
    '/u02/oradata/PROD/redoPROD02a.log',
    '/u03/oradata/PROD/redoPROD02b.log'
  ) SIZE 60M,
  GROUP 3 (
    '/u03/oradata/PROD/redoPROD03a.log',
    '/u01/oradata/PROD/redoPROD03b.log'
  ) SIZE 60M
DATAFILE
        '/u04/oradata/PROD/system01.dbf' size 629145600 autoextend on maxsize unlimited;
shutdown immediate
startup
__________________________________________________________
This seems to work fine, but when I run the $ORACLE_HOME/rdbms/admin/catalog script prior to running the import, I messages similar to the following - this is only a small part of the messages I am receiving:
_______________________________________________________
Statement processed.
drop public synonym USER_MVIEW_JOINS
                    *
ORA-01432: public synonym to be dropped does not exist
Statement processed.
Statement processed.
Statement processed.
Statement processed.
Statement processed.
Statement processed.
Statement processed.
Statement processed.
Statement processed.
drop public synonym ALL_REFRESH_DEPENDENCIES
                    *
ORA-01432: public synonym to be dropped does not exist
Statement processed.
Statement processed.
drop role recovery_catalog_owner
          *
ORA-01919: role 'RECOVERY_CATALOG_OWNER' does not exist
Statement processed.
Statement processed.
Statement processed.
SVRMGR>
____________________________________________________
This is the start of the import:
_______________________________________
SID=PROD>imp system/manager parfile=PROD.import.par
 
Import: Release 8.1.6.0.0 - Production on Wed Mar 5 11:35:49 2003
 
(c) Copyright 1999 Oracle Corporation.  All rights reserved.
 
 
Connected to: Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
With the Partitioning option
JServer Release 8.1.6.0.0 - Production
 
Export file created by EXPORT:V07.03.04 via conventional path
IMP-00017: following statement failed with ORACLE error 2237:
 "CREATE TABLESPACE "RBS" DATAFILE  '/u05/oradata/PROD/rbs01.dbf' SIZE 419430"
 "4000       DEFAULT STORAGE (INITIAL 2097152 NEXT 2097152 MINEXTENTS 1 MAXEX"
 "TENTS 4000 PCTINCREASE 0) ONLINE PERMANENT"
IMP-00003: ORACLE error 2237 encountered
ORA-02237: invalid file size
IMP-00017: following statement failed with ORACLE error 2237:
 "CREATE TABLESPACE "TEMP" DATAFILE  '/u05/oradata/PROD/temp01.dbf' SIZE 4194"
 "304000       DEFAULT STORAGE (INITIAL 2097152 NEXT 2097152 MINEXTENTS 1 MAX"
 "EXTENTS 500 PCTINCREASE 0) ONLINE TEMPORARY"
IMP-00003: ORACLE error 2237 encountered
ORA-02237: invalid file size
________________________________________________
What have I done wrong?
0
Comment
Question by:hartleys
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +4
10 Comments
 
LVL 5

Accepted Solution

by:
DrJekyll earned 400 total points
ID: 8071205
It appears you have hit a bug. Recommended solution is to pre-create tablespaces first then import. See Oracle Note
below

Problem Description:  
====================  
During a full database import, a CREATE TABLESPACE statement fails with an  ORA-2237.  The datafiles are of valid size for this operating system.    

Possible Errors:  
================  
IMP-17  the following statement failed with ORACLE error 2237  IMP-3  ORACLE error 2237 encountered  ORA-2237  invalid file size  
// *Cause: An integer file size does not follow SIZE in a LOGFILE or  
// DATAFILE file list.  
// *Action: Specify an appropriate file size after SIZE.    

Scenario:  
=========  
CREATE TABLESPACE statements work from the command line, but fail on import.  
WARNINGS:  
=========  
None.    

Solution:  
=========  
Precreate the tablespace manually.  To get the size of the datafile, do the  following on the database from which the export was taken:       SELECT TABLESPACE_NAME, FILE_NAME, BYTES FROM DBA_DATA_FILES;  If the source database no longer exists, do the following to get the create  statements:      
1) imp user/password full=y rows=n show=y log=<logname>  
2) The first few lines in the generated logfile will          contain the create tablespace statements. This is a known bug (226644), that has not yet been fixed.  It should only  occur for larger datafiles.
0
 
LVL 4

Expert Comment

by:iozturk
ID: 8071369
I think its better to create the tablespaces with new version and then import the users you want.
0
 

Author Comment

by:hartleys
ID: 8071796
Would you also advise I create the rollback segments before I do the import or is this a bit excessive?
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 8071930
Those messages from the "catalog" script are typical.  It has a number of "drop..." statements in case it is rerun on an existing instance, but in a new database those objects aren't there so these "errors" appear.

You shouldn't need to recreate most rollback segments manually, but you may want to make sure there is at least one outside of the SYSTEM tablespace.  Also, if most (or all) of the tables are in one schema, you may want to create that user/schema manually as well before running the full import.  (This isn't supposed to be necessary, but I've needed to do it occasionally when I rebuilt a database from a full import.)
0
 
LVL 4

Expert Comment

by:iozturk
ID: 8072166
I mean a full functional db with rollback segments, but its not important if they are the same as 7.3.4 as markgear says its outside of system tbs.
You may create database by Database configuration assistant. And importing user by user is like divide-conquer:)
0
 
LVL 9

Expert Comment

by:konektor
ID: 8072456
the OS block size can be a problem. if u had 2K OS block size on server with source DB, u could have datafile eg. 1M+2K = 1050624, but u canot store such datafile on server, where least OS block size is 4K u can store only 1M (1048576) or 1M+4k (1052672).
u can copy create statements from dump file, paste into text file and modify it correcting all sizes to multiple of OS block size
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 8072506
If you create the tablespaces manually then you will have no problem with the O/S block size.  So, this is just another reason to create the tablespaces manually before running import.
0
 
LVL 1

Expert Comment

by:saxena_mohit
ID: 8073333
I would suggest you that create a temp file manually and set the parameter ignore=y in your par file while importing back the data.
Thanks
Mohit
0
 
LVL 1

Expert Comment

by:jim144
ID: 8083383
You might want to look at the OS's maximum file size, as the errors you are getting (I only have what you put int this question to go by) are for datafiles that you are creating at 4GB in size. Some OS's have a limit on the maximum file size. Alot of OS's do not but some do .....
0
 

Author Comment

by:hartleys
ID: 8086896
Thanks very much to everyone for thier help.
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to recover a database from a user managed backup
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question