My Acclaim Certification : Credly Profile
My Oracle ACE Pro Profile
Awarded Top 100 Oracle Blogs from Worldwide - #RANK 39
Posted by Mir Sayeed Hassan on October 26th, 2019
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/
[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
[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 /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 /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.
SQL> select instance_name,status,version from v$instance; INSTANCE_NAME STATUS VERSION ---------------- ---------------------------------------------- testdb OPEN 11.2.0.4.0
[oracle@testdb ~]$ emctl stop dbconsole
