Manual database upgrade from Oracle to

Manual database upgrade from Oracle to


A lot of Oracle DBA’s doesn’t use the dbua to upgrade an existing database to a new release. They would like to control the upgrade process by themselves.

Following I did an “manually upgrade” to The upgrade based on the Oracle MOS note 1932762.1.

My test environment is OEL 6.5 and software installation. I have a container database “CDB” and some pluggable databases “PDB1, PDB2, PDB3”.

I used this environment for my OCP 12c certification upgrade. This OCP exam actually based on the Oracle  release

I passed the OCP exam and so I could upgrade my environment to test the new features in :-)


Setup and installation of the Oracle software

 First of all I download the Patchset from MOS

It is Patchset 17694377

Unzip the archives and create a new directory structure. Keep in mind that Oracle recommend to do an „out of place upgrade“ which means install in an new directory „structure“.


From the temporary directory I start the installer


Here are some screenshots from the software installation







The software installation was done which is the base for starting the database upgrade



Ensure that all database components/objects are “VALID”  in the source database  before starting the upgrade

Check for invalid objects:

select object_name, object_type from all_objects where status = ‘INAVLID’;

SQL> alter pluggable database all open;

Pluggable database altered.


   CON_ID NAME                     OPEN_MODE

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

        2 PDB$SEED                 READ ONLY

        3 PDB1                     READ WRITE

        4 PDB2                     READ WRITE

        5 PDB3                     READ WRITE

SQL> Select object_name, object_type from all_objects where status = 'INVALID';

no rows selected

SQL> alter session set container=PDB1;

Session altered.

SQL> Select object_name, object_type from all_objects where status = 'INVALID';

no rows selected

SQL> alter session set container=PDB2;

Session altered.

SQL> Select object_name, object_type from all_objects where status = 'INVALID';

no rows selected

SQL> alter session set container=PDB3;

Session altered.

SQL> Select object_name, object_type from all_objects where status = 'INVALID';

no rows selected


Check the database status

It should be open and read/write

SQL> select NAME, DECODE(CDB, 'YES', 'Multitenant Option enabled', 'Regular 12c Database: ') "Multitenant Option ?" , OPEN_MODE, CON_ID from V$DATABASE;

NAME     Multitenant Option ?         OPEN_MODE                  CON_ID
--------- -------------------------- --------------------     ----------
CDB      Multitenant Option enabled   READ WRITE                    0


Please take a backup of the whole database before you start

In my example I don’t use an rman catalog

rman target /

backup database <CDB> tag before-upgrade-12102


Pre-Upgrade Steps

From the directory run the preupgrd.sql script

PDB status should be in read / write status

sql> show pdbs;

To make it read write

sql> alter pluggable database <PDB_Name> open;

cd $ORACLE_HOME/rdbms/admin

$ORACLE_HOME/perl/bin/perl -d <directory location of preupgrade scripts> -l <log location of preupgrade script> -c <PDB_Name> -b <preupgrade_log_base_name> preupgrd.sql

For using the script please refer:

MOS note 1932340.1 How to execute sql scripts in Multitenant environment (

Some remarks to the script at the end.

[oracle@srv1 ~]$ env

[oracle@srv1 ~]$
cd $ORACLE_HOME/rdbms/admin

/u01/app/oracle/product/ -d /u01/app/oracle/product/ -l /home/oracle/preupgrd_log -c 'PDB1 PDB2 PDB3' -b preupgrd_12102 preupgrd.sql

After running the preupgrade check the logfile

[oracle@srv1 pdbfiles]$ more preupgrade_fixups.PDB1.log

Oracle Database Pre-Upgrade Information Tool 12-10-2014 09:30:51

Script Version: Build: 006


Database Name:  CDB
Container Name:  PDB1
Container ID:  3
Blocksize:  8192
Platform:  Linux x86 64-bit
Timezone file:  V18


[Update parameters]

[No parameters to update]



[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
--> Real Application Clusters              [upgrade]  OPTION OFF
--> Oracle Workspace Manager               [upgrade]  VALID
--> OLAP Analytic Workspace                [upgrade]  VALID
--> Oracle Label Security                  [upgrade]  VALID
--> Oracle Database Vault                  [upgrade]  VALID



[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;



[Post-Upgrade Recommendations]


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

Please create stats on fixed objects two weeks

after the upgrade using the command:




************  Summary  ************
<strong>0 ERRORS exist in your database.</strong>
<strong>0 WARNINGS exist in your database.</strong>
<strong>1 INFORMATIONAL message that should be reviewed prior to your upgrade.</strong>
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
If needed you may want to upgrade your timezone data using the process
described in My Oracle Support note 1509653.1

 There will be logfiles for all PDB’s. Take a look and process the recommendations

Next step

Collect statistics

Oracle recommends collecting statistics the night before starting the upgrade to decrease the amount of downtime

sqlplus "/as sysdba"

SQL> exec dbms_stats.gather_dictionary_stats;


Check if the statistics where collected by query the dba_optstat_operations view.


SQL> select id,operation,target, START_TIME,END_TIME from dba_optstat_operations


Verify that Materialized View Refreshes have completed before upgrading the database


SQL> select s.obj#,o.obj#,s.containerobj#,lastrefreshdate,pflags,xpflags,,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;


Ensure that no files need media recovery before upgrading

SQL> select * from v$recover_file;


Resolve outstanding distributed transactions before upgrading


SQL> select * from dba_2pc_pending;

if the query has some results please check with the following queries.

select  local_tran_id FROM dba_2pc_pending;

SQL> execute  dbms_transaction.purge_lost_db_entry('');

SQL> commit;


Purge the database recycle bin before upgrading


SQL> purge dba_recyclebin;


Disable all batch and cron jobs.

cron jobs or external jobs controlled at the OS level please contact the unix administrator

For jobs initiated by Oracle the following packages DBMS_JOB, DBMS_SCHEDULER can be used.

Please check the notes for details information

MOS note 404238.1 : How to Disable an Entry from DBMS_SCHEDULER

MOS note 1335741.1 : How To Stop A Running Job Using DBMS_JOB

The following query will give an idea if there are any problems



Verify SYS and SYSTEM Default tablespace

Check for all PDB’s

SQL> alter session set container=<PDB_Name>;

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

Review and Remove any unnecessary hidden/underscore parameters

SQL> alter session set container=<PDB_Name>;

SQL> SELECT name, value from SYS.V$PARAMETER WHERE name LIKE '\_%' ESCAPE '\' order by name;

Requirements and recommendations for target environment

Ensure that newly installed Oracle Home doesn’t have relinking errors

Install the latest opatch available for your platform and database version

Check for the latestet version of opatch and install it if necessary

Again be sure that you have a backup of your source database


Preparing new oracle home for upgrading

Copy the old spfile to the new software location

If using a oracle password file please copy this also to the new location

While upgrading an RAC database set the cluster_database = FALSE

Create a pfile from spfile to adjust new parameters. It makes live easier



After running the preupgrade without errors I start the upgrade procedure


Upgrade the CDB database to

Shutdown the database either CDB or NON-CDB

SQL> shutdown immediate

Changing the oratab


Set the new environment

[oracle@srv1 dbs]$ . oraenv

The Oracle base remains unchanged with value /u01/app/oracle

[oracle@srv1 dbs]$ env|grep ORA

Startup in upgrade mode

SQL> startup upgrade pfile=<pfile location>
Start all PDB in upgrade mode
SQL> alter pluggable database all open upgrade;

Exit SQL*Plus before proceeding to the next step.

SQL> exit;


Executing the upgrade procedure

[oracle@srv1 dbs]$ sqlplus / as sysdba
SQL*Plus: Release Production on Wed Dec 10 11:18:26 2014
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Connected to an idle instance.

SQL> startup upgrade pfile='/u01/app/oracle/product/';

ORACLE instance started.
Total System Global Area 1862270976 bytes
Fixed Size              2925648 bytes
Variable Size               553651120 bytes
Database Buffers    1291845632 bytes
Redo Buffers           13848576 bytes
Database mounted.

alter pluggable database all open upgrade;
Database opened.

SQL> select name,open_mode from v$pdbs;
NAME                       OPEN_MODE
------------------------------ ----------
PDB$SEED                   MIGRATE
PDB1                       MIGRATE
PDB2                       MIGRATE
PDB3                       MIGRATE

Starting the upgrade script

Here a part of the logfile during the upgrade process. If I compare it to older versions the output in the logfile gives more detailed informations.



[oracle@srv1 admin]$ $ORACLE_HOME/perl/bin/perl -l /home/oracle/migrate_log catupgrd.sql

Example output version:
Oracle Base           = /u01/app/oracle
Analyzing file catupgrd.sql
Log files in /home/oracle/migrate_log

catcon: ALL catcon-related output will be written to /home/oracle/migrate_log/catupgrd_catcon_5289.lst

catcon: See /home/oracle/migrate_log/catupgrd*.log files for output generated by scripts

catcon: See /home/oracle/migrate_log/catupgrd_*.lst files for spool files, if any

Number of Cpus        = 1

Parallel PDB Upgrades = 2

SQL PDB Process Count = 2

SQL Process Count     = 0

New SQL Process Count = 1

PDB Inclusion:[PDB$SEED PDB1 PDB2 PDB3] Exclusion:[]


Phases [0-73]
Container Lists Inclusion:[CDB$ROOT] Exclusion:[]
Serial   Phase #: 0 Files: 1     Time: 7s    CDB$ROOT
Serial   Phase #: 1 Files: 5


Here starts the upgrade of PDB3
Total Upgrade Time:          [0d:0h:32m:10s]
Time: 154s  PDB3
Restart  Phase #:61 Files: 1     Time: 0s    PDB3
Serial   Phase #:62 Files: 1     Time: 81s   PDB3
Restart  Phase #:63 Files: 1     Time: 0s    PDB3
Serial   Phase #:64 Files: 1     Time: 1s    PDB3
Serial   Phase #:65 Files: 1 Calling sqlpatch with LD_LIBRARY_PATH=/u01/app/oracle/product/; export LD_LIBRARY_PATH;/u01/app/oracle/product/ -I /u01/app/oracle/product/ -I /u01/app/oracle/product/ /u01/app/oracle/product/ -verbose -upgrade_mode_only -pdbs PDB3 > /home/oracle/migrate_log/catupgrdpdb3_datapatch_upgrade.log 2> /home/oracle/migrate_log/catupgrdpdb3_datapatch_upgrade.err

returned from sqlpatch

Time: 7s    PDB3
Serial   Phase #:66 Files: 1     Time: 2s    PDB3
Serial   Phase #:68 Files: 1     Time: 3s    PDB3
Serial   Phase #:69 Files: 1 Calling sqlpatch with LD_LIBRARY_PATH=/u01/app/oracle/product/; export LD_LIBRARY_PATH;/u01/app/oracle/product/ -I /u01/app/oracle/product/ -I /u01/app/oracle/product/ /u01/app/oracle/product/ -verbose -pdbs PDB3 > /home/oracle/migrate_log/catupgrdpdb3_datapatch_normal.log 2> /home/oracle/migrate_log/catupgrdpdb3_datapatch_normal.err

returned from sqlpatch
Time: 8s    PDB3
Serial   Phase #:70 Files: 1     Time: 5s    PDB3
Serial   Phase #:71 Files: 1     Time: 0s    PDB3
Serial   Phase #:72 Files: 1     Time: 1s    PDB3
Serial   Phase #:73 Files: 1     Time: 0s    PDB3

Grand Total Time: 1657s PDB3
LOG FILES: (catupgrdpdb3*.log)
Upgrade Summary Report Located in:

Total Upgrade Time:          [0d:0h:27m:37s]
Time: 1285s For CDB$ROOT
Time: 4150s For PDB(s)
Grand Total Time: 5435s
LOG FILES: (catupgrd*.log)

upgrade Summary Report Located in:


Grand Total Upgrade Time:    [0d:1h:30m:35s]

Summary of the upgrade process


Oracle Database 12.1 Post-Upgrade Status Tool           12-10-2014 13:01:25


Component                               Current         Version  Elapsed Time
Name                                    Status          Number   HH:MM:SS


Oracle Server                          UPGRADED  00:14:36
JServer JAVA Virtual Machine              VALID  00:01:24
Oracle Real Application Clusters     OPTION OFF  00:00:01
Oracle Workspace Manager                  VALID  00:01:38
OLAP Analytic Workspace                   VALID  00:00:26
Oracle OLAP API                           VALID  00:00:10
Oracle Label Security                     VALID  00:00:08
Oracle XDK                                VALID  00:00:54
Oracle Text                               VALID  00:00:10
Oracle XML Database                       VALID  00:00:59
Oracle Database Java Packages             VALID  00:00:15
Oracle Multimedia                         VALID  00:02:12
Spatial                                UPGRADED  00:02:33
Oracle Application Express                VALID  00:01:10
Oracle Database Vault                     VALID  00:00:17
Final Actions                                                    00:00:07
Post Upgrade                                                     00:00:02

Total Upgrade Time: 00:27:15 [PDB3]

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.10

Last step from the upgrade process


[oracle@srv1 admin]$ $ORACLE_HOME/perl/bin/perl -n 1 -e -b utlu121s -d '''.''' utlu121s.sql

catcon: ALL catcon-related output will be written to utlu121s_catcon_17161.lst

catcon: See utlu121s*.log files for output generated by scripts

catcon: See utlu121s_*.lst files for spool files, if any completed successfully

At this point the manual upgrade is finished.

Please check that you have the correct spfile if not recreate a spfile. Also if needed upgrade the timezone and the statistics tables. (see below)

Upgrade timezone after database upgrade if necessary

If the pre upgrade give you some information for doing an timezone update  DBMS_DST PL/SQL package.

MOS note 1585343.1 : Scripts to automatically update the RDBMS DST (timezone) version in an 11gR2 or 12cR1 database


Upgrade to finished

Recommended tasks if needed

Upgrade rman catalog

If you use a rman catalog you have to do an upgrade to be sure that your catalog will support the new Oracle release.

By the way please do a backup of the database newly upgraded database.



The upgrade procedure running manually via scripts takes a little bit more time if you do this the first time. By doing it in this way you learn a lot about how Oracle handle in Version 12c scripts for a multitenant environment. I show it here again with a simple SQL script.

Example for running scripts in multitenant environments

While using multitenant option the “” program will be your best friend in the future.

In my example I start a  SQL-Script against all PDB’s (PDB1, PDB2, PDB3)


How to start a individual script

$ /u01/app/oracle/product/ -d /home/oracle/stefan -l /home/oracle/preupgrd_log -c 'PDB1 PDB2 PDB3' -b preupgrd_12102 sel_check_user.sql

-d directory where the script exists

-l directory for logfiles

-c the pluggable database where the script has to be run here PDB1, PDB2, PDB3

-b base name for log and spool files (here preupgrd_12102)


Try and test it. Have fun. :-)
















Über spa

Oracle and Unix Professional, main focus on Oracle HA - Systems also an Exadata enthusiasts
Dieser Beitrag wurde unter General abgelegt und mit , , verschlagwortet. Setze ein Lesezeichen auf den Permalink.

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

Du kommentierst mit Deinem Abmelden / Ändern )


Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )


Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

Google+ Foto

Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

Verbinde mit %s