USING FLASHBACK DATABASE TO ROLLBACK FROM 11g to 10g.

Using flashback database to rollback a failed upgrade to 11g works.

This method can probably be used as a first line of defence to avoid a time consuming restore incase a 11g upgrade fails.
However it goes without saying that a proper backup should also be taken prior to attempting any upgrade.
In 10g database –
Flashback should be turned on prior to upgrade –

Create a guaranteed restore point .
sql > create restore point before_upgrade guarantee flashback database ;

Make sure atleast one more flashback log is created in the 10g environment after the restore point is created –
If not and the existing flashback log is appended after the database is upgraded to 11g , the following error will appear later on while trying to flashback the database.
“ORA-38792: encountered unknown flashback record from release 11.0.0.0.0″
Now Upgrade database to 11.2.0.1 –
Once complete –

Still in 11g environment –
SQL> select * from v$version;
BANNER
——————————————————————————–
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
PL/SQL Release 11.2.0.1.0 – Production
CORE 11.2.0.1.0 Production
TNS for IBM/AIX RISC System/6000: Version 11.2.0.1.0 – Production
NLSRTL Version 11.2.0.1.0 – Production
SQL> shutdown immediate;

Log back into the 10g environment –
cd $ORACLE_HOME/dbs
/u01/oracle/product/10.2/ofsad2/dbs> sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 – Production on Sun Jul 2 07:48:00 2017
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> startup mount;
Database mounted.
SQL> flashback database to restore point before_upgrade;

ALERT LOG –
flashback database to restore point before_upgrade
Flashback Restore Start
Sun Jul 2 07:49:37 2017
Flashback Restore Complete
Flashback Media Recovery Start
parallel recovery started with 3 processes
Flashback Media Recovery Log /u03/oradata/flash_recovery_area/DB10G/archivelog/2017_07_01/o1_mf_1_120_62r31dt6_.arc
Sun Jul 2 07:49:37 2017
Incomplete Recovery applied until change 5868883
Flashback Media Recovery Complete
Completed: flashback database to restore point before_upgrade

sql > alter database open resetlogs;

SQL> select * from v$version;
BANNER
—————————————————————-
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bi
PL/SQL Release 10.2.0.4.0 – Production
CORE 10.2.0.4.0 Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.4.0 – Productio
NLSRTL Version 10.2.0.4.0 – Production
Get rid of the restore point created.

SQL> select * from v$restore_point order by scn;
SCN DATABASE_INCARNATION# GUA STORAGE_SIZE
———- ——————— — ————
TIME
—————————————————————————
NAME
——————————————————————————–
5868880 2 YES 646053888
01/JUL/17 11:31:27.000000000 AMbefore_upgrade

SQL> drop restore point before_upgrade;
Restore point dropped.

SQL> select * from v$restore_point order by scn;
no rows selected
SQL>