Database upgrade from 11.2.0.3 to 12.1.0.2 Using DBUA (HP-UX
Itanium)
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.
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.
