Post-Downgrade Tasks for Oracle Database Downgrades

After you downgrade your Oracle Database release, you can be required to complete additional tasks, due to changes that affect compatibility, components, and supported protocols.

Parent topic: Downgrading Oracle Database to an Earlier Release

Reapply Release Update and Other Patches After Downgrade

After the downgrade is run, and catrelod.sql completes successfully, if you installed new patches in your original Oracle home after the upgrade, but before the downgrade, then ensure that you apply any patches that you installed.

If you installed new patches, then run the datapatch tool to apply those patches to the downgraded database. If you did not change the binaries and files in your Oracle Home after the upgrade, then there is no need to run datapatch after running catrelod.sql. However, if you are in any doubt about whether new patches are installed, then run datapatch. There is no safety concern that prevents you from running datapatch as many times as you require to be certain that patches are applied to the database.

Parent topic: Post-Downgrade Tasks for Oracle Database Downgrades

Reenabling Oracle RAC After Downgrading Oracle Database

After the downgrade, you can re-enable Oracle Real Application Clusters (Oracle RAC).

For downgrades with Oracle RAC databases, where you set CLUSTER_DATABASE=FALSE for the downgrade, you can now set CLUSTER_DATABASE=TRUE again and start with all instances in the RAC cluster.

Parent topic: Post-Downgrade Tasks for Oracle Database Downgrades

Reenabling Oracle Database Vault after Downgrading Oracle Database

You must do this if you are instructed during the downgrade to disable Oracle Database Vault.

If you use Oracle Database Vault, then you may have been instructed to disable it before downgrading your databaseTo use Oracle Database Vault after downgrading, you must register it to reenable it.

Related Topics

Parent topic: Post-Downgrade Tasks for Oracle Database Downgrades

Downgrade the Oracle Grid Infrastructure Configuration for the Database

After a downgrade. you must change the Oracle Grid Infrastructure registration to start the database release in the earlier release Oracle Database home.

Later release Oracle Grid Infrastructure can run earlier Oracle Database releases. Oracle does not recommend that you downgrade Oracle Grid Infrastructure, unless you encounter an issue related specifically to Oracle Grid Infrastructure. However, after you downgrade your Oracle Databaes release, you must use the command srvctl downgrade database to configure Oracle Clusterware to start the database in the earlier release Oracle home.

To configure Oracle Clusterware to start the database in the earlier release Oracle home, use the following command syntax, where $LATER_RELEASE_ORACLE_HOME is the path (or environment variable) to the later release Oracle home, old_release_oracle_home is the path to the earlier release Oracle home, and old-release is the earlier release Oracle Database release: 

$LATER_RELEASE_ORACLE_HOME/bin/srvctl downgrade database -d $ORACLE_UNQNAME -o old_release_oracle_home -t old-release

For example:

$ORACLE_HOME/bin/srvctl downgrade database -d $ORACLE_UNQNAME -o /u02/app/oracle/product/19/db_1/ -t 19.0.0

Parent topic: Post-Downgrade Tasks for Oracle Database Downgrades

Restoring Oracle Enterprise Manager after Downgrading Oracle Database

The restore task described in this section is required only if you are performing a downgrade, and Oracle Enterprise Manager is configured on the host.

To restore Oracle Enterprise Manager, you first run Oracle Enterprise Manager configuration assistant (EMCA), and then you run the emdwgrd utility.

Parent topic: Post-Downgrade Tasks for Oracle Database Downgrades

Requirements for Restoring Oracle Enterprise Manager After Downgrading

You must complete these requirements before you upgrade to be able to restore Oracle Enterprise Manager after a downgrade to a release earlier than 12.1

The following must be true to use emca -restore to restore Oracle Enterprise Manager to its previous state:

  • Before the upgrade, you saved a backup of your Oracle Enterprise Manager configuration files and data

  • You run the emca binary located in the new Oracle Database release home for this procedure

