Results 1 to 12 of 12
  1. #1
    Join Date
    Dec 2002
    Posts
    1,245

    Unanswered: Migrating DB from 10gR1 (ASM) to 10gR2 (non-ASM)

    Server 1:
    Windows 2000 (SP4)
    Oracle 10gR1 (10.1.0.2)
    ASM

    Server 2:
    Windows 2003
    Oracle 10gR2 (10.2.0.2.0)

    I am migrating from 10gR1 (with ASM) to 10gR2 (with no ASM).

    As I understand it, my options are:

    1. RMAN backup, then RMAN restore.
    2. Export database to file/import database to file.
    3. Import directly from database using linked server


    I'm struggling with each option because:

    1. With the RMAN backup, I am stuck renaming a bunch of files because we are migrating from ASM to non-ASM. Plus, I don't know what will happen when I restore the files to a non-ASM instance.

    2. With option 2, I keep hitting an error:
    Code:
    Job SGS_FILE_IMPORT has been reopened at Wednesday, 19 April, 2006 16:01 
    Restarting "SYSTEM"."SGS_FILE_IMPORT":  
    Processing object type SCHEMA_EXPORT/USER
    ORA-39126: Worker unexpected fatal error in KUPW$WORKER.LOAD_METADATA [SELECT process_order, flags, xml_clob, NVL(dump_fileid, :1), NVL(dump_position, :2), dump_length, dump_allocation, grantor, object_row, object_schema, object_long_name, processing_stat
    us, processing_state, base_object_type, base_object_schema, base_object_name, property, size_estimate, in_progress FROM "SYSTEM"."SGS_FILE_IMPORT" WHERE  process_order between :3 AND :4 AND processing_state <> :5 AND duplicate = 0 ORDER BY process_order] 
    
    ORA-39096: invalid input value 1 for parameter GET_FILE_INFO:fileNumber
    
    ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
    ORA-06512: at "SYS.KUPW$WORKER", line 6273
    
    ----- PL/SQL Call Stack -----
      object      line  object
      handle    number  name
    8A4D06D4     14916  package body SYS.KUPW$WORKER
    8A4D06D4      6300  package body SYS.KUPW$WORKER
    8A4D06D4      3514  package body SYS.KUPW$WORKER
    8A4D06D4      6889  packag
    e body SYS.KUPW$WORKER
    8A4D06D4      1262  package body SYS.KUPW$WORKER
    872504EC         2  anonymous block
    
    Job "SYSTEM"."SGS_FILE_IMPORT" stopped due to fatal error at 16:01:40
    I suspect that the error is related to long columns in two of my tables, but I am not certain.

    3. With option 3, the import process bombs on two of the tables specifically because of two long columns in two tables. Those tables (and only those tables) fail the import.

    What other options/alternatives am I missing?

    Regards,

    hmscott
    Have you hugged your backup today?

  2. #2
    Join Date
    Dec 2003
    Posts
    1,074
    Is that using IMPORT DATAPUMP or just IMPORT? You might try datapump. Also, you could also try just importing those schemas that you created, and let the SYSTEM schema update itself as necessary during the import of those objects.

    -cf

  3. #3
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by chuck_forbes
    Is that using IMPORT DATAPUMP or just IMPORT? You might try datapump. Also, you could also try just importing those schemas that you created, and let the SYSTEM schema update itself as necessary during the import of those objects.

    -cf
    PM inbound.

    Regards,

    hmscott
    Have you hugged your backup today?

  4. #4
    Join Date
    Dec 2002
    Posts
    1,245
    Update:

    With option 2, I only get the error if I am using multiple files and parallelism. I am using 10gr2 Enterprise. Is there something I missed (ie, do the files have to be sequenced on import properly?)?

    Regards,

    hmscott
    Have you hugged your backup today?

  5. #5
    Join Date
    Dec 2003
    Posts
    1,074
    I was just suggesting that you pinpoint the schemas that you created, and import those only. We've had problems in the past moving from 10gR1 to 10gR2 trying to push a FULL IMPORT over to the newer version. We haven't figured out what the problem was (everything just froze), but we were able to move single schemas over at a time with no problem.

    I was mainly focusing on that suggestion because the item noted in your error message (KUPW$WORKER) is in the SYS schema, and I couldn't tell if 1) the error occurred while it was trying to execute that in 10gR2, or 2) import it from 10gR1. By only importing your application schemas, you would avoid the problem if it were #2.

    -Chuck

  6. #6
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by chuck_forbes
    I was just suggesting that you pinpoint the schemas that you created, and import those only. We've had problems in the past moving from 10gR1 to 10gR2 trying to push a FULL IMPORT over to the newer version. We haven't figured out what the problem was (everything just froze), but we were able to move single schemas over at a time with no problem.

    I was mainly focusing on that suggestion because the item noted in your error message (KUPW$WORKER) is in the SYS schema, and I couldn't tell if 1) the error occurred while it was trying to execute that in 10gR2, or 2) import it from 10gR1. By only importing your application schemas, you would avoid the problem if it were #2.

    -Chuck
    I'm only migrating one schema; it includes all user data and no system tables or data. The error in number 2 only occurs if I try to use parallel processing with multiple files.

    Thanks,

    hmscott
    Have you hugged your backup today?

  7. #7
    Join Date
    Dec 2003
    Posts
    1,074
    Can you avoid parallelism then? I've never used an option like that during import, so I can't help you troubleshoot there.

    Or could you rerun the export from the 10gR1 database, so that it creates a single dmp file, and then import that?

    -cf

  8. #8
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by chuck_forbes
    Can you avoid parallelism then? I've never used an option like that during import, so I can't help you troubleshoot there.

    Or could you rerun the export from the 10gR1 database, so that it creates a single dmp file, and then import that?

    -cf
    That's what I'm having to do. I have to say I'm not real happy about about it. Using parallelism, I can cut down the export process by 13 minutes (out of one hour) for a 7 GB schema. In production when I have to do this, I will only have 4 hours to complete the conversion process (export, copy, import) and the database is 18 GB. I was really hoping that parallelism would give me a little bit of extra cushion.

    Regards,

    hmscott
    Have you hugged your backup today?

  9. #9
    Join Date
    Dec 2003
    Posts
    1,074
    You can try posting this question in the Oracle Technology Forums, since no one here has been able to solve your problem (it'll require a login if you don't have one already, but it's free):

    http://forums.oracle.com/forums/forum.jspa?forumID=61

    and there's always METALINK. Since you're dealing with 10gR2, it could be that you're the first one who's taken the effort to post a problem there.

    -Chuck

  10. #10
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by chuck_forbes
    and there's always METALINK. Since you're dealing with 10gR2, it could be that you're the first one who's taken the effort to post a problem there.

    -Chuck
    Metalink it is going to be. I have about a week before I have to do the migration in production.

    Thanks for helping.

    Regards,

    hmscott
    Have you hugged your backup today?

  11. #11
    Join Date
    Jan 2004
    Posts
    99
    why don't you use datapump to export the schema, and use datapump to import to your new database release.....this is 60x faster than conventional exp/imp.

  12. #12
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by zaki_mtk
    why don't you use datapump to export the schema, and use datapump to import to your new database release.....this is 60x faster than conventional exp/imp.
    I think actually, that I am using the data pump. At least, that's what I'm using if that's what gets executed from the web console (Maintenance | Export Data to File).

    Regards,

    hmscott
    Have you hugged your backup today?

Posting Permissions

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