Pre-Upgrade Tasks
Backup of the Database
Backup of the database is needed in order to guarantee database availability in case of corruption: Backup can be done either via:
- FULL File System Backup
- RMAN Backup ← My Preferred choice.
[oracle@lparaca ~]$ srvctl stop database -d orcl [oracle@lparaca ~]$ sqlplus SQL*Plus: Release 12.1.0.2.0 Production on Tue Jan 23 09:43:23 2018 Copyright (c) 1982, 2014, Oracle. All rights reserved. Enter user-name: / as sysdba Connected to an idle instance. SQL> startup mount; ORACLE instance started. Total System Global Area 1174405120 bytes Fixed Size 2923680 bytes Variable Size 503317344 bytes Database Buffers 654311424 bytes Redo Buffers 13852672 bytes Database mounted. SQ> exit; [oracle@lparaca ~]$ rman target / RMAN> configure channel device type disk format '/backup/ora_bkp_%U'; old RMAN configuration parameters: CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/backup/ora_bkp%F'; new RMAN configuration parameters: CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/backup/ora_bkp_%U'; new RMAN configuration parameters are successfully stored released channel: ORA_DISK_1 RMAN> backup database include current controlfile; Starting backup at 23-JAN-18 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=22 instance=orcl_1 device type=DISK channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set input datafile file number=00003 name=+DATA/ORCL/DATAFILE/sysaux.277.965796631 input datafile file number=00001 name=+DATA/ORCL/DATAFILE/system.278.965796673 input datafile file number=00004 name=+DATA/ORCL/DATAFILE/undotbs1.280.965796733 input datafile file number=00005 name=+DATA/ORCL/DATAFILE/undotbs2.285.965797041 input datafile file number=00006 name=+DATA/ORCL/DATAFILE/users.279.965796729 channel ORA_DISK_1: starting piece 1 at 23-JAN-18 channel ORA_DISK_1: finished piece 1 at 23-JAN-18 piece handle=/backup/ora_bkp_03spcrma_1_1 tag=TAG20180123T094754 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:01:25 channel ORA_DISK_1: starting full datafile backup set channel ORA_DISK_1: specifying datafile(s) in backup set including current control file in backup set including current SPFILE in backup set channel ORA_DISK_1: starting piece 1 at 23-JAN-18 channel ORA_DISK_1: finished piece 1 at 23-JAN-18 piece handle=/backup/ora_bkp_04spcrov_1_1 tag=TAG20180123T094754 comment=NONE channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01 Finished backup at 23-JAN-18
Perform Pre-Upgrade Check(Mandatory)
[oracle@lparaca admin]$ srvctl status database -d orcl^C
[oracle@lparaca admin]$
[oracle@lparaca admin]$ export ORACLE_SID=orcl_2
[oracle@lparaca admin]$ /u01/app/oracle/product/12.2.0.1/jdk/bin/java -jar preupgrade.jar TERMINAL
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: ORCL
Container ID: 0
Version: 12.1.0.2.0
Compatible: 12.1.0.2.0
Blocksize: 8192
Platform: Linux 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] VALID
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 <preupgradeLogDirPath>/preupgrade_fixups.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
---------- ---------- -------- ---------- ------
SYSAUX 870 MB ENABLED 1768 MB None
SYSTEM 800 MB ENABLED 1307 MB None
TEMP 204 MB DISABLED 150 MB None
UNDOTBS2 50 MB ENABLED 400 MB None
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
===================
+ Run 12.1.0.2.0 $ORACLE_HOME/rdbms/admin/utlrp.sql to recompile invalid
objects. You can view the individual invalid objects with
SET SERVEROUTPUT ON;
EXECUTE DBMS_PREUP.INVALID_OBJECTS;
1 objects are INVALID.
There should be no INVALID objects in SYS/SYSTEM or user schemas before
database upgrade.
INFORMATION ONLY
================
+ Consider upgrading APEX manually, before the database upgrade.
The database contains APEX version 4.2.5.00.08 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 <preupgradeLogDirPath>/postupgrade_fixups.sql to complete all
of the AFTER UPGRADE action items below marked with '(AUTOFIXUP)'.
REQUIRED ACTIONS
================
None
RECOMMENDED ACTIONS
===================
+ 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.
Preupgrade generated files:
/u01/app/oracle/product/cfgtoollogs/orcl/preupgrade/preupgrade_fixups.sql
/u01/app/oracle/product/cfgtoollogs/orcl/preupgrade/postupgrade_fixups.sql
The two execution files generated by this script:
- preupgrade_fixups.sql
- postupgrade_fixups.sql
Should be run respectfully before and after the upgrade.
Pre-Upgrade Fixup
[oracle@lparaca admin]$ sqlplus
SQL*Plus: Release 12.1.0.2.0 Production on Tue Jan 23 11:56:49 2018
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Enter user-name: / as sysdba
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
SQL> @/u01/app/oracle/product/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-01-23 10:25:42
For Source Database: ORCL
Source Database Version: 12.1.0.2.0
For Upgrade to Version: 12.2.0.1.0
Fixup
Check Name Status Further DBA Action
---------- ------ ------------------
invalid_objects_exist Failed Manual fixup recommended.
apex_upgrade_msg Failed Manual fixup recommended.
PL/SQL procedure successfully completed.
In case the script, cannot fix the requirements, fix them manually:
Invalid Objects
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql
TIMESTAMP
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2018-01-23 11:57:26
DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC> objects in the database. Recompilation time is proportional to the
DOC> number of invalid objects in the database, so this command may take
DOC> a long time to execute on a database with a large number of invalid
DOC> objects.
DOC>
DOC> Use the following queries to track recompilation progress:
DOC>
DOC> 1. Query returning the number of invalid objects remaining. This
DOC> number should decrease with time.
DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC> 2. Query returning the number of objects compiled so far. This number
DOC> should increase with time.
DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC> This script automatically chooses serial or parallel recompilation
DOC> based on the number of CPUs available (parameter cpu_count) multiplied
DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC> On RAC, this number is added across all RAC nodes.
DOC>
DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC> recompilation. Jobs are created without instance affinity so that they
DOC> can migrate across RAC nodes. Use the following queries to verify
DOC> whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC> 1. Query showing jobs created by UTL_RECOMP
DOC> SELECT job_name FROM dba_scheduler_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC> 2. Query showing UTL_RECOMP jobs that are running
DOC> SELECT job_name FROM dba_scheduler_running_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#
PL/SQL procedure successfully completed.
TIMESTAMP
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2018-01-23 11:57:34
DOC> The following query reports the number of objects that have compiled
DOC> with errors.
DOC>
DOC> If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#
OBJECTS WITH ERRORS
-------------------
0
DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#
ERRORS DURING RECOMPILATION
---------------------------
0
Function created.
PL/SQL procedure successfully completed.
Function dropped.
...Database user "SYS", database schema "APEX_040200", user# "98" 11:58:07
...Compiled 0 out of 3014 objects considered, 0 failed compilation 11:58:07
...271 packages
...263 package bodies
...452 tables
...11 functions
...16 procedures
...3 sequences
...457 triggers
...1320 indexes
...211 views
...0 libraries
...6 types
...0 type bodies
...0 operators
...0 index types
...Begin key object existence check 11:58:07
...Completed key object existence check 11:58:08
...Setting DBMS Registry 11:58:08
...Setting DBMS Registry Complete 11:58:08
...Exiting validate 11:58:08
PL/SQL procedure successfully completed.
Statistics
SQL> EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS; PL/SQL procedure successfully completed. SQL> SQL>