Upgrade Oracle Database 11gR2 (11.2.0.4) to 12c (12.2.0.1) using DBUA
Posted by Mir Sayeed Hassan on October 26th, 2019
Upgrade Oracle Database 11gR2 (11.2.0.4) to 12c (12.2.0.1) using DBUA
DB Environment:
Hostname : testdb Database Name : testdb.com Source DB Version : 11.2.0.4.0 Source DB home location : /u01/app/oracle/product/11.2.0/dbhome_1/ Target DB Version : 12.2.0.1.0 Target DB Home location : /u01/app/oracle/product/12.2.0.1/dbhome_1/
Install the Oracle 12c Software: Refer link:
http://www.mirsayeedhassan.com/how-to-install-the-oracle-12c-software-only-on-oracle-linux-6-7/
Login to the oracle 11g DB & create the directory for preupgrade
[oracle@testdb ~]$ mkdir -p /home/oracle/preupgrade
Run the Pre-upgrade script
[oracle@testdb ~]$ /u01/app/oracle/product/11.2.0/dbhome_1/jdk/bin/java -jar /u01/app/oracle/product/12.2.0.1/dbhome_1/rdbms/admin/preupgrade.jar FILE DIR /home/oracle/preupgrade Preupgrade generated files: /home/oracle/preupgrade/preupgrade.log /home/oracle/preupgrade/preupgrade_fixups.sql /home/oracle/preupgrade/postupgrade_fixups.sql
Check the Preupgrade.log file & perform the recommended action
[oracle@testdb ~]$ cat /home/oracle/preupgrade/ dbms_registry_basic.sql oracle/ preupgrade_driver.sql preupgrade.log preupgrade_package.sql dbms_registry_extended.sql postupgrade_fixups.sql preupgrade_fixups.sql preupgrade_messages.properties upgrade/
[oracle@testdb ~]$ cat /home/oracle/preupgrade/preupgrade.log 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: TESTDB 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: ARCHIVELOG 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 550 MB ENABLED 1453 MB None SYSTEM 750 MB ENABLED 1251 MB None TEMP 29 MB ENABLED 150 MB None UNDOTBS1 70 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 + Set DB_RECOVERY_FILE_DEST_SIZE initialization parameter to at least 5391
-
Check alert log during the upgrade to ensure there is remaining
free space available in the recovery area. DB_RECOVERY_FILE_DEST_SIZE is set at 4182 MB. There is currently 4106 MB of free space remaining, which may not be adequate for the upgrade. Currently: Fast recovery area : /u01/app/oracle/fast_recovery_area Limit : 4182 MB Used : 76 MB Available : 4106 MB The database has archivelog mode enabled, and the upgrade process will need free space to generate archived logs to the recovery area specified by initialization parameter DB_RECOVERY_FILE_DEST. The logs generated must not overflow the limit set by DB_RECOVERY_FILE_DEST_SIZE, as the upgrade may not proceed if the database stops responding. 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. + Run 11.2.0.4.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. + 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.
Recommended Action from Oracle 11gR2
Database Status
SQL> select instance_name,status,version from v$instance; INSTANCE_NAME STATUS VERSION ---------------- ---------------------------------------------- testdb OPEN 11.2.0.4.0
Stop the Enterprise manager
[oracle@testdb ~]$ emctl stop dbconsole
Remove the EM Repository
SQL> !ls -ltr /u01/app/oracle/product/12.2.0.1/dbhome_1/rdbms/admin/emremove.sql; -rw-r--r--. 1 oracle oracle 20740 Aug 8 2016 /u01/app/oracle/product/12.2.0.1/dbhome_1/rdbms/admin/emremove.sql
SQL> @/u01/app/oracle/product/12.2.0.1/dbhome_1/rdbms/admin/emremove.sql;
old 70: IF (upper('&LOGGING') = 'VERBOSE')
new 70: IF (upper('VERBOSE') = 'VERBOSE')
PL/SQL procedure successfully completed.
Remove OLAP Repository
SQL> SET ECHO ON; SQL> SET SERVEROUTPUT ON; SQL> !ls -lrt /u01/app/oracle/product/11.2.0/dbhome_1/olap/admin/catnoamd.sql; -rw-r--r--. 1 oracle oracle 11916 Apr 14 2013 /u01/app/oracle/product/11.2.0/dbhome_1/olap/admin/catnoamd.sql
SQL> @/u01/app/oracle/product/11.2.0/dbhome_1/olap/admin/catnoamd.sql;
Note: This will take 1 mins to complete & all the OLAP repository will be dropped
Update the Processes
Note: Check if the processes is less than 300 then update or else not require
SQL> show parameter processes processes integer 150
SQL> alter system set processes=300 scope=spfile; System altered.
Gather Directory Stats
SQL> SET ECHO ON; SQL> SET SERVEROUTPUT ON; SQL> EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS; PL/SQL procedure successfully completed.
Purge Recyclebin
SQL> purge dba_recyclebin; DBA Recyclebin purged.
Refresh Materialize view
SQL> declare 2 list_failures integer(3) :=0; 3 begin 4 DBMS_MVIEW.REFRESH_ALL_MVIEWS(list_failures,'C','', TRUE, FALSE); 5 end; 6 / PL/SQL procedure successfully completed.
Run preupgrade_fixups.sql;
SQL> SET ECHO ON; SQL> SET SERVEROUTPUT ON; SQL> @/home/oracle/preupgrade/preupgrade_fixups.sql SQL> REM Oracle PRE-Upgrade Fixup Script SQL> REM Auto-Generated by: Oracle Preupgrade Script SQL> REM Version: 12.2.0.1.0 Build: 1 SQL> REM Generated on: 2019-10-14 20:56:59 SQL> REM Source Database: TESTDB SQL> REM Source Database Version: 11.2.0.4.0 SQL> REM For Upgrade to Version: 12.2.0.1.0 SQL> REM Setup Environment SQL> REM SQL> SET ECHO OFF SERVEROUTPUT ON FORMAT WRAPPED TAB OFF LINESIZE 200; Executing Oracle PRE-Upgrade Fixup Script Auto-Generated by: Oracle Preupgrade Script Version: 12.2.0.1.0 Build: 1 Generated on: 2019-10-14 20:56:59 For Source Daabase: TESTDB Source Database Version: 11.2.0.4.0 For Upgrade to Version: 12.2.0.1.0 Fixup Check Name Status Further DBA Action ---------------------------------------------------------------------------------------------- min_recovery_area_size Failed Manual fixup required. em_present Passed None invalid_objects_exist Failed Manual fixup recommended. amd_exists Passed None dictionary_stats Passed None trgowner_no_admndbtrg Passed None apex_upgrade_msg Failed Manual fixup recommended. PL/SQL procedure successfully completed.
SQL> !df -h /u01/app/oracle/fast_recovery_area Filesystem Size Used Avail Use% Mounted on /dev/mapper/centos-u01 98G 22G 76G 23% /u01
Create the Flashback Guaranteed restore point
Note:
DB in archivelog mode, flashback db should not be enable from 11R2 Version onwards.
Check db in archivelog mode
SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 16 Next log sequence to archive 18 Current log sequence 18
Check the status of flashback
SQL> select flashback_on from v$database; FLASHBACK_ON ------------- NO
DB Compatible
SQL> show parameter compatible NAME TYPE VALUE ----------------------------------- ----------- ------------ compatible string 11.2.0.4.0
Increase the archive size
SQL> show parameter db_recovery_file_dest; NAME TYPE VALUE ---------------------------- ----------- ------------------------------------------------------ db_recovery_file_dest string /u01/app/oracle/fast_recovery_area db_recovery_file_dest_size big integer 1G
SQL> alter system set db_recovery_file_dest_size=15g; System altered.
Check the restore point
SQL> select * from V$restore_point; no rows selected
Create the restore point
SQL> create restore point pre_upgrade_14oct19 guarantee flashback database; Restore point created.
SQL> col name for a20 SQL> col GUARANTEE_FLASHBACK_DATABASE for a15 SQL> col TIME for a50 SQL> set lines 300 SQL> select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from V$restore_point; NAME GUARANTEE_FLASH TIME -------------------- --------------- -------------------------------------------------- PRE_UPGRADE_14OCT19 YES 14-OCT-19 09.54.00.000000000 PM
Check the invalid objects exist in database
SQL> SET SERVEROUTPUT ON; SQL> EXECUTE DBMS_PREUP.INVALID_OBJECTS; SYS/SYSTEM INVALID OBJECTS OWNER |OBJECT_NAME |OBJECT_TYPE ----------------------------------------------------------------------------------------- NON SYS/SYSTEM INVALID OBJECTS OWNER |OBJECT_NAME |OBJECT_TYPE ----------------------------------------------------------------------------------------- MIR MP1 PROCEDURE PL/SQL procedure successfully completed.
SQL> drop procedure mir.MP1; Procedure dropped.
Re-run the preupgrade_fixups scripts
SQL> @/home/oracle/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-10-14 20:56:59 For Source Database: TESTDB Source Database Version: 11.2.0.4.0 For Upgrade to Version: 12.2.0.1.0 Fixup Check Name Status Further DBA Action ---------------------------------------------------------------------------------- min_recovery_area_size Passed None em_present Passed None invalid_objects_exist Passed None amd_exists Passed None dictionary_stats Passed None trgowner_no_admndbtrg Passed None apex_upgrade_msg Failed Manual fixup recommended. PL/SQL procedure successfully completed.
RUN UPGRADE USING DBUA
Set the enirnonment variables
[oracle@testdb ~]$ export ORACLE_HOME=$ORACLE_BASE/product/12.2.0.1/dbhome_1
[oracle@testdb ~]$ export PATH=$ORACLE_HOME/bin:$PATH
[oracle@testdb ~]$ dbua
Or
[oracle@testdb ~]$ /u01/app/oracle/product/12.2.0.1/dbhome_1/bin/dbua
Hence the database is upgraded successfully.
Connect to Oracle 12c Upgraded DB & Start the POST UPGRADE PROCESS
[oracle@testdb upgrade2019-10-14_10-26-49-PM]$ export ORACLE_HOME=$ORACLE_BASE/product/12.2.0.1/dbhome_1
[oracle@testdb upgrade2019-10-14_10-26-49-PM]$ export PATH=$ORACLE_HOME/bin:$PATH
[oracle@testdb upgrade2019-10-14_10-26-49-PM]$ !sq sqlplus / as sysdba SQL> select status from v$instance; STATUS ------ OPEN
SQL> select name,open_mode,version from v$database, v$instance; NAME OPEN_MODE VERSION -------- -------------------- ------------ TESTDB READ WRITE 12.2.0.1.0
Perform the Postupgrade Scripts
SQL> @/u01/app/oracle/cfgtoollogs/dbua/upgrade2019-10-14_10-26-49-PM/testdb/postupgrade_fixups.sql; Session altered. PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. Package created. No errors. Package body created. No errors. Package created. No errors. Package body created. No errors. Executing Oracle POST-Upgrade Fixup Script Auto-Generated by: Oracle Preupgrade Script Version: 12.2.0.1.0 Build: 1 Generated on: 2019-10-14 22:30:33 For Source Database: TESTDB Source Database Version: 11.2.0.4.0 For Upgrade to Version: 12.2.0.1.0 Fixup Check Name Status Further DBA Action ------------------------------------------------------------------------------ old_time_zones_exist Passed None post_dictionary Passed None fixed_objects Passed None upg_by_std_upgrd Passed None PL/SQL procedure successfully completed. Session altered.
Upgrade the database time zone version using the DBMS_DST package.SQL> EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
PL/SQL procedure successfully completed.
Gather statistics on fixed objects two weeks after the upgrade
SQL> EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS; PL/SQL procedure successfully completed.
Check the Invalid object status
SQL> select comp_id,status from dba_registry; COMP_ID STATUS ----------------------------------------- CATALOG VALID CATPROC VALID JAVAVM VALID XML VALID CATJAVA VALID APS VALID OWM VALID CONTEXT VALID XDB VALID ORDIM VALID SDO VALID XOQ VALID APEX VALID 13 rows selected.
Oratab
[oracle@testdb ~]$ cat /etc/oratab | grep -i testdb testdb:/u01/app/oracle/product/12.2.0.1/dbhome_1:N
Finally check the flashback status & drop the restore point.
SQL> select name, flashback_on from v$database; NAME FLASHBACK_ON --------------------------------------- TESTDB RESTORE POINT ONLY
Check the restore point name, status & drop
SQL> select NAME, GUARANTEE_FLASHBACK_DATABASE, TIME from v$restore_point; NAME GUA TIME --------------------------------- --------------------------------------------- PRE_UPGRADE_14OCT19 YES 14-OCT-19 09.54.00.000000000 PM
SQL> drop restore point PRE_UPGRADE_14OCT19; Restore point dropped.
Hence
SQL> show parameter compatible; NAME TYPY VALUE ------------------------------------ ------------------------ compatible string 11.2.0.4.0 noncdb_compatible boolean FALSE
SQL> ALTER SYSTEM SET COMPATIBLE = '12.2.0' SCOPE=SPFILE; System altered.
SQL> shu immediate Database closed. Database dismounted. ORACLE instance shut down.
SQL> startup ORACLE instance started. Total System Global Area 1828716544 bytes Fixed Size 8621856 bytes Variable Size 587202784 bytes Database Buffers 1224736768 bytes Redo Buffers 8155136 bytes Database mounted. Database opened.
SQL> show parameter compatible NAME TYPE VALUE --------------------------------------------------------------- compatible string 12.2.0 noncdb_compatible boolean FALSE
SQL> select name, open_mode, version from v$database, v$instance; NAME OPEN_MODE VERSION -------- -------------------- ------------------------------ TESTDB READ WRITE 12.2.0.1.0
Stop & Start the listener & check the status
[oracle@testdb ~]$ lsnrctl stop LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 15-OCT-2019 15:10:46 Copyright (c) 1991, 2016, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=testdb)(PORT=1521))) The command completed successfully
[oracle@testdb ~]$ lsnrctl start
[oracle@testdb ~]$ lsnrctl status LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 15-OCT-2019 15:12:24 Copyright (c) 1991, 2016, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=testdb)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 12.2.0.1.0 - Production Start Date 15-OCT-2019 15:10:50 Uptime 0 days 0 hr. 1 min. 34 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/12.2.0.1/dbhome_1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/testdb/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=testdb)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Services Summary... Service "testdb.mirsayeedhassan.com" has 1 instance(s). Instance "testdb", status READY, has 1 handler(s) for this service... Service "testdbXDB.mirsayeedhassan.com" has 1 instance(s). Instance "testdb", status READY, has 1 handler(s) for this service... The command completed successfully.
=====Hence tested & verified in our test env=====