Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Pranay.

Asked: November 22, 2016 - 5:54 am UTC

Last updated: December 10, 2016 - 1:27 am UTC

Version: 11.2.0

Viewed 10K+ times! This question is

You Asked

Hi,

We have a stable application on 11.2.0 database. Business is planning to upgrade database from 11g to 12c. Kindly advise, if anything particular we need to take care during this upgrade. Is it advisable to lock the existing explain plans before the upgrade.

Thanks in advance.

and Chris said...

Read the database upgrade guide and the following whitepaper:

http://www.oracle.com/technetwork/database/upgrade/upgrading-oracle-database-wp-12c-1896123.pdf
https://docs.oracle.com/database/121/UPGRD/toc.htm

There are several changes in the optimizer. One of the major changes are the adaptive query optimizations. Read this whitepaper for more details:

http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-optimizer-with-oracledb-12c-1963236.pdf

Locking SQL plans using SQL plan management (baselines) before upgrades does help reduce the risk of degraded query performance. The following whitepaper discusses SPM and how to use it to manage upgrades:

http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-sql-plan-mgmt-12c-1963237.pdf

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

12.1 or 12.2

Rajeshwaran, Jeyabal, November 23, 2016 - 11:06 am UTC

Incase if you are upgrading to 12.1 rather than 12.2 - then make sure to apply these patches ( patch for bug# 22652097 and patch for bug# 21171382 ) and ensure that you remove "optimizer_adaptive_features" parameter from SPFILE/PFILE
https://blogs.oracle.com/UPGRADE/entry/enabling_adaptive_features_of_oracle
Connor McDonald
November 24, 2016 - 4:10 am UTC

Nice input, and remiss of me not to mention Mike's upgrade blog.

question NLS

A reader, December 07, 2016 - 1:55 pm UTC

hello
do i need to set the ORA_NLS10 variable in 12c?
and what is for? pls elaborate
Connor McDonald
December 07, 2016 - 2:14 pm UTC

ORA_NLSxx is used to indicate where Oracle RDBMS/client software can locate the definitions of Charactersets (used in NLS_LANG or as NLS_CHARACTERSET/NLS_NCHAR_CHARACTERSET), NLS_SORT, NLS_LANGUAGE (or derived/related parameters) or NLS_TERRITORY (or derived/related parameters).
Those definitions are stored in .nlb files who can be found in the ORA_NLSxx directory.

When using 9i or above there is no need to explicit define the ORA_NLSxx parameter unless a different directory setting is needed.

Example?

A reader, December 08, 2016 - 6:52 am UTC

Thank you Connor.
So why xx? And when is 10 or 33 or..?
Since from 9i not required to be defined ..
Could give example when it us required in the parameters environment?
Connor McDonald
December 10, 2016 - 1:27 am UTC

From 9i upwards, if your characterset definitions are in the default location, then you do not need to set the ORA_NLS.. parameter. 'xx' being representative of *any* value (10, etc)

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.