Case study of OracleStudy-data restoration artifact Flashback (2)

Case study of OracleStudy-data recovery artifact Flashback (2) I. FlashbackTable: For DML misoperations, you can use Undoblock to roll back the table (two modes: time-based and SCN-based) Case Analysis: 1. simulation test environment: SQLselectcurre Based on SCN (you can find the DML operation time point and SCN through logminer)

Case Study of Oracle Study-data recovery artifact Flashback (2) I. Flashback Table: For DML misoperations, you can use the Undo block to roll back the Table (two modes: time-based and SCN-based) case Analysis: 1. simulation test environment: SQLselectcurre Based on SCN (DML operation time point and SCN can be found through logminer)

Case Study of Oracle Study-Flashback (2)

1. Flashback Table:

For DML misoperation, you can use the Undo block to roll back the table (two modes: time-based and SCN-based)

Case Analysis:

1. Based on SCN (you can find the DML operation time point and SCN through logminer)

Test Environment Simulation: SQL> select current_scn from v $ database; CURRENT_SCN ----------- 126417907: 16: 18 SQL> select * from test; empno ename job mgr hiredate sal comm deptno ---------- --------- ---------- -------------- ---------- 7369 smith clerk 7902 CLERK 800 20 7499 allen salesman 7698 CLERK 1600 300 smith clerk 7369 17-DEC-80 7902 20 800 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 ward salesman 7698 22-FEB-81 1250 500 30 7566 jones manager 7839 02-APR-81 2975 20 7654 martin salesman 7698 28-SEP-81 1250 1400 30 7698 blake manager 7839 01-MAY-81 2850 30 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7788 scott analyst 7566 19-APR-87 3000 20 7839 king president 17-NOV-81 5000 10 7844 turner salesman 7698 08-SEP-81 1500 0 30 7876 adams clerk 7788 23-MAY-87 1100 20 7 900 james clerk 7698 03-DEC-81 950 30 7902 ford analyst 7566 03-DEC-81 3000 20 7934 miller clerk 7782 23-JAN-82 1300 1016 rows selected.07: 16: 23 SQL> delete from test; 16 rows deleted.07: 16: 50 SQL> commit; Commit complete.07: 16: 52 SQL> select * from test; no rows selected07: 16: 57 SQL> insert into test select * from emp where rownum = 1; 1 row created.07: 17: 17 SQL> commit; Commit complete.07: 17: 19 SQ L> select * from test; empno ename job mgr hiredate sal comm deptno ------------ ----------- ---------- 7369 SMITH cler7902 17-DEC-80 800 20 through flashback table rollback: 07:17:21 SQL> flashback table test to scn 1264179; flashback table test to scn 1264179 * ERROR at line 1: ORA-08189: cannot flashback the table because row movement is not enabled rollback table must be supported Row movement: 07: 17: 41 SQL> alter table test enable row movement; Table altered.07: 18: 01 SQL> flashback table test to scn 1264179; Flashback complete.07: 18: 05 SQL> select * from test; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- --------- ------------ ---------- 7369 SMITH cler7902 17-DEC-80 800 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 7 369 smith clerk 7902 17-DEC-80 800 20 7499 allen salesman 7698 20-FEB-81 1600 300 30 7521 ward salesman 7698 22-FEB-81 1250 500 7566 30 7839 jones manager 2975 02-APR-81 7654 20 7698 martin salesman 1250 28-SEP-81 1400 7698 30 blke MANAGER 7839 01-MAY-81 2850 30 7782 clark manager 7839 09-JUN-81 2450 10 7788 scott analyst 7566 19-APR-87 3000 20 7839 king president 17-NOV-81 5000 10 7844 TURNER SA LESMAN 7698 08-SEP-81 1500 0 30 7876 adams clerk 7788 23-MAY-87 1100 20 7900 james clerk 7698 03-DEC-81 950 30 7902 ford analyst 7566 03-DEC-81 3000 20 7934 miller clerk 7782 23-JAN-82 1300 1016 rows selected. --- rollback successful!

