Oracle Database Upgrade Process
1) Must install the new Oracle version in separate ORACLE_HOME to which we are upgrading the database.
2) Both the old Oracle Version and New/Upgraded Oracle Version software must be installed properly before starting the upgrade.
3) Take the backup of database before starting the upgrade.
4) Before starting the upgrade Run the pre upgrade script from new ORACLE_HOME/rdbms/admin
utlu112i.sql 11.2
5) Run the database upgrade from New Oracle Home using
DBUA or catupgrd.sql (Manual Upgrade )
6) Once the upgrade is complete validate the upgrade using Post Upgrade Script from new ORACLE_HOME/rdbms/admin/
utlu112s.sql 11.2
Step:-1
My 11gr1 or 11gr2 Oracle_home path
11gr1 ORACLE_HOME=/u01/app/oracle/product/11gr1
11gr1 ORACLE_SID=orcl
11gr2 ORACLE_HOME=/u01/app/oracle/product/11gr2
Step:-2
Preupgrade steps :-
Run the utlu112i.sql in 11GR1 oracle home.
SQL> @/u04/app/cognos/product/11.2.0/dbhome_2/rdbms/admin/utlu112i.sql
Step:-3
Purge recyclebin of database.
SQL> PURGE DBA_RECYCLEBIN;
DBA Recyclebin purged.
Step:-4
Gather Schema and Database statistics
SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
PL/SQL procedure successfully completed.
Step:-5
SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SYS');
PL/SQL procedure successfully completed.
SQL> !
Step:-6
Shutdown 11gr1 database.
SQL> shut immediate
Step:-7
Take a complete backup of datafiles,controlfiles, redo logfiles, Spfile/pfile and password file.
The complete backup of database is needed to revert back if the Database upgrade fails.
Upgrading Database to 11GR2
(Two Methods to do the actual Database Upgrade, either use DBUA
Post Upgrade Steps
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
****************************END***********************************
1) Must install the new Oracle version in separate ORACLE_HOME to which we are upgrading the database.
2) Both the old Oracle Version and New/Upgraded Oracle Version software must be installed properly before starting the upgrade.
3) Take the backup of database before starting the upgrade.
4) Before starting the upgrade Run the pre upgrade script from new ORACLE_HOME/rdbms/admin
utlu112i.sql 11.2
5) Run the database upgrade from New Oracle Home using
DBUA or catupgrd.sql (Manual Upgrade )
6) Once the upgrade is complete validate the upgrade using Post Upgrade Script from new ORACLE_HOME/rdbms/admin/
utlu112s.sql 11.2
Step:-1
My 11gr1 or 11gr2 Oracle_home path
11gr1 ORACLE_HOME=/u01/app/oracle/product/11gr1
11gr1 ORACLE_SID=orcl
11gr2 ORACLE_HOME=/u01/app/oracle/product/11gr2
Step:-2
Preupgrade steps :-
Run the utlu112i.sql in 11GR1 oracle home.
Copy the preupgrade script to any temp location for example /u01/app/oracle
cp /u01/app/oracle/product/11gr2/rdbms/admin/utlu112i.sql /u01/app/oracle
cd /u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/11gr1
export ORACLE_SID=orcl
If you are upgrading to 11gr2 run below script
@utlu112i.sql
Step:-3
Purge recyclebin of database.
SQL> PURGE DBA_RECYCLEBIN;
DBA Recyclebin purged.
Step:-4
Gather Schema and Database statistics
SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
PL/SQL procedure successfully completed.
Step:-5
SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SYS');
PL/SQL procedure successfully completed.
SQL> !
Step:-6
Shutdown 11gr1 database.
SQL> shut immediate
Step:-7
Take a complete backup of datafiles,controlfiles, redo logfiles, Spfile/pfile and password file.
The complete backup of database is needed to revert back if the Database upgrade fails.
Step:-8
Below we are spooling to a log file so that we have the pre upgrade validations stored in upgrade_info.log file that we can refer later.
sql> spool upgrade_info.log
sql> @utlu112i.sql
sql> spool off
The Pre upgrade script suggest any database changes that needs to be done before starting the Database Upgrade using DBUA or catupgrd.sql Script.
The pre upgrade script validates following information:
- Database version.
- Tablespace sizes.
- Updated, renamed and deprecated initialization parameters.
- Init.ora or Spfile Parameters that needs to be resize before starting the actual upgrade. ( This step should not be ignored)
- Database Components that will be upgraded or installed
- SYSAUX tablespace present (if missing).
- Miscellaneous Warnings
- Warning for Old Timezone
- Warning for Stale Optimizer Statistics
- Warning for EM Database Control Repository
Note: Each of the warning suggested by Pre Upgrade tool must be fixed.
Fix all the issues reported by Pre-upgrade script before starting DBUA
Step:-5(Two Methods to do the actual Database Upgrade, either use DBUA
or catupgrd.sql (manual method) which has many steps but it is more flexible)
Oracle recommends using DBUA to upgrade database.
export ORACLE_SID=orcl
Run the DBUA from New 11gr2 ORACLE_HOME/bin
$ cd /u01/app/oracle/product/11gr2/bin
$./dbua
Once the DBUA (Database Upgrade Assistant) GUI starts. Follow below points to upgrade the database. DBUA takes about 60 minutes to complete even for very large databases.
- Select the Database that needs to be upgraded.
- If the database name that needs to be upgraded is not present in DBUA,
Add the entry $ORACLE_SID:$ORACLE_HOME:N to /etc/oratab
For Example: ora11g:/u01/app/oracle/product/orcl:N
Note: The $ORACLE_HOME should be the old oracle home that is getting upgraded.
- Oracle 11g requires the “Diagnostic Destination”. Set diagnostic destination to oracle base.
- Select “Do not move Database as part of upgrade”
- Do not specify Flash Recovery Area option
- Select “Configure database with Enterprise Manager” or Database Control if required.
- Select “Recompile Invalid objects at the end of upgrade” options in dbua
- Do not select the backup option if we have already have taken backup before starting Database Upgrade, Also it is not needed if we already have RMAN backup.
Post Upgrade Steps
Once the upgrade is complete modify the profile file to use the new ORACLE_HOME
Run the post upgrade script to validate the upgrade using new ORACLE_HOME
SQL> @$ORACLE_HOME/rdbms/admin/utlu112s.sql
SQL> @/u01/app/oracle/product/11gr2/rdbms/admin/utlu112s.sql
SQL> select * from v$version;BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
****************************END***********************************