Home » RDBMS Server » Server Administration » oracle upgrade
oracle upgrade [message #214964] Thu, 18 January 2007 15:17 Go to next message
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
Re: oracle upgrade [message #214984 is a reply to message #214964] Thu, 18 January 2007 19:06 Go to previous messageGo to next message
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.
Re: oracle upgrade [message #215142 is a reply to message #214964] Fri, 19 January 2007 07:54 Go to previous messageGo to next message
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.
Re: oracle upgrade [message #215143 is a reply to message #214964] Fri, 19 January 2007 07:57 Go to previous messageGo to next message
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.
Re: oracle upgrade [message #215147 is a reply to message #215142] Fri, 19 January 2007 08:16 Go to previous messageGo to next message
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.
Re: oracle upgrade [message #215153 is a reply to message #214964] Fri, 19 January 2007 08:26 Go to previous messageGo to next message
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
Re: oracle upgrade [message #215161 is a reply to message #215153] Fri, 19 January 2007 09:05 Go to previous messageGo to next message
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.
Re: oracle upgrade [message #215168 is a reply to message #214964] Fri, 19 January 2007 09:28 Go to previous messageGo to next message
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
Re: oracle upgrade [message #215174 is a reply to message #215168] Fri, 19 January 2007 09:36 Go to previous messageGo to next message
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.
Re: oracle upgrade [message #215177 is a reply to message #214964] Fri, 19 January 2007 09:47 Go to previous messageGo to next message
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?
Re: oracle upgrade [message #215180 is a reply to message #215177] Fri, 19 January 2007 09:53 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Did you restart the database after running that script?
Re: oracle upgrade [message #215181 is a reply to message #214964] Fri, 19 January 2007 09:59 Go to previous messageGo to next message
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.
Re: oracle upgrade [message #215184 is a reply to message #215181] Fri, 19 January 2007 10:08 Go to previous messageGo to next message
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');
Re: oracle upgrade [message #215189 is a reply to message #214964] Fri, 19 January 2007 10:29 Go to previous messageGo to next message
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???


Re: oracle upgrade [message #215190 is a reply to message #215189] Fri, 19 January 2007 10:33 Go to previous messageGo to next message
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?
Re: oracle upgrade [message #215195 is a reply to message #214964] Fri, 19 January 2007 10:58 Go to previous messageGo to next message
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.
Re: oracle upgrade [message #215199 is a reply to message #215195] Fri, 19 January 2007 12:08 Go to previous messageGo to next message
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.
Re: oracle upgrade [message #215204 is a reply to message #214964] Fri, 19 January 2007 12:26 Go to previous messageGo to next message
BlackSwan
Messages: 25050
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!
Re: oracle upgrade [message #215218 is a reply to message #215204] Fri, 19 January 2007 17:35 Go to previous message
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??
Previous Topic: import/export package and function
Next Topic: oracle10g installation Problem
Goto Forum:
  


Current Time: Sun Dec 11 07:51:18 CST 2016

Total time taken to generate the page: 0.10588 seconds