Upgradation from 11.2.0.4 to 12.1.0.2
*************************************************************************************
First we need to download the 12.1.0.2 RDBMS software from
metalink and install in seperate home.
Run the new preupgrade check script
preupgrd.sql, in 11.2.0.4 environment – it will generate 3 files:
SQL>
@/u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/preupgrd.sql
Loading Pre-Upgrade Package...
***************************************************************************
Executing Pre-Upgrade Checks in
ORCL...
***************************************************************************
************************************************************
====>> ERRORS FOUND
for ORCL <<====
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
************************************************************
************************************************************
====>> PRE-UPGRADE RESULTS
for ORCL <<====
ACTIONS REQUIRED:
1. Review results of the pre-upgrade
checks:
/u01/app/oracle/cfgtoollogs/orcl/preupgrade/preupgrade.log
2. Execute in the SOURCE environment
BEFORE upgrade:
/u01/app/oracle/cfgtoollogs/orcl/preupgrade/preupgrade_fixups.sql
3. Execute in the NEW environment
AFTER upgrade:
/u01/app/oracle/cfgtoollogs/orcl/preupgrade/postupgrade_fixups.sql
************************************************************
***************************************************************************
Pre-Upgrade Checks in ORCL Completed.
***************************************************************************
***************************************************************************
***************************************************************************
Verify the preupgrade.log and make
necessary changes
$ cd
/u01/app/oracle/cfgtoollogs/UPGR/preupgrade/preupgrade.log
[oracle@localhost
preupgrade]$ cat preupgrade.log
Oracle Database Pre-Upgrade Information Tool 02-08-2017
06:22:36
Script Version: 12.1.0.2.0 Build: 006
**********************************************************************
Database Name: ORCL
Container Name: Not Applicable in Pre-12.1 database
Container ID: Not Applicable in Pre-12.1 database
Version: 11.2.0.4.0
Compatible: 11.2.0.4.0
Blocksize: 8192
Platform: Linux x86 64-bit
Timezone file: V14
**********************************************************************
[Update parameters]
[Update Oracle
Database 11.2.0.4.0 init.ora or spfile]
--> If Target Oracle is 32-bit, refer here for Update
Parameters:
WARNING: --> "processes" needs to be increased
to at least 300
--> If Target Oracle is 64-bit, refer here for Update
Parameters:
WARNING: --> "processes" needs to be increased
to at least 300
**********************************************************************
**********************************************************************
[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
--> Oracle Workspace Manager [upgrade] VALID
--> OLAP Analytic Workspace [upgrade] VALID
--> Oracle Enterprise Manager Repository [upgrade]
VALID
--> Oracle Text [upgrade] VALID
--> Oracle XML Database [upgrade] VALID
--> Oracle Java Packages [upgrade] VALID
--> Oracle Multimedia [upgrade] VALID
--> Oracle Spatial [upgrade] VALID
--> Expression Filter [upgrade] VALID
--> Rule Manager [upgrade]
VALID
--> Oracle Application Express [upgrade] VALID
--> Oracle OLAP API [upgrade] VALID
**********************************************************************
[Tablespaces]
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
minimum required
size: 1245 MB
--> SYSAUX tablespace is adequate for the upgrade.
minimum required
size: 1420 MB
--> UNDOTBS1 tablespace is adequate for the upgrade.
minimum required
size: 400 MB
--> TEMP tablespace is adequate for the upgrade.
minimum required
size: 60 MB
[No adjustments recommended]
**********************************************************************
**********************************************************************
[Pre-Upgrade Checks]
**********************************************************************
WARNING: --> Process Count may be too low
Database has a
maximum process count of 150 which is lower than the
default value of
300 for this release.
You should update
your processes value prior to the upgrade
to a value of at
least 300.
For example:
ALTER SYSTEM
SET PROCESSES=300 SCOPE=SPFILE
or update your init.ora file.
WARNING: --> Enterprise Manager Database Control
repository found in the database
In Oracle Database
12c, Database Control is removed during
the upgrade. To
save time during the Upgrade, this action
can be done prior
to upgrading using the following steps after
copying
rdbms/admin/emremove.sql from the new Oracle home
- Stop EM Database
Control:
$> emctl stop
dbconsole
- Connect to the
Database using the SYS account AS SYSDBA:
SET ECHO ON;
SET SERVEROUTPUT ON;
@emremove.sql
Without the set
echo and serveroutput commands you will not
be able to follow
the progress of the script.
INFORMATION: --> OLAP Catalog(AMD) exists in database
Starting with
Oracle Database 12c, OLAP Catalog component is desupported.
If you are not
using the OLAP Catalog component and want
to remove it, then
execute the
ORACLE_HOME/olap/admin/catnoamd.sql script before or
after the upgrade.
INFORMATION: --> Older Timezone in use
Database is using
a time zone file older than version 18.
After the upgrade,
it is recommended that DBMS_DST package
be used to upgrade
the 11.2.0.4.0 database time zone version
to the latest version
which comes with the new release.
Please refer to My
Oracle Support note number 977512.1 for details.
INFORMATION: --> There are existing Oracle components that
will NOT be
upgraded by the
database upgrade script. Typically, such
components
have their own
upgrade scripts, are deprecated, or obsolete.
Those components
are: OLAP Catalog,OWB
INFORMATION: --> Oracle Application Express (APEX) can be
manually upgraded
prior to database upgrade
APEX is currently
at version 3.2.1.00.12 and will need to be
upgraded to APEX
version 4.2.5 in the new release.
Note 1: To reduce
database upgrade time, APEX can be manually
upgraded
outside of and prior to database upgrade.
Note 2: See MOS
Note 1088970.1 for information on APEX
installation upgrades.
**********************************************************************
[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;
^^^ MANUAL ACTION SUGGESTED ^^^
**********************************************************************
[Post-Upgrade Recommendations]
**********************************************************************
*****************************************
******** Fixed Object Statistics ********
*****************************************
Please create stats on fixed objects two weeks
after the upgrade using the command:
EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
^^^ MANUAL ACTION SUGGESTED ^^^
**********************************************************************
************ Summary ************
0 ERRORS exist in your
database.
2 WARNINGS that Oracle
suggests are addressed to improve database performance.
4 INFORMATIONAL
messages that should be reviewed prior to your upgrade.
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
rdbms/admin/utluiobj.sql
If needed you may want
to upgrade your timezone data using the process
described in My Oracle
Support note 1509653.1
***********************************
The 11.2.0.4 database has the OLAP Catalog AMD component
installed, and this component is no longer included in Oracle Database starting
with Oracle Database 12c.
Remove the OLAP Catalog AMD component using the script from
the 12.1.0.2 Oracle Home:
SQL>
@/u01/app/oracle/product/12.1.0/dbhome_1/olap/admin/catnoamd.sql
Synonym
dropped.
Synonym
dropped.
Synonym
dropped.
Synonym
dropped.
Synonym
dropped.
Synonym
dropped.
Synonym
dropped.
Synonym
dropped.
Synonym
dropped.
Synonym
dropped.
Synonym
dropped.
Synonym
dropped.
Synonym
dropped.
Synonym
dropped.
Synonym
dropped.
Synonym
dropped.
Synonym
dropped.
Synonym
dropped.
Synonym
dropped.
Synonym
dropped.
Synonym
dropped.
Synonym
dropped.
Synonym
dropped.
Synonym
dropped.
Synonym
dropped.
PL/SQL
procedure successfully completed.
1 row
deleted.
SQL> commit;
If The preupgrade log includes a message about moving audit
data from system.aud$ to sys.aud$ because Oracle Label Security is
installed.Move the AUD$ table now using the olspreupgrade.sql script from the
Oracle Database 12c home from SYSTEM to SYS:
SQL> @/u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/olspreupgrade.sql
Function
created.
No
errors.
Function
created.
No
errors.
Function
created.
No
errors.
****THIS
SCRIPT IS NEEDED ONLY IF ORACLE LABEL SECURITY OR DATABASE VAULT ARE
CONFIGURED*****
PL/SQL
procedure successfully completed.
No
errors.
****THIS
SCRIPT IS NEEDED ONLY IF ORACLE LABEL SECURITY OR DATABASE VAULT ARE
CONFIGURED*****
PL/SQL
procedure successfully completed.
No
errors.
Gather dictionary stats prior to the
upgrade:
SQL> EXECUTE dbms_stats.gather_dictionary_stats;
Execute the preupgrade_fixups.sql – it was created by
preupgrd.sql in directory /u01/app/oracle/cfgtoollogs/orcl/preupgrade
SQL>
@/u01/app/oracle/cfgtoollogs/orcl/preupgrade/preupgrade_fixups.sql
Pre-Upgrade
Fixup Script Generated on 2017-02-08 06:22:34
Version: 12.1.0.2 Build: 006
Beginning
Pre-Upgrade Fixups...
Executing
in container ORCL
**********************************************************************
Check
Tag: DEFAULT_PROCESS_COUNT
Check
Summary: Verify min process count is not too low
Fix
Summary: Review and increase if needed,
your PROCESSES value.
**********************************************************************
Fixup
Returned Information:
WARNING:
--> Process Count may be too low
Database has a maximum process count of
150 which is lower than the
default value of 300 for this release.
You should update your processes value
prior to the upgrade
to a value of at least 300.
For example:
ALTER SYSTEM SET PROCESSES=300
SCOPE=SPFILE
or update your init.ora file.
**********************************************************************
**********************************************************************
Check
Tag: EM_PRESENT
Check
Summary: Check if Enterprise Manager is present
Fix
Summary: Execute emremove.sql prior to
upgrade.
**********************************************************************
Fixup
Returned Information:
WARNING:
--> Enterprise Manager Database Control repository found in the database
In Oracle Database 12c, Database Control
is removed during
the upgrade. To save time during the
Upgrade, this action
can be done prior to upgrading using the
following steps after
copying rdbms/admin/emremove.sql from the
new Oracle home
- Stop EM Database Control:
$> emctl stop dbconsole
- Connect to the Database using the SYS
account AS SYSDBA:
SET ECHO ON;
SET SERVEROUTPUT ON;
@emremove.sql
Without the set echo and serveroutput
commands you will not
be able to follow the progress of the
script.
**********************************************************************
**********************************************************************
Check
Tag: AMD_EXISTS
Check
Summary: Check to see if AMD is present in the database
Fix
Summary: Manually execute
ORACLE_HOME/oraolap/admin/catnoamd.sql script to remove OLAP.
**********************************************************************
Fixup
Returned Information:
INFORMATION:
--> OLAP Catalog(AMD) exists in database
Starting with Oracle Database 12c, OLAP
Catalog component is desupported.
If you are not using the OLAP Catalog
component and want
to remove it, then execute the
ORACLE_HOME/olap/admin/catnoamd.sql script
before or
after the upgrade.
**********************************************************************
**********************************************************************
Check
Tag: APEX_UPGRADE_MSG
Check
Summary: Check that APEX will need to be upgraded.
Fix
Summary: Oracle Application Express can
be manually upgraded prior to database upgrade.
**********************************************************************
Fixup
Returned Information:
INFORMATION:
--> Oracle Application Express (APEX) can be
manually upgraded prior to database
upgrade
APEX is currently at version 3.2.1.00.12
and will need to be
upgraded to APEX version 4.2.5 in the new
release.
Note 1: To reduce database upgrade time,
APEX can be manually
upgraded outside of and prior to
database upgrade.
Note 2: See MOS Note 1088970.1 for
information on APEX
installation upgrades.
**********************************************************************
**********************************************************************
[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;
^^^
MANUAL ACTION SUGGESTED ^^^
**************************************************
************* Fixup Summary
************
4 fixup routines generated INFORMATIONAL
messages that should be reviewed.
****************
Pre-Upgrade Fixup Script Complete *********************
PL/SQL
procedure successfully completed.
SQL >shutdown
immediate
Copy Pfile,password file to the new
Oracle Home 12c.
Open the database
in upgrade mode
SQL> startup
upgrade
Then run the
Upgrade script
$ORACLE_HOME/perl/bin/perl
catctl.pl catupgrd.sql
Startup the database and recompile everything:
SQL> startup
SQL>@?/rdbms/admin/utlrp.sql
Execute the postupgrade_fixups.sql:
SQL>@/u01/app/oracle/cfgtoollogs/orcl/preupgrade/postupgrade_fixups.sql
Post Upgrade Fixup Script Generated on 2017-02-08
06:22:34 Version: 12.1.0.2 Build: 006
Beginning Post-Upgrade Fixups...
**********************************************************************
Check Tag:
OLD_TIME_ZONES_EXIST
Check Summary: Check for use of older timezone data file
Fix Summary:
Update the timezone using the DBMS_DST package after upgrade is
complete.
**********************************************************************
Fixup Returned Information:
INFORMATION: --> Older Timezone in use
Database is
using a time zone file older than version 18.
After the
upgrade, it is recommended that DBMS_DST package
be used to
upgrade the 12.1.0.2.0 database time zone version
to the latest
version which comes with the new release.
Please refer
to My Oracle Support note number 977512.1 for details.
**********************************************************************
**********************************************************************
Check Tag:
NOT_UPG_BY_STD_UPGRD
Check Summary: Identify existing components that will NOT
be upgraded
Fix Summary: This
fixup does not perform any action.
**********************************************************************
Fixup Returned Information:
This fixup does not perform any action.
If you want to upgrade those other components, you must
do so manually.
**********************************************************************
**********************************************************************
[Post-Upgrade Recommendations]
**********************************************************************
*****************************************
******** Fixed Object Statistics ********
*****************************************
Please create stats on fixed objects two weeks
after the upgrade using the command:
EXECUTE
DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
^^^ MANUAL ACTION SUGGESTED ^^^
**************************************************
************* Fixup Summary ************
2 fixup routines
generated INFORMATIONAL messages that should be reviewed.
*************** Post Upgrade Fixup Script Complete
********************
PL/SQL procedure successfully completed.
Adjust Time Zone settings – you may look into the scripts
taken from MOS Note: 1509653.1 before executing them:
SQL>@/home/oracle/DST/DST_prepare.sql
SQl>@/home/oracle/DST/DST_adjust.sql
SQL>exit;
And database Upgrade
done successfully.