Oracle 18c Database Upgrade From 11.2.0.3 to 18.3.0.0 Using Manual Method

Oracle 18c Database Manual Upgrade From 11.2.0.3 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 ~]$ /u01/app/oracle/product/11.2.0/dbhome_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/preupgrade_fixups.sql

After the upgrade:

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

Preupgrade complete: 2019-02-08T19:42:18

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

[oracle@test ~]$ cd /u01/preupgrade/
[oracle@test preupgrade]$ ls -lrt
total 648
drwxr-xr-x 3 oracle oinstall 4096 Feb 8 19:38  
drwxr-xr-x 3 oracle oinstall 4096 Feb 8 19:42 oracle
-rw-r--r-- 1 oracle oinstall 7963 Feb 8 19:42 preupgrade_driver.sql
-rw-r--r-- 1 oracle oinstall 14846 Feb 8 19:42 dbms_registry_extended.sql
-rw-r--r-- 1 oracle oinstall 422048 Feb 8 19:42 preupgrade_package.sql
-rw-r--r-- 1 oracle oinstall 83854 Feb 8 19:42 preupgrade_messages.properties
-rw-r--r-- 1 oracle oinstall 14383 Feb 8 19:42 parameters.properties
drwxr-xr-x 3 oracle oinstall 4096 Feb 8 19:42 upgrade
-rw-r--r-- 1 oracle oinstall 50172 Feb 8 19:42 components.properties
-rw-r--r-- 1 oracle oinstall 1 Feb 8 19:42 checksBuffer.tmp
-rw-r--r-- 1 oracle oinstall 14521 Feb 8 19:42 preupgrade_fixups.sql
-rw-r--r-- 1 oracle oinstall 9914 Feb 8 19:42 postupgrade_fixups.sql
-rw-r--r-- 1 oracle oinstall 11816 Feb 8 19:42 preupgrade.log

 Run the preupgrade_fixup.sql

[oracle@test preupgrade]$ export ORACLE_SID=orcl18c

[oracle@test preupgrade]$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1

[oracle@test preupgrade]$ export PATH=/u01/app/oracle/product/11.2.0/dbhome_1/bin:$PATH

[oracle@test ]$ cd /u01/preupgrade 

