What’s new in database upgrade

Before commencing the database upgrade process, it is necessary to become familiar with those new additions or enhancements introduced in the context of Oracle database upgrade procedure.

There are two essential elements that every DBA should realize before the database upgrade:

1) Database upgrades path.

2) Understanding and taking the right advantage of new additions and enactments in the upgrade process.

Upgrade compatible matrix

The following table depicts the compatible matrix for a direct and indirect database upgrade paths:

direct upgrade	indirect upgrade
10.2.0.5 11.1.0.7 >=11.2.0.2	<=9.2.0.8 10.1.0.5 10.2.0.2/3/4 11.1.0.6 11.2.0.1

If your current database version doesn’t match with the direct upgrade list of versions, you therefore need to choose an indirect upgrade path. In this context, you must perform an upgrade of your current database to one of the versions listed in the direct upgrade lists of version and subsequently proceed with the Oracle database 12c upgrade.

Upgrade enchantments/additions

The following new concepts were introduced in Oracle 12c with regards to database upgrade procedure:

· Pre-upgrade tool

· Parallel processing

Pre-upgrade tool: The new pre-upgrade information tool (preupgrd.sql) is capable of verifying a detailed pre-upgrade checks, and generates pre/post-upgrade scripts that likely to address all the key concerns reported. The new tool is replaced with the traditional utlui.sql script, which offers more advantages over the old script.

Parallel processing: Pre Oracle 12c, there was no option to run the database upgrade process in parallel in order to take the advantage of multiple CPU on the server and cut off the total downtime required for the process. The catctl.pl utility loads the data dictionary and database components in parallel to improve the overall database upgrade process time. The DBUA inherits the tool, and triggers automatically. You must manually execute the utility whilst doing the manual database upgrade.

Manual database upgrade procedure

This goal of this section is to explain you a step-by-step procedure which will assist you upgrading a database to Oracle 12c manually.

Let me demonstrate how to manually upgrade an Oracle 11.2.0.3.0 database – TESTUPG, to Oracle database 12c:

TESTUPG oracle@:/remote/app/oracle/product/12.1[128] sqlplus

SQL*Plus: Release 11.2.0.3.0 Production on Tue Apr 22 23:09:06 2014

Enter user-name: /as sysdba

Connected to:

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

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @/remote/app/oracle/product/12.1/rdbms/admin/preupgrd.sql

Loading Pre-Upgrade Package...

Executing Pre-Upgrade Checks...

Pre-Upgrade Checks Complete.

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

Results of the checks are located at:

/remote/app/oracle/product/11.2.0.3.4/cfgtoollogs/TESTUPG/preupgrade/preupgrade.log

Pre-Upgrade Fix up Script (run in source database environment):

/remote/app/oracle/product/11.2.0.3.4/cfgtoollogs/TESTUPG/preupgrade/preupgrade_fixups.sql

Post-Upgrade Fix up Script (run shortly after upgrade):

/remote/app/oracle/product/11.2.0.3.4/cfgtoollogs/TESTUPG/preupgrade/postupgrade_fixups.sql

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

Fix up 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>

--- Refer the log file and sql scripts generated under the /remote/app/oracle/product/11.2.0.3.4/cfgtoollogs/dbuniquename location to verify the concerns raised and fix up scripts for pre/post upgrade process

Attached the preupgrade.log file for reference.

As per the Pre-upgrade recommendations, I executed the below statement in TESTUPG database.

EXECUTE dbms_stats.gather_dictionary_stats;

SQl> SHUTDOWN DATABASE;
SQL> EXIT;

--- switch to Oracle 12c Oracle Home, startup the database in upgrade mode

--- Copy the InitTESTUPG.ora from current oracle Home to 12c Oracle Home

...setting TESTUPG database environment

ORACLE_SID = TESTUPG

ORACLE_HOME = /remote/app/oracle/product/12.1

TESTUPG oracle@:/admin[106] sqlplus

SQL*Plus: Release 12.1.0.1.0 Production on Tue Apr 22 23:36:29 2014

Enter user-name: /as sysdba

Connected to an idle instance.

SQL> startup upgrade

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORACLE instance started.

Total System Global Area 835104768 bytes

Fixed Size 2293880 bytes

Variable Size 251662216 bytes

Database Buffers 574619648 bytes

Redo Buffers 6529024 bytes

Database mounted.

Database opened.

SQL> exit

--- initiate the upgrade procedure in parallel using the new parallel upgrade utility

TESTUPG oracle@:/remote/app/oracle/product/12.1/rdbms/admin[109] perl catctl.pl -n 4 catupgrd.sql

Analyzing file catupgrd.sql

14 scripts found in file catupgrd.sql

Next path: catalog.sql

32 scripts found in file catalog.sql

Next path: catproc.sql

37 scripts found in file catproc.sql

Next path: catptabs.sql

61 scripts found in file catptabs.sql

Next path: catpdbms.sql

205 scripts found in file catpdbms.sql

Next path: catpdeps.sql

77 scripts found in file catpdeps.sql

Next path: catpprvt.sql

260 scripts found in file catpprvt.sql

Next path: catpexec.sql

26 scripts found in file catpexec.sql

Next path: cmpupgrd.sql

16 scripts found in file cmpupgrd.sql

[Phase 54] type is 1 with 1 Files

catupend.sql

[Phase 55] type is 1 with 1 Files

catuppst.sql

[Phase 56] type is 1 with 1 Files

catshutdown.sql

Using 4 processes.

Serial Phase #: 0 Files: 1 Time: 479s

Serial Phase #: 1 Files: 3 Time: 156s

Restart Phase #: 2 Files: 1 Time: 2s

Parallel Phase #: 3 Files: 18 Time: 37s

Restart Phase #: 4 Files: 1 Time: 0s

Serial Phase #: 5 Files: 5 Time: 63s

Serial Phase #: 6 Files: 1 Time: 35s

Serial Phase #: 7 Files: 3 Time: 22s

Restart Phase #: 8 Files: 1 Time: 0s

Parallel Phase #: 9 Files: 60 Time: 122s

Restart Phase #:10 Files: 1 Time: 0s

Serial Phase #:11 Files: 1 Time: 63s

Restart Phase #:12 Files: 1 Time: 0s

Parallel Phase #:13 Files: 199 Time: 67s

Restart Phase #:14 Files: 1 Time: 0s

Serial Phase #:15 Files: 3 Time: 2s

Restart Phase #:16 Files: 1 Time: 0s

Parallel Phase #:17 Files: 33 Time: 63s

Restart Phase #:18 Files: 1 Time: 0s

Serial Phase #:19 Files: 3 Time: 21s

Restart Phase #:20 Files: 1 Time: 1s

Parallel Phase #:21 Files: 23 Time: 144s

Restart Phase #:22 Files: 1 Time: 0s

Parallel Phase #:23 Files: 11 Time: 60s

Restart Phase #:24 Files: 1 Time: 0s

Serial Phase #:25 Files: 1 Time: 74s

Restart Phase #:26 Files: 1 Time: 0s

Parallel Phase #:27 Files: 0 Time: 0s

Serial Phase #:28 Files: 1 Time: 0s

Parallel Phase #:29 Files: 130 Time: 15s

Serial Phase #:30 Files: 1 Time: 0s

Restart Phase #:31 Files: 1 Time: 0s

Serial Phase #:32 Files: 1 Time: 0s

Parallel Phase #:33 Files: 122 Time: 26s

Serial Phase #:34 Files: 1 Time: 0s

Restart Phase #:35 Files: 1 Time: 0s

Serial Phase #:36 Files: 4 Time: 120s

Restart Phase #:37 Files: 1 Time: 0s

Parallel Phase #:38 Files: 13 Time: 82s

Restart Phase #:39 Files: 1 Time: 0s

Parallel Phase #:40 Files: 10 Time: 23s

Restart Phase #:41 Files: 1 Time: 0s

Serial Phase #:42 Files: 1 Time: 22s

Restart Phase #:43 Files: 1 Time: 0s

Serial Phase #:44 Files: 1 Time: 11s

Serial Phase #:45 Files: 1 Time: 1s

Restart Phase #:46 Files: 1 Time: 0s

Serial Phase #:47 Files: 2 Time: 600s

Restart Phase #:48 Files: 1 Time: 1s

Serial Phase #:49 Files: 2 Time: 954s

Restart Phase #:50 Files: 1 Time: 1s

