Oracle 18c Database Upgrade From 12.1.0.2 to 18.3.0.0.0 Using Manual Method

Oracle 18c Database Manual Upgrade From 12.1.0.2 to 18.3.0.0.0 :-

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.

 Pre-upgrade checks :

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.
/u02/app/oracle/product/18.3.0/db_1/rdbms/admin/preupgrade.jar

[oracle@test db_1]$ mkdir /u01/preupgrade

[oracle@test db_1]$ export ORACLE_SID=orcl
[oracle@test db_1]$ export ORACLE_HOME=/u01/app/oracle/product/12.1.0/db_1
[oracle@test db_1]$ /u01/app/oracle/product/12.1.0/db_1/jdk/bin/java -jar /u02/app/oracle/product/18.3.0/db_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_fixups.sql

After the upgrade:

Log into the database and execute the postupgrade fixups
@/u01/postupgrade_fixups.sql

Preupgrade complete: 2019-02-06T04:42:43

After run the above tool we will get below sql files.

[oracle@test db_1]$ cd /u01/preupgrade
[oracle@test u01/preupgrade]$ ls -lrt

total 660
-rw-r--r-- 1 oracle oinstall  14846 Feb  8 04:42 dbms_registry_extended.sql
-rw-r--r-- 1 oracle oinstall 7963 Feb 6 04:42 preupgrade_driver.sql
-rw-r--r-- 1 oracle oinstall 422048 Feb 6 04:42 preupgrade_package.sql
-rw-r--r-- 1 oracle oinstall 14383 Feb 6 04:42 parameters.properties
-rw-r--r-- 1 oracle oinstall 83854 Feb 6 04:42 preupgrade_messages.properties
-rw-r--r-- 1 oracle oinstall 50172 Feb 6 04:42 components.properties
drwxr-xr-x 3 oracle oinstall 4096 Feb 6 04:42 upgrade
-rw-r--r-- 1 oracle oinstall 2 Feb 6 04:42 checksBuffer.tmp
-rw-r--r-- 1 oracle oinstall 9181 Feb 6 04:42 preupgrade_fixups.sql
-rw-r--r-- 1 oracle oinstall 8875 Feb 6 04:42 postupgrade_fixups.sql
-rw-r--r-- 1 oracle oinstall 8120 Feb 6 04:42 preupgrade.log

 Run the preupgrade_fixup.sql

[oracle@test ]$ export ORACLE_SID=orcl

[oracle@test ]$ export ORACLE_HOME=/u01/app/oracle/product/12.1.0/db_1

[oracle@test ]$ export PATH=$ORACLE_HOME/bin:$PATH

[oracle@test ]$ cd /u01/preupgrade

[oracle@test ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Feb 6 04:56:54 2019

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, OLAP, Advanced Analytics and Real Application Testing options

SQL> @preupgrade_fixups.sql
Executing Oracle PRE-Upgrade Fixup Script

Auto-Generated by: Oracle Preupgrade Script
Version: 18.0.0.0.0 Build: 1
Generated on: 2019-02-06 21:36:38

For Source Database: ORCL
Source Database Version: 12.1.0.2.0
For Upgrade to Version: 18.0.0.0.0

Preup Preupgrade
Action Issue Is
Number Preupgrade Check Name Remedied Further DBA Action
------ ------------------------ ---------- --------------------------------
1. apex_manual_upgrade NO Manual fixup recommended.
2. mv_refresh NO Informational only.
Further action is optional.
3. 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.

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
----------
18

SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
 FROM DATABASE_PROPERTIES
 WHERE PROPERTY_NAME LIKE 'DST_%'
 ORDER BY PROPERTY_NAME;

PROPERTY_NAME                VALUE
--------------------------- ----------

DST_PRIMARY_TT_VERSION       18
DST_SECONDARY_TT_VERSION     0
DST_UPGRADE_STATE            NONE

Upgrade Database from 12.1.0.2 to 18c :-

Shutdown the 12c database:

[oracle@test ~]$ sqlplus / as sysdba 

SQL*Plus: Release 12.1.0.2.0 Production on Wed Feb 6 04:56:54 2019 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, OLAP, Advanced Analytics and Real Application Testing options

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

Copy the parameter and password file from the 12c home to the new 18c home.

[oracle@test db_1]$ cp /u01/app/oracle/product/12.1.0/db_1/dbs/spfileorcl.ora /u02/app/oracle/product/18.3.0/db_1/dbs/
[oracle@test db_1]$ cp /u01/app/oracle/product/12.1.0/db_1/dbs/orapworcl /u02/app/oracle/product/18.3.0/db_1/dbs/

Set new ORACLE_HOME for 18c location and startup database in upgrade mode :

[oracle@test ]$ export ORACLE_SID=orcl
[oracle@test ]$ export ORACLE_HOME=/u02/app/oracle/product/18.3.0/db_1/
[oracle@test ]$ export PATH=/u02/app/oracle/product/18.3.0/db_1/bin
[oracle@test db_1]$ sqlplus / as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Wed Feb 6 22:02:24 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 2516580184 bytes
Fixed Size 8660824 bytes
Variable Size 671088640 bytes
Database Buffers 1828716544 bytes
Redo Buffers 8114176 bytes
Database mounted.
Database opened.

Run catcl.pl  script:

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)

