11.2.0.4 DATABASE UPGRADE TO 12C

Test database is called oracle12c for this upgrade..

The utlu112i.sql script which was run in 11g as the pre-upgrade check script has been discontinued.

The new script is called preupgrd.sql - only available in the 12c

In 12c $ORACLE_HOME/rdbms/admin area

Launch sqlplus / as sysdba from 11g database and run this script – preupgrd.sql

cd /u01/oracle/product/12.1.0.1/db_1/rdbms/admin

. /oraenv

oracle12c - in the old 11g environment.

sqlplus / as sysdba

SQL> @preupgrd.sqlLoading Pre-Upgrade Package...Executing Pre-Upgrade Checks...Pre-Upgrade Checks Complete. ************************************************************Results of the checks are located at: /u01/oracle/cfgtoollogs/oracle12c/preupgrade/preupgrade.logPre-Upgrade Fixup Script (run in source database environment): /u01/oracle/cfgtoollogs/ oracle12c/preupgrade/preupgrade_fixups.sqlPost-Upgrade Fixup Script (run shortly after upgrade): /u01/oracle/cfgtoollogs/ oracle12c/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

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

View the preupgrade.log –

View /u01/oracle/cfgtoollogs/oracle12c/preupgrade/preupgrade.log

Oracle Database Pre-Upgrade Information Tool 02-27-2014 12:57:53Script Version: 12.1.0.1.0 Build: 006********************************************************************** Database Name: oracle12c Version: 11.2.0.4.0 Compatible: 11.2.0.0.0 Blocksize: 8192 Platform: AIX-Based Systems (64-bit) Timezone file: V14********************************************************************** [Renamed Parameters] [No Renamed Parameters in use]******************************************************************************************************************************************** [Obsolete/Deprecated Parameters] [No Obsolete or Desupported Parameters in use]********************************************************************** [Component List]**********************************************************************--> Oracle Catalog Views [upgrade] VALID --> Oracle Packages and Types [upgrade] VALID --> JServer JAVA Virtual Machine [upgrade] VALID --> Oracle XDK for Java [ upgrade] VALID --> Oracle Workspace Manager [upgrade] VALID --> OLAP Analytic Workspace [upgrade] OPTION OFF--> Oracle Text [upgrade] VALID --> Oracle XML Database [upgrade] VALID --> Oracle Java Packages [upgrade] VALID --> Oracle Multimedia [upgrade] VALID --> Oracle Spatial [upgrade] VALID --> Expression Filter [upgrade] VALID --> Rule Manager [upgrade] VALID --> Oracle Application Express [upgrade] VALID --> Oracle OLAP API [upgrade] OPTION OFF********************************************************************** [Tablespaces]**********************************************************************

--> SYSTEM tablespace is adequate for the upgrade. minimum required size: 1487 MB--> SYSAUX tablespace is adequate for the upgrade. minimum required size: 2203 MB--> UNDOTBS1 tablespace is adequate for the upgrade. minimum required size: 400 MB--> TEMP tablespace is adequate for the upgrade. minimum required size: 60 MB--> MGMT_TABLESPACE tablespace is adequate for the upgrade. minimum required size: 4415 MB

[No adjustments recommended]

******************************************************************************************************************************************** [Flashback Information]**********************************************************************--> name: /u03/oradata/flash_recovery_area--> limit: 8244 MB--> used: 6081 MB--> size: 8244 MB--> reclaim: 0 MB--> files: 12

WARNING: --> Flashback Database is enabled. Ensure adequate disk space exists in the flash recovery area before performing the upgrade. Your recovery area will require at least 0 MB - or greater - of free space.******************************************************************************************************************************************** [Pre-Upgrade Checks]**********************************************************************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.

WARNING: --> Database contains INVALID objects prior to upgrade

The list of invalid SYS/SYSTEM objects was written to registry$sys_inv_objs. The list of non-SYS/SYSTEM objects was written to registry$nonsys_inv_objs unless there were over 5000. Use utluiobj.sql after the upgrade to identify any new invalid objects due to the upgrade.

INFORMATION: --> OLAP Catalog(AMD) exists in database

Starting with Oracle Database 12c, OLAP 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.

INFORMATION: --> Older Timezone in use

Database is using a time zone file older than version 18. After the upgrade, it is recommended that DBMS_DST package be used to upgrade the 11.2.0.4.0 database time zone version to the latest version which comes with the new release. Please refer to My Oracle Support note number 977512.1 for details.

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

