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