OCA/OCP Oracle Database 11g All-in-One Exam Guide in Software

Creation USS Code 39 in Software OCA/OCP Oracle Database 11g All-in-One Exam Guide

Figure 19-1

on the default of one day Then there is the actual space being taken up by the flashback log files, and the exact time to which the flashback logs could take the database back If the flash recovery area is sized appropriately and the retention target is realistic, then there will be a sensible relationship between the time shown in this query and the current time less the retention target The second query shows the price you are paying for enabling Flashback Database, in terms of the bytes of I/O that it necessitates per hour The top row will always be an incomplete hour, up to the current time In the example, the database was generating about 10MB of flashback data per hour through the night, with higher rates in the late evening and morning The impact of this on performance will need to be discussed with your system administrators, bearing in mind whether the system is I/O bound or not For comparison, the view also shows the I/O related to normal database activity The view will have one row per hour The size of the flashback buffer is outside the DBA s control, but to see the current size, you can query the V$SGASTAT view:

SQL> select * from v$sgastat where name = 'flashback generation buff'; POOL NAME BYTES ------------ -------------------------- ---------shared pool flashback generation buff 3981204

Flashback Database can also be monitored through Database Control Navigate to the Configure Recovery Settings window as in Exercise 19-1, and you will see the equivalent information, with the exception of the flashback buffer size

There are three interfaces to Flashback Database: SQL*Plus, RMAN, and Database Control Whichever tool you choose to use, the method is the same: Shut down the database Mount the database Flash back to a time, an SCN, or a log switch sequence number Open the database with RESETLOGS Provided that all archive logs required are available, a flashback operation will proceed completely automatically PART III

The SQL*Plus flashback syntax will accept either a timestamp or a system change number argument unlike RMAN, it will not accept a date nor a log switch sequence number If you are not sure exactly what time you need to go back to (you will be very fortunate if you know the exact timestamp or SCN), you can have several attempts, by combining flashback with recovery Consider this scenario: It is 20 December 2008 At about 10:00 a junior DBA dropped an important schema on the production database This is terrifyingly easy to do: perhaps during an upgrade of software on a development system when connected to the production database by mistake The error is noticed within ten minutes, but it is a big busy database in a call center, used for taking orders, and every second of processing counts The first step is to shut down the database:

SQL> shutdown abort;

There is no point in using any other type of shutdown all work in progress is going to be lost anyway, and you need to minimize the downtime Then take the database back to 10:00 as follows:

SQL> startup mount; SQL> flashback database to timestamp to_timestamp('20-12-08 10:00:00','dd-mm-yy hh24:mi:ss'); SQL> alter database open read only;

Note that unlike RECOVER DATABASE UNTIL TIME, this command is sensitive to NLS settings for the timestamp format While in READ ONLY mode, you can run a query against the dropped schema If you discover that the schema is still there, perhaps you can recover a bit more user data:

SQL> SQL> SQL> SQL> shutdown abort; startup mount; recover database until time '2008-12-20:10:02:00'; alter database open read only;