Tuesday, June 27, 2017

Oracle : Downgrade 12c Databases to 11g Using Flashback Restore Point

Downgrading Database Without Executing catdwgrd.sql 


My Database was upgraded to 12.1.0.2 but due to performance issues, application team requested us to downgrade the database to 11.2.0.4 (previous version). 

The easiest way I could think of was to flashback the database using the restore point I created before upgrading the database to 12c


Below statements should hold true to flashback the database to the earlier version.
1) We have previous version binaries still present
2) A guarantee restore point created on earlier version before the upgrade is done
3) The compatible parameter is not changed. 

Steps to downgrade the database from 12.1.0.2 to 11.2.0.4


From 12.1.0.2 binaries

Check the restore point if exists

SQL> select NAME,SCN,TIME from v$restore_point;

NAME                     SCN                          TIME
--------------------------------------------  ---------------------
BEFORE_UPGRADE       1.5096E+13                22-JUN-17 01.55.58.000000000 PM


Crosscheck if the compatible parameter is not changed

SQL> show parameter compatible

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string      11.2.0.4.0
noncdb_compatible                    boolean     FALSE


Mount the database

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Database mounted.

Flashback database to the restore point created

SQL> FLASHBACK DATABASE TO RESTORE POINT BEFORE_UPGRADE;

Flashback complete.


Alert log shows below

Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE   MOUNT
Mon Jun 26 10:59:03 2017
FLASHBACK DATABASE TO RESTORE POINT BEFORE_UPGRADE
Mon Jun 26 10:59:05 2017
Flashback Restore Start
Mon Jun 26 10:59:40 2017
Flashback Restore Complete
Flashback Media Recovery Start
 Started logmerger process
Mon Jun 26 10:59:41 2017
Parallel Media Recovery started with 12 slaves
Flashback Media Recovery Log /opt/app/prod1/oraflsh01/PROD1/archivelog/2017_06_22/o1_mf_1_1236_dnrds518_.arc
Mon Jun 26 10:59:42 2017
Incomplete Recovery applied until change 15095662683591 time 06/22/2017 13:56:00
Mon Jun 26 10:59:42 2017
Flashback Media Recovery Complete
Completed: FLASHBACK DATABASE TO RESTORE POINT BEFORE_UPGRADE

Once flashback completes shutdown the database

SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.


Now mount the database from older binaries, [in our case 11.2.0.4], set your environment to point to 11.2.0.4 binaries 

SQL> startup mount
ORACLE instance started.

Total System Global Area 2.1379E+10 bytes
Fixed Size                  2262656 bytes
Variable Size            3221227904 bytes
Database Buffers         1.8119E+10 bytes
Redo Buffers               36073472 bytes
Database mounted.

Open Resetlogs

SQL> alter database open resetlogs;

Database altered.

Now check the DB version, it should show  11.2.0.4

SQL> select comp_name, version, status from dba_registry;

COMP_NAME
--------------------------------------------------------------------------------
VERSION                        STATUS
------------------------------ --------------------------------------------
Oracle XML Database
11.2.0.4.0                     VALID

Oracle Expression Filter
11.2.0.4.0                     VALID

Oracle Rules Manager
11.2.0.4.0                     VALID


COMP_NAME
--------------------------------------------------------------------------------
VERSION                        STATUS
------------------------------ --------------------------------------------
Oracle Workspace Manager
11.2.0.4.0                     VALID

Oracle Database Catalog Views
11.2.0.4.0                     VALID

Oracle Database Packages and Types
11.2.0.4.0                     VALID


COMP_NAME
--------------------------------------------------------------------------------
VERSION                        STATUS
------------------------------ --------------------------------------------
JServer JAVA Virtual Machine
11.2.0.4.0                     VALID

Oracle XDK
11.2.0.4.0                     VALID

Oracle Database Java Packages
11.2.0.4.0                     VALID



I hope these steps help you.. 


1 comment: