Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
General
cancel
Showing results for 
Search instead for 
Did you mean: 

Oracle Migration (8.1.7 --> 9.2.0)

SOLVED
Go to solution

Oracle Migration (8.1.7 --> 9.2.0)

Hi all,

I have HP-UX 9000 Series C8000 workstation loaded with 11.11i as OS level.

I am in process of migration of oracle from 8i to 9i, currently I have installed Oracle 9.2.0 parallel to Oracle 8i. The oracle 9i installation went through fine and also created a database "new". Now i want to replicate the data in 8i database "old" to oracle 9i database "new".

I am using DATABASE UPGRADE ASSISTANT (DBUA) utility to migrate the data, when i execute the dbua script, I am getting a interface in which I could see only the database created in oracle 9i i.e "new", but i want to upgrade the oracle 8i database "old",. which is not getting listed in dbua interface. Please help.

Any valuable suggestions or ideas regarding migration will be helpfull to me.

Thanks in advance.

Regards,
Yajuvendra Singh

11 REPLIES
spex
Honored Contributor
Solution

Re: Oracle Migration (8.1.7 --> 9.2.0)

Hi Yajuvendra,

I've done the 8i -> 9i migration more than a few times, and have always gone the exp/imp route. First get your 9i instance configured properly (SGA, parameters, users, schemas, datafiles, tablespaces, etc.). Then...

Under Oracle 8i:

# nohup exp un/pw@sid file=exp_sid.dmp log=exp_sid.log owner=your_schema direct=Y &

Under Oracle 9i:

# nohup imp un/pw file=exp_sid.dmp log=imp_sid.log fromuser=your_schema touser=your_new_schema buffer=400000 commit=Y &

Afterwards, you will have to clean up tables, views, LOBs, db_links, synonyms, functions, procedures, packages, and the like. The import log is an invaluable resource for doing this.

There are several ways to speed the process up, including importing indexes separately (indexes=n). I recommend consulting the documentation for exp and imp.

PCS


TwoProc
Honored Contributor

Re: Oracle Migration (8.1.7 --> 9.2.0)

I'm with spex on this. Using Imp/Exp is the best approach to doing this, and you'll like what you're left with afterwards much better.
We are the people our parents warned us about --Jimmy Buffett
Steven E. Protter
Exalted Contributor

Re: Oracle Migration (8.1.7 --> 9.2.0)

Shalom,

I don't believe the assistant can do the upgrade.

You may go export/import or go through the migration document oracle keeps on metalink for this purpose.

The document seems complex but if followed precisely is not so bad.

Be sure that OS and kernel requirements are met on the system before beginning and have a good cold backup.

SEP
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com

Re: Oracle Migration (8.1.7 --> 9.2.0)

Dear all,

I am greatfull to all your views, suggestions. I am in need of one clarification further, actually I used exp/imp option also, but when I exp in oracle 8i the xxx.dmp file created is very small; hardly 500 MB, but actually my database is of 2 GB. The same I could import in oracle 9i successfully without any errors, but my PLM application fails fetch the data.

I will be greatful provided with string/command to export the complete database and import the same in oracle 9i.

Regards,
Yajuvendra Singh
spex
Honored Contributor

Re: Oracle Migration (8.1.7 --> 9.2.0)

Yajuvendra,

It is perfectly normal for the data dump to be significantly smaller than the overall size of the datafiles it came from. In fact, in the last export I performed, the dump was 38% of the size of the datafiles. The actual reduction depends on many factors, including how large your indexes are, fragmentation of your datafiles, and unused extents in your datafiles.

If you completed an export and import without incident, but your application cannot read the data, the problem most likely lies in misconfiguration of the database or in your application itself.

Are you sure the data are there? Can you "SELECT..." the data via SQL*Plus? Is the instance configured as your application expects? Is it named properly? Does your application's Oracle user have connect and select roles assigned? Correct credentials?

As for command syntax of exp and imp, see my posting above.

PCS
TwoProc
Honored Contributor

Re: Oracle Migration (8.1.7 --> 9.2.0)

Exports are usually smaller, so count your blessings that the export is so small - it's easier to move that way! If you look at amount of used space in a tablespace, it's usual state is not full, if you're doing your job and making sure that the tables don't get full and stop (or spend the whole day autoextending if you've turned that on).
We are the people our parents warned us about --Jimmy Buffett
Volker Borowski
Honored Contributor

Re: Oracle Migration (8.1.7 --> 9.2.0)

Hi,

to migrate with dbua, your "oratab" file needs a correct entry for your source database. If there is no entry with your old SID, it will not be selected as an upgradeable database.

However, if your DB is only 2 GB,
export+import should be the weapon of choice for the migration, because it leaves the entire source environment completely untouched, so if anything fails, fallback is easy.

Your export might be too small, because you are exporting the wrong user ?!?

Volker
Yogeeraj_1
Honored Contributor

Re: Oracle Migration (8.1.7 --> 9.2.0)

hi Yajuvendra,

I also agree with experts above. EXP/IMP should work.

I would nevertheless recommend that you migrate to oracle 10g instead.

Here are the steps:
1. Identify all the schema objects that are in use. (I would not recommend that just do a full exp/imp)

2. Create all the schema objects on the new database (assuming you already have created the tablespaces, users, etc). You may use exp with rows=n option and import it in the new database.

3. Export the schema objects data and import them in the new database

If your application is not able to connect to the new database, it may be because of some configuration problem. Please verify your listener settings and aliases in the tnsnames.ora

hope this helps!

kind regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Steven E. Protter
Exalted Contributor

Re: Oracle Migration (8.1.7 --> 9.2.0)

I don't see a problem with the export results.

Please see the process through, bring the database up in 9.2.x Oracle and see if your applications, data and tables still work.

SEP
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
Yogeeraj_1
Honored Contributor

Re: Oracle Migration (8.1.7 --> 9.2.0)

hi again,

also try to connect to the database using sqlplus just to check that you are able to connection to the database.

Any error messages that you are getting at the application level?

kind regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)

Re: Oracle Migration (8.1.7 --> 9.2.0)

Hi all,

Thanks for those valuable inputs resolving the issue, I exporting the dump and imported the same using command strings, also I could successfully upgrade the database using dbua utility.

Regards,
Yajuvendra Singh