Database upgrade from 11.2.0.3 to 12.1.0.2 Using DBUA (HP-UX Itanium)

PRE- UPGRADE STEPS:

Old ORACLE_HOME = /vac/app/oracle/product/11.2.0.3

New ORACLE_HOME = /vac/app/oracle/product/12.1.0

1. Install the 12.1.0 in a new Home Location using runInstaller.

2. Execute  Pre upgrade Information gathering Script:

Log in to the system as the owner of the Oracle Database 12.1.0 Oracle home directory.

Copy the Pre-Upgrade Information Tool ( preupgrd.sql & utluppkg.sql ) from the Oracle Database 12.1.0

$ORACLE_HOME/rdbms/admin directory to a directory outside of the Oracle home, such as the temporary directory on your system.

In this case : /vac/orabkup01/preupgrd.sql & /vac/orabkup07/utluppkg.sql

Note  : Note that the new preupgrd.sql script replaces the utlu121i.sql script  and earlier versions of the Pre-Upgrade Information Tool.

  The Pre-Upgrade Information Tool, which now consists of preupgrd.sql  and utluppkg.sql.

  1. Make sure preupgrd.sql and utluppkg.sql (located in the admin directory of the new installation of Oracle Database 12.1) are accessible while connected to your source database, which is the database to be upgraded.

  2. Connect to your source database using an account with DBA privileges and execute the preupgrd.sql script. For more information on the Pre-Upgrade Information Tool, see the Oracle Database Upgrade Guide.

3. Execute Pre Upgrade Script:

Execute preupgrd.sql  from where it had been copied in the previous step. Start SQL*Plus and connect to the database instance as a user with SYSDBA privileges. Then run and spool the preupgrd.sql file. Please note that the database should be started using the Source Oracle Home.

$ sqlplus '/ as sysdba' (11g Oracle_HOme)

SQL> spool upgrade_info.log

SQL> @preupgrd.sql

SQL> spool off

4. Pre-Upgrade Checks[Errors]

ERROR: --> Invalid Oracle supplied table data found in your database. Invalid data can be seen prior to the database upgrade or during PDB plug in.  This table data must be made valid BEFORE upgrade or plug in.

   - To fix the data, load the Preupgrade package and execute the fixup routine.

     For plug in, execute the fix up routine in the PDB.

    @?/rdbms/admin/utluppkg.sql

    SET SERVEROUTPUT ON;

    exec dbms_preup.run_fixup_and_report('INVALID_SYS_TABLEDATA')

    SET SERVEROUTPUT OFF;

Fix:

SQL> @/vac/app/oracle/product/11.2.0.3/rdbms/admin/utluppkg.sql

Package created.

Package body created.

SQL> SET SERVEROUTPUT ON;

SQL> exec dbms_preup.run_fixup_and_report('INVALID_SYS_TABLEDATA')

**********************************************************************

Check Tag:     INVALID_SYS_TABLEDATA

Check Summary: Check for invalid (not converted) table data

Fix Summary:   UPGRADE Oracle supplied table data prior to the database upgrade.

**********************************************************************

Fixup Succeeded

**********************************************************************

PL/SQL procedure successfully completed.

SQL> SET SERVEROUTPUT OFF;

SQL> SELECT rpad(u.name,128) TABLENAME, rpad(o.name,128) OWNER,

  rpad(c.name,128) COLNAME FROM SYS.OBJ$ o, SYS.COL$ c, SYS.COLTYPE$ t,

SYS.USER$ u

WHERE o.OBJ# = t.OBJ# AND c.OBJ# = t.OBJ# AND c.COL# = t.COL#

AND t.INTCOL# = c.INTCOL# AND BITAND(t.FLAGS, 256) = 256

AND o.OWNER# = u.USER# AND o.OWNER# NOT IN

