Database Upgrade From 11204 to 12101 - Manual Steps

In this article, we will see the complete manual upgrade steps for database upgrade from 11204 to 12101. There is another article DATABASE UPGRADE FROM 11202 TO 12101 USING DBUA which explains the database upgrade using DBUA utility. Manual upgrade helps you to understand the complete upgrade process and gives you complete control on the upgrade process. I am presenting both screen shots and also video demo which helps you to understand the complete upgrade process.

1. Following table gives you the upgrade path reference :

Source Database	Target Database
10.2.0.5	12.1.x
11.1.0.7	12.1.x
11.2.0.2 or higher	12.1.x

2. Complete the backup before proceeding with the upgrade.

3. Run the script dbupgdiag.sql for the integrity of the source database. Resolve any duplicate objects reported by this scripts following the NOTE.1030426.6. Also compile invalid objects if you find during this script using utlrp.sql.

Following commands helps you to check the invalids and database components status before proceeding with the upgrade.

set pagesize500
set linesize 100
select substr(comp_name,1,40) comp_name, status, substr(version,1,10) version from dba_registry order by comp_name;
select substr(object_name,1,40) object_name,substr(owner,1,15) owner,object_type from dba_objects where status='INVALID' order by owner,object_type;
select owner,object_type,count(*) from dba_objects where status='INVALID' group by owner,object_type order by owner,object_type ;

4. Pre-Upgrade Steps

4.1 Download the latest pre-upgrade utility from Note 884522.1 and extract it to any temporary directory. The extracted zip file contains the scripts preupgrd.sql and utluppkg.sql . You need to run the script preupgrd.sql in the source database. This scripts produced two xxx_fixup.sql scripts. Review these two scripts generated and take necessary actions. In my environment it generated 4 suggestions. I am presenting the session log for your reference below.

SQL> @preupgrd.sql
Loading Pre-Upgrade Package...
Executing Pre-Upgrade Checks...
Pre-Upgrade Checks Complete.
************************************************************
Results of the checks are located at:
/u01/app/oracle/cfgtoollogs/ORCL/preupgrade/preupgrade.log
Pre-Upgrade Fixup Script (run in source database environment):
/u01/app/oracle/cfgtoollogs/ORCL/preupgrade/preupgrade_fixups.sql
Post-Upgrade Fixup Script (run shortly after upgrade):
/u01/app/oracle/cfgtoollogs/ORCL/preupgrade/postupgrade_fixups.sql
************************************************************
Fixup scripts must be reviewed prior to being executed.
************************************************************
************************************************************
====>> USER ACTION REQUIRED <<====
************************************************************
The following are *** ERROR LEVEL CONDITIONS *** that must be addressed
prior to attempting your upgrade.
Failure to do so will result in a failed upgrade.
You MUST resolve the above errors prior to upgrade
************************************************************
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@db11204 upgrade]$ vi /u01/app/oracle/cfgtoollogs/ORCL/preupgrade/preupgrade_fixups.sql
[oracle@db11204 upgrade]$ sqlplus '/as sysdba'
SQL*Plus: Release 11.2.0.4.0 Production on Sat Mar 1 00:31:27 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @/u01/app/oracle/cfgtoollogs/ORCL/preupgrade/preupgrade_fixups.sql
Pre-Upgrade Fixup Script Generated on 2014-03-01 00:28:08 Version: 12.1.0.1 Build: 006
Beginning Pre-Upgrade Fixups...
PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

**********************************************************************
Check Tag: DEFAULT_PROCESS_COUNT
Check Summary: Verify min process count is not too low
Fix Summary: Review and increase if needed, your PROCESSES value.
**********************************************************************
Fixup Returned Information:
WARNING: --> Process Count may be too low
Database has a maximum process count of 150 which is lower than the
default value of 300 for this release.
You should update your processes value prior to the upgrade
to a value of at least 300.
For example:
ALTER SYSTEM SET PROCESSES=300 SCOPE=SPFILE
or update your init.ora file.
**********************************************************************

PL/SQL procedure successfully completed.

**********************************************************************
Check Tag: EM_PRESENT
Check Summary: Check if Enterprise Manager is present
Fix Summary: Execute emremove.sql prior to upgrade.
**********************************************************************
Fixup Returned Information:
WARNING: --> Enterprise Manager Database Control repository found in the database
In Oracle Database 12c, Database Control is removed during
the upgrade. To save time during the Upgrade, this action
can be done prior to upgrading using the following steps after
copying rdbms/admin/emremove.sql from the new Oracle home
- Stop EM Database Control:
$> emctl stop dbconsole
- Connect to the Database using the SYS account AS SYSDBA:
SET ECHO ON;
SET SERVEROUTPUT ON;
@emremove.sql
Without the set echo and serveroutput commands you will not
be able to follow the progress of the script.
**********************************************************************

