OraFAQ Forum: Server Administration » oracle upgrade

Home » RDBMS Server » Server Administration » oracle upgrade

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

oracle upgrade [message #214964]

Thu, 18 January 2007 15:17

plshelp
Messages: 205
Registered: January 2007

Senior Member

Hi,

I am upgrading oracle from 8.1.7.4 to 9.2.0.7 version.
I am following the document for upgrading. But I have an issue here. After starting up the datbaase in migrate mode, I was running this script u0801070.sql. While running I am not sure what happened i got an error saying NETWORK ERROR.CONNECTION TIMEOUT. What I think is my session ended.
But even now, when i open a new session and type

select * from v$version;

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
PL/SQL Release 9.2.0.7.0 - Production
CORE 9.2.0.7.0 Production
TNS for Solaris: Version 9.2.0.7.0 - Production
NLSRTL Version 9.2.0.7.0 - Production

But I know I still have to run the same script u0801070.sql.
My confusion is, where to start from????? Will it be ok if I just run the script again?? or start from the beginning of the upgrade? Please give me suggestions.
Thank you

Report message to a moderator

Re: oracle upgrade [message #214984 is a reply to message #214964]

Thu, 18 January 2007 19:06

ebrian
Messages: 2794
Registered: April 2006

Senior Member

Were you conducting the upgrade remotely? Did you backup your database prior to the upgrade?

You can re-run u0801070.sql, but I would recommend being on the database server itself.

Report message to a moderator

Re: oracle upgrade [message #215142 is a reply to message #214964]

Fri, 19 January 2007 07:54

plshelp
Messages: 205
Registered: January 2007

Senior Member

Hi Ebrian,

Yes I am upgrading remotely. But the setup is like this. We have the previous version location in /appl/oracle/...
and now the upgraded version in /oracle92/oracle...
so the backup of the database would still be in the previous location. If I am not wrong.

I ran the script again and I spooled it. I see the below errors in the log file:

insert into SYS_PRIVS values (-12, 'CREATE SEC PROF',0)
*
ERROR at line 1:
ORA-00001: unique constraint (SYS.I_SYS_PRIVS) violated

insert into procedure$
*
ERROR at line 1:
ORA-00001: unique constraint (SYS.I_PROCEDURE1) violated

drop index i_inat$_prt$
*
ERROR at line 1:
ORA-01418: specified index does not exist

Please give me your suggestions.

Report message to a moderator

Re: oracle upgrade [message #215143 is a reply to message #214964]

Fri, 19 January 2007 07:57

plshelp
Messages: 205
Registered: January 2007

Senior Member

One more question, Is it ok to run the SQL scripts more than once or twice??? scripts like u0801070.sql, cmpdbmig.sql etc..
Please advise.

Report message to a moderator

Re: oracle upgrade [message #215147 is a reply to message #215142]

Fri, 19 January 2007 08:16

ebrian
Messages: 2794
Registered: April 2006

Senior Member

plshelp wrote on Fri, 19 January 2007 08:54

Yes I am upgrading remotely. But the setup is like this. We have the previous version location in /appl/oracle/...
and now the upgraded version in /oracle92/oracle...
so the backup of the database would still be in the previous location. If I am not wrong.

I ran the script again and I spooled it. I see the below errors in the log file:

insert into SYS_PRIVS values (-12, 'CREATE SEC PROF',0)
*
ERROR at line 1:
ORA-00001: unique constraint (SYS.I_SYS_PRIVS) violated

insert into procedure$
*
ERROR at line 1:
ORA-00001: unique constraint (SYS.I_PROCEDURE1) violated

drop index i_inat$_prt$
*
ERROR at line 1:
ORA-01418: specified index does not exist

Are you indicating that those two locations are physically located on separate servers, but both mounted on the new 9i server?

You can expect errors of this nature if you re-run the upgrade script when the previous run of the upgrade script didn't finish or had problems.

Report message to a moderator

Re: oracle upgrade [message #215153 is a reply to message #214964]

Fri, 19 January 2007 08:26

plshelp
Messages: 205
Registered: January 2007

Senior Member

Hi Ebrian,

The server is same.Just the location is different.

So,is it ok to go ahead and continue with the further steps or do I have to fix these errors.

So, if I rerun the script again will it give me the same error???

Thank you
Geetha

Report message to a moderator

Re: oracle upgrade [message #215161 is a reply to message #215153]

Fri, 19 January 2007 09:05

ebrian
Messages: 2794
Registered: April 2006

Senior Member

You'll see similar errors if you run the upgrade script repeatedly.

Once you have completed the upgrade, run the following select:

SQL> select comp_name, version, status
         from dba_registry;

and also query DBA_OBJECTS to see if anything is INVALID.

Report message to a moderator

Re: oracle upgrade [message #215168 is a reply to message #214964]

Fri, 19 January 2007 09:28

plshelp
Messages: 205
Registered: January 2007

Senior Member

Hi ebrian,

This is what I got from that select :

SQL> select comp_name,version,status from dba_registry;

COMP_NAME VERSION STATUS
----------------------------------- ------------------------------ -----------
Oracle9i Catalog Views 9.2.0.7.0 VALID
Oracle9i Packages and Types 9.2.0.7.0 VALID
JServer JAVA Virtual Machine 9.2.0.7.0 VALID
Oracle9i Java Packages 9.2.0.7.0 VALID
Oracle XDK for Java 9.2.0.2.0 UPGRADED
Oracle interMedia Text 8.1.7.4 LOADED
Oracle interMedia 8.1.7.0.0 LOADED
Oracle Spatial 8.1.7.0.0 PRODUCTION LOADED
Oracle Visual Information Retrieval 8.1.7.0.0 LOADED

9 rows selected.

Now , my next step is run the script cmpdbmig.sql to upgrade the components which can be upgrade with the SYSDBA priv.

Now, with the above output, there is only one comp_name upgraded, so do i have to run this script to upgrade the rest of the components. Please advise.THank you very much for your help

Report message to a moderator

Re: oracle upgrade [message #215174 is a reply to message #215168]

Fri, 19 January 2007 09:36

ebrian
Messages: 2794
Registered: April 2006

Senior Member

The fact that Oracle XDK for Java 9.2.0.2.0 has a status of UPGRADED is fine. Just continue to run the rest of the upgrade scripts.

Report message to a moderator

Re: oracle upgrade [message #215177 is a reply to message #214964]

Fri, 19 January 2007 09:47

plshelp
Messages: 205
Registered: January 2007

Senior Member

Hi Ebrian,

I ran the script to upgrade:
cmpdbmig.sql
but still its showing the same if do the below select:

SQL> select comp_name,version,status from dba_registry;

COMP_NAME VERSION STATUS
----------------------------------- ------------------------------ -----------
Oracle9i Catalog Views 9.2.0.7.0 VALID
Oracle9i Packages and Types 9.2.0.7.0 VALID
JServer JAVA Virtual Machine 9.2.0.7.0 VALID
Oracle9i Java Packages 9.2.0.7.0 VALID
Oracle XDK for Java 9.2.0.2.0 UPGRADED
Oracle interMedia Text 8.1.7.4 LOADED
Oracle interMedia 8.1.7.0.0 LOADED
Oracle Spatial 8.1.7.0.0 PRODUCTION LOADED
Oracle Visual Information Retrieval 8.1.7.0.0 LOADED

Any idea why it did not upgrade the other components?

Report message to a moderator

Re: oracle upgrade [message #215180 is a reply to message #215177]

Fri, 19 January 2007 09:53

ebrian
Messages: 2794
Registered: April 2006

Senior Member

Did you restart the database after running that script?

Report message to a moderator

Re: oracle upgrade [message #215181 is a reply to message #214964]

Fri, 19 January 2007 09:59

plshelp
Messages: 205
Registered: January 2007

Senior Member

No.I didnt restart the database after running the script.

But now I restarted the database and seeing your reply. But still gives me the same output. But I think as long as it shows valid its good to go ahead. Right now I am checking for the invalid objects.
Thanks for your help Ebrian.

Report message to a moderator

Re: oracle upgrade [message #215184 is a reply to message #215181]

Fri, 19 January 2007 10:08

ebrian
Messages: 2794
Registered: April 2006

Senior Member

After restarting, you should run the necessary scripts for (Oracle Text, Oracle Spatial, Oracle interMedia & Oracle Visual Information Retrieval). Once you've completed this, you should run utlrp.sql. If the component is still showing UPGRADED, you should run the following:

exec sys.dbms_registry.loaded('JAVAVM');
exec sys.dbms_registry.valid('JAVAVM');

Report message to a moderator

Re: oracle upgrade [message #215189 is a reply to message #214964]

Fri, 19 January 2007 10:29

plshelp
Messages: 205
Registered: January 2007

Senior Member

Thanks very much Ebrian. Now its showing its upgraded.

I ran utlrp.sql and check for invalid objects.

select substr(owner,1,12) owner,substr(object_name,1,30) object,substr(object_type,1,30) type, status from dba_objects where status <> 'VALID';

OWNER OBJECT TYPE STATUS
------------ ------------------------------ ------------------ -------
ORDSYS ORDTGET PACKAGE BODY INVALID
MDSYS DBA_SDO_INDEX_METADATA VIEW INVALID
MDSYS SDO_CATALOG PACKAGE BODY INVALID
CTXSYS DRIDDLC PACKAGE BODY INVALID
CTXSYS DRIDISP PACKAGE INVALID

In the document, it says there should be no dictionary objects invalid. Does it mean the objects of SYS must not be invalid???
IF that is so, I can procced further???

Report message to a moderator

Re: oracle upgrade [message #215190 is a reply to message #215189]

Fri, 19 January 2007 10:33

ebrian
Messages: 2794
Registered: April 2006

Senior Member

Did you run the required upgrade scripts for Oracle TEXT & Spatial? If so, did they report any errors?

Report message to a moderator

Re: oracle upgrade [message #215195 is a reply to message #214964]

Fri, 19 January 2007 10:58

plshelp
Messages: 205
Registered: January 2007

Senior Member

Hi Ebrian,

I ran the script for invalid objects.

I have a few invalid objects for example,

OWNER OBJECT TYPE STATUS
------------ ------------------------------ ------------------ -------
ORDSYS ORDTGET PACKAGE BODY INVALID

Now if i want to compile it,i was asked to do

EXEC DBMS_DDL('PACKAGE', ' MY_SCHEMA','MY_PACKAGE');

SQL>EXEC DBMS_DDL('PACKAGE','ORDSYS','MY_PACKAGE');

BEGIN DBMS_DDL('PACKAGE','ORDSYS','MY_PACKAGE'); END;

*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00221: 'DBMS_DDL' is not a procedure or is undefined
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

Please advise.

Report message to a moderator

Re: oracle upgrade [message #215199 is a reply to message #215195]

Fri, 19 January 2007 12:08

ebrian
Messages: 2794
Registered: April 2006

Senior Member

You should reference the 9i Migration Guide and connect to the database as the correct user and execute the appropriate upgrade scripts for the options I listed above.

Report message to a moderator

Re: oracle upgrade [message #215204 is a reply to message #214964]

Fri, 19 January 2007 12:26

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

ebrian,
Why should OP be bothered to read the fine manual when you & others are willing to spoon feed answers?
HAND!

Report message to a moderator

Re: oracle upgrade [message #215218 is a reply to message #215204]

Fri, 19 January 2007 17:35

ebrian
Messages: 2794
Registered: April 2006

Senior Member

anacedent,
I typically provide links to the documentation when applicable, but if you took the time to read the thread you'd realize that the OP's questions aren't addressed directly in the documentation. I'm a proponent of the "teach a man to fish" doctrine, however in this case, simply indicating "read the fine manual" would have served no purpose for the OP or the forum in general. If that were the case, why bother with the forum at all and not just have forum groups that had links for each volume of the Oracle documentation??

Report message to a moderator

Previous Topic:	import/export package and function
Next Topic:	oracle10g installation Problem

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Sep 20 04:22:19 CDT 2024