[oracle@test ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri Feb 8 19:48:36 2019

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @/u01/preupgrade/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-08 19:42:16

For Source Database: ORCL18C
Source Database Version: 11.2.0.3.0
For Upgrade to Version: 18.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 NO Informational only.
Further action is optional.
7. mv_refresh NO Informational only.
Further action is optional.
8. pre_fixed_objects YES None.
9. 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
-------
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.3 to 18c :-

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 18c home.

[oracle@test ]$ cp /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileorcl18c.ora /u02/app/oracle/product/18.3.0/db_1/dbs
[oracle@test ]$ cp /u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapworcl18c /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=orcl18c

[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/:$PATH
[oracle@test ~]$ sqlplus / as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Fri Feb 8 20:00:42 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 2365584808 bytes
Fixed Size 8660392 bytes
Variable Size 587202560 bytes
Database Buffers 1761607680 bytes
Redo Buffers 8114176 bytes
Database mounted.
Database opened.

Run the dbupgrade utility :

[oracle@test ~]$ $ORACLE_HOME/bin/dbupgrade -n 2 -l /u01/

-n - number of channels
-l - log file directory

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 = 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: [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 = [/u01/]

catcon::set_log_file_base_path: ALL catcon-related output will be written to [/tmp/cfgtoollogs/upgrade20190208200240/catupgrd_catcon_34323.lst]

catcon::set_log_file_base_path: catcon: See [/tmp/cfgtoollogs/upgrade20190208200240/catupgrd*.log] files for output generated by scripts

catcon::set_log_file_base_path: catcon: See [/tmp/cfgtoollogs/upgrade20190208200240/catupgrd_*.lst] files for spool files, if any


Number of Cpus = 1
Database Name = orcl18c
DataBase Version = 11.2.0.3.0
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/u02/app/oracle/product/18.3.0/db_1/cfgtoollogs/orcl18c/upgrade20190208200243/catupgrd_catcon_34323.lst]

catcon::set_log_file_base_path: catcon: See [/u02/app/oracle/product/18.3.0/db_1/cfgtoollogs/orcl18c/upgrade20190208200243/catupgrd*.log] files for output generated by scripts

catcon::set_log_file_base_path: catcon: See [/u02/app/oracle/product/18.3.0/db_1/cfgtoollogs/orcl18c/upgrade20190208200243/catupgrd_*.lst] files for spool files, if any


Log file directory = [/u02/app/oracle/product/18.3.0/db_1/cfgtoollogs/orcl18c/upgrade20190208200243]

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

------------------------------------------------------
Phases [0-108] End Time:[2019_02_08 20:33:25]
------------------------------------------------------

Grand Total Time: 1843s

LOG FILES: (/u02/app/oracle/product/18.3.0/db_1/cfgtoollogs/orcl18c/upgrade20190208200243/catupgrd*.log)

Upgrade Summary Report Located in:
/u02/app/oracle/product/18.3.0/db_1/cfgtoollogs/orcl18c/upgrade20190208200243/upg_summary.log

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

Execute Post-Upgrade Status Tool, utlu122s.sql.

SQL> @utlu122s.sql


Oracle Database Release 18 Post-Upgrade Status Tool 02-08-2019 20:44:3

Component Current Full Elapsed Time
Name Status Version HH:MM:SS

Oracle Server UPGRADED 18.3.0.0.0 00:11:54
JServer JAVA Virtual Machine VALID 18.3.0.0.0 00:03:27
Oracle XDK UPGRADED 18.3.0.0.0 00:00:25
Oracle Database Java Packages UPGRADED 18.3.0.0.0 00:00:10
OLAP Analytic Workspace UPGRADED 18.3.0.0.0 00:00:12
OLAP Catalog OPTION OFF 11.2.0.3.0 00:00:00
Oracle Text UPGRADED 18.3.0.0.0 00:00:35
Oracle Workspace Manager UPGRADED 18.3.0.0.0 00:00:46
Oracle Real Application Clusters OPTION OFF 18.3.0.0.0 00:00:00
Oracle XML Database UPGRADED 18.3.0.0.0 00:01:56
Oracle Multimedia UPGRADED 18.3.0.0.0 00:01:28
Spatial UPGRADED 18.3.0.0.0 00:02:46
Oracle OLAP API UPGRADED 18.3.0.0.0 00:00:13
Upgrade Datapatch 00:01:09
Final Actions 00:03:06
Post Upgrade 00:01:38
Post Upgrade Datapatch 00:00:15

Total Upgrade Time: 00:29:11

Database time zone version is 14. It is older than current release time
zone version 31. Time zone upgrade is needed using the DBMS_DST package.

Summary Report File = /u02/app/oracle/product/18.3.0/db_1/cfgtoollogs/orcl18c/upgrade20190208200243/upg_summary.log

 

20:44:37 SQL> @catuppst.sql
20:45:08 SQL> Rem
20:45:08 SQL> Rem $Header: rdbms/admin/catuppst.sql /main/60 2017/08/03 17:44:03 wesmith Exp $
20:45:08 SQL> Rem
20:45:08 SQL> Rem catuppst.sql
20:45:08 SQL> Rem
20:45:08 SQL> Rem Copyright (c) 2006, 2017, Oracle and/or its affiliates.
20:45:08 SQL> Rem All rights reserved.
20:45:08 SQL> Rem
20:45:08 SQL> Rem NAME
20:45:08 SQL> Rem catuppst.sql - CATalog UPgrade PoST-upgrade actions
20:45:08 SQL> Rem
20:45:08 SQL> Rem DESCRIPTION
20:45:08 SQL> Rem This post-upgrade script performs remaining upgrade actions that
20:45:08 SQL> Rem do not require that the database be open in UPGRADE mode.
20:45:08 SQL> Rem Automatically apply the latest PSU.
20:45:08 SQL> Rem
20:45:08 SQL> Rem NOTES
20:45:08 SQL> Rem You must be connected AS SYSDBA to run this script.
20:45:08 SQL> Rem
20:45:08 SQL> Rem BEGIN SQL_FILE_METADATA
20:45:08 SQL> Rem SQL_SOURCE_FILE: rdbms/admin/catuppst.sql
20:45:08 SQL> Rem SQL_SHIPPED_FILE: rdbms/admin/catuppst.sql
20:45:08 SQL> Rem SQL_PHASE: UPGRADE
20:45:08 SQL> Rem SQL_STARTUP_MODE: UPGRADE
20:45:08 SQL> Rem SQL_IGNORABLE_ERRORS: NONE
20:45:08 SQL> Rem SQL_CALLING_FILE: rdbms/admin/catupgrd.sql
20:45:08 SQL> Rem END SQL_FILE_METADATA
.
.
.
.

Elapsed: 00:00:00.00
20:45:09 SQL>
20:45:09 SQL> Rem Set errorlogging off
20:45:09 SQL> SET ERRORLOGGING OFF;
20:45:09 SQL>
20:45:09 SQL> Rem
20:45:09 SQL> Rem Set _ORACLE_SCRIPT to false
20:45:09 SQL> Rem
20:45:09 SQL> ALTER SESSION SET "_ORACLE_SCRIPT"=false;

Session altered.

Elapsed: 00:00:00.00
20:45:09 SQL>
20:45:09 SQL> Rem *********************************************************************
20:45:09 SQL> Rem END catuppst.sql
20:45:09 SQL> Rem *********************************************************************

Execute utlrp.sql script to compile  invalid objects.

SQL> @utlrp.sql
SQL> Rem
SQL> Rem $Header: rdbms/admin/utlrp.sql /main/23 2017/03/20 12:21:12 raeburns Exp $
SQL> Rem
SQL> Rem utlrp.sql
SQL> Rem
SQL> Rem Copyright (c) 1998, 2017, Oracle and/or its affiliates.
SQL> Rem All rights reserved.
SQL> Rem
SQL> Rem NAME
SQL> Rem utlrp.sql - Recompile invalid objects
SQL> Rem
SQL> Rem DESCRIPTION
SQL> Rem This script recompiles invalid objects in the database.
SQL> Rem
SQL> Rem When run as one of the last steps during upgrade or downgrade,
SQL> Rem this script will validate all remaining invalid objects. It will
SQL> Rem also run a component validation procedure for each component in
SQL> Rem the database. See the README notes for your current release and
SQL> Rem the Oracle Database Upgrade book for more information about
SQL> Rem using utlrp.sql
SQL> Rem
SQL> Rem Although invalid objects are automatically re-validated when used,
SQL> Rem it is useful to run this script after an upgrade or downgrade and
SQL> Rem after applying a patch. This minimizes latencies caused by
SQL> Rem on-demand recompilation. Oracle strongly recommends running this
SQL> Rem script after upgrades, downgrades and patches.
SQL> Rem
SQL> Rem NOTES
SQL> Rem * This script must be run using SQL*PLUS.
SQL> Rem * You must be connected AS SYSDBA to run this script.
SQL> Rem * There should be no other DDL on the database while running the
SQL> Rem script. Not following this recommendation may lead to deadlocks.
SQL> Rem
SQL> Rem BEGIN SQL_FILE_METADATA
SQL> Rem SQL_SOURCE_FILE: rdbms/admin/utlrp.sql
SQL> Rem SQL_SHIPPED_FILE: rdbms/admin/utlrp.sql
SQL> Rem SQL_PHASE: UTILITY
SQL> Rem SQL_STARTUP_MODE: NORMAL
SQL> Rem SQL_IGNORABLE_ERRORS: NONE
SQL> Rem SQL_CALLING_FILE: NONE
SQL> Rem END SQL_FILE_METADATA
SQL> Rem
SQL> Rem MODIFIED (MM/DD/YY)
SQL> Rem raeburns 03/09/17 - Bug 25616909: Use UTILITY for SQL_PHASE
SQL> Rem gviswana 06/26/03 - Switch default to parallel if appropriate
SQL> Rem gviswana 06/12/03 - Switch default back to serial
SQL> Rem gviswana 05/20/03 - 2814808: Automatic parallelism tuning
SQL> Rem rburns 04/28/03 - timestamps and serveroutput for diagnostics
SQL> Rem gviswana 04/13/03 - utlrcmp.sql load -> catproc
SQL> Rem gviswana 06/25/02 - Add documentation
SQL> Rem gviswana 11/12/01 - Use utl_recomp.recomp_serial
SQL> Rem rdecker 11/09/01 - ADD ALTER library support FOR bug 1952368
SQL> Rem rburns 11/12/01 - validate all components after compiles
SQL> Rem rburns 11/06/01 - fix invalid CATPROC call
SQL> Rem rburns 09/29/01 - use 9.2.0
SQL> Rem rburns 09/20/01 - add check for CATPROC valid
SQL> Rem rburns 07/06/01 - get version from instance view
SQL> Rem rburns 05/09/01 - fix for use with 8.1.x
SQL> Rem arithikr 04/17/01 - 1703753: recompile object type# 29,32,33
SQL> Rem skabraha 09/25/00 - validate is now a keyword
SQL> Rem kosinski 06/14/00 - Persistent parameters
SQL> Rem skabraha 06/05/00 - validate tables also
SQL> Rem jdavison 04/11/00 - Modify usage notes for 8.2 changes.
SQL> Rem rshaikh 09/22/99 - quote name for recompile
SQL> Rem ncramesh 08/04/98 - change for sqlplus
SQL> Rem usundara 06/03/98 - merge from 8.0.5
SQL> Rem usundara 04/29/98 - creation (split from utlirp.sql).
SQL> Rem Mark Ramacher (mramache) was the original
SQL> Rem author of this script.
SQL> Rem
SQL>
SQL> Rem ===========================================================================
SQL> Rem BEGIN utlrp.sql
SQL> Rem ===========================================================================
SQL>
SQL> @@utlprp.sql 0
SQL> Rem Copyright (c) 2003, 2017, Oracle and/or its affiliates.
SQL> Rem All rights reserved.
SQL> Rem
SQL> Rem NAME
SQL> Rem utlprp.sql - Recompile invalid objects in the database
SQL> Rem
SQL> Rem DESCRIPTION
SQL> Rem This script recompiles invalid objects in the database.
SQL> Rem
SQL> Rem This script is typically used to recompile invalid objects
SQL> Rem remaining at the end of a database upgrade or downgrade.
SQL> Rem
SQL> Rem Although invalid objects are automatically recompiled on demand,
SQL> Rem running this script ahead of time will reduce or eliminate
SQL> Rem latencies due to automatic recompilation.
SQL> Rem
SQL> Rem This script is a wrapper based on the UTL_RECOMP package.
SQL> Rem UTL_RECOMP provides a more general recompilation interface,
SQL> Rem including options to recompile objects in a single schema. Please
SQL> Rem see the documentation for package UTL_RECOMP for more details.
SQL> Rem
SQL> Rem INPUTS
SQL> Rem The degree of parallelism for recompilation can be controlled by
SQL> Rem providing a parameter to this script. If this parameter is 0 or
SQL> Rem NULL, UTL_RECOMP will automatically determine the appropriate
SQL> Rem level of parallelism based on Oracle parameters cpu_count and
SQL> Rem parallel_threads_per_cpu. If the parameter is 1, sequential
SQL> Rem recompilation is used. Please see the documentation for package
SQL> Rem UTL_RECOMP for more details.
SQL> Rem
SQL> Rem NOTES
SQL> Rem * You must be connected AS SYSDBA to run this script.
SQL> Rem * There should be no other DDL on the database while running the
SQL> Rem script. Not following this recommendation may lead to deadlocks.
SQL> Rem
SQL> Rem BEGIN SQL_FILE_METADATA
SQL> Rem SQL_SOURCE_FILE: rdbms/admin/utlprp.sql
SQL> Rem SQL_SHIPPED_FILE: rdbms/admin/utlprp.sql
SQL> Rem SQL_PHASE: UTILITY
SQL> Rem SQL_STARTUP_MODE: NORMAL
SQL> Rem SQL_IGNORABLE_ERRORS: NONE
SQL> Rem SQL_CALLING_FILE: utlrp.sql
SQL> Rem END SQL_FILE_METADATA
SQL> Rem
SQL> Rem MODIFIED (MM/DD/YY)
SQL> Rem thbaby 05/11/17 - Bug 26046188: disable redirection in Proxy PDB
SQL> Rem raeburns 04/15/17 - Bug 25790192: Add SQL_METADATA
SQL> Rem jmuller 12/09/14 - Fix bug 19728696 (sort of): clarify comments
SQL> Rem pyam 04/08/14 - 18478064: factor out to reenable_indexes.sql
SQL> Rem kquinn 11/01/11 - 13059165: amend 'OBJECTS WITH ERRORS' SQL
SQL> Rem cdilling 05/15/10 - fix bug 9712478 - call local enquote_name
SQL> Rem anighosh 02/19/09 - #(8264899): re-enabling of function based indexes
SQL> Rem not needed.
SQL> Rem cdilling 07/21/08 - check bitand for functional index - bug 7243270
SQL> Rem cdilling 01/21/08 - add support for ORA-30552
SQL> Rem cdilling 08/27/07 - check disabled indexes only
SQL> Rem cdilling 05/22/07 - add support for ORA-38301
SQL> Rem cdilling 02/19/07 - 5530085 - renable invalid indexes
SQL> Rem rburns 03/17/05 - use dbms_registry_sys
SQL> Rem gviswana 02/07/05 - Post-compilation diagnostics
SQL> Rem gviswana 09/09/04 - Auto tuning and diagnosability
SQL> Rem rburns 09/20/04 - fix validate_components
SQL> Rem gviswana 12/09/03 - Move functional-index re-enable here
SQL> Rem gviswana 06/04/03 - gviswana_bug-2814808
SQL> Rem gviswana 05/28/03 - Created
SQL> Rem
SQL>
SQL> SET VERIFY OFF;
SQL>
SQL> Rem Bug 26046188: In a Proxy PDB, all top-level statements are redirected to
SQL> Rem the target (PDB) of the Proxy PDB. Set underscore parameter so that
SQL> Rem automatic redirection is turned off. This is needed so that utlrp/utlprp
SQL> Rem can be used to recompile objects in Proxy PDB.
SQL> Rem
SQL> alter session set "_enable_view_pdb"=false;

ltered.

00:00:00.00
SQL>
SQL> SELECT dbms_registry_sys.time_stamp('utlrp_bgn') as timestamp from dual;

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2019-02-08 20:48:04

1 row selected.

Elapsed: 00:00:00.02
SQL>
SQL> DOC
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>#
SQL>
SQL> DECLARE
2 threads pls_integer := &&1;
3 BEGIN
4 utl_recomp.recomp_parallel(threads);
5 END;
6 /

PL/SQL procedure successfully completed.

Elapsed: 00:06:46.46
SQL>
SQL> SELECT dbms_registry_sys.time_stamp('utlrp_end') as timestamp from dual;

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2019-02-08 20:54:51

1 row selected.

Elapsed: 00:00:00.02
SQL>
SQL> Rem #(8264899): The code to Re-enable functional indexes, which used to exist
SQL> Rem here, is no longer needed.
SQL>
SQL> DOC
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>#
SQL> select COUNT(*) "OBJECTS WITH ERRORS" from obj$ where status in (3,4,5,6);

OBJECTS WITH ERRORS
-------------------
0

1 row selected.

Elapsed: 00:00:00.01
SQL>
SQL>
SQL> DOC
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>#
SQL> select COUNT(*) "ERRORS DURING RECOMPILATION" from utl_recomp_errors;

ERRORS DURING RECOMPILATION
---------------------------
0

1 row selected.

Elapsed: 00:00:00.00
SQL>
SQL> Rem =====================================================================
SQL> Rem Reenable indexes that may have been disabled, based on the
SQL> Rem table SYS.ENABLED$INDEXES
SQL> Rem =====================================================================
SQL>
SQL> @@?/rdbms/admin/reenable_indexes.sql
SQL> Rem
SQL> Rem $Header: rdbms/admin/reenable_indexes.sql /main/3 2015/02/04 13:57:27 sylin Exp $
SQL> Rem
SQL> Rem reenable_indexes.sql
SQL> Rem
SQL> Rem Copyright (c) 2014, 2015, Oracle and/or its affiliates.
SQL> Rem All rights reserved.
SQL> Rem
SQL> Rem NAME
SQL> Rem reenable_indexes.sql - <one-line expansion of the name>
SQL> Rem
SQL> Rem DESCRIPTION
SQL> Rem <short description of component this file declares/defines>
SQL> Rem
SQL> Rem NOTES
SQL> Rem <other useful comments, qualifications, etc.>
SQL> Rem
SQL> Rem BEGIN SQL_FILE_METADATA
SQL> Rem SQL_SOURCE_FILE: rdbms/admin/reenable_indexes.sql
SQL> Rem SQL_SHIPPED_FILE: rdbms/admin/reenable_indexes.sql
SQL> Rem SQL_PHASE: REENABLE_INDEXES
SQL> Rem SQL_STARTUP_MODE: NORMAL
SQL> Rem SQL_IGNORABLE_ERRORS: NONE
SQL> Rem SQL_CALLING_FILE: rdbms/admin/noncdb_to_pdb.sql
SQL> Rem END SQL_FILE_METADATA
SQL> Rem
SQL> Rem MODIFIED (MM/DD/YY)
SQL> Rem sylin 01/30/15 - bug20422151 - longer identifier
SQL> Rem surman 01/08/15 - 19475031: Update SQL metadata
SQL> Rem pyam 04/03/14 - Reenable indexes based on sys.enabled$indexes
SQL> Rem (formerly in utlprp.sql)
SQL> Rem pyam 04/03/14 - Created
SQL> Rem
SQL>
SQL> Rem
SQL> Rem Declare function local_enquote_name to pass FALSE
SQL> Rem into underlying dbms_assert.enquote_name function
SQL> Rem
SQL> CREATE OR REPLACE FUNCTION local_enquote_name (str varchar2)
2 return varchar2 is
3 begin
4 return dbms_assert.enquote_name(str, FALSE);
5 end local_enquote_name;
6 /

Function created.

Elapsed: 00:00:00.01
QL> Rem
QL> Rem If sys.enabled$index table exists, then re-enable
QL> Rem list of functional indexes that were enabled prior to upgrade
QL> Rem The table sys.enabled$index table is created in catupstr.sql
QL> Rem
QL> SET serveroutput on
QL> DECLARE
2 TYPE tab_char IS TABLE OF VARCHAR2(32767) INDEX BY BINARY_INTEGER;
3 commands tab_char;
4 p_null CHAR(1);
5 p_schemaname dbms_id;
6 p_indexname dbms_id;
7 rebuild_idx_msg BOOLEAN := FALSE;
8 non_existent_index exception;
9 recycle_bin_objs exception;
10 cannot_change_obj exception;
11 no_such_table exception;
12 pragma exception_init(non_existent_index, -1418);
13 pragma exception_init(recycle_bin_objs, -38301);
14 pragma exception_init(cannot_change_obj, -30552);
15 pragma exception_init(no_such_table, -942);
16 type cursor_t IS REF CURSOR;
17 reg_cursor cursor_t;
18
19 BEGIN
20 -- Check for existence of the table marking disabled functional indices
21
22 SELECT NULL INTO p_null FROM DBA_OBJECTS
23 WHERE owner = 'SYS' and object_name = 'ENABLED$INDEXES' and
24 object_type = 'TABLE' and rownum <=1;
25
26 -- Select indices to be re-enabled
27 EXECUTE IMMEDIATE q'+
28 SELECT 'ALTER INDEX ' ||
29 local_enquote_name(e.schemaname) || '.' ||
30 local_enquote_name(e.indexname) || ' ENABLE'
31 FROM enabled$indexes e, ind$ i
32 WHERE e.objnum = i.obj# AND bitand(i.flags, 1024) != 0 AND
33 bitand(i.property, 16) != 0+'
34 BULK COLLECT INTO commands;
35
36 IF (commands.count() > 0) THEN
37 FOR i IN 1 .. commands.count() LOOP
38 BEGIN
39 EXECUTE IMMEDIATE commands(i);
40 EXCEPTION
41 WHEN NON_EXISTENT_INDEX THEN NULL;
42 WHEN RECYCLE_BIN_OBJS THEN NULL;
43 WHEN CANNOT_CHANGE_OBJ THEN rebuild_idx_msg := TRUE;
44 END;
45 END LOOP;
46 END IF;
47
48 -- Output any indexes in the table that could not be re-enabled
49 -- due to ORA-30552 during ALTER INDEX...ENBLE command
50
51 IF rebuild_idx_msg THEN
52 BEGIN
53 DBMS_OUTPUT.PUT_LINE
54 ('The following indexes could not be re-enabled and may need to be rebuilt:');
55
56 OPEN reg_cursor FOR
57 'SELECT e.schemaname, e.indexname
58 FROM enabled$indexes e, ind$ i
59 WHERE e.objnum = i.obj# AND bitand(i.flags, 1024) != 0';
60
61 LOOP
62 FETCH reg_cursor INTO p_schemaname, p_indexname;
63 EXIT WHEN reg_cursor%NOTFOUND;
64 DBMS_OUTPUT.PUT_LINE
65 ('.... INDEX ' || p_schemaname || '.' || p_indexname);
66 END LOOP;
67 CLOSE reg_cursor;
68
69 EXCEPTION
70 WHEN NO_DATA_FOUND THEN CLOSE reg_cursor;
71 WHEN NO_SUCH_TABLE THEN CLOSE reg_cursor;
72 WHEN OTHERS THEN CLOSE reg_cursor; raise;
73 END;
74
75 END IF;
76
77 EXECUTE IMMEDIATE 'DROP TABLE sys.enabled$indexes';
78
79 EXCEPTION
80 WHEN NO_DATA_FOUND THEN NULL;
81
82 END;
83 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.09
SQL>
SQL> DROP function local_enquote_name;

Function dropped.

Elapsed: 00:00:00.01
SQL> SET serveroutput off
SQL>
SQL>
SQL> Rem =========================================================
SQL> Rem Run component validation procedure
SQL> Rem =========================================================
SQL>
SQL> SET serveroutput on
SQL> EXECUTE dbms_registry_sys.validate_components;

PL/SQL procedure successfully completed.

Elapsed: 00:00:04.23
SQL> SET serveroutput off
SQL>
SQL>
SQL> Rem =========================================================
SQL> Rem END utlrp.sql
SQL> Rem =========================================================
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.0.0

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 2365584808 bytes
Fixed Size 8660392 bytes
Variable Size 587202560 bytes
Database Buffers 1761607680 bytes
Redo Buffers 8114176 bytes
Database mounted.
Database opened.
SQL> show parameter compatible;

NAME                                 TYPE        VALUE
------------------------------------ ----------- --------------------
compatible                           string      18.0.0

Compile Fixed objects stats :

SQL> EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

PL/SQL procedure successfully completed.

Upgrade Time Zone version :

SQL> 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_prepare(l_tz_version);
END;
/ 2 3 4 5 6 7 8 9
l_tz_version=31
A prepare window has been successfully started.

PL/SQL procedure successfully completed.

Now check the upgrade timezone version,we are going to upgrade DST_SECONDARY_TT_VERSION  is 31

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;

PROPERTY_NAME                  PROPERTY_VALUE
------------------------------ --------------------
DST_PRIMARY_TT_VERSION         14
DST_SECONDARY_TT_VERSION       31
DST_UPGRADE_STATE              PREPARE

Shutdown and startup database in normal mode.

SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP;
ORACLE instance started.

Total System Global Area 2365584808 bytes
Fixed Size 8660392 bytes
Variable Size 587202560 bytes
Database Buffers 1761607680 bytes
Redo Buffers 8114176 bytes
Database mounted.
Database opened.

Upgrade the database zone file.

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> 
Table list: "IX"."AQ$_ORDERS_QUEUETABLE_S"
Number of failures: 0
Table list: "IX"."AQ$_STREAMS_QUEUE_TABLE_S"
Number of failures: 0
Table list: "IX"."AQ$_ORDERS_QUEUETABLE_L"
Number of failures: 0
Table list: "IX"."AQ$_STREAMS_QUEUE_TABLE_L"
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.

dba_tstz_tables view displays the tables that are processed by the time zone file upgrade, and their current upgrade status.

SQL> SELECT owner,table_name,upgrade_in_progress FROM   dba_tstz_tables
ORDER BY 1,2;

Once the upgrade is complete, check the time zone file version.

SQL> SELECT * FROM v$timezone_file;

FILENAME             VERSION    CON_ID
-------------------- ---------- -------
timezlrg_31.dat      31         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; 

PROPERTY_NAME                  PROPERTY_VALUE
------------------------------ --------------
DST_PRIMARY_TT_VERSION         31
DST_SECONDARY_TT_VERSION       0
DST_UPGRADE_STATE              NONE

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-08 19:42:17

For Source Database: ORCL18C
Source Database Version: 11.2.0.3.0
For Upgrade to Version: 18.0.0.0.0

Preup                        Preupgrade 
Action                       Issue Is 
Number Preupgrade Check Name Remedied Further DBA Action 
------ --------------------- -------- --------------------------------
10. depend_usr_tables        YES       None.
11. old_time_zones_exist     YES       None.
12. post_dictionary          YES       None.
13. post_fixed_objects       NO        Informational only.
                                       Further action is optional.
14. upg_by_std_upgrd         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
--------- ----------------- -----------
ORCL18C   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

Patching Oracle Database Release Updates Revision (RUR) 18.3.0.0.0 to 18.3.2.0.0

Description:-

We are going to perform Patching Oracle Database Release Update Revision 18.3.0.0.0 to 18.3.2.0.0

About Oracle Database Release Numbers:-
Oracle Database releases are released in version and version_full releases. The version is designated in the form major release numeral.0.0.0.0. The release version is the annual release designation of the database software. For example: 2018 is the release year, and the release version is 18.0.0.0.0. The version_full release is updated using numeric segments that change, based on the annual release designation of the software, the quarterly release update version (RU), and the quarterly release updates revision (RUR).
Oracle Database version_full releases are categorized by three numeric segments, which are divided by periods. The first, second, and third numerals provide information about the Oracle Database releases, starting with Oracle Database release 18c:

First numeral: Oracle Database major release number. For example: Oracle Database 18c; Oracle Database 12c. Starting with Oracle Database 18c, the first numeral indicates the initial year in which an Oracle Database version is released. For example: 2018 is the initial release year for Oracle Database 18c (18.0.0.0.0)
Second numeral: Oracle Database Release Update (Update) in Oracle Database 18c and later releases. For example: 18.3 is Release Update 3. In releases before Oracle Database 18c, the second numeral designates a maintenance release number. For example: Oracle Database 12c release 1 (12.1); Oracle 12c release 2 (12.2).
Third numeral: Oracle Database release updates revision (Revision). For example: 18.1.1.; 18.2.1, 18.3.0
Fourth numeral: Oracle Database increment version. This nomenclature can apply to updates in future releases, and it applies to all releases earlier than 18c. For example: 12.1.0.1, 12.2.0.1,
Fifth numeral: This numeral is reserved for future use.

Caution:

Oracle strongly recommends that you apply the most recent release update (Update) or bundle patch or patch set update to your source and target databases before starting an upgrade, and before starting a downgrade.

 

Check Database version :

[oracle@test admin]$ sqlplus / as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Sat Feb 9 01:15:22 2019
Version 18.3.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

Check Opatch version and lsinventory :

Check the opatch version and inventory for ORACLE_HOME.

[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/OPatch:$PATH
[oracle@test ~]$ opatch version
OPatch Version: 12.2.0.1.14

OPatch succeeded.

Minimum version of OPatch utility version 12.2.0.1.14 or later to apply this patch.

[oracle@test ~]$ opatch lsinventory
Oracle Interim Patch Installer version 12.2.0.1.14
Copyright (c) 2019, Oracle Corporation. All rights reserved.

Oracle Home : /u02/app/oracle/product/18.3.0/db_1
Central Inventory : /u01/app/oraInventory
from : /u02/app/oracle/product/18.3.0/db_1/oraInst.loc
OPatch version : 12.2.0.1.14
OUI version : 12.2.0.4.0
Log file location : /u02/app/oracle/product/18.3.0/db_1/cfgtoollogs/opatch/opatch2019-02-09_01-43-56AM_1.log
Lsinventory Output file location : /u02/app/oracle/product/18.3.0/db_1/cfgtoollogs/opatch/lsinv/lsinventory2019-02-09_01-43-56AM.txt

-----------------------------------------------------------------------
Local Machine Information::
Hostname: test.localdomain.com
ARU platform id: 226
ARU platform description:: Linux x86-64

Installed Top-level Products (1):
Oracle Database 18c 18.0.0.0.0
There are 1 products installed in this Oracle Home.
Interim patches (4) :
Patch 27908644 : applied on Wed Jul 18 23:14:11 IST 2018
Unique Patch ID: 22153180
Patch description: "UPDATE 18.3 DATABASE CLIENT JDK IN ORACLE HOME TO JDK8U171"
Created on 4 May 2018, 01:21:02 hrs PST8PDT
Bugs fixed:
27908644
Patch 27923415 : applied on Wed Jul 18 23:11:38 IST 2018
Unique Patch ID: 22239273
Patch description: "OJVM RELEASE UPDATE: 18.3.0.0.180717 (27923415)"
Created on 15 Jul 2018, 10:33:22 hrs PST8PDT
Bugs fixed:
27304131, 27539876, 27952586, 27642235, 27636900, 27461740
Patch 28090553 : applied on Wed Jul 18 23:10:01 IST 2018
Unique Patch ID: 22256940
Patch description: "OCW RELEASE UPDATE 18.3.0.0.0 (28090553)"
Created on 11 Jul 2018, 19:20:31 hrs PST8PDT
Bugs fixed:
12816839, 18701017, 22734786, 23698980, 23840305, 25709124, 25724089
26299684, 26313403, 26433972, 26527054, 26586174, 26587652, 26647619
26827699, 26860285, 26882126, 26882316, 26943660, 26996813, 27012915
27018734, 27032726, 27034318, 27040560, 27080748, 27086406, 27092991
27098733, 27106915, 27114112, 27121566, 27133637, 27144533, 27153755
27166715, 27174938, 27174948, 27177551, 27177852, 27182006, 27182064
27184253, 27204476, 27212837, 27213140, 27220610, 27222423, 27222938
27238077, 27238258, 27249544, 27252023, 27257509, 27263677, 27265816
27267992, 27271876, 27274143, 27285557, 27299455, 27300007, 27302415
27309182, 27314512, 27315159, 27320985, 27334353, 27338838, 27346984
27358232, 27362190, 27370933, 27377219, 27378959, 27379846, 27379956
27393421, 27398223, 27399499, 27399762, 27399985, 27401618, 27403244
27404599, 27426277, 27428790, 27430219, 27430254, 27433163, 27452897
27458829, 27465480, 27475272, 27481406, 27481765, 27492916, 27496806
27503318, 27503413, 27508936, 27508984, 27513114, 27519708, 27526362
27528204, 27532009, 27534289, 27560562, 27560735, 27573154, 27573408
27574335, 27577122, 27579969, 27581484, 27593587, 27595801, 27600706
27609819, 27625010, 27625050, 27627992, 27654039, 27657467, 27657920
27668379, 27682288, 27691717, 27702244, 27703242, 27708711, 27714373
27725967, 27731346, 27734470, 27735534, 27739957, 27740854, 27747407
27748321, 27757979, 27766679, 27768034, 27778433, 27782464, 27783059
27786669, 27786699, 27801774, 27811439, 27839732, 27850736, 27862636
27864737, 27865439, 27889841, 27896388, 27897639, 27906509, 27931506
27935826, 27941514, 27957892, 27978668, 27984314, 27993298, 28023410
28025398, 28032758, 28039471, 28039953, 28045209, 28099592, 28109698
28174926, 28182503, 28204423, 28240153
Patch 28090523 : applied on Wed Jul 18 23:09:24 IST 2018
Unique Patch ID: 22329768
Patch description: "Database Release Update : 18.3.0.0.180717 (28090523)"
Created on 14 Jul 2018, 00:03:50 hrs PST8PDT
Bugs fixed:
9062315, 13554903, 21547051, 21766220, 21806121, 23003564, 23310101
24489904, 24689376, 24737581, 24925863, 25035594, 25035599, 25287072
25348956, 25634405, 25726981, 25743479, 25824236, 25943740, 26226953
26336101, 26423085, 26427905, 26450454, 26476244, 26598422, 26615291
26646549, 26654411, 26731697, 26785169, 26792891, 26818960, 26822620
26843558, 26843664, 26846077, 26894737, 26898279, 26928317, 26933599
26956033, 26961415, 26966120, 26986173, 26992964, 27005278, 27026401
27028251, 27030974, 27036408, 27038986, 27041253, 27044575, 27047831
27053044, 27058530, 27060167, 27060859, 27061736, 27066451, 27066519
27073066, 27086821, 27090765, 27101527, 27101652, 27110878, 27112686
27119621, 27126666, 27128580, 27135647, 27143756, 27143882, 27147979
27153641, 27155549, 27156355, 27163928, 27169796, 27181521, 27181537
27189611, 27190851, 27193810, 27199245, 27208953, 27210038, 27210872
27214085, 27215007, 27216224, 27221900, 27222121, 27222626, 27224987
27226913, 27232983, 27233563, 27236052, 27236110, 27240246, 27240570
27241221, 27241247, 27244337, 27244785, 27249215, 27250547, 27254851
27258578, 27259386, 27259983, 27262650, 27262945, 27263276, 27263996
27270197, 27274456, 27274536, 27275136, 27275776, 27282707, 27283029
27283960, 27284499, 27285244, 27288230, 27292213, 27294480, 27301308
27301568, 27302594, 27302681, 27302695, 27302711, 27302730, 27302777
27302800, 27302960, 27304410, 27304936, 27305318, 27307868, 27310092
27313687, 27314206, 27314390, 27318869, 27321179, 27321834, 27326204
27329812, 27330158, 27330161, 27333658, 27333664, 27333693, 27334316
27334648, 27335682, 27338912, 27338946, 27339115, 27339396, 27339483
27339495, 27341036, 27345190, 27345231, 27345450, 27345498, 27346329
27346644, 27346709, 27346949, 27347126, 27348081, 27348707, 27349393
27352600, 27354783, 27356373, 27357773, 27358241, 27359178, 27359368
27360126, 27364891, 27364916, 27364947, 27365139, 27365702, 27365993
27367194, 27368850, 27372756, 27375260, 27375542, 27376871, 27378103
27379233, 27381383, 27381656, 27384222, 27389352, 27392187, 27395404
27395416, 27395794, 27396357, 27396365, 27396377, 27396624, 27396666
27396672, 27396813, 27398080, 27398660, 27401637, 27405242, 27405696
27410300, 27410595, 27412805, 27417186, 27420715, 27421101, 27422874
27423251, 27425507, 27425622, 27426363, 27427805, 27430802, 27432338
27432355, 27433870, 27434050, 27434193, 27434486, 27434974, 27435537
27439835, 27441326, 27442041, 27444727, 27445330, 27445462, 27447452
27447687, 27448162, 27450355, 27450400, 27450783, 27451049, 27451182
27451187, 27451531, 27452760, 27453225, 27457666, 27457891, 27458164
27459909, 27460675, 27467543, 27469329, 27471876, 27472969, 27473800
27479358, 27483974, 27484556, 27486253, 27487795, 27489719, 27496224
27496308, 27497950, 27498477, 27501327, 27501413, 27501465, 27502420
27504190, 27505603, 27506774, 27508985, 27511196, 27512439, 27517818
27518227, 27518310, 27520070, 27520900, 27522245, 27523368, 27523800
27525909, 27532375, 27533819, 27534509, 27537472, 27544030, 27545630
27547732, 27550341, 27551855, 27558557, 27558559, 27558861, 27560702
27563629, 27563767, 27570318, 27577758, 27579353, 27580996, 27585755
27585800, 27586810, 27586895, 27587672, 27591842, 27592466, 27593389
27595973, 27599689, 27602091, 27602488, 27603841, 27604293, 27607805
27608669, 27610269, 27613080, 27613247, 27615608, 27616657, 27617522
27625274, 27625620, 27631506, 27634676, 27635508, 27644757, 27649707
27652302, 27663370, 27664702, 27679488, 27679664, 27679806, 27679961
27680162, 27680509, 27682151, 27688099, 27688692, 27690578, 27691809
27692215, 27693713, 27697092, 27701795, 27705761, 27707544, 27709046
27718914, 27719187, 27723002, 27726269, 27726780, 27732323, 27739006
27740844, 27744211, 27745220, 27747869, 27748954, 27751006, 27753336
27757567, 27772815, 27773602, 27774320, 27774539, 27779886, 27780562
27782339, 27783289, 27786772, 27791223, 27797290, 27803665, 27807441
27812560, 27812593, 27813267, 27815347, 27818871, 27832643, 27833369
27834984, 27840386, 27847259, 27851757, 27861909, 27869339, 27873643
27882176, 27892488, 27924147, 27926113, 27930478, 27934468, 27941896
27945870, 27950708, 27952762, 27961746, 27964051, 27970265, 27971575
27984028, 27989849, 27993289, 27994333, 27997875, 27999597, 28021205
28022847, 28033429, 28057267, 28059199, 28072130, 28098865, 28106402
28132287, 28169711, 28174827, 28184554, 28188330, 25929650, 28264172

-----------------------------------------------------------------------
OPatch succeeded.

Download and Unzip the Database Release Update Patch :

Release Date and Download link :

15-Jan-2019 Database Release Update Revision 18.3.2.0.0 Patch 28790643
[oracle@test u02]$ unzip p28790643_180000_Linux-x86-64.zip
Archive: p28790643_180000_Linux-x86-64.zip
creating: 28790643/
creating: 28790643/files/
[oracle@test u02]$ ls -lrt
total 553308
drwxr-xr-x 4 oracle oinstall 4096 Dec 1 18:43 28790643

Check Patch Conflict Detection and Resolution :

[oracle@test 28790643]$ opatch prereq CheckConflictAgainstOHWithDetail -ph ./
Oracle Interim Patch Installer version 12.2.0.1.14
Copyright (c) 2019, Oracle Corporation. All rights reserved.
PREREQ session
Oracle Home : /u02/app/oracle/product/18.3.0/db_1
Central Inventory : /u01/app/oraInventory
from : /u02/app/oracle/product/18.3.0/db_1/oraInst.loc
OPatch version : 12.2.0.1.14
OUI version : 12.2.0.4.0
Log file location : /u02/app/oracle/product/18.3.0/db_1/cfgtoollogs/opatch/opatch2019-02-09_01-53-22AM_1.log
Invoking prereq "checkconflictagainstohwithdetail"
Prereq "checkConflictAgainstOHWithDetail" passed.

OPatch succeeded.

Shutdown the database and listener :

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

[oracle@test ~]$ lsnrctl stop

LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 09-FEB-2019 02:05:46
Copyright (c) 1991, 2018, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=test.localdomain.com)(PORT=1521)))
The command completed successfully

Apply the database upgrade patch :

[oracle@test 28790643]$ opatch apply
Oracle Interim Patch Installer version 12.2.0.1.14
Copyright (c) 2019, Oracle Corporation. All rights reserved.

Oracle Home : /u02/app/oracle/product/18.3.0/db_1
Central Inventory : /u01/app/oraInventory
from : /u02/app/oracle/product/18.3.0/db_1/oraInst.loc
OPatch version : 12.2.0.1.14
OUI version : 12.2.0.4.0
Log file location : /u02/app/oracle/product/18.3.0/db_1/cfgtoollogs/opatch/opatch2019-02-09_01-59-16AM_1.log

Verifying environment and performing prerequisite checks...
OPatch continues with these patches: 28790643

Do you want to proceed? [y|n]
y
User Responded with: Y
All checks passed.

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/u02/app/oracle/product/18.3.0/db_1')

Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files...
Applying interim patch '28790643' to OH '/u02/app/oracle/product/18.3.0/db_1'
ApplySession: Optional component(s) [ oracle.network.cman, 18.0.0.0.0 ] , [ oracle.net.cman, 18.0.0.0.0 ] , [ oracle.assistants.asm, 18.0.0.0.0 ] , [ oracle.ons.daemon, 18.0.0.0.0 ] , [ oracle.crs, 18.0.0.0.0 ] , [ oracle.assistants.usm, 18.0.0.0.0 ] , [ oracle.tfa, 18.0.0.0.0 ] , [ oracle.assistants.server.oui, 18.0.0.0.0 ] , [ oracle.has.crs, 18.0.0.0.0 ] not present in the Oracle Home or a higher version is found.
Patching component oracle.ctx.rsf, 18.0.0.0.0...
Patching component oracle.ons, 18.0.0.0.0...
Patching component oracle.nlsrtl.rsf, 18.0.0.0.0...
Patching component oracle.dbjava.ucp, 18.0.0.0.0...
Patching component oracle.sdo.locator, 18.0.0.0.0...
Patching component oracle.nlsrtl.rsf.core, 18.0.0.0.0...
Patching component oracle.xdk, 18.0.0.0.0...
Patching component oracle.rdbms.dbscripts, 18.0.0.0.0...
Patching component oracle.rdbms.rsf, 18.0.0.0.0...
Patching component oracle.rdbms.util, 18.0.0.0.0...
Patching component oracle.rdbms.rman, 18.0.0.0.0...
Patching component oracle.xdk.rsf, 18.0.0.0.0...
Patching component oracle.network.client, 18.0.0.0.0...
Patching component oracle.rdbms.rsf.ic, 18.0.0.0.0...
Patching component oracle.assistants.deconfig, 18.0.0.0.0...
Patching component oracle.oraolap.dbscripts, 18.0.0.0.0...
Patching component oracle.xdk.parser.java, 18.0.0.0.0...
Patching component oracle.rdbms.deconfig, 18.0.0.0.0...
Patching component oracle.assistants.server, 18.0.0.0.0...
Patching component oracle.rdbms.install.plugins, 18.0.0.0.0...
Patching component oracle.network.rsf, 18.0.0.0.0...
Patching component oracle.install.deinstalltool, 18.0.0.0.0...
Patching component oracle.sdo.locator.jrf, 18.0.0.0.0...
Patching component oracle.oracore.rsf, 18.0.0.0.0...
Patching component oracle.assistants.acf, 18.0.0.0.0...
Patching component oracle.ctx.atg, 18.0.0.0.0...
Patching component oracle.dbjava.jdbc, 18.0.0.0.0...
Patching component oracle.rdbms, 18.0.0.0.0...
Patching component oracle.dbjava.ic, 18.0.0.0.0...
Patching component oracle.server, 18.0.0.0.0...
Patching component oracle.rdbms.crs, 18.0.0.0.0...
Patching component oracle.sqlplus, 18.0.0.0.0...
Patching component oracle.sdo, 18.0.0.0.0...
Patching component oracle.network.listener, 18.0.0.0.0...
Patching component oracle.rdbms.oci, 18.0.0.0.0...
Patching component oracle.ctx, 18.0.0.0.0...
Patching component oracle.precomp.rsf, 18.0.0.0.0...
Patching component oracle.sqlplus.ic, 18.0.0.0.0...
Patching component oracle.javavm.client, 18.0.0.0.0...
Patching component oracle.ldap.owm, 18.0.0.0.0...
Patching component oracle.ldap.security.osdt, 18.0.0.0.0...
Patching component oracle.precomp.lang, 18.0.0.0.0...
Patching component oracle.precomp.common, 18.0.0.0.0...
Patch 28790643 successfully applied.
Sub-set patch [28090523] has become inactive due to the application of a super-set patch [28790643].
Please refer to Doc ID 2161861.1 for any possible further required actions.
Log file location: /u02/app/oracle/product/18.3.0/db_1/cfgtoollogs/opatch/opatch2019-02-09_01-59-16AM_1.log

