Example of Manual Upgrade of Windows Non-CDB Oracle Database
These examples show the steps to complete preupgrade checks, upgrade, and postupgrade checks for an Oracle Database 11g Release 2 (11.2.0.4) upgrade to Oracle Database 19c.
The specific examples in these topics show an upgrade on the Microsoft Windows platform. They use tools specific to the Windows environment. However, these example provide an overview of a manual installation experience.
- Preparing to Upgrade Windows Non-CDB Using Command-Line Utilities
Complete the required preupgrade steps before upgrading Non-CDB Oracle Database 11g release 2 (11.2.0.4) databases to a Non-CDB Oracle Database 12c and later releases. - Manually Upgrading Windows Non-CDB Using Command-Line Utilities
These examples show upgrade steps to upgrade a Non-CDB Oracle Database 11g release 2 (11.2.0.4) to a Non-CDB Oracle Database - Running Postupgrade Fixup Scripts After Upgrading a Non-CDB Database
These examples show the process of running thepostupgrade_fixups.sqlscript for Non-CDB to Non-CDB upgrades.
Parent topic: Upgrading Oracle Database
Preparing to Upgrade Windows Non-CDB Using Command-Line Utilities
Complete the required preupgrade steps before upgrading Non-CDB Oracle Database 11g release 2 (11.2.0.4) databases to a Non-CDB Oracle Database 12c and later releases.
This procedure provides an example of how to use command-line utilities to prepare your system to carry out a manual upgrade of a Non-CDB Oracle Database to a current release Non-CDB Oracle Database on Windows. In this example, the Oracle Database that you are upgrading is Oracle Database 11g release 2 (11.2.0.4).
Log in as a user that is a member of the ORA_DBA group. Oracle recommends that you install the new release Oracle Database binaries on the server. Refer to the Oracle Database installation guide for Windows to complete that procedure.
The Preupgrade consists of the following steps:
-
Reset the user environment variables from the new release Oracle home to the 11.2.0.4 Oracle home for the Oracle Installation Owner User Account (Oracle user).
-
Test the connection to the 11.2.0.4 Oracle Database.
-
Run the Pre-Upgrade Information Tool on the 11.2.0.4 Oracle Database home.
-
Review the Pre-Upgrade Information Tool log (
preupgrade.log). If there arepreupgrade_fixups.sqlscripts that you must run, then run them. In this example, we show manual fixes for the following errors:-
Oracle Enterprise Manager is present (
em_present). -
The OLAP catalog is present (
amd_exists). -
The ADMINISTER DATABASE TRIGGER privilege must be granted (
trgowner_no_admndbtrg). -
Materialized views are not refreshed (
mv_refresh). -
Oracle Application Express is not upgraded (
apex_upgrade_msg)
-
-
Run
preupgrade_fixups.sqlscripts. -
Back up your database.
These examples use the following systems, paths, and users:
Oracle Database 11g release 2 (11.2.0.4)
-
Oracle Installation Owner user account:
oracle1, which is a member of the ORA_DBA group. -
Oracle home:
C:\app\oracle\product\11.2.0\dbhome_1
Oracle Database 19c
-
Oracle Installation Owner user account:
oracle2, which is a member of the ORA_DBA group. -
Oracle home:
C:\app\oracle1\product\19\dbhome_1
Example 4-6 Resetting the User Environment Variables
This example shows how to change the environment variables from values set for the Oracle Database 19c installation:
C:\app\oracle1\product\19\dbhome_1\rdbms\admin>set ORACLE_HOME=C:\app\oracle2\product\11.2.0\dbhome_1
C:\app\oracle1\product\19\dbhome_1\rdbms\admin>set ORACLE_SID=orcl11Example 4-7 Testing the Connection to the Database
This example shows how to test the connection to the database, and ensure that you are connecting to the release 11.2.0.4 database that you want to upgrade.
C:\app\oracle1\product\19\dbhome_1\rdbms\admin>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Dec 11 12:14:49 2018
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select version from v$instance;
VERSION
-----------------
11.2.0.4.0
SQL>Example 4-8 Running the Pre-Upgrade Information Tool (preupgrade.jar)
This example shows how to run the tool using the command java -jar %ORACLE_HOME19%\rdbms\admin\preupgrade.jar.
The Preupgrade Information Tool prints the path to the log file, and prints the path to the fixup scripts. By default, these fixup scripts are placed in %ORACLE_BASE%.
C:\app\oracle1\product\19\dbhome_1\rdbms\admin>java -jar preupgrade.jar
Preupgrade generated files:
C:\app\oracle2\cfgtoollogs\orcl11\preupgrade\preupgrade.log
C:\app\oracle2\cfgtoollogs\orcl11\preupgrade\preupgrade_fixups.sql
C:\app\oracle2\cfgtoollogs\orcl11\preupgrade\postupgrade_fixups.sql
C:\app\oracle1\product\12.2.0\dbhome_1\rdbms\admin>Example 4-9 Reviewing the Pre-Upgrade Information Tool Log (preupgrade.log)
In this example, the log file preupgrade.log is generated in the following path: C:\app\oracle2\cfgtoollogs\orcl11\preupgrade\preupgrade.log.
The log file can direct you to run some SQL fixup files manually. If it directs you to run SQL files, then copy these files to the earlier release 11.2.0.4 Oracle home, and then run the scripts.
Example 4-10 Running the Preupgrade Fixup SQL script (preupgrade_fixups.sql)
C:\app\oracle1\product\19\dbhome_1\rdbms\admin>sqlplus / as sysdba @C:\app\oracle2\cfgtoollogs\orcl11\preupgrade\preupgrade_fixups.sql
SQL*Plus: Release 11.2.0.4.0 Production on Tue Dec 11 12:34:08 2018
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Executing Oracle PRE-Upgrade Fixup Script
Auto-Generated by: Oracle Preupgrade Script
Version: 19.0.0.0 Build: 1
Generated on: 2018-12-11 12:18:06
Source Database: ORCL11
Source Database Version: 11.2.0.4.0
For Upgrade to Version: 12.2.0.1.0
Fixup
Check Name Status Further DBA Action
---------- ------ ------------------
em_present Failed Manual fixup recommended.
amd_exists Failed Manual fixup recommended.
dictionary_stats Failed Manual fixup recommended.
trgowner_no_admndbtrg Failed Manual fixup recommended.
mv_refresh Failed Manual fixup recommended.
apex_upgrade_msg Failed Manual fixup recommended.
PL/SQL procedure successfully completed.
In this example, some automatic checks failed, and required manual fixups. Refer to the examples that follow to see how to perform manual fixups.
Example 4-11 Manually Removing Oracle Enterprise Manager (em_present)
The preupgrade_fixups.sql log file provides you with instructions to remove Oracle Enterprise Manager manually (em_present):
+ Remove the EM repository.
- Copy the rdbms/admin/emremove.sql script from the target 19.0.0.0.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
...
...
...
Execution:
Step 1 - Stop dbconsole:
C:\app\oracle1\product\19\dbhome_1\rdbms\admin> emctl stop dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.2.0.4.0
Copyright (c) 1996, 2011 Oracle Corporation. All rights reserved.
https://db01.example.com:1158/em/console/aboutApplication
The OracleDBConsoleorcl11 service is stopping...........
The OracleDBConsoleorcl11 service was stopped successfully.
Step 2 - Remove em (provide credentials if needed)
...
...
...
Dropping synonym : SETEMVIEWUSERCONTEXT ...
Dropping synonym : SMP_EMD_AVAIL_OBJ ...
Dropping synonym : SMP_EMD_DELETE_REC_ARRAY ...
Dropping synonym : SMP_EMD_INTEGER_ARRAY ...
Dropping synonym : SMP_EMD_INTEGER_ARRAY_ARRAY ...
Dropping synonym : SMP_EMD_NVPAIR ...
Dropping synonym : SMP_EMD_NVPAIR_ARRAY ...
Dropping synonym : SMP_EMD_STRING_ARRAY ...
Dropping synonym : SMP_EMD_STRING_ARRAY_ARRAY ...
Dropping synonym : SMP_EMD_TARGET_OBJ ...
Dropping synonym : SMP_EMD_TARGET_OBJ_ARRAY ...
Finished phase 5
Starting phase 6 : Dropping Oracle Enterprise Manager related other roles ...
Finished phase 6
The Oracle Enterprise Manager related schemas and objects are dropped.
Do the manual steps to studown the DB Control if not done before running this
script and then delete the DB Control configuration files
PL/SQL procedure successfully completed.
SQL>Example 4-12 Manually Removing the OLAP Catalog (amd_exists)
The preupgrade_fixups.sql log file provides you with instructions to remove the OLAP catalog manually (amd_exists):
+ 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.
sqlplus / as sysdba @%ORACLE_HOME%\olap\admin\catnoamd.sql
...
...
...
Synonym dropped.
User dropped.
Role dropped.
SQL>Example 4-13 Gathering Current Dictionary Statistics (dictionary_stats)
The preupgrade_fixups.sql log file provides you with instructions to gather directory statistics (dictionary_stats).
+ Gather dictionary statistics prior to database upgrade in off-peak time
using:
EXECUTE dbms_stats.gather_dictionary_stats;
SQL> EXECUTE dbms_stats.gather_dictionary_stats;
PL/SQL procedure successfully completed.Example 4-14 Granting the ADMINISTER DATABASE TRIGGER privilege (trgowner_no_admndbtrg)
The preupgrade_fixups.sql log file provides you with instructions to grant the ADMINISTER DATABASE TRIGGER privilege (trgowner_no_admndbtrg):
+ 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')"
SQL> SELECT OWNER,
2 TRIGGER_NAME FROM DBA_TRIGGERS WHERE BASE_OBJECT_TYPE='DATABASE' AND
3 OWNER NOT IN (SELECT GRANTEE FROM DBA_SYS_PRIVS WHERE
4 PRIVILEGE='ADMINISTER DATABASE TRIGGER');
OWNER TRIGGER_NAME
------------------------------ ------------------------------
MDSYS SDO_DROP_USER
MDSYS SDO_ST_SYN_CREATE
MDSYS SDO_TOPO_DROP_FTBL
MDSYS SDO_GEOR_BDDL_TRIGGER
MDSYS SDO_GEOR_ADDL_TRIGGER
MDSYS SDO_NETWORK_DROP_USER
6 rows selected.
SQL> grant ADMINISTER DATABASE TRIGGER to MDSYS;
Grant succeeded.
Example 4-15 Refreshing Materialized Views (mv_refresh)
The preupgrade_fixups.sql log file provides you with instructions to refresh materialized views (mv_refresh):
+ 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.
SQL> declare
2 num_failures integer(3) :=0;
3 begin
4 DBMS_MVIEW.REFRESH_ALL_MVIEWS(num_failures,'C','', TRUE, FALSE);
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> select count(1) from sumdelta$;
COUNT(1)
----------
0
SQL>Example 4-16 Upgrading Oracle Application Express (apex_upgrade_msg)
Thepreupgrade_fixups.sql log file recommends to you that you can upgrade Oracle Application Express (apex_upgrade_msg). Running this upgrade can reduce the downtime required for the Oracle Database upgrades. The log file provides you with information about how to complete that upgrade:+ 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.11.
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.
Download: http://www.oracle.com/technetwork/developer-tools/apex/downloads/index.html provide credentials.
Then go to: http://www.oracle.com/technetwork/developer-tools/apex/application-express/upgrade-apex-for-xe-154969.html and
https://docs.oracle.com/cd/E59726_01/install.50/e39144/toc.htm to guide you to do the upgrade.
Once downloaded, please go to the directory and unzip the files, run the apex upgrade from there.
...
...
Completing registration process. 12:07:41
Validating installation. 12:07:41
...Starting validation 12:07:41
...Database user "SYS", database schema "APEX_050000", user# "90" 12:07:41
...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 12:07:53
...Completed key object existence check 12:07:54
...Setting DBMS Registry 12:07:54
...Setting DBMS Registry Complete 12:07:54
...Exiting validate 12:07:54
PL/SQL procedure successfully completed.
timing for: Validate Installation
Elapsed: 00:00:13.00
Session altered.
timing for: Complete Installation
Elapsed: 00:18:40.49
...
...
Caution:
After you complete preupgrade steps, Oracle recommends that you back up your database before proceeding with the upgrade.
Related Topics
Manually Upgrading Windows Non-CDB Using Command-Line Utilities
These examples show upgrade steps to upgrade a Non-CDB Oracle Database 11g release 2 (11.2.0.4) to a Non-CDB Oracle Database
After you complete running preupgrade steps, you can upgrade the Non-CDB Oracle Database to the new release Non-CDB Oracle Database. Before starting the upgrade, you must stop the database services. You can stop database services either by using command-line commands, or by using Microsoft Windows PowerShell scripting.
-
Stop the database service, using either command-line commands, or PowerShell.
-
Delete the database service from the earlier release Oracle home.
-
Stop the listener from the earlier release Oracle home
-
Set the environment variables to the new Oracle home.
-
Copy database files, such as
tnsnames.ora,listener.ora, password files, wallets, and other similar files to the new Oracle home. -
Copy the PFILE to the new Oracle Database Oracle home, and create a new service using the Oracle Database binary in the new Oracle home. (In this example, we assume that the PFILE is compatible. It is possible that your PFILE is not compatible with the new release.)
-
Start the database upgrade.
-
Complete the post-upgrade steps.
Example 4-17 Stopping the Database Service Using Command-Line Commands
-
If you do not know the service name, then identify the service name.
c:\apex\apex>sc query type= service | find /i "orcl11" SERVICE_NAME: OracleServiceORCL11 DISPLAY_NAME: OracleServiceORCL11 -
Using the service name, find out what the status is of the service.
c:\apex\apex>sc query OracleServiceORCL11 SERVICE_NAME: OracleServiceORCL11 TYPE : 10 WIN32_OWN_PROCESS STATE : 4 RUNNING (STOPPABLE, PAUSABLE, ACCEPTS_SHUTDOWN) WIN32_EXIT_CODE : 0 (0x0) SERVICE_EXIT_CODE : 0 (0x0) CHECKPOINT : 0x0 WAIT_HINT : 0x0 -
Stop the service
c:\apex\apex>sc stop OracleServiceORCL11 SERVICE_NAME: OracleServiceORCL11 TYPE : 10 WIN32_OWN_PROCESS STATE : 3 STOP_PENDING (STOPPABLE, PAUSABLE, ACCEPTS_SHUTDOWN) WIN32_EXIT_CODE : 0 (0x0) SERVICE_EXIT_CODE : 0 (0x0) CHECKPOINT : 0x5 WAIT_HINT : 0x15f90 -
Wait for a few minutes, and then check the status.
c:\apex\apex>sc query OracleServiceORCL11 SERVICE_NAME: OracleServiceORCL11 TYPE : 10 WIN32_OWN_PROCESS STATE : 1 STOPPED WIN32_EXIT_CODE : 0 (0x0) SERVICE_EXIT_CODE : 0 (0x0) CHECKPOINT : 0x0 WAIT_HINT : 0x0After the services are stopped, you can proceed to delete the services.
Example 4-18 Stopping the Database Service Using Microsoft Windows PowerShell Scripting
-
Check the status of the service.
PS C:\app\oraclbm\cfgtoollogs\orcl\preupgrade> Get-Service | Where-Object {$_.displayName.Contains("ORCL11")} Status Name DisplayName ------ ---- ----------- Stopped OracleJobSchedu... OracleJobSchedulerORCL11 Running OracleServiceOR... OracleServiceORCL11 Running OracleVssWriter... Oracle ORCL11 VSS Writer Service -
Stop the service.
PS C:\app\oraclbm\cfgtoollogs\orcl\preupgrade> Get-Service | Where-Object {$_.displayName.Contains("ORCL11")} | Stop-Ser vice WARNING: Waiting for service 'OracleServiceORCL11 (OracleServiceORCL11)' to stop... WARNING: Waiting for service 'OracleServiceORCL11 (OracleServiceORCL11)' to stop... WARNING: Waiting for service 'OracleServiceORCL11 (OracleServiceORCL11)' to stop... WARNING: Waiting for service 'OracleServiceORCL11 (OracleServiceORCL11)' to stop... WARNING: Waiting for service 'OracleServiceORCL11 (OracleServiceORCL11)' to stop... WARNING: Waiting for service 'OracleServiceORCL11 (OracleServiceORCL11)' to stop... WARNING: Waiting for service 'OracleServiceORCL11 (OracleServiceORCL11)' to stop... WARNING: Waiting for service 'OracleServiceORCL11 (OracleServiceORCL11)' to stop... WARNING: Waiting for service 'OracleServiceORCL11 (OracleServiceORCL11)' to stop... WARNING: Waiting for service 'OracleServiceORCL11 (OracleServiceORCL11)' to stop... WARNING: Waiting for service 'OracleServiceORCL11 (OracleServiceORCL11)' to stop...After the services are stopped, you can proceed to delete the database service.
Example 4-19 Deleting the Database Service from the Earlier Release Oracle Home
For Oracle Database 11g release 2 (11.2.0.4) on Windows, use ORADIM from the earlier release Oracle home. ORADIM is a Windows-specific utility that you can use to administer the Windows service.
c:\apex\apex>oradim -delete -sid orcl11
Unable to stop service, OS Error = 1062
Instance deleted.
Example 4-20 Stopping the Listener for the Earlier Release Oracle Home
c:\apex\apex>lsnrctl status
LSNRCTL for 64-bit Windows: Version 11.2.0.4.0 - Production on 13-JUL-2016 13:58:52
Copyright (c) 1991, 2012, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1522)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 64-bit Windows: Version 11.2.0.4.0 - Production
Start Date 05-JUL-2018 10:01:30
Uptime 8 days 3 hr. 57 min. 23 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File C:\app\oracle2\product\11.2.0\dbhome_1\network\admin\listener.ora
Listener Log File C:\app\oracle2\diag\tnslsnr\slc01auu\listener\alert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1522ipc)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db01.example.com)(PORT=1522)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
c:\apex\apex>lsnrctl stop
LSNRCTL for 64-bit Windows: Version 11.2.0.4.0 - Production on 13-JUL-2018 13:59:00
Copyright (c) 1991, 2012, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1522)))
The command completed successfully Example 4-21 Setting the environment variables to the new Oracle home
c:\apex\apex>set ORACLE_HOME=C:\app\oracle1\product\18.1.0\dbhome_1
c:\apex\apex>SET PATH=%ORACLE_HOME%\BIN;%PATH%
c:\apex\apex>SET ORACLE_SID=ORCL11 Example 4-22 Copying the PFILE to the New Oracle home, and Creating a New Service Using the New Oracle Database binary
This example shows how to use the Windows-specific ORADIM utility to create a new database service. In this example, the PFILE from the earlier release is compatible with the new release.
c:\apex\apex>C:\app\oracle1\product\18.1.0\dbhome_1\bin\oradim -new -sid orcl11 -syspwd
My-sys-password -maxusers 1000 -startmode auto -pfile C:\app\oracle1\product\18.1.0\dbhome_1\database\initorcl11.ora
Enter password for Oracle service user:
Instance created.
Example 4-23 Starting the Database Upgrade
Start the upgrade using the Parallel Upgrade Utility.
Note:
You can find that you have to change the folder symbolic links on the PFILE for paths such as DIAGNOSTIC_DEST. If this is the case, then you see errors such as the following examples:
ORA-48173: error checking directory existence during ADR initialization [C:\app\oracle2\diag\rdbms\orcl11]
ORA-00205: error in identifying control file, check alert log for more info
If you see these errors, then grant permissions to the user on the earlier release Oracle home, and on all the related folders with inheritance. Make this change also on any access control list (ACL) that the database accesses, such as wallet files. This step is particularly necessary if you are using the earlier release Oracle home as an ORADATA location.
If you are using the built-in account, then the service runs as LocalSystem. If you are using a virtual account, then services run using the Windows virtual accounts, with names derived from the service name. If the Oracle Database 19c binaries owner is a virtual account, then you must add that virtual account to the group ORA_Homename_SVCAACCTS, instead of adding the Oracle Home User.
Oracle Database 19c includes a shell script, dbupgrade. The shell script calls the Parallel Upgrade Utility (catctl.pl), so that you can run the upgrade as a command. This example shows the upgrade of Oracle Database from release 11.2.0.4 to release 19c. The upgrade time for your system can vary from the results in this example.
C:\app\oracle1\product\19.1.0\dbhome_1\bin>dbupgrade
...
...
...
C:\app\oracle1\product\19.1.0\dbhome_1\bin>REM Batch file to execute catctl.pl
Argument list for [C:\app\oracle1\product\19\dbhome_1\rdbms\admin\catctl.pl]
Run in c = 0
Do not run in C = 0
Input Directory d = 0
Echo OFF e = 1
Simulate E = 0
Forced cleanup F = 0
Log Id i = 0
Child Process I = 0
Log Dir l = c:\temp2
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: [19.0.0.0.0]
STATUS: [production]
BUILD: [RDBMS_MAIN_WINDOWS.X64_160624]
C:\app\oracle1\product\19.1.0\dbhome_1\rdbms\admin\orahome.exe = [C:\app\oracle1\product\19.1.0\dbhome_1]
C:\app\oracle1\product\19.1.0\dbhome_1\bin\orabasehome.exe = [C:\app\oracle1\product\189.1.0\dbhome_1]
catctlGetOrabase = [C:\app\oracle1\product\19.1.0\dbhome_1]
Analyzing file C:\app\oracle1\product\19.1.0\dbhome_1\rdbms\admin\catupgrd.sql
Log file directory = [c:\temp2]
catcon: ALL catcon-related output will be written to [c:\temp2/catupgrd_catcon_3252.lst]
catcon: See [c:\temp2/catupgrd*.log] files for output generated by scripts
catcon: See [c:\temp2/catupgrd_*.lst] files for spool files, if any
Number of Cpus = 2
Database Name = orcl11
DataBase Version = 11.2.0.4.0
Parallel SQL Process Count = 4
Components in [orcl11]
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-109] Start Time:[2018_12_13 15:49:40]
------------------------------------------------------
*********** Executing Change Scripts ***********
Serial Phase #:0 [orcl11] Files:1
...
...
...
------------------------------------------------------
Phases [0-109] End Time:[2018_12_13 16:53:27]
------------------------------------------------------
Grand Total Time: 3830s
LOG FILES: (c:\temp2\catupgrd*.log)
Upgrade Summary Report Located in:
c:\temp2\upg_summary.log
Grand Total Upgrade Time: [0d:1h:3m:50s]Example 4-24 Completing the Post-Upgrade Checks
Start the upgraded Oracle Database, and check the version and status.
C:\app\oracle1\product\19.1.0\dbhome_1\bin>sqlplus / as sysdba
SQL*Plus: Release 19.1.0 Production on Thu Dec 13 09:38:50 201
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 1828716544 bytes
Fixed Size 8740096 bytes
Variable Size 503317248 bytes
Database Buffers 1308622848 bytes
Redo Buffers 8036352 bytes
Database mounted.
Database opened.
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE
SQL> select version from v$instance;
VERSION
-----------------
19.0.0.0.0
SQL> After the upgrade is complete, carry out post-upgrade checks to ensure that objects are valid, and that there are no remaining issues.
Running Postupgrade Fixup Scripts After Upgrading a Non-CDB Database
These examples show the process of running the postupgrade_fixups.sql script for Non-CDB to Non-CDB upgrades.
The examples show an upgrade from a Non-CDB Oracle Database 11g release 2 (11.2.0.4) to a Non-CDB Oracle Database 19c.
Example 4-25 Running the Postupgrade Fixups Script
Run the postupgrade_fixups.sql script that you generated with the Pre-Upgrade Information Tool in the earlier release Oracle home before starting the upgrade:
SQL> @C:\app\oracle\cfgtoollogs\orcl11\preupgrade\postupgrade_fixups.sql
SQL> REM
SQL> REM Oracle POST-Upgrade Fixup Script
SQL> REM
SQL> REM Auto-Generated by: Oracle Preupgrade Script
SQL> REM Version: 19.0.0.0.0
SQL> REM Generated on: 2018-12-05 12:18:07
SQL> REM
SQL> REM Source Database: ORCL11
SQL> REM Source Database Version: 11.2.0.4.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 POST-Upgrade Fixup Script
Auto-Generated by: Oracle Preupgrade Script
Version: 19.0.0.0.0
Generated on: 2018-12-05 12:18:07
Source Database: ORCL11
Source Database Version: 11.2.0.4.0
For Upgrade to Version: 19.0.0.0.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 Failed Manual fixup recommended.
fixed_objects Passed None
upg_by_std_upgrd Passed None
PL/SQL procedure successfully completed.
Elapsed: 00:00:11.26
SQL> In this example, the postupgrade_fixup.sql script could not complete three fixes. so you are instructed to carry out manual fixups. This outcome is normal. To carry out the manual fixups, look at the log file, preupgrade.log. It contains instructions and script information for how to run manual fixup scripts. These fixup scripts are generated for you by the postupgrade_fixups.sql script. Follow the instructions provided in the log file to fix the issues.
Example 4-26 Manual Fixup Instructions for Oracle-Maintained Types In User Tables (depend_usr_tables)
+ If you use the -T option for the database upgrade, then run
@?/rdbms/admin/utluptabdata.sql after the upgrade is complete, to
VALIDATE and UPGRADE any user tables affected by changes to
Oracle-Maintained types.
SQL> @C:\app\oracle\product\19.1.0\dbhome_1\rdbms\admin\utluptabdata.sql
PL/SQL procedure successfully completed.
SQL>
Example 4-27 Manual Fixup Instructions for Time Zone Version (old_time_zones_exist)
+ Upgrade the database time zone version using the DBMS_DST package.
The database is using timezone datafile version 14 and the target
19.0.0.0.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
SQL> @C:\Users\oracle\Downloads\DBMS_DST_scriptsV1.9\DBMS_DST_scriptsV1.9\countstatsTSTZ.sql
.
Amount of TSTZ data using num_rows stats info in DBA_TABLES.
.
For SYS tables first...
Note: empty tables are not listed.
Stat date - Owner.Tablename.Columnname - num_rows
05/07/2019 - SYS.AQ$_ALERT_QT_S.CREATION_TIME - 5
...
Total numrow of SYS TSTZ columns is : 14652
There are in total 154 non-SYS TSTZ columns.
.
...
For non-SYS tables ...
Note: empty tables are not listed.
...
...
Stat date - Owner.Tablename.Columnname - num_rows
Total numrow of non-SYS TSTZ columns is : 17
There are in total 32 non-SYS TSTZ columns.
Total Minutes elapsed : 0
SQL>
SQL> spool upg_tzv_check.log
SQL> @C:\Users\oracle\Downloads\DBMS_DST_scriptsV1.9\DBMS_DST_scriptsV1.9\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 19.0.0.0.0 .
INFO: Database RDBMS DST version is DSTv14 .
INFO: No known issues detected.
INFO: Now detecting new RDBMS DST version.
A prepare window has been successfully started.
INFO: Newest RDBMS DST version detected is 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> spool off
SQL> @C:\Users\oracle\Downloads\DBMS_DST_scriptsV1.9\DBMS_DST_scriptsV1.9\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. <--------- you might need to wait a couple of minutes and hit enter a couple of times.
ORACLE instance shut down.
ORACLE instance started.
Total System Global Area 1828716544 bytes
Fixed Size 8740096 bytes
Variable Size 503317248 bytes
Database Buffers 1308622848 bytes
Redo Buffers 8036352 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 ... <--------- you might need to wait a couple of minutes
and hit enter a couple of times.
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 8740096 bytes
Variable Size 503317248 bytes
Database Buffers 1308622848 bytes
Redo Buffers 8036352 bytes
Database mounted.
Database opened. ... <--------- you might need to wait a couple of minutes and hit enter a couple of times.
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! ... <--------- you might need to wait a couple of minutes
and hit enter a couple of times.
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: "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
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_L"
Number of failures: 0
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_S"
Number of failures: 0
INFO: Total failures during update of TSTZ data: 0 .
An upgrade window has been successfully ended.
INFO: Your new Server RDBMS DST version is 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.
SQL>
Example 4-28 Manual Fixup for Refreshing Dictionary Statistics (post_dictionary)
+ Gather dictionary statistics after the upgrade using the command:
EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
This recommendation is given for all preupgrade runs.
SQL> EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
PL/SQL procedure successfully completed.
SQL>