Description:-
A manual upgrade consists of running SQL scripts and utilities from a command line to upgrade a database to the new Oracle Database release.
Oracle Database Releases That Support Direct Upgrade
Review the supported options for direct upgrades to the latest Oracle Database release.
You can perform a direct upgrade to the new release from the following releases:
• 11.2.0.4
• 12.1.0.1 and 12.1.0.2
• 12.2.0.1
Presteps for db upgrade :-
Take RMAN full backups before upgrade.
rman target /
run {
allocate channel test_backup_disk1 type disk format '/u01/backup/%d_%U';
backup incremental level 0 tag 'ORCL_BEFORE_UPG' database;
backup format tag 'ORCL_CONTROL_FILE' current controlfile;
release channel test_backup_disk1;
}
Ensure backup is complete before upgrade.
SQL> SELECT * FROM v$backup WHERE status != 'NOT ACTIVE'; no rows selected
Empty Recycle bin.
SQL> PURGE DBA_RECYCLEBIN ; DBA Recyclebin purged.
Run Gather statistics to finish upgrade soon.
SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS; PL/SQL procedure successfully completed.
Run the preupgrade tool:
The preupgrade.jar Pre-Upgrade Information Tool is supplied with Oracle Database 18c.This tool has reside in new oracle home.
/u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/preupgrade.jar
[oracle@19c ~]$ /u01/app/oracle/product/11.2.0/dbhome_1/jdk/bin/java -jar /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/preupgrade.jar FILE DIR /u01/preupgrade/ ================== PREUPGRADE SUMMARY ================== /u01/preupgrade/preupgrade.log /u01/preupgrade/preupgrade_fixups.sql /u01/preupgrade/postupgrade_fixups.sql Execute fixup scripts as indicated below: Before upgrade: Log into the database and execute the preupgrade fixups @/u01/preupgrade/preupgrade_fixups.sql After the upgrade: Log into the database and execute the postupgrade fixups @/u01/preupgrade/postupgrade_fixups.sql Preupgrade complete: 2019-03-21T21:12:31
Run preupgrade fixups.sql
SQL> @/u01/preupgrade/preupgrade_fixups.sql Executing Oracle PRE-Upgrade Fixup Script Auto-Generated by: Oracle Preupgrade Script Version: 19.0.0.0.0 Build: 1 Generated on: 2019-03-21 21:12:27 For Source Database: MIG19C Source Database Version: 11.2.0.4.0 For Upgrade to Version: 19.0.0.0.0 Preup Preupgrade Action Issue Is Number Preupgrade Check Name Remedied Further DBA Action ------ ------------------------ ---------- -------------------------------- 1. parameter_min_val NO Manual fixup recommended. 2. em_present NO Manual fixup recommended. 3. amd_exists NO Manual fixup recommended. 4. apex_manual_upgrade NO Manual fixup recommended. 5. dictionary_stats YES None. 6. trgowner_no_admndbtrg YES None. 7. pre_fixed_objects YES None. 8. tablespaces_info NO Informational only. Further action is optional. 9. exf_rul_exists NO Informational only. Further action is optional. 10. rman_recovery_version NO Informational only. Further action is optional. The fixup scripts have been run and resolved what they can. However, there are still issues originally identified by the preupgrade that have not been remedied and are still present in the database. Depending on the severity of the specific issue, and the nature of the issue itself, that could mean that your database is not ready for upgrade. To resolve the outstanding issues, start by reviewing the preupgrade_fixups.sql and searching it for the name of the failed CHECK NAME or Preupgrade Action Number listed above. There you will find the original corresponding diagnostic message from the preupgrade which explains in more detail what still needs to be done. PL/SQL procedure successfully completed.
Check Timezone version :
Check if target database’s time zone version is lower than the source database time zone version.
SQL> SELECT version FROM v$timezone_file; VERSION ------- 14 SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME; 2 3 4 PROPERTY_NAME VALUE ------------------------ ----- DST_PRIMARY_TT_VERSION 14 DST_SECONDARY_TT_VERSION 0 DST_UPGRADE_STATE NONE
Upgrade Database from 11.2.0.4 to 19c :-
Shutdown the 11g database:
SQL> shut immediate Database closed. Database dismounted. ORACLE instance shut down.
Copy the parameter and password file from the 11g home to the new 19c home.
[oracle@19c ~]$ cp /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileMIG19C.ora /u01/app/oracle/product/19.0.0/dbhome_1 [oracle@19c ~]$ cp /u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwMIG19C /u01/app/oracle/product/19.0.0/dbhome_1
Set new ORACLE_HOME for 19c location and startup database in upgrade mode :
[oracle@19c ~]$ export ORACLE_SID=MIG19C [oracle@19c ~]$ export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1 [oracle@19c ~]$ export PATH=/u01/app/oracle/product/19.0.0/dbhome_1:$PATH [oracle@19c ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Thu Mar 21 21:33:54 2019 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to an idle instance. SQL> startup upgrade ORACLE instance started. Total System Global Area 1543500136 bytes Fixed Size 8896872 bytes Variable Size 939524096 bytes Database Buffers 587202560 bytes Redo Buffers 7876608 bytes Database mounted. Database opened.
Run the dbupgrade utility :
[oracle@19c bin]$ export ORACLE_SID=MIG19C [oracle@19c bin]$ ./dbupgrade Argument list for [/u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/catctl.pl] For Oracle internal use only A = 0 Run in c = 0 Do not run in C = 0 Input Directory d = 0 Echo OFF e = 1 Simulate E = 0 Forced cleanup F = 0 Log Id i = 0 Child Process I = 0 Log Dir l = 0 Priority List Name L = 0 Upgrade Mode active M = 0 SQL Process Count n = 0 SQL PDB Process Count N = 0 Open Mode Normal o = 0 Start Phase p = 0 End Phase P = 0 Reverse Order r = 0 AutoUpgrade Resume R = 0 Script s = 0 Serial Run S = 0 RO User Tablespaces T = 0 Display Phases y = 0 Debug catcon.pm z = 0 Debug catctl.pl Z = 0 catctl.pl VERSION: [19.0.0.0.0] STATUS: [Production] BUILD: [RDBMS_19.2.0.0.0_LINUX.X64_190204] /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/orahome = [/u01/app/oracle/product/19.0.0/dbhome_1] /u01/app/oracle/product/19.0.0/dbhome_1/bin/orabasehome = [/u01/app/oracle/product/19.0.0/dbhome_1] catctlGetOraBaseLogDir = [/u01/app/oracle/product/19.0.0/dbhome_1] Analyzing file /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/catupgrd.sql Log file directory = [/tmp/cfgtoollogs/upgrade20190321214707] catcon::set_log_file_base_path: ALL catcon-related output will be written to [/tmp/cfgtoollogs/upgrade20190321214707/catupgrd_catcon_9390.lst] catcon::set_log_file_base_path: catcon: See [/tmp/cfgtoollogs/upgrade20190321214707/catupgrd*.log] files for output generated by scripts catcon::set_log_file_base_path: catcon: See [/tmp/cfgtoollogs/upgrade20190321214707/catupgrd_*.lst] files for spool files, if any Number of Cpus = 1 Database Name = MIG19C DataBase Version = 11.2.0.4.0 catcon::set_log_file_base_path: ALL catcon-related output will be written to [/u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/MIG19C/upgrade20190321214709/catupgrd_catcon_9390.lst] catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/MIG19C/upgrade20190321214709/catupgrd*.log] files for output generated by scripts catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/MIG19C/upgrade20190321214709/catupgrd_*.lst] files for spool files, if any Log file directory = [/u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/MIG19C/upgrade20190321214709] Components in [MIG19C] Installed [APEX APS CATALOG CATJAVA CATPROC CONTEXT EM JAVAVM ORDIM OWM SDO XDB XML XOQ] Not Installed [DV MGW ODM OLS RAC WK] Parallel SQL Process Count = 4 ------------------------------------------------------ Phases [0-107] Start Time:[2019_03_21 21:47:10] ------------------------------------------------------ *********** Executing Change Scripts *********** Serial Phase #:0 [MIG19C] Files:1 [oracle@19c bin]$ export ORACLE_SID=MIG19C [oracle@19c bin]$ ./dbupgrade Argument list for [/u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/catctl.pl] For Oracle internal use only A = 0 Run in c = 0 Do not run in C = 0 Input Directory d = 0 Echo OFF e = 1 Simulate E = 0 Forced cleanup F = 0 Log Id i = 0 Child Process I = 0 Log Dir l = 0 Priority List Name L = 0 Upgrade Mode active M = 0 SQL Process Count n = 0 SQL PDB Process Count N = 0 Open Mode Normal o = 0 Start Phase p = 0 End Phase P = 0 Reverse Order r = 0 AutoUpgrade Resume R = 0 Script s = 0 Serial Run S = 0 RO User Tablespaces T = 0 Display Phases y = 0 Debug catcon.pm z = 0 Debug catctl.pl Z = 0 catctl.pl VERSION: [19.0.0.0.0] STATUS: [Production] BUILD: [RDBMS_19.2.0.0.0_LINUX.X64_190204] /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/orahome = [/u01/app/oracle/product/19.0.0/dbhome_1] /u01/app/oracle/product/19.0.0/dbhome_1/bin/orabasehome = [/u01/app/oracle/product/19.0.0/dbhome_1] catctlGetOraBaseLogDir = [/u01/app/oracle/product/19.0.0/dbhome_1] Analyzing file /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/catupgrd.sql Log file directory = [/tmp/cfgtoollogs/upgrade20190321214707] catcon::set_log_file_base_path: ALL catcon-related output will be written to [/tmp/cfgtoollogs/upgrade20190321214707/catupgrd_catcon_9390.lst] catcon::set_log_file_base_path: catcon: See [/tmp/cfgtoollogs/upgrade20190321214707/catupgrd*.log] files for output generated by scripts catcon::set_log_file_base_path: catcon: See [/tmp/cfgtoollogs/upgrade20190321214707/catupgrd_*.lst] files for spool files, if any Number of Cpus = 1 Database Name = MIG19C DataBase Version = 11.2.0.4.0 catcon::set_log_file_base_path: ALL catcon-related output will be written to [/u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/MIG19C/upgrade20190321214709/catupgrd_catcon_9390.lst] catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/MIG19C/upgrade20190321214709/catupgrd*.log] files for output generated by scripts catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/MIG19C/upgrade20190321214709/catupgrd_*.lst] files for spool files, if any Log file directory = [/u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/MIG19C/upgrade20190321214709] Components in [MIG19C] Installed [APEX APS CATALOG CATJAVA CATPROC CONTEXT EM JAVAVM ORDIM OWM SDO XDB XML XOQ] Not Installed [DV MGW ODM OLS RAC WK] Parallel SQL Process Count = 4 ------------------------------------------------------ Phases [0-107] Start Time:[2019_03_21 21:47:10] ------------------------------------------------------ *********** Executing Change Scripts *********** Serial Phase #:0 [MIG19C] Files:1 Time: 68s *************** Catalog Core SQL *************** Serial Phase #:1 [MIG19C] Files:5 Time: 48s Restart Phase #:2 [MIG19C] Files:1 Time: 1s *********** Catalog Tables and Views *********** Parallel Phase #:3 [MIG19C] Files:19 Time: 25s Restart Phase #:4 [MIG19C] Files:1 Time: 1s ************* Catalog Final Scripts ************ Serial Phase #:5 [MIG19C] Files:7 Time: 24s ***************** Catproc Start **************** Serial Phase #:6 [MIG19C] Files:1 Time: 12s ***************** Catproc Types **************** Serial Phase #:7 [MIG19C] Files:2 Time: 10s Restart Phase #:8 [MIG19C] Files:1 Time: 0s **************** Catproc Tables **************** Parallel Phase #:9 [MIG19C] Files:67 Time: 42s Restart Phase #:10 [MIG19C] Files:1 Time: 1s ************* Catproc Package Specs ************ Serial Phase #:11 [MIG19C] Files:1 Time: 63s Restart Phase #:12 [MIG19C] Files:1 Time: 0s ************** Catproc Procedures ************** Parallel Phase #:13 [MIG19C] Files:94 Time: 12s Restart Phase #:14 [MIG19C] Files:1 Time: 1s Parallel Phase #:15 [MIG19C] Files:120 Time: 21s Restart Phase #:16 [MIG19C] Files:1 Time: 0s Serial Phase #:17 [MIG19C] Files:22 Time: 3s Restart Phase #:18 [MIG19C] Files:1 Time: 1s ***************** Catproc Views **************** Parallel Phase #:19 [MIG19C] Files:32 Time: 28s Restart Phase #:20 [MIG19C] Files:1 Time: 1s Serial Phase #:21 [MIG19C] Files:3 Time: 11s Restart Phase #:22 [MIG19C] Files:1 Time: 0s Parallel Phase #:23 [MIG19C] Files:25 Time: 197s Restart Phase #:24 [MIG19C] Files:1 Time: 1s Parallel Phase #:25 [MIG19C] Files:12 Time: 106s Restart Phase #:26 [MIG19C] Files:1 Time: 0s Serial Phase #:27 [MIG19C] Files:1 Time: 0s Serial Phase #:28 [MIG19C] Files:3 Time: 4s Serial Phase #:29 [MIG19C] Files:1 Time: 0s Restart Phase #:30 [MIG19C] Files:1 Time: 0s *************** Catproc CDB Views ************** Serial Phase #:31 [MIG19C] Files:1 Time: 1s Restart Phase #:32 [MIG19C] Files:1 Time: 1s Serial Phase #:34 [MIG19C] Files:1 Time: 0s ***************** Catproc PLBs ***************** Serial Phase #:35 [MIG19C] Files:293 Time: 22s Serial Phase #:36 [MIG19C] Files:1 Time: 0s Restart Phase #:37 [MIG19C] Files:1 Time: 0s Serial Phase #:38 [MIG19C] Files:6 Time: 5s Restart Phase #:39 [MIG19C] Files:1 Time: 0s *************** Catproc DataPump *************** Serial Phase #:40 [MIG19C] Files:3 Time: 51s Restart Phase #:41 [MIG19C] Files:1 Time: 1s ****************** Catproc SQL ***************** Parallel Phase #:42 [MIG19C] Files:13 Time: 117s Restart Phase #:43 [MIG19C] Files:1 Time: 1s Parallel Phase #:44 [MIG19C] Files:11 Time: 17s Restart Phase #:45 [MIG19C] Files:1 Time: 0s Parallel Phase #:46 [MIG19C] Files:3 Time: 2s Restart Phase #:47 [MIG19C] Files:1 Time: 0s ************* Final Catproc scripts ************ Serial Phase #:48 [MIG19C] Files:1 Time: 9s Restart Phase #:49 [MIG19C] Files:1 Time: 1s ************** Final RDBMS scripts ************* Serial Phase #:50 [MIG19C] Files:1 Time: 20s ************ Upgrade Component Start *********** Serial Phase #:51 [MIG19C] Files:1 Time: 1s Restart Phase #:52 [MIG19C] Files:1 Time: 0s ********** Upgrading Java and non-Java ********* Serial Phase #:53 [MIG19C] Files:2 Time: 587s ***************** Upgrading XDB **************** Restart Phase #:54 [MIG19C] Files:1 Time: 1s Serial Phase #:56 [MIG19C] Files:3 Time: 28s Serial Phase #:57 [MIG19C] Files:3 Time: 4s Parallel Phase #:58 [MIG19C] Files:10 Time: 3s Parallel Phase #:59 [MIG19C] Files:25 Time: 4s Serial Phase #:60 [MIG19C] Files:4 Time: 7s Serial Phase #:61 [MIG19C] Files:1 Time: 0s Serial Phase #:62 [MIG19C] Files:32 Time: 4s Serial Phase #:63 [MIG19C] Files:1 Time: 0s Parallel Phase #:64 [MIG19C] Files:6 Time: 6s Serial Phase #:65 [MIG19C] Files:2 Time: 22s Serial Phase #:66 [MIG19C] Files:3 Time: 80s **************** Upgrading ORDIM *************** Restart Phase #:67 [MIG19C] Files:1 Time: 1s Serial Phase #:69 [MIG19C] Files:1 Time: 1s Parallel Phase #:70 [MIG19C] Files:2 Time: 56s Restart Phase #:71 [MIG19C] Files:1 Time: 0s Parallel Phase #:72 [MIG19C] Files:2 Time: 1s Serial Phase #:73 [MIG19C] Files:2 Time: 1s ***************** Upgrading SDO **************** Restart Phase #:74 [MIG19C] Files:1 Time: 0s Serial Phase #:76 [MIG19C] Files:1 Time: 85s Serial Phase #:77 [MIG19C] Files:2 Time: 2s Restart Phase #:78 [MIG19C] Files:1 Time: 1s Serial Phase #:79 [MIG19C] Files:1 Time: 19s Restart Phase #:80 [MIG19C] Files:1 Time: 0s Parallel Phase #:81 [MIG19C] Files:3 Time: 82s Restart Phase #:82 [MIG19C] Files:1 Time: 1s Serial Phase #:83 [MIG19C] Files:1 Time: 5s Restart Phase #:84 [MIG19C] Files:1 Time: 0s Serial Phase #:85 [MIG19C] Files:1 Time: 11s Restart Phase #:86 [MIG19C] Files:1 Time: 0s Parallel Phase #:87 [MIG19C] Files:4 Time: 142s Restart Phase #:88 [MIG19C] Files:1 Time: 1s Serial Phase #:89 [MIG19C] Files:1 Time: 1s Restart Phase #:90 [MIG19C] Files:1 Time: 0s Serial Phase #:91 [MIG19C] Files:2 Time: 9s Restart Phase #:92 [MIG19C] Files:1 Time: 0s Serial Phase #:93 [MIG19C] Files:1 Time: 1s Restart Phase #:94 [MIG19C] Files:1 Time: 1s ******* Upgrading ODM, WK, EXF, RUL, XOQ ******* Serial Phase #:95 [MIG19C] Files:1 Time: 31s Restart Phase #:96 [MIG19C] Files:1 Time: 0s *********** Final Component scripts *********** Serial Phase #:97 [MIG19C] Files:1 Time: 2s ************* Final Upgrade scripts ************ Serial Phase #:98 [MIG19C] Files:1 Time: 24s ******************* Migration ****************** Serial Phase #:99 [MIG19C] Files:1 Time: 48s *** End PDB Application Upgrade Pre-Shutdown *** Serial Phase #:100 [MIG19C] Files:1 Time: 1s Serial Phase #:101 [MIG19C] Files:1 Time: 0s Serial Phase #:102 [MIG19C] Files:1 Time: 44s ***************** Post Upgrade ***************** Serial Phase #:103 [MIG19C] Files:1 Time: 38s **************** Summary report **************** Serial Phase #:104 [MIG19C] Files:1 Time: 2s *** End PDB Application Upgrade Post-Shutdown ** Serial Phase #:105 [MIG19C] Files:1 Time: 0s Serial Phase #:106 [MIG19C] Files:1 Time: 0s Serial Phase #:107 [MIG19C] Files:1 Time: 32s ------------------------------------------------------ Phases [0-107] End Time:[2019_03_21 22:25:49] ------------------------------------------------------ Grand Total Time: 2320s LOG FILES: (/u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/MIG19C/upgrade20190321214709/catupgrd*.log) Upgrade Summary Report Located in: /u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/MIG19C/upgrade20190321214709/upg_summary.log Grand Total Upgrade Time: [0d:0h:38m:40s]
Startup database after upgrade completed.
SQL> startup ORACLE instance started. Total System Global Area 1543500136 bytes Fixed Size 8896872 bytes Variable Size 939524096 bytes Database Buffers 587202560 bytes Redo Buffers 7876608 bytes Database mounted. Database opened.
SQL> @catuppst.sql Session altered. Session altered. Session altered. PL/SQL procedure successfully completed. Session altered. System altered. PL/SQL procedure successfully completed. Session altered. TIMESTAMP -------------------------------------------------------------------------------- COMP_TIMESTAMP DBRESTART 2019-03-21 23:05:38 DBUA_TIMESTAMP DBRESTART FINISHED 2019-03-21 23:05:38 DBUA_TIMESTAMP DBRESTART NONE 2019-03-21 23:05:38 TIMESTAMP -------------------------------------------------------------------------------- DBUA_TIMESTAMP CATUPPST STARTED 2019-03-21 23:05:38 TIMESTAMP -------------------------------------------------------------------------------- COMP_TIMESTAMP POSTUP_BGN 2019-03-21 23:05:38 DBUA_TIMESTAMP POSTUP_BGN FINISHED 2019-03-21 23:05:38 DBUA_TIMESTAMP POSTUP_BGN NONE 2019-03-21 23:05:38 TIMESTAMP -------------------------------------------------------------------------------- COMP_TIMESTAMP CATREQ_BGN 2019-03-21 23:05:38 DBUA_TIMESTAMP CATREQ_BGN FINISHED 2019-03-21 23:05:38 DBUA_TIMESTAMP CATREQ_BGN NONE 2019-03-21 23:05:38 PL/SQL procedure successfully completed. catrequtlmg: b_StatEvt = TRUE catrequtlmg: b_SelProps = FALSE catrequtlmg: b_UpgradeMode = FALSE catrequtlmg: b_InUtlMig = FALSE PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. TIMESTAMP -------------------------------------------------------------------------------- COMP_TIMESTAMP CATREQ_END 2019-03-21 23:05:39 DBUA_TIMESTAMP CATREQ_END FINISHED 2019-03-21 23:05:39 DBUA_TIMESTAMP CATREQ_END NONE 2019-03-21 23:05:39 catuppst: Dropping library DBMS_DDL_INTERNAL_LIB PL/SQL procedure successfully completed. catuppst: Dropping view _CURRENT_EDITION_OBJ_MIG PL/SQL procedure successfully completed. catuppst: Dropping view _ACTUAL_EDITION_OBJ_MIG PL/SQL procedure successfully completed. catuppst: Dropping view DBA_PART_KEY_COLUMNS_V$_MIG PL/SQL procedure successfully completed. catuppst: Dropping view DBA_SUBPART_KEY_COLUMNS_V$_MIG PL/SQL procedure successfully completed. catuppst: Dropping table OBJ$MIG catuppst: Dropping table USER$MIG catuppst: Dropping table COL$MIG catuppst: Dropping table CLU$MIG catuppst: Dropping table CON$MIG catuppst: Dropping table BOOTSTRAP$MIG catuppst: Dropping table TAB$MIG catuppst: Dropping table TS$MIG catuppst: Dropping table IND$MIG catuppst: Dropping table ICOL$MIG catuppst: Dropping table LOB$MIG catuppst: Dropping table COLTYPE$MIG catuppst: Dropping table SUBCOLTYPE$MIG catuppst: Dropping table NTAB$MIG catuppst: Dropping table REFCON$MIG catuppst: Dropping table OPQTYPE$MIG catuppst: Dropping table ICOLDEP$MIG catuppst: Dropping table VIEWTRCOL$MIG catuppst: Dropping table ATTRCOL$MIG catuppst: Dropping table TYPE_MISC$MIG catuppst: Dropping table LIBRARY$MIG catuppst: Dropping table ASSEMBLY$MIG catuppst: Dropping table TSQ$MIG catuppst: Dropping table FET$MIG PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. TIMESTAMP -------------------------------------------------------------------------------- COMP_TIMESTAMP POSTUP_END 2019-03-21 23:05:39 DBUA_TIMESTAMP POSTUP_END FINISHED 2019-03-21 23:05:39 DBUA_TIMESTAMP POSTUP_END NONE 2019-03-21 23:05:39 TIMESTAMP -------------------------------------------------------------------------------- COMP_TIMESTAMP CATUPPST 2019-03-21 23:05:39 DBUA_TIMESTAMP CATUPPST FINISHED 2019-03-21 23:05:39 DBUA_TIMESTAMP CATUPPST NONE 2019-03-21 23:05:39 Session altered.
Compile Invalid objects :
SQL> @utlrp.sql
Session altered.
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2019-03-21 23:07:30
DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC> objects in the database. Recompilation time is proportional to the
DOC> number of invalid objects in the database, so this command may take
DOC> a long time to execute on a database with a large number of invalid
DOC> objects.
DOC>
DOC> Use the following queries to track recompilation progress:
DOC>
DOC> 1. Query returning the number of invalid objects remaining. This
DOC> number should decrease with time.
DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC> 2. Query returning the number of objects compiled so far. This number
DOC> should increase with time.
DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC> This script automatically chooses serial or parallel recompilation
DOC> based on the number of CPUs available (parameter cpu_count) multiplied
DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC> On RAC, this number is added across all RAC nodes.
DOC>
DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC> recompilation. Jobs are created without instance affinity so that they
DOC> can migrate across RAC nodes. Use the following queries to verify
DOC> whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC> 1. Query showing jobs created by UTL_RECOMP
DOC> SELECT job_name FROM dba_scheduler_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC> 2. Query showing UTL_RECOMP jobs that are running
DOC> SELECT job_name FROM dba_scheduler_running_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#
PL/SQL procedure successfully completed.
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2019-03-21 23:18:13
DOC> The following query reports the number of invalid objects.
DOC>
DOC> If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#
OBJECTS WITH ERRORS
-------------------
0
DOC> The following query reports the number of exceptions caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC> Note: Typical compilation errors (due to coding errors) are not
DOC> logged into this table: they go into DBA_ERRORS instead.
DOC>#
ERRORS DURING RECOMPILATION
---------------------------
0
Function created.
PL/SQL procedure successfully completed.
Function dropped.
PL/SQL procedure successfully completed.
SQL> select count(*) from dba_objects where status='INVALID'; COUNT(*) ---------- 0
Set COMPATIBLE parameter :
SQL> show parameter compatible; NAME TYPE VALUE ----------------- -------- --------- compatible string 11.2.0.4.0 noncdb_compatible boolean FALSE SQL> ALTER SYSTEM SET COMPATIBLE = '19.0.0' SCOPE=SPFILE; System altered.
Restart the database :
SQL> shut immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 2516580184 bytes Fixed Size 8660824 bytes Variable Size 671088640 bytes Database Buffers 1828716544 bytes Redo Buffers 8114176 bytes Database mounted. Database opened. SQL> show parameter compatible; NAME TYPE VALUE ----------------- -------- --------- compatible string 19.0.0 noncdb_compatible boolean FALSE
Compile Fixed objects stats :
SQL> EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS; PL/SQL procedure successfully completed.
Post-Upgrade Steps :-
Upgrade Time Zone version :
Startup database in Upgrade mode.
SQL> startup upgrade
ORACLE instance started.
Total System Global Area 1543500136 bytes
Fixed Size 8896872 bytes
Variable Size 1040187392 bytes
Database Buffers 486539264 bytes
Redo Buffers 7876608 bytes
Database mounted.
Database opened.
SQL> SET SERVEROUTPUT ON
DECLARE
l_tz_version PLS_INTEGER;
BEGIN
l_tz_version := DBMS_DST.get_latest_timezone_version;
DBMS_OUTPUT.put_line('l_tz_version=' || l_tz_version);
DBMS_DST.begin_upgrade(l_tz_version);
END;
/SQL> 2 3 4 5 6 7 8 9
l_tz_version=32
An upgrade window has been successfully started.
PL/SQL procedure successfully completed.
Shutdown and startup in normal mode :
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1543500136 bytes
Fixed Size 8896872 bytes
Variable Size 1040187392 bytes
Database Buffers 486539264 bytes
Redo Buffers 7876608 bytes
Database mounted.
Database opened.
SQL> SET SERVEROUTPUT ON
DECLARE
l_failures PLS_INTEGER;
BEGIN
DBMS_DST.upgrade_database(l_failures);
DBMS_OUTPUT.put_line('DBMS_DST.upgrade_database : l_failures=' || l_failures);
DBMS_DST.end_upgrade(l_failures);
DBMS_OUTPUT.put_line('DBMS_DST.end_upgrade : l_failures=' || l_failures);
END;
/SQL> 2 3 4 5 6 7 8 9
Table list: "MDSYS"."SDO_DIAG_MESSAGES_TABLE"
Number of failures: 0
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_S"
Number of failures: 0
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_L"
Number of failures: 0
DBMS_DST.upgrade_database : l_failures=0
An upgrade window has been successfully ended.
DBMS_DST.end_upgrade : l_failures=0
PL/SQL procedure successfully completed.
Check Time zone version :
SQL> SELECT * FROM v$timezone_file; FILENAME VERSION CON_ID -------------------- ---------- ---------- timezlrg_32.dat 32 0 SQL>COLUMN property_name FORMAT A30 COLUMN property_value FORMAT A20 SELECT property_name, property_value FROM database_properties WHERE property_name LIKE 'DST_%' ORDER BY property_name;SQL> SQL> SQL> SQL> 2 3 4 PROPERTY_NAME PROPERTY_VALUE -------------------------- --------------- DST_PRIMARY_TT_VERSION 32 DST_SECONDARY_TT_VERSION 0 DST_UPGRADE_STATE NONE
Run postupgrade fixups :
SQL> @/u01/preupgrade/postupgrade_fixups.sql Session altered. PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. Package created. No errors. Package body created. PL/SQL procedure successfully completed. No errors. Package created. No errors. Package body created. No errors. Executing Oracle POST-Upgrade Fixup Script Auto-Generated by: Oracle Preupgrade Script Version: 19.0.0.0.0 Build: 1 Generated on: 2019-03-21 21:12:30 For Source Database: MIG19C Source Database Version: 11.2.0.4.0 For Upgrade to Version: 19.0.0.0.0 Preup Preupgrade Action Issue Is Number Preupgrade Check Name Remedied Further DBA Action ------ ------------------------ ---------- ---------------------------- 11. old_time_zones_exist YES None. 12. dir_symlinks YES None. 13. post_dictionary YES None. 14. post_fixed_objects NO Informational only. Further action is optional. 15. upg_by_std_upgrd NO Informational only. Further action is optional. The fixup scripts have been run and resolved what they can. However, there are still issues originally identified by the preupgrade that have not been remedied and are still present in the database. Depending on the severity of the specific issue, and the nature of the issue itself, that could mean that your database upgrade is not fully complete. To resolve the outstanding issues, start by reviewing the postupgrade_fixups.sql and searching it for the name of the failed CHECK NAME or Preupgrade Action Number listed above. There you will find the original corresponding diagnostic message from the preupgrade which explains in more detail what still needs to be done. PL/SQL procedure successfully completed. Session altered.
Check database name and version :
SQL> select name,version,open_mode from v$database,v$instance; NAME VERSION OPEN_MODE --------- ----------------- ---------- MIG19C 19.0.0.0.0 READ WRITE
Catch Me On:- Hariprasath Rajaram LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/ Facebook:https://www.facebook.com/HariPrasathdba FB Group:https://www.facebook.com/groups/894402327369506/ FB Page: https://www.facebook.com/dbahariprasath/? Twitter: https://twitter.com/hariprasathdba
