Flashback through a database upgrade

While testing how to perform a 10.2.0.3 to 11.1.0.6 upgrade I thought I would attempt to flashback through the upgrade back to the 10.2.0.3 database. There is a major caveat to this, you must not change the compatible parameter to be higher than your original release or you will not be able to open the database with the 10.2.0.3 software. I’m sure this will work for other 10.2 (and probably 10.1) releases.

[oracle@testdb ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.3.0 – Production on Thu Jan 17 14:57:45 2008

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bit Production
With the Partitioning and Data Mining options

SQL> create restore point begin_upgrade guarantee flashback database;

Restore point created.

Best to create a guaranteed restore point, just in case you over run your flashback retention times

SQL> select * from V$restore_point;

SCN DATABASE_INCARNATION# GUA STORAGE_SIZE TIME NAME
———- ———————————- ———– ————– ——— ———
2041092430 2 YES 8192000 17-JAN-08 15.12.47.000000000 BEGIN_UPGRADE

SQL> @/oracle/product/11.1.0/rdbms/admin/utlu111i.sql
Oracle Database 11.1 Pre-Upgrade Information Tool 01-17-2008 14:59:03
.
**********************************************************************
Database:
**********************************************************************
–> name: TESTDB
–> version: 10.2.0.3.0
–> compatible: 10.2.0.3
–> blocksize: 8192
–> platform: Linux 64-bit for AMD
–> timezone file: V4
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
–> SYSTEM tablespace is adequate for the upgrade.
…. minimum required size: 1021 MB
–> UNDOTBS1 tablespace is adequate for the upgrade.
…. minimum required size: 584 MB
–> SYSAUX tablespace is adequate for the upgrade.
…. minimum required size: 685 MB
–> TEMP tablespace is adequate for the upgrade.
…. minimum required size: 61 MB
**********************************************************************
Update Parameters: [Update Oracle Database 11.1 init.ora or spfile]
**********************************************************************
— No update parameter changes are required.
**********************************************************************
Renamed Parameters: [Update Oracle Database 11.1 init.ora or spfile]
**********************************************************************
— No renamed parameters found. No changes are required.
**********************************************************************
Obsolete/Deprecated Parameters: [Update Oracle Database 11.1 init.ora or spfile]
**********************************************************************
–> “background_dump_dest” replaced by “diagnostic_dest”
–> “user_dump_dest” replaced by “diagnostic_dest”
–> “core_dump_dest” replaced by “diagnostic_dest”
**********************************************************************
Components: [The following database components will be upgraded or installed]
**********************************************************************
–> Oracle Catalog Views [upgrade] VALID
–> Oracle Packages and Types [upgrade] VALID
–> JServer JAVA Virtual Machine [upgrade] VALID
–> Oracle XDK for Java [upgrade] VALID
–> Oracle Workspace Manager [upgrade] VALID
–> Oracle Text [upgrade] VALID
–> Oracle XML Database [upgrade] VALID
–> Oracle Java Packages [upgrade] VALID
–> Oracle interMedia [upgrade] VALID
–> Data Mining [upgrade] VALID
–> Expression Filter [upgrade] VALID
–> Rule Manager [upgrade] VALID
**********************************************************************
Miscellaneous Warnings
**********************************************************************
WARNING: –> Database contains schemas with objects dependent on network
packages.
…. Refer to the 11g Upgrade Guide for instructions to configure Network ACLs.
…. USER FLOWS_020000 has dependent objects.
PL/SQL procedure successfully completed.

You must run the utlu111i script as it populates a table that the upgrade script checks, if this has not been run the upgrade will bomb fairly early. Remember to run this with the Oracle 10g environment, it would not work when i tried to run it with the 11g environment.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

startup the database with the 11g ORACLE_HOME

SQL> startup upgrade pfile=’/home/oracle/init11g.ora';
ORACLE instance started.

Total System Global Area 784998400 bytes
Fixed Size 2148512 bytes
Variable Size 201328480 bytes
Database Buffers 578813952 bytes
Redo Buffers 2707456 bytes
Database mounted.
Database opened.

run the upgrade script

SQL> @?/rdbms/admin/catupgrd
.
.
.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup pfile=’/home/oracle/init11g.ora';
ORACLE instance started.

Total System Global Area 784998400 bytes
Fixed Size 2148512 bytes
Variable Size 201328480 bytes
Database Buffers 578813952 bytes
Redo Buffers 2707456 bytes
Database mounted.
Database opened.

We now have an upgraded 11g database

SQL> select * from v$version;

BANNER
——————————————————————————–
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 – 64bit Production
PL/SQL Release 11.1.0.6.0 – Production
CORE 11.1.0.6.0 Production
TNS for Linux: Version 11.1.0.6.0 – Production
NLSRTL Version 11.1.0.6.0 – Production

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 – 64bit Production
With the Real Application Testing option

go back to the 10g Oracle home

[oracle@testdb ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.3.0 – Production on Thu Jan 17 16:39:17 2008

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Connected to an idle instance.

SQL> startup mount pfile=’/home/oracle/init11g.ora';
ORACLE instance started.

Total System Global Area 788529152 bytes
Fixed Size 2076008 bytes
Variable Size 205521560 bytes
Database Buffers 574619648 bytes
Redo Buffers 6311936 bytes
Database mounted.

flashback to our restore point

SQL> flashback database to restore point begin_upgrade;

Flashback complete.

SQL> alter database open resetlogs;

Database altered.

Now our database is back to it’s 10g version

SQL> select * from v$version;

BANNER
—————————————————————-
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – 64bi
PL/SQL Release 10.2.0.3.0 – Production
CORE 10.2.0.3.0 Production
TNS for Linux: Version 10.2.0.3.0 – Production
NLSRTL Version 10.2.0.3.0 – Production

So this could be useful for testing an 11g upgrade from a 10.X release, it could enable you to quickly downgrade and retry should something go wrong. However the lack of the ability to open the upgraded release with compatible set to 11 does limit somewhat the new features you could test.

I’m not really sure how practical it is really, I was more just curious what what would actually happen if you attempted to flashback through the upgrade.

About these ads
Leave a comment

8 Comments

  1. Lars Bo

     /  March 6, 2009

    Hi!
    Tried this out, when migrating from 10.2.0.3 to 11.1.0.7.
    At the point “go back to the 10g Oracle home” I received the error “ORA-38792: encountered unknown flashback record from release 11.0.0.0.0″.
    So I in stead started the database using 11gR1 and then the flashback database worked nicely! This was a 1.1TB database on HP-UX IA64 and the flashback of the 11g migration took approx 7 minutes.
    After that I shutdown the DB, started up into MOUNT state using 10gR2 and opened the DB using the RESETLOGS option. Now back to 10.2.0.3!
    I must say this is a nice (extra) fallback option to have in such situations (a complete DB restore would have taken 8-12 hours from tape)!
    Cheers
    Lars Bo

    Reply
    • jarneil

       /  March 6, 2009

      Hi Lars,

      Thanks for reading!

      Yep flashback definitely is easier and quicker than rman. Though as it’s new it’s not been without it’s issues.

      jason.

      Reply
  2. Matt

     /  July 9, 2009

    Great post. I’ve often wondered about whether flashing back through a database upgrade was possible. With respect to how practical this is, I reckon it could be very helpful for rolling back a failed database upgrade. Lets say for example that the server crashed mid way through or you ran out of space in a tablespace, this flashback option would allow you to quickly flashback and continue with the upgrade without having to restore the database. This could save a huge amount of time.

    Once you had confirmed that the upgrade was successfull (by checking the spooled upgrade log) you could then update the compatible parameter which would then invalidate the guaranteed restore point.

    Reply
  3. Cool stuff, didn’t think it was possible.

    I wonder how people feel about this though, especially in companies with well-established storage teams. In my past there has always been a battle between the DBAs and the storage team when it came to using the preferred “quick exit” method. The storage team wanted to take a snapshot of the LUNs, whereas the DBAs preferred guaranteed restore points. As a DBA responsible for the application uptime I prefer the methods I understand and control. After all, if the storage snapshot is incomplete (human error: forgot to create a consistent view/forgot a LUN etc) then the storage admin can only throw his hands in the air and hand over to you for a full restore. Or to invoke DR. And we don’t really want that.

    That’s my point of view at least, curious to see what others think.

    Reply
  4. Marc

     /  August 1, 2012

    Hi I don’t see what the advantage is of using flashback database instead of using downgrade database …

    http://docs.oracle.com/cd/E11882_01/server.112/e23633/downgrade.htm

    Reply
  5. MarkP

     /  March 28, 2013

    I know this is quite an old thread now but it in answer to Marc’s post on August 1st 2012 the downgrade scripts provided by Oracle do not put the database back exactly as it was prior to the upgrade. It only alters the dictionary sufficiently to allow compatibility with the prior release. This is not the same as returning the database to the exact state it was in prior to the upgrade. In addition to this the downgrade script can only be used to if the upgrade was successful. It the brown stuff has hit the air circulating device mid way through an upgrade the downgrade script cannot be used.

    Flashback can be a useful option that covers many scenarios which can lead to a downgrade be instigated. Like a full restore using RMAN it puts the database back exactly as it was at the block level prior to the upgrade. As Jason suggests it is pretty quick as well.

    Reply
  1. Log Buffer #82: a Carnival of the Vanities for DBAs

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 58 other followers

%d bloggers like this: