Please check this post Database upgrade from 10.2.0.5.0 to 11.2.0.4
Category Archives: Upgrade
Database upgrade from 10.2.0.5.0 to 11.2.0.4
The following document is the best one you can start documenting yourself from:
11gr2-upgrade-bp-apr2012-1610082.pdf
From the above document, I followed:
- Note 251.1: Database Upgrades from 10.2 to 11.2
On that note, on the PLAN tab, I followed:
Complete Checklist for Manual Upgrades to 11gR2 (Doc ID 837570.1)
In this document you’ll find a bunch of steps to be followed. The most important one is the pre-upgrade one, which you have to run it on the SOURCE database. This script is meant to display back to you a lot of useful information in respect to what needs to be changed/adapted on the source (prior upgrade) or on the target (after the upgrade). The script can be found here:
$ORACLE_HOME/rdbms/admin/utlu112i.sql
If you run the upgrade on the same server as the source database is on, it’s better if you copy this file in a different place like /tmp or HOME folder.
In my case, between other advices, I got the following warnings:
********************************************************************** Miscellaneous Warnings ********************************************************************** WARNING: --> Database is using a timezone file older than version 14. .... After the release migration, it is recommended that DBMS_DST package .... be used to upgrade the 10.2.0.5.0 database timezone version .... to the latest version which comes with the new release. WARNING: --> Your recycle bin contains 4 object(s). .... It is REQUIRED that the recycle bin is empty prior to upgrading .... your database. The command: PURGE DBA_RECYCLEBIN .... must be executed immediately prior to executing your upgrade. WARNING: --> JOB_QUEUE_PROCESS value must be updated .... Your current setting of "20" is too low. .... Starting with Oracle Database 11g Release 2 (11.2), setting .... JOB_QUEUE_PROCESSES to 0 causes both DBMS_SCHEDULER and .... DBMS_JOB jobs to not run. Previously, setting JOB_QUEUE_PROCESSES .... to 0 caused DBMS_JOB jobs to not run, but DBMS_SCHEDULER jobs were .... unaffected and would still run. This parameter must be updated to .... a value greater than 48 (default value is 1000) prior to upgrade. .... Not doing so will affect the running of utlrp.sql after the upgrade . **********************************************************************
As you can see, the first advice should be executed on the TARGET database whereas the last 2 on the SOURCE database.
SQL> EXECUTE dbms_stats.gather_dictionary_stats; PL/SQL procedure successfully completed. SQL> alter system set job_queue_processes=100 scope=both; System altered. SQL> PURGE DBA_RECYCLEBIN; DBA Recyclebin purged.
Now I just run the usual scripts in order to upgrade to 11204 version:
cd $ORACLE_HOME/rdbms/admin sqlplus /nolog conn / as sysdba STARTUP UPGRADE SPOOL upgrade11204_DB.log @catupgrd.sql -- perform upgrade actions that do not require the database to be in UPGRADE mode @catuppst.sql @$ORACLE_HOME/ctx/admin/ctxf102.sql @utlrp.sql -- Check for invalid entries: col comp_name format a80 set linesize 200 set pagesize 2000 SELECT COMP_NAME,VERSION,STATUS FROM DBA_REGISTRY; -- Check for invalid objects: SELECT count(*) FROM dba_invalid_objects; select owner, object_type, object_name from dba_invalid_objects; ### CPU Patch (if that's the case) @catbundle cpu apply @utlrp
In the end the, upgrade was successful. However, I still need to fix the timezone issue which appeared in the upgrade check script in the very first step:
SQL> SELECT version FROM v$timezone_file; VERSION ---------- 4 SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME; PROPERTY_NAME ------------------------------ VALUE -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- DST_PRIMARY_TT_VERSION 4 DST_SECONDARY_TT_VERSION 0 DST_UPGRADE_STATE NONE SQL> !ls -ltr $ORACLE_HOME/oracore/zoneinfo/time*14* -rw-r--r-- 1 oracle oinstall 344448 Jul 16 2013 /u01/app/oracle/product/11.2.0.4/oracore/zoneinfo/timezone_14.dat -rw-r--r-- 1 oracle oinstall 791430 Jul 16 2013 /u01/app/oracle/product/11.2.0.4/oracore/zoneinfo/timezlrg_14.dat SQL> exec DBMS_DST.BEGIN_PREPARE(14); A prepare window has been successfully started. PL/SQL procedure successfully completed. SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME; PROPERTY_NAME ------------------------------ VALUE -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- DST_PRIMARY_TT_VERSION 4 DST_SECONDARY_TT_VERSION 14 DST_UPGRADE_STATE PREPARE SQL> TRUNCATE TABLE SYS.DST$TRIGGER_TABLE; Table truncated. SQL> TRUNCATE TABLE sys.dst$affected_tables; Table truncated. SQL> TRUNCATE TABLE sys.dst$error_table; Table truncated. SQL> BEGIN DBMS_DST.FIND_AFFECTED_TABLES (affected_tables => 'sys.dst$affected_tables', log_errors => TRUE, log_errors_table => 'sys.dst$error_table'); END; / PL/SQL procedure successfully completed. SQL> SQL> SELECT * FROM sys.dst$affected_tables; no rows selected SQL> EXEC DBMS_DST.END_PREPARE; A prepare window has been successfully ended. PL/SQL procedure successfully completed. SQL> shut immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup upgrade ORACLE instance started. Total System Global Area 4275781632 bytes Fixed Size 2260088 bytes Variable Size 1090519944 bytes Database Buffers 3170893824 bytes Redo Buffers 12107776 bytes Database mounted. Database opened. SQL> spool DST_upgrade.log SQL> EXEC DBMS_DST.BEGIN_UPGRADE(14); PL/SQL procedure successfully completed. SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME; PROPERTY_NAME ------------------------------ VALUE -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- DST_PRIMARY_TT_VERSION 14 DST_SECONDARY_TT_VERSION 4 DST_UPGRADE_STATE UPGRADE SQL> shut immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 4275781632 bytes Fixed Size 2260088 bytes Variable Size 1090519944 bytes Database Buffers 3170893824 bytes Redo Buffers 12107776 bytes Database mounted. Database opened. SQL> SELECT version FROM v$timezone_file; VERSION ---------- 14
Time to upgrade the OPatch
As the title suggest, it’s time to upgrade the OPatch. This is what I got when I wanted to apply a patch (namely 20760982) to a 11.2.0.4 database (linux):
[oracle@server 20760982]$ $ORACLE_HOME/OPatch/opatch apply Oracle Interim Patch Installer version 11.2.0.3.4 Copyright (c) 2012, Oracle Corporation. All rights reserved. Oracle Home : /u01/app/oracle/product/11.2.0.4 Central Inventory : /u01/app/oracle/oraInv from : /u01/app/oracle/product/11.2.0.4/oraInst.loc OPatch version : 11.2.0.3.4 OUI version : 11.2.0.4.0 Log file location : /u01/app/oracle/product/11.2.0.4/cfgtoollogs/opatch/opatch2017-01-09_08-16-47AM_1.log Verifying environment and performing prerequisite checks... Prerequisite check "CheckMinimumOPatchVersion" failed. The details are: The OPatch being used has version 11.2.0.3.4 while the following patch(es) require higher versions: Patch 17478514 requires OPatch version 11.2.0.3.5. Patch 18031668 requires OPatch version 11.2.0.3.5. Patch 18522509 requires OPatch version 11.2.0.3.5. Patch 19121551 requires OPatch version 11.2.0.3.5. Patch 19769489 requires OPatch version 11.2.0.3.5. Patch 20299013 requires OPatch version 11.2.0.3.5. Patch 20760982 requires OPatch version 11.2.0.3.5. Please download latest OPatch from My Oracle Support. UtilSession failed: Prerequisite check "CheckMinimumOPatchVersion" failed. Log file location: /u01/app/oracle/product/11.2.0.4/cfgtoollogs/opatch/opatch2017-01-09_08-16-47AM_1.log
And this is what I have on my server:
[oracle@server 11204]$ /u01/app/oracle/product/11.2.0.4/OPatch/opatch lsinventory
Oracle Interim Patch Installer version 11.2.0.3.4
Copyright (c) 2012, Oracle Corporation. All rights reserved.
Oracle Home : /u01/app/oracle/product/11.2.0.4
Central Inventory : /u01/app/oracle/oraInv
from : /u01/app/oracle/product/11.2.0.4/oraInst.loc
OPatch version : 11.2.0.3.4
OUI version : 11.2.0.4.0
Log file location : /u01/app/oracle/product/11.2.0.4/cfgtoollogs/opatch/opatch2017-01-09_08-47-49AM_1.log
Lsinventory Output file location : /u01/app/oracle/product/11.2.0.4/cfgtoollogs/opatch/lsinv/lsinventory2017-01-09_08-47-49AM.txt
--------------------------------------------------------------------------------
Installed Top-level Products (1):
Oracle Database 11g 11.2.0.4.0
There are 1 products installed in this Oracle Home.
There are no Interim patches installed in this Oracle Home.
--------------------------------------------------------------------------------
OPatch succeeded.
As you can see, I have an older version of OPatch.
I followed the following steps to fix this:
- Download the latest OPatch from support.oracle.com
p6880880_112000_Linux-x86-64.zip - Make a backup of the actual OPatch and make sure there is no OPatch folder on $ORACLE_HOME folder
[oracle@server 11.2.0.4]$ cd $ORACLE_HOME [oracle@server 11.2.0.4]$ mv OPatch OPatch_old
- Unzip the patch into $ORACLE_HOME
unzip p6880880_112000_Linux-x86-64.zip -d $ORACLE_HOME
And this is how it looks now:
[oracle@server 11204]$ /u01/app/oracle/product/11.2.0.4/OPatch/opatch lsinventory
Oracle Interim Patch Installer version 11.2.0.3.15
Copyright (c) 2017, Oracle Corporation. All rights reserved.
Oracle Home : /u01/app/oracle/product/11.2.0.4
Central Inventory : /u01/app/oracle/oraInv
from : /u01/app/oracle/product/11.2.0.4/oraInst.loc
OPatch version : 11.2.0.3.15
OUI version : 11.2.0.4.0
Log file location : /u01/app/oracle/product/11.2.0.4/cfgtoollogs/opatch/opatch2017-01-09_09-52-47AM_1.log
Lsinventory Output file location : /u01/app/oracle/product/11.2.0.4/cfgtoollogs/opatch/lsinv/lsinventory2017-01-09_09-52-47AM.txt
--------------------------------------------------------------------------------
Local Machine Information::
Hostname: server
ARU platform id: 226
ARU platform description:: Linux x86-64
Installed Top-level Products (1):
Oracle Database 11g 11.2.0.4.0
There are 1 products installed in this Oracle Home.
There are no Interim patches installed in this Oracle Home.
--------------------------------------------------------------------------------
OPatch succeeded.
[oracle@server 11204]$
And now trying again the first patch to deploy:
[oracle@server 20760982]$ $ORACLE_HOME/OPatch/opatch apply Oracle Interim Patch Installer version 11.2.0.3.15 Copyright (c) 2017, Oracle Corporation. All rights reserved. Oracle Home : /u01/app/oracle/product/11.2.0.4 Central Inventory : /u01/app/oracle/oraInv from : /u01/app/oracle/product/11.2.0.4/oraInst.loc OPatch version : 11.2.0.3.15 OUI version : 11.2.0.4.0 Log file location : /u01/app/oracle/product/11.2.0.4/cfgtoollogs/opatch/opatch2017-01-09_09-56-47AM_1.log Verifying environment and performing prerequisite checks... OPatch continues with these patches: 17478514 18031668 18522509 19121551 19769489 20299013 20760982 Do you want to proceed? [y|n] y ......... OPatch succeeded.
So all looks good now.
EM12cR2 upgrade to EM12cR5
I followed the steps below:
# backup the rule sets To make a copy, from the Setup menu, select Incidents, then select Incident Rules. On the Incident Rules - All Enterprise Rules page, in the table, select the out-of-box rule set you want to copy. Then, from the Actions menu, select Create Like Rule Set. In the Create Like Rule Set page, provide the required details and click Save.
# Back up the OMS (the middleware home and the inventory) [oracle@oem bin]$ /u01/app/oracle/product/11.2/OPatch/opatch lsinventory Invoking OPatch 11.2.0.1.1 Oracle Interim Patch Installer version 11.2.0.1.1 Copyright (c) 2009, Oracle Corporation. All rights reserved. Oracle Home : /u01/app/oracle/product/11.2 Central Inventory : /u01/app/oracle/oraInventory from : /etc/oraInst.loc OPatch version : 11.2.0.1.1 OUI version : 11.2.0.2.0 OUI location : /u01/app/oracle/product/11.2/oui Log file location : /u01/app/oracle/product/11.2/cfgtoollogs/opatch/opatch2015-11-18_10-09-47AM.log Patch history file: /u01/app/oracle/product/11.2/cfgtoollogs/opatch/opatch_history.txt Lsinventory Output file location : /u01/app/oracle/product/11.2/cfgtoollogs/opatch/lsinv/lsinventory2015-11-18_10-09-47AM.txt -------------------------------------------------------------------------------- Installed Top-level Products (1): Oracle Database 11g 11.2.0.2.0 There are 1 products installed in this Oracle Home. There are no Interim patches installed in this Oracle Home. -------------------------------------------------------------------------------- OPatch succeeded. [oracle@oem u01]$ locate oraInst.loc | grep oms /u01/app/oracle/product/12.1.0/middleware/oms/oraInst.loc [oracle@oem u01]$ cat /u01/app/oracle/product/12.1.0/middleware/oms/oraInst.loc inventory_loc=/u01/app/oracle/oraInventory inst_group=oinstall [oracle@oem u01]$ locate oraInst.loc | grep agent /u01/app/oracle/product/12.1.0/agent/core/12.1.0.2.0/oraInst.loc [oracle@oem u01]$ cat /u01/app/oracle/product/12.1.0/agent/core/12.1.0.2.0/oraInst.loc inventory_loc=/u01/app/oracle/oraInventory inst_group=oinstall [oracle@oem OMS_BACKUP]$ tar -cvzf GRID_Inventory.tar.gz /u01/app/oracle/oraInventory
# Middle-ware Home and EM domain – take a snapshot of your OMS(Oracle Management Service) use the syntax bellow:
/bin/emctl exportconfig oms [-sysman_pwd ] [-dir ] Specify directory to store backup file [-keep_host] Specify this parameter if the OMS was installed using a virtual hostname (using ORACLE_HOSTNAME=)
cd /u01/app/oracle/product/12.1.0/middleware/oms/bin/ [oracle@oem bin]$ ./emctl exportconfig oms -sysman_pwd -dir /backup/OMS_BACKUP/ Oracle Enterprise Manager Cloud Control 12c Release 2 Copyright (c) 1996, 2012 Oracle Corporation. All rights reserved. ExportConfig started... Machine is Admin Server host. Performing Admin Server backup... Exporting emoms properties... Exporting secure properties... Export has determined that the OMS is not fronted by an SLB. The local hostname was NOT exported. The exported data can be imported on any host but resecure of all agents will be required. Please see the EM Advanced Configuration Guide for more details. Exporting configuration for pluggable modules... Preparing archive file... Backup has been written to file: /backup/OMS_BACKUP/opf_ADMIN_20151118_102429.bka The export file contains sensitive data. Please ensure that it is kept secure. ExportConfig completed successfully!
# Copy the emkey from the existing OMS to the existing Management Repository.
[oracle@oem bin]$ ./emctl config emkey -copy_to_repos -sysman_pwd Oracle Enterprise Manager Cloud Control 12c Release 2 Copyright (c) 1996, 2012 Oracle Corporation. All rights reserved. The EMKey has been copied to the Management Repository. This operation will cause the EMKey to become unsecure. After the required operation has been completed, secure the EMKey by running "emctl config emkey -remove_from_repos". [oracle@oem bin]$ [oracle@oemv bin]$ ./emctl status emkey Oracle Enterprise Manager Cloud Control 12c Release 2 Copyright (c) 1996, 2012 Oracle Corporation. All rights reserved. Enter Enterprise Manager Root (SYSMAN) Password : The EMKey is configured properly, but is not secure. Secure the EMKey by running "emctl config emkey -remove_from_repos". [oracle@oem bin]$
# Preserv changes to the memory
[oracle@oem bin]$ ./emctl get property -name 'JAVA_EM_MEM_ARGS' Oracle Enterprise Manager Cloud Control 12c Release 2 Copyright (c) 1996, 2012 Oracle Corporation. All rights reserved. SYSMAN password: Value for property JAVA_EM_MEM_ARGS for oms All Management Servers is null [oracle@oemv bin]$ $/bin/emctl set property -name 'JAVA_EM_MEM_ARGS' -value '-Xms256m -Xmx1740m'
# stop the EM12c demons:
./$/bin/emctl extended oms jvmd stop -all $/bin/emctl extended oms adp stop –all ./emctl stop oms -all -force ./emctl stop agent
# backup the repository using RMAN
shutdown immediate
startup mount
RMAN> run{
allocate channel c1 device type disk format '/backup/OMS_BACKUP/%U';
backup as compressed backupset database PLUS ARCHIVELOG;
backup current controlfile;
}
.........
shutdown immediate
lsnrctl stop
# Because the DB version is 11.2.0.2 we need to deploy the necessary patches:
p9748749_112020_Linux-x86-64.zip
p11061801_112020_Generic.zip
unzip p9748749_112020_Linux-x86-64.zip unzip p11061801_112020_Generic.zip cd 9748749 $ORACLE_HOME/OPatch/opatch apply cd 11061801 $ORACLE_HOME/OPatch/opatch apply sqlplus / as sysdba startup
#- Upgrade the OMS
cd EM12cr5 ./runInstaller
Deselect the checkbox and click Next:
Click “Yes” and move forward
Select “Skip” and click on Next
I ignored the warning in my case since I have both packages installed already. Choose “Ignore” and click Next.
As we do an upgrade choose as in the image above and click Next.
Fill in the Middleware Home Location. Be aware that it has to be a new location and with ~15GB free.
Fill in the necessary password and confirm that you have backed up the repository and click Next.
Click OK and move on.
For the following 2 screens click OK and Yes respectively.
Below you can see all the components that will be upgraded:
Click Next.
Below you can select additional plugins to be deployed:
Click Next when you’re done.
This time I’m doing the installation of the new home on an NFS volume:
In the message below is warning me and it’s giving me an advice about how to proceed after the installation has completed:
Click OK and move on.
Click Next to proceed with the deployment:
The last thing for the OMS upgrade:
Now running the allroot.sh script
And that was it:
