Oracle 12c - Downgrade Oracle Database 12c to 11g

Downgrade Oracle Database 12c Release 1 (12.1) To Previous Versions

This document is created for use as a guideline and checklist for downgrading a previously upgraded database from Oracle 12c back to the previous release: 11.2.0.3, 11.2.0.2. 11.1.0.7

An important note is that when downgrading a database instance from the current version to the version prior to the upgrade, the database IS NOT returned to the same state as it was pre-upgrade.  Depending on the versions involved, the upgrade process makes changes that are not reversible. The downgrade process allows users to open and access the database instance in the previous version. This is usually sufficient.
Additional corrective actions (such as de-install / re-install or re-upgrade to current patchset levels) may be needed to settle left-over issues after a downgrade. If the goal is to have the instance back EXACTLY as it was pre-upgrade then other processes including a recovery to just before the upgrade should be used.

The process discussed in this article is a script based downgrade.  This article does not discuss the use of Export/Import, Data Pump or other methods to move data from one version to another.

Oracle binaries of the release/version to which you are downgrading should be available/installed on the server before you start the downgrade process.   If you have uninstalled the Oracle executable's to which you want to downgrade, please re-install the oracle binaries to the correct version/patch level for the downgrade.

This process is intended to downgrade a database that was successfully and upgraded to 12c.  It is not intended to back out from a failed upgrade. You can only downgrade to the release and patch level you upgraded from.

Direct upgrade is possible for release 10.2.0.5, 11.1.0.7, or release 11.2.0.2 or higher. Downgrade is possible to these versions except 10.2.0.5.

For example, if you upgraded from Oracle 11.1.0.6 to Oracle 12c (12.1.0) by applying intermediate patch 11.1.0.7, then you can not downgrade to Oracle 11.1.0.6.   Downgrade can be done only to Direct upgrade version

Exception:

Even though direct upgrade is possible for 10.2.0.5, downgrade is not applicable to 10.2.0.5.

This is because in the upgrade process the compatible parameter is set to a minimum 11.0.0.  This prevents downgrade to 10.2.0.5.  The possible downgrade versions are 11.1.0.7, 11.2.0.2, 11.2.0.3 or higher

If there are any patches applied on the source database running from the upgraded home, they need to be rolled back before beginning the downgrade process.
The steps to un-install and roll back patches are documented in the readme for the patch in question.
Failure to un-install and roll back patches can result in a failure to downgrade including dictionary objects that cannot be re-validated.

Example for an Exadata Bundle Patch, the procedure is to:

Un-install the patch
                 Example:   $ opatch auto /u01/app/oracle/patches/14103267 -rollback
Roll back any SQL applied as part of the patch application:    
                 Example:   SQL> @rdbms/admin/catbundle_EXA_<database SID>_ROLLBACK.sql to rollback SQL changes.

SOLUTION

Pre-downgrade steps

• - XML db component is mandatory in 12c.
  During an upgrade to 12c, XML db component would have been installed if it was not present.
  Downgrade from 12c will remove installed XDB component
• - Downgrade is not supported for Enterprise Manager. Before the downgrade reconfigure Oracle EM controls. Refer to
  
  Oracle Database Upgrade Guide 12c Release 1 (12.1) E17642-10
  6 Downgrading Oracle Database to an Earlier Release
  6.6.5 Restoring Oracle Enterprise Manager after Downgrading Oracle Database
  
  - During upgrade to 12c, the database control repository is removed. After downgrade reconfigure DB control.      
            
  Note 870877.1 How To Save Oracle Enterprise Manager Database Control Data Before Upgrading The Single Instance Database To Other Release ?         
  Note 876353.1 How To Restore The Oracle Enterprise Manager Data To Downgrade The Single Instance Database To Previous/Source Release ?
   
• - Compatible parameter should not have changed to 12.1.0.
• - Disable data vault if it is enabled.
  
  
  Note 803948.1  How To Uninstall Or Reinstall Database Vault in 11g (UNIX)
  Note 453902.1 Enabling and Disabling Oracle Database Vault in WINDOWS
   
• - If your database uses Oracle Label Security, then run the Oracle Label Security (OLS) preprocess downgrade olspredowngrade.sql script (available at $ORACLE_HOME/rdbms/admin) in the new Oracle Database 12c Oracle home.
• - Timezone version should be same.
• - Unset ORA_TZFILE if it is set and points to 12c home.
• - If you have Oracle Application Express on your database, then you must copy the apxrelod.sql file from the Oracle Database 12c $ORACLE_HOME/apex/ directory to a directory outside of the Oracle home, such as the temporary directory on your system to execute it later
• - If you have created objects based on fixed objects, then drop these objects to avoid possible ORA-00600 errors. You can re-create these objects after the downgrade.
• - If you are downgrading a cluster database, then shut down the instance completely and change the CLUSTER_DATABASE initialization parameter to FALSE.
  After the downgrade, you must set this parameter back to TRUE.

Once the above prerequisites have been met the downgrade can be proceed.

Downgrade steps for the database

1) Check all the db components are valid. Downgrade is only possible from a successfully upgraded database. To verify db component status, kindly execute below query

Connect as SYS user to the database 

col comp_id format a10
col comp_name format a30
col version format a10
col status format a8
select substr(comp_id,1,15) comp_id,substr(comp_name,1,30) comp_name,substr(version,1,10) version,status from dba_registry

2) Verify there are no sys/system invalid objects

select owner, count(object_name) "Invalid object count" from dba_objects where status!='VALID' and owner in ('SYS','SYSTEM') group by owner;

If count is zero, it is ok to proceed with downgrade.

If there are invalid objects execute utlrp.sql multiple times, if the objects cannot be resolved into a valid state then you cannot proceed with the downgrade.  Open an SR or post a thread to the DBA community for assistance.

Alternatively for 1 and 2 run the script in:

Note 556610.1  Script to Collect DB Upgrade/Migrate Diagnostic Information (dbupgdiag.sql)

3) Shutdown the database

Shutdown immediate

4)  Take a backup of 12c database

5)  Start the database in downgrade mode

Startup downgrade;

6)  Execute downgrade scripts

Sql> Spool downgrade.log
Sql> @$ORACLE_HOME/rdbms/admin/catdwgrd.sql

Note:
$ORACLE_HOME should be pointing to 12c home

The catdwgrd.sql script downgrades all Oracle Database components in the database to the supported major release or patch release from which you originally upgraded

Sql> spool off
Sql> shutdown immediate
Exit SQL Plus
Sql> exit;

7) If operating system is LINUX/UNIX:

Change the following environment variables to source database to which it is downgrading:

ORACLE_HOME

PATH

Edit /etc/oratab or /var/opt/oracle/oratab to change

Map the database to source database oracle home

If your operating system is Windows, then complete the following steps:

a. Stop all Oracle services, including the OracleServiceSID Oracle service of the Oracle Database 12c database, where SID is the instance name.

For example, if your SID is ORCL, then enter the following at a command prompt:

C:\> NET STOP OracleServiceORCL

b. Delete the Oracle service at a command prompt by issuing the ORADIM command. If prompted, enter the password for the standard user account that is active on this Windows system.

For example, if your SID is ORCL, then enter the following command:

C:\> ORADIM -DELETE -SID ORCL

c. Create the Oracle service of the database that you are downgrading at a command prompt using the ORADIM command.

C:\> ORADIM -NEW -SID SID -INTPWD PASSWORD -MAXUSERS USERS
-STARTMODE AUTO -PFILE ORACLE_HOME\DATABASE\INITSID.ORA

8) Restore configuration files

Restore the configuration files (Password file, parameter file and so on) to downgrade release ORACLE_HOME.

9) If this is an Oracle RAC database, execute the following command to return the database to single instance mode:

SET CLUSTER_DATABASE=FALSE

10) Execute catrelod scripts from the downgrade release $ORACLE_HOME/rdbms/admin directory.

Start sqlplus and connect to the database instance as user SYS with sysdba privileges and start the database in upgrade mode:

: cd $ORACLE_HOME/rdbms/admin
: sqlplus
sql> connect sys as sysdba
sql> startup upgrade
sql> spool catrelod.log
sql> @?/rdbms/admin/catrelod.sql
sql> spool off

The catrelod.sql script reloads the appropriate version for each of the database components in the downgraded database.

11) Run the utlrp.sql script:

SQL> @utlrp.sql
Sql> exit;

The utlrp.sql script recompiles all existing PL/SQL modules that were previously in an INVALID state, such as packages, procedures, types, and so on.

12) Check the status of the downgraded database:

Note 556610.1  Script to Collect DB Upgrade/Migrate Diagnostic Information (dbupgdiag.sql) 

This sql script is a set of select 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 file called db_upg_diag_<sid>_<timestamp>.log.

13) After downgrade, you may find invalid QT views under sys schema. This is because views have selected the wrong columns from the base table. You need to recreate these views.

Refer to note:

