12cR1’i 12cR2’ye upgrade etmeden önce “Oracle Database Pre-Upgrade (preupgrade.jar)” tool’unu mutlaka çalıştırmalıyız. Gerekli olduğu durumda upgrade öncesi eksikleri tamamlamamız için bilgileri verir. Upgrade bittikten sonra da “postupgrade_fixups.sql” scriptini çalıştırmalıyız.
[oracle@testdb01 ~]$ $ORACLE_HOME/jdk/bin/java -jar /oracle/app/oracle/product/12.2.0.1/dbhome_1/rdbms/admin/preupgrade.jar 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: TST12C Container Name: CDB$ROOT Container ID: 1 Version: 12.1.0.2.0 Compatible: 12.1.0.2.0 Blocksize: 8192 Platform: Linux x86 64-bit Timezone File: 18 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 Real Application Clusters [to be upgraded] OPTION OFF Oracle Workspace Manager [to be upgraded] VALID OLAP Analytic Workspace [to be upgraded] VALID Oracle Label Security [to be upgraded] VALID Oracle Database Vault [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 Oracle Application Express [to be upgraded] VALID Oracle OLAP API [to be upgraded] VALID ============== BEFORE UPGRADE ============== Run <preupgradeLogDirPath>/preupgrade_fixups_CDB_ROOT.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 640 MB ENABLED 1548 MB None SYSTEM 790 MB ENABLED 1299 MB None TEMP 60 MB ENABLED 150 MB None UNDOTBS1 80 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. + Set DB_RECOVERY_FILE_DEST_SIZE initialization parameter to at least 10 GB. 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 4560 MB. There is currently 2993 MB of free space remaining, which may not be adequate for the upgrade. Currently: Fast recovery area : +DATA Limit : 4560 MB Used : 1567 MB Available : 2993 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. INFORMATION ONLY ================ + Consider upgrading APEX manually, before the database upgrade. The database contains APEX version 4.2.5.00.08 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_CDB_ROOT.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 18 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. 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: TST12C Container Name: PDB$SEED Container ID: 2 Version: 12.1.0.2.0 Compatible: 12.1.0.2.0 Blocksize: 8192 Platform: Linux x86 64-bit Timezone File: 18 Database log mode: ARCHIVELOG Readonly: TRUE 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 Real Application Clusters [to be upgraded] OPTION OFF Oracle Workspace Manager [to be upgraded] VALID OLAP Analytic Workspace [to be upgraded] VALID Oracle Label Security [to be upgraded] VALID Oracle Database Vault [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 Oracle Application Express [to be upgraded] VALID Oracle OLAP API [to be upgraded] VALID ============== BEFORE UPGRADE ============== Run <preupgradeLogDirPath>/preupgrade_fixups_PDB_SEED.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 1422 MB None SYSTEM 250 MB ENABLED 755 MB None TEMP 20 MB ENABLED 150 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. RECOMMENDED ACTIONS =================== + (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 12.1.0.2 Oracle Database SQL Tuning Guide. INFORMATION ONLY ================ + Consider upgrading APEX manually, before the database upgrade. The database contains APEX version 4.2.5.00.08 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_PDB_SEED.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 18 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. Preupgrade generated files: /oracle/app/cfgtoollogs/TST12C/preupgrade/preupgrade_fixups.sql /oracle/app/cfgtoollogs/TST12C/preupgrade/postupgrade_fixups.sql [oracle@testdb01 ~]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Wed Aug 2 16:25:44 2017 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics and Real Application Testing options SQL> @/oracle/app/cfgtoollogs/TST12C/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: 2017-08-02 16:17:00 For Source Database: TST12C Source Database Version: 12.1.0.2.0 For Upgrade to Version: 12.2.0.1.0 Executing in container: CDB$ROOT Fixup Check Name Status Further DBA Action ---------- ------ ------------------ min_recovery_area_size Failed Manual fixup required. apex_upgrade_msg Failed Manual fixup recommended. PL/SQL procedure successfully completed. Session altered. SQL>
——— after upgrade;
[oracle@testdb01 ~]$ export ORACLE_HOME=/oracle/app/oracle/product/12.2.0.1/dbhome_1 [oracle@testdb01 ~]$ echo $ORACLE_SID TST12C [oracle@testdb01 ~]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Thu Aug 3 17:06:40 2017 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL> select name from v$database; NAME --------- TST12C SQL> show con_name CON_NAME ------------------------------ CDB$ROOT SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO SQL> @/oracle/app/cfgtoollogs/TST12C/preupgrade/postupgrade_fixups.sql; Session altered. PL/SQL procedure successfully completed. Session altered. 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: 2017-08-02 16:17:04 For Source Database: TST12C Source Database Version: 12.1.0.2.0 For Upgrade to Version: 12.2.0.1.0 Executing in container: CDB$ROOT Fixup Check Name Status Further DBA Action ---------- ------ ------------------ old_time_zones_exist Passed None post_dictionary Passed None PL/SQL procedure successfully completed. Session altered. SQL>
Orhan Eripek's Oracle Blog 