Run Preupgrade tool before upgrade the PDb or CDB example
Syntax on 12c version of Preupgrade
$Earlier_release_Oracle_home/jdk/bin/java -jar $New_release_Oracle_home
/rdbms/admin/preupgrade.jar [FILE|TERMINAL] [TEXT|XML] [DIR output_dir]
FILE|TERMINAL means File save output in file and in terminal it print on terminal.
TEXT|XML means Output type in text or xml file
DIR means output directory otherwise default location is ORACLE_HOME/cfgtoollogs/dbunique_name/preupgrade
-c include of PDBs databases inside CDB which you want to execute. in windows use double quote “PDB1 PDB2” or linux single quote ‘PDB1 PDB2’
-C Exclude the PDBs database with Capital C
-oh Oracle Home location
-sid oracle SID
Note:
1. Before run the command check that PDBs or CDB must be in OPEN State.
2. Before upgrade check CDB already have name same PDB otherwise rename first that pdb and then upgrade it.
Example for running PREUPGRADE utity from 12.1.0.1 to 12.2.0.1
1. Following are the home location
OLD HOME
SET ORACLE_HOME=C:\Oracle\12.1.0\dbhome_1
SET PATH=%ORACLE_HOME%\bin;%ORACLE_HOME%\jdk\bin;%PATH%
SET ORACLE_SID=ORCL
NEW HOME:
SET ORACLE_HOME=C:\oracle12c\product\12.2.0\dbhome_1
SET PATH=%ORACLE_HOME%\bin;%ORACLE_HOME%\jdk\bin;%PATH%
SET ORACLE_SID=ORCL
2. Set the OLD home environment variable and run the preupgrade command on windows:
java -jar C:\oracle12c\product\12.2.0\dbhome_1\rdbms\admin\preupgrade.jar TERMINAL TEXT -c "PDB2"
Example of Output:
C:\Windows\system32>java -jar C:\oracle12c\product\12.2.0\dbhome_1\rdbms\admin\p
reupgrade.jar TERMINAL TEXT -c "PDB2"
Output
Report generated by Oracle Database Pre-Upgrade Information Tool Version
12.2.0.1.0
Upgrade-To version: 12.2.0.1.0
=======================================
Status of the database prior to upgrade
=======================================
Database Name: ORCL
Container Name: PDB2
Container ID: 3
Version: 12.1.0.1.0
Compatible: 12.1.0.0.0
Blocksize: 8192
Platform: Microsoft Windows x86 64-bit
Timezone File: 18
Database log mode: NOARCHIVELOG
Readonly: FALSE
Edition: EE
Oracle Component Upgrade Action Current Status
---------------- -------------- --------------
Oracle Server [to be upgraded] VALID
JServer JAVA Virtual Machine [to be upgraded] VALID
Oracle XDK for Java [to be upgraded] VALID
Real Application Clusters [to be upgraded] OPTION OFF
Oracle Workspace Manager [to be upgraded] VALID
OLAP Analytic Workspace [to be upgraded] VALID
Oracle Label Security [to be upgraded] VALID
Oracle Database Vault [to be upgraded] VALID
Oracle Text [to be upgraded] VALID
Oracle XML Database [to be upgraded] VALID
Oracle Java Packages [to be upgraded] VALID
Oracle Multimedia [to be upgraded] VALID
Oracle Spatial [to be upgraded] VALID
Oracle Application Express [to be upgraded] VALID
Oracle OLAP API [to be upgraded] VALID
==============
BEFORE UPGRADE
==============
Run /preupgrade_fixups_PDB2.sql to complete all
of the BEFORE UPGRADE action items below marked with '(AUTOFIXUP)'.
REQUIRED ACTIONS
================
+ Adjust TABLESPACE SIZES as needed.
Auto 12.2.0.1.0
Tablespace Size Extend Min Size Action
---------- ---------- -------- ---------- ------
EXAMPLE 359 MB DISABLED 322 MB None
SYSAUX 640 MB ENABLED 1543 MB None
SYSTEM 270 MB ENABLED 773 MB None
TEMP 20 MB DISABLED 150 MB Extend
Note that 12.2.0.1.0 minimum sizes are estimates.
If you plan to upgrade multiple pluggable databases concurrently,
then you must ensure that the UNDO tablespace size is equal to at least
the number of pluggable databases that you upgrade concurrently,
multiplied by that minimum. Failing to allocate sufficient space can
cause the upgrade to fail
RECOMMENDED ACTIONS
===================
+ (AUTOFIXUP) Gather stale data dictionary statistics prior to database
upgrade in off-peak time using:
EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
Dictionary statistics do not exist or are stale (not up-to-date).
Dictionary statistics help the Oracle optimizer find efficient SQL
execution plans and are essential for proper upgrade timing. Oracle
recommends gathering dictionary statistics in the last 24 hours before
database upgrade.
For information on managing optimizer statistics, refer to the 12.1.0.1
Oracle Database SQL Tuning Guide.
+ Please make sure that all the MVs are refreshed and sys.sumdelta$
becomes empty before doing upgrade, unless you have strong business
reasons not to do so. You can use dbms_mview.refresh() to refresh the
MVs except those stale ones to be kept due to business need. If there
are any stale MVs depending on changes in sys.sumdelta$, do not truncate
it, because doing so will cause wrong results after refresh.
There is one or more non-fresh MV in the database or sumdelta$ is not
empty.
Oracle recommends that all materialized views (MV's) are refreshed
before upgrading the database because this will clear the MV logs and
the sumdelta$ table, and make the UPGRADE process faster. If you choose
to not refresh some MVs, the change data for those MV's will be carried
through the UPGRADE process. After UPGRADE, you can refresh the MV's and
MV incremental refresh should work in normal cases.
INFORMATION ONLY
================
+ Consider upgrading APEX manually, before the database upgrade.
The database contains APEX version 4.2.0.00.27 and will need to be
upgraded to at least version 5.0.4.00.12.
To reduce database upgrade time, you can upgrade APEX manually before
the database upgrade. Refer to My Oracle Support Note 1088970.1 for
information on APEX installation upgrades.
=============
AFTER UPGRADE
=============
Run /postupgrade_fixups_PDB2.sql to complete all
of the AFTER UPGRADE action items below marked with '(AUTOFIXUP)'.
REQUIRED ACTIONS
================
None
RECOMMENDED ACTIONS
===================
+ If you use the -T option for the database upgrade, then run
$ORACLE_HOME/rdbms/admin/utluptabdata.sql after the upgrade is complete,
to VALIDATE and UPGRADE any user tables affected by changes to
Oracle-Maintained types.
There are user tables dependent on Oracle-Maintained object types.
If the -T option is used to set user tablespaces to READ ONLY during the
upgrade, user tables in those tablespaces, that are dependent on
Oracle-Maintained types, will not be automatically upgraded. If a type
is evolved during the upgrade, any dependent tables need to be
re-validated and upgraded to the latest type version AFTER the database
upgrade completes.
+ Upgrade the database time zone version using the DBMS_DST package.
The database is using timezone datafile version 18 and the target
12.2.0.1.0 database ships with timezone datafile version 26.
Oracle recommends using the most recent timezone data. For further
information, refer to My Oracle Support Note 1585343.1.
+ (AUTOFIXUP) Gather dictionary statistics after the upgrade using the
command:
EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
Oracle recommends gathering dictionary statistics after upgrade.
Dictionary statistics provide essential information to the Oracle
optimizer to help it find efficient SQL execution plans. After a
database upgrade, statistics need to be re-gathered as there can now be
tables that have significantly changed during the upgrade or new tables
that do not have statistics gathered yet.
+ Gather statistics on fixed objects two weeks after the upgrade using the
command:
EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
This recommendation is given for all preupgrade runs.
Fixed object statistics provide essential information to the Oracle
optimizer to help it find efficient SQL execution plans. Those
statistics are specific to the Oracle Database release that generates
them, and can be stale upon database upgrade.
Preupgrade generated files:
C:\Oracle\cfgtoollogs\orcl\preupgrade\preupgrade_fixups.sql
C:\Oracle\cfgtoollogs\orcl\preupgrade\postupgrade_fixups.sql
Run the Preupgrade fixups command on PDB2 database
SQL> alter session set container=PDB2;
Session altered.
SQL> @C:\Oracle\cfgtoollogs\orcl\preupgrade\preupgrade_fixups.sql;
Executing Oracle PRE-Upgrade Fixup Script
Auto-Generated by: Oracle Preupgrade Script
Version: 12.2.0.1.0 Build: 1
Generated on: 2018-05-26 11:33:56
For Source Database: ORCL
Source Database Version: 12.1.0.1.0
For Upgrade to Version: 12.2.0.1.0
Executing in container: PDB2
Fixup
Check Name Status Further DBA Action
---------- ------ ------------------
dictionary_stats Passed None
mv_refresh Failed Manual fixup recommended.
apex_upgrade_msg Failed Manual fixup recommended.
PL/SQL procedure successfully completed.
Session altered.
Error with providing the wrong PDB or in Single quote in window
C:\Windows\system32>java -jar C:\oracle12c\product\12.2.0\dbhome_1\rdbms\admin\p
reupgrade.jar TERMINAL TEXT -c 'pdb1'
Error - Could not obtain the list of PDBS, failed query: select upper(name)||'@'
||OPEN_MODE from v$containers where upper(name) in (''PDB1'')
C:\Windows\system32>java -jar C:\oracle12c\product\12.2.0\dbhome_1\rdbms\admin\p
reupgrade.jar TERMINAL TEXT -c 'pdb2'
Error - Could not obtain the list of PDBS, failed query: select upper(name)||'@'
||OPEN_MODE from v$containers where upper(name) in (''PDB2'')
Smart way of Technology 