On Oracle Clusterware systems, to restore Oracle Enterprise Manager on an Oracle RAC database, you must have the database registered using srvctl before you run emca -restore. You must run emca -restore from the ORACLE_HOME/bin directory of the earlier Oracle Database release to which the database is being downgraded.

Run the emca -restore command with the appropriate options to restore Oracle Enterprise Manager Database Control or Grid Control to the old Oracle home.

Specify different emca options, depending on whether the database you want to downgrade is a single-instance database, an Oracle RAC database, or an Oracle ASM database.

Related Topics

Parent topic: Restoring Oracle Enterprise Manager after Downgrading Oracle Database

Running EMCA to Restore Oracle Enterprise Manager After Downgrading

Review these topics and select your restoration scenario to restore Oracle Enterprise Manager after a downgrade.

Parent topic: Restoring Oracle Enterprise Manager after Downgrading Oracle Database

Running emca on a Single-Instance Oracle Database Without Oracle ASM

Use Enterprise Manager Configuration Assistant (emca) to manage your database.

Use this command to run Enterprise Manager Configuration Assistant. 

ORACLE_HOME/bin/emca -restore db

You are prompted to enter the following information:

  • Oracle home for the database that you want to restore

  • Database SID

  • Listener port number

Running EMCA on an Oracle RAC Database Without Oracle ASM

Use Enterprise Manager Configuration Assistant (emca) to manage your database:

Use this procedure to run Enterprise Manager Configuration Assistant:

ORACLE_HOME/bin/emca -restore db -cluster

You are prompted to enter the following information:

  • Oracle home for the database that you want to restore

  • Database unique name

  • Listener port number

Running EMCA on a Single-Instance Oracle ASM Instance

Use Enterprise Manager Configuration Assistant (emca) to manage your database and storage.

Use this command to run Enterprise Manager Configuration Assistant. 

ORACLE_HOME/bin/emca -restore asm

You are prompted to enter the following information:

  • Oracle home for the database that you want to restore

  • Oracle ASM port

  • Oracle ASM SID

Running emca on an Oracle ASM on Oracle RAC Instance

Use Enterprise Manager Configuration Assistant (emca) to manage your database and storage.

Use this command to run Enterprise Manager Configuration Assistant. 

ORACLE_HOME/bin/emca -restore asm -cluster

You are prompted to enter the following information:

  • Oracle home for the database that you want to restore

  • Oracle ASM port

Running emca on a Single-Instance Oracle Database With Oracle ASM

Use Enterprise Manager Configuration Assistant (emca) to manage your database and storage.

Use this command to run Enterprise Manager Configuration Assistant. 

ORACLE_HOME/bin/emca -restore db_asm

You are prompted to enter the following information:

  • Oracle home for the Oracle Database that you want to restore

  • Database SID

  • Listener port number

  • Oracle ASM port

  • Oracle ASM home

  • Oracle ASM SID [+ASM]

Running emca on an Oracle RAC Database and Oracle ASM Instance

Use Enterprise Manager Configuration Assistant (emca) to manage your database and storage.

Use this command to run Enterprise Manager Configuration Assistant:

ORACLE_HOME/bin/emca -restore db_asm -cluster

You are prompted to enter the following information:

  • Oracle home for the database that you want to restore

  • Database unique name

  • Listener port number

  • Oracle ASM port

  • Oracle ASM Oracle home

  • Oracle ASM SID [+ASM]

The output of emca varies according to the options that you specify and the values that you enter at the prompts. In Oracle RAC environments, you must repeat this step on all Oracle RAC cluster member nodes.

You must now run the emdwgrd utility to restore Oracle Enterprise Manager Database Control and data.

Running the emdwgrd utility to restore Enterprise Manager Database Control

You can restore the Oracle Enterprise Manager Database Control and data by using the emdwgrd utility after you run emca -restore.

To use emdwgrd, you must do the following:

  • Set ORACLE_HOME and other environment variables to point to the Oracle home from which the upgrade originally took place.

  • Run the emdwgrd utility from the new release Oracle Database Oracle home.

