Saturday, June 2, 2012

flashback database to a previous oracle version


This due to issues with RAT( probably inconsistent data ) i had to flashback the RAT testing db …

now this db was upgraded to 11.2.0.3

so i shut the db down and started it up with the 10.2.0.3 home since the flashback was generated when the  10.2.0.3 home was still used


SQL> select  scn , name from v$restore_point;

SCN                     NAME
8446404412              BEFORE_ACTIVATE

so i issued


SQL> flashback database to restore point before_activate;


however it returned 


flashback database to restore point before_activate
*
ERROR at line 1:
ORA-38792: encountered unknown flashback record from release 11.0.0.0.0


so i stopped and mounted the db with 11.2.0.3 home and did the flashback


SQL> flashback database to restore point before_activate;
Flashback complete.

shutdown the db and started it in mount modus with the 10g home


and opened resetlogs ...

this is was recorded in the alertlog

RESETLOGS after incomplete recovery UNTIL CHANGE 8446404413Resetting resetlogs activation ID 2090464176 (0x7c99f3b0)Online log +DATADG1//onlinelog/group_7.2217.784034813: Thread 2 Group 7 was previously clearedOnline log +ARCHDG1//onlinelog/group_7.316.784034813: Thread 2 Group 7 was previously clearedOnline log +DATADG1//onlinelog/group_8.2218.784034815: Thread 2 Group 8 was previously clearedOnline log +ARCHDG1//onlinelog/group_8.317.784034815: Thread 2 Group 8 was previously cleared

funny thing is that current_scn is much higher, i guess this is to avoid clashes… with scn 's that were created across the different flashback and resetlogs operations I did.

SQL> select current_scn from v$database;

CURRENT_SCN
---------------------
 8447540415

I would like to thank the twitter community and especially @yvelikanov and @jarneil @martinberx 


PS jarneil wrote an excellent article about this but seemed to use the 10.2.0.3 binaries to flashback as this didn't work in my case i decided to devote a blog article for this .

No comments: