Errors while trying to import a Full Database export
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Errors while trying to import a Full Database export

Hybrid View

  1. #1
    Join Date
    Sep 2000
    Posts
    362

    Unhappy

    Hi,
    I have a full database export from an Oracle 8.0.5 database which i am trying to import into an Oracle 8.1.7 database.

    I have created the database and all the tablespaces in Oracle 8i. (The tablespaces and datafiles in 8i are not in the same location as the ones in Oracle 8.0.5).

    Now when i am trying to do a full import using

    imp system/manager@news8i file=expdat.dmp full=y ignore=y

    I get the following errors

    IMP-00003: ORACLE error 1918 encountered
    ORA-01918: user 'SVEN' does not exist
    IMP-00017: following statement failed with ORACLE error 1918:
    "ALTER USER "MTARNEY" QUOTA UNLIMITED ON "TEMPORARY" QUOTA UNLIMITED ON "NEW"
    "S_DATA""
    IMP-00003: ORACLE error 1918 encountered
    ORA-01918: user 'MTARNEY' does not exist
    IMP-00017: following statement failed with ORACLE error 1918:
    "ALTER USER "LUCY" QUOTA UNLIMITED ON "NEWS_INDEX" QUOTA UNLIMITED ON "NEWS_"
    "DATA""
    IMP-00003: ORACLE error 1918 encountered
    ORA-01918: user 'LUCY' does not exist
    IMP-00017: following statement failed with ORACLE error 2221:
    "CREATE PUBLIC ROLLBACK SEGMENT RB0 STORAGE (INITIAL 57344 NEXT 81920 MINEXT"
    "ENTS 2 MAXEXTENTS 2147483645) TABLESPACE "RBS""
    IMP-00003: ORACLE error 2221 encountered
    ORA-02221: invalid MAXEXTENTS storage option value
    IMP-00017: following statement failed with ORACLE error 2221:
    "CREATE PUBLIC ROLLBACK SEGMENT RB1 STORAGE (INITIAL 57344 NEXT 81920 MINEXT"
    "ENTS 2 MAXEXTENTS 2147483645) TABLESPACE "RBS""
    IMP-00003: ORACLE error 2221 encountered
    ORA-02221: invalid MAXEXTENTS storage option value
    . importing NEWS_OWNER's objects into NEWS_OWNER
    IMP-00017: following statement failed with ORACLE error 1435:
    "ALTER SCHEMA = "NEWS_OWNER""
    IMP-00003: ORACLE error 1435 encountered
    ORA-01435: user does not exist
    . importing NEWS_OWNER's objects into NEWS_OWNER
    IMP-00017: following statement failed with ORACLE error 1435:
    "ALTER SCHEMA = "NEWS_OWNER""
    IMP-00003: ORACLE error 1435 encountered
    ORA-01435: user does not exist
    . importing NEWS_OWNER's objects into NEWS_OWNER
    IMP-00017: following statement failed with ORACLE error 1435:
    "ALTER SCHEMA = "NEWS_OWNER""
    IMP-00003: ORACLE error 1435 encountered
    ORA-01435: user does not exist
    . importing NEWS_OWNER's objects into NEWS_OWNER
    IMP-00017: following statement failed with ORACLE error 1435:

    and a lot more.

    The export was done using the command
    exp80 system/manager@news full=y

    Please suggest.

    Thanks
    Anurag
    Appreciation is a wonderful thing;
    It makes what is excellent in others belong to us as well.


  2. #2
    Join Date
    Feb 2001
    Location
    Paris, France
    Posts
    809
    suggestion : create your users first in your new instance

  3. #3
    Join Date
    Feb 2001
    Location
    Bombay,India
    Posts
    530
    Hi,
    The solution to the problem is first create the users in the new database as they were there in old database.Then create the tablespaces as the same names as they were in old database.Then import the dump file and go by user by user basis.It will be simple and more errorfree.

    e.g
    imp sys file=export.dmp log=a.log fromuser=a touser=a rows=y indexes=y grants=y constraints=y

    The above command will import the tables assocaited with user a into user a.Perform the above steps with all the users.
    Dont perform a FULL import of the dump file into the database.
    Perform user-by-user import of the dump file.

    In case of any help please be free to ask me at rohitsn@altavista.com

    Regards,
    Rohit Nirkhe,Oracle DBA,OCP 8i
    rohitsn@altavista.com

  4. #4
    Join Date
    Sep 2000
    Posts
    362
    Hi,
    What is the disadvantage of doing a full import.

    Thanks
    Anurag
    Appreciation is a wonderful thing;
    It makes what is excellent in others belong to us as well.


  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    well full will try to import everything from other database, even tablespaces. I say try because if for example the paths are not the same in both installations you start getting all sort of error (like your message basically )

    If you want to import everything you need to have a clean fresh database then you have to adapt the paths to your old database so when import is creating tablespaces you wont get errors

    OR

    you create the tablespaces first and the users then you use ignore=y in your import

  6. #6
    Join Date
    Sep 2000
    Posts
    362

    Exclamation Please Confirm whether I should ingnore these errors or take some corrective action

    Hi,
    I have created the database with the exact same tablespace names (their location might not be same), rollback segments and users.

    Now when trying to do a full import I get a lot of errors as below. Marked in bold. I tried searching for information on whether I should ignore them or take some corrective action but could not find any.

    Also why is the import trying to import system objects. It should not.

    If somebody can please guide me on this I will be very thankful.

    Thanks
    Anurag

    Connected to: Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
    With the Partitioning option
    JServer Release 8.1.7.0.0 - Production

    Export file created by EXPORT:V08.00.05 via conventional path
    import done in WE8ISO8859P1 character set and WE8ISO8859P1 NCHAR character set

    IMP-00017: following statement failed with ORACLE error 2221:
    "CREATE PUBLIC ROLLBACK SEGMENT RB0 STORAGE (INITIAL 57344 NEXT 81920 MINEXT"
    "ENTS 2 MAXEXTENTS 2147483645) TABLESPACE "RBS""
    IMP-00003: ORACLE error 2221 encountered
    ORA-02221: invalid MAXEXTENTS storage option value
    IMP-00017: following statement failed with ORACLE error 2221:
    "CREATE PUBLIC ROLLBACK SEGMENT RB1 STORAGE (INITIAL 57344 NEXT 81920 MINEXT"
    "ENTS 2 MAXEXTENTS 2147483645) TABLESPACE "RBS""
    IMP-00003: ORACLE error 2221 encountered
    ORA-02221: invalid MAXEXTENTS storage option value

    . importing NEWS_OWNER's objects into NEWS_OWNER
    . importing DISC_OWNER's objects into DISC_OWNER
    . importing SYSTEM's objects into SYSTEM
    . . importing table "AUDIT_ACTIONS" 121 rows imported
    . . importing table "CHAINED_ROWS"
    Note: table contains ROWID column, values may be obsolete 296 rows imported
    . . importing table "DBMS_ALERT_INFO" 0 rows imported
    . . importing table "DBMS_LOCK_ALLOCATED" 0 rows imported
    . . importing table "DEF$_AQCALL" 0 rows imported
    . . importing table "DEF$_AQERROR" 0 rows imported
    . . importing table "DEF$_CALLDEST" 0 rows imported
    . . importing table "DEF$_DEFAULTDEST" 0 rows imported
    . . importing table "DEF$_DESTINATION" 0 rows imported
    . . importing table "DEF$_ERROR" 0 rows imported
    . . importing table "DEF$_LOB" 0 rows imported
    . . importing table "DEF$_ORIGIN" 0 rows imported
    . . importing table "DEF$_PROPAGATOR" 0 rows imported
    . . importing table "DEF$_TEMP$LOB" 0 rows imported
    . . importing table "LOB_STOR" 0 rows imported
    . . importing table "PEER_GROUP" 0 rows imported
    . . importing table "SQLPLUS_PRODUCT_PROFILE" 0 rows imported
    . . importing table "TEST_LOBS" 4 rows imported
    . importing NEWS_OWNER's objects into NEWS_OWNER
    . . importing table "API_OIL_STATISTICS" 287 rows imported
    . . importing table "CALENDAR" 0 rows imported
    . . importing table "CATEGORY" 23 rows imported
    . . importing table "COMMODITY" 18 rows imported
    . . importing table "COMMODITY_BKP" 18 rows imported
    . . importing table "COMMODITY_FUTURES_PRICES" 7357 rows imported
    . . importing table "COMPANY" 1438 rows imported
    . . importing table "CURRENCY" 21 rows imported
    . . importing table "CURRENCY_STOCK_CODE" 13 rows imported
    . . importing table "DAILY_COMMODITY_PRICES" 10 rows imported
    . . importing table "EARNINGS" 9080 rows imported
    . . importing table "EXCEPTIONS"
    Note: table contains ROWID column, values may be obsolete 0 rows imported
    . . importing table "EXCHANGE_RATE" 14884 rows imported
    . . importing table "FILINGS_13D" 692 rows imported
    . . importing table "GAS_STORAGE" 772 rows imported
    . . importing table "GAS_STRIPS" 29 rows imported
    . . importing table "HOH_NEWS_STORIES" 28 rows imported
    . . importing table "IMAGES" 0 rows imported
    . . importing table "INDEX_CONSTANTS" 1 rows imported
    . . importing table "INDEX_PRICES" 5 rows imported
    . . importing table "INDEX_TOTALS" 713 rows imported
    . . importing table "INSIDERS_CODE" 10 rows imported
    . . importing table "INSIDERS_TRANSACTIONS" 696 rows imported
    . . importing table "LG" 7 rows imported
    . . importing table "LG2" 0 rows imported
    . . importing table "LOCATION" 288 rows imported
    . . importing table "NEWS" 76240 rows imported
    . . importing table "NEWS_CATEGORIES" 69756 rows imported
    . . importing table "NEWS_COMPANIES" 61325 rows imported
    . . importing table "NEWS_FUNCTIONS" 69486 rows imported
    . . importing table "NEWS_LOCATIONS" 28230 rows imported
    . . importing table "PEER_GROUP" 29 rows imported
    . . importing table "PLAN_TABLE" 0 rows imported
    . . importing table "PRODUCT_NAME" 2 rows imported
    . . importing table "RIG_COUNT" 551 rows imported
    . . importing table "SMP_REP_EXEC_STAT" 1 rows imported
    . . importing table "SPOT_PRICE" 20207 rows imported
    . . importing table "STOCK_PRICE" 915806 rows imported
    . . importing table "STOCK_PRICE_CHANGE" 668 rows imported
    . . importing table "TEMP_VAR" 23 rows imported
    . . importing table "WEEKLY_COMPANY_STOCKS" 495 rows imported
    . importing DISC_OWNER's objects into DISC_OWNER
    . . importing table "DIS_DOCS_" 79 rows imported
    . . importing table "DIS_GRANTS_" 572 rows imported
    . . importing table "EUL_ACCESS_PRIVS" 49 rows imported
    . . importing table "EUL_APP_PARAMS" 15 rows imported
    . . importing table "EUL_BATCH_PARAMS" 0 rows imported
    . . importing table "EUL_BATCH_QUERIES" 0 rows imported
    . . importing table "EUL_BATCH_REPORTS" 0 rows imported
    . . importing table "EUL_BATCH_SHEETS" 0 rows imported
    . . importing table "EUL_BA_OBJ_LINKS" 57 rows imported
    . . importing table "EUL_BQ_DEPS" 0 rows imported
    . . importing table "EUL_BQ_TABLES" 0 rows imported
    . . importing table "EUL_BR_RUNS" 0 rows imported
    . . importing table "EUL_BUSINESS_AREAS" 2 rows imported
    . . importing table "EUL_DBH_NODES" 4 rows imported
    . . importing table "EUL_DOMAINS" 34 rows imported
    . . importing table "EUL_EUL_USERS" 20 rows imported
    . . importing table "EUL_EXPRESSIONS" 566 rows imported
    . . importing table "EUL_EXP_DEPS" 438 rows imported
    . . importing table "EUL_FREQ_UNITS" 6 rows imported
    . . importing table "EUL_FUNCTIONS" 129 rows imported
    . . importing table "EUL_FUN_ARGUMENTS" 203 rows imported
    . . importing table "EUL_FUN_CATEGORIES" 9 rows imported
    . . importing table "EUL_FUN_FC_LINKS" 120 rows imported
    . . importing table "EUL_GATEWAYS" 2 rows imported
    . . importing table "EUL_HIERARCHIES" 5 rows imported
    . . importing table "EUL_HI_NODES" 4 rows imported
    . . importing table "EUL_HI_SEGMENTS" 3 rows imported
    . . importing table "EUL_IG_EXP_LINKS" 57 rows imported
    . . importing table "EUL_JC_JOIN_LINKS" 0 rows imported
    . . importing table "EUL_JOIN_COMBS" 0 rows imported
    . . importing table "EUL_KEY_CONS" 53 rows imported
    . . importing table "EUL_NMSDO_JC_USGS" 0 rows imported
    . . importing table "EUL_OBJS" 61 rows imported
    . . importing table "EUL_OBJ_DEPS" 34 rows imported
    . . importing table "EUL_OBJ_JOIN_USGS" 17 rows imported
    . . importing table "EUL_PLAN_TABLE" 0 rows imported
    . . importing table "EUL_QPP_STATISTICS" 3750 rows imported
    . . importing table "EUL_SBO_DSGN_ELEMS" 0 rows imported
    . . importing table "EUL_SEGMENTS" 33 rows imported
    . . importing table "EUL_SEQUENCES" 1 rows imported
    . . importing table "EUL_SQ_CRRLTNS" 0 rows imported
    . . importing table "EUL_SUB_QUERIES" 0 rows imported
    . . importing table "EUL_SUMMARY_OBJS" 0 rows imported
    . . importing table "EUL_SUMO_ITEM_USGS" 0 rows imported
    . . importing table "EUL_SUM_BITMAPS" 0 rows imported
    . . importing table "EUL_SUM_RFRSH_SETS" 0 rows imported
    . . importing table "EUL_VERSIONS" 1 rows imported
    . importing SYSTEM's objects into SYSTEM
    . importing NEWS_OWNER's objects into NEWS_OWNER
    . importing DISC_OWNER's objects into DISC_OWNER
    . importing SYSTEM's objects into SYSTEM
    IMP-00017: following statement failed with ORACLE error 6550:
    "BEGIN SYS.DBMS_AQ_IMPORT_INTERNAL.AQ_TABLE_DEFN_UPDATE('SYSTEM','DEF$_AQC"
    "ALL',2,0,2,''); END;"
    IMP-00003: ORACLE error 6550 encountered
    ORA-06550: line 1, column 9:
    PLS-00306: wrong number or types of arguments in call to 'AQ_TABLE_DEFN_UPDATE'
    ORA-06550: line 1, column 9:
    PL/SQL: Statement ignored
    IMP-00017: following statement failed with ORACLE error 24021:
    "BEGIN SYS.DBMS_AQ_IMPORT_INTERNAL.AQ_DEFN_UPDATE(HEXTORAW('DCCB242B3EF011"
    "D3B25E009027718A59'),'SYSTEM','DEF$_AQCALL','AQ$_DEF$_AQCALL_E',1,0,0,0,0,'"
    "exception queue'); END;"
    IMP-00003: ORACLE error 24021 encountered
    ORA-24021: queue table definition not imported for AQ$_DEF$_AQCALL_E.
    ORA-06512: at "SYS.DBMS_AQ_IMPORT_INTERNAL", line 388
    ORA-06512: at line 1
    IMP-00017: following statement failed with ORACLE error 24021:
    "BEGIN SYS.DBMS_AQ_IMPORT_INTERNAL.AQ_DEFN_UPDATE(HEXTORAW('DCCB242C3EF011"
    "D3B25E009027718A59'),'SYSTEM','DEF$_AQCALL','DEF$_AQCALL',0,5,0,1,0,'Deferr"
    "ed RPC Queue'); END;"
    IMP-00003: ORACLE error 24021 encountered
    ORA-24021: queue table definition not imported for DEF$_AQCALL.
    ORA-06512: at "SYS.DBMS_AQ_IMPORT_INTERNAL", line 388
    ORA-06512: at line 1
    IMP-00017: following statement failed with ORACLE error 6550:
    "BEGIN SYS.DBMS_AQ_IMPORT_INTERNAL.AQ_TABLE_DEFN_UPDATE('SYSTEM','DEF$_AQE"
    "RROR',2,0,2,''); END;"
    IMP-00003: ORACLE error 6550 encountered
    ORA-06550: line 1, column 9:
    PLS-00306: wrong number or types of arguments in call to 'AQ_TABLE_DEFN_UPDATE'
    ORA-06550: line 1, column 9:
    PL/SQL: Statement ignored
    IMP-00017: following statement failed with ORACLE error 24021:
    "BEGIN SYS.DBMS_AQ_IMPORT_INTERNAL.AQ_DEFN_UPDATE(HEXTORAW('DCCB242D3EF011"
    "D3B25E009027718A59'),'SYSTEM','DEF$_AQERROR','AQ$_DEF$_AQERROR_E',1,0,0,0,0"
    ",'exception queue'); END;"
    IMP-00003: ORACLE error 24021 encountered
    ORA-24021: queue table definition not imported for AQ$_DEF$_AQERROR_E.
    ORA-06512: at "SYS.DBMS_AQ_IMPORT_INTERNAL", line 388
    ORA-06512: at line 1
    IMP-00017: following statement failed with ORACLE error 24021:
    "BEGIN SYS.DBMS_AQ_IMPORT_INTERNAL.AQ_DEFN_UPDATE(HEXTORAW('DCCB242E3EF011"
    "D3B25E009027718A59'),'SYSTEM','DEF$_AQERROR','DEF$_AQERROR',0,5,0,1,0,'Erro"
    "r Queue for Deferred RPCs'); END;"
    IMP-00003: ORACLE error 24021 encountered
    ORA-24021: queue table definition not imported for DEF$_AQERROR.
    ORA-06512: at "SYS.DBMS_AQ_IMPORT_INTERNAL", line 388
    ORA-06512: at line 1

    . importing DBSNMP's objects into DBSNMP
    . importing SYSTEM's objects into SYSTEM
    . importing DISC_OWNER's objects into DISC_OWNER
    . importing NEWS_OWNER's objects into NEWS_OWNER
    . importing SYSTEM's objects into SYSTEM
    . importing DISC_OWNER's objects into DISC_OWNER
    . importing NEWS_OWNER's objects into NEWS_OWNER
    . importing SYSTEM's objects into SYSTEM
    . importing NEWS_OWNER's objects into NEWS_OWNER
    . importing MTARNEY's objects into MTARNEY
    . importing NEWS_OWNER's objects into NEWS_OWNER
    . importing SYSTEM's objects into SYSTEM
    . importing NEWS_OWNER's objects into NEWS_OWNER
    . importing SYSTEM's objects into SYSTEM
    . importing NEWS_OWNER's objects into NEWS_OWNER
    . importing DISC_OWNER's objects into DISC_OWNER
    . importing NEWS_OWNER's objects into NEWS_OWNER
    . importing SYSTEM's objects into SYSTEM
    About to enable constraints...
    . importing NEWS_OWNER's objects into NEWS_OWNER
    . importing DISC_OWNER's objects into DISC_OWNER
    . importing SYSTEM's objects into SYSTEM
    . importing NEWS_OWNER's objects into NEWS_OWNER
    . importing DISC_OWNER's objects into DISC_OWNER
    Import terminated successfully with warnings.


    [Edited by anuragmin on 06-29-2001 at 10:29 AM]
    Appreciation is a wonderful thing;
    It makes what is excellent in others belong to us as well.


  7. #7
    Join Date
    Sep 2000
    Posts
    362
    Hi All,
    Still waiting eagerly for a response on this.

    Thanks
    Anurag
    Appreciation is a wonderful thing;
    It makes what is excellent in others belong to us as well.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width