Last couple months ago I upgrade oracle 10g database to 11g using rman hot backup.
Source database 10.2.0.4 Sparc 64-bit Solaris 10
Target database 11.2.0.4 Sparc 64-bit Solaris 11
----------------------------------------------
1, Copy pfile from Oracle 10g home to Oracle 11g home.
2. Copy password file from Oracle 10g home to Oracle 11g home.
/ora/app/oracle/product/11.2.0/dbhome_1/dbs
initssaerp.init
pwinitssaerp.ora
SQL> conn / as sysdba
SQL> Startup nomount pfile='/ora/app/oracle/product/11.2.0/dbhome_1/dbs/initssaerp.ora'
$ /usr/open/netbackup/bin
Make oracle link with net backup MML (Media Managmenent Layer)
$./oracle_link
$ cd /usr/openv/netbackup/bin
$./bplist -C lh-ora-ra -t 4 -l -R /
Check the control file tage
$cd $ORACLE_HOME/bin
Restore Oracle control from TAPE
$ rman target / nocatalog
RMAN> set dbid=3389811866
RMAN> run {
allocate channel ch00 type 'SBT_TAPE';
send 'NB_ORA_CLIENT=lh-ora-ra';
restore controlfile from 'cntrl_3235_1_713828356';
release channel ch00;
RMAN> alter database mount;
RMAN> run {
allocate channel ch00 type 'SBT_TAPE';
send 'NB_ORA_CLIENT=lh-ora-rs';
restore database;
recover database;
release channel ch00;
}
Startup database on upgrade mode
RMAN> alter database open resetlogs upgrade
Preupgrade steps.
Check on production server
SQL> select owner,object_name,object_type,status from dba_objects where status='INVALID';
SQL> @/ora/crs/oracle/product/10/app/rdbms/admin/utlu112i.sql (Copy utlu112i.sql from target to production)
SQL> @/ora/crs/oracle/product/10/app//rdbms/admin/utlrp (Production Server) if objects invalid.
Upgrade database.
SQL> conn / as sysdba
SQL> spool /ora/app/upgrde.log
SQL> @/ora/app/oracle/product/11.2.0/dbhome_1/rdbms/admin/catupgrd.sql
SQL> @/ora/app/oracle/product/11.2.0/dbhome1104/rdbms/admin/catupgrd.sql
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> Perform "ALTER SYSTEM CHECKPOINT" prior to "SHUTDOWN ABORT", and use
DOC> a different script or a different server.
DOC>######################################################################
DOC>######################################################################
DOC>#
no rows selected
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 "ALTER SYSTEM CHECKPOINT" prior to "SHUTDOWN ABORT", and
DOC> restart using UPGRADE.
DOC>#######################################################################
DOC>#######################################################################
DOC>#
no rows selected
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
DOC> AS SYSDBA connections are restricted.
DOC>
DOC> Perform "ALTER SYSTEM CHECKPOINT" prior to "SHUTDOWN ABORT", relink
DOC> the server without the Database Vault option, and restart the server
DOC> using UPGRADE mode.
DOC>
DOC>
DOC>#######################################################################
DOC>#######################################################################
DOC>#
no rows selected
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 "ALTER SYSTEM CHECKPOINT" prior to "SHUTDOWN ABORT",
DOC> relink the server with the OLS option (but without the Oracle Database
DOC> Vault option) and restart the server using UPGRADE.
DOC>#######################################################################
DOC>#######################################################################
DOC>#
no rows selected
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>#
no rows selected
DOC>#######################################################################
DOC>#######################################################################
DOC> The following error is generated if the pre-upgrade tool has not been
DOC> run in the old ORACLE_HOME home prior to upgrading a pre-11.2 database:
DOC>
DOC> SELECT TO_NUMBER('MUST_HAVE_RUN_PRE-UPGRADE_TOOL_FOR_TIMEZONE')
DOC> *
DOC> ERROR at line 1:
DOC> ORA-01722: invalid number
DOC>
DOC> o Action:
DOC> Shutdown database ("alter system checkpoint" and then "shutdown abort").
DOC> Revert to the original oracle home and start the database.
DOC> Run pre-upgrade tool against the database.
DOC> Review and take appropriate actions based on the pre-upgrade
DOC> output before opening the datatabase in the new software version.
DOC>
DOC>#######################################################################
DOC>#######################################################################
DOC>#
Session altered.
Table created.
Table altered.
no rows selected
DOC>#######################################################################
DOC>#######################################################################
DOC> The following error is generated if the pre-upgrade tool has not been
DOC> run in the old oracle home prior to upgrading a pre-11.2 database:
DOC>
DOC> SELECT TO_NUMBER('MUST_BE_SAME_TIMEZONE_FILE_VERSION')
DOC> *
DOC> ERROR at line 1:
DOC> ORA-01722: invalid number
DOC>
DOC>
DOC> o Action:
DOC> Shutdown database ("alter system checkpoint" and then "shutdown abort").
DOC> Revert to the original ORACLE_HOME and start the database.
DOC> Run pre-upgrade tool against the database.
DOC> Review and take appropriate actions based on the pre-upgrade
DOC> output before opening the datatabase in the new software version.
DOC>
DOC>#######################################################################
DOC>#######################################################################
DOC>#
no rows selected
DOC>#######################################################################
DOC>#######################################################################
DOC> The following error is generated if (1) the old release uses a time
DOC> zone file version newer than the one shipped with the new oracle
DOC> release and (2) the new oracle home has not been patched yet:
DOC>
DOC> SELECT TO_NUMBER('MUST_PATCH_TIMEZONE_FILE_VERSION_ON_NEW_ORACLE_HOME')
DOC> *
DOC> ERROR at line 1:
DOC> ORA-01722: invalid number
DOC>
DOC> o Action:
DOC> Shutdown database ("alter system checkpoint" and then "shutdown abort").
DOC> Patch new ORACLE_HOME to the same time zone file version as used
DOC> in the old ORACLE_HOME.
DOC>
DOC>#######################################################################
DOC>#######################################################################
DOC>#
no rows selected
DOC>#######################################################################
DOC>#######################################################################
DOC> The following statements will cause an "ORA-01722: invalid number"
DOC> error if the SYSAUX tablespace does not exist or is not
DOC> ONLINE for READ WRITE, PERMANENT, EXTENT MANAGEMENT LOCAL, and
DOC> SEGMENT SPACE MANAGEMENT AUTO.
DOC>
DOC> The SYSAUX tablespace is used in 10.1 to consolidate data from
DOC> a number of tablespaces that were separate in prior releases.
DOC> Consult the Oracle Database Upgrade Guide for sizing estimates.
DOC>
DOC> Create the SYSAUX tablespace, for example,
DOC>
DOC> create tablespace SYSAUX datafile 'sysaux01.dbf'
DOC> size 70M reuse
DOC> extent management local
DOC> segment space management auto
DOC> online;
DOC>
DOC> Then rerun the catupgrd.sql script.
DOC>#######################################################################
DOC>#######################################################################
DOC>#
no rows selected
no rows selected
no rows selected
no rows selected
no rows selected
Session altered.
Table created.
Table created.
0 rows deleted.
Commit complete.
Table created.
17 rows deleted.
1 row created.
Commit complete.
TIMESTAMP
------------------------------------------------------------
COMP_TIMESTAMP UPGRD__BGN 2016-05-17 17:38:24 2457526 63504
Session altered.
Session altered.
Table altered.
Table altered.
Table altered.
Table altered.
Table altered.
0 rows created.
Commit complete.
0 rows deleted.
0 rows deleted.
Commit complete.
Table altered.
Table altered.
Table created.
Index created.
Table created.
Index created.
Table created.
Table altered.
Table altered.
System altered.
Table altered.
Table altered.
Table altered.
Table altered.
Table altered.
no rows selected
0 rows updated.
System altered.
Table created.
Index created.
Table created.
...
...
...
...
...
...
...
...
Oracle Database 11.2 Post-Upgrade Status Tool 05-17-2016 18:05:21
.
Component Current Version Elapsed Time
Name Status Number HH:MM:SS
.
Oracle Server
. VALID 11.2.0.4.0 00:17:58
Oracle Workspace Manager
. VALID 11.2.0.4.0 00:00:40
Oracle Enterprise Manager
. VALID 11.2.0.4.0 00:06:35
Final Actions
. 00:01:42
Total Upgrade Time: 00:26:56
PL/SQL procedure successfully completed.
SQL>
SQL> SET SERVEROUTPUT OFF
SQL> SET VERIFY ON
SQL> commit;
Commit complete.
SQL>
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL>
SQL> DOC
DOC>#######################################################################
DOC>#######################################################################
DOC>
DOC> The above sql script is the final step of the upgrade. Please
DOC> review any errors in the spool log file. If there are any errors in
DOC> the spool file, consult the Oracle Database Upgrade Guide for
DOC> troubleshooting recommendations.
DOC>
DOC> Next restart for normal operation, and then run utlrp.sql to
DOC> recompile any invalid application objects.
DOC>
DOC> If the source database had an older time zone version prior to
DOC> upgrade, then please run the DBMS_DST package. DBMS_DST will upgrade
DOC> TIMESTAMP WITH TIME ZONE data to use the latest time zone file shipped
DOC> with Oracle.
DOC>
DOC>#######################################################################
DOC>#######################################################################
DOC>#
SQL>
SQL> Rem Set errorlogging off
SQL> SET ERRORLOGGING OFF;
SQL>
SQL> REM END OF CATUPGRD.SQL
SQL>
SQL> REM bug 12337546 - Exit current sqlplus session at end of catupgrd.sql.
SQL> REM This forces user to start a new sqlplus session in order
SQL> REM to connect to the upgraded db.
SQL> exit
-bash-4.1$
/ora/app/oracle/product/11.2.0/dbhome1104/bin
-bash-4.1$ ./sqlplus /nolog
SQL*Plus: Release 11.2.0.4.0 Production on Tue May 17 18:07:07 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 1.0292E+10 bytes
Fixed Size 2190488 bytes
Variable Size 1946162024 bytes
Database Buffers 8321499136 bytes
Redo Buffers 21839872 bytes
Database mounted.
Database opened.
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Solaris: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
