How to downgrade an Oracle database from 11.2 to a previous version

How to downgrade an Oracle database from 11.2 to a previous version

This document can be used to downgrade the upgraded database from Oracle 11gR2 to the following guidelines and checklists: Oracle 10gR1, Oracle 10gR2, and Oracle 11gR1. This document also applies to downgrading from 11.2.0.4 to 11.2.0.1

Note that when you downgrade a database instance from the current version to a previous version, the database will not return to the same status as before the upgrade. The upgrade process may cause some irreversible changes, depending on the version involved. The downgrade process allows you to open and Access database instances in previous versions. This is usually enough.

In addition, some corrective actions (such as uninstalling/reinstalling or upgrading to the current patch Assembly level) may be required after the downgrade to solve the residual issues.

If the target is to completely return the instance to its pre-upgrade status, other processes, including restoring the instance to the pre-upgrade status, will be used.

The process discussed in this article is script-based downgrade. This article does not discuss how to migrate data from one version to another using import/export, data pump, or other methods.

Before starting the downgrade process, you should have or have installed the Oracle binary file to be downgraded to the version on the server. If you have uninstalled the Oracle executable file that you want to downgrade to a version, reinstall the Oracle binary file to the correct version/patch level for downgrade.

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian

Note:

1. This process is used to downgrade the database that has been successfully upgraded to 11gR2. This process cannot be used to return the database that failed the upgrade.
2. You can only downgrade to the version and patch level from which you upgrade. For example, if you upgraded from Oracle 10gR1 (10.1.0.5) to Oracle 11gR2 (11.2), you cannot downgrade to Oracle 10gR2 (10.2 ). You can only downgrade to Oracle 10gR1 (10.1.0.5)
3. Downgrading to 9iR2 is supported. This is because the compatibility parameter is set to the minimum 10.1.0 during the upgrade process. This prevents degradation.
4. If the original database has been patched, You need to roll back the patch before downgrading. To uninstall and roll back a patch, you can find it in readme of the patch. Uninstalling or rolling back a patch will cause the downgrade process to fail and the data dictionary object to take effect.

Example: For Exadata Bundle Patch, the process is:

Uninstall patch

Example: $ opatch auto/u01/app/oracle/patches/14103267-rollback

Roll back SQL changes

Example: SQL> @ rdbms/admin/catbundle_EXA _ <database SID> _ ROLLBACK. SQL rollback SQL change.

 

Steps before downgrade:

1: Verify that all components and dictionary objects are valid for 11gR2 and the version is correct.

Set pagesize500
Set linesize 100

Select substr (comp_name,) comp_name, status, substr (version,) version from dba_registry order by comp_name;

Select substr (object_name,) object_name, substr (owner,) 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 any component or the object provided by Oracle is invalid, run utlrp. SQL to verify it again. This script may need to run multiple times to verify all objects.

$ Cd $ ORACLE_HOME/rdbms/admin
$ Sqlplus "/as sysdba"
SQL> @ utlrp. SQL

After completion, run the preceding query again to verify that all invalid objects are valid.

2: If you have enabled Oracle Database Vault on the Database, you must:

A: grant the Database Vault DV_PATCH_ADMIN role to the SYS account.
B: Disable Database Vault before downgrading the Database.

Disable the Oracle Database Vault trigger:
SQL> CONNECT DVSYS/DVSYS
SQL> ALTER TRIGGER DV_BEFORE_DDL_TRG DISABLE;
SQL> ALTER TRIGGER DV_AFTER_DDL_TRG DISABLE;

3: If the operating system parameter ORA_TZFILE is set, cancel the setting. If you do not cancel setting the ORA_TZFILE variable, the following error may occur when you connect to the database:

SP2-1503: Unable to initialize Oracle call interface
SP2-0152: ORACLE may not be functioning properly

4: If you have run the DBMS_DST package to upgrade the time zone version to the new version before downgrading to the previous database version (for example, upgrade to version 11 during the upgrade step of 11gR2 ), you must install the time zone file of version 11 to the $ ORACLE_HOME/oracore/zoneinfo directory of the old Oracle database version.

5: Check the database compatibility level to determine whether the database may have any incompatibility to prevent degradation. The maximum value of compatible that you have set is the lowest version that you can downgrade. For example:

If the database compatibility level is up to date (for example, 11.2.0.4), the database cannot be downgraded.