2, Based on timestamp (you can find the DML operation time point and SCN through logminer)

05:43:31 SQL> delete from scott.emp1;14 rows deleted.05:44:25 SQL> flashback table scott.emp1 to timestamp to_timestamp('2011-03-18 04:50:00','yyyy-mm-dd hh24:mi:ss');Flashback complete.05:44:32 SQL> select * from scott.emp1;     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------      7369 SMITH      CLERK           7902 1980-12-17 00:00:00        800                    20      7499 ALLEN      SALESMAN        7698 1981-02-20 00:00:00       1600        300         30      7521 WARD       SALESMAN        7698 1981-02-22 00:00:00       1250        500         30      7566 JONES      MANAGER         7839 1981-04-02 00:00:00       2975                    20      7654 MARTIN     SALESMAN        7698 1981-09-28 00:00:00       1250       1400         30      7698 BLAKE      MANAGER         7839 1981-05-01 00:00:00       2850                    30      7782 CLARK      MANAGER         7839 1981-06-09 00:00:00       2450                    10      7788 SCOTT      ANALYST         7566 1987-04-19 00:00:00       3000                    20      7839 KING       PRESIDENT            1981-11-17 00:00:00       5000                    10      7844 TURNER     SALESMAN        7698 1981-09-08 00:00:00       1500          0         30      7876 ADAMS      CLERK           7788 1987-05-23 00:00:00       1100                    20      7900 JAMES      CLERK           7698 1981-12-03 00:00:00        950                    30      7902 FORD       ANALYST         7566 1981-12-03 00:00:00       3000                    20      7934 MILLER     CLERK           7782 1982-01-23 00:00:00       1300                    1014 rows selected.

1. undo-based table recovery. Pay attention to the impact of DDL operations.

The third step is to perform DDL operations on the table after the data is modified and submitted, including:

Drop/modify column, move table, drop partition (if any), truncate table/partition. These operations will invalidate the undo tablespace data revocation, applying flashback query to tables that have performed these operations triggers a ORA-01466 error. In addition, although some table structure modification statements do not affect the undo tablespace revocation record, it is possible that the record duplication in the undo table cannot be applied due to table structure modification. For example, if a constraint is added, however, the undo record queried by flashback does not meet the new constraints, and direct recovery is obviously not successful at this time. You must either temporarily disable the record or use appropriate logic, after the data to be restored is processed, the data is restored.

In addition, flashback query is invalid for dynamic performance views such as v $ tables and x $ tables, but it is effective for data dictionaries such as dba _ *, all _ *, and user. At the same time, this feature fully supports accessing remote databases, such as select * from tbl @ dblink as of scn 360.

2. for undo-based table restoration, flashback table actually performs dml operations (dml locks will be applied to the operated table). Therefore, pay attention to the impact of triggers on it.

By default, flashback table to scn/timestamp will automatically disable triggers that are different from the operation table during execution. If you want the trigger to continue using the triggers during this period, you can attach

ENABLE TRIGGERSClause.

Ii. Flashback Drop

650) this. width = 650; "src =" http://www.68idc.cn/help/uploads/allimg/151111/1215545616-0.jpg "title =" f2.png "alt =" wKiom1S2J6LTWazuAAMgwbErcYQ756.jpg "/>

In actual development and maintenance, data tables are sometimes dropped. In the past, we can only useBackupTo perform incomplete backup. This workload is huge and troublesome. The recycle bin mechanism has been introduced since Oracle10g to save drop data tables in the recycle bin. When a data table is deleted by mistake, you can recycle it from the recycle bin.

The recycle bin mechanism is similarWindows. In windows, when we choose to delete a file, the file is not deleted from the hard disk in essence, but the file is renamed in one form, so that you can see it from the recycle bin.

Oracle's recycle bin adopts the same principle. Next we will make a simpleLab.

First, determine the system parameters. In Oracle10g, there is a parameterRecyclebinTo control the startup and shutdown of the data table recycle bin mechanism.

