This discussion is archived
10 Replies Latest reply: Feb 11, 2013 7:16 AM by RK334 RSS

Rollback from 11g to 10g - Incremental Data

RK334 Newbie
Currently Being Moderated
Hi All,

I want to test a rollback scenario in my database.

1) Oracle 10g -- Solaris server
2) Oracle 11g - AIX Server.

After the upgrade from Solaris to AIX, if we were to roll back, is there any option in expdp to get only the incremental data that was added to my new database after the migration

Any pointers to this would be helpful
  • 1. Re: Rollback from 11g to 10g - Incremental Data
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    Not that I am aware of - unless you have a date/timestamp column in your tables to be able to isolate that data.

    Why would you want to rollback after an upgrade ?

    HTH
    Srini
  • 2. Re: Rollback from 11g to 10g - Incremental Data
    RK334 Newbie
    Currently Being Moderated
    Srini Chavali wrote:
    Not that I am aware of - unless you have a date/timestamp column in your tables to be able to isolate that data.
    How can I isolate the data in the expdp if i have a time stamp option in my tables?

    Why would you want to rollback after an upgrade ?
    Rollback could be because of many external factors related to routing/application servers also.
  • 3. Re: Rollback from 11g to 10g - Incremental Data
    TSharma-Oracle Guru
    Currently Being Moderated
    You can do that but it would be very tedious.

    One method is you can use log miner and by using logminer you can rollback everything on the new database and put it back on old server.
    Another method is applicable if there were only "Inserts" (new data added as you mentioned), you can tke the minus of data and delete that from old server or you can take the union of both and import back onto the old server.
    Again it would be the pain. Also depends how long after you want to do this.
  • 4. Re: Rollback from 11g to 10g - Incremental Data
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    RK334 wrote:
    Srini Chavali wrote:
    Not that I am aware of - unless you have a date/timestamp column in your tables to be able to isolate that data.
    How can I isolate the data in the expdp if i have a time stamp option in my tables?

    Use the QUERY parameter of expdp to select rows that need to be exported - http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_export.htm#sthref120
    Why would you want to rollback after an upgrade ?
    Rollback could be because of many external factors related to routing/application servers also.
    Not sure what you mean by this

    HTH
    Srini
  • 5. Re: Rollback from 11g to 10g - Incremental Data
    RK334 Newbie
    Currently Being Moderated
    >
    Another method is applicable if there were only "Inserts" (new data added as you mentioned), you can tke the minus of data and delete that from old server or you can take the union of both and import back onto the old server.
    The new data will be only inserts in my new database. Can you please explain the process that you explained above?
    Again it would be the pain. Also depends how long after you want to do this.
    If there are only 10-20 tables updated out of a total of 500 tables,we can do it. The duration we decide of rolling back would be a day's data.
  • 6. Re: Rollback from 11g to 10g - Incremental Data
    TSharma-Oracle Guru
    Currently Being Moderated
    Is this your production database?
    Are you taking export backup of your newly migrated database? If yes, you can import the first export backup of 11g database(after you move to new server and higher version) than compare both the databases(your 10g and 11g). The change would be comparitively less. You can delete the change and then you will back to the original data.
    You can compare the data with the help of some external tool also like TOAD.
  • 7. Re: Rollback from 11g to 10g - Incremental Data
    RK334 Newbie
    Currently Being Moderated
    Thanks for the information Sharma. But we wouldn't be looking at any Third party tools.

    I came across this link on internet. Thinking this might server my purpose.

    http://www.dbspecialists.com/files/scripts/compare_data.sql

    I am going to test and let the group know. Please provide your views if you have already tried this approach.
  • 8. Re: Rollback from 11g to 10g - Incremental Data
    TSharma-Oracle Guru
    Currently Being Moderated
    I have not tried this exact approach but somehow similar I was trying to tell to compare over db links. Even TOAD is running same queries behind the scenes. This should work. Good Luck and let us knowif this script works.
  • 9. Re: Rollback from 11g to 10g - Incremental Data
    Richard Harrison . Expert
    Currently Being Moderated
    Hi,
    Can you not just export from 11g and load back in to 10g - you just have to set the version parameter correctly and it will create a file that can be loaded into 10g.

    Having to extract all the changes to apply to the original source is likely to be painful however you do it.

    If you really wanted to get clever you'd go with streams which could be revered back the other way if you set it up right....

    Cheers,
    Harry
  • 10. Re: Rollback from 11g to 10g - Incremental Data
    RK334 Newbie
    Currently Being Moderated
    Richard Harrison .  wrote:

    Can you not just export from 11g and load back in to 10g - you just have to set the version parameter correctly and it will create a file that can be loaded into 10g.
    I can do an export and load the data back to 10g database. But, I am making some character set changes for all the datatypes in my database in 11g.

    So, the other approach i am looking at would be to get the whole dump from 11g and import the data alone; but I fear there might be something missing with this approach.

    I don't want the table datatypes to be moved back to 10g.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points