Hello, I'm currently a student in an Oracle DBA class. I'm totally new to the Oracle world.
One of our final assignments concerns upgrading Oracle instances without the use of DBUA.
I've currently got Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 and am trying to upgrade to 11.1.0.7.0
This is all inside of VirtualBox, running Windows Server 2008. I've been using 10g with absolutely no issues for the duration of this class.
I successfully installed 11g, choosing to install the software only and not use dbua.
I changed my environment variables to point towards the 10g installation; ORACLE_HOME, ORACLE_SID, PATH, and LD_LIBRARY_PATH.
Then logged into 10g's SQLPlus and ran utlu111i.sql, which ran successfully. I issued a shutdown immediate and exited SQLPlus.
Changed my environment variables to point towards the 11g home and set ORACLE_SID as the 10g database name. I was able to log into 11g's SQL plus, was told I was connected to an idle instance, which I expected. I issued a startup upgrade command, and then attempted to run catupgrd.sql, but got an error stating that I had to run the script from 11g.
If I don't set the environment variables then when I try to log into 11g's sqlplus, I get an ORA-12560: TNS:protocol adapter error... I've tried reinstalling 11g from scratch, but to no avail. My instructor and I have been emailing back and forth frequently over the past few days but we have not been able to reach a solution, so I thought I would see if I could pick your brains.
Here's all of the terminal output from the process:
C:\Users\Administrator>sqlplus
SQL*Plus: Release 10.2.0.3.0 - Production on Fri Apr 29 20:15:09 2011
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Enter user-name: connect /as sysdba
Enter password:
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> @?
SP2-0310: unable to open file "C:\270Oracle.sql"
SQL> @C:\NewCIS270\product\11.1.0\db_1\rdbms\admin\utlu111i
Oracle Database 11.1 Pre-Upgrade Information Tool 04-29-2011 20:16:16
.
**********************************************************************
Database:
**********************************************************************
--> name: KYLMAN
--> version: 10.2.0.3.0
--> compatible: 10.2.0.3.0
--> blocksize: 8192
--> platform: Microsoft Windows IA (32-bit)
--> timezone file: V4
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 729 MB
.... AUTOEXTEND additional space required: 249 MB
--> UNDOTBS1 tablespace is adequate for the upgrade.
.... minimum required size: 417 MB
.... AUTOEXTEND additional space required: 352 MB
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 455 MB
.... AUTOEXTEND additional space required: 215 MB
--> TEMP tablespace is adequate for the upgrade.
.... minimum required size: 61 MB
.... AUTOEXTEND additional space required: 33 MB
--> EXAMPLE tablespace is adequate for the upgrade.
.... minimum required size: 78 MB
.
**********************************************************************
Update Parameters: [Update Oracle Database 11.1 init.ora or spfile]
**********************************************************************
-- No update parameter changes are required.
.
**********************************************************************
Renamed Parameters: [Update Oracle Database 11.1 init.ora or spfile]
**********************************************************************
-- No renamed parameters found. No changes are required.
.
**********************************************************************
Obsolete/Deprecated Parameters: [Update Oracle Database 11.1 init.ora or spfile]
**********************************************************************
--> "background_dump_dest" replaced by "diagnostic_dest"
--> "user_dump_dest" replaced by "diagnostic_dest"
--> "core_dump_dest" replaced by "diagnostic_dest"
.
**********************************************************************
Components: [The following database components will be upgraded or installed]
**********************************************************************
--> Oracle Catalog Views [upgrade] VALID
--> Oracle Packages and Types [upgrade] VALID
--> JServer JAVA Virtual Machine [upgrade] VALID
--> Oracle XDK for Java [upgrade] VALID
--> Oracle Workspace Manager [upgrade] VALID
--> OLAP Analytic Workspace [upgrade] VALID
--> OLAP Catalog [upgrade] VALID
--> EM Repository [upgrade] VALID
--> Oracle Text [upgrade] VALID
--> Oracle XML Database [upgrade] VALID
--> Oracle Java Packages [upgrade] VALID
--> Oracle interMedia [upgrade] VALID
--> Spatial [upgrade] VALID
--> Data Mining [upgrade] VALID
--> Expression Filter [upgrade] VALID
--> Rule Manager [upgrade] VALID
--> Oracle OLAP API [upgrade] VALID
.
**********************************************************************
Miscellaneous Warnings
**********************************************************************
WARNING: --> Database contains stale optimizer statistics.
.... Refer to the 11g Upgrade Guide for instructions to update
.... statistics prior to upgrading the database.
.... Component Schemas with stale statistics:
.... SYS
.... OLAPSYS
.... SYSMAN
.... CTXSYS
.... XDB
WARNING: --> Database contains schemas with objects dependent on network
packages.
.... Refer to the 11g Upgrade Guide for instructions to configure Network ACLs.
WARNING: --> EM Database Control Repository exists in the database.
.... Direct downgrade of EM Database Control is not supported. Refer to the
.... 11g Upgrade Guide for instructions to save the EM data prior to upgrade.
.
PL/SQL procedure successfully completed.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Pr
oduction
With the Partitioning, OLAP and Data Mining options
C:\Users\Administrator>set ORACLE_HOME=C:\NewCIS270\product\11.1.0\db_1
C:\Users\Administrator>set ORACLE_SID=KYLMAN
C:\Users\Administrator>set PATH=%ORACLE_HOME%\bin
C:\Users\Administrator>sqlplus "/as sysdba"
SQL*Plus: Release 11.1.0.7.0 - Production on Fri Apr 29 20:22:49 2011
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup upgrade;
ORACLE instance started.
Total System Global Area 440401920 bytes
Fixed Size 1291072 bytes
Variable Size 146803904 bytes
Database Buffers 289406976 bytes
Redo Buffers 2899968 bytes
Database mounted.
Database opened.
SQL> @?
SP2-0310: unable to open file "C:\NewCIS270\product\11.1.0\db_1.sql"
SQL> @?/rdbms/admin/catupgrd
DOC>#######################################################################
DOC>#######################################################################
DOC>
DOC> The first time this script is run, there should be no error messages
DOC> generated; all normal upgrade error messages are suppressed.
DOC>
DOC> If this script is being re-run after correcting some problem, then
DOC> expect the following error which is not automatically suppressed:
DOC>
DOC> ORA-00001: unique constraint (<constraint_name>) violated
DOC> possibly in conjunction with
DOC> ORA-06512: at "<procedure/function name>", line NN
DOC>
DOC> These errors will automatically be suppressed by the Database Upgrade
DOC> Assistant (DBUA) when it re-runs an upgrade.
DOC>
DOC>#######################################################################
DOC>#######################################################################
DOC>#
DOC>######################################################################
DOC>######################################################################
DOC> The following statement will cause an "ORA-01722: invalid number"
DOC> error if the user running this script is not SYS. Disconnect
DOC> and reconnect with AS SYSDBA.
DOC>######################################################################
DOC>######################################################################
DOC>#
no rows selected
DOC>######################################################################
DOC>######################################################################
DOC> The following statement will cause an "ORA-01722: invalid number"
DOC> error if the database server version is not correct for this script.
DOC> Shutdown ABORT and use a different script or a different server.
DOC>######################################################################
DOC>######################################################################
DOC>#
SELECT TO_NUMBER('MUST_BE_11_1') FROM v$instance
*
ERROR at line 1:
ORA-01722: invalid number
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Pr
oduction
With the Partitioning, OLAP and Data Mining options
C:\Users\Administrator>
Edited by: 855732 on Apr 29, 2011 6:59 PM