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.3 and 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.
1. 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/18.3/rdbms/admin/preupgrade.jar
SYNTAX:
java -jar /u01/app/oracle/product/18.3/rdbms/admin/preupgrade.jar
After run the above tool we will get below sql files.
-rw-r--r--. 1 root root 416K Dec 10 14:05 preupgrade_package.sql -rw-r--r--. 1 root root 83K Dec 10 14:05 preupgrade_messages.properties -rw-r--r--. 1 root root 7.8K Dec 10 14:05 preupgrade_driver.sql -rw-r--r--. 1 root root 15K Dec 10 14:05 parameters.properties -rw-r--r--. 1 root root 15K Dec 10 14:05 dbms_registry_extended.sql -rw-r--r--. 1 root root 49K Dec 10 14:05 components.properties -rw-r--r--. 1 root root 671K Dec 10 14:19 preupgrade.jar -rwxrwxr-x. 1 oracle oinstall 95 Feb 5 02:00 initmeta.sql -rwxr--r--. 1 root root 289K Feb 6 00:16 preupgrade_181_cbuild_6_lf.zip
[oracle@ram ~]$ export ORACLE_SID=orcldb [oracle@ram ~]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Wed Feb 6 00:20:57 2019 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> select instance from v$thread; INSTANCE ---------------- orcldb
[oracle@ram ~]$ java -jar /u01/app/oracle/product/18.3/rdbms/admin/preupgrade.jar TEXT TERMINAL Report generated by Oracle Database Pre-Upgrade Information Tool Version 18.0.0.0.0 Build: 6 on 2019-02-06T00:28:41 Upgrade-To version: 18.0.0.0.0 ======================================= Status of the database prior to upgrade ======================================= Database Name: ORCLDB 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 OLAP API [to be upgraded] VALID ============== BEFORE UPGRADE ============== REQUIRED ACTIONS ================ 1. (AUTOFIXUP) Empty the RECYCLEBIN immediately before database upgrade. The database contains 5 objects in the recycle bin. The recycle bin must be completely empty before database upgrade. RECOMMENDED ACTIONS =================== 2. Update NUMERIC INITIALIZATION PARAMETERS to meet estimated minimums. This action may be done now or when starting the database in upgrade mode using the 18.0.0.0.0 ORACLE HOME. Parameter Currently 18.0.0.0.0 minimum --------- --------- ------------------ processes 150 300 The database upgrade process requires certain initialization parameters to meet minimum values. The Oracle upgrade process itself has minimum values which may be higher and are marked with an asterisk. After upgrading, those asterisked parameter values may be reset if needed. 3. Remove the EM repository. - Copy the $ORACLE_HOME/rdbms/admin/emremove.sql script from the target 18.0.0.0.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. 4. 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. This step can be manually performed before the upgrade to reduce downtime. 5. Upgrade Oracle Application Express (APEX) manually before the database upgrade. The database contains APEX version 3.2.1.00.12. Upgrade APEX to at least version 5.1.3.00.05. Starting with Oracle Database Release 18, APEX is not upgraded automatically as part of the database upgrade. Refer to My Oracle Support Note 1088970.1 for information about APEX installation and upgrades. 6. 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 TRIM(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. 7. (AUTOFIXUP) Gather statistics on fixed objects prior the upgrade. None of the fixed object tables have had stats collected. Gathering statistics on fixed objects, if none have been gathered yet, is recommended prior to upgrading. For information on managing optimizer statistics, refer to the 11.2.0.4 Oracle Database Performance Tuning Guide. INFORMATION ONLY ================ 8. To help you keep track of your tablespace allocations, the following AUTOEXTEND tablespaces are expected to successfully EXTEND during the upgrade process. Min Size Tablespace Size For Upgrade ---------- ---------- ----------- SYSAUX 600 MB 710 MB SYSTEM 740 MB 1180 MB TEMP 59 MB 150 MB Minimum tablespace sizes for upgrade are estimates. ORACLE GENERATED FIXUP SCRIPT ============================= All of the issues in database ORCLDB which are identified above as BEFORE UPGRADE "(AUTOFIXUP)" can be resolved by executing the following SQL>@/u01/app/oracle/cfgtoollogs/orcldb/preupgrade/preupgrade_fixups.sql ============= AFTER UPGRADE ============= REQUIRED ACTIONS ================ None RECOMMENDED ACTIONS =================== 9. Upgrade the database time zone file using the DBMS_DST package. The database is using time zone file version 14 and the target 18.0.0.0.0 release ships with time zone file version 31. Oracle recommends upgrading to the desired (latest) version of the time zone file. For more information, refer to "Upgrading the Time Zone File and Timestamp with Time Zone Data" in the 18.0.0.0.0 Oracle Database Globalization Support Guide. 10. (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. 11. Gather statistics on fixed objects after the upgrade and when there is a representative workload on the system 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. For information on managing optimizer statistics, refer to the 11.2.0.4 Oracle Database Performance Tuning Guide. INFORMATION ONLY ================ 12. 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. ORACLE GENERATED FIXUP SCRIPT ============================= All of the issues in database ORCLDB which are identified above as AFTER UPGRADE "(AUTOFIXUP)" can be resolved by executing the following SQL>@/u01/app/oracle/cfgtoollogs/orcldb/preupgrade/postupgrade_fixups.sql ================== PREUPGRADE SUMMARY ================== /u01/app/oracle/cfgtoollogs/orcldb/preupgrade/preupgrade.log /u01/app/oracle/cfgtoollogs/orcldb/preupgrade/preupgrade_fixups.sql /u01/app/oracle/cfgtoollogs/orcldb/preupgrade/postupgrade_fixups.sql Execute fixup scripts as indicated below: Before upgrade log into the database and execute the preupgrade fixups @/u01/app/oracle/cfgtoollogs/orcldb/preupgrade/preupgrade_fixups.sql After the upgrade: Log into the database and execute the postupgrade fixups @/u01/app/oracle/cfgtoollogs/orcldb/preupgrade/postupgrade_fixups.sql Preupgrade complete: 2019-02-06T00:28:41
Change process parameter and reflect in spfile.
SQL> alter system set processes=300 scope=spfile; System altered. SQL> create pfile from spfile; File created.
Remove OLAP Catalog by running the 11.2.0.4.0 SQL script $ORACLE_HOME/olap/admin/catnoamd.sql script
If you have Apex do the Upgrade Oracle Application Express (APEX) manually before the database upgrade.
SQL> @catnoamd.sql Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. Synonym dropped. ............. ............. ............. ............. View dropped. View dropped. Type dropped. Type dropped. View dropped. Type dropped. Type dropped. Type dropped. Type dropped. PL/SQL procedure successfully completed. Role dropped. PL/SQL procedure successfully completed. 1 row deleted.
Run the preupgrade_fixup.sql
Shutdown the database:
Take full database backup. we should turn on the flashback database and create a Guaranteed Restore Point which can be used to rollback if upgrade process fails.
SQL>@/u01/app/oracle/cfgtoollogs/orcldb/preupgrade/preupgrade_fixups.sql Executing Oracle PRE-Upgrade Fixup Script Auto-Generated by: Oracle Preupgrade Script Version: 18.0.0.0.0 Build: 6 Generated on: 2019-02-06 00:28:36 For Source Database: ORCLDB Source Database Version: 11.2.0.4.0 For Upgrade to Version: 18.0.0.0.0 Preup Preupgrade Action Issue Is Number Preupgrade Check Name Remedied Further DBA Action ------ --------------------- -------- -------------------------- 1. purge_recyclebin YES None. 2. parameter_min_val NO Manual fixup recommended. 3. em_present NO Manual fixup recommended. 4. amd_exists YES None. 5. apex_manual_upgrade NO Manual fixup recommended. 6. trgowner_no_admndbtrg NO Informational only. Further action is optional. 7. pre_fixed_objects YES None. 8. tablespaces_info 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. SQL> shut immediate Database closed. Database dismounted. ORACLE instance shut down. SQL>
Copy the parameter and password file from the 11g home to the new 18c home.
[oracle@ram ~]$ . .bash_profile [oracle@ram ~]$ cd /u01/app/oracle/product/11.2.0/db_1/dbs/ [oracle@ram dbs]$ ls -lrth total 24K -rwxrwxr-x. 1 oracle oinstall 2.8K May 15 2009 init.ora -rwxrwxr-x. 1 oracle oinstall 1.5K Feb 5 00:51 orapworcldb -rwxrwxr-x. 1 oracle oinstall 24 Feb 5 00:51 lkORCLDB -rwxrwxr-x. 1 oracle oinstall 2.5K Feb 6 00:52 spfileorcldb.ora -rwxrwxr-x. 1 oracle oinstall 1.6K Feb 6 02:02 hc_orcldb.dat -rwxrwxr-x. 1 oracle oinstall 961 Feb 6 02:05 initorcldb.ora [oracle@ram dbs]$ cp initorcldb.ora /u01/app/oracle/product/18.3/db_1/dbs/ [oracle@ram dbs]$ cp spfileorcldb.ora /u01/app/oracle/product/18.3/db_1/dbs/ [oracle@ram dbs]$ cd /u01/app/oracle/product/18.3/db_1/dbs/ [oracle@ram dbs]$ export ORACLE_HOME=/u01/app/oracle/product/18.3/db_1/dbs [oracle@ram dbs]$ echo $ORACLE_HOME /u01/app/oracle/product/18.3/db_1 [oracle@ram dbs]$ orapwd file=orapwupgr password=oracle format=12 [oracle@ram dbs]$ ls -lrth total 16K -rwxrwxr-x. 1 oracle oinstall 3.1K May 14 2015 init.ora -rw-r--r--. 1 oracle oinstall 961 Feb 6 02:07 initorcldb.ora -rwxr-xr-x. 1 oracle oinstall 2.5K Feb 6 02:07 spfileorcldb.ora -rw-r-----. 1 oracle oinstall 1.5K Feb 6 02:16 orapwUPGR [oracle@ram dbs]$
Set new ORACLE HOME of 18c location:
export ORACLE_HOME=/u01/app/oracle/product/18.3/db_1/
start the database in upgrade mode.
[oracle@ram dbs]$ echo $ORACLE_HOME
/u01/app/oracle/product/18.3/db_1
[oracle@ram dbs]$ echo $PATH
/u01/app/oracle/product/18.3/db_1/bin:/u01/app/oracle/product/18.3/db_1/bin:/usr/sbin:/usr/local/bin:/u01/app/oracle/product/11.2.0/db_1/bin:/usr/sbin:/usr/lib64/qt-3.3/bin:/usr/local/sbin:/usr/sbin:/sbin:/usr/local/bin:/usr/bin:/bin:/root/bin:/home/oracle/bin
[oracle@ram dbs]$ sqlplus / as sysdba
SQL*Plus: Release 18.0.0.0.0 - Production on Wed Feb 6 03:14:07 2019
Version 18.3.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup upgrade
ORACLE instance started.
Total System Global Area 3305107504 bytes
Fixed Size 8663088 bytes
Variable Size 1912602624 bytes
Database Buffers 1375731712 bytes
Redo Buffers 8110080 bytes
Database mounted.
Database opened.
SQL> exit
Disconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0
Run catctl.pl
catctl.pl is introduce in Oracle Database 12c, Parallel Upgrade Utility replaces the SQL Upgrade Utility catupgrd.sql. With Parallel Upgrade Utility, we can run upgrade scripts and processes in parallel. Using this script will increase our server CPU capacity and reduce the upgrade time.
-n is used for parallelism (new feature on oracle 12c)
$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catctl.pl -d $ORACLE_HOME/rdbms/admin -l /home/oracle/ -n 4 catupgrd.sql
/u01/app/oracle/product/18.3/db_1/perl/bin/perl catctl.pl -n 4 -l /home/oracle/UPGR catupgrd.sql
[oracle@ram admin]$ /u01/app/oracle/product/18.3/db_1/perl/bin/perl catctl.pl -n 4 -l /home/oracle/UPGR catupgrd.sql Argument list for [catctl.pl] 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 = /home/oracle/UPGR Priority List Name L = 0 Upgrade Mode active M = 0 SQL Process Count n = 4 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: [18.0.0.0.0] STATUS: [Production] BUILD: [RDBMS_18.3.0.0.0DBRU_LINUX.X64_180627] /u01/app/oracle/product/18.3/db_1/rdbms/admin/orahome = [/u01/app/oracle/product/18.3/db_1] /u01/app/oracle/product/18.3/db_1/bin/orabasehome = [/u01/app/oracle/product/18.3/db_1] catctlGetOrabase = [/u01/app/oracle/product/18.3/db_1] Analyzing file /u01/app/oracle/product/18.3/db_1/rdbms/admin/catupgrd.sql Log file directory = [/home/oracle/UPGR] catcon::set_log_file_base_path: ALL catcon-related output will be written to [/home/oracle/UPGR/catupgrd_catcon_22121.lst] catcon::set_log_file_base_path: catcon: See [/home/oracle/UPGR/catupgrd*.log] files for output generated by scripts catcon::set_log_file_base_path: catcon: See [/home/oracle/UPGR/catupgrd_*.lst] files for spool files, if any Number of Cpus = 1 Database Name = orcldb DataBase Version = 18.0.0.0.0 Parallel SQL Process Count = 4 Components in [orcldb] Installed [APEX APS CATALOG CATJAVA CATPROC CONTEXT EM JAVAVM ORDIM OWM SDO XDB XML XOQ] Not Installed [DV MGW ODM OLS RAC WK] ------------------------------------------------------ Phases [0-108] Start Time:[2019_02_06 03:50:33] ------------------------------------------------------ *********** Executing Change Scripts *********** Serial Phase #:0 [orcldb] Files:1 Time: 55s *************** Catalog Core SQL *************** Serial Phase #:1 [orcldb] Files:5 Time: 26s Restart Phase #:2 [orcldb] Files:1 Time: 0s *********** Catalog Tables and Views *********** Parallel Phase #:3 [orcldb] Files:19 Time: 20s Restart Phase #:4 [orcldb] Files:1 Time: 0s ************* Catalog Final Scripts ************ Serial Phase #:5 [orcldb] Files:7 Time: 15s ***************** Catproc Start **************** Serial Phase #:6 [orcldb] Files:1 Time: 12s ***************** Catproc Types **************** Serial Phase #:7 [orcldb] Files:2 Time: 11s Restart Phase #:8 [orcldb] Files:1 Time: 0s **************** Catproc Tables **************** Parallel Phase #:9 [orcldb] Files:66 Time: 26s Restart Phase #:10 [orcldb] Files:1 Time: 0s ************* Catproc Package Specs ************ Serial Phase #:11 [orcldb] Files:1 Time: 71s Restart Phase #:12 [orcldb] Files:1 Time: 1s ************** Catproc Procedures ************** Parallel Phase #:13 [orcldb] Files:94 Time: 15s Restart Phase #:14 [orcldb] Files:1 Time: 1s Parallel Phase #:15 [orcldb] Files:117 Time: 26s Restart Phase #:16 [orcldb] Files:1 Time: 1s Serial Phase #:17 [orcldb] Files:17 Time: 2s Restart Phase #:18 [orcldb] Files:1 Time: 0s ***************** Catproc Views **************** Parallel Phase #:19 [orcldb] Files:32 Time: 24s Restart Phase #:20 [orcldb] Files:1 Time: 0s Serial Phase #:21 [orcldb] Files:3 Time: 7s Restart Phase #:22 [orcldb] Files:1 Time: 0s Parallel Phase #:23 [orcldb] Files:24 Time: 132s Restart Phase #:24 [orcldb] Files:1 Time: 0s Parallel Phase #:25 [orcldb] Files:12 Time: 78s Restart Phase #:26 [orcldb] Files:1 Time: 1s Serial Phase #:27 [orcldb] Files:1 Time: 0s Serial Phase #:28 [orcldb] Files:3 Time: 1s Serial Phase #:29 [orcldb] Files:1 Time: 0s Restart Phase #:30 [orcldb] Files:1 Time: 1s *************** Catproc CDB Views ************** Serial Phase #:31 [orcldb] Files:1 Time: 0s Restart Phase #:32 [orcldb] Files:1 Time: 0s Serial Phase #:34 [orcldb] Files:1 Time: 0s ***************** Catproc PLBs ***************** Serial Phase #:35 [orcldb] Files:288 Time: 81s Serial Phase #:36 [orcldb] Files:1 Time: 0s Restart Phase #:37 [orcldb] Files:1 Time: 0s Serial Phase #:38 [orcldb] Files:2 Time: 4s Restart Phase #:39 [orcldb] Files:1 Time: 0s *************** Catproc DataPump *************** Serial Phase #:40 [orcldb] Files:3 Time: 38s Restart Phase #:41 [orcldb] Files:1 Time: 0s ****************** Catproc SQL ***************** Parallel Phase #:42 [orcldb] Files:13 Time: 82s Restart Phase #:43 [orcldb] Files:1 Time: 0s Parallel Phase #:44 [orcldb] Files:11 Time: 8s Restart Phase #:45 [orcldb] Files:1 Time: 1s Parallel Phase #:46 [orcldb] Files:3 Time: 0s Restart Phase #:47 [orcldb] Files:1 Time: 1s ************* Final Catproc scripts ************ Serial Phase #:48 [orcldb] Files:1 Time: 2s Restart Phase #:49 [orcldb] Files:1 Time: 0s ************** Final RDBMS scripts ************* Serial Phase #:50 [orcldb] Files:1 Time: 10s ************ Upgrade Component Start *********** Serial Phase #:51 [orcldb] Files:1 Time: 0s Restart Phase #:52 [orcldb] Files:1 Time: 0s ********** Upgrading Java and non-Java ********* Serial Phase #:53 [orcldb] Files:2 Time: 1s ***************** Upgrading XDB **************** Restart Phase #:54 [orcldb] Files:1 Time: 0s Serial Phase #:56 [orcldb] Files:3 Time: 15s Serial Phase #:57 [orcldb] Files:3 Time: 4s Parallel Phase #:58 [orcldb] Files:9 Time: 3s Parallel Phase #:59 [orcldb] Files:25 Time: 5s Serial Phase #:60 [orcldb] Files:4 Time: 4s Serial Phase #:61 [orcldb] Files:1 Time: 0s Serial Phase #:62 [orcldb] Files:31 Time: 2s Serial Phase #:63 [orcldb] Files:1 Time: 0s Parallel Phase #:64 [orcldb] Files:6 Time: 3s Serial Phase #:65 [orcldb] Files:2 Time: 25s Serial Phase #:66 [orcldb] Files:3 Time: 56s **************** Upgrading ORDIM *************** Restart Phase #:67 [orcldb] Files:1 Time: 0s Serial Phase #:69 [orcldb] Files:1 Time: 2s Parallel Phase #:70 [orcldb] Files:2 Time: 42s Serial Phase #:71 [orcldb] Files:1 Time: 41s Restart Phase #:72 [orcldb] Files:1 Time: 0s Parallel Phase #:73 [orcldb] Files:2 Time: 8s Serial Phase #:74 [orcldb] Files:2 Time: 1s ***************** Upgrading SDO **************** Restart Phase #:75 [orcldb] Files:1 Time: 0s Serial Phase #:77 [orcldb] Files:1 Time: 27s Serial Phase #:78 [orcldb] Files:1 Time: 2s Restart Phase #:79 [orcldb] Files:1 Time: 0s Serial Phase #:80 [orcldb] Files:1 Time: 14s Restart Phase #:81 [orcldb] Files:1 Time: 0s Parallel Phase #:82 [orcldb] Files:3 Time: 56s Restart Phase #:83 [orcldb] Files:1 Time: 0s Serial Phase #:84 [orcldb] Files:1 Time: 4s Restart Phase #:85 [orcldb] Files:1 Time: 0s Serial Phase #:86 [orcldb] Files:1 Time: 4s Restart Phase #:87 [orcldb] Files:1 Time: 1s Parallel Phase #:88 [orcldb] Files:4 Time: 80s Restart Phase #:89 [orcldb] Files:1 Time: 0s Serial Phase #:90 [orcldb] Files:1 Time: 1s Restart Phase #:91 [orcldb] Files:1 Time: 0s Serial Phase #:92 [orcldb] Files:2 Time: 6s Restart Phase #:93 [orcldb] Files:1 Time: 0s Serial Phase #:94 [orcldb] Files:1 Time: 0s Restart Phase #:95 [orcldb] Files:1 Time: 0s ******* Upgrading ODM, WK, EXF, RUL, XOQ ******* Serial Phase #:96 [orcldb] Files:1 Time: 16s Restart Phase #:97 [orcldb] Files:1 Time: 1s *********** Final Component scripts *********** Serial Phase #:98 [orcldb] Files:1 Time: 1s ************* Final Upgrade scripts ************ Serial Phase #:99 [orcldb] Files:1 Time: 157s ******************* Migration ****************** Serial Phase #:100 [orcldb] Files:1 Time: 41s *** End PDB Application Upgrade Pre-Shutdown *** Serial Phase #:101 [orcldb] Files:1 Time: 0s Serial Phase #:102 [orcldb] Files:1 Time: 0s Serial Phase #:103 [orcldb] Files:1 Time: 60s ***************** Post Upgrade ***************** Serial Phase #:104 [orcldb] Files:1 Time: 94s **************** Summary report **************** Serial Phase #:105 [orcldb] Files:1 Time: 0s *** End PDB Application Upgrade Post-Shutdown ** Serial Phase #:106 [orcldb] Files:1 Time: 0s Serial Phase #:107 [orcldb] Files:1 Time: 0s Serial Phase #:108 [orcldb] Files:1 Time: 34s ------------------------------------------------------ Phases [0-108] End Time:[2019_02_06 04:16:37] ------------------------------------------------------ Grand Total Time: 1565s LOG FILES: (/home/oracle/UPGR/catupgrd*.log) Upgrade Summary Report Located in: /home/oracle/UPGR/upg_summary.log Grand Total Upgrade Time: [0d:0h:26m:5s]
Execute Post-Upgrade Status Tool, utlu112s.sql
[oracle@test ]$ cd $ORACLE_HOME/rdbms/admin SQL> @utlu112s.sql Oracle Database Release 18 Post-Upgrade Status Tool 02-06-2019 23:16:2 Component Current Full Elapsed Time Name Status Version HH:MM:SS Oracle Server UPGRADED 18.3.0.0.0 00:11:38 JServer JAVA Virtual Machine VALID 18.3.0.0.0 00:04:25 Oracle XDK UPGRADED 18.3.0.0.0 00:00:59 Oracle Database Java Packages UPGRADED 18.3.0.0.0 00:00:10 OLAP Analytic Workspace UPGRADED 18.3.0.0.0 00:00:14 Oracle Label Security UPGRADED 18.3.0.0.0 00:00:06 Oracle Database Vault UPGRADED 18.3.0.0.0 00:00:16 Oracle Text UPGRADED 18.3.0.0.0 00:00:33 Oracle Workspace Manager UPGRADED 18.3.0.0.0 00:00:31 Oracle Real Application Clusters UPGRADED 18.3.0.0.0 00:00:00 Oracle XML Database UPGRADED 18.3.0.0.0 00:01:13 Oracle Multimedia UPGRADED 18.3.0.0.0 00:01:36 Spatial UPGRADED 18.3.0.0.0 00:02:52 Oracle OLAP API UPGRADED 18.3.0.0.0 00:00:10 Upgrade Datapatch 00:01:10 Final Actions 00:03:46 Post Upgrade 00:00:13 Post Upgrade Datapatch 00:00:15 Total Upgrade Time: 00:29:10 Database time zone version is 18. It is older than current release time zone version 31. Time zone upgrade is needed using the DBMS_DST package. Summary Report File = /home/oracle//upg_summary.log
SQL> @catuppst.sql SQL> Rem SQL> Rem $Header: rdbms/admin/catuppst.sql /main/60 2017/08/03 17:44:03 wesmith Exp $ SQL> Rem SQL> Rem catuppst.sql SQL> Rem SQL> Rem Copyright (c) 2006, 2017, Oracle and/or its affiliates. SQL> Rem All rights reserved. SQL> Rem SQL> Rem NAME SQL> Rem catuppst.sql - CATalog UPgrade PoST-upgrade actions SQL> Rem SQL> Rem DESCRIPTION SQL> Rem This post-upgrade script performs remaining upgrade actions that SQL> Rem do not require that the database be open in UPGRADE mode. SQL> Rem Automatically apply the latest PSU. SQL> Rem SQL> Rem NOTES SQL> Rem You must be connected AS SYSDBA to run this script. SQL> Rem SQL> Rem BEGIN SQL_FILE_METADATA SQL> Rem SQL_SOURCE_FILE: rdbms/admin/catuppst.sql SQL> Rem SQL_SHIPPED_FILE: rdbms/admin/catuppst.sql SQL> Rem SQL_PHASE: UPGRADE SQL> Rem SQL_STARTUP_MODE: UPGRADE SQL> Rem SQL_IGNORABLE_ERRORS: NONE SQL> Rem SQL_CALLING_FILE: rdbms/admin/catupgrd.sql SQL> Rem END SQL_FILE_METADATA SQL> Rem
Execute utlrp.sql script to compile invalid objects.
SQL> @utlrp.sql
Session altered.
TIMESTAMP
-----------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2019-02-06 23:24:56
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-02-06 20:31:50
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 = '18.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 18.0.0 noncdb_compatible boolean FALSE
Compile Fixed objects stats :
SQL> EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS; PL/SQL procedure successfully completed.
Upgrade Time Zone version :
Startup database in Upgrade mode.
SQL> startup upgrade 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.
Start the upgrade window
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> l_tz_version=31 An upgrade window has been successfully started. PL/SQL procedure successfully completed.
Shutdown the database and startup in normal mode.
SQL> SHUTDOWN 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.
Upgrade Timezone version :
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: "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 Table list: "DVSYS"."SIMULATION_LOG$" Number of failures: 0 Table list: "DVSYS"."AUDIT_TRAIL$" Number of failures: 0 Table list: "APEX_040200"."WWV_FLOW_FEEDBACK" Number of failures: 0 Table list: "APEX_040200"."WWV_FLOW_FEEDBACK_FOLLOWUP" Number of failures: 0 Table list: "APEX_040200"."WWV_FLOW_DEBUG_MESSAGES" Number of failures: 0 Table list: "APEX_040200"."WWV_FLOW_DEBUG_MESSAGES2" Number of failures: 0 Table list: "APEX_040200"."WWV_FLOW_WORKSHEET_NOTIFY" Number of failures: 0 Table list: "IX"."AQ$_STREAMS_QUEUE_TABLE_S" Number of failures: 0 Table list: "IX"."AQ$_ORDERS_QUEUETABLE_S" Number of failures: 0 Table list: "IX"."AQ$_STREAMS_QUEUE_TABLE_L" Number of failures: 0 Table list: "IX"."AQ$_ORDERS_QUEUETABLE_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 file version :
SQL> SELECT * FROM v$timezone_file; FILENAME VERSION CON_ID -------------------- ---------- ------- timezlrg_14.dat 14 0 SQL> select TZ_VERSION from registry$database; TZ_VERSION ---------- 14 If they differ after an upgrade then updating registry$database can be done by SQL> update registry$database set TZ_VERSION = (select version FROM v$timezone_file); 1 row updated. SQL> commit; Commit complete. SQL> select TZ_VERSION from registry$database; TZ_VERSION ---------- 31
Run “postupgrade_fixups.sql” to check post upgrade status.
SQL>@/u01/app/oracle/cfgtoollogs/orcldb/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: 18.0.0.0.0 Build: 1
Generated on: 2019-02-06 21:36:48
For Source Database: ORCLDB
Source Database Version: 11.2.0.4.0
For Upgrade to Version: 18.0.0.0.0
Preup Preupgrade
Action Issue Is
Number Preupgrade Check Name Remedied Further DBA
Action
------ ------------------------ ---------- ------------------
4. depend_usr_tables YES None.
5. old_time_zones_exist YES None.
6. post_dictionary YES None.
7. post_fixed_objects NO Informational only.
Further action is optional.
The fixup scripts have been run and resovled 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 version and status.
SQL> select name,version,open_mode from v$database,v$instance; NAME VERSION OPEN_MODE --------- ----------------- -------------------- ORCLDB 18.0.0.0.0 READ WRITE
Reference : Oracle DB 18c – Complete Checklist for Manual Upgrades to Non-CDB Oracle Database 18c (Doc ID 2418045.1)
Catch Me On:- Hariprasath Rajaram Telegram:https://t.me/joinchat/I_f4DkeGfZuxgMIoJSpQZg LinkedIn:https://www.linkedin.com/in/hariprasathdba 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