The following procedure is for Linux and Unix. To run it on Windows, substitute emdwgrd.bat for emdwgrd.

  1. Set ORACLE_HOME to the Oracle home from which the database upgrade originally took place.

  2. Set ORACLE_SID to the SID of the database that was upgraded and then downgraded.

  3. Set PATH, LD_LIBRARY_PATH and SHLIB_PATH to point to the Oracle home from which the database upgrade originally took place.

  4. Go to the new Oracle Database release Oracle home:

    cd $ORACLE_HOME/bin

  5. Run emdwgrd using one of the following procedures:

    1. For a single-instance database, run the following command, where SID is the SID of the database that was upgraded and then downgraded and save_directory is the path to the storage location you chose when saving your database control files and data: 

      emdwgrd -restore -sid SID -path save_directory -tempTablespace TEMP

    2. For an Oracle RAC database, remote copy is required across the cluster nodes. Define an environment variable to indicate which remote copy is configured. For example:

      setenv EM_REMCP /usr/bin/scp

      Then, run emdwgrd —restore with the following options: 

      emdwgrd -restore -tempTablespace TEMP -cluster -sid SID_OldHome -path save_directory

      If the Oracle home is on a shared device, then add -shared to the emdwgrd command options.

  6. Enter the SYS and SYSMAN passwords when prompted by emdwgrd.

  7. When emdwgrd completes, Oracle Enterprise Manager Database Control is downgraded to the old Oracle home.

Parent topic: Restoring Oracle Enterprise Manager after Downgrading Oracle Database

Restoring Oracle APEX to the Earlier Release

After a downgrade, if you upgraded Oracle APEX (formerly Oracle Application Express) at the same time as you upgraded Oracle Database, then you must complete steps to revert to the earlier release.

To complete the downgrade of Oracle APEX after a database downgrade, complete all the steps listed in Oracle APEX Installation Guide to revert your Oracle APEX release to the earlier release. The steps to revert are different, depending on whether your architecture is a Non-CDB or a multitenant architecture (CDB) Oracle Database.

Note:

You only need to complete these steps if you upgraded Oracle APEX at the same time that you upgraded the database.

Related Topics

Parent topic: Post-Downgrade Tasks for Oracle Database Downgrades

Gathering Dictionary Statistics After Downgrading

To help to assure good performance after you downgrade, use this procedure to gather dictionary statistics.

Oracle recommends that you gather dictionary statistics after downgrading the database, so that the statistics are collected for the downgraded release Data Dictionary tables.

Note:

After a downgrade process, be aware that the the data dictionary can have changes that persist in the downgraded dictionary. These changes are insignificant. The downgraded data dictionary is functionally equivalent to an earlier release data dictionary.

  • Non-CDB Oracle Database: Oracle recommends that you use the DBMS_STATS.GATHER_DICTIONARY_STATS procedure to gather these statistics. For example, enter the following SQL statement: 

    SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

  • CDB (multitenant architecture) Oracle Database: Oracle recommends that you use catcon to gather Data Dictionary statistics across the entire multitenant architecture.

    To gather dictionary statistics for all PDBs in a container database, use the following syntax:

    $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -l /tmp -b gatherstats -- --x"exec dbms_stats.gather_dictionary_stats"

    To gather dictionary statistics on a particular PDB, use syntax similar to the following:

    $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -l /tmp -c
'SALES1' -b gatherstats -- --x"exec dbms_stats.gather_dictionary_stats"

    In the preceding example the -c SALES1 option specifies a PDB inclusion list for the command that you run, specifying the database named SALES1. The option -b gatherstatsspecifies the base name for the logs. The option --x specifies the SQL command that you want to execute. The SQL command itself is inside the quotation marks.

Related Topics

Parent topic: Post-Downgrade Tasks for Oracle Database Downgrades

Regathering Fixed Object Statistics After Downgrading

After the downgrade, run representative workloads on Oracle Database, and regather fixed object statistics.