OPatch succeeded.

Start the listener and database :

[oracle@test ~]$ lsnrctl start

LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 09-FEB-2019 02:04:49

Copyright (c) 1991, 2018, Oracle. All rights reserved.

Starting /u02/app/oracle/product/18.3.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 18.0.0.0.0 - Production
System parameter file is /u02/app/oracle/product/18.3.0/db_1/network/admin/listener.ora
Log messages written to /u02/app/oracle/diag/tnslsnr/test/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=test.localdomain.com)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=test.localdomain.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 18.0.0.0.0 - Production
Start Date 09-FEB-2019 02:04:51
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u02/app/oracle/product/18.3.0/db_1/network/admin/listener.ora
Listener Log File /u02/app/oracle/diag/tnslsnr/test/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=test.localdomain.com)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully

[oracle@test ~]$ lsnrctl status

LSNRCTL for Linux: Version 18.0.0.0.0 - Production on 09-FEB-2019 02:05:05

Copyright (c) 1991, 2018, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=test.localdomain.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 18.0.0.0.0 - Production
Start Date 09-FEB-2019 02:04:51
Uptime 0 days 0 hr. 0 min. 13 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u02/app/oracle/product/18.3.0/db_1/network/admin/listener.ora
Listener Log File /u02/app/oracle/diag/tnslsnr/test/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=test.localdomain.com)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=test.localdomain.com)(PORT=5500))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "dbwr.localdomain.com" has 1 instance(s).
Instance "dbwr", status READY, has 1 handler(s) for this service...
Service "dbwrXDB.localdomain.com" has 1 instance(s).
Instance "dbwr", status READY, has 1 handler(s) for this service...
The command completed successfully.
[oracle@test ~]$ sqlplus / as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Sat Feb 9 02:04:20 2019
Version 18.3.2.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Connected to an idle instance.

SQL> startup
ORACLE instance started.
Total System Global Area 1660943808 bytes
Fixed Size 8658368 bytes
Variable Size 1040187392 bytes
Database Buffers 603979776 bytes
Redo Buffers 8118272 bytes
Database mounted.
Database opened.

Loading Modified SQL Files into the Database : 

[oracle@test OPatch]$ ./datapatch -verbose

SQL Patching tool version 18.0.0.0.0 Production on Sat Feb 9 02:09:25 2019
Copyright (c) 2012, 2018, Oracle. All rights reserved.

Log file for this invocation: /u02/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_7208_2019_02_09_02_09_25/sqlpatch_invocation.log

Connecting to database...OK
Gathering database info...done
Bootstrapping registry and package to current versions...done
Determining current state...done

Current state of interim SQL patches:
Interim patch 27923415 (OJVM RELEASE UPDATE: 18.3.0.0.180717 (27923415)):
Binary registry: Installed
SQL registry: Applied successfully on 09-JAN-19 02.47.25.515405 AM

Current state of release update SQL patches:
Binary registry:
18.3.2.0.0 Release_Update_Revision 1811212344: Installed
SQL registry:
Applied 18.3.0.0.0 Release_Update 1806280943 successfully on 09-JAN-19 02.46.23.143918 AM

Adding patches to installation queue and performing prereq checks...done
Installation queue:
No interim patches need to be rolled back
Patch 28790643 (Database Release Update Revision : 18.3.2.0.190115 (28790643)):
Apply from 18.3.0.0.0 Release_Update 1806280943 to 18.3.2.0.0 Release_Update_Revision 1811212344
No interim patches need to be applied

Installing patches...
Patch installation complete. Total patches installed: 1

Validating logfiles...done
Patch 28790643 apply: SUCCESS
logfile: /u02/app/oracle/cfgtoollogs/sqlpatch/28790643/22605424/28790643_apply_DBWR_2019Feb09_02_10_00.log (no errors)
SQL Patching tool complete on Sat Feb 9 02:11:05 2019

Patch 28790643 – Database Release Update Revision 18.3.2.0.190115 is applied successfully and Database is upgraded from 18.3.0.0.0 to 18.3.2.0.0.

Reference : Patch README file

Run utlrp.sql to compile invalid objects :-

SQL> @?/rdbms/admin/utlrp

Session altered.

TIMESTAMP
-----------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2019-02-09 02:12:45

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-09 02:12:47

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

dba_registry_sqlpatch :

From Oracle 12c,we are using dba_registry_sqlpatch instead of REGISTRY$HISTORY  to check latest applied patches like RU,PSU and CPU.

SQL> select STATUS,DESCRIPTION,SOURCE_VERSION,TARGET_VERSION from dba_registry_sqlpatch;

STATUS DESCRIPTION                                      SOURCE_VERSION 
------ ----------------------------------------------   --------------
TARGET_VERSION
--------------
SUCCESS Database Release Update : 18.3.0.0.180717(28090523) 18.1.0.0.0  
18.3.0.0.0

SUCCESS OJVM RELEASE UPDATE: 18.3.0.0.180717 (27923415)     18.3.0.0.0
18.3.0.0.0

SUCCESS Database Release Update Revision : 18.3.2.0.190115  18.3.0.0.0 
        (28790643) 
18.3.2.0.0

To check database version :

SQL> select BANNER,BANNER_FULL from v$version

BANNER                     BANNER FULL  
-------------------------- --------------------------              
Oracle Database 18c        Oracle Database 18c        
Enterprise Edition Release Enterprise Edition Release
18.0.0.0.0 - Production    18.0.0.0.0 - Production 
                           Version 18.3.2.0.0

 

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

Oracle Database Upgrade 11.2.0.4 to 18.3.0.0 Using Manual Method

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

ReferenceOracle 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

 

 

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

 

 

Oracle 18c Database Upgrade From 12.2.0.1 to 18.3.0.0 Using Manual Method

Oracle 18c Database Manual Upgrade From 12.2.0.1 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

Take RMAN backups before upgrade.

    rman target / 

    run
    {
        ALLOCATE CHANNEL chan_name TYPE DISK;
        BACKUP DATABASE FORMAT '/u01%U' TAG before_upgrade;
        BACKUP CURRENT CONTROLFILE FORMAT '/u01/control%U';
    }

Ensure backup is complete.

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

[oracle@ram ~]$ export ORACLE_SID=orcldb
[oracle@ram ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Thu Feb 7 05:03:08 2019

Copyright (c) 1982, 2016, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 2516582400 bytes
Fixed Size 8623832 bytes
Variable Size 671090984 bytes
Database Buffers 1828716544 bytes
Redo Buffers 8151040 bytes
Database mounted.
Database opened.
SQL> 

[oracle@ram db_1]$ export ORACLE_SID=orcldb
[oracle@ram db_1]$ export ORACLE_HOME=/u01/app/oracle/product/12.2.0.1/db_1
[oracle@ram db_1]$ /u01/app/oracle/product/12.2.0.1/db_1/jdk/bin/java -jar /u01/app/oracle/product/18.0.0/db_1/rdbms/admin/preupgrade.jar FILE DIR /u01/preupgrade1/
==================
PREUPGRADE SUMMARY
==================
  /u01/preupgrade1/preupgrade.log
  /u01/preupgrade1/preupgrade_fixups.sql
  /u01/preupgrade1/postupgrade_fixups.sql

Execute fixup scripts as indicated below:

Before upgrade log into the database and execute the preupgrade fixups
@/u01/preupgrade1/preupgrade_fixups.sql

After the upgrade:

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

Preupgrade complete: 2019-02-08T22:48:35

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

[oracle@ram db_1]$ cd /u01/preupgrade1/
[oracle@ram preupgrade1]$ ls -lrt
total 628
drwxr-xr-x. 3 oracle oinstall 4096 Feb 8 22:48 oracle
-rw-r--r--. 1 oracle oinstall 7963 Feb 8 22:48 preupgrade_driver.sql
-rw-r--r--. 1 oracle oinstall 14846 Feb 8 22:48 dbms_registry_extended.sql
-rw-r--r--. 1 oracle oinstall 422048 Feb 8 22:48 preupgrade_package.sql
-rw-r--r--. 1 oracle oinstall 14383 Feb 8 22:48 parameters.properties
-rw-r--r--. 1 oracle oinstall 83854 Feb 8 22:48 preupgrade_messages.properties
drwxr-xr-x. 3 oracle oinstall 4096 Feb 8 22:48 upgrade
-rw-r--r--. 1 oracle oinstall 50172 Feb 8 22:48 components.properties
-rw-r--r--. 1 oracle oinstall 2 Feb 8 22:48 checksBuffer.tmp
-rw-r--r--. 1 oracle oinstall 5838 Feb 8 22:48 preupgrade_fixups.sql
-rw-r--r--. 1 oracle oinstall 7593 Feb 8 22:48 postupgrade_fixups.sql
-rw-r--r--. 1 oracle oinstall 5641 Feb 8 22:48 preupgrade.log

 Run the preupgrade_fixup.sql

[oracle@ram ~]$ export ORACLE_SID=orcldb
[oracle@ram ~]$ export ORACLE_HOME=/u01/app/oracle/product/12.2.0.1/db_1
[oracle@ram ~]$ export PATH=/u01/app/oracle/product/12.2.0.1/db_1/bin:$PATH
[oracle@ram ~]$ cd /u01/preupgrade1/
[oracle@ram preupgrade1]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Fri Feb 8 22:57:55 2019

Copyright (c) 1982, 2016, Oracle. All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> @/u01/preupgrade1/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-08 22:48:32

For Source Database: ORCLDB
Source Database Version: 12.2.0.1.0
For Upgrade to Version: 18.0.0.0.0

Preup                            Preupgrade
Action                           Issue Is
Number   Preupgrade Check Name   Remedied         Further DBA Action
------ ------------------------  ----------    ------------------------
  1.        dictionary_stats      YES                  None.
  2.        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
-------
26

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   26
DST_SECONDARY_TT_VERSION 0
DST_UPGRADE_STATE        NONE

Upgrade Database from 12.2.0.1 to 18c :-

Shutdown the 12c database:

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@ram ~]$ cd /u01/app/oracle/product/12.2.0.1/db_1/dbs/
[oracle@ram dbs]$ ls -lrth
total 20K
-rwxrwxr-x. 1 oracle oinstall 3.1K May 15 2015 init.ora
-rwxrwxr-x. 1 oracle oinstall 24 Feb 7 04:01 lkORCLDB
-rw-r-----. 1 oracle oinstall 3.5K Feb 7 04:06 orapworcldb
-rwxrwxr-x. 1 oracle oinstall 3.5K Feb 8 22:44 spfileorcldb.ora
-rwxrwxr-x. 1 oracle oinstall 1.6K Feb 8 23:06 hc_orcldb.dat
[oracle@ram dbs]$ cp spfileorcldb.ora orapworcldb /u01/app/oracle/product/18.0.0/db_1/dbs/

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

[oracle@ram ~]$ export ORACLE_SID=orcldb
[oracle@ram ~]$ export ORACLE_HOME=/u01/app/oracle/product/18.0.0/db_1
[oracle@ram ~]$ export PATH=/u01/app/oracle/product/18.0.0/db_1/bin:$PATH
[oracle@ram ~]$ sqlplus / as sysdba

SQL*Plus: Release 18.0.0.0.0 - Production on Fri Feb 8 23:10:39 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 the dbupgrade utility :

[oracle@ram ~]$ /u01/app/oracle/product/18.0.0/db_1/bin/dbupgrade -n 4 -l /u01/

