0. Check Compatibility Before Upgrading Oracle Database
1. Environment
PRE-UPGRADE TASKS 2. Backup 3. Run preupgrade script 4. View Preupgrade log 5. Minimum tablespace sizes for upgrade 6. Update INITIALIZATION PARAMETERS 7. Gather DICTIONARY STATS 8. Purge Recyclebin 9. Refresh MVs 10. Run preupgrade_fixups.sql 11. Verify archive log dest size 12. Stop LISTENER 13. Create Flashback Guaranteed Restore Point
UPGRADE TASK
14. Shutdown Database 15. Copy init and password files from 12c to 19c dbs home 16. Startup DB in Upgrade mode 17. Run dbupgrade 18. Starup DB from 19c home POST-UPGRADE TASKS WHEN DBUA USING
19. Run catcon.pl to start utlrp.sql 20. Run postupgrade_fixups.sql 21. Upgrade Timezone 22. Run utlusts.sql 23. Run catuppst.sql 24. Re-Run postupgrade_fixups.sql 25. Reverify INVALID OBJECTS 26. Drop Restore point 27. Set COMPATIBALE parameter value to 19.0.0 28. Verify DBA_REGISTRY 29. Add TNS Entries in 19c TNS home 30. Password File – orapwPRIM 31. Edit oratab 32. Back Up the Database
SQL> select count(*) from dba_objects where status='INVALID'; COUNT(*) 0 <---- SQL>
3. Run preupgrade script
1 2 3
. oraenv (PRIM) /u01/app/oracle/product/12.2.0/dbhome_1/jdk/bin/java -jar /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/preupgrade.jar TERMINAL TEXT
— OR —
1
/u01/app/oracle/product/12.2.0/dbhome_1/jdk/bin/java -jar /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/preupgrade.jar FILE DIR /home/oracle/PRIM/preupgrade
1 2 3 4 5 6 7 8
[oracle@rac1 ~]$ mkdir -p /home/oracle/PRIM/preupgrade [oracle@rac1 ~]$ [oracle@rac1 ~]$ . oraenv ORACLE_SID = [oracle] ? PRIM The Oracle base has been set to /u01/app/oracle [oracle@rac1 ~]$ [oracle@rac1 ~]$
1
/u01/app/oracle/product/12.2.0/dbhome_1/jdk/bin/java -jar /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/preupgrade.jar FILE DIR /home/oracle/PRIM/preupgrade
Report generated by Oracle Database Pre-Upgrade Information Tool Version 19.0.0.0.0 Build: 1 on 2020-01-28T20:45:05
Upgrade-To version: 19.0.0.0.0
======================================= Status of the database prior to upgrade ======================================= Database Name: PRIM Container Name: PRIM 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: ARCHIVELOG Readonly: FALSE Edition: EE
Oracle Component Upgrade Action Current Status —————- ————– ————– Oracle Server [to be upgraded] VALID JServer JAVA Virtual Machine [to be upgraded] VALID Oracle XDK for Java [to be upgraded] VALID 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
============== BEFORE UPGRADE REQUIRED ACTIONS ================ None RECOMMENDED ACTIONS ===================
A). (AUTOFIXUP) Gather stale data dictionary statistics prior to database
upgrade in off-peak time using: EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
==============
Dictionary statistics do not exist or are stale (not up-to-date).
Dictionary statistics help the Oracle optimizer find efficient SQL execution plans and are essential for proper upgrade timing. Oracle recommends gathering dictionary statistics in the last 24 hours before database upgrade.
For information on managing optimizer statistics, refer to the 12.2.0.1 Oracle Database SQL Tuning Guide.
B) (AUTOFIXUP) Gather statistics on fixed objects prior the upgrade.
None of the fixed object tables have had stats collected.
Gathering statistics on fixed objects, if none have been gathered yet, is recommended prior to upgrading.
For information on managing optimizer statistics, refer to the 12.2.0.1 Oracle Database SQL Tuning Guide.
INFORMATION ONLY
================ C). 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 460 MB 500 MB SYSTEM 800 MB 912 MB TEMP 32 MB 150 MB UNDOTBS1 70 MB 439 MB
Minimum tablespace sizes for upgrade are estimates. D) Ensure there is additional disk space in LOG_ARCHIVE_DEST_1 for at least 4618 MB of archived logs. Check alert log during the upgrade that there is no write error to the destination due to lack of disk space.
Archiving cannot proceed if the archive log destination is full during upgrade.
The database has archiving enabled. The upgrade process will need free disk space in the archive log destination(s) to generate archived logs to.
E) 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 PRIM which are identified above as BEFORE UPGRADE “(AUTOFIXUP)” can be resolved by executing the following
F) Upgrade the database time zone file using the DBMS_DST package.
The database is using time zone file version 26 and the target 19 release ships with time zone file version 32.
Oracle recommends upgrading to the desired (latest) version of the time zone file. For more information, refer to “Upgrading the Time Zone File and Timestamp with Time Zone Data” in the 19 Oracle Database Globalization Support Guide.
G) (AUTOFIXUP) Gather dictionary statistics after the upgrade using the command:
1
EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
Oracle recommends gathering dictionary statistics after upgrade.
Dictionary statistics provide essential information to the Oracle optimizer to help it find efficient SQL execution plans. After a database upgrade, statistics need to be re-gathered as there can now be tables that have significantly changed during the upgrade or new tables that do
not have statistics gathered yet.
H). Gather statistics on fixed objects after the upgrade and when there is a representative workload on the system using the command:
1
EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
This recommendation is given for all preupgrade runs.
Fixed object statistics provide essential information to the Oracle optimizer to help it find efficient SQL execution plans. Those statistics are specific to the Oracle Database release that generates them, and can be stale upon database upgrade.
For information on managing optimizer statistics, refer to the 12.2.0.1 Oracle Database SQL Tuning Guide.
ORACLE GENERATED FIXUP SCRIPT ============================= All of the issues in database PRIM which are identified above as AFTER UPGRADE “(AUTOFIXUP)” can be resolved by executing the following
SQL> @/home/oracle/PRIM/preupgrade/preupgrade_fixups.sql SQL> REM SQL> REM Oracle PRE-Upgrade Fixup Script SQL> REM SQL> REM Auto-Generated by: Oracle Preupgrade Script SQL> REM Version: 19.0.0.0.0 Build: 1 SQL> REM Generated on: 2020-01-28 20:45:02 SQL> REM SQL> REM Source Database: PRIM SQL> REM Source Database Version: 12.2.0.1.0 SQL> REM For Upgrade to Version: 19.0.0.0.0 SQL> REM SQL> SQL> REM SQL> REM Setup Environment SQL> REM SQL> SET ECHO OFF SERVEROUTPUT ON FORMAT WRAPPED TAB OFF LINESIZE 200; Executing Oracle PRE-Upgrade Fixup Script Auto-Generated by: Oracle Preupgrade Script Version: 19.0.0.0.0 Build: 1 Generated on: 2020-01-28 20:45:02 For Source Database: PRIM 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
dictionary_stats YES None. pre_fixed_objects YES None. tablespaces_info NO Informational only. Further action is optional. min_archive_dest_size NO Informational only. Further action is optional. 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>
11. Verify archive log dest size
*** Please verify free space on ALL LOG_ARCHIVE_DEST_ locations including ALL standby destinations
1 2 3 4 5 6 7 8 9 10 11
SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination /u01/app/archive/PRIM Oldest online log sequence 1 Next log sequence to archive 2 Current log sequence 2 SQL> SQL> !df -h /u01/app/archive/PRIM Filesystem Size Used Avail Use% Mounted on /dev/sda5 67G 35G 33G 52% /u01 <---- SQL>
13. Create Flashback Guaranteed Restore Point *** NO need to enable Flashback Database from 11.2.0.1 onwards *** Database MUST be in Archive Log mode *** MUST NOT change the compatible parameter to higher version
1 2 3 4 5
SQL> select flashback_on from v$database; FLASHBACK_ON NO
1 2 3 4 5
SQL> select name,open_mode,log_mode from v$database; NAME OPEN_MODE LOG_MODE
!mkdir -p /u01/app/oracle/fast_recovery_area SQL> alter system set db_recovery_file_dest_size=10G; System altered. SQL> alter system set db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'; System altered.
SQL> select * from V$restore_point; no rows selected SQL> SQL> create restore point pre_upgrade guarantee flashback database; Restore point created. SQL>
1 2 3 4 5 6 7 8 9 10 11
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; NAME GUARANTEE_ TIME
[oracle@rac1 ~]$ export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1 [oracle@rac1 ~]$ export ORACLE_SID=PRIM [oracle@rac1 ~]$ PATH=/u01/app/oracle/product/19.0.0/dbhome_1/bin:$PATH; export PATH [oracle@rac1 ~]$ which sqlplus /u01/app/oracle/product/19.0.0/dbhome_1/bin/sqlplus [oracle@rac1 ~]$ sqlplus / as sysdbaSQL*Plus: Release 19.0.0.0.0 - Production on Tue Jan 28 21:13:24 2020 Version 19.4.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 1560277408 bytes Fixed Size 8896928 bytes Variable Size 939524096 bytes Database Buffers 603979776 bytes Redo Buffers 7876608 bytes Database mounted. Database opened. SQL> SQL> select name,open_mode,cdb,version,status from v$database,v$instance; NAME OPEN_MODE PRIM VERSION STATUS
PRIM READ WRITE NO 19.0.0.0.0 OPEN MIGRATE
<— SQL>
17. Run dbupgrade
You can run the upgrade using either of the following commands. The second is actually just a shorthand for the former.
[oracle@rac1 upgrade20200128213345]$ cd /u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/PRIM/upgrade20200128213345 [oracle@rac1 upgrade20200128213345]$ ls -ltr *.log -rw-------. 1 oracle oinstall 1688451 Jan 28 21:37 catupgrd1.log -rw-------. 1 oracle oinstall 1278535 Jan 28 21:37 catupgrd2.log -rw-------. 1 oracle oinstall 1461585 Jan 28 21:37 catupgrd3.log -rw-------. 1 oracle oinstall 7944713 Jan 28 21:38 catupgrd0.log [oracle@rac1 upgrade20200128213345]$ tail -f catupgrd0.log tail -f catupgrd1.log tail -f catupgrd2.log tail -f catupgrd3.log [oracle@rac1 ~]$ cd /u01/app/oracle/product/19.0.0/dbhome_1/bin/ [oracle@rac1 bin]$ more nohup.out Argument list for [/u01/app/oracle/product/19.0.0/dbhome_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.4.0.0.0DBRU_LINUX.X64_190626] /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/orahome = [/u01/app/oracle/product/19.0.0/dbhome_1] /u01/app/oracle/product/19.0.0/dbhome_1/bin/orabasehome = [/u01/app/oracle/product/19.0.0/dbhome_1] catctlGetOraBaseLogDir = [/u01/app/oracle/product/19.0.0/dbhome_1] Analyzing file /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/catupgrd.sql Log file directory = [/tmp/cfgtoollogs/upgrade20200128213334] catcon::set_log_file_base_path: ALL catcon-related output will be written to [/tmp/cfgtoollogs/upgrade20200128213334/catupgrd_catcon_22589.lst] catcon::set_log_file_base_path: catcon: See [/tmp/cfgtoollogs/upgrade20200128213334/catupgrd*.log] files for output generated by scripts catcon::set_log_file_base_path: catcon: See [/tmp/cfgtoollogs/upgrade20200128213334/catupgrd_*.lst] files for spool files, if any Number of Cpus = 1 Database Name = PRIM 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/dbhome_1/cfgtoollogs/PRIM/upgrade20200128213345/catupgrd_cat con_22589.lst] catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/PRIM/upgrade20200128213345/catupgrd*.log] files for output generated by scripts catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/PRIM/upgrade20200128213345/catupgrd_*.lst] files for spool files, if any Log file directory = [/u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/PRIM/upgrade20200128213345] Parallel SQL Process Count = 4 Components in [PRIM] 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] End Time:[2020_01_28 22:20:35] Grand Total Time: 2800s LOG FILES: (/u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/PRIM/upgrade20200128213345/catupgrd*.log) Upgrade Summary Report Located in: /u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/PRIM/upgrade20200128213345/upg_summary.log Grand Total Upgrade Time: [0d:0h:46m:40s] [oracle@rac1 bin]$ [oracle@rac1 upgrade20200128213345]$ pwd /u01/app/oracle/product/19.0.0/dbhome_1/cfgtoollogs/PRIM/upgrade20200128213345 [oracle@rac1 upgrade20200128213345]$ cat upg_summary.log Oracle Database Release 19 Post-Upgrade Status Tool 01-28-2020 22:20:0 Database Name: PRIM Component Current Full Elapsed Time Name Status Version HH:MM:SS Oracle Server UPGRADED 19.4.0.0.0 00:18:01 JServer JAVA Virtual Machine UPGRADED 19.4.0.0.0 00:02:29 Oracle XDK UPGRADED 19.4.0.0.0 00:01:12 Oracle Database Java Packages UPGRADED 19.4.0.0.0 00:00:13 OLAP Analytic Workspace UPGRADED 19.4.0.0.0 00:00:19 Oracle Label Security UPGRADED 19.4.0.0.0 00:00:11 Oracle Database Vault UPGRADED 19.4.0.0.0 00:00:32 Oracle Text UPGRADED 19.4.0.0.0 00:00:43 Oracle Workspace Manager UPGRADED 19.4.0.0.0 00:00:37 Oracle Real Application Clusters UPGRADED 19.4.0.0.0 00:00:01 Oracle XML Database UPGRADED 19.4.0.0.0 00:01:49 Oracle Multimedia UPGRADED 19.4.0.0.0 00:00:55 Spatial LOADING 19.4.0.0.0 00:07:19 Oracle OLAP API UPGRADED 19.4.0.0.0 00:00:14 Datapatch 00:08:34 Final Actions 00:08:43 Post Upgrade 00:00:12 Total Upgrade Time: 00:44:08 Database time zone version is 26. It is older than current release time zone version 32. Time zone upgrade is needed using the DBMS_DST package. Grand Total Upgrade Time: [0d:0h:46m:40s] [oracle@rac1 upgrade20200128213345]$
[oracle@rac1 ~]$ export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1 [oracle@rac1 ~]$ export ORACLE_SID=PRIM [oracle@rac1 ~]$ PATH=/u01/app/oracle/product/19.0.0/dbhome_1/bin:$PATH; export PATH [oracle@rac1 ~]$ which sqlplus /u01/app/oracle/product/19.0.0/dbhome_1/bin/sqlplus [oracle@rac1 ~]$ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jan 28 22:25:18 2020 Version 19.4.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 1560277408 bytes Fixed Size 8896928 bytes Variable Size 1174405120 bytes Database Buffers 369098752 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 PRIM VERSION STATUS
PRIM READ WRITE NO 19.0.0.0.0 OPEN <----- SQL> SQL> col COMP_ID for a10 col COMP_NAME for a40 col VERSION for a15 set lines 180 set pages 999 select COMP_ID,COMP_NAME,VERSION,STATUS from dba_registry; COMP_ID COMP_NAME VERSION STATUS
cd /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin nohup sqlplus "/ as sysdba" @utlrp.sql > /home/oracle/utlrp.out 2>&1 & SQL> select count(*) from dba_objects where status='INVALID'; COUNT(*) 1413 <---- SQL> select count(*) from dba_objects where status='INVALID' and owner in ('SYS','SYSTEM'); COUNT(*) 655 <---- SQL> SQL>@/u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/utlrp.sql Session altered. TIMESTAMP COMP_TIMESTAMP UTLRP_BGN 2020-01-29 00:31:04 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 2020-01-29 00:36:03 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>
1 2 3 4 5 6 7
SQL> select count(*) from dba_objects where status='INVALID'; COUNT(*) 0 SQL>
SQL>@/home/oracle/PRIM/preupgrade/postupgrade_fixups.sqlSession 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-01-28 20:45:05 For Source Database: PRIM 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
old_time_zones_exist NO Manual fixup recommended. post_dictionary YES None. post_fixed_objects 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 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. SQL>
21. Upgrade Timezone For releases (18c, 19c), the timezone upgrade scripts are included in the target ORACLE_HOME under rdbms/admin directory The following scripts get delivered with Oracle Database 18c onward
1
$ORACLE_HOME/rdbms/admin/utltz_countstats.sql
Script to gives how much TIMESTAMP WITH TIME ZONE data there is in a database using stats info. No restart required.
1
$ORACLE_HOME/rdbms/admin/utltz_countstar.sql
Script to approximate how much TIMESTAMP WITH TIME ZONE data there is in a database using a COUNT(*) for each table that has a TSTZ column. This script is useful when using DBMS_DST package or the scripts of utlz_upg_check.sql and utlz_upg_apply.sql scripts.
1
$ORACLE_HOME/rdbms/admin/utltz_upg_check.sql
Time zone upgrade check script
1
$ORACLE_HOME/rdbms/admin/utltz_upg_apply.sql
Time zone apply script. Warning: This script will restart the database and adjust time zone data.
[oracle@rac1 ~]$ cd /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/ [oracle@rac1 admin]$ ls -ltr utltz_countstats.sql utltz_countstar.sql utltz_upg_check.sql utltz_upg_apply.sql -rw-r--r--. 1 oracle oinstall 8317 Feb 25 2017 utltz_countstats.sql -rw-r--r--. 1 oracle oinstall 7423 Feb 25 2017 utltz_countstar.sql -rw-r--r--. 1 oracle oinstall 33684 Sep 9 2017 utltz_upg_check.sql -rw-r--r--. 1 oracle oinstall 21526 Sep 9 2017 utltz_upg_apply.sql [oracle@rac1 admin]$ SQL> SELECT version FROM v$timezone_file; VERSION 26 <----- SQL> SQL>@/u01/app/oracle/product/19.0.0/dbhome_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> SQL>@/u01/app/oracle/product/19.0.0/dbhome_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 1560277408 bytes Fixed Size 8896928 bytes Variable Size 1191182336 bytes Database Buffers 352321536 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 1560277408 bytes Fixed Size 8896928 bytes Variable Size 1191182336 bytes Database Buffers 352321536 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_S" Number of failures: 0 Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_L" Number of failures: 0 Table list: "MDSYS"."SDO_DIAG_MESSAGES_TABLE" Number of failures: 0 Table list: "DVSYS"."SIMULATION_LOG$" Number of failures: 0 Table list: "DVSYS"."AUDIT_TRAIL$" 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> SELECT version FROM v$timezone_file; VERSION 32 <---- 1 row selected. SQL>
22. Run utlusts.sql *** Note: utluNNNs.sql is replaced by utlusts.sql in 19c version *** Note: In 19c Earlier version utluNNNs.sql is replaced by utlusts.sql *** Run utlusts.sql as many times as you want, at any time after the upgrade is completed. *** utlusts.sql reads the view called dba_registry_log and displays the upgrade results for the database components.
SQL> @/u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/utlusts.sql TEXT Oracle Database Release 19 Post-Upgrade Status Tool 01-29-2020 01:41:3 Database Name: PRIM
Component Current Full Elapsed Time Name Status Version HH:MM:SS
Database time zone version is 32. It meets current release needs.
SQL>
23. Run catuppst.sql
/* In 12c: https://docs.oracle.com/en/database/oracle/oracle-database/12.2/upgrd/oracle-database-upgrade-utilities.html#GUID-408F22C3-2AD6-4DA4-8015-F5C6149508F0 You must run this script, either through DBUA or manually, if you perform a manual upgrade. DBUA automatically runs catuppst.sql. You only must run this script separately for manual upgrades.
Do not run this in UPGRADE mode. Run catuppst.sql, located in the ORACLE_HOME/rdbms/admin directory, to perform remaining upgrade actions that do not require the database to be in UPGRADE mode. If an Oracle bundle patch or patch set update (PSU or BP) is installed in the Oracle home, then this script automatically applies that patch set update to the database.
Caution: If you perform a manual upgrade, and you do not run catuppst.sql, then your database suffers performance degradation over time. */
*** Actually it will run as part of upgrade. We have reviewed catupgrd0.log and below is the output… found catuppst.sql ran and don’t see errors.
/* Rem catuppst.sql Rem Rem Copyright (c) 2006, 2018, Oracle and/or its affiliates. Rem All rights reserved. Rem Rem NAME Rem catuppst.sql – CATalog UPgrade PoST-upgrade actions Rem Rem DESCRIPTION Rem This post-upgrade script performs remaining upgrade actions that Rem do not require that the database be open in UPGRADE mode. Rem Automatically apply the latest PSU. Rem Rem NOTES Rem You must be connected AS SYSDBA to run this script. .. .. 22:19:59 SQL> — DBUA_TIMESTAMP: catuppst.sql finished 22:19:59
SQL> SELECT dbms_registry_sys.time_stamp('CATUPPST') as timestamp from dual; TIMESTAMP COMP_TIMESTAMP CATUPPST 2020-01-28 22:19:59 DBUA_TIMESTAMP CATUPPST FINISHED 2020-01-28 22:19:59 DBUA_TIMESTAMP CATUPPST NONE 2020-01-28 22:19:59 */ *** If we had no errors, the "catuppst.sql" script would have been run as part of the upgrade. we need to run it manually if did have errors. *** However can run one more time make sure no errors during execution. SQL>@/u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin/catuppst.sql TIMESTAMP COMP_TIMESTAMP DBRESTART 2020-01-29 02:24:23 DBUA_TIMESTAMP DBRESTART FINISHED 2020-01-29 02:24:23 DBUA_TIMESTAMP DBRESTART NONE 2020-01-29 02:24:23 TIMESTAMP DBUA_TIMESTAMP CATUPPST STARTED 2020-01-29 02:24:23 TIMESTAMP COMP_TIMESTAMP POSTUP_BGN 2020-01-29 02:24:23 DBUA_TIMESTAMP POSTUP_BGN FINISHED 2020-01-29 02:24:23 DBUA_TIMESTAMP POSTUP_BGN NONE 2020-01-29 02:24:23 TIMESTAMP COMP_TIMESTAMP CATREQ_BGN 2020-01-29 02:24:23 DBUA_TIMESTAMP CATREQ_BGN FINISHED 2020-01-29 02:24:23 DBUA_TIMESTAMP CATREQ_BGN NONE 2020-01-29 02:24:23 catrequtlmg: b_StatEvt = TRUE catrequtlmg: b_SelProps = FALSE catrequtlmg: b_UpgradeMode = FALSE catrequtlmg: b_InUtlMig = FALSE TIMESTAMP COMP_TIMESTAMP CATREQ_END 2020-01-29 02:24:23 DBUA_TIMESTAMP CATREQ_END FINISHED 2020-01-29 02:24:23 DBUA_TIMESTAMP CATREQ_END NONE 2020-01-29 02:24:23 catuppst: Dropping library DBMS_DDL_INTERNAL_LIB catuppst: Dropping view _CURRENT_EDITION_OBJ_MIG catuppst: Dropping view _ACTUAL_EDITION_OBJ_MIG catuppst: Dropping view DBA_PART_KEY_COLUMNS_V$_MIG catuppst: Dropping view DBA_SUBPART_KEY_COLUMNS_V$_MIG catuppst: Dropping table OBJ$MIG catuppst: Dropping table USER$MIG catuppst: Dropping table COL$MIG catuppst: Dropping table CLU$MIG catuppst: Dropping table CON$MIG catuppst: Dropping table BOOTSTRAP$MIG catuppst: Dropping table TAB$MIG catuppst: Dropping table TS$MIG catuppst: Dropping table IND$MIG catuppst: Dropping table ICOL$MIG catuppst: Dropping table LOB$MIG catuppst: Dropping table COLTYPE$MIG catuppst: Dropping table SUBCOLTYPE$MIG catuppst: Dropping table NTAB$MIG catuppst: Dropping table REFCON$MIG catuppst: Dropping table OPQTYPE$MIG catuppst: Dropping table ICOLDEP$MIG catuppst: Dropping table VIEWTRCOL$MIG catuppst: Dropping table ATTRCOL$MIG catuppst: Dropping table TYPE_MISC$MIG catuppst: Dropping table LIBRARY$MIG catuppst: Dropping table ASSEMBLY$MIG catuppst: Dropping table TSQ$MIG catuppst: Dropping table FET$MIG TIMESTAMP COMP_TIMESTAMP POSTUP_END 2020-01-29 02:24:24 DBUA_TIMESTAMP POSTUP_END FINISHED 2020-01-29 02:24:24 DBUA_TIMESTAMP POSTUP_END NONE 2020-01-29 02:24:24 TIMESTAMP COMP_TIMESTAMP CATUPPST 2020-01-29 02:24:24 DBUA_TIMESTAMP CATUPPST FINISHED 2020-01-29 02:24:24 DBUA_TIMESTAMP CATUPPST NONE 2020-01-29 02:24:24 SQL> Re-Run postupgrade_fixups.sql [php] SQL> @/home/oracle/PRIM/preupgrade/postupgrade_fixups.sql No errors. No errors.No errors. 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-01-28 20:45:05 For Source Database: PRIM 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
old_time_zones_exist YES None. post_dictionary YES None. post_fixed_objects 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 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. SQL>
25. Reverify INVALID OBJECTS
1 2 3
SQL> select count(*) from dba_objects where status='INVALID'; 0 <----- SQL>
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;NAME GUARANTEE_ TIME
PRE_UPGRADE YES 28-JAN-20 09.05.50.000000000 PM SQL> SQL> !ls -ltr /u01/app/oracle/fast_recovery_area/PRIM/flashback total 1433680 -rw-r-----. 1 oracle oinstall 209723392 Jan 28 21:39 o1_mf_h30dfg5q_.flb -rw-r-----. 1 oracle oinstall 209723392 Jan 28 21:52 o1_mf_h30dfkos_.flb -rw-r-----. 1 oracle oinstall 209723392 Jan 28 21:56 o1_mf_h30gdpcm_.flb -rw-r-----. 1 oracle oinstall 209723392 Jan 28 22:12 o1_mf_h30h4zjr_.flb -rw-r-----. 1 oracle oinstall 209723392 Jan 28 22:15 o1_mf_h30hdtcw_.flb -rw-r-----. 1 oracle oinstall 209723392 Jan 28 22:15 o1_mf_h30jhoxw_.flb -rw-r-----. 1 oracle oinstall 209723392 Jan 29 02:35 o1_mf_h30jc0ct_.flb SQL> SQL> drop restore point PRE_UPGRADE; Restore point dropped. SQL> select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from V$restore_point; no rows selected SQL> SQL> !ls -ltr /u01/app/oracle/fast_recovery_area/PRIM/flashback total 0 <--- SQL>
27. Set COMPATIBALE parameter value to 19.0.0
Warning: If the value of COMPATIBLE parameter is changed to 19.0.0 then if for some reasons database needs to be downgraded to 12.2.0.1 the DBA would not have any option other than export/import to downgrade the database. But if this parameter is left unchanged for sometime to see how the database performs after upgrade then it is very easy and fast to downgrade the database if for some reason it is required to be downgraded.
If you change COMPATIBLE you can directly drop your restore points as they are useless. You can’t use Flashback Database to restore point back across a compatibility change of your database.
SQL> col COMP_ID for a10 col COMP_NAME for a40 col VERSION for a15 set lines 180 set pages 999 select COMP_ID,COMP_NAME,VERSION,STATUS from dba_registry;COMP_ID COMP_NAME VERSION STATUS
NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME) [oracle@rac1 admin]$ [oracle@rac1 admin]$ lsnrctl start LISTENER_PRIM LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 29-JAN-2020 03:16:28 Copyright (c) 1991, 2019, Oracle. All rights reserved. Starting /u01/app/oracle/product/19.0.0/dbhome_1/bin/tnslsnr: please wait… TNSLSNR for Linux: Version 19.0.0.0.0 - Production System parameter file is /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora Log messages written to /u01/app/oracle/diag/tnslsnr/rac1/listener_prim/alert/log.xml Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=prim.oracle.com)(PORT=1524))) Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=prim.oracle.com)(PORT=1524))) STATUS of the LISTENER Alias LISTENER_PRIM Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production Start Date 29-JAN-2020 03:16:28 Uptime 0 days 0 hr. 0 min. 10 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/rac1/listener_prim/alert/log.xml Listening Endpoints Summary… (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=prim.oracle.com)(PORT=1524))) Services Summary… Service "prim.oracle.com" has 1 instance(s). Instance "PRIM", status UNKNOWN, has 1 handler(s) for this service… The command completed successfully [oracle@rac1 admin]$ [oracle@rac1 admin]$ tnsping prim TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 29-JAN-2020 03:16:50 Copyright (c) 1997, 2019, Oracle. All rights reserved. Used parameter files: /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/sqlnet.ora
1 2 3 4
Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = prim.oracle.com)(PORT = 1524)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = prim.oracle.com))) OK (0 msec) [oracle@rac1 admin]$
30. Password File – orapwPRIM
*** REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE *** Password file orapwPRIM copied automatically during upgrade process. No action taken.
1 2 3 4 5 6 7 8 9
[oracle@rac1 dbs]$ pwd /u01/app/oracle/product/19.0.0/dbhome_1/dbs [oracle@rac1 dbs]$ ls -ltr orapwPRIM -rw-r-----. 1 oracle oinstall 3584 Jan 28 22:26 orapwPRIM <---- [oracle@rac1 dbs]$ SQL> show parameter password NAME TYPE VALUE