Note 1520209.1 QT_*BUFER Views Invalid after downgrade from 12C 

Post Downgrade Steps:

1) If you are downgrading to Oracle Database 11g Release 1 (11.1.0.7) and you have Oracle Application Express in your database, then change to the directory to which you had copied the apxrelod.sql script (in pre-downgrade steps).
Manually reload Oracle Application Express by running the apxrelod.sql script:

SQL> @apxrelod.sql 

Running the apxrelod.sql script avoids package APEX_030200.WWV_FLOW_HELP being INVALID due to the following error:

PLS-00201: identifier 'CTX_DDL' must be declared 

2) If Oracle Label security was enabled in database, execute below scripts

  a. Copy the olstrig.sql script from the Oracle home under Oracle Database 12c to the Oracle home of the release to which the database is to be downgraded.

  b. From the Oracle home of the release you are downgrading to, run olstrig.sql to re-create DML triggers on tables with Oracle Label Security policies:     

SQL> @olstrig.sql 

3) If you are downgrading a cluster database, then you must run the following command to downgrade the Oracle Clusterware database configuration:

$ srvctl downgrade database -d db-unique-name -o oraclehome -t to_version 

where db-unique-name is the database name (not the instance name), oraclehome is the location of the old Oracle home for the downgraded database, and to_version is the database release to which the database is downgraded

******************************************************************************

Convert Single Instance Databases to RAC

Convert Single Instance Databases to RAC

1.1 Overview

We have Oracle grid infrastructure 11.2.0.3 with ASM installed but we most databases running as standalones instead of RAC databases. Recently we decide to convert all databases to RAC databases. The database version is 11.2.0.3. They are already running on the RAC home and on the grid cluster.

Oracle provide 4 ways to convert a standalone database to a RAC database: DBCA, manual, RConfig and OEM.

With DBCA, we can backup the database as a template and then create a RAC database with the template. This way is simple but the long down time is not acceptable in most environments so we quickly excluded as a choice.

I found the manual way is most reliable. You know exactly what each step does. It has the minimum downtime. The drawback is you have to do the manual steps for each database. If you have 100 database, you might not like to repeat the steps again, again and again.

The RConfig is the most applicable way. All we need to do is to create an xml file and run a command. The benefit is once you create the first xml file, you can reuse it with very minimum modification. Sometimes you only need to modify the name of the database. You can run multiple rconfig at the same time.

OEM may appears like the easiest way as simple as a few clicks. But it turned out that I never make it to work. I actually never spend the time to fix the issue. It's not worth the time to troubleshoot OEM. I just went with RConfig as it's easy enough.

In this article, we provides 2 samples. First one with manual steps and the second one use RConfig. Our assumption is that the database already running in the RAC home. If your database is not currently in the RAC home, you need to move it there first. You can do it with RMAN or datapump, or manually copy the files and recreate the control file. If your database is not in the same version, you might want to upgrade or patch to the same patch level before you move your database.

1.2 Manual Conversion of Standalone database to RAC

1.      Create a temporary PFILE from the SPFILE and add cluster parameters.

CREATE PFILE='/u03/oracle/wangm/pfile' from spfile;

Edit the pfile created and add the following RAC related parameters:

cluster_database_instances=2

cluster_database=true

E91DEV1.instance_number=1

E91DEV2.instance_number=2

E91DEV1.thread=1

E91DEV2.thread=2

E91DEV1.undo_tablespace='PSUNDO01'

E91DEV2.undo_tablespace='PSUNDO02'

E91DEV1.local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=devclstr1-vip)(PORT=1521))'

E91DEV2.local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=devclstr2-vip)(PORT=1521))'

Note you need to delete the two parameters:

undo_tablespace='PSUNDO01'

local_listener=’ DEVCLSTR1_LOCAL’

1.      Create additional UNDO tablespace.

create undo tablespace psundo02 datafile '+DATA';

1.      Create redo threads and enable the second thread.

E91DEV> alter database add logfile thread 2 group 5 ('+DATA','+FLASH') size 128m;

Database altered.

E91DEV> alter database add logfile thread 2 group 6 ('+DATA','+FLASH') size 128m;

Database altered.

E91DEV> alter database add logfile thread 2 group 7 ('+DATA','+FLASH') size 128m;

Database altered.

E91DEV> alter database add logfile thread 2 group 8 ('+DATA','+FLASH') size 128m;

Database altered.

E91DEV> alter database enable public thread 2;

Database altered.

E91DEV>