PL/SQL procedure successfully completed.

**********************************************************************
Check Tag: DBMS_LDAP_DEPENDENCIES_EXIST
Check Summary: Check for dependency on DBMS_LDAP package
Fix Summary: Network Objects must be reviewed manually.
**********************************************************************
Fixup Returned Information:
WARNING: --> Existing DBMS_LDAP dependent objects
Database contains schemas with objects dependent on DBMS_LDAP package.
Refer to the Upgrade Guide for instructions to configure Network ACLs.
USER APEX_030200 has dependent objects.
**********************************************************************

PL/SQL procedure successfully completed.

**********************************************************************
Check Tag: AMD_EXISTS
Check Summary: Check to see if AMD is present in the database
Fix Summary: Manually execute ORACLE_HOME/oraolap/admin/catnoamd.sql script to remove OLAP.
**********************************************************************
Fixup Returned Information:
INFORMATION: --> OLAP Catalog(AMD) exists in database
Starting with Oracle Database 12c, OLAP Catalog component is desupported.
If you are not using the OLAP Catalog component and want
to remove it, then execute the
ORACLE_HOME/olap/admin/catnoamd.sql script before or
after the upgrade.
**********************************************************************

PL/SQL procedure successfully completed.

**********************************************************************
[Pre-Upgrade Recommendations]
**********************************************************************

PL/SQL procedure successfully completed.
*****************************************
********* Dictionary Statistics *********
*****************************************
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;
^^^ MANUAL ACTION SUGGESTED ^^^

PL/SQL procedure successfully completed.

**************************************************
************* Fixup Summary ************
4 fixup routines generated INFORMATIONAL messages that should be reviewed.

PL/SQL procedure successfully completed.
**************** Pre-Upgrade Fixup Script Complete *********************
PL/SQL procedure successfully completed.
SQL>

If there are any suggestions returned by this script specific to your environment, you need to complete all those recommendations.

NOTE : You will also get preupgrd.sql script from the $ORACLE_HOME/rdbms/admin directory of the 12c home, if you already have installed by this time.

4.2 Check if you have any other privileges assigned to CONNECT role.(applicable only to 9.2 and 10.1)

SQL> SELECT grantee FROM dba_role_privs WHERE granted_role = 'CONNECT' and grantee NOT IN ( 'SYS', 'OUTLN', 'SYSTEM', 'CTXSYS', 'DBSNMP', 'LOGSTDBY_ADMINISTRATOR', 'ORDSYS', 'ORDPLUGINS', 'OEM_MONITOR', 'WKSYS', 'WKPROXY', 'WK_TEST', 'WKUSER', 'MDSYS', 'LBACSYS', 'DMSYS', 'WMSYS', 'EXFSYS', 'SYSMAN', 'MDDATA', 'SI_INFORMTN_SCHEMA', 'XDB', 'ODM');

4.3 Check if you have any dependencies on Network Utility Packages

