Thursday, 25 July 2013

Upgrade Oracle Database using DBUA

Upgrade Oracle Database using DBUA

Steps to Upgrade an Oracle database
11- Is a major database release number
2- Is a database maintenance release number
0- Is a application server release number
1- Is a component specific release number/Patchset number
0- Is a platform specific release number

If you upgrade/migrate the database, the FIRST number and the SECOND number will changes. TheFOURTH digit indicate the patchset number, this number will change whenever you apply the patchsets to the database.
We can use below steps to upgrade from below Oracle versions to a higher Oracle Version (10gr1, 10gr2, 11gr1 and 11gr2) or higher release   -> 10gr1, 10gr2, 11gr1 and 11gr2 or higher            ->   10gr2, 11gr1 and 11gr2 or higher            ->   11gr1 and 11gr2 or higher            ->   11gr2 and 12c

Oracle Upgrade Summary
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
utlu111i.sql      11.1
utlu102i.sql      10.2
utlu101i.sql      10.1
5)  Run the database upgrade from New Oracle Home using
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
utlu111s.sql      11.1
utlu102s.sql      10.2
utlu101s.sql      10.1

Oracle Upgrade Detailed Steps
Note: The steps in this document can be used to upgrade a 9i/10gr1/10gr2/11gr1 to 10gr1/10gr2/11gr1/11gr2.
In this document we are upgrading from 11gr1 to 11gr2 so both software must be installed and 11gr1 database with ORACLE_SID=orcl must be present before we start the upgrade.
Our Example will use
11gr1 ORACLE_HOME=/u01/app/oracle/product/11g
11gr1 ORACLE_SID=orcl
11gr2 ORACLE_HOME=/u01/app/oracle/product/11gr2

1) Shutdown the database
2) Take a comple 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.

A) Pre upgrade Steps
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/11g
export ORACLE_SID=orcl
If you are upgrading to 11gr2 run below script
If you are upgrading to 11gr1 run below script
@ utlu111i.sql
If you are upgrading to 10gr2 run below script
@ utlu102i.sql

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

B) Upgrade Steps
(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.
1. Login in as Oracle 11g user, and initiate all the variables need for the environment
$ export ORACLE_BASE=/u01/app/o11g
$ export ORACLE_HOME=/u01/app/o11g/product/11.1.0/db_1
$ export PATH=$ORACLE_HOME/bin:$PATH
2. Confirm the Oracle 10g database name and ORACLE_HOME path is entered in /etc/oratab file
$ cat /etc/oratab
3. Execute the dbca utility from Oracle 11g’s path
$ $ORACLE_HOME/bin/dbua
export ORACLE_SID=orcl
Run the DBUA from New 11gr2 ORACLE_HOME/bin
$ cd /u01/app/oracle/product/11gr2/bin
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.
C) 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

This completes the Oracle upgrade from 11gr1 to 11gr2 for orcl database!!!!

Miscellaneous Commands that may be required before running DBUA
Sometimes the Pre Upgrade tool (utlu112i.sql) complains that some pre steps needs to be done before starting the Database Upgrade. Few of these Miscellaneous Warnings can be fixed by using below commands:


exec dbms_stats.gather_schema_stats(‘SYS‘,options=>’GATHER’, -
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, -
method_opt => ‘FOR ALL COLUMNS SIZE AUTO’, cascade => TRUE);
exec dbms_stats.gather_schema_stats(‘WKSYS‘,options=>’GATHER’, -
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, -
method_opt => ‘FOR ALL COLUMNS SIZE AUTO’, cascade => TRUE);
exec dbms_stats.gather_schema_stats(‘SYSMAN‘,options=>’GATHER’, -
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, -
method_opt => ‘FOR ALL COLUMNS SIZE AUTO’, cascade => TRUE);
exec dbms_stats.gather_schema_stats(‘OLAPSYS‘,options=>’GATHER’, -
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, -
method_opt => ‘FOR ALL COLUMNS SIZE AUTO’, cascade => TRUE);

No comments:

Post a Comment