1.      Copy the Oracle password file and init file from the initial node, or from the node from which you are working, to the corresponding location on the additional nodes on which the cluster database will have an instance. Replace the ORACLE_SID name in each password file appropriately for each additional instance.

devclstr1-/u03/oracle/wangm>cd $ORACLE_HOME/dbs

devclstr1-/u01/app/oracle/product/11.2/dbs>mv orapwE91DEV orapwE91DEV1

devclstr1-/u01/app/oracle/product/11.2/dbs>mv initE91DEV.ora initE91DEV1.ora

devclstr1-/u01/app/oracle/product/11.2/dbs>scp -p orapwE91DEV1 devclstr2:/u01/app/oracle/product/11.2/dbs/orapwE91DEV2

orapwE91DEV1 100% 2560 2.5KB/s 00:00

devclstr1-/u01/app/oracle/product/11.2/dbs>scp -p initE91DEV1.ora devclstr2:/u01/app/oracle/product/11.2/dbs/initE91DEV2.ora

initE91DEV1.ora

1.      Create the SPFILE from the updated PFILE.

E91DEV> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

E91DEV> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

devclstr1-/u03/oracle/wangm>export ORACLE_SID=E91DEV1

devclstr1-/u03/oracle/wangm>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jun 25 15:52:16 2014

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to an idle instance.

none> create spfile='+DATA/E91DEV/spfileE91DEV.ora' from pfile='/u03/oracle/wangm/pfile';

File created.

none> startup

ORACLE instance started.

Total System Global Area 2137886720 bytes

Fixed Size 2230072 bytes

Variable Size 654313672 bytes

Database Buffers 1442840576 bytes

Redo Buffers 38502400 bytes

Database mounted.

Database opened.

none>

1.      On the local node, run catclust.sql. This command creates the dictionary views needed for Oracle RAC databases. For example:

SQL> start ?/rdbms/admin/catclust.sql

1.      Add the configuration for the Oracle RAC database and its instance-to-node mapping using SRVCTL.

devclstr1-/u03/oracle/wangm>srvctl add database -d E91DEV -o /u01/app/oracle/product/11.2

devclstr1-/u03/oracle/wangm>srvctl add instance -d E91DEV -i E91DEV1 -n devclstr1

devclstr1-/u03/oracle/wangm>srvctl add instance -d E91DEV -i E91DEV2 -n devclstr2

devclstr1-/u03/oracle/wangm>

1.      Start the database using SRVCTL.

devclstr1-/u03/oracle/wangm>srvctl start database -d E91DEV

devclstr1-/u03/oracle/wangm>srvctl status database -d E91DEV

Instance E91DEV1 is running on node devclstr1

Instance E91DEV2 is running on node devclstr2

devclstr1-/u03/oracle/wangm>

After starting the database with SRVCTL, your conversion process is complete and, for example, you can run the following SQL statement to see the status of all the instances in your Oracle RAC database:

E91DEV> select * from v$active_instances;

INST_NUMBER INST_NAME

----------- ------------------------------------------------------------

1 devclstr1:E91DEV1

2 devclstr2:E91DEV2

E91DEV>

1.3 Converting with RConfig

1.      As the user, copy the file $ORACLE_HOME/assistants/rconfig/sampleXMLs/ConvertToRAC_AdminManaged.xml to a working directory and edit the file.

Here is the file E91TRP.xml after modification.

<?xml version="1.0" encoding="UTF-8"?>

<n:RConfig xmlns:n="http://www.oracle.com/rconfig"

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

xsi:schemaLocation="http://www.oracle.com/rconfig rconfig.xsd">

<n:ConvertToRAC>

<!-- Verify does a precheck to ensure all pre-requisites are met, before the conversion is attempted. Allowable values are: YES|NO|ONLY -->

<n:Convert verify="ONLY">

<!--Specify current OracleHome of non-rac database for SourceDBHome -->

<n:SourceDBHome>/u01/app/oracle/product/11.2</n:SourceDBHome>

<!--Specify OracleHome where the rac database should be configured. It can be same as SourceDBHome -->

<n:TargetDBHome>/u01/app/oracle/product/11.2</n:TargetDBHome>

<!--Specify SID of non-rac database and credential. User with sysdba role is required to perform conversion -->

<n:SourceDBInfo SID="E91TPR">

<n:Credentials>

<n:User>sys</n:User>

<n:Password>password</n:Password>

<n:Role>sysdba</n:Role>

</n:Credentials>

</n:SourceDBInfo>

<!--Specify the list of nodes that should have rac instances running for the Admin Managed Cluster Database. LocalNode should be the first node in this nodelist. --> <n:NodeList>

