FILE|TERMINAL - Use this option to direct script output to a file. Use TERMINAL to direct output to the terminal. If it is not specified then default is FILE.
TEXT - Use this option to specify log should be in Text format. Use TEXT to specify text output. Use XML to specify XML output. If you do not specify an output type, then the default is text.
DIR - Logs will be created under <output_dir>. Directs the output to a specific directory. If you do not specify an output directory with the DIR option, then the output is directed to one of the default locations: If you define ORACLE_BASE environment variable then the generated scripts and log files will be created under $ORACLE_BASE/cfgtoollogs/<dbname>/preupgrade/ location else it will create under $ORACLE_HOME/cfgtoollogs/db_name/preupgrade/.
please make sure all the pluggable databases are open before running below preupgrade step:
source Oracle Home : /refresh/app/oracle/product/184.108.40.206.0
target Oracle Home : /refresh/app/oracle/product/220.127.116.11
$ sqlplus "/ as sysdba"
Step 3.3 Backing Up Oracle Database For Upgrade.
It is recommended to backup Oracle database after you run the Pre-Upgrade Information Tool. Take backup or create a guaranteed restore point or both. Test your backup. Ensure there is a proper fallback plan in case of any issues.
Step 3.4 Verify SYS and SYSTEM default tablespace.
Ensure the users SYS and SYSTEM have 'SYSTEM' as their default tablespace.
You must have sufficient space in the 'SYSTEM' tablespace or set extents to unlimited.
SQL> alter session set container=<PDB_Name>;
SQL> SELECT username, default_tablespace
WHERE username in ('SYS','SYSTEM');
If DEFAULT_TABLESPACE is anything other than SYSTEM tablespace, modify the default tablespace for user SYS and SYSTEM to SYSTEM by using the command below:
SQL> alter user SYS default tablespace SYSTEM;
SQL> alter user SYSTEM default tablespace SYSTEM;
Step 3.5 Gathering Optimizer Statistics to Decrease Database Upgrade Downtime
Oracle strongly recommends that you use this procedure to gather statistics before performing Oracle Database upgrades.Oracle recommends that you use the DBMS_STATS.GATHER_DICTIONARY_STATS procedure to gather these statistics. For example, enter the following command:
Step 3.6 Verifying Materialized View Refreshes are Complete Before Upgrade
Use this procedure to query the system to determine if there are any materialized view refreshes still in progress. Before upgrading Oracle Database, you must wait until all materialized views have
$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -l /tmp -b mview_refresh -- --x"SELECT o.name FROM sys.obj$ o, sys.user$ u, sys.sum$ s WHERE o.type# = 42 AND bitand(s.mflags, 8) =8"
It will verify against all the pluggable databases or below query can be used to verify against particular PDB:
SQL> SELECT o.name FROM sys.obj$ o, sys.user$ u, sys.sum$ s WHERE o.type# = 42 AND bitand(s.mflags, 8) =8;
Doc ID 1406586.1 - How to Handle Materialized Views When You Upgrade or Clone a Database
Step 3.7 Check of TIMESTAMP WITH TIMEZONE Datatype
The time zone files that are supplied with Oracle Database 19c release is version 32.
Case 1 Timezone version of source database is lower or equal 32.
If the source database is using a timezone file lower than version 32 then there is no DST patch to apply in source oracle home or target 12cR2 home.
Case 2 Timezone version of source database is higher than 32.
If the source database uses a Timezone version higher than 32 then BEFORE the upgrade you MUST patch the target 19c Oracle Home with a timezone data file of the SAME version as the one used in the source release database.
Step 3.8 Ensuring That No Files Are in Backup Mode and no files need media recovery Before Upgrading
Execute below query to check for the status of the backup:
SQL> SELECT * FROM v$backup WHERE status != 'NOT ACTIVE';
Ensure that no files require media recovery:
SQL> SELECT * FROM v$recover_file;
Step 3.9 Purging Recycle Bin before upgrade
below command can be used to purge from all the pdbs:
Step 3.11 Starting with Oracle Database 12c release 2 (12.2), you can upgrade the database without disabling Oracle Database Vault.
If your target Oracle Database release is 12.2 or later, then you can upgrade without disabling Oracle Database Vault.
If you have Oracle Database Vault enabled in your source Oracle Database release, then you can upgrade Oracle Database to Oracle Database 18c and later releases without first disabling Oracle Database Vault. After the upgrade, if your source Oracle Database release is Oracle Database 12c release 1 (12.1) or later, then Oracle Database Vault is enabled with the same enforcement settings that you had in place before the upgrade.
For example, if your source database is Oracle Database release 12.1, and Oracle Database Vault was disabled in that release, then it remains disabled after you upgrade.
If your source Oracle Database release 12.1 database had Oracle Database Vault enabled before the upgrade, then Oracle Database Vault is enabled after the upgrade.
If you manually disable Oracle Database Vault before the upgrade, then you must enable Oracle Database Vault manually after the upgrade
Step 3.12 Back Up Files to Preserve Downgrade and Recovery Options.
Oracle Data Guard Broker Configuration File and Downgrades
With upgrades to Oracle Database 19c and later releases, you must back up the Data Guard broker configuration file to preserve the capability to downgrade to an earlier release.
In releases before Oracle Database 19c, Oracle Database settings that are mapped to Oracle Data Guard broker properties are maintained in the Oracle Data Guard broker configuration file, and can be modified using the DGMGRL command-line interface. However, starting with Oracle Database 19c, these database settings are no longer stored in the broker configuration file. As a result of this change, although you can continue to modify these properties using DGMGRL, the values that you modify are no longer stored in the Oracle Data Guard broker configuration file. Instead, the DGMGRL commands directly modify the Oracle Database initialization parameters or database settings to which these Oracle Data Guard Broker properties are mapped.
Because of this change to the way that property settings are managed, if you use Oracle Data Guard broker, then Oracle recommends that you export your earlier release Oracle Data Guard broker configuration file to a secure backup location before you start the upgrade. If you do not back up the Oracle Data Guard broker configuration file before the upgrade, then after the upgrade, you cannot downgrade to an earlier release and retain the property options you previously selected for Oracle Data Guard
Step 3.13 Schema-Only Accounts and Upgrading EXPIRED Password Accounts
Before starting your upgrade, determine if you want to use password authenticate to default Oracle Database accounts where their passwords are in EXPIRED status, and their account is in LOCKED status
During upgrades to Oracle Database 19c, default Oracle accounts that have not had their passwords reset before upgrade (and are set to EXPIRED status), and that are also set to LOCKED status, are set to NO AUTHENTICATION after the upgrade is complete.
Because of this new feature, default accounts that are changed to schema-only accounts become unavailable for password authentication. The benefit of this feature is that administrators no longer have to periodically rotate the passwords for these Oracle Database-provided schemas. This feature also reduces the security risk of attackers using default passwords to hack into these accounts.
If you want to prevent these Oracle accounts from being set to schema-only accounts during the upgrade, then you must either set a valid strong password for the account before you start the upgrade, or set a valid strong password for these accounts after upgrade, or unlock the accounts before you log in to the upgraded Oracle Database.
After the upgrade, an administrator can also enable password authentication for schema-only accounts. However, for better security, Oracle recommends that you keep these accounts as schema only accounts.
If Oracle wallet used with Transparent Data Encryption (TDE), then copy the sqlnet.ora and wallet file to the new Oracle home. You must copy the sqlnet.ora and the wallet file manually before starting the upgrade.
1. Log in as an authorized user.
2. Manually copy the sqlnet.ora file, and the wallet file, ewallet.p12, to the new release Oracle home.
Open the Oracle wallet in mount.
SQL> STARTUP MOUNT;
SQL> ALTER SYSTEM SET ENCRYPTION WALLET OPEN
Step 3.15 Understanding Password Case Sensitivity and SEC_CASE_SENSITIVE_LOGON parameter
Starting with Oracle Database 12c release 2 (12.2), the default password-based authentication protocol configuration excludes the use of the case-insensitive 10G password version. By default, the SQLNET.ORA parameter SQLNET.ALLOWED_LOGON_VERSION_SERVER is set to 12, which is an Exclusive Mode.
For greater security, Oracle recommends that you leave case-sensitive password-based authentication enabled. This setting is the default. However, you can temporarily disable case-sensitive authentication during the upgrade to new Oracle Database releases. After the upgrade, you can then decide if you want to enable the case-sensitive password-based authentication feature as part of your implementation plan to manage your password versions.
Before upgrading, Oracle recommends that you determine if this change to the default password-based authentication protocol configuration affects you. Perform the following checks:
Identify if you have accounts that use only 10G case-insensitive password authentication versions.
Identify if you have Oracle Database 11g release 2 (18.104.22.168) database or earlier clients that have not applied critical patch update CPUOct2012, or a later patch update, and have any account that does not have the case-insensitive 10G password version.
Ensure that you do not have the deprecated parameter SEC_CASE_SENSITIVE_LOGON set to FALSE. Setting this parameter to FALSE prevents the use of the case-sensitive password versions (the 11G and 12C password versions) for authentication.
Step 3.16 Dependencies on Network Utility Packages
After executing preupgrade script, verify the preupgrade log
WARNING: --> Database contains schemas with objects dependent on network packages.
.... Refer to the Database Upgrade Guide for instructions to configure Network ACLs.
.... USER WKSYS has dependent objects.
.... USER SYSMAN has dependent objects.
.... USER FLOWS_010600 has dependent objects.
Execute the following query to view DBA_DEPENDIENCIES
SQL> SELECT * FROM DBA_DEPENDENCIES WHERE referenced_name IN
('UTL_TCP','UTL_SMTP','UTL_MAIL','UTL_HTTP','UTL_INADDR','DBMS_LDAP') AND owner NOT IN ('SYS','PUBLIC','ORDPLUGINS');
To ensure that the new access controls are part of your upgrade testing, prepare a post-upgrade script to make the scripts available in your database environment. After the upgrade, grant specific required privileges. Access is based on the usage in the original database.
Step 3.17 Running Upgrades with Read-Only Tablespaces.
Use the Parallel Upgrade Utility with the -T option to take schema-based tablespaces offline during upgrade. Oracle Database can read file headers created in earlier releases, so you are not required to do anything to them during the upgrade. The file headers of READ ONLY tablespaces are updated when they are changed to READ WRITE. If the upgrade suffers a catastrophic error, so that the upgrade is unable to bring the tablespaces back online, then review the upgrade log files. The log files contain the actual SQL statements required to make the tablespaces available. To bring the tablespaces back online, you must run the SQL statements in the log files for the database, or run the log files for each PDB.
Step 3.18 Disable all batch and cron jobs.
For jobs initiated by Oracle then packages DBMS_JOB, DBMS_SCHEDULER can be used.
For cron jobs (external jobs controlled at the OS level) then this is a task for your Unix administrator.
Note 404238.1 : How to Disable an Entry from DBMS_SCHEDULER
Note 1335741.1 : How To Stop A Running Job Using DBMS_JOB
Remove desupported initialization parameters and adjust deprecated initialization parameters. In new releases, some parameters are desupported, and other parameters are deprecated. Remove all desupported parameters from any parameter file that starts the new Oracle Database instance. Desupported parameters can cause errors in new Oracle Database releases.
The Pre-Upgrade Information Tool displays any deprecated parameters and desupported parameters it finds in the Deprecated Parameters and Desupported Parameters sections, respectively.
Step 3.20 Review and Remove any unnecessary hidden/underscore parameters
Please review and remove any unnecessary hidden/underscore parameters prior to upgrading. It is strongly recommended that these be removed before upgrade unless your application vendors and/or Oracle Support state differently. Changes will need to be made in the init.ora or spfile.To view existing hidden parameters execute the following command while connected AS SYSDBA
SQL> SELECT name, value from SYS.V$PARAMETER WHERE name LIKE '\_%' ESCAPE '\' order by name;
Step 4 Preparing the New Oracle Home for Upgrading
Copy configuration files from the Oracle home of the database being upgraded to the new release Oracle Database Oracle home.
If you have a password file that resides within the old environment Oracle home, then move or copy the password file to the new Oracle home. It is recommended to recreate the password file freshly to leverage the new features (if any) provided by orapwd utility.
Remove desupported initialization parameters and adjust deprecated initialization parameters. In new releases, some parameters are desupported, and other parameters are deprecated. Remove all desupported parameters from any parameter file that starts the new Oracle Database instance. Desupported parameters can cause errors in new Oracle Database releases. Also, alter any parameter whose syntax has changed in the new release.
If you are upgrading a cluster database, then you need to change CLUSTER_DATABASE=FALSE before starting upgrade
Step 5 Upgrade step
Start the instance by issuing the following command in SQL*Plus:
Startup CDB in ugprade mode
SQL> startup upgrade pfile=<pfile location>
Start all PDB in upgrade mode
SQL> alter pluggable database all open upgrade;
Exit SQL*Plus before proceeding to the next step.
Start the upgrade using the Parallel Upgrade Utility (catctl.pl, using the shell command dbupgrade), where -d specifies the location of the directory:
dbupgrade -d $ORACLE_HOME/rdbms/admin
catupgrade logs will be generated separately for Seed database and each PDB.
Note: The catuppst.sql script is run as part of the upgrade process unless the upgrade returns errors during the process. Check the log file for "BEGIN catuppst.sql" to verify that catuppst.sql ran during the upgrade process.
Review the upg_summary.log to confirm that the upgrade was successful, and if necessary, review other logs.
Step 6 Post-Upgrade steps
Recompile the invalid objects by executing the utlrp.sql against the container and all the pluggable databases.
Execute Post-Upgrade Status Tool, utlusts.sql and review the same. The script verifies that all issues are fixed.
For Oracle RAC environment, set the initialization parameter value for CLUSTER_DATABASE to TRUE, and Run srvctl command for Oracle Database 19c to upgrade the database. For example:
ALTER SYSTEM SET CLUSTER_DATABASE=TRUE SCOPE=SPFILE;
$ORACLE_HOME/bin/srvctl upgrade database -db name -o ORACLE_HOME
Recovery Catalog Upgrade
If you use a version of the recovery catalog schema that is older than that required by the RMAN client, then you must upgrade it. You can upgrade the Recovery catalog by executing the UPGRADE CATALOG command
Please refer to Oracle documentation under "Upgrading the Recovery Catalog" for complete information and steps
Upgrade the Time Zone File Version After Upgrading Oracle Database
If the Pre-Upgrade Information Tool instructed you to upgrade the time zone files after completing the database upgrade, then use the DBMS_DST PL/SQL package to update the RDBMS DST (timezone) version.
For latest DST patch refer to Note 412160.1.
The following scripts get delivered with Oracle Database 18c onward
Script to gives how much TIMESTAMP WITH TIME ZONE data there is in a database using stats info. No restart required.
Script to approximate how much TIMESTAMP WITH TIME ZONE data there is in a database using a COUNT(*) for each table that has a TSTZ column. This script is useful when using DBMS_DST package or the scripts of utlz_upg_check.sql and utlz_upg_apply.sql scripts.
Time zone upgrade check script
Time zone apply script. Warning: This script will restart the database and adjust time zone data.