PRECHECKS ->
1 Minimum version of the database that can be directly upgraded to Oracle 19c
| Source | Target |
| 11.2.0.4 | 19c |
| 12.1.0.2 | 19c |
| 12.2.0.1 | 19c |
| 18.1 | 19c |
2 Changes in 19c need to be discussed with Application Teams
There are lot of changes in 19c like – utl_file_dir, streams, oracle multimedia are de-supported, do check with your application team before going ahead with your upgrade plan.
3 OS version is the key, please check the certification for 19c to understand if your OS level supports 19c or not.
4 Make sure your Grid Infrastructure is on 19c before going for database upgrade.
5 It’s always better to take a database backup before going for changes.
6 Here we are assuming you have already installed the oracle home in new home location for production and for any standby environment you have. And applied latest PSU patch on it.
Before starting the upgrade, stop the recovery of standby database. And remove the destination from production database.
Lets begin with upgrade activity.
How to Upgrade Database (Non-CDB) from 11g to 19c
- Lets create a restore point for database, in case if upgrade failed then we will use it to roll back.
Before creating a restore point make sure FRA is enabled.
show parameter recoverycreate restore point BEFORE_19c guarantee flashback database;2. Create pfile from spfile, copy it in 19c home dbs location similarly copy listener.ora and tnsnames.ora in 19c home network admin
SQL>create pfile='/tmp/pfile_backup_dbname.ora' from spfile;cp /tmp/pfile_backup_dbname.ora /u01/oracle/app/product/19.3.0/dbs
cp /u01/oracle/app/product/11.2.0.4/dbhome/network/admin /u01/oracle/app/product/19.3.0/network/adminIf you are doing it for RAC make sure you copy listener.ora , sqlnet.ora and tnsnames.ora in 19c home on all the nodes of the cluster
Make necessary changes in new pfile and listener.ora (you need to update the new oracle home in it)
3. Run the pre-jar file which will generate preupgrade_fixup,postupgrade_fixups and preupgrade logfile. Command syntax goes as follows –
[oracle@development]$ $CURRENT_VERSION_HOME/jdk/bin/java -jar $NEW_VERSION_HOME/rdbms/admin/preupgrade.jar DIR locationExample –
/u01/oracle/app/product/11.2.0.4/dbhome/jdk/bin/java -jar /u01/oracle/app/product/19.3.0/rdbms/admin/preupgrade.jar DIR /u01/UPGRADE/22JUNE21
==================
PREUPGRADE SUMMARY
==================
/u01/UPGRADE/22JUNE21/preupgrade.log
/u01/UPGRADE/22JUNE21/preupgrade_fixups.sql
/u01/UPGRADE/22JUNE21/postupgrade_fixups.sql
Execute fixup scripts as indicated below:
Before upgrade:
Log into the database and execute the preupgrade fixups
@/u01/UPGRADE/22JUNE21/preupgrade_fixups.sql
After the upgrade:
Log into the database and execute the postupgrade fixups
@/u01/UPGRADE/22JUNE21/postupgrade_fixups.sql
Preupgrade complete: 2021-06-22T17:44:324. Now check the logfile generated in when you executed the pre-jar. Here is the output –
[oracle@development]$ more /u01/UPGRADE/22JUNE21/preupgrade.log
Report generated by Oracle Database Pre-Upgrade Information Tool Version
19.0.0.0.0 Build: 1 on 2021-06-22T17:44:32
Upgrade-To version: 19.0.0.0.0
=======================================
Status of the database prior to upgrade
=======================================
Database Name:
Container Name: Not Applicable in Pre-12.1 database
Container ID: Not Applicable in Pre-12.1 database
Version: 11.2.0.4.0
DB Patch Level: PSU 11.2.0.4.201020
Compatible: 11.2.0.3
Blocksize: 8192
Platform: Solaris[tm] OE (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
==============
BEFORE UPGRADE
==============
REQUIRED ACTIONS
================
1. Set the value of JOB_QUEUE_PROCESSES to a non-zero value, or remove the
setting entirely and accept the Oracle default.
The database has JOB_QUEUE_PROCESSES=0.
Starting with Oracle Database 11.2, setting JOB_QUEUE_PROCESSES=0 will
disable job execution via DBMS_JOBS and DBMS_SCHEDULER.
RECOMMENDED ACTIONS
===================
2. Remove initialization parameters that Oracle has obsoleted or removed.
This action may be done now or when starting the database in upgrade mode
using the target ORACLE HOME.
Parameter
---------
standby_archive_dest
utl_file_dir
If parameters that are obsolete or removed from the target release are
present in the pfile/spfile, the Oracle database may not start, or it may
start with an ORA- error.
3. Run 11.2.0.4.0 $ORACLE_HOME/rdbms/admin/utlrp.sql to recompile invalid
objects. You can view the individual invalid objects with
SET SERVEROUTPUT ON;
EXECUTE DBMS_PREUP.INVALID_OBJECTS;
8 objects are INVALID.
There should be no INVALID objects in SYS/SYSTEM or user schemas before
database upgrade.
4. Perform one of the following:
1) Expire user accounts that use only the old 10G password version and
follow the procedure recommended in Oracle Database Upgrade Guide under
the section entitled, "Checking for Accounts Using Case-Insensitive
Password Version".
2) Explicitly set SQLNET.ALLOWED_LOGON_VERSION_SERVER in the 19
SQLNET.ORA to a non-Exclusive Mode value, such as "11". (This is a short
term approach and is not recommended because it will retain known
security risks associated with the 10G password version.)
Your database system has at least one account with only the 10G password
version (see the PASSWORD_VERSIONS column of DBA_USERS).
Starting with Oracle Database release 12.2.0.1, Exclusive Mode is the new
default password-based authentication mode. All Exclusive Mode
login/authentication attempts will fail for preexisting user accounts
which only have the 10G password version and neither the 11G or 12C
password version (see DBA_USERS.PASSWORD_VERSIONS.) For more information,
refer to "Understanding Password Case Sensitivity and Upgrades" in the
Oracle Database Upgrade Guide.
5. Review and remove any unnecessary HIDDEN/UNDERSCORE parameters.
The database contains the following initialization parameters whose name
begins with an underscore:
_high_priority_processes
_ksb_restart_policy_times
_use_adaptive_log_file_sync
_datafile_write_errors_crash_instance
(list truncated)
Remove hidden parameters before database upgrade unless your application
vendors and/or Oracle Support state differently. Changes will need to be
made in the pfile/spfile.
6. (AUTOFIXUP) 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.
INFORMATION ONLY
================
7. Consider removing the following deprecated initialization parameters.
Parameter
---------
background_dump_dest
parallel_adaptive_multi_user
user_dump_dest
These deprecated parameters probably will be obsolete in a future release.
8. Check the Oracle Backup and Recovery User's Guide for information on how
to manage an RMAN recovery catalog schema.
If you are using a version of the recovery catalog schema that is older
than that required by the RMAN client version, then you must upgrade the
catalog schema.
It is good practice to have the catalog schema the same or higher version
than the RMAN client version you are using.
ORACLE GENERATED FIXUP SCRIPT
=============================
All of the issues in database /u01
which are identified above as BEFORE UPGRADE "(AUTOFIXUP)" can be resolved by
executing the following
SQL>@/u01/UPGRADE/22JUNE21/preupgrade_fixups.sql
(Truncated)Check each and every points mentioned under BEFORE UPGRADE
Points which has (AUTOFIXUP) written as prefix you can ignore as when you will run the preupgrade_fixup.sql that open action will be taken care by the script itself.
Here for my example, I need to work on point 1,2,3,4,5,7
Point 1, I need to set the job_queue_processes to non-zero value.
Point 2, Remove these parameters – standby_archive_dest and utl_file_dir from PFILE which will be used in 19c upgrade.
Point 3, Run utlrp.sql
Point 4, Set SQLNET.ALLOWED_LOGON_VERSION_SERVER=10 in my sqlnet.ora
Point 5, remove all the underscore parameters from PFILE which will be used in 19c upgrade.
Point 7, remove these parameters background_dump_dest,parallel_adaptive_multi_user and user_dump_dest from PFILE which will be used in 19c upgrade.
5. Shutdown the database
If it’s rac remove cluster_database and cluster_database_instances from new 19c pfile.
Don’t change the diag_dest parameter in 19c pfile now, we will change it during actual run.
sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Jun 22 17:51:11 2021
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, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>6. Start the database using same home but with new 19c pfile and execute /u01/UPGRADE/22JUNE21/preupgrade_fixups.sql
export ORACLE_HOME=/u01/oracle/app/product/11.2.0.4/dbhome
export PATH=$PATH:$ORACLE_HOME/bin:$ORACLE_HOME/OPatch
sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Jun 22 17:53:49 2021
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, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL>startup pfile='/u01/oracle/app/product/19.3.0/dbs/19c_pfile.ora';
ORACLE instance started.
Total System Global Area 2.5662E+10 bytes
Fixed Size 2193544 bytes
Variable Size 1.3824E+10 bytes
Database Buffers 1.1811E+10 bytes
Redo Buffers 24633344 bytes
Database mounted.
Database opened.
SQL>@/u01/UPGRADE/22JUNE21/preupgrade_fixups.sql
Sample Output as follows ->
Executing Oracle PRE-Upgrade Fixup Script
Auto-Generated by: Oracle Preupgrade Script
Version: 19.0.0.0.0 Build: 1
Generated on: 2021-06-22 17:44:30
For Source Database:
Source Database Version: 11.2.0.4.0
For Upgrade to Version: 19.0.0.0.0
Preup Preupgrade
Action Issue Is
Number Preupgrade Check Name Remedied Further DBA Action
------ ------------------------ ---------- --------------------------------
1. job_queue_process_0 YES None.
2. parameter_obsolete YES None.
3. invalid_objects_exist NO Manual fixup recommended.
4. exclusive_mode_auth NO Manual fixup recommended.
5. hidden_params YES None.
6. trgowner_no_admndbtrg YES None.
7. parameter_deprecated YES None.
8. 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 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> SQL>7. Now check the details before shutting the database for final time.
sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Jun 22 17:55:31 2021
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, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
DB_N READ WRITE PRIMARY
SQL>Select comp_name,comp_id,version,status from dba_registry;
COMP_NAME COMP_ID VERSION STATUS
----------------------------------- ---------- ----------- -----------
Oracle Database Catalog Views CATALOG 11.2.0.4.0 VALID
Oracle Database Packages and Types CATPROC 11.2.0.4.0 VALID
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production8. Start the database using new 19c home as upgrade mode.
unset ORACLE_HOME
unset ORACLE_SID
unset ORACLE_BASE
export ORACLE_SID=INSTANCE_NAME
export ORACLE_BASE=/u01/oracle/app
export ORACLE_HOME=/u01/oracle/app/product/19.3.0
export PATH=$PATH:$ORACLE_HOME/bin:$ORACLE_HOME/OPatch
sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jun 22 17:57:51 2021
Version 19.9.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 2.5770E+10 bytes
Fixed Size 30120952 bytes
Variable Size 1.3892E+10 bytes
Database Buffers 1.1811E+10 bytes
Redo Buffers 36986880 bytes
Database mounted.
Database opened.
SQL>exit9. Start the upgrade.
echo $ORACLE_SID
cd $ORACLE_HOME/bin
sh dbupgrade -n 6
You will see something on your screen
(truncated)
Parallel SQL Process Count = 6
Components in [database_name]
Installed [CATALOG CATPROC]
Not Installed [APEX APS CATJAVA CONTEXT DV EM JAVAVM MGW ODM OLS ORDIM OWM RAC SDO WK XDB XML XOQ]
DataBase Version = 11.2.0.4.0
------------------------------------------------------
Phases [0-107] Start Time:[2021_06_22 17:59:40]
------------------------------------------------------
*********** Executing Change Scripts ***********
Serial Phase #:0 [database_name] Files:1 Time: 161s
*************** Catalog Core SQL ***************
Serial Phase #:1 [database_name] Files:5 Time: 41s
Restart Phase #:2 [database_name] Files:1 Time: 0s
*********** Catalog Tables and Views ***********
Parallel Phase #:3 [database_name] Files:19 Time: 8s
Restart Phase #:4 [database_name] Files:1 Time: 0s
************* Catalog Final Scripts ************
Serial Phase #:5 [database_name] Files:7 Time: 17s
***************** Catproc Start ****************
Serial Phase #:6 [database_name] Files:1 Time: 12s
***************** Catproc Types ****************
Serial Phase #:7 [database_name] Files:2 Time: 8s
Restart Phase #:8 [database_name] Files:1 Time: 0s
**************** Catproc Tables ****************
Parallel Phase #:9 [database_name] Files:67 Time: 13s
Restart Phase #:10 [database_name] Files:1 Time: 0s
************* Catproc Package Specs ************
Serial Phase #:11 [database_name] Files:1 Time: 62s
Restart Phase #:12 [database_name] Files:1 Time: 1s
************** Catproc Procedures **************
Parallel Phase #:13 [database_name] Files:94 Time: 3s
Restart Phase #:14 [database_name] Files:1 Time: 0s
Parallel Phase #:15 [database_name] Files:121 Time: 6s
Restart Phase #:16 [database_name] Files:1 Time: 0s
Serial Phase #:17 [database_name] Files:22 Time: 3s
Restart Phase #:18 [database_name] Files:1 Time: 0s
***************** Catproc Views ****************
Parallel Phase #:19 [database_name] Files:32 Time: 8s
Restart Phase #:20 [database_name] Files:1 Time: 0s
Serial Phase #:21 [database_name] Files:3 Time: 10s
Restart Phase #:22 [database_name] Files:1 Time: 1s
Parallel Phase #:23 [database_name] Files:25 Time: 95s
Restart Phase #:24 [database_name] Files:1 Time: 0s
Parallel Phase #:25 [database_name] Files:12 Time: 67s
Restart Phase #:26 [database_name] Files:1 Time: 0s
Serial Phase #:27 [database_name] Files:1 Time: 0s
Serial Phase #:28 [database_name] Files:3 Time: 4s
Serial Phase #:29 [database_name] Files:1 Time: 0s
Restart Phase #:30 [database_name] Files:1 Time: 0s
*************** Catproc CDB Views **************
Serial Phase #:31 [database_name] Files:1 Time: 1s
Restart Phase #:32 [database_name] Files:1 Time: 0s
Serial Phase #:34 [database_name] Files:1 Time: 0s
***************** Catproc PLBs *****************
Serial Phase #:35 [database_name] Files:295 Time: 37s
Serial Phase #:36 [database_name] Files:1 Time: 0s
Restart Phase #:37 [database_name] Files:1 Time: 0s
Serial Phase #:38 [database_name] Files:6 Time: 4s
Restart Phase #:39 [database_name] Files:1 Time: 1s
*************** Catproc DataPump ***************
Serial Phase #:40 [database_name] Files:3 Time: 45s
Restart Phase #:41 [database_name] Files:1 Time: 1s
****************** Catproc SQL *****************
Parallel Phase #:42 [database_name] Files:13 Time: 65s
Restart Phase #:43 [database_name] Files:1 Time: 0s
Parallel Phase #:44 [database_name] Files:11 Time: 5s
Restart Phase #:45 [database_name] Files:1 Time: 0s
Parallel Phase #:46 [database_name] Files:3 Time: 1s
Restart Phase #:47 [database_name] Files:1 Time: 0s
************* Final Catproc scripts ************
Serial Phase #:48 [database_name] Files:1 Time: 73s
Restart Phase #:49 [database_name] Files:1 Time: 0s
************** Final RDBMS scripts *************
Serial Phase #:50 [database_name] Files:1 Time: 32s
************ Upgrade Component Start ***********
Serial Phase #:51 [database_name] Files:1 Time: 0s
Restart Phase #:52 [database_name] Files:1 Time: 0s
********** Upgrading Java and non-Java *********
Serial Phase #:53 [database_name] Files:2 Time: 1s
***************** Upgrading XDB ****************
Restart Phase #:54 [database_name] Files:1 Time: 0s
Serial Phase #:56 [database_name] Files:3 Time: 1s
Serial Phase #:57 [database_name] Files:3 Time: 0s
Parallel Phase #:58 [database_name] Files:10 Time: 1s
Parallel Phase #:59 [database_name] Files:25 Time: 0s
Serial Phase #:60 [database_name] Files:4 Time: 1s
Serial Phase #:61 [database_name] Files:1 Time: 0s
Serial Phase #:62 [database_name] Files:32 Time: 0s
Serial Phase #:63 [database_name] Files:1 Time: 0s
Parallel Phase #:64 [database_name] Files:6 Time: 1s
Serial Phase #:65 [database_name] Files:2 Time: 1s
Serial Phase #:66 [database_name] Files:3 Time: 0s
**************** Upgrading ORDIM ***************
Restart Phase #:67 [database_name] Files:1 Time: 0s
Serial Phase #:69 [database_name] Files:1 Time: 1s
Parallel Phase #:70 [database_name] Files:2 Time: 0s
Restart Phase #:71 [database_name] Files:1 Time: 1s
Parallel Phase #:72 [database_name] Files:2 Time: 0s
Serial Phase #:73 [database_name] Files:2 Time: 1s
***************** Upgrading SDO ****************
Restart Phase #:74 [database_name] Files:1 Time: 0s
Serial Phase #:76 [database_name] Files:1 Time: 1s
Serial Phase #:77 [database_name] Files:2 Time: 0s
Restart Phase #:78 [database_name] Files:1 Time: 0s
Serial Phase #:79 [database_name] Files:1 Time: 1s
Restart Phase #:80 [database_name] Files:1 Time: 0s
Parallel Phase #:81 [database_name] Files:3 Time: 1s
Restart Phase #:82 [database_name] Files:1 Time: 0s
Serial Phase #:83 [database_name] Files:1 Time: 1s
Restart Phase #:84 [database_name] Files:1 Time: 0s
Serial Phase #:85 [database_name] Files:1 Time: 0s
Restart Phase #:86 [database_name] Files:1 Time: 1s
Parallel Phase #:87 [database_name] Files:4 Time: 0s
Restart Phase #:88 [database_name] Files:1 Time: 0s
Serial Phase #:89 [database_name] Files:1 Time: 1s
Restart Phase #:90 [database_name] Files:1 Time: 0s
Serial Phase #:91 [database_name] Files:2 Time: 1s
Restart Phase #:92 [database_name] Files:1 Time: 0s
Serial Phase #:93 [database_name] Files:1 Time: 0s
Restart Phase #:94 [database_name] Files:1 Time: 1s
******* Upgrading ODM, WK, EXF, RUL, XOQ *******
Serial Phase #:95 [database_name] Files:1 Time: 0s
Restart Phase #:96 [database_name] Files:1 Time: 0s
*********** Final Component scripts ***********
Serial Phase #:97 [database_name] Files:1 Time: 1s
************* Final Upgrade scripts ************
Serial Phase #:98 [database_name] Files:1 Time: 118s
******************* Migration ******************
Serial Phase #:99 [database_name] Files:1 Time: 31s
*** End PDB Application Upgrade Pre-Shutdown ***
Serial Phase #:100 [database_name] Files:1 Time: 1s
Serial Phase #:101 [database_name] Files:1 Time: 0s
Serial Phase #:102 [database_name] Files:1 Time: 59s
***************** Post Upgrade *****************
Serial Phase #:103 [database_name] Files:1 Time: 27s
**************** Summary report ****************
Serial Phase #:104 [database_name] Files:1 Time: 1s
*** End PDB Application Upgrade Post-Shutdown **
Serial Phase #:105 [database_name] Files:1 Time: 1s
Serial Phase #:106 [database_name] Files:1 Time: 0s
Serial Phase #:107 [database_name] Files:1 Time: 27s
------------------------------------------------------
Phases [0-107] End Time:[2021_06_22 18:17:29]
------------------------------------------------------
Grand Total Time: 1069s
LOG FILES:
(truncated)
Grand Total Upgrade Time: [0d:0h:17m:49s]10. Now start the database in 19c home in normal mode and run utlrp.sql just to see if all your components are valid or not.
sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jun 22 18:17:57 2021
Version 19.9.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 2.5770E+10 bytes
Fixed Size 30120952 bytes
Variable Size 1.3892E+10 bytes
Database Buffers 1.1811E+10 bytes
Redo Buffers 36986880 bytes
Database mounted.
Database opened.
SQL>
SQL> @?/rdbms/admin/utlrp.sql
Session altered.
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2021-06-22 18:18:51
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-06-22 18:20:00
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
-------------------
8
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 comp_name,comp_id,version,status from dba_registry;
COMP_NAME COMP_ID VERSION STATUS
----------------------------------- --------- ----------- -----------
Oracle Database Catalog Views CATALOG 19.0.0.0.0 VALID
Oracle Database Packages and Types CATPROC 19.0.0.0.0 VALID
Oracle Real Application Clusters RAC 19.0.0.0.0 OPTION OFF
Oracle XML Database XDB 19.0.0.0.0 VALID
11. Update the time zone
cd $ORACLE_HOME/network/admin
sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jun 22 18:20:43 2021
Version 19.9.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.9.0.0.0
SQL> @utltz_countstats.sql
(output truncated)
SQL> @utltz_countstar.sql
(output truncated)
SQL> @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 DSTv14 .
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>@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 2.5770E+10 bytes
Fixed Size 30120952 bytes
Variable Size 1.3892E+10 bytes
Database Buffers 1.1811E+10 bytes
Redo Buffers 36986880 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 2.5770E+10 bytes
Fixed Size 30120952 bytes
Variable Size 1.3892E+10 bytes
Database Buffers 1.1811E+10 bytes
Redo Buffers 36986880 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: "SQLTXPLAIN"."SQLT$_DBA_COL_STATS_VERSIONS"
Number of failures: 0
Table list: "SQLTXPLAIN"."SQLT$_SQL_STATEMENT"
Number of failures: 0
Table list: "SQLTXPLAIN"."SQLT$_DBA_AUTOTASK_CLIENT_HST"
Number of failures: 0
Table list: "SQLTXPLAIN"."SQLT$_DBA_HISTGRM_STATS_VERSN"
Number of failures: 0
Table list: "SQLTXPLAIN"."SQLT$_DBA_IND_STATS_VERSIONS"
Number of failures: 0
Table list: "SQLTXPLAIN"."SQLT$_DBA_OPTSTAT_OPERATIONS"
Number of failures: 0
Table list: "SQLTXPLAIN"."SQLT$_DBA_SCHEDULER_JOBS"
Number of failures: 0
Table list: "SQLTXPLAIN"."SQLT$_DBA_TAB_STATS_VERSIONS"
Number of failures: 0
Table list: "SQLTXPLAIN"."SQLT$_OPTSTAT_USER_PREFS$"
Number of failures: 0
Table list: "SQLTXPLAIN"."SQLT$_WRI$_OPTSTAT_AUX_HISTORY"
Number of failures: 0
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
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>
SQL>
SQL>
SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.9.0.0.012. Run utlusts.sql to check the total details
sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jun 22 18:23:37 2021
Version 19.9.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.9.0.0.0
SQL> @?/rdbms/admin/utlusts.sql TEXT
Oracle Database Release 19 Post-Upgrade Status Tool 06-22-2021 18:23:4
Database Name: /u01
Component Current Full Elapsed Time
Name Status Version HH:MM:SS
Oracle Server VALID 19.9.0.0.0 00:13:03
Oracle Real Application Clusters OPTION OFF 19.9.0.0.0 00:00:00
Oracle XML Database VALID 19.9.0.0.0 00:00:00
Datapatch 00:01:52
Final Actions 00:02:29
Post Upgrade 00:00:25
Post Compile 00:01:09
Total Upgrade Time: 00:17:24
Database time zone version is 32. It meets current release needs.
SQL>utlusts.sql13.Run the postupgrade_fixups.sql
SQL> @/u01/UPGRADE/22JUNE21/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: 19.0.0.0.0 Build: 1
Generated on: 2020-12-15T01:07:13
For Source Database:
Source Database Version: 11.2.0.4.0
For Upgrade to Version: 19.0.0.0.0
Preup Preupgrade
Action Issue Is
Number Preupgrade Check Name Remedied Further DBA Action
------ ------------------------ ---------- --------------------------------
11. old_time_zones_exist YES None.
12. dir_symlinks YES None.
13. post_dictionary YES None.
14. post_fixed_objects NO Informational only.
Further action is optional.
15. upg_by_std_upgrd YES None.
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 messagefrom the preupgrade which explains in more detail what still needs to be done.
PL/SQL procedure successfully completed.
Session altered.
SQL>14. Password file
orapwd file=orapwdatabase_name password=manager entries=12 FORCE=YMake sure you copy the password file in all the nodes if you want to keep it in dbs location or create the password file in ASM itself. Move them to all the DR databases.
15. Post checks
Start the listener from new home, (make sure you have updated the new home in listener.ora )
Check the alert logs if you see any error post upgrade.
If RAC , Remove the database from cluster and add it again from 19c home
or
fire srvctl upgrade database -d db_unique_name .And then start the database using srvctl command.
Now it’s time to release the system to Application team
16. Once application team confirmed that everything is working fine. Set the destination for standby database on production database. Start the Standby database using new home in mount stage, and start the recovery.