12c Software, apply patches but do not create a Database
be aware that after your install the new 12c Software, a new
Directory will be added to your PATH variable:
Server Preparation Upgrade Checklist
this checklist to prepare your server with the earlier
Oracle Database release before you start the upgrade.
up the database
SYS User, Empty the recycle bin
for INVALID objects in SYS and SYSTEM
- col owner format a15
- col object_name format a45
- col object_type format a15
- select owner, object_name,
object_type from dba_objects where owner
in ('SYS','SYSTEM') and status='INVALID'
order by owner,object_type;
for duplicate objects in SYS and SYSTEM
- SELECT object_name,
object_type, subobject_name, object_id
WHERE object_name||object_type in
WHERE owner = 'SYS')
AND owner = 'SYSTEM'
AND object_name NOT in
the status of all materialized views (MV),
and refresh any materialized views that are
- SELECT o.name
FROM sys.obj$ o, sys.user$ u, sys.sum$ s
WHERE o.type# = 42 AND bitand(s.mflags, 8)
Time Zone setting
the following query to detect the timezone
of your source
- select version from
time zone version for Oracle Database 12c
release 2 (12.2) is V26
database Time Zone version is higher than
the target database, then apply the Time
Zone patch on the target database to match the
source database version.
Time Zone defaults are in located in the
EM Repository (if exists)
emremove.sql script. The script will be
located in new
- SET ECHO ON
SET SERVEROUTPUT ON
- After emremove.sql
completes, you must manually remove
directories from your file system.
accounts using ONLY 10g password version
SYS, enter the following SQL query
- select USERNAME from
DBA_USERS where ( PASSWORD_VERSIONS = '10G
' or PASSWORD_VERSIONS = '10G HTTP ') and
USERNAME <> 'ANONYMOUS';
there is any account, then:
- 1- Configure the system so
that it is not running in Exclusive Mode
by editing the setting of the SQLNET.ORA
SQLNET.ALLOWED_LOGON_VERSION_SERVER to a
level appropriate for affected accounts.
- 2- After you make this
change, proceed with the upgrade
- 3- After the upgrade
completes, use the following command
syntax to expire the accounts you found in
step 1, where username is the name of a
user returned from the query in step 1:
ALTER USER username PASSWORD EXPIRE;
- 4- Ask the users for whom
you have expired the passwords to log in.
- 5- When these users log in,
they are prompted to reset their
passwords. The system internally generates
the missing 11G and 12C password versions
for their account, in addition to the 10G
password version. The 10G password version
is still present, because the system is
running in the permissive mode.
that you do not have the deprecated parameter
SEC_CASE_SENSITIVE_LOGON set to FALSE
SYS execute: show parameter
SEC_CASE_SENSITIVE_LOGON. If is TRUE or
FALSE, remove it from the init.ora File and
restart the DB
dictionary statistics to decrease Oracle Database
up the database (recommended)
up the database, create a guaranteed
flashback restore point, or both.
your fallback strategies at least once
before your upgrade window.
that you have fallback strategies for issues
both during upgrade, and after upgrade.
you can invoke DBUA from Command Prompt
DBUA (Step 1 of 9)
need to choose the database SID for upgrade to 22.214.171.124. In
this case, it is orcl
DBUA (step 2of 9)
below screen, DBUA will perform various pre-checks. It will
execute the preupgrade script (preupgrade.jar) and return
warnings / errors.
DBUA (Step 3 of 9)
the pre-upgrade warnings has been adressed, next screen will
show various options like Enable Parallel Upgrade, Recompile
invalid objects in post upgrade phase, upgrading timezone,
gather statistics before the upgrade, setting the user
tablespaces to read only during upgrade. also, you can
provide any SQL scripts before or after the upgrade. I
unchecked the "Gather Statistics Before Upgrade" Option
DBUA (Step 4 of 9)
selecting option, The next screen will show various Recovery
options where it will create Guaranteed Restore Point, or
Take an RMAN backup or the backup is already taken outside
DBUA then "I have my own backup and restore stratege "
option can be chosen.
DBUA (Step 5 of 9)
this screen, we can create a new listener or upgrade the
existing listener to 126.96.36.199. I used the existing listener.
DBUA (Step 6 of 9)
screen is for configure EM express or register the upgraded
database with EM Cloud control. Uncheck all the options.
DBUA (step 7 of 9)
is the summary screen before the actual upgrade starts.
Click on Finish to proceed with the upgrade.
DBUA (Step 8 of 9)
DBUA will start the upgrade process of orcl database. It
will perform pre-upgrade, database upgrade and post upgrade
steps. It takes about 1 hour.
will perform the upgrade of database components like Oracle
server, XML etc.
DBUA Step 9 of 9)
final screen will show the result of upgrade.
will show the log file location and the time taken by
various steps of upgrade.
Target Server Post-Upgrade
these checks on the upgraded Oracle Database environment.
for any INVALID Objects
substr(owner,1,12) owner, substr(object_name,1,12)
object, substr(object_type,1,30) object, status
from dba_objects WHERE status <> 'VALID';
postinstallation SQL scripts as SYS
upgrade logs and trace files
(Look under $ORACLE_HOME if $ORACLE_BASE is
not set). Also, folders with the system
identifier (SID) of individual database are
in this timestamp folder. The SID folders
contain files for individual databases for
the preupgrade and upgrade process.
upgrade status for CATALOG and CATPROC components
substr(comp_name, 1, 45) as comp_name,
substr(version,1,8) as version ,
substr(status,1,8) as status from
Look for version and status changes.
Oracle Database 12.2 Post-Upgrade Status Tool
time zone settings
Time Zone version at source database is
lower than the target database, then run
Time Zone conversion after the upgrade.
Time Zone defaults are in the path
Oracle Database system files
an SPFILE from the PFILE
new Oracle Cost-Based Optimizer (CBO)
up the database