(SELECT UNIQUE (d.USER_ID) FROM SYS.DBA_USERS d, SYS.REGISTRY$ r

WHERE d.USER_ID = r.SCHEMA# and r.NAMESPACE='SERVER'); 

no rows selected

Note : Refer Metalink Doc ID 2009405.1 for more information on this error and fix.

5.Pre-Upgrade Recommendations

Please gather dictionary statistics 24 hours prior to upgrading the database. To gather dictionary statistics execute the following command while connected as SYSDBA:

    EXECUTE dbms_stats.gather_dictionary_stats;

6. Check for the integrity of the source database:

 If the dbupgdiag.sql script reports any invalid objects, run $ORACLE_HOME/rdbms/admin/utlrp.sql (multiple times) to validate the invalid objects in the

             ====>> PRE-UPGRADE RESULTS for   <<====

ACTIONS REQUIRED:

1. Review results of the pre-upgrade checks:

 /vac/app/oracle/cfgtoollogs/ /preupgrade/preupgrade.log

2. Execute in the SOURCE environment BEFORE upgrade:

 /vac/app/oracle/cfgtoollogs/ /preupgrade/preupgrade_fixups.sql

3. Execute in the NEW environment AFTER upgrade:

 /vac/app/oracle/cfgtoollogs/ /preupgrade/postupgrade_fixups.sql

**********************************************************************

                   ************  Summary  ************

 0 ERRORS exist in your database.

 3 WARNINGS that Oracle suggests are addressed to improve database performance.

 1 INFORMATIONAL message that should be reviewed prior to your upgrade.

 After your database is upgraded and open in normal mode you must run

 rdbms/admin/catuppst.sql which executes several required tasks and completes

 the upgrade process.

 You should follow that with the execution of rdbms/admin/utlrp.sql, and a

 comparison of invalid objects before and after the upgrade using

 rdbms/admin/utluiobj.sql

 If needed you may want to upgrade your timezone data using the process

 described in My Oracle Support note 1509653.1

                   ***********************************

Run utlrp.sql run validate invalid objects in Database before upgarde.

$ cd $ORACLE_HOME/rdbms/admin

$ sqlplus "/ as sysdba"

SQL> @utlrp.sql

 7. Check that National Characterset:

Check that National Characterset (NLS_NCHAR_CHARACTERSET) is UTF8 and AL16UTF16.

select value from NLS_DATABASE_PARAMETERS where parameter = 'NLS_NCHAR_CHARACTERSET';

If this is UTF8 or AL16UTF16 then no action is needed.

If is not UTF8 or AL16UTF16 then refer the following article

SQL> select value from NLS_DATABASE_PARAMETERS where parameter = 'NLS_NCHAR_CHARACTERSET';

VALUE

----------------------------------------

AL16UTF16

 8. Ensure that no files need media recovery:

Sql> SELECT * FROM v$recover_file;

9. Set New ORACLE_HOME

export ORACLE_HOME= /vac/app/oracle/product/12.1.0

export PATH=$ORACLE_HOME/bin:$PATH

Start Database Upgrade assistant to upgrade database.

[ /vac/app/oracle/product/12.1.0/bin ]

nh1d0t04:SID= =>./dbua

Select target database oracle home and SID to be upgrade.

Database SID upgraded below à    

Select ‘Ugrade Oracle Database’ if upgrade from same location or select the other if you want to move Oracle home.

Since below warnings can be fixed post-upgrade ignored here. Ignore if not necessary to address or else take necessary action.

Select parallelism for upgrade/Invalid objects parallelism.

Also Upgrade Time zone Date, this is recommended by oracle to upgrade. If you check below box for Time Zone upgrade, it’ll upgrade from version 14 to 18.  If you forget to check box during upgrade, we can do it after the upgrade manually. Check Oracle Metalink for steps to update time zone manually.  

Select if you want to configure OEM during upgrade. 

Select if you want to upgrade the existing 11g listener or create new one using below options.

Upgrade finished successfully.

POST Upgrade VALIDATION:

Setlines 200

SQL> select * from v$version;

BANNER                                                                               CON_ID

-------------------------------------------------------------------------------- ----------

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production              0

PL/SQL Release 12.1.0.2.0 - Production                                                    0

CORE    12.1.0.2.0      Production                                                                0

TNS for HPUX: Version 12.1.0.2.0 - Production                                             0

NLSRTL Version 12.1.0.2.0 - Production                                                    0

SQL> column COMP_NAME format a35;

column VERSION format a10;

column status format a10;

SQL> select COMP_NAME, VERSION,status from dba_registry;

COMP_NAME                           VERSION    STATUS

----------------------------------- ---------- ----------

Oracle Database Catalog Views       12.1.0.2.0 VALID

Oracle Database Packages and Types  12.1.0.2.0 VALID

Oracle Workspace Manager            12.1.0.2.0 VALID

JServer JAVA Virtual Machine        12.1.0.2.0 VALID

Oracle XDK                          12.1.0.2.0 VALID

Oracle Database Java Packages       12.1.0.2.0 VALID

Oracle XML Database                 12.1.0.2.0 VALID

col SOURCE_VERSION for a40

col TARGET_VERSION for a40

set lines 200

SELECT to_char(action_time, 'DD-MON-YYYY HH:MI:SS AM') upgrade_date,comments source_version, version Target_Version FROM dba_registry_history WHERE action = 'UPGRADE';

UPGRADE_DATE            SOURCE_VERSION                           TARGET_VERSION

----------------------- ---------------------------------------- ----------------------------------------

17-MAY-2009 01:07:00 AM Upgraded from 10.2.0.3.0                 10.2.0.4.0

31-JAN-2018 12:54:45 PM Upgraded from 10.2.0.4.0                 11.2.0.3.0

DBA_REGISTRY_HISTORY Does Not Get Updated on Database Upgraded to 12.1.0.2 (Doc ID 1941383.1)

Note -: Create New Listener from new oracle home and register database with New Listener.