Source database is 10.2.0.1, O/S is Windows server 2003 64 bit
On destination server installed oracle database software 11.2.0.1, O/S is Windows server 2012 64 bit
Note : direct upgrade between these versions is not possible, see Oracle Documentation http://docs.oracle.com/cd/E11882_01/server.112/e23633/preup.htm#UPGRD12358, hence we need to upgrade source database version from 10.2.0.1 to 10.2.0.2 or(higher)
after applying patchset 10.2.0.5, we can continue upgrading to 11g on another host.
The steps for 10g database:
1) Run the utlu112i.sql Pre-upgrade script. You can find this script under @%ORACLE_HOME%\rdbms\admin\. It must be copied from the 11g database software.
SQL> @%ORACLE_HOME%\rdbms\admin\utlu112i.sql
it performs following operation
SQL> ALTER TABLE registry$database ADD (tz_version NUMBER); SQL> UPDATE registry$database set tz_version =4; SQL> ALTER PACKAGE “SYS”.”DBMS_REGISTRY” COMPILE BODY; SQL> ALTER VIEW “SYS”.”DBA_REGISTRY_DATABASE” COMPILE;
2) Connect to 10g database and take RMAN full backup.
run {
ALLOCATE CHANNEL RMAN_BACK_CH01 TYPE DISK;
backup tag test_full database format "E:\databasefiles_%d_%u_%s_%T";
sql 'ALTER SYSTEM ARCHIVE LOG CURRENT';
BACKUP archivelog all format "E:\archivelogs_%d_%u_%s_%T";
backup current controlfile format "E:\control01.cf";
RELEASE CHANNEL RMAN_BACK_CH01;
}3) Copy 10g database backup files and archive files to 11g database server.
The steps for 11g database:
1) Create temporary pfile in $ORACLE_HOME$\dbs and change path of directories parameters as you have.
2) Open the database in NOMOUNT state.
rman target / RMAN> startup nomount;
3) Restore controlfile from backup.
RMAN> restore controlfile from 'D:\backup\control01.cf';
4) Open the database in MOUNT state.
RMAN> alter database mount;
5) Make crosscheck backup and archive log, for delete expireds, Catalog RMAN backup files and archive log files.
RMAN> crosscheck backup; RMAN> crosscheck archivelog all; RMAN> delete expired backup; RMAN> delete expired archivelog all; RMAN> catalog start with 'D:\backup';
6) restore and recover 10g database backup
RMAN> run
{
allocate channel c1 device type disk FORMAT 'D:\backup';
SET NEWNAME FOR DATAFILE 1 TO ‘D:\oradata\sd01\SYSTEM01.DBF';
SET NEWNAME FOR DATAFILE 2 TO ‘D:\oradata\sd01\UNDOTBS01.DBF';
SET NEWNAME FOR DATAFILE 3 TO ‘D:\oradata\sd01\SYSAUX01.DBF';
SET NEWNAME FOR DATAFILE 4 TO ‘D:\oradata\sd01\USERS01.DBF';
SET NEWNAME FOR DATAFILE 5 TO ‘D:\oradata\sd01\USERS02.DBF';
SET NEWNAME FOR DATAFILE 6 TO ‘D:\oradata\sd01\USERS03.DBF';
SET NEWNAME FOR DATAFILE 7 TO ‘D:\oradata\sd01\USERS04.DBF';
restore database;
switch datafile all;
recover database;
}7) open database with upgrade mode
SQL> shutdown immediate; SQL> startup upgrade;
8) Run the upgrade script
SQL> SPOOL upgradelog.log SQL> @%ORACLE_HOME%\rdbms\admin\catupgrd.sql SQL> SPOOL off
9) If our 10g and 11g database os platforms are different then you must run utlmmig.sql script
SQL> SHUTDOWN IMMEDIATE SQL> STARTUP UPGRADE SQL> SPOOL upgradelog.log SQL> @%ORACLE_HOME%\rdbms\admin\utlmmig.sql SQL> SPOOL off
10) open the database
SQL> SHUTDOWN IMMEDIATE SQL> STARTUP
11) Run the Post-Upgrade script
SQL> @%ORACLE_HOME%\rdbms\admin\utlu112s.sql
12) Compile invalid objects
SQL> @%ORACLE_HOME%\rdbms\admin\utlrp.sql
that's all, have a good upgrade.
