Beiträge getaggt mit flashback
Flashback Part 3
In this last part about Flashback, we will focus on flashback database, which is again based on a completely different architecture than the other two kinds of flashback discussed before. The main difference compared to the other flashback types is, that you move your entire database back to a point in the past, with exactly the same outcome as you were able to achieve long before version 10g with a Database Point In Time Recovery (DBPITR), but with a much faster (and even easier) method. But before you can take advantage of this much faster DBPITR, you must invest in form of writing flashback logs, which is not done by default. You may turn on flashback logging as follows:
SQL> select flashback_on from v$database;
FLASHBACK_ON ------------------ NO
SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started.
Total System Global Area 373293056 bytes Fixed Size 1219496 bytes Variable Size 134218840 bytes Database Buffers 234881024 bytes Redo Buffers 2973696 bytes Database mounted. SQL> alter database flashback on; alter database flashback on * ERROR at line 1: ORA-38706: Cannot turn on FLASHBACK DATABASE logging. ORA-38707: Media recovery is not enabled.
SQL> alter database archivelog;
Database altered.
SQL> alter database flashback on;
Database altered.
SQL> alter database open;
Database altered.
SQL> select flashback_on from v$database;
FLASHBACK_ON ------------------ YES
SQL> show parameter db_recovery_file_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /u01/app/oracle/flash_recovery
_area
db_recovery_file_dest_size big integer 2G
SQL> alter system set db_recovery_file_dest_size=10g;
System altered.
Please notice that you must be in Archive Mode before turning on flashback logging and that the flashback logs are being written into the Flash-Recovery-Area as well as the Archive Logs. You may need to increase the amount of space reserved in the Flash-Recovery-Area for your database, since the default reserved size of 2G is very small.
The amount of space needed for your flashback logs is based on two factors: The frequency in which blocks are being modified and the point in time, that you want to be able to flashback your database to. The latter is defined by the dynamic parameter db_flashback_retention_target. Also, you can see an estimation of the amount of space needed for your flashback logs with the dynamic performance view v$flashback_database_log:
SQL> show parameter flashback
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_flashback_retention_target integer 1440
SQL> select ESTIMATED_FLASHBACK_SIZE/1024/1024 as mb from v$flashback_database_log;
MB ---------- 157.289063
SQL> alter system set db_flashback_retention_target=60 -- unit is minutes ;
System altered.
SQL> select ESTIMATED_FLASHBACK_SIZE/1024/1024 as mb from v$flashback_database_log;
MB ---------- 6.46875
Now let us assume, that you are just about doing some critical operation with your database, for example an application upgrade that changes contents of many tables. You want to be able to undo these changes if necessary as quick as possible on the database level. That is why you turned on flashback as described above. Additionally, you may create a restore point now (a label associated with the current SCN and stored in the Database Controlfile), to flashback to instead of having to provide a timestamp or even a SCN. The scenario might go like this:
SQL> create restore point before_critical_op;
Restore point created.
SQL> select * from v$restore_point;
SCN DATABASE_INCARNATION# GUA STORAGE_SIZE
---------- --------------------- --- ------------
TIME
-------------------------------------------------
NAME
-------------------------------------------------
618103 2 NO 0
10-MAR-09 08.36.29.000000000 AM
BEFORE_CRITICAL_OP
SQL> drop user hr cascade -- oops! Mistake... ;
User dropped.
SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started.
Total System Global Area 373293056 bytes Fixed Size 1219496 bytes Variable Size 138413144 bytes Database Buffers 230686720 bytes Redo Buffers 2973696 bytes Database mounted. SQL> flashback database to restore point before_critical_op;
Flashback complete.
SQL> alter database open resetlogs;
Database altered.
SQL> select username from dba_users where username='HR';
USERNAME ------------------------------ HR
SQL> drop restore point before_critical_op;
Restore point dropped.
Please notice that the flashback database is done in Status mount and ends with an open resetlogs as both would be the case with a regular DBPITR. But we are way faster now, because we do not have to do a complete restore of all our datafiles from backup! In fact, what has happend under the covers during this operation was a restore of older block versions from the flashback logs over the current blocks in the datafiles that were too new for the point in the past, we wanted the database to be flashed back to.
Because not every block change leads to the writing of that block to the flashback logs (that would not be performant), there is still something to be actualized to reach the exact point in the past. This is done internally in form of recovery with redo protocol from archive logs or online logs, which is why you can only use flashback database together with archiving.
Flashback Part 2 11g Addendum
As mentioned before, we can query the content of a table from the past (since 9i with flashback query) and even reinstate the past content of that table (since 10g with flashback table to timestamp) by making additional use of before images stored in the undo tablespace. Of course, this is only possible as long as these before images in the undo tablespace are not overwritten by new ones.
A new feature of the 11g version is to safe the information from the before images for selected tables, thereby enabling a flashback query or flashback table to timestamp for these selected tables even years after their modification. That is called Total Recall and works like this:
SQL> create flashback archive fla1 tablespace tbs1 retention 10 year;
SQL> alter table hr.departments flashback archive fla1;
You will now be able to do a flashback query for the departments table in 10 years until today and to any time in between!
Flashback Part 2
To introduce the second kind of flashback, let me first present you a seemingly trivial scenario: Open one session against your Oracle Database, take a table of your choice and make an update of one row of it without commit. Then open a second session and select the rows of that table.
Please notice two things: 1. The query does not wait for the first session to commit. 2. The query shows a consistent result set with the old value of the row that you updated in the first session. This is called read consistency and is delivered by Oracle Databases since version 6 without the need for the select to lock rows in the table. We make that possible by retaining the old value of the updated row (called before image) in undo segments. Since the 9i version, we make additional usage of these before images; in 9i you can do a flashback query like this:
select * from t as of timestamp systimestamp – interval ’15‘ minute;
That select shows the content of table t as it was before 15 minutes – provided that the before images to reconstruct this content are still present in the undo segments. 10g takes that approach one step further by making it possible to do a „table point in time recovery“ with the old values of the table reconstructed from the before images. The command goes
alter table t enable row movement;
flashback table t to timestamp systimestamp – interval ’15‘ minute;
You can also provide a comma separated list of tables in that statement to flashback all the tables that belong together. This can be extremely useful and works very fast and easy compared to the old cumbersome and time consuming methods to achieve the same result!
If you just want to investigate who made exactly what kind of logical mistake, you can achieve that with Flashback Transaction Query – also a 10g New Feature and explained here.
Pickleball spielen auf YouTube
Pickleball spielen 