I begin thinking about the topic while walking through restoring log backup files for SQL Server and longing for a third party tool. Oracle Flashback is a feature in Oracle database that bypasses such steps by allowing an
administrator to flashback pieces of the database to a point in time. Unlike SQL Server there is an
independent flashback logging process that runs in addition to the archive logs
in Oracle (akin to mssql logs). The best benefit of flashback is it can be done with different
levels of granularity including table, row-level and version-level (using
oracle IDs) that enables the correction of human errors as well as data
corruptions. So in essence during a window between archive log switches or
checkpoints, another layer of logging for flashback can capture activity in the
database. This is supported by supplemental logging and undo set aside for extra processing in the redo logs to track changes. This capability lends itself well in sectors such as finance, healthcare,
and education where data accuracy and consistency is just as important as recoverability and reporting/analytics. Downside: although the performance
overhead is minimal (in a perfect world) you will need storage space set aside for
all those logs. There is a "self-awareness" to flashback that will help an admin estimate flashback recovery area sizes based on activity. Using the recommendations an upper limit can be set so that if your volume maxes out, new flashback logs will begin overwriting in a last in, first out fashion. As with all things Oracle there are also separate utilities, RMAN and OEM, for managing recovery processes and performance.
In comparison with SQL Server an administrator
would have to use create a duplicate database to roll back the hands of
time and find human error. Changes to a table would require an import process from the duplicate. A working around could be a change tracking table requiring manual setup in SQL. The best methods for maintaining secondary instances of older versions of data include database snapshots (i.e. review the last two hours) or more recently replica
databases in SQL 2012 enterprise edition. The replica database instance is a
part of the new AlwaysOn HighAvailabiltiy group feature. This is the other end of the spectrum to the un-sophisticated restoring of backups and multiple log files another database. To be fair in
Oracle's dataguard this would be akin to manually copying archive logs to a standby and opening the database read-only. In SQL Server the structure is different but in concept log files and full recovery mode (archivelog mode in Oracle) supports point-in-time recovery; its just not a real-time option. Also there are really no built-in automated features in SQL Server to limit growth and storage usage. SQL Agent jobs and maintenance plans have to be created to manually truncate logs using IF..EXISTS..THEN scripts for "emergency" maintenance at some threshold. Also SQL Agent alerts can be scheduled much like OEM with thresholds for critical states. Customize alerting using scripts or triggers can take place with built-in "sp_" and "DBMS_" stored procedures in both products as linked here if you want to read more. I will step through Oracle point-in-time recovery scenarios, pluggable databases and monitoring options mentioned in this article in Part 2.