Fixed objects are the X$ tables and their indexes. V$ performance views are defined through X$ tables. After you downgrade, regather fixed object statistics to ensure that the optimizer for the restored database can generate good execution plans. These execution plans can improve database performance. Failing to obtain representative statistics can lead to suboptimal execution plans, which can cause performance problems

Gather fixed objects statistics by using the DBMS_STATS.GATHER_FIXED_OBJECTS_STATS PL/SQL procedure. DBMS_STATS.GATHER_FIXED_OBJECTS_STATS also displays recommendations for removing all hidden or underscore parameters and events from init.ora and SPFILE.

To gather statistics for fixed objects, run the following PL/SQL procedure:

SQL> execute dbms_stats.gather_fixed_objects_stats;

See Also:

Oracle Database PL/SQL Packages and Types Reference for more information about using the GATHER_FIXED_OBJECTS_STATS procedure

Parent topic: Post-Downgrade Tasks for Oracle Database Downgrades

Regathering Stale CBO Statistics After Downgrade

Oracle recommends that you regather Oracle Cost-Based Optimizer (CBO) statistics after completing an Oracle Database downgrade.

When you upgrade Oracle Database and gather new CBO statistics, the upgraded database has new database statistics. The upgraded database also can include new histogram types. For this reason, when you downgrade the database, the statistics that you collected for the new release can be different from the previous release. This issue is applicable both to data dictionary tables, and to regular user tables.

Regather stale statistics either by using GATHER_DATABASE_STATS, or by using gather commands that you typically use to update stale statistics in the dictionary and application schemas.

For example, after a downgrade:

  • Non-CDB Oracle Database: To regather statistics, Oracle recommends that you use the GATHER_DATABASE_STATS procedure, with the option 'GATHER STALE'. For example: 

    SQL> execute dbms_stats.gather_database_stats(options=>'GATHER STALE');

  • CDB (multitenant architecture) Oracle Database: to regather Data Dictionary statistics across the entire multitenant architecture, Oracle recommends that you use catcon.

    To regather stale dictionary statistics for all PDBs in a container database, use the following syntax:

    $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -l /tmp -b gatherstats -- --x"exec dbms_stats.gather_database_stats(options=>'GATHER STALE')"

    To gather dictionary statistics on a particular PDB, use syntax similar to the following:

    $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -l /tmp -c
'SALES1' -b gatherstats -- --x"exec dbms_stats.gather_database_stats(options=>'GATHER STALE')"

    In the preceding example, the -c SALES1 option specifies a PDB inclusion list for the command that you run, specifying the database named SALES1. The option -b gatherstatsspecifies the base name for the logs. The option --x specifies the SQL command that you want to execute. The SQL command itself is inside the quotation marks.

Related Topics

Parent topic: Post-Downgrade Tasks for Oracle Database Downgrades

Checking Validity of Registry Components After Downgrade

Check the validity of registry components and identify any invalid components.

After the downgrade, check the state of the components in the database. If you downgrade a complete CDB, then you can use the CDB_REGISTRY view. You can also check an individual PDB using DBA_REGISTRY. Ensure that all components are either VALID or OPTION OFF.

Example 10-1 Check Registry on a CDB with CDB_REGISTRY View


set line 200
set pages 1000
col COMP_ID format a8
col COMP_NAME format a34
col SCHEMA format a12
col STATUS format a10
col VERSION format a12
col CON_ID format 99

select CON_ID, COMP_ID, comp_name, schema, status, version from CDB_REGISTRY order by 1,2;

Example 10-2 Check Registry on a PDB with DBA_REGISTRY View


set line 200
set pages 1000
col COMP_ID format a8
col COMP_NAME format a34
col SCHEMA format a12
col STATUS format a10
col VERSION format a12

select COMP_ID, comp_name, schema, status, version from DBA_REGISTRY order by 1;

Related Topics

Parent topic: Post-Downgrade Tasks for Oracle Database Downgrades