Argument list for [/u01/app/oracle/product/18.0.0/db_1/rdbms/admin/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 = /u01/
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.0.0/db_1/rdbms/admin/orahome = [/u01/app/oracle/product/18.0.0/db_1]
/u01/app/oracle/product/18.0.0/db_1/bin/orabasehome = [/u01/app/oracle/product/18.0.0/db_1]
catctlGetOrabase = [/u01/app/oracle/product/18.0.0/db_1]

Analyzing file /u01/app/oracle/product/18.0.0/db_1/rdbms/admin/catupgrd.sql

Log file directory = [/u01/]

catcon::set_log_file_base_path: ALL catcon-related output will be written to [/u01/catupgrd_catcon_93556.lst]

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

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


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

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

------------------------------------------------------
Phases [0-108] End Time:[2019_02_08 23:59:27]
------------------------------------------------------

Grand Total Time: 2746s

LOG FILES: (/u01//catupgrd*.log)

Upgrade Summary Report Located in:
/u01//upg_summary.log

Grand Total Upgrade Time: [0d:0h:45m:46s]

Execute Post-Upgrade Status Tool, utlu122s.sql.

SQL> @utlu122s.sql


Oracle Database Release 18 Post-Upgrade Status Tool 02-09-2019 00:16:5

Component Current Full Elapsed Time
Name Status Version HH:MM:SS

Oracle Server                 UPGRADED 18.3.0.0.0    00:24:12
JServer JAVA Virtual Machine  VALID 18.3.0.0.0       00:01:38
Oracle XDK                    UPGRADED 18.3.0.0.0    00:00:45
Oracle Database Java Packages UPGRADED 18.3.0.0.0    00:00:19
OLAP Analytic Workspace       UPGRADED 18.3.0.0.0    00:00:20
Oracle Label Security         UPGRADED 18.3.0.0.0    00:00:09
Oracle Database Vault         UPGRADED 18.3.0.0.0    00:00:30
Oracle Text                   UPGRADED 18.3.0.0.0    00:00:53
Oracle Workspace Manager      UPGRADED 18.3.0.0.0    00:00:55
Oracle Real Application Clusters UPGRADED 18.3.0.0.0 00:00:00
Oracle XML Database           UPGRADED 18.3.0.0.0    00:02:25
Oracle Multimedia             UPGRADED 18.3.0.0.0    00:02:44
Spatial                       UPGRADED 18.3.0.0.0    00:05:50
Oracle OLAP API               UPGRADED 18.3.0.0.0    00:00:17
Upgrade Datapatch                                    00:02:27
Final Actions                                        00:02:33
Post Upgrade                                         00:00:04
Post Upgrade Datapatch                               00:00:24

Total Upgrade Time: 00:44:17

Database time zone version is 26. It is older than current release time
zone version 31. Time zone upgrade is needed using the DBMS_DST package.

Summary Report File = /u01//upg_summary.log

00:16:53 SQL>
00:16:53 SQL> @catuppst.sql 
00:21:32 SQL> Rem
00:21:32 SQL> Rem $Header: rdbms/admin/catuppst.sql /main/60 2017/08/03 17:44:03 wesmith Exp $
00:21:32 SQL> Rem
00:21:32 SQL> Rem catuppst.sql
00:21:32 SQL> Rem
00:21:32 SQL> Rem Copyright (c) 2006, 2017, Oracle and/or its affiliates.
00:21:32 SQL> Rem All rights reserved.
00:21:32 SQL> Rem
00:21:32 SQL> Rem NAME
00:21:32 SQL> Rem catuppst.sql - CATalog UPgrade PoST-upgrade actions
00:21:32 SQL> Rem
00:21:32 SQL> Rem DESCRIPTION
00:21:32 SQL> Rem This post-upgrade script performs remaining upgrade actions that
00:21:32 SQL> Rem do not require that the database be open in UPGRADE mode.
00:21:32 SQL> Rem Automatically apply the latest PSU.
00:21:32 SQL> Rem
00:21:32 SQL> Rem NOTES
00:21:32 SQL> Rem You must be connected AS SYSDBA to run this script.
00:21:32 SQL> Rem
00:21:32 SQL> Rem BEGIN SQL_FILE_METADATA
00:21:32 SQL> Rem SQL_SOURCE_FILE: rdbms/admin/catuppst.sql
00:21:32 SQL> Rem SQL_SHIPPED_FILE: rdbms/admin/catuppst.sql
00:21:32 SQL> Rem SQL_PHASE: UPGRADE
00:21:32 SQL> Rem SQL_STARTUP_MODE: UPGRADE
00:21:32 SQL> Rem SQL_IGNORABLE_ERRORS: NONE
00:21:32 SQL> Rem SQL_CALLING_FILE: rdbms/admin/catupgrd.sql
00:21:32 SQL> Rem END SQL_FILE_METADATA
00:21:32 SQL> Rem
00:21:32 SQL> Rem MODIFIED (MM/DD/YY)
00:21:32 SQL> Rem wesmith 08/07/17 - bug 22187143: fix dba_part/subpart_key_columns_v$
00:21:32 SQL> Rem cmlim 05/01/17 - bug 25248712 - Move the copy for bug 19651064
00:21:32 SQL> Rem from catuppst.sql to a1201000.sql
00:21:32 SQL> Rem cmlim 04/08/17 - bug 25248712: cut down on # of parallel slaves
00:21:32 SQL> Rem spawned
00:21:32 SQL> Rem frealvar 03/10/17 - move the gather_fixed_objects_stats code for Bug
00:21:32 SQL> Rem 14258301 as a preupgrade check
00:21:32 SQL> Rem raeburns 03/08/17 - Bug 25616909: Use UPGRADE for SQL_PHASE
00:21:32 SQL> Rem stanaya 12/08/16 - Bug-25191487 : adding sql metadata
00:21:32 SQL> Rem pyam 11/10/16 - 70732: add catalog app upgrade for post-shutdown
00:21:32 SQL> Rem anighosh 09/15/16 - Bug 24669189: Cleanup utlmmig replacement tables
00:21:32 SQL> Rem vperiwal 07/07/16 - 23726702: remove ORA-65173
00:21:32 SQL> Rem cmlim 06/06/16 - bug 23215791: add more DBUA_TIMESTAMPS during db
00:21:32 SQL> Rem upgrades
00:21:32 SQL> Rem anupkk 04/03/16 - Bug 22917286: Moved call to olstrig.sql to
00:21:32 SQL> Rem olsdbmig.sql
00:21:32 SQL> Rem raeburns 02/29/16 - Bug 22820096: revert ALTER TYPE to default
00:21:32 SQL> Rem CASCADE
00:21:32 SQL> Rem rmorant 02/11/16 - Bug22340563 add parallel hint
00:21:32 SQL> Rem atomar 02/04/16 - move aq action to release specific script
00:21:32 SQL> Rem raeburns 12/09/15 - Bug 22175911: add SERVEROUTPUT OFF after
00:21:32 SQL> Rem catuptabdata.sql
00:21:32 SQL> Rem rmorant 11/27/15 - bug22271668 add append hint
00:21:32 SQL> Rem welin 11/11/15 - Bug 21099929: 12.2 cleanup
..............................
.............................
............................
00:21:32 SQL> Rem rburns 10/23/06 - Created
00:21:32 SQL> Rem
00:21:32 SQL> 
00:21:32 SQL> @@?/rdbms/admin/sqlsessstart.sql
00:21:32 SQL> Rem
00:21:32 SQL> Rem $Header: rdbms/admin/sqlsessstart.sql /main/2 2017/05/28 22:46:11 stanaya Exp $
00:21:32 SQL> Rem
00:21:32 SQL> Rem sqlsessstart.sql
00:21:32 SQL> Rem
00:21:32 SQL> Rem Copyright (c) 2013, 2017, Oracle and/or its affiliates.
00:21:32 SQL> Rem All rights reserved.
00:21:32 SQL> Rem
00:21:32 SQL> Rem NAME
00:21:32 SQL> Rem sqlsessstart.sql - SQL session start
00:21:32 SQL> Rem
00:21:32 SQL> Rem DESCRIPTION
00:21:32 SQL> Rem Any commands which should be run at the start of all oracle
00:21:32 SQL> Rem supplied scripts.
00:21:32 SQL> Rem
00:21:32 SQL> Rem NOTES
00:21:32 SQL> Rem See sqlsessend.sql for the corresponding end script.
00:21:32 SQL> Rem
00:21:32 SQL> Rem BEGIN SQL_FILE_METADATA
00:21:32 SQL> Rem SQL_SOURCE_FILE: rdbms/admin/sqlsessstart.sql
00:21:32 SQL> Rem SQL_SHIPPED_FILE: rdbms/admin/sqlsessstart.sql
00:21:32 SQL> Rem SQL_PHASE: SQLSESSSTART
00:21:32 SQL> Rem SQL_STARTUP_MODE: NORMAL
00:21:32 SQL> Rem SQL_IGNORABLE_ERRORS: NONE
00:21:32 SQL> Rem END SQL_FILE_METADATA
00:21:32 SQL> Rem
00:21:32 SQL> Rem MODIFIED (MM/DD/YY)
00:21:32 SQL> Rem surman 03/08/13 - 16462837: Common start and end scripts
00:21:32 SQL> Rem surman 03/08/13 - Created
00:21:32 SQL> Rem
00:21:32 SQL> 
00:21:32 SQL> alter session set "_ORACLE_SCRIPT" = true;

Session altered.

Elapsed: 00:00:00.00
00:21:32 SQL> 
00:21:32 SQL> Rem =====================================================================
00:21:32 SQL> Rem Assure CHAR semantics are not used in the dictionary
00:21:32 SQL> Rem =====================================================================
00:21:32 SQL> ALTER SESSION SET NLS_LENGTH_SEMANTICS=BYTE;

Session altered.

Elapsed: 00:00:00.00
00:21:32 SQL> 
00:21:32 SQL> 
00:21:32 SQL> 
00:21:32 SQL> Rem =====================================================================
00:21:32 SQL> Rem Begin Catalog App Upgrade for Post-shutdown. All additional code
00:21:32 SQL> Rem should be placed after this.
00:21:32 SQL> Rem =====================================================================
00:21:32 SQL> @@catappupgbeg2.sql
00:21:32 SQL> Rem
00:21:32 SQL> Rem $Header: rdbms/admin/catappupgbeg2.sql /main/4 2017/09/29 14:04:55 pyam Exp $
00:21:32 SQL> Rem
00:21:32 SQL> Rem catappupgbeg2.sql
00:21:32 SQL> Rem
00:21:32 SQL> Rem Copyright (c) 2016, 2017, Oracle and/or its affiliates.
00:21:32 SQL> Rem All rights reserved.
00:21:32 SQL> Rem
00:21:32 SQL> Rem NAME
00:21:32 SQL> Rem catappupgbeg2.sql - <one-line expansion of the name>
00:21:32 SQL> Rem
00:21:32 SQL> Rem DESCRIPTION
00:21:32 SQL> Rem <short description of component this file declares/defines>
00:21:32 SQL> Rem
00:21:32 SQL> Rem NOTES
00:21:32 SQL> Rem <other useful comments, qualifications, etc.>
00:21:32 SQL> Rem
00:21:32 SQL> Rem BEGIN SQL_FILE_METADATA
00:21:32 SQL> Rem SQL_SOURCE_FILE: rdbms/admin/catappupgbeg2.sql
00:21:32 SQL> Rem SQL_SHIPPED_FILE:
00:21:32 SQL> Rem SQL_PHASE: INSTALL
00:21:32 SQL> Rem SQL_STARTUP_MODE: NORMAL
00:21:32 SQL> Rem SQL_IGNORABLE_ERRORS: NONE
00:21:32 SQL> Rem SQL_CALLING_FILE:
00:21:32 SQL> Rem END SQL_FILE_METADATA
00:21:32 SQL> Rem
00:21:32 SQL> Rem MODIFIED (MM/DD/YY)
00:21:32 SQL> Rem pyam 09/27/17 - Bug 26856671: alter system with scope=memory
00:21:32 SQL> Rem pyam 08/28/17 - Bug 25857770: set _enable_cdb_upgrade_capture
00:21:32 SQL> Rem only during upgrade
00:21:32 SQL> Rem pyam 06/09/17 - RTI 19984265: catch no_data_found
00:21:32 SQL> Rem pyam 11/17/16 - Catalog Application Upgrade Begin Part 2
00:21:32 SQL> Rem pyam 11/17/16 - Created
00:21:32 SQL> Rem
00:21:32 SQL> 
00:21:32 SQL> @@?/rdbms/admin/sqlsessstart.sql
00:21:32 SQL> Rem
00:21:32 SQL> Rem $Header: rdbms/admin/sqlsessstart.sql /main/2 2017/05/28 22:46:11 stanaya Exp $
00:21:32 SQL> Rem
00:21:32 SQL> Rem sqlsessstart.sql
00:21:32 SQL> Rem
00:21:32 SQL> Rem Copyright (c) 2013, 2017, Oracle and/or its affiliates.
00:21:32 SQL> Rem All rights reserved.
00:21:32 SQL> Rem
00:21:32 SQL> Rem NAME
00:21:32 SQL> Rem sqlsessstart.sql - SQL session start
00:21:32 SQL> Rem
00:21:32 SQL> Rem DESCRIPTION
00:21:32 SQL> Rem Any commands which should be run at the start of all oracle
00:21:32 SQL> Rem supplied scripts.
00:21:32 SQL> Rem
00:21:32 SQL> Rem NOTES
00:21:32 SQL> Rem See sqlsessend.sql for the corresponding end script.
00:21:32 SQL> Rem
00:21:32 SQL> Rem BEGIN SQL_FILE_METADATA
00:21:32 SQL> Rem SQL_SOURCE_FILE: rdbms/admin/sqlsessstart.sql
00:21:32 SQL> Rem SQL_SHIPPED_FILE: rdbms/admin/sqlsessstart.sql
00:21:32 SQL> Rem SQL_PHASE: SQLSESSSTART
00:21:32 SQL> Rem SQL_STARTUP_MODE: NORMAL
00:21:32 SQL> Rem SQL_IGNORABLE_ERRORS: NONE
00:21:32 SQL> Rem END SQL_FILE_METADATA
......................
......................
......................
00:21:52 SQL> Rem BEGIN SQL_FILE_METADATA
00:21:52 SQL> Rem SQL_SOURCE_FILE: rdbms/admin/nothing.sql
00:21:52 SQL> Rem SQL_SHIPPED_FILE: rdbms/admin/nothing.sql
00:21:52 SQL> Rem SQL_PHASE: NOTHING
00:21:52 SQL> Rem SQL_STARTUP_MODE: NORMAL
00:21:52 SQL> Rem SQL_IGNORABLE_ERRORS: NONE
00:21:52 SQL> Rem SQL_CALLING_FILE: NONE
00:21:52 SQL> Rem END SQL_FILE_METADATA
00:21:52 SQL> Rem
00:21:52 SQL> 
00:21:52 SQL> Rem =======================================================================
00:21:52 SQL> Rem EM End
00:21:52 SQL> Rem =======================================================================
00:21:52 SQL> 
00:21:52 SQL> 
00:21:52 SQL> Rem =======================================================================
00:21:52 SQL> Rem Do Java longer identifiers name translation, if necessary
00:21:52 SQL> Rem =======================================================================
00:21:52 SQL> 
00:21:52 SQL> declare
00:21:52 2 ret varchar2(20);
00:21:52 3 begin
00:21:52 4 ret := dbms_java_test.funcall('-lid_translate_all', ' ');
00:21:52 5 exception
00:21:52 6 when others then
00:21:52 7 null;
00:21:52 8 end;
00:21:52 9 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
00:21:52 SQL> 
00:21:52 SQL> Rem =======================================================================
00:21:52 SQL> Rem Java longer identifiers name translation End
00:21:52 SQL> Rem =======================================================================
00:21:52 SQL> 
00:21:52 SQL> Rem =======================================================================
00:21:52 SQL> Rem Signal 'end' of catuppst.sql before catbundle.sql is executed
00:21:52 SQL> Rem =======================================================================
00:21:52 SQL> SELECT dbms_registry_sys.time_stamp('POSTUP_END') as timestamp from dual;

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP POSTUP_END 2019-02-09 00:21:52
DBUA_TIMESTAMP POSTUP_END FINISHED 2019-02-09 00:21:52
DBUA_TIMESTAMP POSTUP_END NONE 2019-02-09 00:21:52


1 row selected.

Elapsed: 00:00:00.01
00:21:52 SQL> 
00:21:52 SQL> -- DBUA_TIMESTAMP: catuppst.sql finished
00:21:52 SQL> SELECT dbms_registry_sys.time_stamp('CATUPPST') as timestamp from dual;

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP CATUPPST 2019-02-09 00:21:52
DBUA_TIMESTAMP CATUPPST FINISHED 2019-02-09 00:21:52
DBUA_TIMESTAMP CATUPPST NONE 2019-02-09 00:21:52


1 row selected.

Elapsed: 00:00:00.01
00:21:52 SQL> 
00:21:52 SQL> Rem Set errorlogging off
00:21:52 SQL> SET ERRORLOGGING OFF;
00:21:52 SQL> 
00:21:52 SQL> Rem
00:21:52 SQL> Rem Set _ORACLE_SCRIPT to false
00:21:52 SQL> Rem
00:21:52 SQL> ALTER SESSION SET "_ORACLE_SCRIPT"=false;

Session altered.

Elapsed: 00:00:00.00
00:21:52 SQL> 
00:21:52 SQL> Rem *********************************************************************
00:21:52 SQL> Rem END catuppst.sql
00:21:52 SQL> Rem *********************************************************************

Execute utlrp.sql script to compile  invalid objects.

SQL> select count(*) from dba_objects where status='INVALID';

COUNT(*)
----------
1765

1 row selected.
00:27:47 SQL> @utlrp.sql
00:28:27 SQL> Rem
00:28:27 SQL> Rem $Header: rdbms/admin/utlrp.sql /main/23 2017/03/20 12:21:12 raeburns Exp $
00:28:27 SQL> Rem
00:28:27 SQL> Rem utlrp.sql
00:28:27 SQL> Rem
00:28:27 SQL> Rem Copyright (c) 1998, 2017, Oracle and/or its affiliates.
00:28:27 SQL> Rem All rights reserved.
00:28:27 SQL> Rem
00:28:27 SQL> Rem NAME
00:28:27 SQL> Rem utlrp.sql - Recompile invalid objects
00:28:27 SQL> Rem
00:28:27 SQL> Rem DESCRIPTION
00:28:27 SQL> Rem This script recompiles invalid objects in the database.
00:28:27 SQL> Rem
00:28:27 SQL> Rem When run as one of the last steps during upgrade or downgrade,
00:28:27 SQL> Rem this script will validate all remaining invalid objects. It will
00:28:27 SQL> Rem also run a component validation procedure for each component in
00:28:27 SQL> Rem the database. See the README notes for your current release and
00:28:27 SQL> Rem the Oracle Database Upgrade book for more information about
00:28:27 SQL> Rem using utlrp.sql
00:28:27 SQL> Rem
00:28:27 SQL> Rem Although invalid objects are automatically re-validated when used,
00:28:27 SQL> Rem it is useful to run this script after an upgrade or downgrade and
00:28:27 SQL> Rem after applying a patch. This minimizes latencies caused by
00:28:27 SQL> Rem on-demand recompilation. Oracle strongly recommends running this
00:28:27 SQL> Rem script after upgrades, downgrades and patches.
00:28:27 SQL> Rem
00:28:27 SQL> Rem NOTES
00:28:27 SQL> Rem * This script must be run using SQL*PLUS.
00:28:27 SQL> Rem * You must be connected AS SYSDBA to run this script.
00:28:27 SQL> Rem * There should be no other DDL on the database while running the
00:28:27 SQL> Rem script. Not following this recommendation may lead to deadlocks.
00:28:27 SQL> Rem
00:28:27 SQL> Rem BEGIN SQL_FILE_METADATA
00:28:27 SQL> Rem SQL_SOURCE_FILE: rdbms/admin/utlrp.sql
00:28:27 SQL> Rem SQL_SHIPPED_FILE: rdbms/admin/utlrp.sql
00:28:27 SQL> Rem SQL_PHASE: UTILITY
00:28:27 SQL> Rem SQL_STARTUP_MODE: NORMAL
00:28:27 SQL> Rem SQL_IGNORABLE_ERRORS: NONE
00:28:27 SQL> Rem SQL_CALLING_FILE: NONE
00:28:27 SQL> Rem END SQL_FILE_METADATA
00:28:27 SQL> Rem
00:28:27 SQL> Rem MODIFIED (MM/DD/YY)
00:28:27 SQL> Rem raeburns 03/09/17 - Bug 25616909: Use UTILITY for SQL_PHASE
00:28:27 SQL> Rem gviswana 06/26/03 - Switch default to parallel if appropriate
00:28:27 SQL> Rem gviswana 06/12/03 - Switch default back to serial
00:28:27 SQL> Rem gviswana 05/20/03 - 2814808: Automatic parallelism tuning
00:28:27 SQL> Rem rburns 04/28/03 - timestamps and serveroutput for diagnostics
00:28:27 SQL> Rem gviswana 04/13/03 - utlrcmp.sql load -> catproc
00:28:27 SQL> Rem gviswana 06/25/02 - Add documentation
00:28:27 SQL> Rem gviswana 11/12/01 - Use utl_recomp.recomp_serial
00:28:27 SQL> Rem rdecker 11/09/01 - ADD ALTER library support FOR bug 1952368
00:28:27 SQL> Rem rburns 11/12/01 - validate all components after compiles
00:28:27 SQL> Rem rburns 11/06/01 - fix invalid CATPROC call
00:28:27 SQL> Rem rburns 09/29/01 - use 9.2.0
00:28:27 SQL> Rem rburns 09/20/01 - add check for CATPROC valid
00:28:27 SQL> Rem rburns 07/06/01 - get version from instance view
00:28:27 SQL> Rem rburns 05/09/01 - fix for use with 8.1.x
00:28:27 SQL> Rem arithikr 04/17/01 - 1703753: recompile object type# 29,32,33
00:28:27 SQL> Rem skabraha 09/25/00 - validate is now a keyword
00:28:27 SQL> Rem kosinski 06/14/00 - Persistent parameters
00:28:27 SQL> Rem skabraha 06/05/00 - validate tables also
00:28:27 SQL> Rem jdavison 04/11/00 - Modify usage notes for 8.2 changes.
00:28:27 SQL> Rem rshaikh 09/22/99 - quote name for recompile
00:28:27 SQL> Rem ncramesh 08/04/98 - change for sqlplus
00:28:27 SQL> Rem usundara 06/03/98 - merge from 8.0.5
00:28:27 SQL> Rem usundara 04/29/98 - creation (split from utlirp.sql).
00:28:27 SQL> Rem Mark Ramacher (mramache) was the original
00:28:27 SQL> Rem author of this script.
00:28:27 SQL> Rem
00:28:27 SQL> 
00:28:27 SQL> Rem ===========================================================================
00:28:27 SQL> Rem BEGIN utlrp.sql
00:28:27 SQL> Rem ===========================================================================
00:28:27 SQL> 
00:28:27 SQL> @@utlprp.sql 0
00:28:27 SQL> Rem Copyright (c) 2003, 2017, Oracle and/or its affiliates.
00:28:27 SQL> Rem All rights reserved.
00:28:27 SQL> Rem
00:28:27 SQL> Rem NAME
00:28:27 SQL> Rem utlprp.sql - Recompile invalid objects in the database
00:28:27 SQL> Rem
00:28:27 SQL> Rem DESCRIPTION
..........................
..........................
..........................

Function dropped.

Elapsed: 00:00:00.01
00:37:06 SQL> SET serveroutput off
00:37:06 SQL> 
00:37:06 SQL> 
00:37:06 SQL> Rem =====================================================================
00:37:06 SQL> Rem Run component validation procedure
00:37:06 SQL> Rem =====================================================================
00:37:06 SQL> 
00:37:06 SQL> SET serveroutput on
00:37:06 SQL> EXECUTE dbms_registry_sys.validate_components;

PL/SQL procedure successfully completed.

Elapsed: 00:00:05.82
00:37:12 SQL> SET serveroutput off
00:37:12 SQL> 
00:37:12 SQL> 
00:37:12 SQL> Rem =======================================================================
00:37:12 SQL> Rem END utlrp.sql
00:37:12 SQL> Rem =======================================================================

Set COMPATIBLE parameter :

00:37:12 SQL> show parameter compatible;

NAME				     TYPE	 VALUE
------------------------------------ ----------- ----------
compatible			     string	 12.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 2365584808 bytes
Fixed Size 8660392 bytes
Variable Size 587202560 bytes
Database Buffers 1761607680 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 :

SQL> 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_prepare(l_tz_version);
END;
/ 
l_tz_version=31
A prepare window has been successfully started.

PL/SQL procedure successfully completed.

Now check the upgrade timezone version,we are going to upgrade DST_SECONDARY_TT_VERSION  is 31

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;

PROPERTY_NAME                  PROPERTY_VALUE
------------------------------ --------------------
DST_PRIMARY_TT_VERSION         26
DST_SECONDARY_TT_VERSION       31
DST_UPGRADE_STATE              PREPARE

Shutdown and startup database in normal mode.

SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP;
ORACLE instance started.

Total System Global Area 2365584808 bytes
Fixed Size 8660392 bytes
Variable Size 587202560 bytes
Database Buffers 1761607680 bytes
Redo Buffers 8114176 bytes
Database mounted.
Database opened.

Upgrade the database zone file.

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> 
Table list: "IX"."AQ$_ORDERS_QUEUETABLE_S"
Number of failures: 0
Table list: "IX"."AQ$_STREAMS_QUEUE_TABLE_S"
Number of failures: 0
Table list: "IX"."AQ$_ORDERS_QUEUETABLE_L"
Number of failures: 0
Table list: "IX"."AQ$_STREAMS_QUEUE_TABLE_L"
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.

dba_tstz_tables view displays the tables that are processed by the time zone file upgrade, and their current upgrade status.

SQL> SELECT owner,table_name,upgrade_in_progress FROM   dba_tstz_tables
ORDER BY 1,2;

Once the upgrade is complete, check the time zone file version.

SQL> SELECT * FROM v$timezone_file;

FILENAME             VERSION    CON_ID
-------------------- ---------- -------
timezlrg_31.dat      31         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; 

PROPERTY_NAME                  PROPERTY_VALUE
------------------------------ --------------
DST_PRIMARY_TT_VERSION         31
DST_SECONDARY_TT_VERSION       0
DST_UPGRADE_STATE              NONE

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/preupgrade1/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-08 22:48:34
For Source Database: ORCLDB
Source Database Version: 12.2.0.1.0
For Upgrade to Version: 18.0.0.0.0

Preup                             Preupgrade
Action                            Issue Is
Number   Preupgrade Check Name    Remedied       Further DBA Action
------ ------------------------ ---------- ----------------------------
3.       old_time_zones_exist      YES       None.
4.       post_dictionary           YES       None.
5.       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

UNIX Shell Script To Run DataPump Jobs

Description:-

We  want to create a shell script to run export/import utilities.

Script to run 

# SCRIPT 
# ---------
# 
# Set environment variables for ORACLE_HOME, ORACLE_SID, 
# and DUMPDIR -- in case of datapump --, as well as PATH if needed.
#
#
trap '' 1 # to use nohup in a shell script 
# Set these to appropriate values if needed:
#
#ORACLE_HOME=
#ORACLE_SID= 
#DUMPDIR= 
# Customize PATH if needed 
#
#PATH=/bin:/usr/bin:${ORACLE_HOME}/bin:/usr/local/bin:/usr/lbin 
#export ORACLE_HOME ORACLE_SID PATH 
echo "Exporting $ORACLE_SID database. start `date`" 
# 
expdp system/password dumpfile=scott.dmp directory=DUMPDIR schemas=scott logfile=scott.log
#
echo "Export of $ORACLE_SID database completed at `date`" 
#End of Script

Running the script

If you save the script as exp_comp.sh, type the following command at the Unix prompt to run it:

#> ./exp_comp.sh

Note: Edit the above script to use it for your specific exports or imports (like full, schema, table, TTS).

In case of traditional export/imp (exp/imp), the above command may be something like:

exp system/manager file=scott.dmp owner=scott log=scott.log

Reference

UNIX Shell Script To Run DataPump Or Traditional Export Or Import Utilities (Doc ID 1154409.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

 

 

 

Oracle Database Cloning Using Cold Backup

Cold database backup means while taking backup or doing cloning, we need to shutdown the source database. This method is usually used for test database when database is in noarchivelog mode.

Note- Both the source and target db server should be on same platform and the target db version will be that of the source db.So make sure oracle binary is already installed on target db server.

SOURCE DB – jupiter
TARGET DB – sakthi

Steps:

1.Take backup of controlfile as trace:[SOURCE DB]

2. Note down the location of datafiles[SOURCE DB]

3. Shutdown the database:[SOURCE DB]

4. Copy the data files and temp files to the target db server

5. Prepare the init file for target db:[TARGET DB]

6. Start the database in nomount stage:[TARGET DB]

7. Re-recreate the controlfile [ TARGET DB ]

8. Open the database in resetlog mode

Send the datafile,log files and control files to target destination

 

[oracle@jupiter rock]$ scp -r *.log oracle@192.168.1.135:/u01/ram

The authenticity of host '192.168.1.135 (192.168.1.135)' can't be established.

RSA key fingerprint is 9b:35:ae:ab:bf:6b:33:b4:43:86:f5:98:8b:bb:11:1c.

Are you sure you want to continue connecting (yes/no)? oracle

Please type 'yes' or 'no': yes

Warning: Permanently added '192.168.1.135' (RSA) to the list of known hosts.

oracle@192.168.1.135's password:

redo01.log                     100%  200MB   3.1MB/s   01:05    

redo02.log                     100%  200MB   4.0MB/s   00:50    

redo03.log                     100%  200MB   3.5MB/s   00:58    

[oracle@jupiter rock]$ scp -r users01.dbf oracle@192.168.1.135:/u01/ram

oracle@192.168.1.135's password:

users01.dbf                  100% 5128KB   5.0MB/s   00:01    

[oracle@jupiter rock]$ scp -r undotbs01.dbf oracle@192.168.1.135:/u01/ram

oracle@192.168.1.135's password:

undotbs01.dbf               100%   70MB   3.7MB/s   00:19    

[oracle@jupiter rock]$ scp -r temp01.dbf oracle@192.168.1.135:/u01/ram

oracle@192.168.1.135's password:

temp01.dbf                100%   32MB   2.9MB/s   00:11    

[oracle@jupiter rock]$ scp -r system01.dbf oracle@192.168.1.135:/u01/ram

oracle@192.168.1.135's password:

system01.dbf             100%  810MB   3.0MB/s   04:31    

[oracle@jupiter rock]$ scp -r sysaux01.dbf oracle@192.168.1.135:/u01/ram

oracle@192.168.1.135's password:

sysaux01.dbf            100%  490MB   3.2MB/s   02:34    

[oracle@jupiter u01]$ scp -r ctrl.sql oracle@192.168.1.135:/u01/ram
oracle@192.168.1.135's password:

ctrl.sql               100% 5865     5.7KB/s   00:00

Now all the files are in the target destination.

[oracle@sakthi ram]$ ls

ctrl.sql         redo01.log  redo03.log    system01.dbf  undotbs01.dbf

initjupiter.ora  redo02.log  sysaux01.dbf  temp01.dbf    users01.dbf

Edit the control file

[oracle@sakthi ram]$ vi ctrl.sql

CREATE CONTROLFILE SET  DATABASE "ROCK" RESETLOGS ARCHIVELOG

    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/ram/redo01.log'  SIZE 200M BLOCKSIZE 512,
  GROUP 2 '/u01/ram/redo02.log'  SIZE 200M BLOCKSIZE 512,
  GROUP 3 '/u01/ram/redo03.log'  SIZE 200M BLOCKSIZE 512
DATAFILE
  '/u01/ram/system01.dbf',
  '/u01/ram/sysaux01.dbf',
  '/u01/ram/undotbs01.dbf',
  '/u01/ram/users01.dbf'
CHARACTER SET AL32UTF8 

Edit the PFILE

[oracle@sakthi ram]$ vi initjupiter.ora

db_name=rock
control_files='/u01/ram/ctrl.ctl'

NOW open the database.

[oracle@sakthi ram]$ export ORACLE_SID=rock

[oracle@sakthi ram]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Fri Jan 18 01:20:35 2019
Copyright (c) 1982, 2016, Oracle.  All rights reserved.
Connected to an idle instance.

SQL> startup pfile='/u01/ram/initjupiter.ora' nomount

ORACLE instance started.
Total System Global Area  243269632 bytes
Fixed Size                  8619256 bytes
Variable Size             180357896 bytes
Database Buffers           50331648 bytes
Redo Buffers                3960832 bytes

SQL> @ctrl.sql
Control file created.

SQL> alter database open resetlogs;
Database altered.

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
------    -------------
ROCK      READ WRITE
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

Connecting To An Autonomous Transaction Processing Database

Description:-

This topic gives an overview of connecting a client to an Autonomous Transaction Processing database and describes how to obtain the credentials and information you need to create a connection.

Applications and tools connect to Autonomous Transaction Processing databases by using Oracle Net Services (also known as SQL*Net). SQL*Net supports a variety of connection types to Autonomous Transaction Processing databases, including Oracle Call Interface (OCI), ODBC drivers, JDBC OC, and JDBC Thin Driver.

To support connections of any type, you’ll need to download the client security credentials and network configuration settings required to access your database. You’ll also need to supply the applicable TNS names or connection strings for a connection, depending on the client application or tool, type of connection, and service level. You can view or copy the TNS names and connection strings in the DB Connection dialog for your Autonomous Transaction Processing database.

About Downloading Client Credentials
The client credentials .zip that you download contains the following files:

cwallet.sso – Oracle auto-login wallet
ewallet.p12 – PKCS #12 wallet file associated with the auto-login wallet
sqlnet.ora – SQL*Net profile configuration file that includes the wallet location and TNSNAMES naming method
tnsnames.ora – SQL*Net configuration file that contains network service names mapped to connect descriptors for the local naming method
Java Key Store (JKS) files – Key store files for use with JDBC Thin Connections

Wallet files, along with the database user ID and password, provide access to data in your Autonomous Transaction Processing database. Store wallet files in a secure location. Share wallet files only with authorized users. If wallet files are transmitted in a way that might be accessed by unauthorized users (for example, over public email), transmit the wallet password separately and securely

To access the client credentials and connection information for your Autonomous Transaction Processing database

Step1:-Open the navigation menu. Under Database, click Autonomous Transaction Processing.Step2:-Choose your Compartment.

Step3:-In the list of Autonomous Transaction Processing databases, click on the display name of the database you are interested in.

Step4:-Click DB Connection.Step5:-To obtain the client credentials, click Download.

You will be prompted to provide a password to encrypt the keys inside the wallet. The password must be at least 8 characters long and must include at least 1 letter and either 1 numeric character or 1 special character.Save the client credentials zip file to a secure location.

Catch Me On:- Hariprasath Rajaram 

Telegram:https://t.me/joinchat/I_f4DkeGfZuxgMIoJSpQZg 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

Creating An Autonomous Transaction Processing Database

Description:-

This topic describes how to provision a new Autonomous Transaction Processing database using the Oracle Cloud Infrastructure Console

To create an Autonomous Transaction Processing Database

Step1:-  Click Services to show the available services. In the list of available services, select Autonomous Transaction Processing.

Step2:-The console for Autonomous Transaction Processing displays. You can use the List Scope drop-down menu to select a compartment;

This console shows no databases. If there were a long list of databases, you could filter the list by using the Filters drop-down menu to filter by the state of the databases (available, stopped, terminated, and so on.)

Click Create Autonomous Transaction Processing Database to create a database instance.

Step3:-The Create Autonomous Transaction Processing Database dialog appears. At the top of the dialog, select a compartment for the database from the drop-down list.

In the Create Autonomous Transaction Processing Database dialog, enter the following:
Display Name,Database Name,CPU Core Count,Storage,Administrator Credentials,License Type,Tags

Step4:-Click Create Autonomous Transaction Processing Database.

Database creation in Progress

Step5:- Database ready for use

Successfully Created Autonomous Transaction Processing Database

 

Catch Me On:- Hariprasath Rajaram 

Telegram:https://t.me/joinchat/I_f4DkeGfZuxgMIoJSpQZg 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

Overview Of Oracle Autonomous Transaction Processing

Description:-

  1. Autonomous Transaction Processing is a self-driving database, meaning it eliminates the human labor needed to provision, secure, update, monitor, backup, and troubleshoot a database.
  2. Oracle Cloud Infrastructure’s Autonomous Transaction Processing Cloud Service is a fully managed, preconfigured database environment. You do not need to configure or manage any hardware, or install any software. After provisioning, you can scale the number of CPU cores or the storage capacity of the database at any time without impacting availability or performance. Autonomous Transaction Processing handles creating the database, as well as the following maintenance tasks:

    Backing up the database
    Patching the database
    Upgrading the database
    Tuning the database of Oracle Autonomous Transaction Processing

  3. This reduction in database maintenance tasks reduces costs and frees scarce administrator resources to work on higher-value tasks. Oracle Autonomous Transaction Processing supports mixed workloads for data warehousing and transaction processing.
  4. The newly introduced Oracle Autonomous Transaction Processing Cloud automates the entire data management life cycle.
  5. Self-Driving
    Leverages machine learning to automate database and infrastructure management, monitoring, and tuning
  6. Self-Securing
    Protects from both external attacks and malicious internal users.
    Isolates operational users from application data and ensures up to
    date security patching via software update automation
  7. Self-Repairing
    Protects from all downtime including planned maintenance and
    online application schema updates
  8. Oracle guarantees 99.995 percent availability—that’s less than 2.5 minutes of downtime per month, including patching. Autonomous management lowers administration costs, so you can focus on higher value projects.
  9. Same Experience us Autonomous Transaction Processing uses the same Oracle Database software and technology that runs your existing on-premises database applications, making it compatible with all your existing tools and skill sets.

Key Features of Autonomous Transaction Processing

Managed: Oracle simplifies end-to-end management of the database:

Provisioning new databases
Growing or shrinking storage and compute resources
Patching and upgrades
Backup and recovery

Fully elastic scaling:

Scale compute and storage independently to fit your database workload with no downtime:
Size the Autonomous Transaction Processing to the exact compute and storage required
Scale the Autonomous Transaction Processing on demand: Independently scale compute or storage
Shut off idle compute to save money

Autonomous Transaction Processing supports:

Existing applications, running in the cloud or on-premises
Connectivity via SQL*Net, JDBC, ODBC
Third-party data-integration tools
Oracle cloud services: Analytics Cloud Service, Golden Gate Cloud Service, Integration Cloud Service, and others

Simple Cloud-based Data Loading

Autonomous Transaction Processing provides:
Fast, scalable data-loading from Oracle Object Store, Azure Blob Storage, AWS S3, or on-premises data sources.

Connect to Your Autonomous Transaction Processing

As a database application developer, business user, or DBA, Autonomous Transaction Processing lets you use all your existing design, data integration, analysis, and reporting tools.

Application Developers
Build new applications using use any of the supported client drivers including JDBC, .NET, Python, Node.js, PHP, C/C++, and more.

Business Users
Connect rapid application development tools like APEX* and other standard
reporting tools through JDBC or SQL*Net.

Database Administrators
SQL Developer provides a full database development environment:
SQL Worksheet, data loading wizards, data modeling, and more tailored to the optimized features of Autonomous Transaction Processing.
Create tables, indexes, and materialized views in Autonomous Transaction Processing
Load data into Autonomous Transaction Processing
Copy tables to Autonomous Transaction Processing
Transfer a schema to Autonomous Transaction Processing

Catch Me On:- Hariprasath Rajaram 

Telegram:https://t.me/joinchat/I_f4DkeGfZuxgMIoJSpQZg 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