8 Replies Latest reply on May 1, 2013 8:02 PM by Srini Chavali-Oracle

    9i to 10g

      Brief background
      Upgrading one of the 9i database to 11g that supports a 3rd party software - ***Vendor provided an over-simplified documentations*** and recommends moving from 9i to 10g before going to 11g. A few changes from 9i to 10g.
      1) db_block_size
      2) character sets

      Anyway, created the database DBUPGTEST on (ultimately moving to 11gR2, so no point patching to, is there?) with all the parameter changes. At this point, these are the 2 db in play:

      Current production db: Oracle 9i - PROD dbname => 2048K db block size
      Current migrating db to: Oracle10g - DBUPGTEST dbname => 8192k db block size

      According to vendor notes / documentation,
      1) create db
      2) exp full from 9i
      3) imp full to 10g

      1) import ended with completed unsuccessful.
      2) user accounts are imported (because their default tablespace is USERS - which had already been created during DB creation); but, user accounts (schema accounts) with a different default tablespace are not imported.
      Looking at the imp.log - seems like it's complaining about the db_block_size during tablespace creation - which explains why the schema accounts are not imported; because the tablespace was not created.

      My questions
      1) How do I import to 10g? Can I create all the tablespace in 10g first? Then import? Will it crap out because it already exists? Or will it import the objects in the schema?

      2) How do I refresh data from PROD? Remember this is 9i and most of the expdp functionalities are not available. And I cannot re-exp and re-imp because there are steps (sql to run) after moving to 10g to fix some software upgrade table mappings. If I re-exp from 9i and re-imp to 10g, won't I have to re-run all those steps before the apps will run?

      Thanks in advance.
        • 1. Re: 9i to 10g
          Zoltan Kecskemethy
          First I hope your source has the latest patchset aka v or so.
          Yes use the latest 10g ( as it is required to upgrade to 11g.

          re 1. yes, create tbs and add in IGNORE=Y as import parameter

          re 2. you may need to refresh data manually. create db link and merge into 10g changes. but I may not fully understand this question.

          Be aware of NLS_LANG when you export out and import in for the character set conversion.
          Also you may end up much bigger database at the end so setup auto increment for your tbs and check available space.

          Edited by: Zoltan Kecskemethy on Apr 30, 2013 10:18 PM

          [url http://docs.oracle.com/cd/B19306_01/server.102/b14238/expimp.htm#BABJHBEJ]Hope you aware that it is important which version of export and import to use

          Edited by: Zoltan Kecskemethy on Apr 30, 2013 10:32 PM
          added in exp-imp version info link from upgrade guide
          • 2. Re: 9i to 10g
            Srini Chavali-Oracle
            Pl always post 4 digit versions (e.g. or of Oracle - "9i" and "10g" are meaningless marketing labels - along with your exact OS versions.

            You can directly upgrade from "9i" (or any other version) to 11gR2 using export/import - there is no requirement to make a "pit-stop" at 10gR2.

            Pl also post your exact export and import commands used, along with the first 20 lines of the export and import logs.

            Posting the exact error messages and codes also helps. If the tablespaces do not exist on the target, then import will try to create them on the same exact directory path as the source. If the tablespace creation fails, then expect many downstream errors (creation of users will fail since tablespace does not exist, creation of objects will fail since users do not exist etc).

            You can pre-create the tablespaces as needed, and ignore the CREATE TABLESPACE errors that the import process will report.

            • 3. Re: 9i to 10g
              Yes, it is
              Really? Can't expdp from and impdp to 11.2.x.x?? Can you please point me to the possible issues from to 11.2.x.x??

              Thanks for the heads up and cautions. Appreciate it. I'll try it tomorrow.

              Sorry. I'm glad to report it is

              "pit-stop" requirements at 10gR2 is a requirement of the vendor ... we kinda need to play by their rules for their software piece support.

              I'll try to run it again tomorrow and post the logs. Unfortunately, it's time to put on the home life and responsibilities hat now.

              Thanks to both for now.
              • 4. Re: 9i to 10g
                Richard Harrison .
                If I was doing this I would just upgrade the database from 9i to 10g using manual upgrade or dbua. Then data pump from this over a network link to the new 11g database. This should give you the quickest upgrade method and should meet all of your requirements?

                • 5. Re: 9i to 10g
                  refer below notes:

                  Master Note For Oracle Database Upgrades and Migrations [ID 1152016.1]
                  • 6. Re: 9i to 10g
                    That was my plan going from to until I was told I needed to patch to
                    At this point, I'm trying to move from to and upgrade the applications and apply fixes / changes from the vendor on Once everything is done, I'll worry about upgrading / to That's not to say, I don't appreciate your comments. Thank you for your opinion. I appreciate it.

                    To all,
                    Creating the tablespace first and importing using ignore=y seems to be working. It's still running, but, it does shows it's importing tables into the environment. So, thank you so much.
                    • 7. Re: 9i to 10g
                      Thanks for the guide. I'll definitely look at it when I get to that point.
                      • 8. Re: 9i to 10g
                        Srini Chavali-Oracle
                        Does the plan to upgrade from 10gR2 to 11gR2 also involve export/import ? If so, there is no need to apply any patchset on top of the patchset (or higher) is only required if you plan to upgrade the 10gR2 database to 11gR2 by either using DBUA or upgrade scripts