Serial Phase #:51 Files: 2 Time: 3526s

Restart Phase #:52 Files: 1 Time: 2s

Serial Phase #:53 Files: 1 Time: 6s

Serial Phase #:54 Files: 1 Time: 1517s

Serial Phase #:55 Files: 1 Time: 984s

Serial Phase #:56 Files: 1 Time: 26s

Grand Total Time: 9328s

TESTUPG oracle:admin[110]

Upon completing the catupgrd.sql script, the database will be shutdown. You, therefore need to start the database from the Oracle 12c home and run couple of post database upgrade steps.

TESTUPG oracle@:/remote/app/oracle/product/12.1[105] sqlplus

SQL*Plus: Release 12.1.0.1.0 Production on Wed Apr 23 12:06:29 2014

Enter user-name: /as sysdba

Connected to an idle instance.

SQL> startup

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORACLE instance started.

Total System Global Area 835104768 bytes

Fixed Size 2293880 bytes

Variable Size 251662216 bytes

Database Buffers 574619648 bytes

Redo Buffers 6529024 bytes

Database mounted.

Database opened.

SQL> @?/rdbms/admin/utlu121s.sql;

Oracle Database 12.1 Post-Upgrade Status Tool 04-23-2014 12:21:19

Component Current Version Elapsed Time

Name Status Number HH:MM:SS

Oracle Server

. UPGRADED 12.1.0.1.0 00:28:19

JServer JAVA Virtual Machine

. VALID 12.1.0.1.0 00:07:57

Oracle Workspace Manager

. VALID 12.1.0.1.0 00:03:34

OLAP Analytic Workspace

. VALID 12.1.0.1.0 00:00:57

Oracle OLAP API

. VALID 12.1.0.1.0 00:00:58

Oracle XDK

. VALID 12.1.0.1.0 00:01:04

Oracle Text

. VALID 12.1.0.1.0 00:02:27

Oracle XML Database

. VALID 12.1.0.1.0 00:09:10

Oracle Database Java Packages

. VALID 12.1.0.1.0 00:00:40

Oracle Multimedia

. VALID 12.1.0.1.0 00:05:04

Spatial

. VALID 12.1.0.1.0 00:13:42

Oracle Application Express

. VALID 4.2.0.00.27 00:38:07

Final Actions

. 00:23:23

Total Upgrade Time: 02:16:28

PL/SQL procedure successfully completed.

SQL> @?/rdbms/admin/utlrp.sql;

TIMESTAMP

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

COMP_TIMESTAMP UTLRP_BGN 2014-04-23 12:21:33

PL/SQL procedure successfully completed.

TIMESTAMP

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

COMP_TIMESTAMP UTLRP_END 2014-04-23 12:26:43

OBJECTS WITH ERRORS

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

ERRORS DURING RECOMPILATION

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

Function created.

PL/SQL procedure successfully completed.

Function dropped.

...Database user "SYS", database schema "APEX_040200", user# "116" 12:27:20

...Compiled 0 out of 2998 objects considered, 0 failed compilation 12:27:20

...263 packages

...255 package bodies

...453 tables

...11 functions

...16 procedures

...3 sequences

...458 triggers

...1322 indexes

...207 views

...0 libraries

...6 types

...0 type bodies

...0 operators

...0 index types

...Begin key object existence check 12:27:20

...Completed key object existence check 12:27:20

...Setting DBMS Registry 12:27:20

...Setting DBMS Registry Complete 12:27:21

...Exiting validate 12:27:21

PL/SQL procedure successfully completed.

SQL> select * from v$version;

BANNER

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

CON_ID

----------

Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

PL/SQL Release 12.1.0.1.0 - Production

CORE 12.1.0.1.0 Production

BANNER

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

CON_ID

----------

TNS for Linux: Version 12.1.0.1.0 - Production

NLSRTL Version 12.1.0.1.0 - Production

SQL> select name from v$database;

NAME

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

TESTUPG

SQL>exit;

dbCafè

Tuesday, September 2, 2014

Oracle Database Upgrade 12C

What’s new in database upgrade

Upgrade compatible matrix

Upgrade enchantments/additions

Manual database upgrade procedure

Reference

No comments:

Post a Comment

Blog Archive

Followers