Home » RDBMS Server » Server Administration » Upgration and Migration.
Upgration and Migration. [message #287334] Wed, 12 December 2007 00:22 Go to next message
IBNHUSSAIN
Messages: 39
Registered: December 2007
Location: INDIA
Member
Hello gurus,

I have to upgrade and migrate 8i on wind NT to 9i wind 2003.
I would like to get the knowledge about things which i should consider and the different ways which i can use to do this and the best way, before I proceed.

Please kindly suggest the solutions and proper links to this issue.
Re: Upgration and Migration. [message #287337 is a reply to message #287334] Wed, 12 December 2007 00:35 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

>which i can use to do this and the best way, before I proceed.
1. The best way is First test complete procedure on your dev database.

2. If it is production database then don't upgrade in oracle 9i. try to upgrade in 10g.It is recent version (except 11g).

3. three method you can use.
1. export/import (if database size is small)
2. through DBUA (db upgrade assistant)
3. manually ---(readme.txt) file read for more details.

Personally i like MANUAL method to upgrade database.


Re: Upgration and Migration. [message #287340 is a reply to message #287334] Wed, 12 December 2007 00:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Database Migration

Regards
Michel
Re: Upgration and Migration. [message #287355 is a reply to message #287340] Wed, 12 December 2007 01:23 Go to previous messageGo to next message
IBNHUSSAIN
Messages: 39
Registered: December 2007
Location: INDIA
Member
Thanks for reply,

Yes friends now i have decided to go with export/import option.

So please can you guide me to any link or guide for a step by step procedure to move the database using exp/imp.

thanks once again for your kind replies.

Regards
Re: Upgration and Migration. [message #287357 is a reply to message #287334] Wed, 12 December 2007 01:29 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
using exp/imp is simple.

Take export of the entire database.
Install oracle 9i in win 2003.
Create database and tablespaces as per requirment.
Create users and assign them respective tablespaces and quotas.
Import the database and i guess the game is over.
Re: Upgration and Migration. [message #287358 is a reply to message #287355] Wed, 12 December 2007 01:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The link I posted contains a step by step detailled explainations for each way you can choose.
For instance, for export/import mode see Chapter 8 "Database Migration Using Export/Import" without omitting the chapters "Preparing to Upgrade", "After Upgrading a Database" and so on or you will fail.

Regards
Michel
Re: Upgration and Migration. [message #287374 is a reply to message #287358] Wed, 12 December 2007 02:01 Go to previous messageGo to next message
IBNHUSSAIN
Messages: 39
Registered: December 2007
Location: INDIA
Member
Thanks for your kind replies,

I will give you all the feedback, once i finish it successfully
Any suggestions from your side in the last?
Thanks,
Re: Upgration and Migration. [message #287377 is a reply to message #287374] Wed, 12 December 2007 02:05 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

If it is production database then first test on dev db and check performance.

All the best
Re: Upgration and Migration. [message #287392 is a reply to message #287377] Wed, 12 December 2007 02:37 Go to previous messageGo to next message
IBNHUSSAIN
Messages: 39
Registered: December 2007
Location: INDIA
Member
Thanks for your guidance,

Yes, I will try it first on test.
But a simple doubt is there in my mind. Can you please clarify?

DOUBT: At the time of export if users are updating any tables, can my export capture that. I know that i can use consistent=n to capture it(but again again my exp may left some changes done after export). But I want to start my new database with all changes made by users at the time of export.

Do I have to shut db down. Please clarify

Please how can I achieve this.
Re: Upgration and Migration. [message #287394 is a reply to message #287392] Wed, 12 December 2007 02:40 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

>Do I have to shut db down. Please clarify
then how can you export your database.
But if you able to down your database means you have downtime then export your database when noone is connect to database except export user.


Re: Upgration and Migration. [message #287396 is a reply to message #287392] Wed, 12 December 2007 02:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
A migration is not something you do when database is open to users.
You have to plan a maintenance window.

Regards
Michel
Re: Upgration and Migration. [message #287425 is a reply to message #287396] Wed, 12 December 2007 03:56 Go to previous messageGo to next message
IBNHUSSAIN
Messages: 39
Registered: December 2007
Location: INDIA
Member
thanks a lot,

Now it is very clear to me. Smile

and the significance of consistent=n parameter is not applicable to migration, am i right?


Thanks,

Regards
Re: Upgration and Migration. [message #287429 is a reply to message #287425] Wed, 12 December 2007 04:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, you don't need to use it as you should not have other users in front of you.

Regards
Michel
Re: Upgration and Migration. [message #290646 is a reply to message #287429] Mon, 31 December 2007 00:36 Go to previous messageGo to next message
IBNHUSSAIN
Messages: 39
Registered: December 2007
Location: INDIA
Member
Hi all,

Friends, as on the way to migration, i have exported without warnings and imported the database with some warnings (in test). But i need again your kind support to get perfect idea of every warning i got.

--> I did export full=y without any warning.

--> Then I created tablespaces and users in the target database since the location for datafiles was different than that of source database.

--> Then i imported it with warnings such as..
1. Related to tablespace creation (many).
Error:
IMP-00003: ORACLE error 12913 encountered
ORA-12913: Cannot create dictionary managed tablespace
IMP-00017: following statement failed with ORACLE error 12913:


2. Related to constraint only one.
Error:
IMP-00017: following statement failed with ORACLE error 2270:
 "ALTER TABLE "DEF$_CALLDEST" ADD CONSTRAINT "DEF$_CALL_DESTINATION" FOREIGN "
 "KEY ("DBLINK") REFERENCES "DEF$_DESTINATION" ("DBLINK") ENABLE NOVALIDATE"
IMP-00003: ORACLE error 2270 encountered
ORA-02270: no matching unique or primary key for this column-list


3. Related to view creation.(many)

Error:
IMP-00041: Warning: object created with compilation warnings
 "CREATE FORCE VIEW "ADEEB"."SMINSTALLED_PRODUCT_V"                          "
 " ("INSTALLATION_ID","PRODUCT_ID") AS "
 "select"

IMP-00017: following statement failed with ORACLE error 12154:
 "CREATE FORCE VIEW "VISYS"."ERP_DEPT"                           ("NAME") AS "
 "Select Name"
 "From PER_ORGANIZATION_UNITS@erp.prod.link"
 "WHERE TYPE = 'DEPT'"
IMP-00003: ORACLE error 12154 encountered
ORA-12154: TNS:could not resolve service name
ORA-12154: TNS:could not resolve service name


I would like to inform you all what i have understood of these errors.

error 1, is because tablespaces were already created.
error 2, child row was trying to be inserted before parent row.
and when i see dba_constraints, i found this constraint is created.
( but here my doubt is , if it is full import, oracle import should know it, and if does not how to solve please tell me)
error 3. is because base table is not present to which view is trying to point. And second kind of error in this category is due to database link created in source database is not available here.


Please clarify me, if i am wrong anywhere.
Please kindly give me more explanation for these warnings.
Finally how can I know that my import was successful 100%.

Thanks very much.

[Updated on: Mon, 31 December 2007 00:41]

Report message to a moderator

Re: Upgration and Migration. [message #290653 is a reply to message #290646] Mon, 31 December 2007 01:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

error 1, is because tablespaces were already created

Wrong, it is because tablespace creation statement is for a DMT and you can't create DMT in your database. The solution is to precreate the tablespace and this seems to be what you already did. So you can ignore this message.

Quote:

error 2, child row was trying to be inserted before parent row.

No, this error has nothing to do with data. There is no primary key or unique key on the referenced table, so foreign key can't be created.
Verify the referenced PK or UK existed in source database.
Verify it has been created during import.

error 3: error 12154 is the most common network error. The most likely is that the service declared in database link is not present in your tnsnames.ora. Search for 12154 for more detailed information.

Regards
Michel
Re: Upgration and Migration. [message #290661 is a reply to message #290653] Mon, 31 December 2007 01:18 Go to previous messageGo to next message
IBNHUSSAIN
Messages: 39
Registered: December 2007
Location: INDIA
Member
Thanks very much Mr. Michel,

I guessed correct reasons for my warnings, but could not put it correctly in the post.

And as always you corrected me.

Thanks very much again.

Note: i guess if i do not get any error and warning in import that means it is 100% successful.

Regards
Re: Upgration and Migration. [message #290670 is a reply to message #290661] Mon, 31 December 2007 01:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes but in your case you will have at least the error from tablespace creation.

Regards
Michel
Re: Upgration and Migration. [message #290682 is a reply to message #290670] Mon, 31 December 2007 01:50 Go to previous messageGo to next message
IBNHUSSAIN
Messages: 39
Registered: December 2007
Location: INDIA
Member
Yes, thanks

Quote:

Verify the referenced PK or UK existed in source database.
Verify it has been created during import


--> yes, i verify the referenced pk in the source as well as in
the target database, and it is available in both
databasesthere.But there is no data in both the chiland
the parent table.

I am confused, when there is no data in both the tables, why
import gave me this warning.

IMP-00017: following statement failed with ORACLE error 2270:
 "ALTER TABLE "DEF$_CALLDEST" ADD CONSTRAINT "DEF$_CALL_DESTINATION" FOREIGN "
 "KEY ("DBLINK") REFERENCES "DEF$_DESTINATION" ("DBLINK") ENABLE NOVALIDATE"
IMP-00003: ORACLE error 2270 encountered
ORA-02270: no matching unique or primary key for this column-list



Please clarify.
Re: Upgration and Migration. [message #290688 is a reply to message #290682] Mon, 31 December 2007 01:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

There is no primary key or unique key on the referenced table, so foreign key can't be created.

Check againĄ PK AND unique key. Is there any on "DEF$_DESTINATION" ("DBLINK")?

Regards
Michel
Re: Upgration and Migration. [message #290818 is a reply to message #290688] Tue, 01 January 2008 01:37 Go to previous message
IBNHUSSAIN
Messages: 39
Registered: December 2007
Location: INDIA
Member
Hi Mr. Michel,

Sorry I could not reply you yesterday.

But i have checked it one more time for primary key and it is
available here is the output from both the databases the source
and the target.

ON THE SOURCE

SQL> select a.constraint_name,a.constraint_type,a.owner,b.table_name,b.column_na
me from dba_constraints a, dba_cons_columns b where a.constraint_name=b.constrai
nt_name and a.table_name='DEF$_DESTINATION' AND COLUMN_NAME='DBLINK';

CONSTRAINT_NAME                CONSTRAINT_TYP OWNER           TABLE_NAME
------------------------------ -------------- --------------- -------------
COLUMN_NAME
---------------
DEF$_DESTINATION_PRIMARY       P              SYSTEM          DEF$_DESTINAT
                                                              ION
DBLINK

SQL> SELECT COUNT(*) FROM DEF$_DESTINATION;

  COUNT(*)
----------
         0

--THIS IS FOREIGN KEY REFERENCE.
SQL> select a.constraint_name,a.constraint_type,a.owner,b.table_name,b.column_na
me,A.R_OWNER from dba_constraints a, dba_cons_columns b where a.constraint_name=
b.constraint_name and a.table_name='DEF$_CALLDEST' AND COLUMN_NAME='DBLINK';

CONSTRAINT_NAME                CONSTRAINT_TYP OWNER           TABLE_NAME
------------------------------ -------------- --------------- -------------
COLUMN_NAME     R_OWNER
--------------- ------------------------------
DEF$_CALLDEST_PRIMARY          P              SYSTEM          DEF$_CALLDEST
DBLINK

DEF$_CALL_DESTINATION          R              SYSTEM          DEF$_CALLDEST
DBLINK          SYSTEM



ON TARGET DATABASE

SQL> select a.constraint_name,a.constraint_type,a.owner,b.table_name,b.column_na
me from dba_constraints a, dba_cons_columns b where a.constraint_name=b.constrai
nt_name and a.table_name='DEF$_DESTINATION' AND COLUMN_NAME='DBLINK';

CONSTRAINT_NAME                CONSTRAINT_TYP OWNER           TABLE_NAME
------------------------------ -------------- --------------- -------------
COLUMN_NAME
---------------
DEF$_DESTINATION_PRIMARY       P              SYSTEM          DEF$_DESTINAT
                                                              ION
DBLINK
SQL> select a.constraint_name,a.constraint_type,a.owner,b.table_name,b.column_na
me from dba_constraints a, dba_cons_columns b where a.constraint_name=b.constrai
nt_name and a.table_name='DEF$_CALLDEST' AND COLUMN_NAME='DBLINK';

CONSTRAINT_NAME                CONSTRAINT_TYP OWNER           TABLE_NAME
------------------------------ -------------- --------------- -------------
COLUMN_NAME
---------------
DEF$_CALLDEST_PRIMARY          P              SYSTEM          DEF$_CALLDEST
DBLINK

DEF$_CALL_DESTINATION          R              SYSTEM          DEF$_CALLDEST
DBLINK

SQL> SELECT COUNT(*) FROM DEF$_DESTINATION;

  COUNT(*)
----------
         0


Please clarify, Hence Foreign key is created, when there is no data in referenced table.
Previous Topic: Cant increase the SGA
Next Topic: ORACLE Reports not working after switch from RBO to CBO (9i DB)
Goto Forum:
  


Current Time: Fri Dec 09 05:36:37 CST 2016

Total time taken to generate the page: 0.11871 seconds