Introduction:
There are different methods to upgrade an Oracle database from a lower version to a higher version.
Oracle provides multiple methods to upgrade based on the version. A few of them have been listed below:
Database Upgrade Assistant (DBUA)
Manual Upgrade
Transportable Tablespaces
Data Pump export/import
Oracle Streams
Oracle GoldenGate
Here in this blog, we are going to see the upgrade of the Oracle database from version 12c to 19c using the Manual Upgrade Method.
Environment
Hostname : Oracle12c.localdomain
Database Name : Oracle12c
Version : 12.2.0.1
Cdb : Non-Cdb, Single Instance
Db home path : /u01/app/oracle/product/12.2.0/dbhome_1
Datafile location : /u01/App/Oracle/Oradata/
Target db version : 19c (19.3.0.0.0)
Target Db Path : /u01/app/oracle/product/19.0.0/dbhome_1
Upgrade Method : Manual Upgrade
Backup
** Database Backup
** tnsnames.ora
** listener.ora
** sqlnet.ora
** pfile and spfile
** orapw file
Step 1: create a new directory for backup and upgrade.
[oracle@oracle12c u01]$ mkdir backup [oracle@oracle12c u01]$ ls app backup oradata [oracle@oracle12c u01]$ mkdir preupgrade [oracle@oracle12c u01]$ ls app backup oradata preupgrade
Step 2: Take backup of listener.ora,tnsnames.ora,spfile,password file.
[oracle@oracle12c ~]$ cd /u01/app/oracle/product/12.2.0/db_1/network/admin/ [oracle@oracle12c admin]$ cp -p listener.ora sqlnet.ora tnsnames.ora /u01/backup [oracle@oracle12c admin]$ ls -ltr /u01/backup/*.ora -rwxrwxr-x. 1 oracle oinstall 743 Feb 15 12:58 /u01/backup/listener.ora -rwxrwxr-x. 1 oracle oinstall 202 Feb 15 12:59 /u01/backup/sqlnet.ora -rwxrwxr-x. 1 oracle oinstall 816 Feb 15 13:02 /u01/backup/tnsnames.ora [oracle@oracle12c dbs]$ pwd /u01/app/oracle/product/12.2.0/dbhome_1/dbs [oracle@oracle12c dbs]$ cp -p spfileoracle12c.ora orapworacle12c /u01/backup/
Step 3: Run preupgrade tool.
[oracle@oracle19c ~]$ /u01/app/oracle/product/12.2.0/db_1/jdk/bin/java -jar /u01/app/oracle/product/19.0.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
Step 4: View Preupgrade log
[oracle@oracle19c preupgrade]$ cat preupgrade.log Report generated by Oracle Database Pre-Upgrade Information Tool Version 19.0.0.0.0 Build: 1 on 2021-02-12T10:35:29 Upgrade-To version: 19.0.0.0.0 ======================================= Status of the database prior to upgrade ======================================= Database Name: ORACLE12 Container Name: oracle12 Container ID: 0 Version: 12.2.0.1.0 DB Patch Level: No Patch Bundle applied Compatible: 12.2.0 Blocksize: 8192 Platform: Linux x86 64-bit Timezone File: 26 Database log mode: NOARCHIVELOG 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 Real Application Clusters [to be upgraded] OPTION OFF Oracle Workspace Manager [to be upgraded] VALID OLAP Analytic Workspace [to be upgraded] VALID Oracle Label Security [to be upgraded] VALID Oracle Database Vault [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 Oracle OLAP API [to be upgraded] VALID 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 470 MB 500 MB SYSTEM 800 MB 912 MB TEMP 32 MB 150 MB UNDOTBS1 65 MB 439 MB Minimum tablespace sizes for upgrade are estimates.
Step 5: Gather Dictionary Stats.
SET ECHO ON; SET SERVEROUTPUT ON; EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS; PL/SQL procedure successfully completed.
Step 6: Purge Recyclebin
SQL> PURGE DBA_RECYCLEBIN; DBA Recyclebin purged.
Step 7: Refresh MVs
SQL> declare list_failures integer(3) :=0; begin DBMS_MVIEW.REFRESH_ALL_MVIEWS(list_failures,'C','', TRUE, FALSE); end; / PL/SQL procedure successfully completed.
Step 8: Run preupgrade_fixups.sql
SQL> @/u01/preupgrade/preupgrade_fixups.sql Executing Oracle PRE-Upgrade Fixup Script Auto-Generated by: Oracle Preupgrade Script Version: 19.0.0.0.0 Build: 1 Generated on: 2021-02-12 10:35:26 For Source Database: ORACLE12 Source Database Version: 12.2.0.1.0 For Upgrade to Version: 19.0.0.0.0 Preup Preupgrade Action Issue Is Number Preupgrade Check Name Remedied Further DBA Action ------ ---------------------- ------ --------------------- 1 uniaud_records_in_file YES None 2 dictionary_stats YES None 3 pre_fixed_objects YES None 4 tablespaces_info NO Informational only Further action is optional. 5 rman_recovery_version NO Informational only Further action is optional. The fixup scripts have been run and resolved what they can. However, there are still issues originally identified by the preupgrade that have not been remedied and are still present in the databas 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.
Step 9: Verify archive log dest.
SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 3 Next log sequence to archive 5 Current log sequence 5
Step 10: Stop LISTENER
[oracle@oracle19c admin]$ lsnrctl status LISTENER LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 16-FEB-2021 07:44:27 Copyright (c) 1991, 2016, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.23)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias cloud Version TNSLSNR for Linux: Version 12.2.0.1.0 - Production Start Date 16-FEB-2021 07:42:48 Uptime 0 days 0 hr. 1 min. 39 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/12.2.0/db_1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/oracle19c/cloud/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.23)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521))) Services Summary... Service "oracle12c.localdomain" has 1 instance(s). Instance "oracle12c", status READY, has 1 handler(s) for this service... The command completed successfully [oracle@oracle19c admin]$ lsnrctl stop LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 16-FEB-2021 07:42:15 Copyright (c) 1991, 2016, Oracle. All rights reserved. Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) The command completed successfully
Step 11: Create Flashback Guaranteed Restore Point
[oracle@oracle19c admin]$ sqlplus / as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Tue Feb 16 07:46:37 2021 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL> select flashback_on from v$database; FLASHBACK_ON ------------------ NO SQL> select name,open_mode,log_mode from v$database; NAME OPEN_MODE LOG_MODE --------- ----------------- ------------ ORACLE12 MOUNTED ARCHIVELOG SQL> show parameter db_recovery_file_dest_size NAME TYPE VALUE -------------------------- ----------- -------- db_recovery_file_dest_size big integer 8016M SQL> alter system set db_recovery_file_dest_size=11g; System altered. SQL> create restore point pre_upgrade guarantee flashback database; Restore point created.
Step 12: Shutdown Database
[oracle@Prod21 ~]$ sqlplus / as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Tue Feb 16 07:05:59 2020 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down.
Step 13: Copy init and password files from 12c to 19c dbs home
[oracle@oracle19c dbs]$ ls hc_oracle12c.dat init.ora lkORACLE12 lkORACLE12C orapworacle12c spfileoracle12c.ora [oracle@oracle19c dbs]$ cp spfileoracle12c.ora init.ora orapworacle12c /u01/app/oracle/product/19.0.0/db_1/dbs/
Step 14: Startup DB in Upgrade mode from 19c home
[oracle@oracle19c scripts]$ . ora19cEnv.sh [oracle@oracle19c scripts]$ export ORACLE_SID=oracle12c [oracle@oracle19c scripts]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Tue Feb 16 07:55:30 2021 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to an idle instance. SQL> startup upgrade; ORACLE instance started. Total System Global Area 2684354104 bytes Fixed Size 8900152 bytes Variable Size 570425344 bytes Database Buffers 2097152000 bytes Redo Buffers 7876608 bytes Database mounted. Database opened. SQL> select name,open_mode,cdb,version,status from v$database,v$instance; NAME OPEN_MODE CDB VERSION STATUS ------- -------------- --- ---------- ------------ ORACLE12 READ WRITE NO 19.0.0.0.0 OPEN MIGRATE
Step 15: Run dbupgrade.
All things will be done automatically no manual intervention needed after running ./dbupgrade
[oracle@oracle19c ~]$ $ORACLE_HOME/bin/dbupgrade Argument list for [/u01/app/oracle/product/19.0.0/db_1/rdbms/admin/catctl.pl] For Oracle internal use only A = 0 Run in c = 0 Do not run in C = 0 Input Directory d = 0 Echo OFF e = 1 Simulate E = 0 Forced cleanup F = 0 Log Id i = 0 Child Process I = 0 Log Dir l = 0 Priority List Name L = 0 Upgrade Mode active M = 0 SQL Process Count n = 0 SQL PDB Process Count N = 0 Open Mode Normal o = 0 Start Phase p = 0 End Phase P = 0 Reverse Order r = 0 AutoUpgrade Resume R = 0 Script s = 0 Serial Run S = 0 RO User Tablespaces T = 0 Display Phases y = 0 Debug catcon.pm z = 0 Debug catctl.pl Z = 0 catctl.pl VERSION: [19.0.0.0.0] STATUS: [Production] BUILD: [RDBMS_19.3.0.0.0DBRU_LINUX.X64_190417] /u01/app/oracle/product/19.0.0/db_1/rdbms/admin/orahome = [/u01/app/oracle/product/19.0.0/db_1] /u01/app/oracle/product/19.0.0/db_1/bin/orabasehome = [/u01/app/oracle/product/19.0.0/db_1] catctlGetOraBaseLogDir = [/u01/app/oracle/product/19.0.0/db_1] Analyzing file /u01/app/oracle/product/19.0.0/db_1/rdbms/admin/catupgrd.sql Log file directory = [/tmp/cfgtoollogs/upgrade20210216080439] catcon::set_log_file_base_path: ALL catcon-related output will be written to [/tmp/cfgtoollogs/upgrade20210216080439/catupgrd_catcon_9901.lst] catcon::set_log_file_base_path: catcon: See [/tmp/cfgtoollogs/upgrade20210216080439/catupgrd*.log] files for output generated by scripts catcon::set_log_file_base_path: catcon: See [/tmp/cfgtoollogs/upgrade20210216080439/catupgrd_*.lst] files for spool files, if any Number of Cpus = 1 Database Name = oracle12c DataBase Version = 12.2.0.1.0 catcon::set_log_file_base_path: ALL catcon-related output will be written to [/u01/app/oracle/product/19.0.0/db_1/cfgtoollogs/oracle12c/upgrade20210216080457/catupgrd_catcon_9901.lst] catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/product/19.0.0/db_1/cfgtoollogs/oracle12c/upgrade20210216080457/catupgrd*.log] files for output generated by scripts catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/product/19.0.0/db_1/cfgtoollogs/oracle12c/upgrade20210216080457/catupgrd_*.lst] files for spool files, if any Log file directory = [/u01/app/oracle/product/19.0.0/db_1/cfgtoollogs/oracle12c/upgrade20210216080457] Parallel SQL Process Count = 4 Components in [oracle12c] 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-107] Start Time:[2021_02_16 08:05:11] ------------------------------------------------------ *********** Executing Change Scripts *********** Serial Phase #:0 [oracle12c] Files:1 Time: 65s *************** Catalog Core SQL *************** Serial Phase #:1 [oracle12c] Files:5 Time: 34s Restart Phase #:2 [oracle12c] Files:1 Time: 3s *********** Catalog Tables and Views *********** Parallel Phase #:3 [oracle12c] Files:19 Time: 32s Restart Phase #:4 [oracle12c] Files:1 Time: 4s ************* Catalog Final Scripts ************ Serial Phase #:5 [oracle12c] Files:7 Time: 12s ***************** Catproc Start **************** Serial Phase #:6 [oracle12c] Files:1 Time: 13s ***************** Catproc Types **************** Serial Phase #:7 [oracle12c] Files:2 Time: 16s Restart Phase #:8 [oracle12c] Files:1 Time: 4s **************** Catproc Tables **************** Parallel Phase #:9 [oracle12c] Files:67 Time: 51s Restart Phase #:10 [oracle12c] Files:1 Time: 4s ************* Catproc Package Specs ************ Serial Phase #:11 [oracle12c] Files:1 Time: 125s Restart Phase #:12 [oracle12c] Files:1 Time: 4s ************** Catproc Procedures ************** Parallel Phase #:13 [oracle12c] Files:94 Time: 20s Restart Phase #:14 [oracle12c] Files:1 Time: 5s Parallel Phase #:15 [oracle12c] Files:120 Time: 34s Restart Phase #:16 [oracle12c] Files:1 Time: 2s Serial Phase #:17 [oracle12c] Files:22 Time: 4s Restart Phase #:18 [oracle12c] Files:1 Time: 1s ***************** Catproc Views **************** Parallel Phase #:19 [oracle12c] Files:32 Time: 30s Restart Phase #:20 [oracle12c] Files:1 Time: 4s Serial Phase #:21 [oracle12c] Files:3 Time: 13s Restart Phase #:22 [oracle12c] Files:1 Time: 4s Parallel Phase #:23 [oracle12c] Files:25 Time: 158s Restart Phase #:24 [oracle12c] Files:1 Time: 4s Parallel Phase #:25 [oracle12c] Files:12 Time: 92s Restart Phase #:26 [oracle12c] Files:1 Time: 4s Serial Phase #:27 [oracle12c] Files:1 Time: 0s Serial Phase #:28 [oracle12c] Files:3 Time: 4s Serial Phase #:29 [oracle12c] Files:1 Time: 0s Restart Phase #:30 [oracle12c] Files:1 Time: 2s *************** Catproc CDB Views ************** Serial Phase #:31 [oracle12c] Files:1 Time: 2s Restart Phase #:32 [oracle12c] Files:1 Time: 3s Serial Phase #:34 [oracle12c] Files:1 Time: 0s ***************** Catproc PLBs ***************** Serial Phase #:35 [oracle12c] Files:293 Time: 22s Serial Phase #:36 [oracle12c] Files:1 Time: 0s Restart Phase #:37 [oracle12c] Files:1 Time: 4s Serial Phase #:38 [oracle12c] Files:6 Time: 5s Restart Phase #:39 [oracle12c] Files:1 Time: 3s *************** Catproc DataPump *************** Serial Phase #:40 [oracle12c] Files:3 Time: 42s Restart Phase #:41 [oracle12c] Files:1 Time: 5s ****************** Catproc SQL ***************** Parallel Phase #:42 [oracle12c] Files:13 Time: 109s Restart Phase #:43 [oracle12c] Files:1 Time: 3s Parallel Phase #:44 [oracle12c] Files:11 Time: 11s Restart Phase #:45 [oracle12c] Files:1 Time: 2s Parallel Phase #:46 [oracle12c] Files:3 Time: 4s Restart Phase #:47 [oracle12c] Files:1 Time: 3s ************* Final Catproc scripts ************ Serial Phase #:48 [oracle12c] Files:1 Time: 6s Restart Phase #:49 [oracle12c] Files:1 Time: 2s ************** Final RDBMS scripts ************* Serial Phase #:50 [oracle12c] Files:1 Time: 4s ************ Upgrade Component Start *********** Serial Phase #:51 [oracle12c] Files:1 Time: 2s Restart Phase #:52 [oracle12c] Files:1 Time: 3s ********** Upgrading Java and non-Java ********* Serial Phase #:53 [oracle12c] Files:2 Time: 274s ***************** Upgrading XDB **************** Restart Phase #:54 [oracle12c] Files:1 Time: 3s Serial Phase #:56 [oracle12c] Files:3 Time: 8s Serial Phase #:57 [oracle12c] Files:3 Time: 6s Parallel Phase #:58 [oracle12c] Files:10 Time: 5s Parallel Phase #:59 [oracle12c] Files:25 Time: 8s Serial Phase #:60 [oracle12c] Files:4 Time: 8s Serial Phase #:61 [oracle12c] Files:1 Time: 0s Serial Phase #:62 [oracle12c] Files:32 Time: 5s Serial Phase #:63 [oracle12c] Files:1 Time: 0s Parallel Phase #:64 [oracle12c] Files:6 Time: 9s Serial Phase #:65 [oracle12c] Files:2 Time: 16s Serial Phase #:66 [oracle12c] Files:3 Time: 21s **************** Upgrading ORDIM *************** Restart Phase #:67 [oracle12c] Files:1 Time: 3s Serial Phase #:69 [oracle12c] Files:1 Time: 4s Parallel Phase #:70 [oracle12c] Files:2 Time: 26s Restart Phase #:71 [oracle12c] Files:1 Time: 4s Parallel Phase #:72 [oracle12c] Files:2 Time: 4s Serial Phase #:73 [oracle12c] Files:2 Time: 5s ***************** Upgrading SDO **************** Restart Phase #:74 [oracle12c] Files:1 Time: 3s Serial Phase #:76 [oracle12c] Files:1 Time: 32s Serial Phase #:77 [oracle12c] Files:2 Time: 5s Restart Phase #:78 [oracle12c] Files:1 Time: 4s Serial Phase #:79 [oracle12c] Files:1 Time: 36s Restart Phase #:80 [oracle12c] Files:1 Time: 4s Parallel Phase #:81 [oracle12c] Files:3 Time: 63s Restart Phase #:82 [oracle12c] Files:1 Time: 3s Serial Phase #:83 [oracle12c] Files:1 Time: 7s Restart Phase #:84 [oracle12c] Files:1 Time: 2s Serial Phase #:85 [oracle12c] Files:1 Time: 11s Restart Phase #:86 [oracle12c] Files:1 Time: 1s Parallel Phase #:87 [oracle12c] Files:4 Time: 97s Restart Phase #:88 [oracle12c] Files:1 Time: 3s Serial Phase #:89 [oracle12c] Files:1 Time: 5s Restart Phase #:90 [oracle12c] Files:1 Time: 2s Serial Phase #:91 [oracle12c] Files:2 Time: 10s Restart Phase #:92 [oracle12c] Files:1 Time: 3s Serial Phase #:93 [oracle12c] Files:1 Time: 2s Restart Phase #:94 [oracle12c] Files:1 Time: 3s ******* Upgrading ODM, WK, EXF, RUL, XOQ ******* Serial Phase #:95 [oracle12c] Files:1 Time: 10s Restart Phase #:96 [oracle12c] Files:1 Time: 1s *********** Final Component scripts *********** Serial Phase #:97 [oracle12c] Files:1 Time: 3s ************* Final Upgrade scripts ************ Serial Phase #:98 [oracle12c] Files:1 Time: 190s ******************* Migration ****************** Serial Phase #:99 [oracle12c] Files:1 Time: 2s *** End PDB Application Upgrade Pre-Shutdown *** Serial Phase #:100 [oracle12c] Files:1 Time: 2s Serial Phase #:101 [oracle12c] Files:1 Time: 0s Serial Phase #:102 [oracle12c] Files:1 Time: 48s ***************** Post Upgrade ***************** Serial Phase #:103 [oracle12c] Files:1 Time: 21s **************** Summary report **************** Serial Phase #:104 [oracle12c] Files:1 Time: 3s *** End PDB Application Upgrade Post-Shutdown ** Serial Phase #:105 [oracle12c] Files:1 Time: 3s Serial Phase #:106 [oracle12c] Files:1 Time: 0s Serial Phase #:107 [oracle12c] Files:1 Time: 57s ------------------------------------------------------ Phases [0-107] End Time:[2021_02_16 08:38:59] ------------------------------------------------------ Grand Total Time: 2029s LOG FILES: (/u01/app/oracle/product/19.0.0/db_1/cfgtoollogs/oracle12c/upgrade20210216080457/catupgrd*.log) Upgrade Summary Report Located in: /u01/app/oracle/product/19.0.0/db_1/cfgtoollogs/oracle12c/upgrade20210216080457/upg_summary.log Grand Total Upgrade Time: [0d:0h:33m:49s]
Step 16: Startup DB from 19c home.
[oracle@oracle19c ~]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Tue Feb 16 08:42:53 2021 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to an idle instance. SQL> startup ORACLE instance started. Total System Global Area 2684354104 bytes Fixed Size 8900152 bytes Variable Size 671088640 bytes Database Buffers 1996488704 bytes Redo Buffers 7876608 bytes Database mounted. Database opened. SQL> select name,open_mode,cdb,version,status from v$database,v$instance; NAME OPEN_MODE CDB VERSION STATUS ------- ------------- --- ------------ ------------ ORACLE12 READ WRITE NO 19.0.0.0.0 OPEN Once Upgrade completes,Verify the Component,version and status from DBA_REGISTRY.
Step 17: Run utlrp.sql
SQL> @/u01/app/oracle/product/19.0.0/db_1/rdbms/admin/utlrp.sql Session altered. TIMESTAMP ------------------------------------------------------ COMP_TIMESTAMP UTLRP_BGN 2021-02-16 08:47:13 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 2021-02-16 08:52:01 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 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. The fixup scripts have been run and resolved what they can. However, there are still issues originally identified by the preupgrade that have not been remedied and are still present in the database. Depending on the severity of the specific issue, and the nature of the issue itself, that could mean that your database upgrade is not fully complete. To resolve the outstanding issues, start by reviewing the postupgrade_fixups.sql and searching it for the name of the failed CHECK NAME or Preupgrade Action Number listed above. There you will find the original corresponding diagnostic message from the preupgrade which explains in more detail what still needs to be done. PL/SQL procedure successfully completed. Session altered.
Step 18: Upgrade Timezone
[oracle@oracle19c ~]$ cd /u01/app/oracle/product/19.0.0/db_1/rdbms/admin/ [oracle@oracle19c admin]$ ls -ltr utltz_countstats.sql utltz_countstar.sql utltz_upg_check.sql utltz_upg_apply.sql -rwxrwxr-x. 1 oracle oinstall 8317 Feb 25 2017 utltz_countstats.sql -rwxrwxr-x. 1 oracle oinstall 7423 Feb 25 2017 utltz_countstar.sql -rwxrwxr-x. 1 oracle oinstall 33684 Sep 9 2017 utltz_upg_check.sql -rwxrwxr-x. 1 oracle oinstall 21526 Sep 9 2017 utltz_upg_apply.sql [oracle@oracle19c admin]$ [oracle@oracle19c admin]$ [oracle@oracle19c admin]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Tue Feb 16 09:03:50 2021 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0 SQL> SELECT version FROM v$timezone_file; VERSION ---------- 26 SQL> @/u01/app/oracle/product/19.0.0/db_1/rdbms/admin/utltz_upg_check.sql Session altered. INFO: Starting with RDBMS DST update preparation. INFO: NO actual RDBMS DST update will be done by this script. INFO: If an ERROR occurs the script will EXIT sqlplus. INFO: Doing checks for known issues ... INFO: Database version is 19.0.0.0 . INFO: Database RDBMS DST version is DSTv26 . INFO: No known issues detected. INFO: Now detecting new RDBMS DST version. A prepare window has been successfully started. INFO: Newest RDBMS DST version detected is DSTv32 . INFO: Next step is checking all TSTZ data. INFO: It might take a while before any further output is seen ... A prepare window has been successfully ended. INFO: A newer RDBMS DST version than the one currently used is found. INFO: Note that NO DST update was yet done. INFO: Now run utltz_upg_apply.sql to do the actual RDBMS DST update. INFO: Note that the utltz_upg_apply.sql script will INFO: restart the database 2 times WITHOUT any confirmation or prompt. Session altered. SQL> @/u01/app/oracle/product/19.0.0/db_1/rdbms/admin/utltz_upg_apply.sql Session altered. INFO: If an ERROR occurs, the script will EXIT SQL*Plus. INFO: The database RDBMS DST version will be updated to DSTv32 . WARNING: This script will restart the database 2 times WARNING: WITHOUT asking ANY confirmation. WARNING: Hit control-c NOW if this is not intended. INFO: Restarting the database in UPGRADE mode to start the DST upgrade. Database closed. Database dismounted. ORACLE instance shut down. ORACLE instance started. Total System Global Area 2684354104 bytes Fixed Size 8900152 bytes Variable Size 687865856 bytes Database Buffers 1979711488 bytes Redo Buffers 7876608 bytes Database mounted. Database opened. INFO: Starting the RDBMS DST upgrade. INFO: Upgrading all SYS owned TSTZ data. INFO: It might take time before any further output is seen ... An upgrade window has been successfully started. INFO: Restarting the database in NORMAL mode to upgrade non-SYS TSTZ data. Database closed. Database dismounted. ORACLE instance shut down. ORACLE instance started. Total System Global Area 2684354104 bytes Fixed Size 8900152 bytes Variable Size 687865856 bytes Database Buffers 1979711488 bytes Redo Buffers 7876608 bytes Database mounted. Database opened. INFO: Upgrading all non-SYS TSTZ data. INFO: It might take time before any further output is seen ... INFO: Do NOT start any application yet that uses TSTZ data! INFO: Next is a list of all upgraded tables: Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_L" Number of failures: 0 Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_S" Number of failures: 0 Table list: "MDSYS"."SDO_DIAG_MESSAGES_TABLE" Number of failures: 0 Table list: "DVSYS"."AUDIT_TRAIL$" Number of failures: 0 Table list: "DVSYS"."SIMULATION_LOG$" Number of failures: 0 INFO: Total failures during update of TSTZ data: 0 . An upgrade window has been successfully ended. INFO: Your new Server RDBMS DST version is DSTv32 . INFO: The RDBMS DST update is successfully finished. INFO: Make sure to exit this SQL*Plus session. INFO: Do not use it for timezone related selects. Session altered. SQL> SELECT version FROM v$timezone_file; VERSION ---------- 32 1 row selected.
Step 19: Run utlusts.sql
SQL> @/u01/app/oracle/product/19.0.0/db_1/rdbms/admin/utlusts.sql TEXT
Step 20: Once you have confirmed that upgrade is successful and there is no rollback, you can drop the restore point
SQL> col name for a20 col GUARANTEE_FLASHBACK_DATABASE for a10 col TIME for a60 set lines 190 select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from V$restore_point;SQL> SQL> SQL> SQL> NAME GUARANTEE_ TIME ------------- ---------- ----------------------- PRE_UPGRADE YES 16-FEB-21 07.47.48.000000000 AM SQL> drop restore point PRE_UPGRADE; Restore point dropped.
Step 21: Update Compatible parameter value to 19.0.0
SQL> show parameter COMPATIBLE NAME TYPE VALUE ----------------------------- ----------- --------- compatible string 12.2.0 noncdb_compatible boolean FALSE SQL> ALTER SYSTEM SET COMPATIBLE = '19.0.0' SCOPE=SPFILE; System altered. SQL> shut immediate; Database closed Database dismounted. ORACLE instance shut down. SQL> startup; ORACLE instance started. Total System Global Area 2684354104 bytes Fixed Size 8900152 bytes Variable Size 687865856 bytes Database Buffers 1979711488 bytes Redo Buffers 7876608 bytes Database mounted. Database opened. SQL> show parameter COMPATIBLE NAME TYPE VALUE ----------------------------- ----------- ----------- compatible string 19.0.0 noncdb_compatible boolean FALSE
Step 22: Verify Component name and status from DBA_REGISTRY.
Step 23: Check Listener status.
Step 24: Verify PasswordFile.
[oracle@oracle19c dbs]$ ls -ltr total 18332 -rwxrwxr-x. 1 oracle oinstall 3079 May 14 2015 init.ora -rwxrwxr-x. 1 oracle oinstall 24 Feb 8 12:49 lkORACLE19C -rwxrwxr-x. 1 oracle oinstall 24 Feb 8 12:52 lkORACLE19 -rwxrwxr-x. 1 oracle oinstall 2048 Feb 8 12:58 orapworacle19c -rwxrwxr-x. 1 oracle oinstall 18726912 Feb 9 09:09 snapcf_oracle19c.f -rwxrwxr-x. 1 oracle oinstall 24 Feb 11 07:39 lkORACLE12C -rwxrwxr-x. 1 oracle oinstall 799 Feb 11 08:40 initoracle12c.ora -rwxrwxr-x. 1 oracle oinstall 3584 Feb 12 07:12 spfileoracle19c.ora -rwxrwxr-x. 1 oracle oinstall 1544 Feb 12 15:59 hc_oracle19c.dat -rw-r-----. 1 oracle oinstall 3584 Feb 16 08:43 orapworacle12c -rwxrwxr-x. 1 oracle oinstall 1544 Feb 16 09:20 hc_oracle12c.dat -rw-r-----. 1 oracle oinstall 3584 Feb 16 09:20 spfileoracle12c.ora
Step 25: Edit oratab to change the new version for Database.
[oracle@oracle19c dbs]$ cat /etc/oratab | grep -i oracle12c oracle12c:/u01/app/oracle/product/19.0.0/db_1:N oracle12c:/u01/app/oracle/product/12.2.0/db_1:N
Recommended Posts
