Oracle Upgrade 11.2.0.3 to 12.2.0.1 Step by Step
Oracle
Upgrade 11.2.0.3 to 12.2.0.1
Upgrade
Grid Infrastructure 11.2.0.3 to 12.2.0.1
Upgrading
to Oracle Database 12c
PURPOSE: All
documents are provided on this Blog just for educational purposes only. Please make sure that you run it in your test
environment before to move on to production environment.
==============
STEP 1:
Upgrade Grid
==============
Those Files are required:
linuxx64_12201_grid_home.zip
linuxx64_12201_database.zip
p6880880_122010_Linux-x86-64.zip
p28689130_122010_Linux-x86-64.zip
=>> 28689130 COMBO OF OJVM RU
COMPONENT 12.2.0.1.181016 + 12.2.0.1.181016 GIOCT2018RU
bug_patch
=>>p21255373_112039_Linux-x86-64.zip
p25183818_12201190716OCWJUL2019RU_Linux-x86-64.zip =>>
DBBug_25183818 =>>
p25415713_12201190115DBJAN2019RU_Linux-x86-64.zip
=>>The mmon trace is reported with the following error: AUTO SGA:
kmgs_parameter_update_timeout gen0 0 mmon alive 1
Swap space required 16GB, but the Installation
will work with 8GB. You can skip the warning.
Keep following
detail we need later this Information.
srvctl config asm
ASM home: /oracle/product/11.2.0.3/grid
ASM listener: LISTENER
Spfile:
+DATA/asm/asmparameterfile/registry.256.1024325177
ASM diskgroup discovery string: /dev/*
Login as ASM
sqlplus / as sysasm
col COMPATIBILITY form a10
col DATABASE_COMPATIBILITY form a10
col NAME form a20
select group_number,
name, compatibility, database_compatibility from v$asm_diskgroup;
GROUP_NUMBER
NAME COMPATIBIL
DATABASE_C
------------
-------------------- ---------- ----------
1 DATA 11.2.0.0.0 10.1.0.0.0
If the
compatibility is 11.2.0.0.0 then change to 11.2.0.2.0
asmcmd setattr -G DATA compatible.asm 11.2.0.2.0
select group_number,
name, compatibility, database_compatibility from v$asm_diskgroup;
GROUP_NUMBER
NAME COMPATIBIL
DATABASE_C
------------
-------------------- ---------- ----------
1 DATA 11.2.0.2.0 10.1.0.0.0
#ASM spfile looks like as follow:
*.asm_diskstring='/dev/*'
*.asm_power_limit=1
*.diagnostic_dest='/oracle'
*.instance_type='asm'
*.large_pool_size=12M
*.remote_login_passwordfile='EXCLUSIVE'
Commands you
need throw the upgrade process. Just keep on a side.
Check the version:
crsctl query has
releaseversion
Oracle High
Availability Services release version on the local node is [11.2.0.3.0]
crsctl query has
softwareversion
crsctl query has
releasepatch
crsctl query has
softwarepatch
unset ORACLE_BASE
unset ORACLE_HOME
unset ORACLE_SID
srvctl stop database
-d DBNAME
srvctl start database
-d DBNAME
srvctl status listener -l LISTENER
srvctl stop listener
-l LISTENER
srvctl start listener
-l LISTENER
or
lsnrctl stop LISTENER
lsnrctl start
LISTENER
srvctl status asm
srvctl stop asm -f
srvctl start asm
srvctl stop database
-d KARDBPRD
crsctl stop res -all
crsctl stop has
crsctl stat res -t
crsctl start has
srvctl add database
-db KARDBPRD -oraclehome /oracle/product/12.2.0.1/db_12c
As oracle
user:
New Grid_Home location for 12.2 vaersion.
cd $ORACLE_BASE/product/12.2.0.1/grid_12c
unzip /download/linuxx64_12201_grid_home.zip
unzip /download/p6880880_122010_Linux-x86-64.zip
DISPLAY=xxx.xx.xxx.xxx:0.0; export DISPLAY
unset ORACLE_BASE
unset ORACLE_HOME
unset ORACLE_SID
cd $GRID_HOME
./gridSetup.sh
-applyPSU /download/28689130/28714316
Chose the Upgrade option.
I got following error Error:
PRVG-1261 : Required Oracle patch
"21255373" in home
NOTE:
We need to install the patch on 11g home before we processed with the upgrade.
Let us apply
the following patch on both 11g Oracle_Home and for 11g Grid_Home
p21255373_112039_Linux-x86-64.zip
Create
ocm.rsp file, if it does not exist:
cd $ORACLE_BASE/product/11.2.0.3/grid/OPatch/ocm/bin/
./emocmrsp
Do you wish to remain uninformed of security issues
([Y]es, [N]o) [N]: Y
The OCM configuration response file (ocm.rsp) was
successfully created.
Create response file for DB_Home
cd $ORACLE_BASE/product/11.2.0.3/db/OPatch/ocm/bin
Do you wish to remain uninformed of security issues
([Y]es, [N]o) [N]: Y
The OCM configuration response file (ocm.rsp) was
successfully created.
./emocmrsp
As root
user:
export
PATH=$PATH:/oracle/product/11.2.0.3/grid/OPatch
/oracle/product/11.2.0.3/grid/OPatch/opatch
auto /download/bug_patch -ocmrf
/oracle/product/11.2.0.3/grid/OPatch/ocm/bin/ocm.rsp
...
...
opatch auto
succeeded.
Patch
Installation is completed. Let us give a try again.
Stop DB and ASM and try again to update Grid.
./gridSetup.sh -applyPSU /download/28689130/28714316
Execute as
root user:
/oracle/product/12.2.0.1/grid_12c/rootupgrade.sh
ERROR:
Getting this error:
Upgrade cannot proceed because the ASM instance failed
to start. Fix the issue or startup the ASM instance, verify and try again.
ORA-01078: failure in processing system
parameters
ORA-29701: unable to connect to Cluster
Synchronization Service
2019/11/19 13:13:22 CLSRSC-164: ASM
upgrade failed
2019/11/19 13:13:23 CLSRSC-304: Failed
to upgrade ASM for Oracle Restart configuration
I started ASM
manually and executed the script rootupgrade.sh again, but this time I got another error.
PRCA-1003 : Failed to create ASM asm
resource ora.asm
PRCR-1071 : Failed to register or update
resource ora.asm
CRS-2415: Resource 'ora.asm' cannot be
registered because its owner 'root' is not the same as the Oracle Restart user
'oracle'.
This is due
to Bug 25183818
The Patch
needs to be applied to 12.2 Home.
(Doc ID
2246888.1)
Let's fix the Issue.
NOTE:
We need to install
this Patch p25183818_12201190716OCWJUL2019RU_Linux-x86-64.zip on new 12c
Grid_Home
As root user
export
PATH=$PATH:/oracle/product/12.2.0.1/grid_12c/OPatch
/oracle/product/12.2.0.1/grid_12c/OPatch/opatchauto
apply /download/Bug_25183818/25183818 -oh /oracle/product/12.2.0.1/grid_12c
You could
see such type of error at the end. But this is fine, because the "Binary patch applied
successfully"
Start applying binary patch on home
/oracle/product/12.2.0.1/grid_12c
Binary patch
applied successfully on home /oracle/product/12.2.0.1/grid_12c
Starting CRS service on home
/oracle/product/12.2.0.1/grid_12c
Failed to start CRS service on home
/oracle/product/12.2.0.1/grid_12c
...
...
OPatchAuto failed.
opatchauto failed with error code 42
Let’s give a
try again and start the rootupgrade.sh again.
/oracle/product/12.2.0.1/grid_12c/rootupgrade.sh
The script went successfully
...
...
CRS-4123: Oracle High Availability Services has been
started.
2019/11/19 14:49:00 CLSRSC-327: Successfully
configured Oracle Restart for a standalone server
Edit the new
home in the oratab:
/etc/oratab
+ASM:/oracle/product/12.2.0.1/grid_12c:N
When I try
to start ASM. I am getting this error.
srvctl start asm
PRCR-1001 : Resource ora.asm does not
exist
Those steps are the most Important Steps!
srvctl add asm
srvctl config asm
ASM home: <CRS home>
Password file:
Backup of Password file:
ASM listener: LISTENER
Spfile:
ASM diskgroup discovery string:
++no-value-at-resource-creation--never-updated-through-ASM++
The above
command replies empty configuration. Let us add the config which we keep in the
early.
srvctl modify asm
-listener LISTENER -spfile +DATA/asm/asmparameterfile/registry.256.1024325177
-diskstring '/dev/*'
Restart the
service:
crsctl stop has
crsctl start has
check the DBA and ASM
ps -ef | grep -i asm
ps -ef | grep -i
oracle
Everything is up and running.
At this
point DB is still running SQL*Plus: Release 11.2.0.3.0. You can login into DB and in ASM.
Everything
works.
sqlplus / as sysasm
SQL*Plus: Release
12.2.0.1.0 Production on Tue Nov 19 15:36:24 2019
Copyright (c) 1982,
2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c
Enterprise Edition Release 12.2.0.1.0 - 64bit Production
NOTE:
1. Adjust the Listener parameter.
2. Chnage the ASM Diskgroup compatibility to 12
asmcmd setattr -G DATA compatible.asm 12.2.0.0.0
OR
ALTER DISKGROUP DATA SET ATTRIBUTE 'compatible.asm' =
'12.2.0'
3) If someone use Oracle client 11g. You need add the following
entry in
sqlnet.ora file
SQLNET.ALLOWED_LOGON_VERSION_SERVER=11
SQLNET.ALLOWED_LOGON_VERSION_CLIENT=11
4. If you have following issues in your environment
then you need to Install FIX for the issue.
Patch 27986817
Slow extent allocation for partitioned compressed
table causing slowdown with enq: tx - contention and L1 validation wait events
Patch 25415713
The mmon trace is reported with the following error:
AUTO SGA: kmgs_parameter_update_timeout gen0 0 mmon alive 1
At this point,
we are done with the GRID_HOME upgrade.
=========================
STEP 2:
Upgrade DB
=========================
You need to
download following file:
linuxx64_12201_database.zip
unzip linuxx64_12201_database.zip
1. Login as
11g and create a pfile.
create pfile='/tmp/initKARDBPRD.ora' from spfile;
2. Check the
Invalid Objects (ALL SHOULD BE VALID)
select comp_id,status from dba_registry;
3. Check
duplicate objects owned by system and sys
col OBJECT_NAME format a30
select object_name, object_type from dba_objects where
object_name||object_type in
(select
object_name||object_type
from
dba_objects
where owner
= 'SYS')
and owner = 'SYSTEM';
OBJECT_NAME OBJECT_TYPE
------------------------------ -------------------
AQ$_SCHEDULES TABLE
AQ$_SCHEDULES_PRIMARY INDEX
DBMS_REPCAT_AUTH PACKAGE
DBMS_REPCAT_AUTH PACKAGE BODY
NOTE: If
you found any other objects other than these four, then those need to clean up.
4. Install
Just Oracle 12c Software. I am going to use the silent Installation approach.
./runInstaller
-silent -debug -ignorePreReq -force \
FROM_LOCATION=/downloads/Oracle_12c/database/stage/products.xml
\
oracle.install.option=INSTALL_DB_SWONLY
\
UNIX_GROUP_NAME=dba \
INVENTORY_LOCATION=/apps/oraInventory
\
ORACLE_HOME=/apps/oracle/product/12.2.0.1/db_12c
\
ORACLE_BASE=/apps/oracle
\
DECLINE_SECURITY_UPDATES=true
\
oracle.install.db.InstallEdition=EE
\
oracle.install.db.isCustomInstall=true
\
oracle.install.db.DBA_GROUP=dba
\
oracle.install.db.OPER_GROUP=dba
\
oracle.install.db.BACKUPDBA_GROUP=dba
\
oracle.install.db.DGDBA_GROUP=dba
\
oracle.install.db.KMDBA_GROUP=dba
\
DECLINE_SECURITY_UPDATES=true
Or
You can use
the GUI for Installation.
cd / downloads/Oracle_12c/database
export DISPLAY=xx.xxx.xx.xxx:0.0
unset ORACLE_BASE
unset ORACLE_HOME
unset ORACLE_SID
./runInstaller
Chose following options:
Install database
software only
Single Instance
database Installation
Enterprise Edition
Oracle Base ( this is
the directory for all oracle products )
Software location
I chose the dba for
all groups
execute the script when the Installer asked.
/oracle/product/12.2.0.1/db_12c/root.sh
chose no:
Do you want to setup
Oracle Trace File Analyzer (TFA) now ? yes|[no] :
no
Oracle Software
Installation is completed.
[Execute as follow: $ORACLE_HOME11g/jdk/bin/java -jar
$ORACLE_HOME12c/rdbms/admin/preupgrade.jar FILE TEXT ]
$ORACLE_HOME/jdk/bin/java
-jar /apps/oracle/product/12.2.0.1/db_12c/rdbms/admin/preupgrade.jar FILE TEXT
Preupgrade generated files:
/apps/oracle/cfgtoollogs/KARDBPRD/preupgrade/preupgrade.log
/apps/oracle/cfgtoollogs/KARDBPRD/preupgrade/preupgrade_fixups.sql /apps/oracle/cfgtoollogs/KARDBPRD/preupgrade/postupgrade_fixups.sql
ACTIONS REQUIRED:
1. Review results of the pre-upgrade checks:
/apps/oracle/cfgtoollogs/KARDBPRD/preupgrade/preupgrade.log
2. Execute in the SOURCE environment BEFORE upgrade:
/apps/oracle/cfgtoollogs/KARDBPRD/preupgrade/preupgrade_fixups.sql
3. Execute in the NEW environment AFTER upgrade:
/apps/oracle/cfgtoollogs/KARDBPRD/preupgrade/postupgrade_fixups.sql
Login as 11g
and execute preupgrd.sql script
SQL>@/apps/oracle/cfgtoollogs/KARDBPRD/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: 2019-11-21 11:33:40
For Source Database: KARDBPRD
Source Database Version: 11.2.0.3.0
For Upgrade to Version: 12.2.0.1.0
Fixup
Check Name Status Further DBA Action
---------- ------ ------------------
invalid_objects_exist 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.
Fix the
above Issues and continue with the update.
Gather dictionary statistics 24 hours prior to
upgrading the database.
EXECUTE dbms_stats.gather_dictionary_stats;
PURGE DBA_RECYCLEBIN;
Check the following parameters values:
SQL> show
parameters PROCESSES;
-- meet estimated
minimums Values
--
Parameter=processes=300
alter system set processes=300 scope=spfile;
set lines 200;
SET ECHO ON;
SET SERVEROUTPUT ON;
EXECUTE
DBMS_PREUP.INVALID_OBJECTS;
NOTE:
If you use APEX. You have update version grater then 5.0.4.
This is requirement for Oracle 12.2.0.1
5. Modify
the file as required, which we created earlier “/tmp/initKARDBPRD.ora”
NOTE:
Keep the compatible parameter as it
*.compatible='11.2.0' It allow you to downgrade later in any case. You update
the parameter later sometime. It required DB restart.
SELECT name, value, description FROM v$parameter WHERE
name = 'compatible';
Restart the process, just to make sure everything is
clean and works
crsctl stop has
crsctl start has
Shutdown 11g
DB:
shutdown immediate;
lsnrctl stop LISTENER
Edit the "/etc/oratab" file, setting the new
ORACLE_HOME value.
6. Login as
12c home:
create spfile from pfile='/tmp/initKARDBPRD.ora
';
If you want
to Install via Installer GUI do following and follow the steps.
cd /oracle/product/12.2.0.1/db_12c/bin
./dbua
7. I am
going to use the manual steps.
cd
$ORACLE_HOME/rdbms/admin
SQL> startup
upgrade;
ORACLE instance
started.
Total System Global
Area 3.0266E+10 bytes
Fixed Size 12177264 bytes
Variable Size 3690987664 bytes
Database Buffers 2.6508E+10 bytes
Redo Buffers 54931456 bytes
Database mounted.
Database opened.
SQL> exit
8. You can
use Regular upgrade command or Shorthand command ($ORACLE_HOME/bin/dbupgrade). I am going to use Regular command.
cd
$ORACLE_HOME/rdbms/admin
$ORACLE_HOME/perl/bin/perl
catctl.pl -n 8 catupgrd.sql
...
...
Serial Phase
#:115 [KARDBPRD] Files:1 Time: 23s
------------------------------------------------------
Phases [0-115]
End Time:[2019_11_21 16:01:58]
------------------------------------------------------
Grand Total Time: 870s
LOG FILES:
(/oracle/product/12.2.0.1/db_12c/cfgtoollogs/KARDBPRD/upgrade20191121154728/catupgrd*.log)
Upgrade Summary Report Located in:
/oracle/product/12.2.0.1/db_12c/cfgtoollogs/KARDBPRD/upgrade20191121154728/upg_summary.log
Grand Total Upgrade Time: [0d:0h:14m:30s]
...
...
9. Execute
the postupgrade script.
SQL> EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
SQL>
@/oracle/cfgtoollogs/KARDBPRD/preupgrade/postupgrade_fixups.sql
col COMP_NAME format
a50;
set linesize 300
Check the
components version.
set pagesize 100
select COMP_NAME,
version, status from dba_registry;
COMP_NAME
VERSION
STATUS
--------------------------------------------------
------------------------------
Oracle Database
Catalog Views
12.2.0.1.0
UPGRADED
Oracle Database
Packages and Types
12.2.0.1.0
UPGRADED
JServer JAVA Virtual
Machine
12.2.0.1.0 UPGRADED
Oracle XDK
12.2.0.1.0
UPGRADED
Oracle Database Java
Packages
12.2.0.1.0
UPGRADED
Oracle Workspace
Manager 12.2.0.1.0 UPGRADED
Oracle XML
Database
12.2.0.1.0
UPGRADED
Oracle
Multimedia
12.2.0.1.0
UPGRADED
Oracle Application
Express 5.1.4.00.08 VALID
9 rows selected.
10. You need
to add DB location, wenn you upgrade DB manuelly.
PRCD-1229 : An attempt to access configuration of
database KARDBPRD was rejected
srvctl upgrade
database -d KARDBPRD -o /apps/oracle/product/12.2.0.1/db_12c
NOTE: You can
Install new patch on both GRID_HOME and ORACLE_HOME. How to Install the patch please a have look following document.
DONE:
