Flashback Database

[Former Member]

All,

I was reading a note ( 966117 : Oracle Database 10g ; Flashback Database ) about Flashback database. Has anyone used this feature of Oracle with SAP ? It would be great if you can share the pros and cons of enabling this in an SAP environment

Thanks

Prince Jose

[Former Member · Accepted solution]

Morning

I think flashback rules, i did not see it was finally supported by SAP. I will definitely give it a try soon. I can post my findings here.

Regards Michael

[Former Member · Accepted solution]

SAP supports Flashback database now .. check notes 966117, 966073. If you are trying pls post your findings.

Thanks

Prince Jose

[Former Member · Accepted solution]

Hello again

I was quite busy with work the last days, more than expected, so I didn't find a minute to look into the flashback issue. Well until today...

Scenario:

- SAP ERP2005 (NW 7.0) with a approx. 200gb database

- take a guaranteed restore point (GRP)

- apply support packages SAPKB70008 - SAPKB70010 and SAPKA70008 - SAPKA70010

- go back to the grp

Findings:

- during the patch run, roughly 4gb redo log was generated and 4.1gb where used in the flashback area

- the setup for the flashback is quite easy, no challenge for the average DBA or for BASIS folks with good Oracle knowlegde

- the time needed for the flashback was below 3 minutes !!! Compare that to your normal restore + recovery time.

Tue Mar 11 15:29:14 2008
Database mounted in Exclusive Mode
Completed: alter database mount
Tue Mar 11 15:29:24 2008
alter database recover datafile list clear
Tue Mar 11 15:29:24 2008
Completed: alter database recover datafile list clear
Flashback Restore Start
Tue Mar 11 15:32:02 2008
Flashback Restore Complete
Flashback Media Recovery Start
 parallel recovery started with 3 processes
Flashback Media Recovery Log /oracle/SID/saparch/SIDarch1_3072_594406689.dbf
Tue Mar 11 15:32:10 2008
Incomplete Recovery applied until change 1680735783066
Flashback Media Recovery Complete
Tue Mar 11 15:33:20 2008
alter database open resetlogs

I agree with Eric's points, in my scenario (support packages) you probably would need to switch the SAP kernel back to the original one, if you created SPDD/SPAU requests, they will stay in your TRANSDIR. But this applies to a restore/recovery procedure as well!

I think you can use flashback to:

- have a fast way to switch back to a good system state after maintenance gone wrong

- on a test system to go back to a defined config for big test runs, actually you can repeat the same tests without preparing the data or cleaning up

- on educational systems to set the system back to its original state after a training

- if you use 'Normal' flashback logging, you can go back whenever you want, avoiding time consuming restores

I think this feature seriously kicks ass. The only additional requirement is off course the needed disk space for the oraflash filesystem. I did not do any performance tests so far, but i don't think the additional IO does any harm.

Regards Michael

[former_member204746 · Accepted solution]

in a support pack strategy, this really looks interesting. I will surely consider it in my next SP update..

[Former Member · Accepted solution]

Thanks MHO for sharing your experience. It really helps when cones to support pack application. Please le us know your finding about performance

Thanks

Prince Jose

[Former Member]

This is an excellent substitute for point-in-time recovery. This was the only option available for incomplete restores for Oracle 8i/9i databases. This required a fair amount of down-time to perform.

Flashback database will not restore databases if media failures occur, however, it will restore databases to a prior point-in-time if logical and/or user errors occur. It does come at a cost, considering the additional space and I/O needed. With that being said, it might not be ideal for a large OLTP production database. However, this is perfect for a QA environment that performs a lot of testing and requires the ability to rollback to base-line data for additional testing. This feature easily accommodates this type of environment.

It’s fast - recovers in minutes, not hours. More over, this feature removes the need for database incomplete recoveries that require physical movement of datafiles/restores. A database can be restored with a simple SQL command.

SQL> Flashback Database to scn 1329643

A new background process is introduced – RVWR and a flash_back_area is needed to store the changed data written by RVWR.

Restrictions

• Not used for Media failure errors. Used for Logical/User errors.

• The database control file has been restored or re-created.

• Previous tablespace has been dropped.

• The database data file that contains the object to be queried has been shrunk.

• A recovery through the resetlogs command has occurred.

Views for Monitoring flashback database logs

• V$Database

• V$Flashback_Database_Log

• V$Flashback_Database_Stat

[Former Member]

Shaji,

Your answer looks like you havent put any effort to read the question. I didnt ask what Flashback Database is .When you [ plagiarize |http://www.oracle-base.com/links/10g_new_features.doc] next time , please make sure that it is somehow realted to the question.

Thanks

Prince Jose

[former_member204746]

I found another great usage for Flashback technology.

in the past, in another company, they were making client copies every weekend to refresh the training system. This would take roughly 15 hours to run because they were refreshing 5 clients.

with flashback technology, it would be much easier to go back 1 week with this instead of a weekly client copy.

[Former Member]

Thats right , If we have lot of training going on then it saves lot of time .. may be SAP might be using this for their trainigs as well !! . The other area which I am concerned is Performance and the real use of flashback in Production systems ?

Thanks

Prince Jose

[former_member204746]

I don't see a lot of advantages. You have thousands of user updating the database, if you flashback, you lose the work of thousansd of users. this does not sound like a reasonable solution.

uit may be very interesting for smaller databases but not for huge ones.

[Former Member]

Thanks Eric,But how about when applying Support packs and upgrade. For ex. If something goes wrong when applying support pack and want to go back , I think it saves time if we have a Guaranteed restore point ( GRP ) than restoring the database .. But really needs to know the administration cost vs the time !!

Thanks

Prince Jose

[former_member204746]

this could be a good idea, but yours still have log files and other files that are stored in /usr/sap/trans ... they are not part of flashback technology.

[Former Member]

if the transport directory is on it's own disk you may think about using shadow copies on Windows.

But you will still have the problem that a transport directory is typically a directory shared by several systems and you will still not be able to reset the changes of only one of them.

peter