You can run the dbupgrade using either of the following commands.

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catctl.pl -d $ORACLE_HOME/rdbms/admin -l /home/oracle/ -n 4 catupgrd.sql

$ORACLE_HOME/bin/dbupgrade

[oracle@test db_1]$ $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catctl.pl -d $ORACLE_HOME/rdbms/admin -l /home/oracle/ -n 4 catupgrd.sql

Argument list for [/u02/app/oracle/product/18.3.0/db_1//rdbms/admin/catctl.pl]
Run in c = 0
Do not run in C = 0
Input Directory d = /u02/app/oracle/product/18.3.0/db_1//rdbms/admin
Echo OFF e = 1
Simulate E = 0
Forced cleanup F = 0
Log Id i = 0
Child Process I = 0
Log Dir l = /home/oracle/
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]

/u02/app/oracle/product/18.3.0/db_1/rdbms/admin/orahome = [/u02/app/oracle/product/18.3.0/db_1/]
/u02/app/oracle/product/18.3.0/db_1//bin/orabasehome = [/u02/app/oracle/product/18.3.0/db_1/]
catctlGetOrabase = [/u02/app/oracle/product/18.3.0/db_1/]

Analyzing file /u02/app/oracle/product/18.3.0/db_1//rdbms/admin/catupgrd.sql

Log file directory = [/home/oracle/]

catcon::set_log_file_base_path: ALL catcon-related output will be written to [/home/oracle/catupgrd_catcon_37104.lst]

catcon::set_log_file_base_path: catcon: See [/home/oracle/catupgrd*.log] files for output generated by scripts

catcon::set_log_file_base_path: catcon: See [/home/oracle/catupgrd_*.lst] files for spool files, if any

Number of Cpus = 1
Database Name = orcl
DataBase Version = 12.1.0.2.0
Parallel SQL Process Count = 4
Components in [orcl]
Installed [APEX APS CATALOG CATJAVA CATPROC CONTEXT DV JAVAVM OLS ORDIM OWM SDO XDB XML XOQ]
Not Installed [EM MGW ODM RAC WK]

