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>
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>
Good Post! Thank you so much for sharing this pretty post, it was so good to read and useful to improve my knowledge as updated one, keep blogging…
ReplyDeleteOracle Training in Chennai
Oracle Training Institute in Chennai
This comment has been removed by the author.
ReplyDeleteThis comment has been removed by the author.
ReplyDeleteInspiring writings and I greatly admired what you have to say , I hope you continue to provide new ideas for us all and greetings success always for you..Keep update more information..
ReplyDeleterpa training in Chennai | best rpa training in chennai
rpa training in pune
rpa online training | rpa training in bangalore
We are a group of volunteers and starting a new initiative in a community. Your blog provided us valuable information to work on.You have done a marvellous job!
ReplyDeleteData Science training in rajaji nagar | Data Science with Python training in chenni
Data Science training in electronic city | Data Science training in USA
Data science training in pune | Data science training in kalyan nagar
I was looking for this certain information for a long time. Thank you and good luck.
ReplyDeletejava training in chennai | java training in bangalore
java online training | java training in pune
Thank you for taking the time to provide us with your valuable information. We strive to provide our candidates with excellent care and we take your comments to heart.As always, we appreciate your confidence and trust in us
ReplyDeletePython training in marathahalli
Python training institute in pune
Wonderful Blog !!! with more latest info stuffed. Thanks for sharing.
ReplyDeleteselenium testing training
best training institute for selenium in chennai
Selenium Training in Chennai
Big Data Training in Chennai
salesforce certification in chennai
best salesforce training in chennai
Thanks for sharing this blog. every blog should be very neatly represented.
ReplyDeleteData Science Training Course In Chennai | Data Science Training Course In Anna Nagar | Data Science Training Course In OMR | Data Science Training Course In Porur | Data Science Training Course In Tambaram | Data Science Training Course In Velachery
I have search sites with relevant information on given topics and provide them to our teacher opinions and the article.oracle dba page clear to share the message !!!
ReplyDeleteAndroid Training in Chennai
Android Online Training in Chennai
Android Training in Bangalore
Android Training in Hyderabad
Android Training in Coimbatore
Android Training
Android Online Training