Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 776
  • Last Modified:

Import - Am I doing something wrong?

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
hartleys
Asked:
hartleys
  • 2
  • 2
  • 2
  • +4
1 Solution
 
DrJekyllCommented:
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
 
iozturkCommented:
I think its better to create the tablespaces with new version and then import the users you want.
0
 
hartleysAuthor Commented:
Would you also advise I create the rollback segments before I do the import or is this a bit excessive?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Mark GeerlingsDatabase AdministratorCommented:
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
 
iozturkCommented:
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
 
konektorCommented:
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
 
Mark GeerlingsDatabase AdministratorCommented:
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
 
saxena_mohitCommented:
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
 
jim144Commented:
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
 
hartleysAuthor Commented:
Thanks very much to everyone for thier help.
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 2
  • 2
  • 2
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now