If the database compatibility level used to be 11.2.0 or higher, you cannot downgrade the database to a version earlier than 11.2.0.1.
To downgrade the database to 11gR1, the COMPATIBLE initialization parameter must never be higher than 11.1.0.
To downgrade the database to 10gR2, the COMPATIBLE initialization parameter must never be higher than 10.2.0.
To downgrade the database to 10gR1, the COMPATIBLE initialization parameter must never be higher than 10.1.0.

6: If Oracle Application Express is installed on the database, you must set apxrelevels. copy the SQL file from the Oracle 11gR2 (11.2) ORACLE_HOME/apex/directory to a directory outside the Oracle Home Directory, such as a temporary directory on the system. Write down the new location of the file.

7. Back up the 11gR2 database before the downgrade.

If you want to downgrade the database from 11.2.0.4 to 11.2.0.2, an error occurs when you use the 11.2.0.2 catrelevels. SQL script.
For version 11.2.0.2 to download and apply patch 11811073, it provides the latest version of catrelevels. SQL.

1: log on to the system as the owner of the Oracle Database 11g Release 2 (11.2) Oracle Home Directory.

2: If you have configured and run Enterprise Manager Database Control for the Database, stop Database Control as follows:

A: Set the ORACLE_UNQNAME environment variable to the unique name of the database.
B: run the following command: RACLE_HOME/bin/emctl stop dbconsole

3: If the database to be downgraded is an Oracle Real Application Clusters (Oracle RAC) database, perform this step on all instances.

A: If you want to downgrade the Oracle RAC database to 10g Release 1 (10.1), you must remove the additional voting disk before closing the Oracle Clusterware stack.
To view the number of voting disks used and list the dial paths, run the following command:Oracle_Clusterware_Home/bin/crsctl query css votedisk

B: run the following command to remove each extra voting disk. The path is the voting disk path found in the previous step:

Oracle_Clusterware_Home/bin/crsctl delete css votedisk path

Note: to downgrade the cluster database, you must completely disable the instance and set the ter_database initialization parameter to false. After downgrade, you must set this parameter back to true.

SET CLUSTER_DATABASE = FALSE

Note steps 38 After Upgrade

4: Go to the ORACLE_HOME/rdbms/admin directory at the system prompt.

5: start SQL * Plus and connect to the database instance as a user with SYSDBA permissions.

6: If the instance is currently running, disable it.
SQL> SHUTDOWN IMMEDIATE 

7: Start the instance in DOWNGRADE mode.
SQL> STARTUP DOWNGRADE 
If necessary, specify the location of the initialization parameter file.

8a: If Enterprise Manager is configured in the Database and the Enterprise Manager Database Control Data is backed up before the upgrade, then:

Drop the SYSMAN schema:
Drop user sysman CASCADE;

Note: After this step, the MGMT * synonym may be invalid. Follow the steps described in "Restoring Oracle Enterprise Manager" in "Post-downgrade" in "11.2 Upgrade Guide" (Chapter 6, page 9th, Downgrading Oracle Database to an Earlier Release ), restore the backup and verify the synonym.

8b: If Enterprise Manager is configured in the Database before the upgrade, but the Enterprise Manager Database Control Data is not backed up, then:

Delete An Enterprise Manager User:
@? /Sysman/admin/emdrep/SQL/core/latest/admin/admin_drop_users. SQL SYSMAN:
DEFINE EM_REPOS_USER = SYSMAN @? /Sysman/admin/emdrep/SQL/core/latest/admin/admin_drop_repos_user. SQL SYSMAN Delete roles and synonyms:
DEFINE EM_REPOS_USER = SYSMAN @? /Sysman/admin/emdrep/SQL/core/latest/admin/admin_drop_synonyms. SQL Drop the SYSMAN schema:
Drop user sysman CASCADE;

Note: After this step, the Enterprise Manager component will be removed from the database instance. The Enterprise Manager component can be re-created after being downgraded. For more information, see

9: set the system to record the results to the log file for later verification:
SQL> SPOOL downgrade. log

10: Run catdwgrd. SQL:
SQL> @ catdwgrd. SQL

Note:
This script will downgrade all Oracle Database components in the Database to the master version or Oracle Database 11g patch version that was initially upgraded from it.

If you encounter any problems when running the script or any scripts in other steps, you need to locate the cause and correct the problem, and then re-run the script. You can re-run any script described in this chapter multiple times as needed.

If a downgrade of a component fails, a ORA-39709 error is displayed, the SQL * Plus session ends, and the Oracle Database data dictionary is not downgraded. Before downgrading the Oracle Database data dictionary, all components must be successfully downgraded. You must identify and correct the problem before re-running the catdwgrd. SQL script.

For more details, please continue to read the highlights on the next page:

1 2 Next Page