Incomplete recovery of flashback

Flash recovery is incomplete recovery and Incomplete recovery. It is fast and simple. The disadvantage is that it can only process logic, physical errors such as deletion of data files cannot be processed (the records in the table space in the controlfile also disappear after the tablespace is deleted, so they cannot be flashed back ). The opposite of flashback is restoration recover. The Flashback Database function is very similar to the incomplete recovery of RMAN. It can roll back the entire Database to a certain time point in the past. This function depends on the Flashback log. Faster and more efficient than RMAN. Therefore, Flashback Database can be seen as an alternative to Incomplete recovery. But it also has some limitations: 1. the Flashback Database cannot solve the Media Failure. Therefore, RMAN recovery is still the only option. if you delete a data file or use the Shrink technology to reduce the size of the data file, you cannot use the Flashback Database technology to roll back to the previous state, at this time, you must first use RMAN to back up and restore the files before or after deletion, and then use Flashback Database to execute the remaining Flashback Datbase. 3. If the control file is recovered from the backup or the control file is rebuilt, Flashback Database cannot be used. Otherwise, the error "ORA-38727: flashback database requires the current control file ." 4. The oldest SCN that can be recovered using the Flashback Database lock depends on the oldest SCN recorded in the Flashback Log. Flashback Database architecture the entire Flashback Database architecture includes a process Recover Writer (RVWR) background process, Flashback Database Log and Flash Recovery Area. Once the Flashback Database is enabled for the Database, the RVWR process starts and writes the Flashback Database Log to the Flash Recovery Area, these logs include the "before image" of the data block, which is also the reason why the Flashback Database technology does not completely restore the block. Application prerequisites a, archive mode B, flashback_on start SYS @ jsce> select flashback_on from v $ database; FLASHBACK_ON------------------NO show parameter retention -- 1440 points, that is, one day users Modify Table data, all are recorded in the Undo tablespace, which provides the basis for data recovery for the flashback of the table. For example, a modification operation is recorded in the Undo tablespace after submission, and the retention time is 900 seconds. You can perform a flashback operation on the table within 900 seconds, in this way, the data in the table is restored to the state before modification. Q: Can it be longer if no other operations are performed within S? V $ flashback_database_log: How long is the flashback? 1. Check the current scn SYS @ ncbeta> select current_scn from v $ database; CURRENT_SCN ----------- 3625299 Delete the emp table under scott -- the ddl statement itself implies commit, and the drop table does not need to flash back to the database. An error is returned when the emp is retrieved, you need to mount (basically all data recovery needs to be mounted) SYS @ ncbeta> flashback database to scn 3625299; after opening, You Need to resetlogs to return to the time: flashback database to timestamp to_timestamp ('12-01-1315: 26: 34', 'yy-mm-dd hh24: mi: ss'); scoot emp recovery supplement: s The conversion between cn and time can be as follows: SYS @ ncbeta> Select to_char (scn_to_timestamp (3625299), 'yyyy-MM-DDHH24: MI: ss') from dual; TO_CHAR (SCN_TO_TIME-------------------2013-01-21 11:10:54 II. Flashback table this operation does not require mount principle: Restore from undo_tablespace first to start the table row movement feature SCOTT @ ncbeta> select ROW_MOVEMENT fromuser_tables where table_name = 'emp '; ROW_MOVE--------DISABLED SCOTT @ ncbeta> alter table emp enable row movement; the table has changed. In sys, locate the current database's scn: 3632069 and insert the emp table -- 14 rows into 15 rows; xs is newly added and an error is found, flashback table -- flashback is indeed fast SCOTT @ ncbeta> flashback table emp to scn 3632069; Considerations for flashback table 1. the FLASHBACKTABLE command is executed as a single transaction and a single DML lock is obtained. the statistical data of the table will not be flashed back. 3. the current index and subordinate object will be maintained 4 system tables cannot be flashed back 5 cannot cross DDL operation 6 will be written warning log 7 generate cancel and redo data such as 2 statistical data, user_tables has a NUM_ROWS field. After the insert table, the sum of the statistical values does not correspond to the analyze analysis. This problem can be solved by SCOTT @ ncbeta> analyze table emp compute statistics. for deleting a table, t can be used O before drop to flash back SCOTT @ ncbeta> flashback table emp to before drop; after the flash back mode is enabled, the deletion operation is shown in the flash back area. The table to be deleted will be updated only when the rollback segment is full and renamed at the station, if you want to delete it permanently, you can use drop table xx purge. 3. Flashback VERSION Query (query change) flashback query can only view the data in the table at a specific time point, however, data changes in the table between two time points cannot be fully viewed. Flash back VERSION Query provides this function, which provides a function to audit the data changes of each row. It can find all submitted operations, their operation time, and results. Therefore, flashback version query can replace the fine-grained audit function and the LogMiner tool function to some extent. Any existing data in the withdrawal segment can be used for flash back version query. Therefore, the maximum available version depends on the setting of the undo_retention initialization parameter. Because this parameter is not mandatory, If you want to ensure that the data submitted in the Undo segment is not overwritten within the time set by this parameter, you need to set the retention guarantee option for the Undo tablespace. You can query the field changes within a period of time. For example, if the changes are applied to stocks, first query the current scn scott @ ncbeta> select timestamp_to_scn (SYSTIMESTAMP) as scn from dual under SCOTT; SCN ---------- 3634862 at this time empnoename sal ------ ---------- after 22xs 8000 update select sal, versions_starttime, versions_endtime, latency, versions_operation from empversions between scn 3636070 and 3636113 where empno = '22 '; the ORA-30052: lower limit snapshot expression is invalid when testing because the specified time range is not in UNDO_R ETENTION is within the revocation range (for example, you specified the time before 2 hours, and UNDO_RETENTION <7200 ). Note: The undo_retention and retention guarantee undo_retention parameters are used to control the retention time of undo information after the transaction is committed. The undo information can be used to construct the consistent read and for a series of Flash recovery, and enough undo information can also reduce the classic ORA-01555 Snapshot too old (the Snapshot is too old, undo data that has not expired during a long query has been overwritten ). The default value of this parameter is 900 (seconds). This is a noguarantee restriction, that is, after I commit undo_retention = 900, previously, the undo data can be overwritten by other transactions only after 900 seconds. However, when undo space is required for other transactions, at this point, the undo tablespace does not have enough space, and we have not enabled automatic expansion. Because our retention is noguarantee, therefore, the transaction will ignore this retention time limit and directly overwrite our undo information. This result is not expected in many cases. Oracle adds a parameter guarantee after 10 Gb, which can force oracle to protect undo information. That is to say, even if a new transaction requires undo space, even if the undo space is insufficient, this session will not forcibly overwrite the undo information protected by undo_retention, and the newly added transaction will report an error due to the lack of undo space. Retention guarantee in 10 GB: 1. Explain how long undo_retention is set in undo_retention to ensure that the data after commit is retained in the undo segment. However, it is not guaranteed that the undo information after commit will not be overwritten during the undo_retention period. When undo segment is insufficient, it will overwrite the undo information of commit. 2. If you need to ensure that the undo information is not overwritten during the undo_retention time, you can set retention guarantee for the undo segment. However, this parameter is limited by undo_retention and undo size. If the undo size is too small and the limit setting is too long, when setting retention guarantee, an error is reported: ORA-30036: unable to extend segment by 8in undo tablespace 'undotbs2 '3. Set this parameter altertablespace UNDOTBS2 retention guar; undo this parameter altertablespace undotbs2 retention noguarantee; 14:50:45 update: After the update operation, commit is required to write the redo log, and the system can recognize select sal, ename, versions_xid from emp versions between scn minvalue and maxvaluewhere empno = '22' 4. Flashback transaction query the flashback transaction query is actually an extension or continuation of the flashback version query. It provides a way to view changes to transaction-level databases. You usually need to first use the flash back version query to find out the transaction version number, and then perform the flash back transaction query. Flashback_transaction_query select sal, ename, versions_xid from emp versions between scn minvalue and maxvaluewhere empno = '22'; an update error is found. query SQL and undo SQL select xid, start_scn by xid, to_char (start_timestamp, 'yyyy-mm-dd hh24: mi: ss') starttime, undo_sqlfrom region where xid = '06000200f2060000 'andtable_name = 'emp'; undo_ SQL: update "SCOTT ". "EMP" set "SAL" = '000000' where ROWID = 'aaar3saaeaaaactaab'; -- changed to the original 5600