SQL> SELECT * FROM DBA_DEPENDENCIES
WHERE referenced_name IN ('UTL_TCP','UTL_SMTP','UTL_MAIL','UTL_HTTP','UTL_
INADDR','DBMS_LDAP')
AND owner NOT IN ('SYS','PUBLIC','ORDPLUGINS');

4.4 Collect database links information. This will help incase if there is any need to downgrade your database for any foreseen issues. We will not be able to recover the encrypted passwords during downgrade.

SQL> SELECT 'CREATE '||DECODE(U.NAME,'PUBLIC','public ')||'DATABASE LINK '||CHR(10)
||DECODE(U.NAME,'PUBLIC',Null, 'SYS','',U.NAME||'.')|| L.NAME||chr(10)
||'CONNECT TO ' || L.USERID || ' IDENTIFIED BY "'||L.PASSWORD||'" USING
'''||L.HOST||''''
||chr(10)||';' TEXT
FROM SYS.LINK$ L, SYS.USER$ U
WHERE L.OWNER# = U.USER#;

4.5 Verify the time zone.

Verify the time zone in your environment. The latest 12c versions brings time zone 18. If you plan to upgrade the time zone as part of your upgrade, refer the Note 1509653.1.

SELECT version FROM v$timezone_file;

4.6 Run following command to gather statistics in advance to avoid downtime during upgrade.

SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

4.7 Verify that the MVs have completed their refresh before upgrade.

SQL> select s.obj#,o.obj#,s.containerobj#,lastrefreshdate,pflags,xpflags,o.name,o.owner#, bitand(s.mflags, 8) from obj$ o, sum$ s where o.obj# = s.obj# and o.type# = 42 AND bitand(s.mflags, 8) = 8;

4.8 Ensure that no datafiles are in recovery mode and in backup mode.

SQL> SELECT * FROM v$recover_file;
SQL> SELECT * FROM v$backup WHERE status != 'NOT ACTIVE';

4.9 If your database is OLTP and have distributed transactions runnings, make sure that the following query returns 0 rows.

SQL> SELECT * FROM dba_2pc_pending;

If you see any output for above query, use the following queries to resolve.

SQL> SELECT local_tran_id FROM dba_2pc_pending;
SQL> EXECUTE dbms_transaction.purge_lost_db_entry('');
SQL> COMMIT;

4.10 Purge recyclebin. Because the downtime during upgrade depends on the number of objects present in recyclebin. Also make sure all the cronjobs and any automated jobs are disabled till the completion of the upgrade.

SQL> PURGE DBA_RECYCLEBIN

4.11 Verify that the sys and system users have only SYSTEM as default tablespace using the following command.

SQL> SELECT username, default_tablespace FROM dba_users WHERE username in ('SYS','SYSTEM');

4.12 Verify auditing tables data. The large auditing data also effects the upgrade downtime. So if you don't need auditing data in the upgraded database, purge the data using following commands.

SQL> SELECT owner,tablespace_name FROM dba_tables WHERE table_name='AUD$';
SQL> truncate table sys.aud$;
SQL> truncate table sys.fga_log$;

4.13 Remove the enterprise manager database control. 12c database is desupported for database control. It has got new tool, oracle enterprise manager express control. This also amounts to downtime for database upgrade. So please remove database control before starting the upgrade.

$emctl stop dbcontrol
SQL> @ ?/rdbms/admin/emremove.sql

5. Install 12c binaries

Please follow the video to install the 12c binaries. Choose software only install for 12c database installation. We will use these binaries to upgrade the existing database.

6. Start Upgrade

6.1 Copy the spfile or pfile from 11g oracle home to 12c home created in step 5.

6.2 Set 11g home and stop the listener from 11g home. Now set the 12c home, and start netca from 12c home and create a new listener. Verify that the new listener is started from new 12c home. Refer video for more information on how to create listener using netca.

6.3 Set 11g home and stop the database. Now set 12c home and start the database in upgrade mode.

SQL> shutdown immediate; <=== From 11g home

$ export ORACLE_HOME=<location of Oracle 12.1>
$ export PATH=$ORACLE_HOME/bin:$PATH
$ export ORACLE_BASE=<Oracle_Base set during installation>
$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus "/ as sysdba"
SQL> startup UPGRADE <== From 12c home
SQL> exit

6.4 Run the upgrade script catctl.pl

cd $ORACLE_HOME/rdbms/admin
$ORACLE_HOME/perl/bin/perl catctl.pl -n 6 -l $ORACLE_HOME/diagnostics catupgrd.sql

6.5 Run the Post-Upgrade Status Tool $ORACLE_HOME/rdbms/admin/utlu121s.sql which provides a summary of the upgrade at the end of the spool log.

$ sqlplus "/as sysdba"
SQL> STARTUP
SQL> @utlu121s.sql

6.6 Verify the invaild objects and run the utlrp.sql scripts for resolve any invalid objects. Also run dbupgdiag.sql to collect upgrade information.

7.Post upgrade Steps

Change the profile value for oracle user to reflect the new oracle home.
Remove any obsolete parameters from the spfile/pfile. Also set the new compatibility parameter to 12.0.0.
Create new password file.
Upgrade the recovery catalog after database upgrade.
Upgrade time zone

8.Final verification steps

set pagesize500
set linesize 100
select substr(comp_name,1,40) comp_name, status, substr(version,1,10) version from dba_registry order by comp_name;
select substr(object_name,1,40) object_name,substr(owner,1,15) owner,object_type from dba_objects where status='INVALID' order by owner,object_type;
select owner,object_type,count(*) from dba_objects where status='INVALID' group by owner,object_type order by owner,object_type ;
select name,user,created,CDB,CON_ID,CON_DBID from v$database;
select instance_name,status from v$instance;
SELECT version FROM v$timezone_file;
SELECT dbms_xdb_config.gethttpsport FROM dual;
Database enterprise manager express control url : https://<database-ip>:5500/em

askMLabs