Log On with the sys account and confirm the recyclebin parameter:

SQL> show parameter recyclebin;

NAME TYPE VALUE

-----------------------------------------------------

Recyclebin string on // when the value is on, the recycle bin function is enabled;

Case Analysis:

(1) 06:52:29 SQL> select * from tab; tname tabtype clusterid certificate ------- ---------- dept tableemp tablebonus tablesalgrade tabletest TABLET01 TABLET02 TABLE7 rows selected.06: 52: 31 SQL> drop table t01; Table dropped. view the recycle bin: 06: 52: 38 SQL> show recycle; ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME -------------------------------------------------------------------------- --- T01 BIN $ q1_lbl74zgvgqkja8agb/A = $0 TABLE 2011-08-17: 06: 52: 38 -------- in addition to the system tablespace, all the other tablespaces have A similar windows recycle BIN, In the drop table, actually, rename the table and put it in recyclebin. 06:52:44 SQL> flashback table t01 to before drop; Flashback complete.06: 54: 05 SQL> show recycle; 06:54:07 SQL> select * from tab; tname tabtype clusterid certificate ------- ---------- dept tableemp tablebonus tablesalgrade tabletest TABLET01 TABLET02 TABLE7 rows selected.06: 54: 11 SQL> drop table t02 purge; // purge deletes table Table partition: 54: 54: 40 SQL> show recycle; ----------- clear Recyclebin06: 54: 43 SQL> drop table t01; Table dropped.06: 55: 49 SQL> show recycle; original name recyclebin name object type drop time ---------------- hour ------------ ------------------- T01 BIN $ hour/A ==$ 0 TABLE: 06: 55: 4906: 55: 51 SQL> purge recyclebin; recyclebin purged.06: 55: 57 SQL> show recycle; 06:55:59 SQL> -------------- how to restore a tab with the same name under the same schema Le06: 56: 32 SQL> drop table test; Table dropped.06: 56: 42 SQL> create table test as select * from emp; Table created.06: 56: 46 SQL> select * from tab; tname tabtype clusterid certificate ------- ---------- dept tableemp tablebonus tablesalgrade tablebin $ tables/A ===$ 0 TABLETEST TABLE6 rows selected.06: 56: 50 SQL> show recycle; ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME ---------------- ------------------------------ ----------------------- test bin $ tables/A = $0 TABLE: 06: 56: 3606: 56: 58 SQL> flashback table test to before drop; flashback table test to before drop * ERROR at line 1: ORA-38312: original name is used by an existing object06: 57: 09 SQL> flashback table test to before drop rename to test_old; flashback complete.06: 57: 32 SQL> select * from tab; TNAME TABTYPE CLUSTERID ------------------------------ ------- ---------- DEPT TABLEEMP TABLEBONUS TABLESALGRADE TABLETEST_OLD TABLETEST TABLE6 rows selected. flashback Drop does not support sys User: ---- the system tablespace does not have recyclebin. The table is directly deleted at 06:57:36 SQL> conn/as sysdba Connected.06: 58: 33 SQL> 06:58:33 SQL> create table test as select * from user_tables; Table created.06: 58: 42 SQL> drop table test; Table dropped.06: 58: 46 SQL> show recycle; flash back to the Table recycle bin-three views, the flash back feature also focuses on two recycle bin views. User_recyclebin, all_recyclebin, and dba_recyclebin.

The so-called flashback drop is a type of false object deletion.Technology. When the system parameter recyclebin is set to on, the flashback drop function is enabled in Oracle. When you use drop for a data table, Oracle does not directly Delete the object, but uses the object name. Rename the deleted data table (logically) and change it to an encoding starting with BIN $. This encoding occupies all resources (including object information and storage information) of the original data table, but cannot be considered as the equivalent of the original object. That is to say, if we directly modify the name, the system will report an error because Oracle does not consider this object as a data table. The data dictionary information of table T is deleted, but the renamed object information is not included.