topic Re: Oracle Migration (8.1.7 --> 9.2.0) in Operating System - HP-UX

Oracle Migration (8.1.7 --> 9.2.0)

Yajuvendra Singh — Tue, 22 Aug 2006 10:58:17 GMT

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

Re: Oracle Migration (8.1.7 --> 9.2.0)

spex — Tue, 22 Aug 2006 11:17:29 GMT

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

Re: Oracle Migration (8.1.7 --> 9.2.0)

TwoProc — Tue, 22 Aug 2006 11:31:26 GMT

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.

Re: Oracle Migration (8.1.7 --> 9.2.0)

Steven E. Protter — Tue, 22 Aug 2006 11:45:10 GMT

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

Re: Oracle Migration (8.1.7 --> 9.2.0)

Yajuvendra Singh — Tue, 22 Aug 2006 12:05:53 GMT

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

Re: Oracle Migration (8.1.7 --> 9.2.0)

spex — Tue, 22 Aug 2006 13:31:51 GMT

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

Re: Oracle Migration (8.1.7 --> 9.2.0)

TwoProc — Tue, 22 Aug 2006 14:39:30 GMT

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).

Re: Oracle Migration (8.1.7 --> 9.2.0)

Volker Borowski — Tue, 22 Aug 2006 16:17:30 GMT

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

Re: Oracle Migration (8.1.7 --> 9.2.0)

Yogeeraj_1 — Wed, 23 Aug 2006 01:06:24 GMT

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

Re: Oracle Migration (8.1.7 --> 9.2.0)

Steven E. Protter — Wed, 23 Aug 2006 01:24:53 GMT

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

Re: Oracle Migration (8.1.7 --> 9.2.0)

Yogeeraj_1 — Wed, 23 Aug 2006 01:26:09 GMT

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

Re: Oracle Migration (8.1.7 --> 9.2.0)

Yajuvendra Singh — Wed, 23 Aug 2006 02:19:31 GMT

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