Upgrade Oracle Database 11gR2 (11.2.0.4) to 12c (12.2.0.1) using MANUAL
Posted by Mir Sayeed Hassan on October 21st, 2019
Upgrade Oracle Database 11gR2 (11.2.0.4) to 12c (12.2.0.1) using MANUAL
Server Environment
Hostname : testdb Database Name : testdb.localdomain Source DB Version : 11.2.0.4.0 Source DB home location : /u01/app/oracle/product/11.2.0/dbhome_1/ Target DB Version : 12.2.0.1.0 Target DB Home location : /u01/app/oracle/product/12.2.0.1/dbhome_1/
Install the Oracle 12c Software Only, Refer this link:
Create a directory & run the Pre-Upgrade Script
[oracle@testdb ~]$ mkdir -p /home/oracle/preupgrade
[oracle@testdb ~]$ /u01/app/oracle/product/11.2.0/db_1/jdk/bin/java -jar /u01/app/oracle/product/12.2.0/db_1/rdbms/admin/preupgrade.jar FILE DIR /home/oracle/preupgrade Preupgrade generated files: /home/oracle/preupgrade/preupgrade.log /home/oracle/preupgrade/preupgrade_fixups.sql /home/oracle/preupgrade/postupgrade_fixups.sql
[oracle@testdb ~]$ cat /home/oracle/scripts/preupgrade/preupgrade.log Report generated by Oracle Database Pre-Upgrade Information Tool Version 12.2.0.1.0 Upgrade-To version: 12.2.0.1.0 ======================================= Status of the database prior to upgrade ======================================= Database Name: TESTDB Container Name: Not Applicable in Pre-12.1 database Container ID: Not Applicable in Pre-12.1 database Version: 11.2.0.4.0 Compatible: 11.2.0.4.0 Blocksize: 8192 Platform: Linux x86 64-bit Timezone File: 14 Database log mode: ARCHIVELOG Readonly: FALSE Edition: EE Oracle Component Upgrade Action Current Status ---------------- -------------- -------------- Oracle Server [to be upgraded] VALID JServer JAVA Virtual Machine [to be upgraded] VALID Oracle XDK for Java [to be upgraded] VALID Oracle Workspace Manager [to be upgraded] VALID OLAP Analytic Workspace [to be upgraded] VALID Oracle Enterprise Manager Repository [to be upgraded] VALID Oracle Text [to be upgraded] VALID Oracle XML Database [to be upgraded] VALID Oracle Java Packages [to be upgraded] VALID Oracle Multimedia [to be upgraded] VALID Oracle Spatial [to be upgraded] VALID Expression Filter [to be upgraded] VALID Rule Manager [to be upgraded] VALID Oracle Application Express [to be upgraded] VALID cle OLAP API [to be upgraded] VALID ============= BEFORE UPGRADE ============= Run /preupgrade_fixups.sql to complete all of the BEFORE UPGRADE action items below marked with '(AUTOFIXUP)'. REQUIRED ACTIONS ================ + Adjust TABLESPACE SIZES as needed. Auto 12.2.0.1.0 Tablespace Size Extend Min Size Action ---------- ---------- -------- ---------- ------ EXAMPLE 347 MB DISABLED 310 MB None SYSAUX 640 MB ENABLED 1540 MB None SYSTEM 760 MB ENABLED 1265 MB None TEMP 29 MB ENABLED 150 MB None UNDOTBS1 130 MB ENABLED 400 MB None Note that 12.2.0.1.0 minimum sizes are estimates. If you plan to upgrade multiple pluggable databases concurrently, then you must ensure that the UNDO tablespace size is equal to at least the number of pluggable databases that you upgrade concurrently, multiplied by that minimum. Failing to allocate sufficient space can cause the upgrade to fail. + Set DB_RECOVERY_FILE_DEST_SIZE initialization parameter to at least 6293MB. Check alert log during the upgrade to ensure there is remaining free space available in the recovery area. DB_RECOVERY_FILE_DEST_SIZE is set at 4182 MB. There is currently 3204MB of free space remaining, which may not be adequate for the upgrade. Currently: Fast recovery area : /u01/app/oracle/fast_recovery_area Limit : 4182 MB Used : 978 MB Available : 3204 MB The database has archivelog mode enabled, and the upgrade process will need free space to generate archived logs to the recovery area specified by initialization parameter DB_RECOVERY_FILE_DEST. The logs generated must not overflow the limit set by DB_RECOVERY_FILE_DEST_SIZE, as the upgrade may not proceed if the database stops responding. RECOMMENDED ACTIONS =================== + Remove the EM repository. - Copy the $ORACLE_HOME/rdbms/admin/emremove.sql script from the target 12.2.0.1.0 ORACLE_HOME into the source 11.2.0.4.0 ORACLE_HOME. Step 1: If database control is configured, stop EM Database Control, using the following command $> emctl stop dbconsole Step 2: Connect to the database using the SYS account AS SYSDBA SET ECHO ON; SET SERVEROUTPUT ON; @emremove.sql Without the set echo and serveroutput commands, you will not be able to follow the progress of the script. The database has an Enterprise Manager Database Control repository. Starting with Oracle Database 12c, the local Enterprise Manager Database Control does not exist anymore. The repository will be removed from your database during the upgrade. This step can be manually performed before the upgrade to reduce downtime. + Remove OLAP Catalog by running the 11.2.0.4.0 SQL script $ORACLE_HOME/olap/admin/catnoamd.sql script. The OLAP Catalog component, AMD, exists in the database. Starting with Oracle Database 12c, the OLAP Catalog (OLAP AMD) is desupported and will be automatically marked as OPTION OFF during the database upgrade if present. Oracle recommends removing OLAP Catalog (OLAP AMD) before database upgrade. + (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 11.2.0.4 Oracle Database Performance Tuning Guide. + 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 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. + Please make sure that all the MVs are refreshed and sys.sumdelta$ becomes empty before doing upgrade, unless you have strong business reasons not to do so. You can use dbms_mview.refresh() to refresh the MVs except those stale ones to be kept due to business need. If there are any stale MVs depending on changes in sys.sumdelta$, do not truncate it, because doing so will cause wrong results after refresh. There is one or more non-fresh MV in the database or sumdelta$ is not empty. Oracle recommends that all materialized views (MV's) are refreshed before upgrading the database because this will clear the MV logs and the sumdelta$ table, and make the UPGRADE process faster. If you choose to not refresh some MVs, the change data for those MV's will be carried through the UPGRADE process. After UPGRADE, you can refresh the MV's and MV incremental refresh should work in normal cases. INFORMATION ONLY ================ + Consider upgrading APEX manually, before the database upgrade. The database contains APEX version 3.2.1.00.12 and will need to be upgraded to at least version 5.0.4.00.12. To reduce database upgrade time, you can upgrade APEX manually before the database upgrade. Refer to My Oracle Support Note 1088970.1 for information on APEX installation upgrades. ============= AFTER UPGRADE ============= Run <preupgradeLogDirPath> /postupgrade_fixups.sql to complete all of the AFTER UPGRADE action items below marked with '(AUTOFIXUP)'. REQUIRED ACTIONS ================ None RECOMMENDED ACTIONS =================== + If you use the -T option for the database upgrade, then run $ORACLE_HOME/rdbms/admin/utluptabdata.sql after the upgrade is complete, to VALIDATE and UPGRADE any user tables affected by changes to Oracle-Maintained types. There are user tables dependent on Oracle-Maintained object types. If the -T option is used to set user tablespaces to READ ONLY during the upgrade, user tables in those tablespaces, that are dependent on Oracle-Maintained types, will not be automatically upgraded. If a type is evolved during the upgrade, any dependent tables need to be re-validated and upgraded to the latest type version AFTER the database upgrade completes. + Upgrade the database time zone version using the DBMS_DST package. The database is using timezone datafile version 14 and the target 12.2.0.1.0 database ships with timezone datafile version 26. Oracle recommends using the most recent timezone data. For further information, refer to My Oracle Support Note 1585343.1. + (AUTOFIXUP) Gather dictionary statistics after the upgrade using the command: EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS; Oracle recommends gathering dictionary statistics after upgrade. Dictionary statistics provide essential information to the Oracle optimizer to help it find efficient SQL execution plans. After a database upgrade, statistics need to be re-gathered as there can now be tables that have significantly changed during the upgrade or new tables that do not have statistics gathered yet. + Gather statistics on fixed objects two weeks after the upgrade using the command: EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS; This recommendation is given for all preupgrade runs. Fixed object statistics provide essential information to the Oracle optimizer to help it find efficient SQL execution plans. Those statistics are specific to the Oracle Database release that generates them, and can be stale upon database upgrade. INFORMATION ONLY ================ + Check the Oracle documentation for the identified components for their specific upgrade procedure. The database upgrade script will not upgrade the following Oracle components: OLAP Catalog,OWB The Oracle database upgrade script upgrades most, but not all Oracle Database components that may be installed. Some components that are not upgraded may have their own upgrade scripts, or they may e deprecated or obsolete.
--------------------------- STOP THE ENTERPRISE MANAGER ---------------------------- [oracle@testdb ~]$ emctl stop dbconsole Oracle Enterprise Manager 11g Database Control Release 11.2.0.4.0 Copyright (c) 1996, 2013 Oracle Corporation. All rights reserved. http://testdb.localdomain:1158/em/console/aboutApplication Stopping Oracle Enterprise Manager 11g Database Control ... ... Stopped.
SQL> select instance_name,status,version from V$instance; INSTANCE_NAME STATUS VERSION ---------- ------------ ----------------- testdb OPEN 11.2.0.4.0
Remove the Enterprise manager from Database
[oracle@testdb admin]$ !sq
sqlplus / as sysdba
SQL> @/u01/app/oracle/product/12.2.0/db_1/rdbms/admin/emremove.sql;
old 70: IF (upper('&LOGGING') = 'VERBOSE')
new 70: IF (upper('VERBOSE') = 'VERBOSE')
PL/SQL procedure successfully completed.
Remove the OLAP Catalog
SQL> @/u01/app/oracle/product/11.2.0/db_1/olap/admin/catnoamd.sql; Synonym dropped. Synonym dropped. Synonym dropped. .. ..
Check the Processes & Gather Dictionary STATS, It should be more than 300, If its now try to increase by using : alter system set processes=600;
SQL> show parameter processes NAME TYPE VALUE ---------------- ----------- ------------------------------ processes integer 600
SQL> SET ECHO ON; SQL> SET SERVEROUTPUT ON; SQL> EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS; PL/SQL procedure successfully completed.
Purge Recyclebin
SQL> PURGE DBA_RECYCLEBIN; DBA Recyclebin purged.
Refresh the Materialize Views & Run the preupdate_fixups.sql as shown below
SQL> declare 2 list_failures integer(3) :=0; 3 begin 4 DBMS_MVIEW.REFRESH_ALL_MVIEWS(list_failures,'C','', TRUE, FALSE); 5 end; 6 / PL/SQL procedure successfully completed.
SQL> SET ECHO ON; SQL> SET SERVEROUTPUT ON; SQL> @/home/oracle/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: 12.2.0.1.0 Build: 1 SQL> REM Generated on: 2019-10-20 19:48:40 SQL> REM SQL> REM Source Database: TESTDB SQL> REM Source Database Version: 11.2.0.4.0 SQL> REM For Upgrade to Version: 12.2.0.1.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: 12.2.0.1.0 Build: 1 Generated on: 2019-10-20 19:48:40 for Source Database: TESTDB Source Database Version: 11.2.0.4.0 For Upgrade to Version: 12.2.0.1.0 Fixup Check Name Status Further DBA Action ---------- ------ ------------------ min_recovery_area_size Failed Manual fixup required. mim_present Passed None amd_exists Passed None dictionary_stats Passed None trgowner_no_admndbtrg Passed None mv_refresh Passed None apex_upgrade_msg Failed Manual fixup recommended. PL/SQL procedure successfully completed.
Check the archivelog location & size assign to database, if its low try to increase
SQL> show parameter db_recovery_file_dest NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string /u01/app/oracle/fast_recovery_area db_recovery_file_dest_size big integer 4182M
Increase the archivelog size
SQL> alter system set db_recovery_file_dest_size=15g; System altered.
Stop listener
[oracle@testdb ~]$ lsnrctl stop LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 20-OCT-2019 20:13:24 Copyright (c) 1991, 2013, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=testdb.localdomain)(PORT=1521))) The command completed successfully
Check the flashback status
SQL> select flashback_on from v$database; FLASHBACK_ON ------------- NO
Check the database status with archivelog
SQL> select name,open_mode,log_mode from v$database; NAME OPEN_MODE LOG_MODE --------- -------------------- ----------- TESTDB READ WRITE ARCHIVELOG
Check the database compatibility
SQL> show parameter compatible NAME TYPE VALUE ---------------- ----------- ------------------------------ compatible string 11.2.0.4.0
Verify the archivelog location & increased size
SQL> show parameter recovery NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string /u01/app/oracle/fast_recovery_area db_recovery_file_dest_size big integer 15G recovery_parallelism integer 0
Check the restore point
SQL> select * from V$restore_point; no rows selected
Create the restore point in database
SQL> create restore point pre_upgrade_20oct19 guarantee flashback database; Restore point created.
Verify the restore point created
SQL> col name for a10 SQL> col GUARANTEE_FLASHBACK_DATABASE for a15 SQL> col TIME for a40 SQL> set lines 250 SQL> select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from V$restore_point; NAME GUARANTEE_ TIME ------ ---------- -------------------------------------------------- PRE_UPGRADE_20OCT19 YES 20-OCT-19 08.14.38.000000000 PM
Shutdown the database
SQL> shu immediate Database closed. Database dismounted. ORACLE instance shut down.
Copy the init & password from Oracle 11gr2 default location to Oracle 12c default location
[oracle@testdb ~]$ cd $ORACLE_HOME/dbs
[oracle@testdb dbs]$ pwd /u01/app/oracle/product/11.2.0/db_1/dbs
[oracle@testdb dbs]$ ls hc_testdb.dat init.ora inittestdb.ora lkTESTDB orapwtestdb spfiletestdb.ora
[oracle@testdb dbs]$ cp orapwtestdb spfiletestdb.ora /u01/app/oracle/product/12.2.0/db_1/db dbjava/ dbs/
[oracle@testdb dbs]$ cp orapwtestdb spfiletestdb.ora /u01/app/oracle/product/12.2.0/db_1/dbs/ [oracle@testdb dbs]$ ll /u01/app/oracle/product/12.2.0/db_1/dbs/ total 12 -rw-r--r--. 1 oracle oracle 3079 May 15 2015 init.ora -rw-r-----. 1 oracle oracle 1536 Oct 20 20:17 orapwtestdb -rw-r-----. 1 oracle oracle 3584 Oct 20 20:17 spfiletestdb.ora
SET THE 12C ENVIRNONMENT & START THE UPGRADE MODE
[oracle@testdb ~]$ export ORACLE_HOME=/u01/app/oracle/product/12.2.0/db_1/
[oracle@testdb ~]$ export ORACLE_SID=testdb
[oracle@testdb ~]$ PATH=/u01/app/oracle/product/12.2.0/db_1/bin/:$PATH; export PATH
[oracle@testdb ~]$ which sqlplus /u01/app/oracle/product/12.2.0/db_1/bin/sqlplus
Start database in Upgrade state
[oracle@testdb ~]$ sqlplus / as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Sun Oct 20 20:20:35 2019 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to an idle instance. SQL> startup upgrade ORACLE instance started. Total System Global Area 1828716544 bytes Fixed Size 8621856 bytes Variable Size 654311648 bytes Database Buffers 1157627904 bytes Redo Buffers 8155136 bytes Database mounted. Database opened.
Verify the DB Status
SQL>select instance_name,version,status from v$instance; INSTANCE_NAME VERSION STATUS ---------------- ----------------- ------------ testdb 12.2.0.1.0 OPEN MIGRATE
Run CATCTL.PL & CATUPGRD.SQL
[oracle@testdb ~]$ cd /u01/app/oracle/product/12.2.0/db_1/rdbms/admin/ [oracle@testdb admin]$ nohup /u01/app/oracle/product/12.2.0/db_1/perl/bin/perl catctl.pl -l /home/oracle/whileupgrade -n 4 catupgrd.sql & [1] 22739 [oracle@testdb admin]$ nohup: ignoring input and appending output to ‘nohup.out’ [1]+ Done nohup /u01/app/oracle/product/12.2.0/db_1/perl/bin/perl catctl.pl -l /home/oracle/whileupgrade -n 4 catupgrd.sql
[oracle@testdb admin]$ disown -bash: disown: current: no such job
[oracle@testdb admin]$ ps -ef | grep -i catctl.pl oracle 31213 5239 0 21:30 pts/1 00:00:00 grep --color=auto -i catctl.pl
Verify the output log
[oracle@testdb admin]$ more nohup.out Argument list for [catctl.pl] Run in c = 0 Do not run in C = 0 Input Directory d = 0 Echo OFF e = 1 Simulate E = 0 Forced cleanup F = 0 Log Id i = 0 Child Process I = 0 Log Dir l = /home/oracle/whileupgrade Priority List Name L = 0 Upgrade Mode active M = 0 SQL Process Count n = 4 SQL PDB Process Count N = 0 Open Mode Normal o = 0 Start Phase p = 0 End Phase P = 0 Reverse Order r = 0 AutoUpgrade Resume R = 0 Script s = 0 Serial Run S = 0 RO User Tablespaces T = 0 Display Phases y = 0 Debug catcon.pm z = 0 Debug catctl.pl Z = 0 catctl.pl VERSION: [12.2.0.1.0] STATUS: [production] BUILD: [RDBMS_12.2.0.1.0_LINUX.X64_170125] /u01/app/oracle/product/12.2.0/db_1/rdbms/admin/orahome = [/u01/app/oracle/product/12.2.0/db_1/] /u01/app/oracle/product/12.2.0/db_1//bin/orabasehome = [/u01/app/oracle/product/12.2.0/db_1/] catctlGetOrabase = [/u01/app/oracle/product/12.2.0/db_1/] Analyzing file /u01/app/oracle/product/12.2.0/db_1/rdbms/admin/catupgrd.sql Log file directory = [/home/oracle/whileupgrade] catcon: ALL catcon-related output will be written to [/home/oracle/whileupgrade/catupgrd_catcon_22739.lst] catcon: See [/home/oracle/whileupgrade/catupgrd*.log] files for output generated by scripts catcon: See [/home/oracle/whileupgrade/catupgrd_*.lst] files for spool files, if any Number of Cpus = 6 Database Name = testdb DataBase Version = 11.2.0.4.0 Parallel SQL Process Count = 4 Components in [testdb] Installed [APEX APS CATALOG CATJAVA CATPROC CONTEXT JAVAVM ORDIM OWM SDO XDB XML XOQ] Not Installed [DV EM MGW ODM OLS RAC WK] ------------------------------------------------------ Phases [0-115] Start Time:[2019_10_20 20:23:47] ------------------------------------------------------ *********** Executing Change Scripts *********** Serial Phase #:0 [testdb] Files:1 Time: 137s *************** Catalog Core SQL *************** serial Phase #:1 [testdb] Files:5 Time: 68s Restart Phase #:2 [testdb] Files:1 Time: 0s *********** Catalog Tables and Views *********** Parallel Phase #:3 [testdb] Files:19 Time: 15s Restart Phase #:4 [testdb] Files:1 Time: 0s ************* Catalog Final Scripts ************ Serial Phase #:5 [testdb] Files:6 Time: 27s ***************** Catproc Start **************** Serial Phase #:6 [testdb] Files:1 Time: 22s ***************** Catproc Types **************** Serial Phase #:7 [testdb] Files:2 Time: 21s Restart Phase #:8 [testdb] Files:1 Time: 0s **************** Catproc Tables **************** Parallel Phase #:9 [testdb] Files:69 Time: 23s Restart Phase #:10 [testdb] Files:1 Time: 0s ************* Catproc Package Specs ************ Serial Phase #:11 [testdb] Files:1 Time: 61s Restart Phase #:12 [testdb] Files:1 Time: 0s ************** Catproc Procedures ************** Parallel Phase #:13 [testdb] Files:97 Time: 8s Restart Phase #:14 [testdb] Files:1 Time: 1s Parallel Phase #:15 [testdb] Files:118 Time: 12s Restart Phase #:16 [testdb] Files:1 Time: 0s Serial Phase #:17 [testdb] Files:13 Time: 5s Restart Phase #:18 [testdb] Files:1 Time: 0s ***************** Catproc Views **************** Parallel Phase #:19 [testdb] Files:33 Time: 20s Restart Phase #:20 [testdb] Files:1 Time: 0s Serial Phase #:21 [testdb] Files:3 Time: 13s Restart Phase #:22 [testdb] Files:1 Time: 0s Parallel Phase #:23 [testdb] Files:24 Time: 119s Restart Phase #:24 [testdb] Files:1 Time: 0s Parallel Phase #:25 [testdb] Files:11 Time: 71s Restart Phase #:26 [testdb] Files:1 Time: 0s Serial Phase #:27 [testdb] Files:1 Time: 0s Serial Phase #:28 [testdb] Files:3 Time: 6s Serial Phase #:29 [testdb] Files:1 Time: 0s Restart Phase #:30 [testdb] Files:1 Time: 0s *************** Catproc CDB Views ************** Serial Phase #:31 [testdb] Files:1 Time: 0s Restart Phase #:32 [testdb] Files:1 Time: 0s Serial Phase #:34 [testdb] Files:1 Time: 0s ***************** Catproc PLBs ***************** Serial Phase #:35 [testdb] Files:283 Time: 34s Serial Phase #:36 [testdb] Files:1 Time: 0s Restart Phase #:37 [testdb] Files:1 Time: 0s Serial Phase #:38 [testdb] Files:1 Time: 8s Restart Phase #:39 [testdb] Files:1 Time: 0s *************** Catproc DataPump *************** Serial Phase #:40 [testdb] Files:3 Time: 82s Restart Phase #:41 [testdb] Files:1 Time: 1s ****************** Catproc SQL ***************** Parallel Phase #:42 [testdb] Files:13 Time: 72s Restart Phase #:43 [testdb] Files:1 Time: 0s Parallel Phase #:44 [testdb] Files:12 Time: 16s Restart Phase #:45 [testdb] Files:1 Time: 1s Parallel Phase #:46 [testdb] Files:2 Time: 1s Restart Phase #:47 [testdb] Files:1 Time: 0s ************* Final Catproc scripts ************ Serial Phase #:48 [testdb] Files:1 Time: 9s Restart Phase #:49 [testdb] Files:1 Time: 0s ************** Final RDBMS scripts ************* Serial Phase #:50 [testdb] Files:1 Time: 34s ************ Upgrade Component Start *********** Serial Phase #:51 [testdb] Files:1 Time: 1s Restart Phase #:52 [testdb] Files:1 Time: 0s **************** Upgrading Java **************** Serial Phase #:53 [testdb] Files:1 Time: 394s Restart Phase #:54 [testdb] Files:1 Time: 1s ***************** Upgrading XDK **************** Serial Phase #:55 [testdb] Files:1 Time: 54s Restart Phase #:56 [testdb] Files:1 Time: 0s ********* Upgrading APS,OLS,DV,CONTEXT ********* Serial Phase #:57 [testdb] Files:1 Time: 102s ***************** Upgrading XDB **************** Restart Phase #:58 [testdb] Files:1 Time: 0s Serial Phase #:60 [testdb] Files:3 Time: 41s Serial Phase #:61 [testdb] Files:3 Time: 13s Parallel Phase #:62 [testdb] Files:9 Time: 4s Parallel Phase #:63 [testdb] Files:24 Time: 3s Serial Phase #:64 [testdb] Files:4 Time: 11s serial Phase #:65 [testdb] Files:1 Time: 0s Serial Phase #:66 [testdb] Files:30 Time: 5s Serial Phase #:67 [testdb] Files:1 Time: 0s Parallel Phase #:68 [testdb] Files:6 Time: 4s Serial Phase #:69 [testdb] Files:2 Time: 28s Serial Phase #:70 [testdb] Files:3 Time: 124s Restart Phase #:71 [testdb] Files:1 Time: 1s ********* Upgrading CATJAVA,OWM,MGW,RAC ******** Serial Phase #:72 [testdb] Files:1 Time: 129s **************** Upgrading ORDIM *************** Restart Phase #:73 [testdb] Files:1 Time: 0s Serial Phase #:75 [testdb] Files:1 Time: 1s Parallel Phase #:76 [testdb] Files:2 Time: 50s Serial Phase #:77 [testdb] Files:1 Time: 79s Restart Phase #:78 [testdb] Files:1 Time: 0s Parallel Phase #:79 [testdb] Files:2 Time: 18s serial Phase #:80 [testdb] Files:2 Time: 2s ***************** Upgrading SDO **************** Restart Phase #:81 [testdb] Files:1 Time: 0s Serial Phase #:83 [testdb] Files:1 Time: 51s Serial Phase #:84 [testdb] Files:1 Time: 1s Restart Phase #:85 [testdb] Files:1 Time: 1s Serial Phase #:86 [testdb] Files:1 Time: 51s Restart Phase #:87 [testdb] Files:1 Time: 0s Parallel Phase #:88 [testdb] Files:3 Time: 123s Restart Phase #:89 [testdb] Files:1 Time: 0s Serial Phase #:90 [testdb] Files:1 Time: 6s Restart Phase #:91 [testdb] Files:1 Time: 0s Serial Phase #:92 [testdb] Files:1 Time: 4s Restart Phase #:93 [testdb] Files:1 Time: 0s Parallel Phase #:94 [testdb] Files:4 Time: 57s Restart Phase #:95 [testdb] Files:1 Time: 0s Serial Phase #:96 [testdb] Files:1 Time: 1s Restart Phase #:97 [testdb] Files:1 Time: 0s Serial Phase #:98 [testdb] Files:2 Time: 72s Restart Phase #:99 [testdb] Files:1 Time: 0s Serial Phase #:100 [testdb] Files:1 Time: 0s Restart Phase #:101 [testdb] Files:1 Time: 0s *********** Upgrading Misc. ODM, OLAP ********** Serial Phase #:102 [testdb] Files:1 Time: 35s **************** Upgrading APEX **************** Restart Phase #:103 [testdb] Files:1 Time: 0s Serial Phase #:104 [testdb] Files:1 Time: 1207s Restart Phase #:105 [testdb] Files:1 Time: 0s *********** Final Component scripts *********** Serial Phase #:106 [testdb] Files:1 Time: 1s ************* Final Upgrade scripts ************ Serial Phase #:107 [testdb] Files:1 Time: 116s ********** End PDB Application Upgrade ********* Serial Phase #:108 [testdb] Files:1 Time: 0s ******************* Migration ****************** Serial Phase #:109 [testdb] Files:1 Time: 65s Serial Phase #:110 [testdb] Files:1 Time: 0s Serial Phase #:111 [testdb] Files:1 Time: 50s ***************** Post Upgrade ***************** Serial Phase #:112 [testdb] Files:1 Time: 132s **************** Summary report **************** Serial Phase #:113 [testdb] Files:1 Time: 2s Serial Phase #:114 [testdb] Files:1 Time: 0s Serial Phase #:115 [testdb] Files:1 Time: 25s --------------------------------------------------- Phases [0-115] End Time:[2019_10_20 21:29:39] ------------------------------------------------------ Grand Total Time: 3964s LOG FILES: (/home/oracle/whileupgrade/catupgrd*.log) Upgrade Summary Report Located in: /home/oracle/whileupgrade/upg_summary.log Grand Total Upgrade Time: [0d:1h:6m:4s]
DB Status
[oracle@testdb admin]$ !sq sqlplus / as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Sun Oct 20 21:31:51 2019 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to an idle instance. SQL>
Start the database normally
SQL> startup; ORACLE instance started. Total System Global Area 1828716544 bytes Fixed Size 8621856 bytes Variable Size 654311648 bytes Database Buffers 1157627904 bytes Redo Buffers 8155136 bytes Database mounted. Database opened.
Verify the Status of
SQL> col COMP_ID for a50 SQL>col COMP_NAME for a30 SQL> col VERSION for a20 SQL>set lines 250 SQL> set pages 9999 SQL>select COMP_ID,COMP_NAME,VERSION,STATUS from dba_registry; COMP_ID COMP_NAME VERSION STATUS ---------- ---------------------------------------- --------------- -------------------------------------------- CATALOG Oracle Database Catalog Views 12.2.0.1.0 UPGRADED CATPROC Oracle Database Packages and Types 12.2.0.1.0 UPGRADED JAVAVM JServer JAVA Virtual Machine 12.2.0.1.0 UPGRADED XML Oracle XDK 12.2.0.1.0 UPGRADED CATJAVA Oracle Database Java Packages 12.2.0.1.0 UPGRADED APS OLAP Analytic Workspace 12.2.0.1.0 UPGRADED OWM Oracle Workspace Manager 12.2.0.1.0 UPGRADED CONTEXT Oracle Text 12.2.0.1.0 UPGRADED XDB Oracle XML Database 12.2.0.1.0 UPGRADED ORDIM Oracle Multimedia 12.2.0.1.0 UPGRADED SDO Spatial 12.2.0.1.0 UPGRADED XOQ Oracle OLAP API 12.2.0.1.0 UPGRADED APEX Oracle Application Express 5.0.4.00.12 UPGRADED 13 rows selected.
Run POSTUPGRADE_FIXUPS.SQL Script
SQL> @postupgrade_fixups.sql Session altered. PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. Package created. No errors. Package body created. No errors. Package created. No errors. Package body created. No errors. Executing Oracle POST-Upgrade Fixup Script Auto-Generated by: Oracle Preupgrade Script Version: 12.2.0.1.0 Build: 1 Generated on: 2019-10-20 19:48:41 For Source Database: TESTDB Source Database Version: 11.2.0.4.0 For Upgrade to Version: 12.2.0.1.0 Fixup Check Name Status Further DBA Action ---------- ------ ------------------ depend_usr_tables Failed Manual fixup recommended. old_time_zones_exist Failed Manual fixup recommended. post_dictionary Passed None fixed_objects Passed None upg_by_std_upgrd Passed None PL/SQL procedure successfully completed. Session altered.
Update the timezone of DB, Here you need to download the DBMS_DST_scriptsV1.9.zip from Oracle support with ID: 1585343.1 & config
[oracle@testdb ~]$ cd /u01/app/oracle/product/12.2.0 [oracle@testdb 12.2.0]$ ls db_1 timezone_script [oracle@testdb 12.2.0]$ cd timezone_script/ [oracle@testdb timezone_script]$ ls DBMS_DST_scriptsV1.9.zip [oracle@testdb timezone_script]$ unzip DBMS_DST_scriptsV1.9.zip Archive: DBMS_DST_scriptsV1.9.zip creating: DBMS_DST_scriptsV1.9/ inflating: DBMS_DST_scriptsV1.9/countstarTSTZ.sql inflating: DBMS_DST_scriptsV1.9/countstatsTSTZ.sql inflating: DBMS_DST_scriptsV1.9/upg_tzv_apply.sql inflating: DBMS_DST_scriptsV1.9/upg_tzv_check.sql [oracle@testdb timezone_script]$ ls DBMS_DST_scriptsV1.9 DBMS_DST_scriptsV1.9.zip [oracle@testdb timezone_script]$ cd DBMS_DST_scriptsV1.9/ [oracle@testdb DBMS_DST_scriptsV1.9]$ ls countstarTSTZ.sql countstatsTSTZ.sql upg_tzv_apply.sql upg_tzv_check.sql
Check the current DB timezone
[oracle@testdb DBMS_DST_scriptsV1.9]$ !sq sqlplus / as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Sun Oct 20 22:05:01 2019 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL> @upg_tzv_check.sql; 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 12.2.0.1 . 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 DSTv26 . 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 upg_tzv_apply.sql to do the actual RDBMS DST update. INFO: Note that the upg_tzv_apply.sql script will INFO: restart the database 2 times WITHOUT any confirmation or prompt.
SQL> SELECT version FROM v$timezone_file;
VERSION
-------
14
1 row selected.
Apply the timezone script
SQL> @upg_tzv_apply.sql; INFO: If an ERROR occurs the script will EXIT sqlplus. INFO: The database RDBMS DST version will be updated to DSTv26 . 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 1828716544 bytes Fixed Size 8621856 bytes Variable Size 654311648 bytes Database Buffers 1157627904 bytes Redo Buffers 8155136 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 1828716544 bytes Fixed Size 8621856 bytes Variable Size 654311648 bytes Database Buffers 1157627904 bytes Redo Buffers 8155136 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: "IX"."AQ$_ORDERS_QUEUETABLE_L" Number of failures: 0 Table list: "IX"."AQ$_ORDERS_QUEUETABLE_S" Number of failures: 0 Table list: "IX"."AQ$_STREAMS_QUEUE_TABLE_L" Number of failures: 0 Table list: "IX"."AQ$_STREAMS_QUEUE_TABLE_S" 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 Table list: "APEX_050000"."WWV_FLOW_DEBUG_MESSAGES" Number of failures: 0 Table list: "APEX_050000"."WWV_FLOW_DEBUG_MESSAGES2" Number of failures: 0 Table list: "APEX_050000"."WWV_FLOW_FEEDBACK" Number of failures: 0 Table list: "APEX_050000"."WWV_FLOW_FEEDBACK_FOLLOWUP" Number of failures: 0 Table list: "APEX_050000"."WWV_FLOW_WORKSHEET_NOTIFY" 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 DSTv26 . INFO: The RDBMS DST update is successfully finished. INFO: Make sure to exit this sqlplus session. INFO: Do not use it for timezone related selects.
Verify the timezone as per requirement
SQL> SELECT version FROM v$timezone_file;
VERSION
---------
26
1 row selected.
---------------------------------------------------- AGAIN RUN THE POSTSCRIPT AS "postupgrade_fixups.sql" ---------------------------------------------------- SQL> @/home/oracle/preupgrade/postupgrade_fixups.sql; Session altered. PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. Package created. No errors. Package body created. No errors. 1 row selected. Package created. No errors. Package body created. No errors. Executing Oracle POST-Upgrade Fixup Script Auto-Generated by: Oracle Preupgrade Script Version: 12.2.0.1.0 Build: 1 Generated on: 2019-10-20 19:48:41 For Source Database: TESTDB Source Database Version: 11.2.0.4.0 For Upgrade to Version: 12.2.0.1.0 Fixup Check Name Status Further DBA Action ---------- ------ ------------------ depend_usr_tables Failed Manual fixup recommended. old_time_zones_exist Passed None post_dictionary Passed None fixed_objects Passed None upg_by_std_upgrd Passed None PL/SQL procedure successfully completed. Session altered.
Note: depend_usr_tables Failed Manual fixup – We can ignore this. old_time_zones_exist
Run “utlu122s.sql” script
SQL> @/u01/app/oracle/product/12.2.0/db_1/rdbms/admin/utlu122s.sql;
Oracle Database 12.2 Post-Upgrade Status Tool 10-20-2019 22:10:23
Component Current Version Elapsed Time
Name Status Number HH:MM:SS
Oracle Server UPGRADED 12.2.0.1.0 00:14:45
JServer JAVA Virtual Machine UPGRADED 12.2.0.1.0 00:06:33
Oracle Workspace Manager UPGRADED 12.2.0.1.0 00:01:49
OLAP Analytic Workspace UPGRADED 12.2.0.1.0 00:00:24
Oracle OLAP API UPGRADED 12.2.0.1.0 00:00:17
Oracle XDK UPGRADED 12.2.0.1.0 00:00:53
Oracle Text UPGRADED 12.2.0.1.0 00:01:16
Oracle XML Database UPGRADED 12.2.0.1.0 00:03:52
Oracle Database Java Packages UPGRADED 12.2.0.1.0 00:00:19
Oracle Multimedia UPGRADED 12.2.0.1.0 00:02:28
Spatial UPGRADED 12.2.0.1.0 00:06:05
Oracle Application Express UPGRADED 5.0.4.00.12 00:20:05
Final Actions 00:03:01
Post Upgrade 00:02:12
Total Upgrade Time: 01:04:32
Database time zone version is 26. It meets current release needs.
Summary Report File = /home/oracle/whileupgrade/upg_summary.log
-----------------------------
RUN THE "CATUPPST.SQL" SCRIPT
-----------------------------
22:10:23 SQL> @/u01/app/oracle/product/12.2.0/db_1/rdbms/admin/catuppst.sql;
22:10:49 SQL> Rem
22:10:49 SQL> Rem $Header: rdbms/admin/catuppst.sql /main/52 2016/06/14 23:41:26 cmlim Exp $
22:10:49 SQL> Rem
22:10:49 SQL> Rem catuppst.sql
22:10:49 SQL> Rem
22:10:49 SQL> Rem Copyright (c) 2006, 2016, Oracle and/or its affiliates.
22:10:49 SQL> Rem All rights reserved.
22:10:49 SQL> Rem
22:10:49 SQL> Rem NAME
22:10:49 SQL> Rem catuppst.sql - CATalog UPgrade PoST-upgrade actions
22:10:49 SQL> Rem
22:10:49 SQL> Rem DESCRIPTION
22:10:49 SQL> Rem This post-upgrade script performs remaining upgrade actions that
22:10:49 SQL> Rem do not require that the database be open in UPGRADE mode.
22:10:49 SQL> Rem Automatically apply the latest PSU.
22:10:49 SQL> Rem
22:10:49 SQL> Rem NOTES
22:10:49 SQL> Rem You must be connected AS SYSDBA to run this script.
22:10:49 SQL> Rem
22:10:49 SQL> Rem MODIFIED (MM/DD/YY)
22:10:49 SQL> Rem cmlim 06/06/16 - bug 23215791: add more DBUA_TIMESTAMPS during db
22:10:49 SQL> Rem upgrades
22:10:49 SQL> Rem anupkk 04/03/16 - Bug 22917286: Moved call to olstrig.sql to
22:10:49 SQL> Rem olsdbmig.sql
22:10:49 SQL> Rem raeburns 02/29/16 - Bug 22820096: revert ALTER TYPE to default
22:10:49 SQL> Rem CASCADE
22:10:49 SQL> Rem rmorant 02/11/16 - Bug22340563 add parallel hint
22:10:49 SQL> Rem atomar 02/04/16 - move aq action to release specific script
22:10:49 SQL> Rem raeburns 12/09/15 - Bug 22175911: add SERVEROUTPUT OFF after
22:10:49 SQL> Rem catuptabdata.sql
22:10:49 SQL> Rem rmorant 11/27/15 - bug22271668 add append hint
22:10:49 SQL> Rem welin 11/11/15 - Bug 21099929: 12.2 cleanup
22:10:49 SQL> Rem nneeluru 09/14/15 - Add Java name translation for longer identifiers
22:10:49 SQL> Rem raeburns 08/24/15 - use catuptabdata.sql instead of inline code
22:10:49 SQL> Rem raeburns 06/05/15 - Bug 21322727: upgrade Oracle-maintained table data
22:10:49 SQL> Rem rmorant 05/19/15 - Bug19651064 added upgrade actions
22:10:49 SQL> Rem amadan 05/08/15 - Bug 21027329 remove AQ upgrade dequeue log
22:10:49 SQL> Rem rpang 04/28/15 - Bug 20723336: remove network ACL check
22:10:49 SQL> Rem jaeblee 03/09/15 - lrg 14235955: ignore ORA-65173 on revoke from
22:10:49 SQL> Rem cdb_keepsizes
22:10:49 SQL> Rem ssubrama 02/12/15 - bug 20494207 sharded q flag during upgrade
22:10:49 SQL> Rem maba 01/28/15 - fix bug 20184738
22:10:49 SQL> Rem cderosa 07/03/14 - Gather table stats on logminer dictionary tables
22:10:49 SQL> Rem to initialize incremental mode.
22:10:49 SQL> Rem wesmith 05/23/14 - Project 47511: data-bound collation: move fix
22:10:49 SQL> Rem for bug 17526621 from c1201000.sql
2:10:49 SQL> Rem surman 05/19/14 - 17277459: Remove call to catbundle
22:10:49 SQL> Rem jerrede 01/17/14 - Fix Bug 18071399 Add Post Upgrade Report Time
22:10:49 SQL> Rem surman 05/31/13 - 16790144: Use @@
22:10:49 SQL> Rem cmlim 05/15/13 - bug 16816410: add table name to errorlogging
22:10:49 SQL> Rem syntax
22:10:49 SQL> Rem surman 03/19/13 - 16094163: Add catbundleapply.sql
22:10:49 SQL> Rem cmlim 03/01/13 - bug 16306200: remove the workaround (added in
22:10:49 SQL> Rem txn in bug 16085743) that re-updated
22:10:49 SQL> Rem oracle-supplied bit in views owned by SYS after
22:10:49 SQL> Rem bootstrap. Workaround not needed once the shared
22:10:49 SQL> Rem pool is flushed in catuposb.sql (bug 16306200).
22:10:49 SQL> Rem jerrede 01/14/13 - XbranchMerge jerrede_bug-16097914 from
22:10:49 SQL> Rem st_rdbms_12.1.0.1
22:10:49 SQL> Rem jerrede 01/11/13 - Move Removal of EXF/RUL to upgrade.
22:10:49 SQL> Rem LogMiner/Standyby can not deal with removing
22:10:49 SQL> Rem a component outside of upgrade.
22:10:49 SQL> Rem sjanardh 01/10/13 - XbranchMerge maba_bug-14615619 from main
22:10:49 SQL> Rem jerrede 12/19/12 - Bug#16025279 Add Event for Not Removing EXF/RUL
22:10:49 SQL> Rem Upgrade Components
22:10:49 SQL> Rem surman 12/10/12 - XbranchMerge surman_bug-12876907 from main
22:10:49 SQL> Rem maba 11/26/12 - fixed bug 14615619
22:10:49 SQL> Rem jerrede 11/05/12 - Add Exadata Bundle support
22:10:49 SQL> Rem cmlim 10/27/12 - bug 14258301 : gather fixed obj stats if none of
22:10:49 SQL> Rem the fixed object tables have had stats collected
22:10:49 SQL> Rem mfallen 09/20/12 - bug 14390165: check if AWR data needs update
22:10:49 SQL> Rem jerrede 10/23/12 - Add Session Info
22:10:49 SQL> Rem maba 09/13/12 - added create dequeue log for bug 14278722
22:10:49 SQL> Rem jerrede 06/26/12 - Set event to optionally update required stats
22:10:49 SQL> Rem during upgrade
22:10:49 SQL> Rem rpang 05/21/12 - Add network ACL migration status check
22:10:49 SQL> Rem traney 05/09/12 - lrg 6949943: mask ORA-942s
22:10:49 SQL> Rem jerrede 04/17/12 - Moved Mandatory Changes to catrequired.sql
22:10:49 SQL> Rem traney 04/04/12 - lrg 6762280: drop DBMS_DDL_INTERNAL_LIB
22:10:49 SQL> Rem traney 03/12/12 - bug 13719175: move post-utlmmig stats here
22:10:49 SQL> Rem cdilling 12/13/11 - drop SYSMAN schema - removal of EM component for
22:10:49 SQL> Rem upgrade to 12.1
22:10:49 SQL> Rem aramappa 06/22/11 - Always run olstrig.sql when OLS installed in DB
22:10:49 SQL> Rem xbarr 04/28/11 - move DMSYS removal code to odmu112.sql
22:10:49 SQL> Rem xbarr 10/25/10 - run dmsysrem.sql to drop DMSYS schema if exists
22:10:49 SQL> Rem cdilling 07/21/10 - add call to catbundle.sql for bug 9925339
22:10:49 SQL> Rem srtata 12/16/08 - run olstrig.sql when upgrading from prior to 10.2
22:10:49 SQL> Rem srtata 10/15/08 - put back olstrig.sql as we found it cannot be run
22:10:49 SQL> Rem as part of upgrade
22:10:49 SQL> Rem srtata 02/26/08 - move olstrig.sql to olsdbmig.sql
22:10:49 SQL> Rem ushaft 02/05/07 - post upgrade for ADDM tasks.
22:10:49 SQL> Rem cdilling 12/06/06 - add support for error logging
22:10:49 SQL> Rem rburns 11/10/06 - post upgrade actions
22:10:49 SQL> Rem rburns 11/10/06 - Created
22:10:49 SQL> Rem
22:10:49 SQL>
22:10:49 SQL> Rem =====================================================================
22:10:49 SQL> Rem Call Common session settings
22:10:49 SQL> Rem =====================================================================
22:10:49 SQL> @@catpses.sql
22:10:49 SQL> Rem
22:10:49 SQL> Rem $Header: rdbms/admin/catpses.sql /main/4 2015/07/23 11:34:46 jerrede Exp $
22:10:49 SQL> Rem
22:10:49 SQL> Rem catpses.sql
22:10:49 SQL> Rem
22:10:49 SQL> Rem Copyright (c) 2006, 2015, Oracle and/or its affiliates.
22:10:49 SQL> Rem All rights reserved.
22:10:49 SQL> Rem
22:10:49 SQL> Rem NAME
22:10:49 SQL> Rem catpses.sql - CATalog and CATProc SESsion script
22:10:49 SQL> Rem
22:10:49 SQL> Rem DESCRIPTION
22:10:49 SQL> Rem This script initializes the session for running catalog
22:10:49 SQL> Rem and/or catproc scripts
22:10:49 SQL> Rem
22:10:49 SQL> Rem NOTES
22:10:49 SQL> Rem It is used as the session script for parallel processes
22:10:49 SQL> Rem when catalog.sql and/or catproc.sql is run using multiprocesses
22:10:49 SQL> Rem
22:10:49 SQL> Rem BEGIN SQL_FILE_METADATA
22:10:49 SQL> Rem SQL_SOURCE_FILE: rdbms/admin/catpses.sql
22:10:49 SQL> Rem SQL_SHIPPED_FILE: rdbms/admin/catpses.sql
22:10:49 SQL> Rem SQL_PHASE: CATPSES
22:10:49 SQL> Rem SQL_STARTUP_MODE: NORMAL
22:10:49 SQL> Rem SQL_IGNORABLE_ERRORS: NONE
2:10:49 SQL> Rem SQL_CALLING_FILE: rdbms/admin/cdstrt.sql
22:10:49 SQL> Rem END SQL_FILE_METADATA
22:10:49 SQL> Rem
22:10:49 SQL> Rem MODIFIED (MM/DD/YY)
22:10:49 SQL> Rem jerrede 06/03/15 - Remove Session End we do not want to set
22:10:49 SQL> Rem ORACLE_SCRIPT to false for session files in the
22:10:49 SQL> Rem upgrade
22:10:49 SQL> Rem surman 12/29/13 - 13922626: Update SQL metadata
22:10:49 SQL> Rem jerrede 05/08/12 - Added session info for CDB.
22:10:49 SQL> Rem rburns 10/23/06 - add session script
22:10:49 SQL> Rem rburns 10/23/06 - Created
22:10:49 SQL> Rem
22:10:49 SQL>
22:10:49 SQL> @@?/rdbms/admin/sqlsessstart.sql
22:10:49 SQL> Rem
22:10:49 SQL> Rem $Header: rdbms/admin/sqlsessstart.sql /main/1 2013/03/13 13:08:33 surman Exp $
22:10:49 SQL> Rem
22:10:49 SQL> Rem sqlsessstart.sql
22:10:49 SQL> Rem
22:10:49 SQL> Rem Copyright (c) 2013, Oracle and/or its affiliates. All rights reserved.
22:10:49 SQL> Rem
22:10:49 SQL> Rem NAME
22:10:49 SQL> Rem sqlsessstart.sql - SQL session start
22:10:49 SQL> Rem
22:10:49 SQL> Rem DESCRIPTION
22:10:49 SQL> Rem Any commands which should be run at the start of all oracle
22:10:49 SQL> Rem supplied scripts.
22:10:49 SQL> Rem
22:10:49 SQL> Rem NOTES
22:10:49 SQL> Rem See sqlsessend.sql for the corresponding end script.
22:10:49 SQL> Rem
22:10:49 SQL> Rem MODIFIED (MM/DD/YY)
22:10:49 SQL> Rem surman 03/08/13 - 16462837: Common start and end scripts
22:10:49 SQL> Rem surman 03/08/13 - Created
22:10:49 SQL> Rem
22:10:49 SQL>
Elapsed: 00:00:00.00
22:10:49 SQL>
22:10:49 SQL> Rem =====================================================================
22:10:49 SQL> Rem Assure CHAR semantics are not used in the dictionary
22:10:49 SQL> Rem =====================================================================
22:10:49 SQL> ALTER SESSION SET NLS_LENGTH_SEMANTICS=BYTE;
session altered.
Elapsed: 00:00:00.00
22:10:49 SQL>
22:10:49 SQL>
22:10:49 SQL>
22:10:49 SQL> Rem *********************************************************************
22:10:49 SQL> Rem BEGIN catuppst.sql
22:10:49 SQL> Rem *********************************************************************
22:10:49 SQL> Rem Set identifier to POSTUP for errorlogging
22:10:49 SQL>
22:10:49 SQL> SET ERRORLOGGING ON TABLE SYS.REGISTRY$ERROR IDENTIFIER 'POSTUP';
22:10:49 SQL>
22:10:49 SQL> -- DBUA_TIMESTAMP: db shutdown/startup is finished by now
22:10:49 SQL> SELECT dbms_registry_sys.time_stamp('DBRESTART') as timestamp from dual;
TIMESTAMP
-----------------------------------------------------------------------
COMP_TIMESTAMP DBRESTART 2019-10-20 22:10:49
DBUA_TIMESTAMP DBRESTART FINISHED 2019-10-20 22:10:49
DBUA_TIMESTAMP DBRESTART NONE 2019-10-20 22:10:49
1 row selected.
Elapsed: 00:00:00.03
22:10:49 SQL>
22:10:49 SQL> -- DBUA_TIMESTAMP: catuppst.sql begins
22:10:49 SQL> SELECT dbms_registry_sys.time_stamp_display('CATUPPST') AS timestamp FROM DUAL;
TIMESTAMP
-----------------------------------------------------------
DBUA_TIMESTAMP CATUPPST STARTED 2019-10-20 22:10:49
1 row selected.
Elapsed: 00:00:00.02
22:10:49 SQL>
22:10:49 SQL>
22:10:49 SQL> SELECT dbms_registry_sys.time_stamp('POSTUP_BGN') as timestamp from dual;
TIMESTAMP
--------------------------------------------------------------
COMP_TIMESTAMP POSTUP_BGN 2019-10-20 22:10:49
DBUA_TIMESTAMP POSTUP_BGN FINISHED 2019-10-20 22:10:49
DBUA_TIMESTAMP POSTUP_BGN NONE 2019-10-20 22:10:49
1 row selected.
Elapsed: 00:00:00.01
22:10:49 SQL>
22:10:49 SQL>
22:10:49 SQL> Rem =======================================================================
22:10:49 SQL> Rem Run Post Upgrade Operations
22:10:49 SQL> Rem =======================================================================
22:10:49 SQL>
22:10:49 SQL> @@catrequired.sql
22:10:49 SQL> Rem
22:10:49 SQL> Rem $Header: rdbms/admin/catrequired.sql /main/1 2012/07/19 11:27:56 jerrede Exp $
22:10:49 SQL> Rem
22:10:49 SQL> Rem catrequired.sql
22:10:49 SQL> Rem
22:10:49 SQL> Rem Copyright (c) 2006, 2012, Oracle and/or its affiliates.
22:10:49 SQL> Rem All rights reserved.
22:10:49 SQL> Rem
22:10:49 SQL> Rem NAME
22:10:49 SQL> Rem catrequired.sql - Catalog Mandatory Upgrade Script
22:10:49 SQL> Rem
22:10:49 SQL> Rem DESCRIPTION
22:10:49 SQL> Rem This catalog script is a place holder
22:10:49 SQL> Rem for other things that may be added in the future.
22:10:49 SQL> Rem Right now it only calls catrequtlmg.sql.
22:10:49 SQL> Rem
22:10:49 SQL> Rem NOTES
22:10:49 SQL> Rem You must be connected AS SYSDBA to run this script.
22:10:49 SQL> Rem
22:10:49 SQL> Rem MODIFIED (MM/DD/YY)
22:10:49 SQL> Rem jerrede 04/17/12 - Created
22:10:49 SQL> Rem
22:10:49 SQL>
22:10:49 SQL>
22:10:49 SQL> Rem *********************************************************************
22:10:49 SQL> Rem BEGIN catrequired.sql
22:10:49 SQL> Rem *********************************************************************
22:10:49 SQL>
22:10:49 SQL> Rem
22:10:49 SQL> Rem Display Start TimeStamp
22:10:49 SQL> Rem
22:10:49 SQL> SELECT sys.dbms_registry_sys.time_stamp('catreq_bgn') as timestamp from dual;
TIMESTAMP
------------------------------------------------------------
COMP_TIMESTAMP CATREQ_BGN 2019-10-20 22:10:49
DBUA_TIMESTAMP CATREQ_BGN FINISHED 2019-10-20 22:10:49
DBUA_TIMESTAMP CATREQ_BGN NONE 2019-10-20 22:10:49
1 row selected.
Elapsed: 00:00:00.01
22:10:49 SQL>
22:10:49 SQL>
22:10:49 SQL> Rem
22:10:49 SQL> Rem Post-utlmmig statistics gathering
22:10:49 SQL> Rem
22:10:49 SQL> @@catrequtlmg.sql
22:10:49 SQL> Rem
22:10:49 SQL> Rem $Header: rdbms/admin/catrequtlmg.sql /main/5 2016/05/08 17:26:15 hvieyra Exp $
22:10:49 SQL> Rem
22:10:49 SQL> Rem catrequtlmg.sql
22:10:49 SQL> Rem
22:10:49 SQL> Rem Copyright (c) 2006, 2016, Oracle and/or its affiliates.
22:10:49 SQL> Rem All rights reserved.
22:10:49 SQL> Rem
22:10:49 SQL> Rem NAME
22:10:49 SQL> Rem catrequtlmg.sql - Catalog Mandatory Upgrade Script
22:10:49 SQL> Rem
22:10:49 SQL> Rem DESCRIPTION
22:10:49 SQL> Rem This catalog script can run from utlmmig.sql or catuppst.sql.
22:10:49 SQL> Rem The event _utlmmig_table_stats_gathering determines where it
22:10:49 SQL> Rem is run. If TRUE (the default) it is run from utlmmig.sql, if
22:10:49 SQL> Rem FALSE it will be run from catuppst.sql. This script gathers
22:10:49 SQL> Rem statistics on migration stats that are recreated after an
22:10:49 SQL> Rem upgrade occurs.
22:10:49 SQL> Rem
22:10:49 SQL> Rem NOTES
22:10:49 SQL> Rem You must be connected AS SYSDBA to run this script.
22:10:49 SQL> Rem
22:10:49 SQL> Rem MODIFIED (MM/DD/YY)
22:10:49 SQL> Rem hvieyra 05/03/16 - Fix for bug 23223406. Remove estimate_percent
22:10:49 SQL> Rem clause.
22:10:49 SQL> Rem anighosh 09/03/15 - #(21774511): create cluster index name
22:10:49 SQL> Rem based on whether operating under utlmmig
22:10:49 SQL> Rem or not.
22:10:49 SQL> Rem anighosh 08/16/15 - #(21377496): Gather cluster index stats
22:10:49 SQL> Rem jerrede 12/20/12 - Turn off set serveroutput
22:10:49 SQL> Rem jerrede 04/17/12 - Moved from catuppst.sql
22:10:49 SQL> Rem which was written by Tom Raney.
22:10:49 SQL> Rem
22:10:49 SQL>
22:10:49 SQL>
22:10:49 SQL> Rem *********************************************************************
22:10:49 SQL> Rem BEGIN catrequtlmg.sql
22:10:49 SQL> Rem *********************************************************************
22:10:49 SQL>
22:10:49 SQL> Rem =======================================================================
22:10:49 SQL> Rem Statistics gathering
22:10:49 SQL> Rem =======================================================================
22:10:49 SQL> -- DBMS_STATS now depends on DBMS_UTILITY which may have gotten invalidated
22:10:49 SQL> -- by some preceeding DDL statement, so package state needs to be cleared to
22:10:49 SQL> -- avoid ORA-04068, reset_package causes set serveroutput on to not work.
22:10:49 SQL>
22:10:49 SQL> execute dbms_session.reset_package;
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
22:10:49 SQL> set serveroutput on;
22:10:49 SQL>
22:10:49 SQL> declare
22:10:49 2
22:10:49 3
22:10:49 4 c_TRACEEVENT CONSTANT VARCHAR2(30) := '_utlmmig_table_stats_gathering';
22:10:49 5 c_POSTUPGRADE CONSTANT VARCHAR2(19) := 'CATREQ_POST_UPGRADE';
22:10:49 6 c_BOOTERR CONSTANT VARCHAR2(23) := 'BOOTSTRAP_UPGRADE_ERROR';
22:10:49 7 c_MIGTABLE CONSTANT VARCHAR2(4) := '$MIG';
22:10:49 8 c_POSTUPGTABLE CONSTANT VARCHAR2(1) := '$';
22:10:49 9 s_TableName VARCHAR2(4) := c_MIGTABLE;
22:10:49 10 s_IndexName VARCHAR2(3) := 'MIG';
22:10:49 11 b_InUtlMig BOOLEAN := sys.dbms_registry_sys.select_props_data(c_BOOTERR);
22:10:49 12 b_UpgradeMode BOOLEAN := sys.dbms_registry.is_in_upgrade_mode();
22:10:49 13 b_StatEvt BOOLEAN := sys.dbms_registry.is_trace_event_set(c_TRACEEVENT);
22:10:49 14 b_SelProps BOOLEAN := sys.dbms_registry_sys.select_props_data(c_POSTUPGRADE);
22:10:49 15 b_Props BOOLEAN := TRUE;
22:10:49 16
22:10:49 17 begin
22:10:49 18
22:10:49 19 --
22:10:49 20 -- Debug Info
22:10:49 21 --
22:10:49 22 IF (b_StatEvt) THEN
22:10:49 23 sys.dbms_output.put_line('catrequtlmg: b_StatEvt = TRUE');
22:10:49 24 ELSE
22:10:49 25 sys.dbms_output.put_line('catrequtlmg: b_StatEvt = FALSE');
22:10:49 26 END IF;
22:10:49 27
22:10:49 28 IF (b_SelProps) THEN
22:10:49 29 sys.dbms_output.put_line('catrequtlmg: b_SelProps = TRUE');
22:10:49 30 ELSE
22:10:49 31 sys.dbms_output.put_line('catrequtlmg: b_SelProps = FALSE');
22:10:49 32 END IF;
22:10:49 33
22:10:49 34 IF (b_UpgradeMode) THEN
22:10:49 35 sys.dbms_output.put_line('catrequtlmg: b_UpgradeMode = TRUE');
22:10:49 36 ELSE
22:10:49 37 sys.dbms_output.put_line('catrequtlmg: b_UpgradeMode = FALSE');
22:10:49 38 END IF;
22:10:49 39
22:10:49 40 IF (b_InUtlMig) THEN
22:10:49 41 sys.dbms_output.put_line('catrequtlmg: b_InUtlMig = TRUE');
22:10:49 42 ELSE
22:10:49 43 sys.dbms_output.put_line('catrequtlmg: b_InUtlMig = FALSE');
22:10:49 44 s_TableName := c_POSTUPGTABLE;
22:10:49 45 s_IndexName := '';
22:10:49 46 END IF;
22:10:49 47
22:10:49 48 --
22:10:49 49 -- b_StatEvt = FALSE indicates don't collect stats
22:10:49 50 -- in upgrade mode.
22:10:49 51 --
22:10:49 52 -- Don't do the migration stats in UPGRADE mode.
22:10:49 53 -- Stats will run no matter what mode we are in
22:10:49 54 -- if post upgrade data is found in sys.props$.
22:10:49 55 --
22:10:49 56 IF (b_StatEvt = FALSE AND b_SelProps = FALSE) THEN
22:10:49 57
22:10:49 58 --
22:10:49 59 -- In Upgrade Mode Only
22:10:49 60 --
22:10:49 61 IF (b_UpgradeMode) THEN
22:10:49 62
22:10:49 63 --
22:10:49 64 -- Set sys.props$ table indicating that it
22:10:49 65 -- needs to be run in the post upgrade script.
22:10:49 66 --
22:10:49 67 b_Props := sys.dbms_registry_sys.insert_props_data(c_POSTUPGRADE,
22:10:49 68 'Run Migration Stats',
22:10:49 69 'Startup database in normal mode and run catuppst.sql');
22:10:49 70 IF (b_Props) THEN
22:10:49 71 sys.dbms_output.put_line('catrequtlmg: insert_props_data: Success');
22:10:49 72 ELSE
22:10:49 73 sys.dbms_output.put_line('catrequtlmg: insert_props_data: Failure');
22:10:49 74 END IF;
22:10:49 75
22:10:49 76 END IF;
22:10:49 77
22:10:49 78 RETURN;
22:10:49 79
22:10:49 80 END IF;
22:10:49 81
22:10:49 82 --
22:10:49 83 -- b_StatEvt = TRUE indicates collect stats
22:10:49 84 -- in upgrade mode.
22:10:49 85 --
22:10:49 86 -- Don't do the migration stats in NORMAL mode.
22:10:49 87 -- Stats will run no matter what mode we are in
22:10:49 88 -- if post upgrade data is found in sys.props$.
22:10:49 89 --
22:10:49 90 IF (b_StatEvt = TRUE AND b_SelProps = FALSE AND b_UpgradeMode = FALSE) THEN
22:10:49 91
22:10:49 92 RETURN;
22:10:49 93
22:10:49 94 END IF;
22:10:49 95
22:10:49 96 --
22:10:49 97 -- Updating migration stats in post upgrade. Write an entry to
22:10:49 98 -- sys.props$ table to indicate that stat collection has started.
22:10:49 99 -- If this entry is present then this routine has failed.
22:10:49 100 --
22:10:49 101 IF (b_SelProps) THEN
22:10:49 102
22:10:49 103 b_Props := sys.dbms_registry_sys.update_props_data(c_POSTUPGRADE,
22:10:49 104 'Started Migration Stats');
22:10:49 105 IF (b_Props) THEN
22:10:49 106 sys.dbms_output.put_line('catrequtlmg: update_props_data: Success');
22:10:49 107 ELSE
22:10:49 108 sys.dbms_output.put_line('catrequtlmg: update_props_data: Failure');
22:10:49 109 END IF;
22:10:49 110
22:10:49 111 END IF;
22:10:49 112
22:10:49 113
22:10:49 114 --
22:10:49 115 -- Delete Stats
22:10:49 116 --
22:10:49 117 sys.dbms_output.put_line('catrequtlmg: Deleting table stats');
22:10:49 118 sys.dbms_stats.delete_table_stats('SYS', 'OBJ' || s_TableName);
22:10:49 119 sys.dbms_stats.delete_table_stats('SYS', 'USER' || s_TableName);
22:10:49 120 sys.dbms_stats.delete_table_stats('SYS', 'COL' || s_TableName);
22:10:49 121 sys.dbms_stats.delete_table_stats('SYS', 'CLU' || s_TableName);
22:10:49 122 sys.dbms_stats.delete_table_stats('SYS', 'CON' || s_TableName);
22:10:49 123 sys.dbms_stats.delete_table_stats('SYS', 'TAB' || s_TableName);
22:10:49 124 sys.dbms_stats.delete_table_stats('SYS', 'IND' || s_TableName);
22:10:49 125 sys.dbms_stats.delete_table_stats('SYS', 'ICOL' || s_TableName);
22:10:49 126 sys.dbms_stats.delete_table_stats('SYS', 'LOB' || s_TableName);
22:10:49 127 sys.dbms_stats.delete_table_stats('SYS', 'COLTYPE' || s_TableName);
22:10:49 128 sys.dbms_stats.delete_table_stats('SYS', 'SUBCOLTYPE' || s_TableName);
22:10:49 129 sys.dbms_stats.delete_table_stats('SYS', 'NTAB' || s_TableName);
22:10:49 130 sys.dbms_stats.delete_table_stats('SYS', 'REFCON' || s_TableName);
22:10:49 131 sys.dbms_stats.delete_table_stats('SYS', 'OPQTYPE' || s_TableName);
22:10:49 132 sys.dbms_stats.delete_table_stats('SYS', 'ICOLDEP' || s_TableName);
22:10:49 133 sys.dbms_stats.delete_table_stats('SYS', 'TSQ' || s_TableName);
22:10:49 134 sys.dbms_stats.delete_table_stats('SYS', 'VIEWTRCOL' || s_TableName);
22:10:49 135 sys.dbms_stats.delete_table_stats('SYS', 'ATTRCOL' || s_TableName);
22:10:49 136 sys.dbms_stats.delete_table_stats('SYS', 'TYPE_MISC' || s_TableName);
22:10:49 137 sys.dbms_stats.delete_table_stats('SYS', 'LIBRARY' || s_TableName);
22:10:49 138 sys.dbms_stats.delete_table_stats('SYS', 'ASSEMBLY' || s_TableName);
22:10:49 139
22:10:49 140 --
22:10:49 141 -- Gather Stats
22:10:49 142 --
22:10:49 143 sys.dbms_output.put_line('catrequtlmg: Gathering Table Stats OBJ' ||
22:10:49 144 s_TableName);
22:10:49 145 sys.dbms_stats.gather_table_stats('SYS', 'OBJ' || s_TableName,
22:10:49 146 method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
22:10:49 147 sys.dbms_output.put_line('catrequtlmg: Gathering Table Stats USER' ||
22:10:49 148 s_TableName);
22:10:49 149 sys.dbms_stats.gather_table_stats('SYS', 'USER' || s_TableName,
22:10:49 150 method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
22:10:49 151 sys.dbms_output.put_line('catrequtlmg: Gathering Table Stats COL' ||
22:10:49 152 s_TableName);
22:10:49 153 sys.dbms_stats.gather_table_stats('SYS', 'COL' || s_TableName,
22:10:49 154 method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
22:10:49 155 sys.dbms_output.put_line('catrequtlmg: Gathering Table Stats CLU' ||
22:10:49 156 s_TableName);
22:10:49 157 sys.dbms_stats.gather_table_stats('SYS', 'CLU' || s_TableName,
22:10:49 158 method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
22:10:49 159 sys.dbms_output.put_line('catrequtlmg: Gathering Table Stats CON' ||
22:10:49 160 s_TableName);
22:10:49 161 sys.dbms_stats.gather_table_stats('SYS', 'CON' || s_TableName,
22:10:49 162 method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
22:10:49 163 sys.dbms_output.put_line('catrequtlmg: Gathering Table Stats TAB' ||
22:10:49 164 s_TableName);
22:10:49 165 sys.dbms_stats.gather_table_stats('SYS', 'TAB' || s_TableName,
22:10:49 166 method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
22:10:49 167 sys.dbms_output.put_line('catrequtlmg: Gathering Table Stats IND' ||
22:10:49 168 s_TableName);
22:10:49 169 sys.dbms_stats.gather_table_stats('SYS', 'IND' || s_TableName,
22:10:49 170 method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
22:10:49 171 sys.dbms_output.put_line('catrequtlmg: Gathering Table Stats ICOL' ||
22:10:49 172 s_TableName);
22:10:49 173 sys.dbms_stats.gather_table_stats('SYS', 'ICOL' || s_TableName,
22:10:49 174 method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
22:10:49 175 sys.dbms_output.put_line('catrequtlmg: Gathering Table Stats LOB' ||
22:10:49 176 s_TableName);
22:10:49 177 sys.dbms_stats.gather_table_stats('SYS', 'LOB' || s_TableName,
22:10:49 178 method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
22:10:49 179 sys.dbms_output.put_line('catrequtlmg: Gathering Table Stats COLTYPE' ||
22:10:49 180 s_TableName);
22:10:49 181 sys.dbms_stats.gather_table_stats('SYS', 'COLTYPE' || s_TableName,
22:10:49 182 method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
22:10:49 183 sys.dbms_output.put_line('catrequtlmg: Gathering Table Stats SUBCOLTYPE' ||
22:10:49 184 s_TableName);
22:10:49 185 sys.dbms_stats.gather_table_stats('SYS', 'SUBCOLTYPE' || s_TableName,
22:10:49 186 method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
22:10:49 187 sys.dbms_output.put_line('catrequtlmg: Gathering Table Stats NTAB' ||
22:10:49 188 s_TableName);
22:10:49 189 sys.dbms_stats.gather_table_stats('SYS', 'NTAB' || s_TableName,
22:10:49 190 method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
22:10:49 191 sys.dbms_output.put_line('catrequtlmg: Gathering Table Stats REFCON' ||
22:10:49 192 s_TableName);
22:10:49 193 sys.dbms_stats.gather_table_stats('SYS', 'REFCON' || s_TableName,
22:10:49 194 method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
22:10:49 195 sys.dbms_output.put_line('catrequtlmg: Gathering Table Stats OPQTYPE' ||
22:10:49 196 s_TableName);
22:10:49 197 sys.dbms_stats.gather_table_stats('SYS', 'OPQTYPE' || s_TableName,
22:10:49 198 method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
22:10:49 199 sys.dbms_output.put_line('catrequtlmg: Gathering Table Stats ICOLDEP' ||
22:10:49 200 s_TableName);
22:10:49 201 sys.dbms_stats.gather_table_stats('SYS', 'ICOLDEP' || s_TableName,
22:10:49 202 method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
22:10:49 203 sys.dbms_output.put_line('catrequtlmg: Gathering Table Stats TSQ' ||
22:10:49 204 s_TableName);
22:10:49 205 sys.dbms_stats.gather_table_stats('SYS', 'TSQ' || s_TableName,
22:10:49 206 method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
22:10:49 207 sys.dbms_output.put_line('catrequtlmg: Gathering Table Stats VIEWTRCOL' ||
22:10:49 208 s_TableName);
22:10:49 209 sys.dbms_stats.gather_table_stats('SYS', 'VIEWTRCOL' || s_TableName,
22:10:49 210 method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
22:10:49 211 sys.dbms_output.put_line('catrequtlmg: Gathering Table Stats ATTRCOL' ||
22:10:49 212 s_TableName);
22:10:49 213 sys.dbms_stats.gather_table_stats('SYS', 'ATTRCOL' || s_TableName,
22:10:49 214 method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
22:10:49 215 sys.dbms_output.put_line('catrequtlmg: Gathering Table Stats TYPE_MISC' ||
22:10:49 216 s_TableName);
22:10:49 217 sys.dbms_stats.gather_table_stats('SYS', 'TYPE_MISC' || s_TableName,
22:10:49 218 method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
22:10:49 219 sys.dbms_output.put_line('catrequtlmg: Gathering Table Stats LIBRARY' ||
22:10:49 220 s_TableName);
22:10:49 221 sys.dbms_stats.gather_table_stats('SYS', 'LIBRARY' || s_TableName,
22:10:49 222 method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
22:10:49 223 sys.dbms_output.put_line('catrequtlmg: Gathering Table Stats ASSEMBLY' ||
22:10:49 224 s_TableName);
22:10:49 225 sys.dbms_stats.gather_table_stats('SYS', 'ASSEMBLY' || s_TableName,
22:10:49 226 method_opt=>'FOR ALL COLUMNS SIZE SKEWONLY');
22:10:49 227
22:10:49 228
22:10:49 229 -- [21377496]: Gather_Table_Stats does not collect stats for cluster index.
22:10:49 230 -- Cluster index is not associated with any table, but only with a cluster.
22:10:49 231 -- Thus we need to explicitly collected stats for this index.
22:10:49 232 --
22:10:49 233 -- [21774511]: Note that utlmmig may not be invoked for patch upgrades.
22:10:49 234 -- Given that, create the index name appropriately depending on whether
22:10:49 235 -- we are inside utlmmig or not.
22:10:49 236
22:10:49 237 -- Delete Cluster Index Stats
22:10:49 238
22:10:49 239 sys.dbms_output.put_line('catrequtlmg: Deleting cluster index stats');
22:10:49 240 sys.dbms_stats.delete_index_stats('SYS', 'I_USER#' || s_IndexName);
22:10:49 241 sys.dbms_stats.delete_index_stats('SYS', 'I_OBJ#' || s_IndexName);
22:10:49 242
22:10:49 243 -- Gather Cluster Index Stats
22:10:49 244
22:10:49 245 sys.dbms_output.put_line('catrequtlmg: Gathering Index Stats I_USER#' ||
22:10:49 246 s_IndexName);
22:10:49 247 sys.dbms_stats.gather_index_stats('SYS', 'I_USER#' || s_IndexName);
22:10:49 248
22:10:49 249 sys.dbms_output.put_line('catrequtlmg: Gathering Index Stats I_OBJ#'||
22:10:49 250 s_IndexName);
22:10:49 251 sys.dbms_stats.gather_index_stats('SYS', 'I_OBJ#' || s_IndexName);
22:10:49 252
22:10:49 253 --
22:10:49 254 -- Delete any previous entry that may have been stored in
22:10:49 255 -- sys.props$ table.
22:10:49 256 --
22:10:49 257 b_Props := sys.dbms_registry_sys.delete_props_data(c_POSTUPGRADE);
22:10:49 258 IF (b_Props) THEN
22:10:49 259 sys.dbms_output.put_line('catrequtlmg: delete_props_data: Success');
22:10:49 260 ELSE
22:10:49 261 sys.dbms_output.put_line('catrequtlmg: delete_props_data: No Props Data');
22:10:49 262 END IF;
22:10:49 263
22:10:49 264 end;
22:10:49 265 /
catrequtlmg: b_StatEvt = TRUE
catrequtlmg: b_SelProps = FALSE
catrequtlmg: b_UpgradeMode = FALSE
catrequtlmg: b_InUtlMig = FALSE
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.14
22:10:49 SQL>
22:10:49 SQL> --
22:10:49 SQL> -- Set serveroutput off
22:10:49 SQL> --
22:10:49 SQL> set serveroutput off;
22:10:49 SQL>
22:10:49 SQL> --
22:10:49 SQL> -- Reset Package to be on the safe side for the
22:10:49 SQL> -- case where we are running in catuppst.sql
22:10:49 SQL> --
22:10:49 SQL> execute dbms_session.reset_package;
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
22:10:49 SQL>
22:10:49 SQL>
22:10:49 SQL> Rem *********************************************************************
22:10:49 SQL> Rem END catrequtlmg.sql
22:10:49 SQL> Rem *********************************************************************
22:10:49 SQL>
22:10:49 SQL>
22:10:49 SQL>
22:10:49 SQL> Rem
22:10:49 SQL> Rem Display End TimeStamp
22:10:49 SQL> Rem
22:10:49 SQL> SELECT sys.dbms_registry_sys.time_stamp('catreq_end') as timestamp from dual;
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP CATREQ_END 2019-10-20 22:10:49
DBUA_TIMESTAMP CATREQ_END FINISHED 2019-10-20 22:10:49
DBUA_TIMESTAMP CATREQ_END NONE 2019-10-20 22:10:49
1 row selected.
Elapsed: 00:00:00.01
22:10:49 SQL>
22:10:49 SQL> Rem *********************************************************************
22:10:49 SQL> Rem END catrequired.sql
22:10:49 SQL> Rem *********************************************************************
22:10:49 SQL>
22:10:49 SQL>
22:10:49 SQL> --
22:10:49 SQL> -- These were created in utlmmig.sql but could not be dropped until now.
22:10:49 SQL> -- Suppress "does not exist" errors.
22:10:49 SQL> --
22:10:49 SQL> set serveroutput on;
22:10:49 SQL> begin
22:10:49 2 sys.dbms_output.put_line('catuppst: Dropping library DBMS_DDL_INTERNAL_LIB');
22:10:49 3 execute immediate 'drop library DBMS_DDL_INTERNAL_LIB';
22:10:49 4 exception
22:10:49 5 when others then
22:10:49 6 if sqlcode = -4043 then
22:10:49 7 null;
2:10:49 8 end if;
22:10:49 9 end;
22:10:49 10 /
catuppst: Dropping library DBMS_DDL_INTERNAL_LIB
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.02
22:10:49 SQL>
22:10:49 SQL> begin
22:10:49 2 sys.dbms_output.put_line('catuppst: Dropping view _CURRENT_EDITION_OBJ_MIG');
22:10:49 3 execute immediate 'drop view "_CURRENT_EDITION_OBJ_MIG"';
22:10:49 4 exception
22:10:49 5 when others then
22:10:49 6 if sqlcode = -942 then
22:10:49 7 null;
22:10:49 8 end if;
22:10:49 9 end;
22:10:49 10 /
catuppst: Dropping view _CURRENT_EDITION_OBJ_MIG
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
22:10:49 SQL>
22:10:49 SQL> begin
22:10:49 2 sys.dbms_output.put_line('catuppst: Dropping view _ACTUAL_EDITION_OBJ_MIG');
22:10:49 3 execute immediate 'drop view "_ACTUAL_EDITION_OBJ_MIG"';
22:10:49 4 exception
2:10:49 5 when others then
22:10:49 6 if sqlcode = -942 then
22:10:49 7 null;
22:10:49 8 end if;
22:10:49 9 end;
22:10:49 10 /
catuppst: Dropping view _ACTUAL_EDITION_OBJ_MIG
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
22:10:49 SQL>
22:10:49 SQL> Rem *************************************************************************
22:10:49 SQL> Rem Bug 17526621 revoke select_catalog_role
22:10:49 SQL> Rem *************************************************************************
22:10:49 > begin
22:10:49 2 execute immediate 'revoke select on cdb_keepsizes from select_catalog_role';
22:10:49 3 exception when others then
22:10:49 4 if sqlcode in (-1927, -942, -65173) then null;
22:10:49 5 else raise;
22:10:49 6 end if;
22:10:49 7 end;
22:10:49 8 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.02
22:10:49 SQL>
22:10:49 SQL>
22:10:49 SQL> set serveroutput off
22:10:49 SQL>
22:10:49 SQL>
22:10:49 SQL>
22:10:49 SQL> Rem =======================================================================
22:10:49 SQL> Rem Bug 14258301 - Gather fixed objects stats IF NONE of the fixed object
22:10:49 SQL> Rem tables has stats
22:10:49 SQL> Rem =======================================================================
22:10:49 SQL>
22:10:49 SQL> set serveroutput on
22:10:49 SQL> declare
22:10:49 2 has_stats_cnt number := 0; -- # of fixed object tables that have stats
22:10:49 3 begin
22:10:49 4 -- find # of fixed object tables that have had stats collected
22:10:49 5 execute immediate
22:10:49 6 'select count(*) ' ||
22:10:49 7 'from sys.dba_tab_statistics ' ||
22:10:49 8 ' where owner = ''SYS'' and table_name like ''X$%'' ' ||
22:10:49 9 ' and last_analyzed is not null'
22:10:49 10 into has_stats_cnt;
22:10:49 11
22:10:49 12 -- if none of the fixed obj tables have had stats collected
22:10:49 13 -- then gather fixed objects stats
22:10:49 14 -- else do nothing
22:10:49 15 if (has_stats_cnt = 0) then
22:10:49 16 sys.dbms_output.put_line('catuppst: Gathering fixed objects stats now...');
22:10:49 17 sys.dbms_stats.gather_fixed_objects_stats;
22:10:49 18 sys.dbms_output.put_line('catuppst: Gathering fixed objects stats done.');
22:10:49 19 end if;
22:10:49 20 end;
22:10:49 21 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.24
22:10:49 SQL> set serveroutput off
22:10:50 SQL>
22:10:50 SQL> Rem =======================================================================
22:10:50 SQL> Rem Gather Fixed Objects Stats end
22:10:50 SQL> Rem =======================================================================
22:10:50 SQL>
22:10:50 SQL> Rem =======================================================================
22:10:50 SQL> Rem Gather stats on Logminer Dictionary tables to initialize incremental
22:10:50 SQL> Rem stats mode
22:10:50 SQL> Rem =======================================================================
22:10:50 SQL>
22:10:50 SQL> @@execlmnrstats.sql
22:10:50 SQL> Rem
22:10:50 SQL> Rem $Header: rdbms/admin/execlmnrstats.sql /main/1 2014/12/17 08:57:27 cderosa Exp $
22:10:50 SQL> Rem
22:10:50 SQL> Rem execlmnrstats.sql
22:10:50 SQL> Rem
22:10:50 SQL> Rem Copyright (c) 2014, Oracle and/or its affiliates. All rights reserved.
22:10:50 SQL> Rem
22:10:50 SQL> Rem NAME
22:10:50 SQL> Rem execlmnrstats.sql - Gather stats on Logminer dictionary tables.
22:10:50 SQL> Rem
22:10:50 SQL> Rem DESCRIPTION
22:10:50 SQL> Rem Gather stats on Logminer dictionary tables. This is the first
22:10:50 SQL> Rem time stats are called after incremental prefs are set, so this
22:10:50 SQL> Rem will set up the incremental infrastructure.
22:10:50 SQL> Rem
22:10:50 SQL> Rem NOTES
22:10:50 SQL> Rem This is called during db creation and during upgrade across 12.1
22:10:50 SQL> Rem
22:10:50 SQL> Rem BEGIN SQL_FILE_METADATA
22:10:50 SQL> Rem SQL_SOURCE_FILE: rdbms/admin/execlmnrstats.sql
22:10:50 SQL> Rem SQL_SHIPPED_FILE: rdbms/admin/execlmnrstats.sql
22:10:50 SQL> Rem SQL_PHASE: EXECLMNRSTATS
22:10:50 SQL> Rem SQL_STARTUP_MODE: NORMAL
22:10:50 SQL> Rem SQL_IGNORABLE_ERRORS: NONE
22:10:50 SQL> Rem SQL_CALLING_FILE: rdbms/admin/execlmnr.sql
22:10:50 SQL> Rem END SQL_FILE_METADATA
22:10:50 SQL> Rem
22:10:50 SQL> Rem MODIFIED (MM/DD/YY)
22:10:50 SQL> Rem cderosa 07/03/14 - Initial statistics gathering after incremental
22:10:50 SQL> Rem table prefs are set.
22:10:50 SQL> Rem cderosa 07/03/14 - Created
22:10:50 SQL> Rem
22:10:50 SQL>
22:10:50 SQL> @@?/rdbms/admin/sqlsessstart.sql
22:10:50 SQL> Rem
22:10:50 SQL> Rem $Header: rdbms/admin/sqlsessstart.sql /main/1 2013/03/13 13:08:33 surman Exp $
22:10:50 SQL> Rem
22:10:50 SQL> Rem sqlsessstart.sql
22:10:50 SQL> Rem
22:10:50 SQL> Rem Copyright (c) 2013, Oracle and/or its affiliates. All rights reserved.
22:10:50 SQL> Rem
22:10:50 SQL> Rem NAME
22:10:50 SQL> Rem sqlsessstart.sql - SQL session start
22:10:50 SQL> Rem
22:10:50 SQL> Rem DESCRIPTION
22:10:50 SQL> Rem Any commands which should be run at the start of all oracle
22:10:50 SQL> Rem supplied scripts.
22:10:50 SQL> Rem
22:10:50 SQL> Rem NOTES
22:10:50 SQL> Rem See sqlsessend.sql for the corresponding end script.
22:10:50 SQL> Rem
22:10:50 SQL> Rem MODIFIED (MM/DD/YY)
22:10:50 SQL> Rem surman 03/08/13 - 16462837: Common start and end scripts
22:10:50 SQL> Rem surman 03/08/13 - Created
22:10:50 SQL> Rem
22:10:50 SQL>
Elapsed: 00:00:00.00
22:10:50 SQL> DECLARE
22:10:50 2 cursor table_name_cursor is
22:10:50 3 select x.name table_name
22:10:50 4 from sys.x$krvxdta x
22:10:50 5 where bitand(x.flags, 12) != 0;
22:10:50 6 filter_lst DBMS_STATS.OBJECTTAB := DBMS_STATS.OBJECTTAB();
22:10:50 7 obj_lst DBMS_STATS.OBJECTTAB := DBMS_STATS.OBJECTTAB();
22:10:50 8 ind number := 1;
22:10:50 9 BEGIN
22:10:50 10 for rec in table_name_cursor loop
22:10:50 11 begin
22:10:50 12 filter_lst.extend(1);
22:10:50 13 filter_lst(ind).ownname := 'SYSTEM';
22:10:50 14 filter_lst(ind).objname := 'LOGMNR_'|| rec.table_name||'';
22:10:50 15 ind := ind + 1;
22:10:50 16 end;
22:10:50 17 end loop;
22:10:50 18 DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME=>'SYSTEM', objlist=>obj_lst, obj_filter_list=>filter_lst);
22:10:50 19 END;
22:10:50 20 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:03.37
22:10:53 SQL> @?/rdbms/admin/sqlsessend.sql
22:10:53 SQL> Rem
22:10:53 SQL> Rem $Header: rdbms/admin/sqlsessend.sql /main/1 2013/03/13 13:08:33 surman Exp $
22:10:53 SQL> Rem
22:10:53 SQL> Rem sqlsessend.sql
22:10:53 SQL> Rem
22:10:53 SQL> Rem Copyright (c) 2013, Oracle and/or its affiliates. All rights reserved.
22:10:53 SQL> Rem
22:10:53 SQL> Rem NAME
22:10:53 SQL> Rem sqlsessend.sql - SQL session end
22:10:53 SQL> Rem
22:10:53 SQL> Rem DESCRIPTION
22:10:53 SQL> Rem Any commands which should be run at the end of all oracle
22:10:53 SQL> Rem supplied scripts.
22:10:53 SQL> Rem
22:10:53 SQL> Rem NOTES
22:10:53 SQL> Rem See sqlsessstart.sql for the corresponding start script.
22:10:53 SQL> Rem
22:10:53 SQL> Rem MODIFIED (MM/DD/YY)
22:10:53 SQL> Rem surman 03/08/13 - 16462837: Common start and end scripts
22:10:53 SQL> Rem surman 03/08/13 - Created
22:10:53 SQL> Rem
22:10:53 SQL>
Elapsed: 00:00:00.00
22:10:53 SQL>
22:10:53 SQL>
22:10:53 SQL>
22:10:53 SQL> Rem =======================================================================
22:10:53 SQL> Rem Logminer End
22:10:53 SQL> Rem =======================================================================
22:10:53 SQL>
22:10:53 SQL> Rem =======================================================================
22:10:53 SQL> Rem Upgrade types in Oracle-Maintained tables if any have not already
22:10:53 SQL> Rem been upgraded to the latest versions of evolved types.
22:10:53 SQL> Rem =======================================================================
22:10:53 SQL>
22:10:53 SQL> @@catuptabdata.sql
22:10:53 SQL> Rem
22:10:53 SQL> Rem $Header: rdbms/admin/catuptabdata.sql /main/2 2016/01/09 07:48:40 raeburns Exp $
22:10:53 SQL> Rem
22:10:53 SQL> Rem catuptabdata.sql
22:10:53 SQL> Rem
2:10:53 SQL> Rem Copyright (c) 2015, Oracle and/or its affiliates. All rights reserved.
2:10:53 SQL> Rem
2:10:53 SQL> Rem NAME
2:10:53 SQL> Rem catuptabdata.sql - CATalog UPgrade oracle-maintained TABle DATA
2:10:53 SQL> Rem
2:10:53 SQL> Rem DESCRIPTION
22:10:53 SQL> Rem This script runs ALTER TABLE UPGRADE statements for any
22:10:53 SQL> Rem Oracle-Maintained tables that are flagged as having type data
22:10:53 SQL> Rem that needs to be upgraded. The utluptabdata.sql script performs
22:10:53 SQL> Rem ALTER TABLE UPGRADE statements for customer tables that
22:10:53 SQL> Rem depend on Oracle-Maintained types and need to be upgraded.
22:10:53 SQL> Rem
22:10:53 SQL> Rem NOTES
22:10:53 SQL> Rem This script must be run connected AS SYSDBA.
22:10:53 SQL> Rem
22:10:53 SQL> Rem BEGIN SQL_FILE_METADATA
22:10:53 SQL> Rem SQL_SOURCE_FILE: rdbms/admin/catuptabdata.sql
22:10:53 SQL> Rem SQL_SHIPPED_FILE: rdbms/admin/catuptabdata.sql
22:10:53 SQL> Rem SQL_PHASE: CATUPTABDATA
22:10:53 SQL> Rem SQL_STARTUP_MODE: NORMAL
22:10:53 SQL> Rem SQL_IGNORABLE_ERRORS: NONE
22:10:53 SQL> Rem SQL_CALLING_FILE: rdbms/admin/catuppst.sql
22:10:53 SQL> Rem END SQL_FILE_METADATA
22:10:53 SQL> Rem
22:10:53 SQL> Rem MODIFIED (MM/DD/YY)
22:10:53 SQL> Rem raeburns 12/09/15 - Bug 22175911: remove serveroutput off,
22:10:53 SQL> Rem improve error message
22:10:53 SQL> Rem raeburns 08/24/15 - script to upgrade types in
22:10:53 SQL> Rem Oracle-Maintained tables
22:10:53 SQL> Rem raeburns 08/24/15 - Created
22:10:53 SQL> Rem
22:10:53 SQL>
22:10:53 SQL> Rem ====================================================================
22:10:53 SQL> Rem BEGIN catuptabdata.sql
22:10:53 SQL> Rem ====================================================================
22:10:53 SQL>
22:10:53 SQL> set serveroutput on
22:10:53 SQL>
22:10:53 SQL> DECLARE
22:10:53 2 CURSOR tabs IS
22:10:53 3 SELECT DISTINCT u.name owner, o.name name
22:10:53 4 FROM sys.obj$ o, sys.user$ u, sys.col$ c, sys.coltype$ t
22:10:53 5 WHERE bitand(t.flags,256) = 256 AND -- NOT upgraded
22:10:53 6 t.intcol# = c.intcol# AND
22:10:53 7 t.col# = c.col# AND
22:10:53 8 t.obj# = c.obj# AND
22:10:53 9 c.obj# = o.obj# AND
22:10:53 10 o.owner# = u.user# AND
22:10:53 11 o.owner# IN -- Oracle-supplied user
22:10:53 12 (SELECT user# FROM sys.user$
22:10:53 13 WHERE type#=1 and bitand(spare1, 256)= 256);
22:10:53 14 BEGIN
22:10:53 15 FOR tab IN tabs LOOP
22:10:53 16 BEGIN
22:10:53 17 EXECUTE IMMEDIATE 'ALTER TABLE ' ||
22:10:53 18 dbms_assert.enquote_name(tab.owner)||
22:10:53 19 '.' || dbms_assert.enquote_name(tab.name) ||
22:10:53 20 ' UPGRADE INCLUDING DATA';
22:10:53 21 dbms_output.put_line ('Table ' || tab.owner || '.' ||
22:10:53 22 tab.name || ' upgraded.');
22:10:53 23 EXCEPTION
22:10:53 24 WHEN OTHERS THEN
22:10:53 25 dbms_output.put_line
22:10:53 26 ('Table ' || tab.owner || '.' || tab.name || ' not upgraded.');
22:10:53 27 dbms_output.put_line
22:10:53 28 ('..' || SUBSTR(SQLERRM, 1, 78));
22:10:53 29 END;
22:10:53 30 END LOOP;
22:10:53 31 END;
22:10:53 32 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.06
22:10:53 SQL>
22:10:53 SQL> Rem ====================================================================
22:10:53 SQL> Rem END catuptabdata.sql
22:10:53 SQL> Rem ====================================================================
22:10:53 SQL>
22:10:53 SQL> SET SERVEROUTPUT OFF
22:10:53 SQL>
22:10:53 SQL> Rem =======================================================================
22:10:53 SQL> Rem Bug 19651064 - Copy data to new WRH$_SYSMETRIC_HISTORY table
22:10:53 SQL> Rem =======================================================================
22:10:53 SQL>
22:10:53 SQL> begin
22:10:53 2 execute immediate 'insert /*+ APPEND parallel enable_parallel_dml */ into
22:10:53 3 WRH$_SYSMETRIC_HISTORY select /*+ PARALLEL */ * from
22:10:53 4 TMP_SYSMETRIC_HISTORY';
22:10:53 5 execute immediate 'drop index TMP_SYSMETRIC_HISTORY_INDEX';
22:10:53 6 execute immediate 'drop table TMP_SYSMETRIC_HISTORY';
22:10:53 7 commit;
22:10:53 8 exception when others then
22:10:53 9 if sqlcode in (-942, -1418) then null;
22:10:53 10 else raise;
22:10:53 11 end if;
22:10:53 12 end;
22:10:53 13 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
22:10:53 SQL>
22:10:53 SQL> Rem =======================================================================
22:10:53 SQL> Rem Bug 19651064 - End
22:10:53 SQL> Rem =======================================================================
22:10:53 SQL>
22:10:53 SQL> Rem =======================================================================
22:10:53 SQL> Rem Component Postupgrade action for 12.2
22:10:53 SQL> Rem =======================================================================
22:10:53 SQL>
22:10:53 SQL> Rem =======================================================================
22:10:53 SQL> Rem If EM in the database, run @emremove.sql to remove EM schema
22:10:53 SQL> Rem This is only needed for upgrading database from 11.2 and prior
22:10:53 SQL> Rem =======================================================================
22:10:53 SQL>
22:10:53 SQL> COLUMN :em_name NEW_VALUE em_file NOPRINT;
22:10:53 SQL> VARIABLE em_name VARCHAR2(30)
22:10:53 SQL> DECLARE
22:10:53 2 BEGIN
22:10:53 3 IF dbms_registry.is_loaded('EM') IS NOT NULL THEN
22:10:53 4 :em_name := '@emremove.sql'; -- EM exists in DB
22:10:53 5 ELSE
22:10:53 6 :em_name := dbms_registry.nothing_script; -- No EM
22:10:53 7 END IF;
22:10:53 8 END;
22:10:53 9 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.00
22:10:53 SQL> SELECT :em_name FROM DUAL;
1 row selected.
Elapsed: 00:00:00.00
22:10:53 SQL> @&em_file
22:10:53 SQL> Rem $Header: rdbms/admin/nothing.sql /main/3 2014/05/19 21:59:08 aketkar Exp $
22:10:53 SQL> Rem
22:10:53 SQL> Rem
22:10:53 SQL> Rem BEGIN SQL_FILE_METADATA
22:10:53 SQL> Rem SQL_SOURCE_FILE: rdbms/admin/nothing.sql
22:10:53 SQL> Rem SQL_SHIPPED_FILE: rdbms/admin/nothing.sql
22:10:53 SQL> Rem SQL_PHASE: NOTHING
22:10:53 SQL> Rem SQL_STARTUP_MODE: NORMAL
22:10:53 SQL> Rem SQL_IGNORABLE_ERRORS: NONE
22:10:53 SQL> Rem SQL_CALLING_FILE: NONE
22:10:53 SQL> Rem END SQL_FILE_METADATA
22:10:53 SQL> Rem
22:10:53 SQL>
22:10:53 SQL> Rem =======================================================================
22:10:53 SQL> Rem EM End
22:10:53 SQL> Rem =======================================================================
22:10:53 SQL>
22:10:53 SQL>
22:10:53 SQL> Rem =======================================================================
22:10:53 SQL> Rem Do Java longer identifiers name translation, if necessary
22:10:53 SQL> Rem =======================================================================
22:10:53 SQL>
22:10:53 SQL> declare
22:10:53 2 ret varchar2(20);
22:10:53 3 begin
22:10:53 4 ret := dbms_java_test.funcall('-lid_translate_all', ' ');
22:10:53 5 exception
22:10:53 6 when others then
22:10:53 7 null;
22:10:53 8 end;
22:10:53 9 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.02
22:10:53 SQL>
22:10:53 SQL> Rem =======================================================================
22:10:53 SQL> Rem Java longer identifiers name translation End
22:10:53 SQL> Rem =======================================================================
22:10:53 SQL>
22:10:53 SQL> Rem =======================================================================
22:10:53 SQL> Rem Signal 'end' of catuppst.sql before catbundle.sql is executed
22:10:53 SQL> Rem =======================================================================
22:10:53 SQL> SELECT dbms_registry_sys.time_stamp('POSTUP_END') as timestamp from dual;
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP POSTUP_END 2019-10-20 22:10:53
DBUA_TIMESTAMP POSTUP_END FINISHED 2019-10-20 22:10:53
DBUA_TIMESTAMP POSTUP_END NONE 2019-10-20 22:10:53
1 row selected.
Elapsed: 00:00:00.00
22:10:53 SQL>
22:10:53 SQL> -- DBUA_TIMESTAMP: catuppst.sql finished
22:10:53 SQL> SELECT dbms_registry_sys.time_stamp('CATUPPST') as timestamp from dual;
TIMESTAMP
-------------------------------------------------------------------------------
COMP_TIMESTAMP CATUPPST 2019-10-20 22:10:53
DBUA_TIMESTAMP CATUPPST FINISHED 2019-10-20 22:10:53
DBUA_TIMESTAMP CATUPPST NONE 2019-10-20 22:10:53
1 row selected.
Elapsed: 00:00:00.01
22:10:53 SQL>
22:10:53 SQL> Rem Set errorlogging off
22:10:53 SQL> SET ERRORLOGGING OFF;
22:10:53 SQL>
22:10:53 SQL> Rem
Elapsed: 00:00:00.00
22:10:53 SQL>
22:10:53 SQL> Rem *********************************************************************
22:10:53 SQL> Rem END catuppst.sql
22:10:53 SQL> Rem *********************************************************************
Run “utlrp.sql” for Recompile the objects
SQL> @/u01/app/oracle/product/12.2.0/db_1/rdbms/admin/utlrp.sql; TIMESTAMP -------------------------------------------------------------------------------- COMP_TIMESTAMP UTLRP_BGN 2019-10-20 22:12:45 DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid DOC> objects in the database. Recompilation time is proportional to the DOC> number of invalid objects in the database, so this command may take DOC> a long time to execute on a database with a large number of invalid DOC> objects. DOC> DOC> Use the following queries to track recompilation progress: DOC> DOC> 1. Query returning the number of invalid objects remaining. This DOC> number should decrease with time. DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6); DOC> DOC> 2. Query returning the number of objects compiled so far. This number DOC> should increase with time. DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED; DOC> DOC> This script automatically chooses serial or parallel recompilation DOC> based on the number of CPUs available (parameter cpu_count) multiplied DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu). DOC> On RAC, this number is added across all RAC nodes. DOC> DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel DOC> recompilation. Jobs are created without instance affinity so that they DOC> can migrate across RAC nodes. Use the following queries to verify DOC> whether UTL_RECOMP jobs are being created and run correctly: DOC> DOC> 1. Query showing jobs created by UTL_RECOMP DOC> SELECT job_name FROM dba_scheduler_jobs DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%'; DOC> DOC> 2. Query showing UTL_RECOMP jobs that are running DOC> SELECT job_name FROM dba_scheduler_running_jobs DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%'; DOC> PL/SQL procedure successfully completed. TIMESTAMP ----------------------------------------------------------- COMP_TIMESTAMP UTLRP_END 2019-10-20 22:15:41 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. ...Starting validation 22:15:57 ...Database user "SYS", database schema "APEX_050000", user# "128" 22:15:57 ...grant execute on "SYS"."DBMS_CRYPTO_INTERNAL" to APEX_050000 22:16:00 ...272 packages ...265 package bodies ...465 tables ...8 functions ...16 procedures ...4 sequences ...497 triggers ...1582 indexes ...255 views ...0 libraries ...14 types ...5 type bodies ...0 operators ..0 index types ...Begin key object existence check 22:16:06 ...Completed key object existence check 22:16:06 ...Setting DBMS Registry 22:16:06 ...Setting DBMS Registry Complete 22:16:06 ...Exiting validate 22:16:06 PL/SQL procedure successfully completed.
Verify the Invalid objects
SQL> select count(1) from dba_objects where status='INVALID';
COUNT(1)
-------
0
Check the restore point & drop from database
SQL> col name for a20 SQL> col GUARANTEE_FLASHBACK_DATABASE for a10 SQL> col TIME for a60 SQL> set lines 250 SQL> select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from V$restore_point; NAME GUARANTEE_ TIME -------------------- ---------- -------------------------------- PRE_UPGRADE_20OCT19 YES 20-OCT-19 08.14.38.000000000 PM
Drop the restore point
SQL> drop restore point PRE_UPGRADE_20OCT19; Restore point dropped.
Verify & Check the database compatibilty & Update compatibility parameter
SQL> select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from V$restore_point; no rows selected
SQL> show parameter compatible NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ compatible string 11.2.0.4.0
SQL> ALTER SYSTEM SET COMPATIBLE = '12.2.0' SCOPE=SPFILE; System altered.
SQL> shu immediate Database closed. Database dismounted. ORACLE instance shut down.
Start the database normally
SQL> startup ORACLE instance started. Total System Global Area 1828716544 bytes Fixed Size 8621856 bytes Variable Size 654311648 bytes Database Buffers 1157627904 bytes Redo Buffers 8155136 bytes Database mounted. Database opened.
Verfiy the DB Compatibility
SQL>select instance_name,status,version from v$instance; INSTANCE_NAME STATUS VERSION --------- ------------ ----------------- testdb OPEN 12.2.0.1.0
---------------------- VERIFY THE DB REGISTRY ---------------------- > col COMP_ID for a15 > col COMP_NAME for a30 > col VERSION for a20 > set lines 250 > set pages 999 > select COMP_ID,COMP_NAME,VERSION,STATUS from dba_registry; COMP_ID COMP_NAME VERSION STATUS ----------------------------- --------------- -------------------------------------------- CATALOG Oracle Database Catalog Views 12.2.0.1.0 VALID CATPROC Oracle Database Packages and Types 12.2.0.1.0 VALID JAVAVM JServer JAVA Virtual Machine 12.2.0.1.0 VALID XML Oracle XDK 12.2.0.1.0 VALID CATJAVA Oracle Database Java Packages 12.2.0.1.0 VALID APS OLAP Analytic Workspace 12.2.0.1.0 VALID OWM Oracle Workspace Manager 12.2.0.1.0 VALID CONTEXT Oracle Text 12.2.0.1.0 VALID XDB Oracle XML Database 12.2.0.1.0 VALID ORDIM Oracle Multimedia 12.2.0.1.0 VALID SDO Spatial 12.2.0.1.0 VALID XOQ Oracle OLAP API 12.2.0.1.0 VALID APEX Oracle Application Express 5.0.4.00.12 VALID 13 rows selected.
Start the DB listener
[oracle@testdb ~]$ lsnrctl start
Verify listener status
[oracle@testdb ~]$ lsnrctl status LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 20-OCT-2019 22:31:46 Copyright (c) 1991, 2016, Oracle. All rights reserved. Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521)) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 12.2.0.1.0 - Production Start Date 20-OCT-2019 22:31:25 Uptime 0 days 0 hr. 0 min. 20 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Log File /u01/app/oracle/diag/tnslsnr/testdb/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=testdb.localdomain)(PORT=1521))) Services Summary... Service "testdb" has 1 instance(s). Instance "testdb", status READY, has 1 handler(s) for this service... Service "testdbXDB" has 1 instance(s). Instance "testdb", status READY, has 1 handler(s) for this service... The command completed successfully
=====Hence tested & verified in our test env=====