***************************************** ********* 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 ^^^

***************************************** ************ Existing Events ************ *****************************************

Please review any defined events prior to upgrading.

To view existing nondefault events execute the following commands while connected AS SYSDBA: Events: SELECT (translate(value,chr(13)||chr(10),'' '')) FROM sys.v$parameter2 WHERE UPPER(name) =''EVENT'' AND isdefault=''FALSE'';

Trace Events: SELECT (translate(value,chr(13)||chr(10),'' '')) from sys.v$parameter2 WHERE (UPPER(name) = ''_TRACE_EVENTS'') AND isdefault=''FALSE'';

Changes will need to be made in the init.ora or spfile.

^^^ MANUAL ACTION SUGGESTED ^^^

The Pre-Upgrade Fixup Script will display the Existing Events currently defined

********************************************************************** [Post-Upgrade Recommendations]**********************************************************************

***************************************** ******** Fixed Object Statistics ******** *****************************************

Please create stats on fixed objects two weeks after the upgrade using the command:

EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

^^^ MANUAL ACTION SUGGESTED ^^^

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

0 ERRORS exist in your database. 3 WARNINGS that Oracle suggests are addressed to improve database performance. 2 INFORMATIONAL messages 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 977512.1

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

STEPS RUN BEFORE COMMENCING UPGRADE.

1. REMOVE OLAP.

From 12c ORACLE_HOME/olap/admin

cd /u01/oracle/product/12.1.0.1/db_1/olap/admin

In 11g database to be upgraded

slplus / as sysdba

@/u01/oracle/product/12.1.0.1/db_1/olap/admin/catnoamd.sql

Deletes the olap related stuff.

2. GATHER STATS

SQL> EXECUTE dbms_stats.gather_dictionary_stats;

PL/SQL procedure successfully completed.

3. CONFIRM NO MATERIALIZED VIEW REFRESH IN PROGRESS

SELECT o.name FROM sys.obj$ o, sys.user$ u, sys.sum$ s WHERE o.type# = 42 AND bitand(s.mflags, 8) =8;

4. ENSURE NO FILES NEED MEDIA RECOVERY:

SELECT * FROM v$recover_file;

5. RESOLVE OUTSTANDING DISTRIBUTED TRANSACTIONS:

SELECT * FROM dba_2pc_pending;

If the query in the previous step returns any rows, then run the following statements:

SELECT local_tran_id FROM dba_2pc_pending;

EXECUTE dbms_transaction.purge_lost_db_entry('');

COMMIT;

6. RECOMPILE INVALID OBJECTS

@$ORACLE_HOME/rdbms/admin/utlrp.sql

7. BACKUP DATABASE AND CREATE RESTORE POINT IF FLASHBACK DATABASE IS ON.

In the 12c version Database Upgrade Assistant (DBUA) enables you to specify your backup and restore point.

If you wish, a restore point can be created manually too – to play safe.

8. CHECK FOR INVALID OBJECTS

select count(*) from dba_objects where status='INVALID';

COUNT(*)---------- 0

9. PURGE RECYCLE BIN

PURGE DBA_RECYCLEBIN;

10. INCREASE FLASH RECOVERY AREA SO THAT ARCHIVELOGS CAN BE CREATED.

ALTER SYSTEM SET db_recovery_file_dest_size=20000M SCOPE=MEMORY;

11. READY TO UPGRADE USING DBUA.

Database ruuning from 11g ORACLE_HOME

cd 12c ORACLE_HOME/bin

export DISPLAY= XXXXXX

export ORACLE_SID=oracle12c

export ORACLE_HOME=/u01/oracle/product/12.1.0.1/db_1

export PATH=$ORACLE_HOME/bin:$PATH

export LD_LIBRARY_PATH=/u01/oracle/product/12.1.0.1/db_1/lib:/usr/lib:/lib

unset TNS_ADMIN

unset ORACLE_BASE

Launch DBUA from 12c ORACLE_HOME

$ ORACLE_HOME/bin

./dbua

Choose the 11g Source Oracle Home Release - Next

Ignore warning for LDAP dependencies – choose ignore from drop down list under Action.

Next

Untick box for configuring EM

Create a restore point so that the DBUA can manage any flashback.

DBUA confirms the Recovery Option – creating a guaranteed restore point.

FINISH

Click on Activity and Alert Log – another window opens up and actual entries are recorded.

Check for the restore point – Login to oracle12c as sysdba - using the new 12c Home environment.

