This discussion is archived
1 2 Previous Next 17 Replies Latest reply: Mar 26, 2013 9:05 AM by marksmithusa RSS

Flashback Database Scenario

moslee Newbie
Currently Being Moderated
Hi to all

Just want to hear from you guys if Flashback Database can be used in the following scenario. Main objective is to show that in using Flashback Database, the DBA has great flexibility and control over in determine the best Point-in-Time recovery. Secondary objective is to show that Flashback Database can be used to determine the time of error occurred. Let me know if I can use Flashback Database to fulfill these 2 objectives. Thanks for your feedbacks!

Settings
db_flashback_retention_target=60
db_recovery_file_dest_size big integer 20G

Scenario
1) Logical data corruptions reported to occur in DB at about *1455*.
2) DBA ABC feedbacks suspecting that scheduled job at 1450 has logical errors. But DBA XYZ feedbacks suspecting that new release of procedure that runs at 1455 has logical errors.
3) I, supposed to be the saviour, shutdown immediate and startup mount the DB.
4) Then I flashback the database to 1450. Followed by ALTER DATABASE OPEN READ ONLY; to do logical checking with developer and dba.
5) Result found that scheduled job at 1450 has no problem.
6) Next I shutdown immediate and startup mount the DB again.
7) Roll forward the DB with RECOVER command to 1455. Followed by ALTER DATABASE OPEN READ ONLY; to do logical checking with developer and dba.
8) Result found that new release of procedure at 1455 has logical errors.
9) For the third time, I shutdown immediate and startup mount the DB.
10) Then I flashback the database to 1454. Followed by ALTER DATABASE OPEN READ ONLY; to do logical checking with developer and dba the last time.
11) For the fourth time, I shutdown immediate and startup mount the DB.
12) ALTER DATABASE OPEN RESETLOGS; to confirm recovery of DB at 1454.
13) Correct and retest the procedure in Development DB before releasing to Production.
14) Case resolved.
  • 1. Re: Flashback Database Scenario
    sb92075 Guru
    Currently Being Moderated
    I doubt that FlashBack database would ever be used against a Production OLTP DB which accepts new orders, processes Inventory Management as orders get fulfilled or does Customer Billings.
  • 2. Re: Flashback Database Scenario
    moslee Newbie
    Currently Being Moderated
    Hi sb92075

    What do you mean?

    This prod db that I have is like a "hub" with data coming from multiple sources for user to query. It also have a fair share of procedures and packages that are scheduled to run throughout the day.
  • 3. Re: Flashback Database Scenario
    sb92075 Guru
    Currently Being Moderated
    moslee wrote:
    Hi sb92075

    What do you mean?

    This prod db that I have is like a "hub" with data coming from multiple sources for user to query. It also have a fair share of procedures and packages that are scheduled to run throughout the day.
    3) I, supposed to be the saviour, shutdown immediate
    After SHUTDOWN DATABASE, no new orders can be entered, no inventory records updated & no customers billed; which is an excellent way to get yourself fired for stopping every business activity required by the business to stay in business.
  • 4. Re: Flashback Database Scenario
    moslee Newbie
    Currently Being Moderated
    Hi sb92075

    No, this prod db that I'm talking about is not the main db that deals directly with business activity. Business activity are handled by multiple sources of db. This one with Flashback Database on is more for reporting purpose, but it has a fair share of procedures/packages, scheduled jobs.

    Regarding downtime, I can have downtime for this prod db. More so especially in the context of logical errors. No worries about that. Business activity continues as normal because the downtime of this prod db has no direct impact.
  • 5. Re: Flashback Database Scenario
    sb92075 Guru
    Currently Being Moderated
    moslee wrote:
    Hi sb92075

    No, this prod db that I'm talking about is not the main db that deals directly with business activity. Business activity are handled by multiple sources of db. This one with Flashback Database on is more for reporting purpose, but it has a fair share of procedures/packages, scheduled jobs.
    & flawed Quality Assurance group that allows bugs into Production.
    Since just a reporting DB, then fix the code & run the reports again.
    There is no need for Flash Back.
    It appears that you have a Solution that is looking for a Problem.
  • 6. Re: Flashback Database Scenario
    KR10822864 Pro
    Currently Being Moderated
    is it your database Datawarehose db....?
    what is your db version...?
  • 7. Re: Flashback Database Scenario
    rp0428 Guru
    Currently Being Moderated
    >
    Main objective is to show that in using Flashback Database, the DBA has great flexibility and control over in determine the best Point-in-Time recovery.
    >
    No - Flashback is AN ALTERNATIVE to using point-in-time recovery.
    >
    Secondary objective is to show that Flashback Database can be used to determine the time of error occurred.
    >
    No - Flashback won't determine anything at all. YOU have to determine if there was an error and, if so, what time the error occured. Flashback can assist you by giving you more efficient access to the entire database from a previous savepoint or point-in-time.

    But if you have some idea what tables are involved you could more easily use flashback queries on those tables to determine when the problem happened. And sometimes you can just restore a flashback version of the data in those tables to resolve the issue without having to shut anything down at all.

    See 'Using Flashback Database and Restore Points' in the Backup and Recovery Guide
    http://docs.oracle.com/cd/E11882_01/backup.112/e10642/flashdb.htm

    The use case you provided might be better served by using flashback queries on affected tables to diagnose the problem and them flashing those tables back.

    Flashing back the entire database would be an option of near-last resort; just prior to point-in-time recovery.
  • 8. Re: Flashback Database Scenario
    KR10822864 Pro
    Currently Being Moderated
    moslee wrote:
    Hi to all

    Just want to hear from you guys if Flashback Database can be used in the following scenario. Main objective is to show that in using Flashback Database, the DBA has great flexibility and control over in determine the best Point-in-Time recovery. Secondary objective is to show that Flashback Database can be used to determine the time of error occurred. Let me know if I can use Flashback Database to fulfill these 2 objectives. Thanks for your feedbacks!

    Settings
    db_flashback_retention_target=60
    db_recovery_file_dest_size big integer 20G

    Scenario
    1) Logical data corruptions reported to occur in DB at about *1455*.
    2) DBA ABC feedbacks suspecting that scheduled job at 1450 has logical errors. But DBA XYZ feedbacks suspecting that new release of procedure that runs at 1455 has logical errors.
    3) I, supposed to be the saviour, shutdown immediate and startup mount the DB.
    4) Then I flashback the database to 1450. Followed by ALTER DATABASE OPEN READ ONLY; to do logical checking with developer and dba.
    5) Result found that scheduled job at 1450 has no problem.
    6) Next I shutdown immediate and startup mount the DB again.
    7) Roll forward the DB with RECOVER command to 1455. Followed by ALTER DATABASE OPEN READ ONLY; to do logical checking with developer and dba.
    8) Result found that new release of procedure at 1455 has logical errors.
    9) For the third time, I shutdown immediate and startup mount the DB.
    10) Then I flashback the database to 1454. Followed by ALTER DATABASE OPEN READ ONLY; to do logical checking with developer and dba the last time.
    11) For the fourth time, I shutdown immediate and startup mount the DB.
    12) ALTER DATABASE OPEN RESETLOGS; to confirm recovery of DB at 1454.
    13) Correct and retest the procedure in Development DB before releasing to Production.
    14) Case resolved.
    above approch is fair enough....to recover database as per your requirement...but fix your code error ( *new release of procedure).....and test it 1st in test environment ..then run in  prod..
    please post what logical corruption you have faced ..any error info...?

    Performing Flashback and Database Point-in-Time Recovery
    http://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmflash.htm

    Edited by: KR10822864 on Mar 25, 2013 8:57 PM
    added url for ref
  • 9. Re: Flashback Database Scenario
    moslee Newbie
    Currently Being Moderated
    Hi sb92075

    You are sharp there. Yes, I am actually looking for a problem, or rather a scenario only that fulfills the objectives stated in my first post. I confess that the issue stated in my scenario is quite bad..
  • 10. Re: Flashback Database Scenario
    moslee Newbie
    Currently Being Moderated
    Hi KR......


    I'm just stating a scenario with a general logical error in the db.. no real error faced now.. all fine =)
  • 11. Re: Flashback Database Scenario
    Justin_Mungal Journeyer
    Currently Being Moderated
    As SB mentioned, Flashback Database requires downtime. When troubleshooting individual transactions in production, such as a job gone awry, you would be better off making use of Flashback Technology that operates at the logical level, which use UNDO as opposed to flashback logs. Please study the following carefully.
    http://docs.oracle.com/cd/E11882_01/appdev.112/e25518/adfns_flashback.htm

    Use Flashback Database in your production environment as a replacement for traditional point-in-time media recovery, when physical corruption is not the issue (see http://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmflash.htm#BGBDCAFA for more information on this). Flashback Database can be preferable to traditional media recovery because it does not restore the entire database, rather, it rewinds changes that have been made to it.

    You can also use LogMiner to analyze changes made to the database. It's easier to use than you might expect.
    http://docs.oracle.com/cd/E11882_01/server.112/e22490/logminer.htm

    Think of it this way: Flashback Database is drastic, and is designed as a potential alternative to media recovery. It rewinds the entire database and requires downtime. Flashback Technology such Flashback Transaction or Flashback Query let you analyze and make changes at the object/transaction level, and are designed to assist you in troubleshooting and reversing erroneous changes made to the database. There may be times when Flashback Database is necessary, but consider alternative options first. I hope this clears things up for you slightly.
  • 12. Re: Flashback Database Scenario
    moslee Newbie
    Currently Being Moderated
    Thanks for clearing things up here. Once again, I found myself learning again. Great! =)
  • 13. Re: Flashback Database Scenario
    Justin_Mungal Journeyer
    Currently Being Moderated
    Glad I could be of help.
  • 14. Re: Flashback Database Scenario
    moslee Newbie
    Currently Being Moderated
    Hi rp0428

    So in your opinion, in what kind of use case will we be better of by using flashback database?
1 2 Previous Next

Legend

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