| upgrading from 9.2.0.7 to 11.1.0 (2 Merged) [message #494557] |
Tue, 15 February 2011 13:18  |
cakeday
Messages: 31
Registered: May 2010
|
Member |
|
|
Hi all:
I am upgrading oracle database from 9.2.0.7 to 11.1.0 (clients need 11.1. andnot 11.2).
Metalink tells me that I can do a direct upgrade and don't have to go to 9.2.0.8. so that's good news. Since time is of essence here : )
I ran the utlu111i.sql.
And made the following changes, per its recommendation.
Obsolete/Deprecated Parameters: [Update Oracle Database 11.1 init.ora or spfile]
**********************************************************************
--> "log_archive_start"
--> "max_enabled_roles"
--> "parallel_automatic_tuning"
--> "background_dump_dest" replaced by "diagnostic_dest"
--> "user_dump_dest" replaced by "diagnostic_dest"
--> "core_dump_dest" replaced by "diagnostic_dest"
there are a few issues here that I need over help with:
ISSUE 1:I already have a sysaux tablespace in my db, but the log still says, ANY IDEA WHY ?SYSAUX Tablespace:
[Create tablespace in the Oracle Database 11.1 environment]
**********************************************************************
--> New "SYSAUX" tablespace
.... minimum required size for database upgrade: 500 MB
ISSUE 2:I copied the 9i orapw file to the 11g $ORACLE_HOME/dbs location but when I try to login, using the same login (after I source the new profile) it still asks me for username/password. Doesn't allow remote sysdba login ? DO I NEED TO DO SOMETHING SPECIAL WITH THE PASSWORD FILE ?
ISSUE 3:
Here are the steps I took before I get the next error:
I copied the init file from 9i location to the 11g location made the above changes in the init file.
I set the compatible to 10.1.0.
Added diagnostic_dest and got rid of bdump, udump and cdump
Then I shutdown the db (9i)
I sourced the 9i profile (IS THIS TRUE OR SHOULD I SOURCE THE 11G PROFILE ?), and tried to start the db using startup upgrade (my changes have been made to the 9i init file and that is the one I am using to start the db with in the upgrade mode, is that ok ? ) I GET THE FOLLOWING ERROR
SP2-0714: invalid combination of STARTUP options ANY IDEA WHY ?
|
|
|
|
| Re: upgrading from 9.2.0.7 to 11.1.0 (2 Merged) [message #494560 is a reply to message #494557] |
Tue, 15 February 2011 13:28  |
 |
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
>>I already have a sysaux tablespace in my db, but the log still says, ANY IDEA WHY ?SYSAUX Tablespace:
SYSAUX was introduced in 10g and you are using it in 9i?
Do you have a custom tablespace named sysaux?
>>DO I NEED TO DO SOMETHING SPECIAL WITH THE PASSWORD FILE ?
I would just create a new one.
>>I set the compatible to 10.1.0.
>>Added diagnostic_dest and got rid of bdump, udump and cdump
diagnostic_dest is introduced 11.x
And your compatible is set to 10.x.
And please do not explain what you did. Helps us little.
Post what you did. Just copy and paste.
Unless we see the init.ora you are using, your session we have
no idea what you are doing.
[Updated on: Tue, 15 February 2011 13:31] Report message to a moderator |
|
|
|
| Re: upgrading from 9.2.0.7 to 11.1.0 (2 Merged) [message #494564 is a reply to message #494560] |
Tue, 15 February 2011 14:38 |
cakeday
Messages: 31
Registered: May 2010
|
Member |
|
|
i got the instance online in the startup upgrade mode and then when i tried to upgrade -- it errored out. ??????
ERROR LOG is below. please help.
===
now i can't even bring the instance online as the 9i instance because it says, ORA-00201: controlfile version 9.2.0.0.0 incompatible with ORACLE version
9.2.0.0.0
====
iocl29x5:/p072x/oracle/product/11.1.0/rdbms/admin> sqlplus /nolog
SQL*Plus: Release 11.1.0.7.0 - Production on Tue Feb 15 12:28:06 2011
Copyright (c) 1982, 2008, Oracle. All rights reserved.
SQL> connect / as sysdba
Connected to an idle instance.
SQL> startup upgrade pfile='/p072x/admin/pfile/initP072.ora'
ORACLE instance started.
Total System Global Area 7349010432 bytes
Fixed Size 2154736 bytes
Variable Size 4093644560 bytes
Database Buffers 3221225472 bytes
Redo Buffers 31985664 bytes
Database mounted.
Database opened.
SQL>
CREATE TABLESPACE sysaux datafile '/p072x/data001/sysaux01.dbf'
size 500M extent management local segment space management auto online;
SQL> 2
Tablespace created.
SQL> SQL> set echo on
SQL> SPOOL upgrade.log
SQL> @catupgrd.sql
SQL> Rem
SQL> Rem $Header: catupgrd.sql 29-mar-2007.12:50:24 cdilling Exp $
SQL> Rem
SQL> Rem catupgrd.sql
SQL> Rem
SQL> Rem Copyright (c) 1999, 2007, Oracle. All rights reserved.
SQL> Rem
SQL> Rem NAME
SQL> Rem catupgrd.sql - CATalog UPGraDe to the new release
SQL> Rem
SQL> Rem DESCRIPTION
SQL> Rem This script is to be used for upgrading a 9.2, 10.1 or 10.2
SQL> Rem database to the new release. This script provides a direct
SQL> Rem upgrade path from these releases to the new Oracle release.
SQL> Rem
SQL> Rem The upgrade is partitioned into the following 5 stages:
SQL> Rem STAGE 1: call the "i" script for the oldest supported release:
SQL> Rem This loads all tables that are necessary
SQL> Rem to perform basic DDL commands for the new release
SQL> Rem STAGE 2: call utlip.sql to invalidate PL/SQL objects
SQL> Rem STAGE 3: Determine the original release and call the
SQL> Rem c0x0x0x0.sql for the release. This performs all
SQL> Rem necessary dictionary upgrade actions to bring the
SQL> Rem database from the original release to new release.
SQL> Rem
SQL> Rem NOTES
SQL> Rem
SQL> Rem * This script needs to be run in the new release environment
SQL> Rem (after installing the release to which you want to upgrade).
SQL> Rem * You must be connected AS SYSDBA to run this script.
SQL> Rem
SQL> Rem MODIFIED (MM/DD/YY)
SQL> Rem cdilling 03/29/07 - set error logging off - bug 5959958
SQL> Rem rburns 12/11/06 - eliminate first phase
SQL> Rem rburns 07/19/06 - fix log miner location
SQL> Rem rburns 05/22/06 - restructure for parallel upgrade
SQL> Rem rburns 02/15/06 - re-run message with expected errors
SQL> Rem gviswana 03/09/06 - Add utlrdt
SQL> Rem rburns 02/10/06 - fix re-run logic for 11.1
SQL> Rem rburns 01/10/06 - release 11.1.0
SQL> Rem rburns 11/09/05 - version fixes
SQL> Rem rburns 10/21/05 - remove 817 and 901 upgrades
SQL> Rem cdilling 09/28/05 - temporary version until db version updated
SQL> Rem ssubrama 08/17/05 - bug 4523571 add note before utlip
SQL> Rem sagrawal 06/28/05 - invalidate PL/SQL objects for upgrade to 11
SQL> Rem rburns 03/14/05 - dbms_registry_sys timestamp
SQL> Rem rburns 02/27/05 - record action for history
SQL> Rem rburns 10/18/04 - remove catpatch.sql
SQL> Rem rburns 09/02/04 - remove dbms_output compile
SQL> Rem rburns 06/17/04 - use registry log and utlusts
SQL> Rem mvemulap 05/26/04 - grid mcode compatibility
SQL> Rem jstamos 05/20/04 - utlip workaround
SQL> Rem rburns 05/17/04 - rburns_single_updown_scripts
SQL> Rem rburns 01/27/04 - Created
SQL> Rem
SQL>
SQL> DOC
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>#
SQL>
SQL> Rem Initial checks and RDBMS upgrade scripts
SQL> @@catupstr.sql
SQL> Rem
SQL> Rem $Header: catupstr.sql 11-nov-2007.12:35:40 rburns Exp $
SQL> Rem
SQL> Rem catupstr.sql
SQL> Rem
SQL> Rem Copyright (c) 2006, 2007, Oracle. All rights reserved.
SQL> Rem
SQL> Rem NAME
SQL> Rem catupstr.sql - CATalog UPgrade STaRt script
SQL> Rem
SQL> Rem DESCRIPTION
SQL> Rem This script performs the initial checks for upgrade
SQL> Rem (open for UPGRADE, AS SYSDBA, etc.) and then runs
SQL> Rem the "i" scripts, utlip.sql, and the "c" scripts
SQL> Rem to complete the basic RDBMS upgrade
SQL> Rem
SQL> Rem NOTES
SQL> Rem Invoked from catupgrd.sql
SQL> Rem
SQL> Rem MODIFIED (MM/DD/YY)
SQL> Rem rburns 11/11/07 - XbranchMerge rburns_bug-6446262 from
SQL> Rem st_rdbms_project-18813
SQL> Rem rburns 11/08/07 - check for INVALID old versions of types
SQL> Rem cdilling 08/23/07 - check disabled indexes only
SQL> Rem rburns 07/16/07 - add 11.1 patch upgrade
SQL> Rem rburns 05/29/07 - add timezone version check
SQL> Rem rburns 05/01/07 - reload dbms_assert
SQL> Rem rburns 03/10/07 - add DV and OLS check
SQL> Rem cdilling 02/19/07 - add sys.enabled$indexes table for bug 5530085
SQL> Rem dvoss 02/19/07 - Check bootstrap migration status
SQL> Rem rburns 10/23/06 - add session script
SQL> Rem rburns 08/14/06 - add RDBMS identifier
SQL> Rem cdilling 06/08/06 - add error logging table
SQL> Rem gviswana 06/07/06 - Enable 4523571 fix
SQL> Rem rburns 05/22/06 - parallel upgrade
SQL> Rem rburns 05/22/06 - Created
SQL> Rem
SQL>
SQL> Rem =====================================================================
SQL> Rem Exit immediately if there are errors in the initial checks
SQL> Rem =====================================================================
SQL>
SQL> WHENEVER SQLERROR EXIT;
SQL>
SQL> 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>#
SQL>
SQL> SELECT TO_NUMBER('MUST_BE_AS_SYSDBA') FROM DUAL
2 WHERE USER != 'SYS';
no rows selected
SQL>
SQL> DOC
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>#
SQL>
SQL> SELECT TO_NUMBER('MUST_BE_11_1') FROM v$instance
2 WHERE substr(version,1,6) != '11.1.0';
no rows selected
SQL>
SQL> DOC
DOC>#######################################################################
DOC>#######################################################################
DOC> The following statement will cause an "ORA-01722: invalid number"
DOC> error if the database has not been opened for UPGRADE.
DOC>
DOC> Perform a "SHUTDOWN ABORT" and
DOC> restart using UPGRADE.
DOC>#######################################################################
DOC>#######################################################################
DOC>#
SQL>
SQL> SELECT TO_NUMBER('MUST_BE_OPEN_UPGRADE') FROM v$instance
2 WHERE status != 'OPEN MIGRATE';
no rows selected
SQL>
SQL> DOC
DOC>#######################################################################
DOC>#######################################################################
DOC> The following statement will cause an "ORA-01722: invalid number"
DOC> error if the Oracle Database Vault option is TRUE. Upgrades cannot
DOC> be run with the Oracle Database Vault option set to TRUE since AS
DOC> SYSDBA connections are restricted.
DOC>
DOC> Perform a "SHUTDOWN ABORT", relink the server without the Database
DOC> Vault option (but with the OLS option), and restart the server using
DOC> UPGRADE.
DOC>#######################################################################
DOC>#######################################################################
DOC>#
SQL>
SQL> SELECT TO_NUMBER('DATA_VAULT_OPTION_ON') FROM v$option
2 WHERE value = 'TRUE' and parameter = 'Oracle Database Vault';
no rows selected
SQL>
SQL> DOC
DOC>#######################################################################
DOC>#######################################################################
DOC> The following statement will cause an "ORA-01722: invalid number"
DOC> error if Database Vault is installed in the database but the Oracle
DOC> Label Security option is FALSE. To successfully upgrade Oracle
DOC> Database Vault, the Oracle Label Security option must be TRUE.
DOC>
DOC> Perform a "SHUTDOWN ABORT", relink the server with the OLS option
DOC> (but without the Oracle Database Vault option) and restart the server
DOC> using UPGRADE.
DOC>#######################################################################
DOC>#######################################################################
DOC>#
SQL>
SQL> SELECT TO_NUMBER('LABEL_SECURITY_OPTION_OFF') FROM v$option
2 WHERE value = 'FALSE' and parameter =
3 (SELECT 'Oracle Label Security' FROM user$ where name = 'DVSYS');
no rows selected
SQL>
SQL> DOC
DOC>#######################################################################
DOC>#######################################################################
DOC> The following statement will cause an "ORA-01722: invalid number"
DOC> error if bootstrap migration is in progress and logminer clients
DOC> require utlmmig.sql to be run next to support this redo stream.
DOC>
DOC> Run utlmmig.sql
DOC> then (if needed)
DOC> restart the database using UPGRADE and
DOC> rerun the upgrade script.
DOC>#######################################################################
DOC>#######################################################################
DOC>#
SQL>
SQL> SELECT TO_NUMBER('MUST_RUN_UTLMMIG.SQL')
2 FROM SYS.V$DATABASE V
3 WHERE V.LOG_MODE = 'ARCHIVELOG' and
4 V.SUPPLEMENTAL_LOG_DATA_MIN != 'NO' and
5 exists (select 1 from sys.props$
6 where name = 'LOGMNR_BOOTSTRAP_UPGRADE_ERROR');
no rows selected
SQL>
SQL> DOC
DOC>#######################################################################
DOC>#######################################################################
DOC> The following statement will cause an "ORA-01722: invalid number"
DOC> error if the current timezone version file is not the same as the
DOC> version in the database to be upgraded. The statement will cause an
DOC> ORA-00942 or ORA-00904 error if the pre-upgrade utility (utlu111i.sql)
DOC> has not be run to update registry$database table to include the existing
DOC> database timezone file version.
DOC>
DOC> Shutdown ABORT and revert to the original ORACLE_HOME. Then apply
DOC> the required timezone version patch to the original ORACLE_HOME
DOC> prior to attempting to upgrade. Re-run utlu111i.sql after applying
DOC> the patch to record the new timzone file version in the
DOC> registry$database table.
DOC>
DOC>#######################################################################
DOC>#######################################################################
DOC>#
SQL>
SQL> SELECT TO_NUMBER('MUST_BE_SAME_TIMEZONE_FILE_VERSION')
2 FROM registry$database
3 WHERE tz_version != (SELECT version from v$timezone_file);
SELECT TO_NUMBER('MUST_BE_SAME_TIMEZONE_FILE_VERSION')
*
ERROR at line 1:
ORA-01722: invalid number
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: upgrading from 9.2.0.7 to 11.1.0 (2 Merged) [message #494578 is a reply to message #494572] |
Tue, 15 February 2011 15:43 |
cakeday
Messages: 31
Registered: May 2010
|
Member |
|
|
thank you guys!!
so here are a few questions before i do anything. i will wait until i hear from you before i do anything.
QUESTION 1:
I cannot bring the DB instance online (meaning opened or mounted) in the 9i version because it says the control file is corrupted. I can do a restore but that will take a while.
So is this something that can be fixed, WHEN THE DATABASE IS IN THIS STATE ? OR DO I HAVE TO DO A RESTORE BEFORE I CAN ATTEMPT THIS ?
QUESTION 2:
If i can do this without the restore --
When i ran the utlu111i.sql -- it came back with the following information. does this mean, my timezone is at VERSION 1 ?
WHICH VERSION OF TIMEZONE DO I NEED TO UPGRADE THE TIMEZONE VERSION TO GET RID OF THIS ERROR ?
SQL> @utlu111i.sql
Oracle Database 11.1 Pre-Upgrade Information Tool 02-10-2011 14:31:38
.
**********************************************************************
Database:
**********************************************************************
--> name: P072
--> version: 9.2.0.7.0
--> compatible: 9.2.0
--> blocksize: 8192
--> timezone file: V1
|
|
|
|
| Re: upgrading from 9.2.0.7 to 11.1.0 (2 Merged) [message #494585 is a reply to message #494578] |
Tue, 15 February 2011 18:17 |
|
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
2)Quoting from the documentation link posted earlier.
Quote:If you have not already updated your existing databases to time zone file version 4 (patch 5632264 - TZ V4 file), you must do so prior to upgrading to Oracle Database 11g Release 1 (11.1).
1). We have no idea what you have really done. Nothing in your post to
prove whatever you are claiming.
I would just startover.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
