This article provides an overview of upgrading an existing non-CDB database from Oracle 11.2.0.4 to Oracle 12.2.0.1. Upgrades can be very complicated, so this is highly recommended to read the oracle provided manual and test thoroughly before considering an upgrade of a production environment.
- 1. Upgrade Path for 12.2 Oracle database.
- 2. Requirements and recommendations for source database.
- 3. Requirements and recommendations for target database.
- 4. Pre-upgrade checks.
- 5. Preupgrade step.
- 6. Upgrade Database to 12.2 using DBUA.
- 7. Rollback to Earlier Version (11.2.0.4).
- 8. Manually Upgrade Database to 12.2.
- 9. Post-upgrade.
1. Upgrade Path for 12.2 Oracle database:
2. Requirements and recommendations for source database:
- Take a cold or hot back up of the source database.
- Disable any custom triggers that would get executed before / after DDL. You can enable it once upgrade is completed.
- Data security roles defined in a release 11g Oracle Database instance are not automatically converted to ORAS. Before you upgrade an 11g database to Oracle Database 12c, you must delete any data security roles that are defined in the 11g database. After the upgrade, you may use Analytic Workspace Manager 12c to define the data security roles again.
- If you upgrade an 11g database to Oracle Database 12c without deleting the 11g data security roles, then any data security policies that include a data security role are invalid in the Oracle Database 12c database.
- Timezone should less than or equal to target database timezone version.
- IF APEX is installed then it is recommended to upgrade APEX in the source DB first before upgrading DB
2.1. Take a cold or hot back up of the source database:
During database upgradation cutover window, we can take backup. Details on session 5.6.
2.2. Disable any custom triggers that would get executed before / after DDL:
No Custom Trigger.
2.3. Timezone should less than or equal to target database timezone version:
SQL> select TZ_VERSION from registry$database;
TZ_VERSION
----------
14
SQL> COL VALUE FOR A20
SQL> COL PROPERTY_NAME FOR A50
SQL> SET LINESIZE 150
SQL>
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%';
PROPERTY_NAME VALUE
-------------------------------------------------- --------------------
DST_UPGRADE_STATE NONE
DST_PRIMARY_TT_VERSION 14
DST_SECONDARY_TT_VERSION 0
2.4. Upgrade APEX in the source DB first before upgrading DB:
If APEX is installed on your database then it is recommended to upgrade APEX in the source DB first before upgrading DB.
3. Requirements and recommendations for target database:
- Install Oracle Database 12.2.0.1 (Software Only)
- Install 12.2.0.1, verify there are no installation related issues.
- Download and install latest PSU if any
- Copy spfile or pfile from source ORACLE_HOME (under $ORACLE_HOME/dbs) to target home
- Remove any _parameter, obsolete and deprecated parameters in pfile
- Note min value of COMPATIBLE parameter to upgrade 12.2 is “11.2.0”, make sure you have COMPATIBLE parameter is set to 11.2.0 or greater
3.1. Perform Oracle Installation Prerequisites:
[root@dbupgrade ~]# yum install oracle-database-server-12cR2-preinstall -y Loaded plugins: langpacks ol7_UEKR3 | 2.5 kB 00:00:00 ol7_latest | 2.7 kB 00:00:00 Resolving Dependencies --> Running transaction check ---> Package oracle-database-server-12cR2-preinstall.x86_64 0:1.0-5.el7 will be installed --> Finished Dependency Resolution Dependencies Resolved =========================================================================================================================== Package Arch Version Repository Size =========================================================================================================================== Installing: oracle-database-server-12cR2-preinstall x86_64 1.0-5.el7 ol7_latest 19 k Transaction Summary =========================================================================================================================== Install 1 Package Total download size: 19 k Installed size: 55 k Downloading packages: oracle-database-server-12cR2-preinstall-1.0-5.el7.x86_64.rpm | 19 kB 00:00:01 Running transaction check Running transaction test Transaction test succeeded Running transaction Installing : oracle-database-server-12cR2-preinstall-1.0-5.el7.x86_64 1/1 Verifying : oracle-database-server-12cR2-preinstall-1.0-5.el7.x86_64 1/1 Installed: oracle-database-server-12cR2-preinstall.x86_64 0:1.0-5.el7 Complete!
3.2. Install Oracle Database 12.2.0.1 (Software Only):
./runInstaller -ignorePrereq -waitforcompletion -silent \
-responseFile /u01/software/database/response/db_install.rsp \
oracle.install.option=INSTALL_DB_SWONLY \
ORACLE_HOSTNAME=dbupgrade.localdomain \
UNIX_GROUP_NAME=oinstall \
INVENTORY_LOCATION=/u01/app/oraInventory \
SELECTED_LANGUAGES=en \
ORACLE_HOME=/u01/app/oracle/product/12.2.0.1/dbhome_1 \
ORACLE_BASE=/u01/app/oracle \
oracle.install.db.InstallEdition=EE \
oracle.install.db.OSDBA_GROUP=dba \
oracle.install.db.OSBACKUPDBA_GROUP=dba \
oracle.install.db.OSDGDBA_GROUP=dba \
oracle.install.db.OSKMDBA_GROUP=dba \
oracle.install.db.OSRACDBA_GROUP=dba \
SECURITY_UPDATES_VIA_MYORACLESUPPORT=false \
DECLINE_SECURITY_UPDATES=true
Starting Oracle Universal Installer...
Checking Temp space: must be greater than 500 MB. Actual 16889 MB Passed
Checking swap space: must be greater than 150 MB. Actual 6143 MB Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2020-10-05_02-42-58PM. Please wait ...[WARNING] [INS-32016] The selected Oracle home contains directories or files.
ACTION: To start with an empty Oracle home, either remove its contents or choose another location.
You can find the log of this install session at:
/u01/app/oraInventory/logs/installActions2020-10-05_02-42-58PM.log
The installation of Oracle Database 12c was successful.
Please check '/u01/app/oraInventory/logs/silentInstall2020-10-05_02-42-58PM.log' for more details.
As a root user, execute the following script(s):
1. /u01/app/oraInventory/orainstRoot.sh
2. /u01/app/oracle/product/12.2.0.1/dbhome_1/root.sh
Successfully Setup Software.
3.3. Run root script:
[oracle@dbupgrade database]$ su - root Password: Last login: Sun Oct 4 23:23:22 EDT 2020 from 192.168.56.1 on pts/0 [root@dbupgrade ~]# /u01/app/oraInventory/orainstRoot.sh Changing permissions of /u01/app/oraInventory. Adding read,write permissions for group. Removing read,write,execute permissions for world. Changing groupname of /u01/app/oraInventory to oinstall. The execution of the script is complete. [root@dbupgrade ~]# /u01/app/oracle/product/12.2.0.1/dbhome_1/root.sh Check /u01/app/oracle/product/12.2.0.1/dbhome_1/install/root_dbupgrade.localdomain_2020-10-05_14-49-20-407901660.log for the output of root script
3.4. Install 12.2.0.1, verify there are no installation related issues:
Verified and No error.
3.5. Copy spfile or pfile from source database 11.2.0.4 to target database 12.2.0.1:
SQL> show parameter spfile NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileorcl.ora [oracle@dbupgrade dbs]$ pwd /u01/app/oracle/product/11.2.0/dbhome_1/dbs [oracle@dbupgrade dbs]$ ls -lrt total 24 -rw-r--r--. 1 oracle oinstall 2851 May 15 2009 init.ora -rw-r-----. 1 oracle oinstall 24 Oct 4 15:56 lkORCL -rw-r-----. 1 oracle oinstall 1536 Oct 4 15:56 orapworcl -rw-r-----. 1 oracle oinstall 931 Oct 5 15:05 pfile_orcl.ora -rw-rw----. 1 oracle oinstall 1544 Oct 5 15:10 hc_orcl.dat -rw-r-----. 1 oracle oinstall 2560 Oct 5 15:10 spfileorcl.ora [oracle@dbupgrade dbs]$ cp *ora /u01/app/oracle/product/12.2.0.1/dbhome_1/dbs [oracle@dbupgrade dbs]$ cp orapworcl /u01/app/oracle/product/12.2.0.1/dbhome_1/dbs
3.6. COMPATIBLE Values (should be minimum 11.2 on source DB):
SQL> show parameter COMPATIBLE NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ compatible string 11.2.0.4.0
4. Pre-upgrade Checks:
- Execute dbupgdiag.sql (refer note 556610.1 to download this script) and verify whether there are any INVALID components or objects owned by SYS/SYSTEM. If any, fix them before proceeding to upgrade the database. You can execute utlrp.sql multiple times to Validate them, if still objects are INVALID, create a service request with Oracle support.
- Execute utlrp.sql multiple times and verify there are no INVALID objects.
4.1 Download dbupgdiag.sql and execute on source database:
SQL> @dbupgdiag.sql
Enter location for Spooled output:
Enter value for 1: /home/oracle
06_Oct_2020_0113 .log
orcl_
*** Start of LogFile ***
Oracle Database Upgrade Diagnostic Utility 10-06-2020 13:13:59
===============
Hostname
===============
dbupgrade.localdomain
===============
Database Name
===============
ORCL
===============
Database Uptime
===============
12:58 06-OCT-20
=================
Database Wordsize
=================
This is a 64-bit database
================
Software Version
================
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
=============
Compatibility
=============
Compatibility is set as 11.2.0.4.0
================
Archive Log Mode
================
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Next log sequence to archive 3
Current log sequence 3
================
Auditing Check
================
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /u01/app/oracle/admin/orcl/adu
mp
audit_sys_operations boolean FALSE
audit_syslog_level string
audit_trail string DB
================
Cluster Check
================
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cluster_database boolean FALSE
cluster_database_instances integer 1
DOC>################################################################
DOC>
DOC> If CLUSTER_DATABASE is set to TRUE, change it to FALSE before
DOC> upgrading the database
DOC>
DOC>################################################################
DOC>#
===========================================
Tablespace and the owner of the aud$ table ( IF Oracle Label Security and Oracle Database Vault are installed then aud$ will be in SYSTEM.AUD$)
===========================================
OWNER TABLESPACE_NAME
------------ ------------------------------
SYS SYSTEM
============================================================================
count of records in the sys.aud$ table where dbid is null- Standard Auditing
============================================================================
0
============================================================================================
count of records in the system.aud$ when dbid is null, Std Auditing with OLS or DV installed
============================================================================================
select count(*) from system.aud$ where dbid is null
*
ERROR at line 1:
ORA-00942: table or view does not exist
=============================================================================
count of records in the sys.fga_log$ when dbid is null, Fine Grained Auditing
=============================================================================
0
==========================================
Oracle Label Security is installed or not
==========================================
Oracle Label Security is NOT installed at database level
================
Number of AQ Records in Message Queue Tables
================
SYS - ALERT_QT - 0
SYS - AQ$_MEM_MC - 0
SYS - AQ_EVENT_TABLE - 0
SYS - AQ_PROP_TABLE - 0
SYS - KUPC$DATAPUMP_QUETAB - 0
SYS - SCHEDULER$_EVENT_QTAB - 0
SYS - SCHEDULER$_REMDB_JOBQTAB - 0
SYS - SCHEDULER_FILEWATCHER_QT - 0
SYS - SYS$SERVICE_METRICS_TAB - 0
SYSMAN - MGMT_LOADER_QTABLE - 0
SYSMAN - MGMT_NOTIFY_INPUT_QTABLE - 0
SYSMAN - MGMT_NOTIFY_QTABLE - 0
SYSMAN - MGMT_PAF_MSG_QTABLE_1 - 0
SYSMAN - MGMT_PAF_MSG_QTABLE_2 - 0
SYSMAN - MGMT_TASK_QTABLE - 27
SYSTEM - DEF$_AQCALL - 0
SYSTEM - DEF$_AQERROR - 0
WMSYS - WM$EVENT_QUEUE_TABLE - 0
================
Time Zone version
================
14
================
Local Listener
================
================
Default and Temporary Tablespaces By User
================
USERNAME TEMPORARY_TABLESPACE DEFAULT_TABLESPACE
---------------------------- ---------------------- ----------------------
SYS TEMP SYSTEM
SYSTEM TEMP SYSTEM
OUTLN TEMP SYSTEM
MGMT_VIEW TEMP SYSTEM
FLOWS_FILES TEMP SYSAUX
MDSYS TEMP SYSAUX
ORDSYS TEMP SYSAUX
EXFSYS TEMP SYSAUX
DBSNMP TEMP SYSAUX
WMSYS TEMP SYSAUX
APPQOSSYS TEMP SYSAUX
APEX_030200 TEMP SYSAUX
OWBSYS_AUDIT TEMP SYSAUX
ORDDATA TEMP SYSAUX
CTXSYS TEMP SYSAUX
ANONYMOUS TEMP SYSAUX
SYSMAN TEMP SYSAUX
XDB TEMP SYSAUX
ORDPLUGINS TEMP SYSAUX
OWBSYS TEMP SYSAUX
SI_INFORMTN_SCHEMA TEMP SYSAUX
OLAPSYS TEMP SYSAUX
SCOTT TEMP USERS
ORACLE_OCM TEMP USERS
XS$NULL TEMP USERS
MDDATA TEMP USERS
DIP TEMP USERS
APEX_PUBLIC_USER TEMP USERS
SPATIAL_CSW_ADMIN_USR TEMP USERS
SPATIAL_WFS_ADMIN_USR TEMP USERS
================
Component Status
================
Comp ID Component Status Version Org_Version Prv_Version
------- ---------------------------------- --------- -------------- -------------- --------------
AMD OLAP Catalog VALID 11.2.0.4.0
APEX Oracle Application Express VALID 3.2.1.00.12
APS OLAP Analytic Workspace VALID 11.2.0.4.0
CATALOG Oracle Database Catalog Views VALID 11.2.0.4.0
CATJAVA Oracle Database Java Packages VALID 11.2.0.4.0
CATPROC Oracle Database Packages and Types VALID 11.2.0.4.0
CONTEXT Oracle Text VALID 11.2.0.4.0
EM Oracle Enterprise Manager VALID 11.2.0.4.0
EXF Oracle Expression Filter VALID 11.2.0.4.0
JAVAVM JServer JAVA Virtual Machine VALID 11.2.0.4.0
ORDIM Oracle Multimedia VALID 11.2.0.4.0
OWB OWB VALID 11.2.0.4.0
OWM Oracle Workspace Manager VALID 11.2.0.4.0
RUL Oracle Rules Manager VALID 11.2.0.4.0
SDO Spatial VALID 11.2.0.4.0
XDB Oracle XML Database VALID 11.2.0.4.0
XML Oracle XDK VALID 11.2.0.4.0
XOQ Oracle OLAP API VALID 11.2.0.4.0
======================================================
List of Invalid Database Objects Owned by SYS / SYSTEM
======================================================
Number of Invalid Objects
------------------------------------------------------------------
There are no Invalid Objects
DOC>################################################################
DOC>
DOC> If there are no Invalid objects below will result in zero rows.
DOC>
DOC>################################################################
DOC>#
no rows selected
================================
List of Invalid Database Objects
================================
Number of Invalid Objects
------------------------------------------------------------------
There are no Invalid Objects
DOC>################################################################
DOC>
DOC> If there are no Invalid objects below will result in zero rows.
DOC>
DOC>################################################################
DOC>#
no rows selected
======================================================
Count of Invalids by Schema
======================================================
==============================================================
Identifying whether a database was created as 32-bit or 64-bit
==============================================================
DOC>###########################################################################
DOC>
DOC> Result referencing the string 'B023' ==> Database was created as 32-bit
DOC> Result referencing the string 'B047' ==> Database was created as 64-bit
DOC> When String results in 'B023' and when upgrading database to 10.2.0.3.0
DOC> (64-bit) , For known issue refer below articles
DOC>
DOC> Note 412271.1 ORA-600 [22635] and ORA-600 [KOKEIIX1] Reported While
DOC> Upgrading Or Patching Databases To 10.2.0.3
DOC> Note 579523.1 ORA-600 [22635], ORA-600 [KOKEIIX1], ORA-7445 [KOPESIZ] and
DOC> OCI-21500 [KOXSIHREAD1] Reported While Upgrading To 11.1.0.6
DOC>
DOC>###########################################################################
DOC>#
Metadata Initial DB Creation Info
-------- -----------------------------------
B047 Database was created as 64-bit
===================================================
Number of Duplicate Objects Owned by SYS and SYSTEM
===================================================
Counting duplicate objects ....
COUNT(1)
----------
0
=========================================
Duplicate Objects Owned by SYS and SYSTEM
=========================================
Querying duplicate objects ....
DOC>
DOC>################################################################################
DOC>Below are expected and required duplicates objects and OMITTED in the report .
DOC>
DOC>Without replication installed:
DOC>INDEX AQ$_SCHEDULES_PRIMARY
DOC>TABLE AQ$_SCHEDULES
DOC>
DOC>If replication is installed by running catrep.sql:
DOC>INDEX AQ$_SCHEDULES_PRIMARY
DOC>PACKAGE DBMS_REPCAT_AUTH
DOC>PACKAGE BODY DBMS_REPCAT_AUTH
DOC>TABLE AQ$_SCHEDULES
DOC>
DOC>If any objects found please follow below article.
DOC>Note 1030426.6 How to Clean Up Duplicate Objects Owned by SYS and SYSTEM schema
DOC>Read the Exceptions carefully before taking actions.
DOC>
DOC>################################################################################
DOC>#
========================
Password protected roles
========================
DOC>
DOC>################################################################################
DOC>
DOC> In version 11.2 password protected roles are no longer enabled by default so if
DOC> an application relies on such roles being enabled by default and no action is
DOC> performed to allow the user to enter the password with the set role command, it
DOC> is recommended to remove the password from those roles (to allow for existing
DOC> privileges to remain available). For more information see:
DOC>
DOC> Note 745407.1 : What Roles Can Be Set as Default for a User?
DOC>
DOC>################################################################################
DOC>#
Querying for password protected roles ....
Password protected Role Assigned by default to user
------------------------------ ------------------------------
OWB$CLIENT OWBSYS
================
JVM Verification
================
================================================
Checking Existence of Java-Based Users and Roles
================================================
DOC>
DOC>################################################################################
DOC>
DOC> There should not be any Java Based users for database version 9.0.1 and above.
DOC> If any users found, it is faulty JVM.
DOC>
DOC>################################################################################
DOC>#
User Existence
---------------------------
No Java Based Users
DOC>
DOC>###############################################################
DOC>
DOC> Healthy JVM Should contain Six Roles. For 12.2 Seven Roles
DOC> If there are more or less than six role, JVM is inconsistent.
DOC>
DOC>###############################################################
DOC>#
Role
------------------------------
There are 6 JAVA related roles
Roles
ROLE
------------------------------
JAVA_DEPLOY
JAVAUSERPRIV
JAVAIDPRIV
JAVASYSPRIV
JAVADEBUGPRIV
JAVA_ADMIN
=========================================
List of Invalid Java Objects owned by SYS
=========================================
There are no SYS owned invalid JAVA objects
DOC>
DOC>#################################################################
DOC>
DOC> Check the status of the main JVM interface packages DBMS_JAVA
DOC> and INITJVMAUX and make sure it is VALID.
DOC>
DOC> If there are no Invalid objects below will result in zero rows.
DOC>
DOC>#################################################################
DOC>#
no rows selected
DOC>
DOC>#################################################################
DOC>
DOC> If the JAVAVM component is not installed in the database (for
DOC> example, after creating the database with custom scripts), the
DOC> next query will report the following error:
DOC>
DOC> select dbms_java.longname('foo') "JAVAVM TESTING" from dual
DOC> *
DOC> ERROR at line 1:
DOC> ORA-00904: "DBMS_JAVA"."LONGNAME": invalid identifier
DOC>
DOC> If the JAVAVM component is installed, the query should succeed
DOC> with 'foo' as result.
DOC>
DOC>#################################################################
DOC>#
JAVAVM TESTING
---------------
foo
===================================
Oracle Multimedia/InterMedia status
===================================
.
Oracle Multimedia/interMedia is installed and listed with the following version: 11.2.0.4.0 and status: VALID
.
Checking for installed Database Schemas...
ORDSYS user exists.
ORDPLUGINS user exists.
MDSYS user exists.
SI_INFORMTN_SCHEMA user exists.
ORDDATA user exists.
.
Checking for Prerequisite Components...
JAVAVM installed and listed as valid
XDK installed and listed as valid
XDB installed and listed as valid
Validating Oracle Multimedia/interMedia...(no output if component status is valid)
PL/SQL procedure successfully completed.
*** End of LogFile ***
Upload db_upg_diag_orcl_06_Oct_2020_0113.log from "/home/oracle" directory
5. Preupgrade Step:
5.1 Run Preupgrade Script:
[oracle@dbupgrade dbs]$ export ORACLE_11G_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
[oracle@dbupgrade dbs]$ export ORACLE_12C_HOME=/u01/app/oracle/product/12.2.0.1/dbhome_1
[oracle@dbupgrade dbs]$
[oracle@dbupgrade dbs]$ $ORACLE_11G_HOME/jdk/bin/java -jar $ORACLE_12C_HOME/rdbms/admin/preupgrade.jar FILE TERMINAL TEXT
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: 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: 14
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
Oracle Workspace Manager [to be upgraded] VALID
OLAP Analytic Workspace [to be upgraded] VALID
Oracle Enterprise Manager Repository [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
Expression Filter [to be upgraded] VALID
Rule Manager [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 490 MB ENABLED 1404 MB None
SYSTEM 740 MB ENABLED 1245 MB None
TEMP 20 MB ENABLED 150 MB None
UNDOTBS1 30 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.
+ Update NUMERIC INITIALIZATION PARAMETERS to meet estimated minimums.
Parameter 12.2.0.1.0 minimum
--------- ------------------
processes 300
RECOMMENDED ACTIONS
===================
+ Remove the EM repository.
- Copy the $ORACLE_HOME/rdbms/admin/emremove.sql script from the target
12.2.0.1.0 ORACLE_HOME into the source 11.2.0.4.0 ORACLE_HOME.
Step 1: If database control is configured, stop EM Database Control,
using the following command
$> emctl stop dbconsole
Step 2: 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.
The database has an Enterprise Manager Database Control repository.
Starting with Oracle Database 12c, the local Enterprise Manager Database
Control does not exist anymore. The repository will be removed from your
database during the upgrade. This step can be manually performed before
the upgrade to reduce downtime.
+ Remove OLAP Catalog by running the 11.2.0.4.0 SQL script
$ORACLE_HOME/olap/admin/catnoamd.sql script.
The OLAP Catalog component, AMD, exists in the database.
Starting with Oracle Database 12c, the OLAP Catalog (OLAP AMD) is
desupported and will be automatically marked as OPTION OFF during the
database upgrade if present. Oracle recommends removing OLAP Catalog
(OLAP AMD) before database upgrade.
+ (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 11.2.0.4
Oracle Database Performance Tuning Guide.
+ Directly grant ADMINISTER DATABASE TRIGGER privilege to the owner of the
trigger or drop and re-create the trigger with a user that was granted
directly with such. You can list those triggers using "SELECT OWNER,
TRIGGER_NAME FROM DBA_TRIGGERS WHERE BASE_OBJECT_TYPE=''DATABASE'' AND
OWNER NOT IN (SELECT GRANTEE FROM DBA_SYS_PRIVS WHERE
PRIVILEGE=''ADMINISTER DATABASE TRIGGER'')"
There is one or more database triggers whose owner does not have the
right privilege on the database.
The creation of database triggers must be done by users granted with
ADMINISTER DATABASE TRIGGER privilege. Privilege must have been granted
directly.
INFORMATION ONLY
================
+ Consider upgrading APEX manually, before the database upgrade.
The database contains APEX version 3.2.1.00.12 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 14 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.
INFORMATION ONLY
================
+ Check the Oracle documentation for the identified components for their
specific upgrade procedure.
The database upgrade script will not upgrade the following Oracle
components: OLAP Catalog,OWB
The Oracle database upgrade script upgrades most, but not all Oracle
Database components that may be installed. Some components that are not
upgraded may have their own upgrade scripts, or they may be deprecated
or obsolete.
Preupgrade generated files:
/u01/app/oracle/cfgtoollogs/orcl/preupgrade/preupgrade_fixups.sql
/u01/app/oracle/cfgtoollogs/orcl/preupgrade/postupgrade_fixups.sql
5.2 Gather Dictionary Statistics:
SQL> EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS; PL/SQL procedure successfully completed.
5.3. Compile Objects:
SQL> @?/rdbms/admin/utlrp.sql
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2020-10-05 17:24:10
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 2020-10-05 17:24:10
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.
PL/SQL procedure successfully completed.
SQL>
SQL> select count(*) from dba_objects where status='INVALID';
COUNT(*)
----------
0
5.4. List of Invalid Objects:
SQL> select count(*) from dba_objects where status='INVALID';
COUNT(*)
----------
0
5.5. Purge Recyclebin:
SQL> PURGE DBA_RECYCLEBIN; DBA Recyclebin purged.
SQL> @/u01/app/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: 2020-10-05 15:42:49
For Source Database: ORCL
Source Database Version: 11.2.0.4.0
For Upgrade to Version: 12.2.0.1.0
Fixup
Check Name Status Further DBA Action
---------- ------ ------------------
em_present Failed Manual fixup recommended.
amd_exists Failed Manual fixup recommended.
dictionary_stats Passed None
trgowner_no_admndbtrg Failed Manual fixup recommended.
apex_upgrade_msg Failed Manual fixup recommended.
PL/SQL procedure successfully completed.
5.6. Database Backup:
Highly recommended to take cold backup. If not possible due to database size and downtime, then take online database full backup using RMAN before starting actual upgradation, stop all application connection then take incremental or archive log backup.
RMAN> run
{
allocate channel ch01 type disk format '/u01/software/ch01_orcl%U.rman' ;
BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG;
release channel ch01;
}2> 3> 4> 5> 6>
allocated channel: ch01
channel ch01: SID=35 device type=DISK
Starting backup at 05-OCT-20
current log archived
channel ch01: starting compressed archived log backup set
channel ch01: specifying archived log(s) in backup set
input archived log thread=1 sequence=5 RECID=1 STAMP=1053027873
channel ch01: starting piece 1 at 05-OCT-20
channel ch01: finished piece 1 at 05-OCT-20
piece handle=/u01/software/ch01_orcl01vc7rh1_1_1.rman tag=TAG20201005T194433 comment=NONE
channel ch01: backup set complete, elapsed time: 00:00:01
Finished backup at 05-OCT-20
Starting backup at 05-OCT-20
channel ch01: starting compressed full datafile backup set
channel ch01: specifying datafile(s) in backup set
input datafile file number=00001 name=/u02/oradata/orcl/orcl/system01.dbf
input datafile file number=00002 name=/u02/oradata/orcl/orcl/sysaux01.dbf
input datafile file number=00003 name=/u02/oradata/orcl/orcl/undotbs01.dbf
input datafile file number=00004 name=/u02/oradata/orcl/orcl/users01.dbf
channel ch01: starting piece 1 at 05-OCT-20
channel ch01: finished piece 1 at 05-OCT-20
piece handle=/u01/software/ch01_orcl02vc7rh2_1_1.rman tag=TAG20201005T194434 comment=NONE
channel ch01: backup set complete, elapsed time: 00:00:25
Finished backup at 05-OCT-20
Starting backup at 05-OCT-20
current log archived
channel ch01: starting compressed archived log backup set
channel ch01: specifying archived log(s) in backup set
input archived log thread=1 sequence=6 RECID=2 STAMP=1053027899
channel ch01: starting piece 1 at 05-OCT-20
channel ch01: finished piece 1 at 05-OCT-20
piece handle=/u01/software/ch01_orcl03vc7rhr_1_1.rman tag=TAG20201005T194459 comment=NONE
channel ch01: backup set complete, elapsed time: 00:00:01
Finished backup at 05-OCT-20
Starting Control File and SPFILE Autobackup at 05-OCT-20
piece handle=/u01/app/oracle/fast_recovery_area/ORCL/autobackup/2020_10_05/o1_mf_s_1053027900_hqqczx1g_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 05-OCT-20
released channel: ch01
5.7. Create a Flashback Guaranteed Restore Point:
Flashback Guaranteed Restoration option will help you to restore your database from any point (if failed or need to roll backup the upgradation processes) to earlier point and it will simple to restore and will save time.
SQL> select flashback_on from v$database; FLASHBACK_ON ------------------ NO SQL> select name,open_mode,log_mode from v$database; NAME OPEN_MODE LOG_MODE --------- -------------------- ------------ ORCL READ WRITE ARCHIVELOG SQL> show parameter compatible NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ compatible string 11.2.0.4.0 SQL> show parameter recovery NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string /u01/app/oracle/fast_recovery_area db_recovery_file_dest_size big integer 4182M recovery_parallelism integer 0 SQL> alter system set db_recovery_file_dest_size=10G scope=both; System altered. SQL> show parameter recovery NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string /u01/app/oracle/fast_recovery_area db_recovery_file_dest_size big integer 10G recovery_parallelism integer 0 SQL> select * from V$restore_point; no rows selected SQL> create restore point pre_upgrade guarantee flashback database; Restore point created. SQL> col name for a20 col GUARANTEE_FLASHBACK_DATABASE for a10 col TIME for a60 set lines 190 SQL> SQL> select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from V$restore_point; NAME GUARANTEE_ TIME -------------------- ---------- ------------------------------------------------------------ PRE_UPGRADE YES 05-OCT-20 07.56.01.000000000 PM
6. Upgrade Database to 12.2 using DBUA:
6.1. Set Environment Variable (Target Oracle Home -> 12.2.0.1) and run DBUA:
[oracle@dbupgrade ~]$ export ORACLE_HOME=/u01/app/oracle/product/12.2.0.1/dbhome_1 [oracle@dbupgrade ~]$ export PATH=$PATH:$ORACLE_HOME/bin [oracle@dbupgrade ~]$ dbua
6.2. DBUA:
6.2.1. Select Database which you want to upgrade to 12.2.0.1. Click Next…
6.2.2. Prerequisite Check: Don’t avoid any error and must fix it before upgradation. Avoid Warning carefully. Click Next if you are ok with Prerequisite Checks. Click Next…
6.2.3. Click Yes to confirmation as you are avoiding warning. Click Next…
6.2.4. Choose options carefully. Click Next…
6.2.5. As you have taken backup, so select Flashback option, it will save your time to rollback if incase upgradation fail any reason. Click Next…
6.2.6. Choose Listener. If you have multiple database on source database and you not upgrading all, then Create new listener for target version. (make sure application use correct port if incase create new one). Click Next..
6.2.7. Click Next… (EM agent can be configured after upgradation)
6.2.8. Summary of upgradation: If happy with settings then Click on Finish otherwise Click back and change.
6.2.9. Upgradation is progressing.
Note::: Make sure no connection disturbance during upgradation.
6.2.10. Upgradation completed successfully. Click Close.
6.3. Upgrade Details:
[oracle@dbupgrade orcl]$ pwd /u01/app/oracle/cfgtoollogs/dbua/upgrade2020-10-05_08-14-14-PM/orcl [oracle@dbupgrade orcl]$ ls -lrt total 223716 drwxr-x---. 3 oracle oinstall 20 Oct 5 20:15 oracle drwxr-x---. 3 oracle oinstall 23 Oct 5 20:15 upgrade -rw-r-----. 1 oracle oinstall 5373 Oct 5 20:18 dbms_registry_basic.sql -rw-r-----. 1 oracle oinstall 12693 Oct 5 20:18 dbms_registry_extended.sql -rw-r-----. 1 oracle oinstall 7027 Oct 5 20:18 preupgrade_driver.sql -rw-r-----. 1 oracle oinstall 390244 Oct 5 20:18 preupgrade_package.sql -rw-r-----. 1 oracle oinstall 60144 Oct 5 20:18 preupgrade_messages.properties -rw-r-----. 1 oracle oinstall 6511 Oct 5 20:18 preupgrade_fixups.sql -rw-r-----. 1 oracle oinstall 6877 Oct 5 20:18 postupgrade_fixups.sql -rw-r-----. 1 oracle oinstall 8497 Oct 5 20:18 upgrade.xml -rw-r-----. 1 oracle oinstall 2882 Oct 5 20:27 PreUpgradeResults.html -rw-r-----. 1 oracle oinstall 33 Oct 5 20:28 GatherDOStat.log -rw-r-----. 1 oracle oinstall 0 Oct 5 20:28 Backup.log -rw-r-----. 1 oracle oinstall 932 Oct 5 20:28 init.ora -rwxr-xr-x. 1 oracle oinstall 314 Oct 5 20:28 createSPFile_orcl.sql -rwxr-xr-x. 1 oracle oinstall 121 Oct 5 20:28 grpOpen_orcl.sql -rwxr-xr-x. 1 oracle oinstall 1768 Oct 5 20:28 orcl_restore.sh -rwxr-xr-x. 1 oracle oinstall 42 Oct 5 20:28 shutdown_orcl.sql -rwxr-xr-x. 1 oracle oinstall 35 Oct 5 20:28 startup_orcl.sql -rw-r-----. 1 oracle oinstall 398 Oct 5 20:28 PreUpgrade.log -rw-r-----. 1 oracle oinstall 69 Oct 5 20:28 Migrate_Sid.log -rw-r-----. 1 oracle oinstall 493 Oct 5 20:28 catupgrd_catcon_4556.lst -rw-r-----. 1 oracle oinstall 0 Oct 5 20:53 catupgrd_datapatch_upgrade.err -rw-r-----. 1 oracle oinstall 626 Oct 5 20:54 catupgrd_datapatch_upgrade.log -rw-r-----. 1 oracle oinstall 0 Oct 5 20:57 catupgrd_datapatch_normal.err -rw-r-----. 1 oracle oinstall 626 Oct 5 20:57 catupgrd_datapatch_normal.log -rw-r-----. 1 oracle oinstall 7726264 Oct 5 20:58 catupgrd1.log -rw-r-----. 1 oracle oinstall 6844746 Oct 5 20:58 catupgrd2.log -rw-r-----. 1 oracle oinstall 7012481 Oct 5 20:58 catupgrd3.log -rw-r-----. 1 oracle oinstall 1563 Oct 5 20:59 upg_summary.log -rw-r-----. 1 oracle oinstall 206756476 Oct 5 20:59 catupgrd0.log -rw-r-----. 1 oracle oinstall 10455 Oct 5 20:59 Oracle_Server.log -rw-r-----. 1 oracle oinstall 716 Oct 5 21:04 Utlprp.log -rw-r-----. 1 oracle oinstall 1537 Oct 5 21:06 UpgradeTimezone.log -rw-r-----. 1 oracle oinstall 301 Oct 5 21:07 PostUpgrade.log -rw-r-----. 1 oracle oinstall 6627 Oct 5 21:07 UpgradeResults.html -rw-r-----. 1 oracle oinstall 108571 Oct 5 21:07 sqls.log
[oracle@dbupgrade orcl]$ pwd /u01/app/oracle/cfgtoollogs/dbua/upgrade2020-10-05_08-14-14-PM/orcl [oracle@dbupgrade orcl]$ cat upg_summary.log Oracle Database 12.2 Post-Upgrade Status Tool 10-05-2020 20:58:30 Component Current Version Elapsed Time Name Status Number HH:MM:SS Oracle Server UPGRADED 12.2.0.1.0 00:07:26 JServer JAVA Virtual Machine UPGRADED 12.2.0.1.0 00:02:36 Oracle Workspace Manager UPGRADED 12.2.0.1.0 00:00:37 OLAP Analytic Workspace UPGRADED 12.2.0.1.0 00:00:09 OLAP Catalog OPTION OFF 11.2.0.4.0 00:00:00 Oracle OLAP API UPGRADED 12.2.0.1.0 00:00:10 Oracle XDK UPGRADED 12.2.0.1.0 00:00:23 Oracle Text UPGRADED 12.2.0.1.0 00:00:27 Oracle XML Database UPGRADED 12.2.0.1.0 00:01:25 Oracle Database Java Packages UPGRADED 12.2.0.1.0 00:00:08 Oracle Multimedia UPGRADED 12.2.0.1.0 00:01:09 Spatial UPGRADED 12.2.0.1.0 00:03:32 Oracle Application Express UPGRADED 5.0.4.00.12 00:06:50 Final Actions 00:02:43 Post Upgrade 00:01:12 Total Upgrade Time: 00:29:01 Database time zone version is 14. It is older than current release time zone version 26. Time zone upgrade is needed using the DBMS_DST package. Grand Total Upgrade Time: [0d:0h:30m:20s]
6.4. Time Zone:
SQL> select TZ_VERSION from registry$database;
TZ_VERSION
----------
14
SQL> COL VALUE FOR A20
SQL> COL PROPERTY_NAME FOR A50
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'; 2 3
PROPERTY_NAME VALUE
-------------------------------------------------- --------------------
DST_SECONDARY_TT_VERSION 0
DST_PRIMARY_TT_VERSION 26
DST_UPGRADE_STATE NONE
6.5. Verify Database Registry::
SQL> select name,open_mode,cdb,version,status from v$database,v$instance; NAME OPEN_MODE CDB VERSION STATUS --------- -------------------- --- ----------------- ------------ ORCL READ WRITE NO 12.2.0.1.0 OPEN SQL> COL COMP_ID FOR A20 SQL> COL COMP_NAME FOR A45 SQL> COL VERSION FOR A20 SQL> SET LINESIZE 180 SQL> SELECT COMP_ID,COMP_NAME,VERSION,STATUS FROM DBA_REGISTRY; COMP_ID COMP_NAME VERSION STATUS -------------------- --------------------------------------------- -------------------- ----------- CATALOG Oracle Database Catalog Views 12.2.0.1.0 VALID CATPROC Oracle Database Packages and Types 12.2.0.1.0 VALID JAVAVM JServer JAVA Virtual Machine 12.2.0.1.0 VALID XML Oracle XDK 12.2.0.1.0 VALID CATJAVA Oracle Database Java Packages 12.2.0.1.0 VALID APS OLAP Analytic Workspace 12.2.0.1.0 VALID OWM Oracle Workspace Manager 12.2.0.1.0 VALID CONTEXT Oracle Text 12.2.0.1.0 VALID XDB Oracle XML Database 12.2.0.1.0 VALID ORDIM Oracle Multimedia 12.2.0.1.0 VALID SDO Spatial 12.2.0.1.0 VALID XOQ Oracle OLAP API 12.2.0.1.0 VALID AMD OLAP Catalog 11.2.0.4.0 OPTION OFF APEX Oracle Application Express 5.0.4.00.12 VALID 14 rows selected.
6.6. Invalid Object List:
SQL> select count(*) from dba_objects where status='INVALID';
COUNT(*)
----------
0
6.7. Database Backup:
[oracle@dbupgrade dbhome_1]$ rman target /
Recovery Manager: Release 12.2.0.1.0 - Production on Mon Oct 5 23:38:22 2020
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1580708519)
RMAN> run
{
allocate channel ch01 type disk format '/u01/software/ch01_orcl_after_update%U.rman' ;
BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG;
release channel ch01;
}
using target database control file instead of recovery catalog
allocated channel: ch01
channel ch01: SID=55 device type=DISK
Starting backup at 05-OCT-20
current log archived
Starting backup at 05-OCT-20
current log archived
channel ch01: starting compressed archived log backup set
channel ch01: specifying archived log(s) in backup set
input archived log thread=1 sequence=5 RECID=1 STAMP=1053027873
... ... ...
input archived log thread=1 sequence=124 RECID=120 STAMP=1053036043
input archived log thread=1 sequence=125 RECID=121 STAMP=1053041661
input archived log thread=1 sequence=126 RECID=122 STAMP=1053041906
channel ch01: starting piece 1 at 05-OCT-20
channel ch01: finished piece 1 at 05-OCT-20
piece handle=/u01/software/ch01_orcl_after_update06vc899t_1_1.rman tag=TAG20201005T233826 comment=NONE
channel ch01: backup set complete, elapsed time: 00:01:05
Finished backup at 05-OCT-20
Starting backup at 05-OCT-20
channel ch01: starting compressed full datafile backup set
channel ch01: specifying datafile(s) in backup set
input datafile file number=00003 name=/u02/oradata/orcl/orcl/undotbs01.dbf
input datafile file number=00001 name=/u02/oradata/orcl/orcl/system01.dbf
input datafile file number=00002 name=/u02/oradata/orcl/orcl/sysaux01.dbf
input datafile file number=00004 name=/u02/oradata/orcl/orcl/users01.dbf
channel ch01: starting piece 1 at 05-OCT-20
channel ch01: finished piece 1 at 05-OCT-20
piece handle=/u01/software/ch01_orcl_after_update07vc89bu_1_1.rman tag=TAG20201005T234046 comment=NONE
channel ch01: backup set complete, elapsed time: 00:01:05
Finished backup at 05-OCT-20
Starting backup at 05-OCT-20
current log archived
channel ch01: starting compressed archived log backup set
channel ch01: specifying archived log(s) in backup set
input archived log thread=1 sequence=127 RECID=123 STAMP=1053042111
channel ch01: starting piece 1 at 05-OCT-20
channel ch01: finished piece 1 at 05-OCT-20
piece handle=/u01/software/ch01_orcl_after_update08vc89e0_1_1.rman tag=TAG20201005T234151 comment=NONE
channel ch01: backup set complete, elapsed time: 00:00:01
Finished backup at 05-OCT-20
Starting Control File and SPFILE Autobackup at 05-OCT-20
piece handle=/u01/app/oracle/fast_recovery_area/ORCL/autobackup/2020_10_05/o1_mf_s_1053042113_hqqsw18p_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 05-OCT-20
released channel: ch01
6.8. Check COMPATIBLE parametr values and change to 12.2:
If there is no chance to rollback, then change COMPATIBLE parameter to 12.2.
SQL> show parameter COMPATIBLE NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ compatible string 11.2.0.4.0 SQL> ALTER SYSTEM SET COMPATIBLE = '12.2.0' SCOPE=SPFILE; System altered. SQL> SHUT IMMEDIATE; Database closed. Database dismounted. ORACLE instance shut down. SQL> STARTUP ORACLE instance started. Total System Global Area 1560277408 bytes Fixed Size 8896928 bytes Variable Size 1275068416 bytes Database Buffers 268435456 bytes Redo Buffers 7876608 bytes Database mounted. Database opened. SQL> show parameter COMPATIBLE NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ compatible string 12.2.0 noncdb_compatible boolean FALSE
7. Rollback to Earlier Version (11.2.0.4):
If you get any major issue after upgradation and need to rollback (Not recommended to rollback but no other option) then follow the following steps.
7.1. Database Status on 12.2.0.1:
SQL> select name,open_mode,cdb,version,status from v$database,v$instance; NAME OPEN_MODE CDB VERSION STATUS --------- -------------------- --- ----------------- ------------ ORCL READ WRITE NO 12.2.0.1.0 OPEN
7.2. Flashback Status on 12.2.0.1:
SQL> col name for a20 col GUARANTEE_FLASHBACK_DATABASE for a10 col TIME for a60 set lines 190 SQL> SQL> SQL> select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from V$restore_point; NAME GUARANTEE_ TIME -------------------- ---------- ------------------------------------------------------------ PRE_UPGRADE YES 05-OCT-20 07.56.01.000000000 PM
7.3. Restore Database using Guarantee Flashback:
It will take couple of minutes to complete.
SQL> shu immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> SQL> startup mount; ORACLE instance started. Total System Global Area 2415919104 bytes Fixed Size 8623544 bytes Variable Size 654314056 bytes Database Buffers 1744830464 bytes Redo Buffers 8151040 bytes Database mounted. SQL> SQL> FLASHBACK DATABASE TO RESTORE POINT PRE_UPGRADE; Flashback complete. SQL> shu immediate; ORA-01109: database not open Database dismounted. ORACLE instance shut down.
7.4. Start Database from older version(11g):
[oracle@dbupgrade ~]$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 [oracle@dbupgrade ~]$ export ORACLE_SID=orcl [oracle@dbupgrade ~]$ export PATH=$PATH:$ORACLE_HOME/bin [oracle@dbupgrade ~]$ echo $PATH /usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/home/oracle/.local/bin:/home/oracle/bin:/u01/app/oracle/product/11.2.0/dbhome_1/bin [oracle@dbupgrade ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Mon Oct 5 23:47:13 2020 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 2405122048 bytes Fixed Size 2255592 bytes Variable Size 603981080 bytes Database Buffers 1795162112 bytes Redo Buffers 3723264 bytes Database mounted. ORA-01589: must use RESETLOGS or NORESETLOGS option for database open SQL> alter database open RESETLOGS; Database altered. SQL> select name,open_mode,version,status from v$database,v$instance; NAME OPEN_MODE VERSION STATUS --------- -------------------- ----------------- ------------ ORCL READ WRITE 11.2.0.4.0 OPEN
7.5. Invalid Object List:
SQL> select count(*) from dba_objects where status='INVALID';
COUNT(*)
----------
0
7.6. COMPATIBLE Values:
SQL> show parameter COMPATIBLE NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ compatible string 11.2.0.4.0
7.7. Time Zone Status:
SQL> COL VALUE FOR A20 SQL> COL PROPERTY_NAME FOR A50 SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%'; 2 3 PROPERTY_NAME VALUE -------------------------------------------------- -------------------- DST_UPGRADE_STATE NONE DST_PRIMARY_TT_VERSION 14 DST_SECONDARY_TT_VERSION 0
7.8. Drop Flashback Restore Point:
SQL> select flashback_on from v$database; FLASHBACK_ON ------------------ RESTORE POINT ONLY SQL> select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from V$restore_point; NAME -------------------------------------------------------------------------------- GUA TIME --- --------------------------------------------------------------------------- PRE_UPGRADE YES 05-OCT-20 07.56.01.000000000 PM SQL> col name for a20 SQL> col GUARANTEE_FLASHBACK_DATABASE for a10 SQL> col TIME for a60 SQL> set lines 190 SQL> select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from V$restore_point; NAME GUARANTEE_ TIME -------------------- ---------- ------------------------------------------------------------ PRE_UPGRADE YES 05-OCT-20 07.56.01.000000000 PM SQL> drop restore point PRE_UPGRADE; Restore point dropped. SQL> select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from V$restore_point; no rows selected
Script to Collect DB Upgrade/Migrate Diagnostic Information (dbupgdiag.sql) (Doc ID 556610.1)