SQL> select name,time from v$restore_point;

NAME--------------------------------------------------------------------------------------------------------------------------------TIME---------------------------------------------------------------------------GRP_139389212636804-MAR-14 08.15.26.000000000 AM

All good.

Run following script to compile list of objects invalidated due to the upgrade

cd $ORACLE_HOME/rdbms/admin

sqlplus / as sysdba

SQL> @utluiobj.sql

.Oracle Database 12.1 Post-Upgrade Invalid Objects Tool 03-04-2014 13:11:07

.

This tool lists post-upgrade invalid objects that were not invalid

prior to upgrade (it ignores pre-existing pre-upgrade invalid objects).

.

Owner Object Name Object Type

.

PL/SQL procedure successfully completed.

SQL >

If for any reason you wish to restore the database to the 11.2.0.4 version – a fallback.

Click on Restore Database

Yes

Check the alert log –

Archived Log entry 570 added for thread 1 sequence 514 ID 0x3a80da51 dest 1:

Wed Mar 05 14:58:13 2014

Shutting down instance (abort)

License high water mark = 22

Wed Mar 05 14:58:13 2014

USER (ospid: 37355720): terminating the instance

Wed Mar 05 14:58:14 2014

Instance terminated by USER, pid = 37355720

Wed Mar 05 14:58:14 2014

Instance shutdown complete

Starts up instance from old 11g home

Starting ORACLE instance (normal)

LICENSE_MAX_SESSION = 0

LICENSE_SESSIONS_WARNING = 0

Initial number of CPU is 8

Number of processor cores in the system is 4

CELL communication is configured to use 0 interface(s):

CELL IP affinity details:

NUMA status: non-NUMA system

cellaffinity.ora status: N/A

CELL communication will use 1 IP group(s):

Grp 0:

Picked latch-free SCN scheme 3

Using LOG_ARCHIVE_DEST_1 parameter default value as USE_DB_RECOVERY_FILE_DEST

Autotune of undo retention is turned on.

IMODE=BR

ILAT =27

LICENSE_MAX_USERS = 0

SYS auditing is disabled

Starting up:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning option.

ORACLE_HOME = /u01/oracle/product/11.2.0.4/db_1

System name: AIX

Node name: p520test1

Release: 1

Version: 6

Machine: 0009061CD700

Using parameter settings in server-side spfile /u01/oracle/product/11.2.0.4/db_1/dbs/spfiletest11g.ora

System parameters with non-default values:

Lost write protection disabled

Completed: ALTER DATABASE MOUNT

Wed Mar 05 14:58:38 2014

flashback database to restore point grp_1393996885298

Flashback Restore Start

Spfile /u01/oracle/product/11.2.0.4/db_1/dbs/spfiletest11g.ora is in old pre-11 format and compatible >= 11.0.0; converting to new H.A.R.D. compliant format.

Wed Mar 05 14:59:15 2014

Flashback Restore Complete

Flashback Media Recovery Start

Flashback Media Recovery Complete

Completed: flashback database to restore point grp_1393996885298

alter database open resetlogs

Check

View /etc/oratab

test11g:/u01/oracle/product/11.2.0.4/db_1:N

dbua changes the entry back to the 11g home

. /oraenv

test11g

sqlplus / as sysdba

SQL> select comp_name,version from dba_registry;

COMP_NAME

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

VERSION

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

Oracle Application Express

4.2.1.00.08

OWB

11.2.0.3.0

Oracle Enterprise Manager

11.2.0.4.0

COMP_NAME

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

VERSION

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

Spatial

11.2.0.4.0

Oracle Multimedia

11.2.0.4.0

Oracle XML Database

11.2.0.4.0

COMP_NAME

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

VERSION

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

Oracle Text

11.2.0.4.0

Oracle Expression Filter

11.2.0.4.0

Oracle Rules Manager

11.2.0.4.0

COMP_NAME

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

VERSION

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

Oracle Workspace Manager

11.2.0.4.0

Oracle Database Catalog Views

11.2.0.4.0

Oracle Database Packages and Types

11.2.0.4.0

COMP_NAME

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

VERSION

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

JServer JAVA Virtual Machine

11.2.0.4.0

Oracle XDK

11.2.0.4.0

Oracle Database Java Packages

11.2.0.4.0

COMP_NAME

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

VERSION

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

OLAP Analytic Workspace

11.2.0.3.0

Oracle OLAP API

11.2.0.3.0

17 rows selected.