------------------------------------------------------
Phases [0-108] Start Time:[2019_02_06 22:05:38]
------------------------------------------------------
*********** Executing Change Scripts ***********
Serial Phase #:0 [orcl] Files:1 Time: 45s
*************** Catalog Core SQL ***************
Serial Phase #:1 [orcl] Files:5 Time: 33s
Restart Phase #:2 [orcl] Files:1 Time: 0s
*********** Catalog Tables and Views ***********
Parallel Phase #:3 [orcl] Files:19 Time: 19s
Restart Phase #:4 [orcl] Files:1 Time: 0s
************* Catalog Final Scripts ************
Serial Phase #:5 [orcl] Files:7 Time: 10s
***************** Catproc Start ****************
Serial Phase #:6 [orcl] Files:1 Time: 10s
***************** Catproc Types ****************
Serial Phase #:7 [orcl] Files:2 Time: 9s
Restart Phase #:8 [orcl] Files:1 Time: 0s
**************** Catproc Tables ****************
Parallel Phase #:9 [orcl] Files:66 Time: 31s
Restart Phase #:10 [orcl] Files:1 Time: 1s
************* Catproc Package Specs ************
Serial Phase #:11 [orcl] Files:1 Time: 53s
Restart Phase #:12 [orcl] Files:1 Time: 0s
************** Catproc Procedures **************
Parallel Phase #:13 [orcl] Files:94 Time: 14s
Restart Phase #:14 [orcl] Files:1 Time: 0s
Parallel Phase #:15 [orcl] Files:117 Time: 26s
Restart Phase #:16 [orcl] Files:1 Time: 0s
Serial Phase #:17 [orcl] Files:17 Time: 3s
Restart Phase #:18 [orcl] Files:1 Time: 0s
***************** Catproc Views ****************
Parallel Phase #:19 [orcl] Files:32 Time: 24s
Restart Phase #:20 [orcl] Files:1 Time: 1s
Serial Phase #:21 [orcl] Files:3 Time: 6s
Restart Phase #:22 [orcl] Files:1 Time: 0s
Parallel Phase #:23 [orcl] Files:24 Time: 137s
Restart Phase #:24 [orcl] Files:1 Time: 0s
Parallel Phase #:25 [orcl] Files:12 Time: 79s
Restart Phase #:26 [orcl] Files:1 Time: 0s
Serial Phase #:27 [orcl] Files:1 Time: 0s
Serial Phase #:28 [orcl] Files:3 Time: 3s
Serial Phase #:29 [orcl] Files:1 Time: 0s
Restart Phase #:30 [orcl] Files:1 Time: 0s
*************** Catproc CDB Views **************
Serial Phase #:31 [orcl] Files:1 Time: 0s
Restart Phase #:32 [orcl] Files:1 Time: 1s
Serial Phase #:34 [orcl] Files:1 Time: 0s
***************** Catproc PLBs *****************
Serial Phase #:35 [orcl] Files:288 Time: 19s
Serial Phase #:36 [orcl] Files:1 Time: 0s
Restart Phase #:37 [orcl] Files:1 Time: 1s
Serial Phase #:38 [orcl] Files:2 Time: 3s
Restart Phase #:39 [orcl] Files:1 Time: 0s
*************** Catproc DataPump ***************
Serial Phase #:40 [orcl] Files:3 Time: 45s
Restart Phase #:41 [orcl] Files:1 Time: 0s
****************** Catproc SQL *****************
Parallel Phase #:42 [orcl] Files:13 Time: 91s
Restart Phase #:43 [orcl] Files:1 Time: 0s
Parallel Phase #:44 [orcl] Files:11 Time: 13s
Restart Phase #:45 [orcl] Files:1 Time: 0s
Parallel Phase #:46 [orcl] Files:3 Time: 1s
Restart Phase #:47 [orcl] Files:1 Time: 1s
************* Final Catproc scripts ************
Serial Phase #:48 [orcl] Files:1 Time: 3s
Restart Phase #:49 [orcl] Files:1 Time: 0s
************** Final RDBMS scripts *************
Serial Phase #:50 [orcl] Files:1 Time: 19s
************ Upgrade Component Start ***********
Serial Phase #:51 [orcl] Files:1 Time: 0s
Restart Phase #:52 [orcl] Files:1 Time: 0s
********** Upgrading Java and non-Java *********
Serial Phase #:53 [orcl] Files:2 Time: 439s
***************** Upgrading XDB ****************
Restart Phase #:54 [orcl] Files:1 Time: 0s
Serial Phase #:56 [orcl] Files:3 Time: 8s
Serial Phase #:57 [orcl] Files:3 Time: 4s
Parallel Phase #:58 [orcl] Files:9 Time: 3s
Parallel Phase #:59 [orcl] Files:25 Time: 4s
Serial Phase #:60 [orcl] Files:4 Time: 5s
Serial Phase #:61 [orcl] Files:1 Time: 0s
Serial Phase #:62 [orcl] Files:31 Time: 3s
Serial Phase #:63 [orcl] Files:1 Time: 0s
Parallel Phase #:64 [orcl] Files:6 Time: 3s
Serial Phase #:65 [orcl] Files:2 Time: 13s
Serial Phase #:66 [orcl] Files:3 Time: 31s
**************** Upgrading ORDIM ***************
Restart Phase #:67 [orcl] Files:1 Time: 1s
Serial Phase #:69 [orcl] Files:1 Time: 0s
Parallel Phase #:70 [orcl] Files:2 Time: 44s
Serial Phase #:71 [orcl] Files:1 Time: 44s
Restart Phase #:72 [orcl] Files:1 Time: 0s
Parallel Phase #:73 [orcl] Files:2 Time: 8s
Serial Phase #:74 [orcl] Files:2 Time: 1s
***************** Upgrading SDO ****************
Restart Phase #:75 [orcl] Files:1 Time: 0s
Serial Phase #:77 [orcl] Files:1 Time: 29s
Serial Phase #:78 [orcl] Files:1 Time: 1s
Restart Phase #:79 [orcl] Files:1 Time: 0s
Serial Phase #:80 [orcl] Files:1 Time: 15s
Restart Phase #:81 [orcl] Files:1 Time: 0s
Parallel Phase #:82 [orcl] Files:3 Time: 53s
Restart Phase #:83 [orcl] Files:1 Time: 0s
Serial Phase #:84 [orcl] Files:1 Time: 3s
Restart Phase #:85 [orcl] Files:1 Time: 0s
Serial Phase #:86 [orcl] Files:1 Time: 5s
Restart Phase #:87 [orcl] Files:1 Time: 1s
Parallel Phase #:88 [orcl] Files:4 Time: 60s
Restart Phase #:89 [orcl] Files:1 Time: 0s
Serial Phase #:90 [orcl] Files:1 Time: 0s
Restart Phase #:91 [orcl] Files:1 Time: 1s
Serial Phase #:92 [orcl] Files:2 Time: 5s
Restart Phase #:93 [orcl] Files:1 Time: 0s
Serial Phase #:94 [orcl] Files:1 Time: 1s
Restart Phase #:95 [orcl] Files:1 Time: 0s
******* Upgrading ODM, WK, EXF, RUL, XOQ *******
Serial Phase #:96 [orcl] Files:1 Time: 11s
Restart Phase #:97 [orcl] Files:1 Time: 0s
*********** Final Component scripts ***********
Serial Phase #:98 [orcl] Files:1 Time: 1s
************* Final Upgrade scripts ************
Serial Phase #:99 [orcl] Files:1 Time: 189s
******************* Migration ******************
Serial Phase #:100 [orcl] Files:1 Time: 38s
*** End PDB Application Upgrade Pre-Shutdown ***
Serial Phase #:101 [orcl] Files:1 Time: 0s
Serial Phase #:102 [orcl] Files:1 Time: 0s
Serial Phase #:103 [orcl] Files:1 Time: 68s
***************** Post Upgrade *****************
Serial Phase #:104 [orcl] Files:1 Time: 13s
**************** Summary report ****************
Serial Phase #:105 [orcl] Files:1 Time: 1s
*** End PDB Application Upgrade Post-Shutdown **
Serial Phase #:106 [orcl] Files:1 Time: 0s
Serial Phase #:107 [orcl] Files:1 Time: 0s
Serial Phase #:108 [orcl] Files:1 Time: 28s

------------------------------------------------------
Phases [0-108] End Time:[2019_02_06 22:36:13]
------------------------------------------------------

Grand Total Time: 1836s

LOG FILES: (/home/oracle//catupgrd*.log)

Upgrade Summary Report Located in:
/home/oracle//upg_summary.log

Grand Total Upgrade Time: [0d:0h:30m:36s]

Execute Post-Upgrade Status Tool, utlu122s.sql.

[oracle@test ]$ cd $ORACLE_HOME/rdbms/admin

SQL> @utlu122s.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 23: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  12.1.0.2.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> 2 3 4 5 6 7 8 9
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_31.dat      31         0


SQL> select TZ_VERSION from registry$database;

TZ_VERSION
----------
        18

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

Reference: Steps to Upgrade Time Zone File and Timestamp with Time Zone Data

Post-upgrade checks:

Run “postupgrade_fixups.sql” to check post upgrade status.

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: 18.0.0.0.0 Build: 1
Generated on: 2019-02-06 21:36:48

For Source Database: ORCL
Source Database Version: 12.1.0.2.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
--------- ----------------- --------------------
ORCL      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

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *