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