<n:Node name="devclstr3"/>

<n:Node name="devclstr2"/>

</n:NodeList>

<!--Specify RacOneNode along with servicename to convert database to RACOne Node -->

<!--n:RacOneNode servicename="salesrac1service"/-->

<!--Instance Prefix tag is optional starting with 11.2. If left empty, it is derived from db_unique_name.-->

<n:InstancePrefix></n:InstancePrefix>

<!-- Listener details are no longer needed starting 11.2. Database is registered with default listener and SCAN listener running from Oracle Grid Infrastructure home. -->

<!--Specify the type of storage to be used by rac database. Allowable values are CFS|ASM. The non-rac database should have same storage type. ASM credentials are no needed for conversion. -->

<n:SharedStorage type="ASM">

<!--Specify Database Area Location to be configured for rac database.If this field is left empty, current storage will be used for rac database. For CFS, this field will have directory path. -->

<n:TargetDatabaseArea></n:TargetDatabaseArea>

<!--Specify Fast Recovery Area to be configured for rac database. If this field is left empty, current recovery area of non-rac database will be configured for rac database. If current database is not using recovery Area, the resulting rac database will not have a recovery area. -->

<n:TargetFlashRecoveryArea></n:TargetFlashRecoveryArea>

</n:SharedStorage>

</n:Convert>

</n:ConvertToRAC>

</n:RConfig>

1.      Run :

First we set the verify in the xml to “ONLY” to make sure we can convert.

<n:Convert verify="ONLY">

devclstr1-/u03/oracle/wangm>rconfig E91TPR.xml

<?xml version="1.0" ?>

<RConfig version="1.1" >

<ConvertToRAC>

<Convert>

<Response>

<Result code="0" >

Operation Succeeded

</Result>

</Response>

<ReturnValue type="object">

There is no return value for this step </ReturnValue>

</Convert>

</ConvertToRAC></RConfig>

devclstr1-/u03/oracle/wangm>

Then we set it to “YES” to perform the convert.

<n:Convert verify="YES">

devclstr1-/u03/oracle/wangm>rconfig E91TPR.xml

Converting Database "E91TPR" to Cluster Database. Target Oracle Home: /u01/app/oracle/product/11.2. Database Role: PRIMARY.

Setting Data Files and Control Files

Adding Database Instances

Adding Redo Logs

Enabling threads for all Database Instances

Setting TEMP tablespace

Adding UNDO tablespaces

Adding Trace files

Setting Fast Recovery Area

Updating Oratab

Creating Password file(s)

Configuring Listeners

Configuring related CRS resources

Starting Cluster Database

<?xml version="1.0" ?>

<RConfig version="1.1" >

<ConvertToRAC>

<Convert>

<Response>

<Result code="0" >

Operation Succeeded

</Result>

</Response>

<ReturnValue type="object">

<Oracle_Home>

/u01/app/oracle/product/11.2

</Oracle_Home>

<Database type="ADMIN_MANAGED" >

<InstanceList>

<Instance SID="E91TPR1" Node="devclstr1" >

</Instance>

<Instance SID="E91TPR2" Node="devclstr2" >

</Instance>

</InstanceList>

</Database> </ReturnValue>

</Convert>

</ConvertToRAC></RConfig>

devclstr1-/u03/oracle/wangm>

1.4 Post Conversion steps.

1) Update crontab if applicable.

2) Remove and add the database target in OEM.

3) Modify local listener setting.

alter system set local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=devclstr1-vip)(PORT=1521))' sid='E91TPR1';

alter system set local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=devclstr2-vip)(PORT=1521))' sid='E91TPR2';

4) If using RConfig, the tool automatically name the new undo tablespace to UNDOTBS2. To change it, do the following.

sys@E91TPR2> alter tablespace UNDOTBS2 rename to PSUNDO02;

Tablespace altered.

sys@E91TPR2> alter system set undo_tablespace=PSUNDO02 sid='E91TPR2';

System altered.

sys@E91TPR2> show parameter undo

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

undo_management string AUTO

undo_retention integer 10800

undo_tablespace string PSUNDO02

sys@E91TPR2>

1.5 Notes

1) Oracle supplies two sample xml files, one for Admin managed and one for policy managed. Since we are using the Admin managed xml here. The policy managed one is similiar.

2) We can create a new undo table space with desired name, swith the undo_tablespace parameter and then drop the undo tablespace created by RConfig. I've noticed if the psundo02 table space exists before running RConfig, then it's smart enough to use it without creating the default undo tablespace.