Oracle 19c - Complete checklist for Manual Upgrade for upgrading Oracle 12.x, 18c Container database (CDB) to Oracle 19c (19.x) (Doc ID 2549866.1)
|
In this Document
APPLIES TO:Oracle Database - Enterprise Edition - Version 12.1.0.2 to 19.3.0.0.0 [Release 12.1 to 18]Information in this document applies to any platform. PURPOSEThe purpose of this article is to perform upgrade of 12c, 18c container databases (CDB with one or more pluggable databases) using Manual upgrade method to Oracle 19c release. SCOPEDBA, Support DETAILSAbout Manual UpgradeUpgrade Path / Compatibility Matrix for 19c Oracle Container Database.Minimum version of the database that can be directly upgraded to Oracle 19c.
Manual Upgrade can be used to upgrade Oracle 12.1.0.2 or higher container (CDB) databases to 19c release.
Step 1: Requirements and Recommendations for source CDB / PDB database.
Doc Id 556610.1 - Script to Collect DB Upgrade/Migrate Diagnostic Information (dbupgdiag.sql) dbupgdiag.sql script is a set of sql statements intended to provide a user friendly output to diagnose the status of the database either before (or) after upgrade. The script will create a output file called db_upg_diag_<sid>_<timestamp>.log
Doc ID 1406586.1 - How to Handle Materialized Views When You Upgrade or Clone a Database
Step 2: Requirements and Recommendations for Target database
Doc ID 2118136.2 - Assistant: Download Reference for Oracle Database/GI Update, Revision, PSU, SPU(CPU), Bundle Patches, Patchsets and Base Releases
Doc ID 2539751.1 - Patches to apply before upgrading Oracle GI and DB to 19c
Step 3 PreupgradeStep 3.1 Preupgrade script execution$Earlier_release_Oracle_home/jdk/bin/java -jar $New_release_Oracle_home/rdbms/admin/preupgrade.jar [FILE|TERMINAL] [TEXT|XML] [DIR output_dir]
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/12.2.0.1.0 target Oracle Home : /refresh/app/oracle/product/19.0.0.0
For example, $ export ORACLE_HOME=/refresh/app/oracle/product/12.2.0.1.0 Execute fixup scripts across the entire CDB: Before upgrade: 1. Execute preupgrade fixups with the below command 2. Review logs under /refresh/app/oracle/cfgtoollogs/cdb12201/preupgrade/ After the upgrade: 1. Execute postupgrade fixups with the below command 2. Review logs under /refresh/app/oracle/cfgtoollogs/cdb12201/preupgrade/. The preupgrade.jar has generated fixup scripts for CDB$ROOT,PDB$SEED and pluggable databases. you may find below scripts in /u01/app/oracle/cfgtoollogs/cdb12102/preupgrade/*.sql.
Examine the preupgrade.log file and follow the recommendation. Execute the preupgrade_fixups_<pdb_name>.sql against all or respective pluggable database. The below command will run the preupgrade_fixups.sql against all the pluggable databases. $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 2 -e -b preupgrade_fixups /refresh/app/oracle/cfgtoollogs/cdb12201/preupgradepreupgrade_fixups.sql
To run the preupgrade fixups script against a particular pluggable database, below command can be used: $ORACLE_HOME/perl/bin/perl catcon.pl -c 'CDB$ROOT' -n 2 -e -b /preupgrade_fixups_cdbroot /refresh/app/oracle/cfgtoollogs/cdb12201/preupgrade/preupgrade_fixups_CDB_ROOT.sql
Step 3.2 Check for Invalid Objects / Components.Verify the below queries against the database and all the pluggable databases. set pagesize 500 select substr(object_name,1,40) object_name,substr(owner,1,15) owner,object_type from dba_objects where status='INVALID' order by owner,object_type; select owner,object_type,count(*) from dba_objects where status='INVALID' group by owner,object_type order by owner,object_type ; If you find invalid objects and/or database components then try to VALIDATE the invalid objects and/or database components by executing the following steps: Run $ORACLE_HOME/rdbms/admin/utlrp.sql to validate the invalid objects in the database. You can execute the utlrp.sql scripts multiple times to validate the invalid objects. From source Oracle Home, execute the utlrp.sql against the container and all the pluggable databases. cd $ORACLE_HOME/rdbms/admin/ $ 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 FROM dba_users 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 DowntimeOracle 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: $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -l /tmp -b gatherstats -- --x"exec dbms_stats.gather_dictionary_stats"
Above command gather dictionary statistics for all PDBs in a container database. you can use the below command to gather statistics against a particular PDB.
For pdb_1 pluggable database : $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -l /tmp -c 'PDB_1' -b pdb_1_gatherstats -- --x"exec dbms_stats.gather_dictionary_stats" for cdb$root container: $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -l /tmp -c 'CDB$ROOT' -b root_gatherstats -- --x"exec dbms_stats.gather_dictionary_stats"
Step 3.6 Verifying Materialized View Refreshes are Complete Before UpgradeUse 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 DatatypeThe 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 upgradebelow command can be used to purge from all the pdbs: $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -l /tmp -b purge_recyclebin -- --x"PURGE DBA_RECYCLEBIN" or SQL> PURGE DBA_RECYCLEBIN; Step 3.10 Resolve Outstanding Distributed Transactions Before UpgradingTo resolve outstanding distributed transactions: 1. Issue the following statement: SQL> Select * from dba_2pc_pending;
2. If the query in the previous step returns any rows, then issue the following statements: SQL> select local_tran_id FROM dba_2pc_pending; SQL> execute dbms_transaction.purge_lost_db_entry(''); SQL> commit;
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. 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 AccountsBefore 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. Step 3.14 Copying Transparent Encryption Oracle WalletsIf 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. Open the Oracle wallet in mount. For example: SQL> STARTUP MOUNT; Step 3.15 Understanding Password Case Sensitivity and SEC_CASE_SENSITIVE_LOGON parameterStarting 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:
Step 3.16 Dependencies on Network Utility PackagesAfter executing preupgrade script, verify the preupgrade log WARNING: --> Database contains schemas with objects dependent on network packages. 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. See also: Note 404238.1 : How to Disable an Entry from DBMS_SCHEDULER Note 1335741.1 : How To Stop A Running Job Using DBMS_JOB Note 67695.1 : PROCEDURE DBMS_JOB.BROKEN Specification
Step 3.19 Deprecated and Desupported ParametersRemove 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 parametersPlease 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
Step 5 Upgrade stepStart 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. SQL> exit;
Start the upgrade using the Parallel Upgrade Utility (catctl.pl, using the shell command dbupgrade), where -d specifies the location of the directory: cd $ORACLE_HOME/rdbms/admin 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 stepsRecompile the invalid objects by executing the utlrp.sql against the container and all the pluggable databases. cd $ORACLE_HOME/rdbms/admin/ Modify the corresponding entry in /etc/oratab file to point to the Oracle 19c ORACLE_HOME. then execute the postupgrade_fixups.sql script generated. Below is the command: $ORACLE_HOME/perl/bin/perl -I$ORACLE_HOME/perl/lib -I$ORACLE_HOME/rdbms/admin $ORACLE_HOME/rdbms/admin/catcon.pl -l /refresh/app/oracle/cfgtoollogs/cdb12201/preupgrade/ -b postup_cdb12201 /refresh/app/oracle/cfgtoollogs/cdb12201/preupgrade/postupgrade_fixups.sql Execute Post-Upgrade Status Tool, utlusts.sql and review the same. The script verifies that all issues are fixed. SQL> @$ORACLE_HOME/rdbms/admin/utlusts.sql
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
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 $ORACLE_HOME/rdbms/admin/utltz_countstats.sql $ORACLE_HOME/rdbms/admin/utltz_countstar.sql $ORACLE_HOME/rdbms/admin/utltz_upg_check.sql ?/rdbms/admin/utltz_upg_apply.sql |