Shulou(Shulou.com)06/01 Report--

The case of Oracle Study-data recovery artifact Flashback (2)

1. Flashback Table:

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

Case study:

1. Based on SCN (the time point and SCN of DML operation can be found through logminer)

Simulation test environment: SQL > select current_scn from vault database alternate curent SCN-126417907 SQL 16 SQL > select * SCN EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO-- -7369 SMITH CLERK 7902 17-DEC-80 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 30 7369 SMITH CLERK 7902 17 -DEC-80 80020 7499 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 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.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 SQL > select * from test EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO-- -7369 SMITH CLERK 7902 17-DEC-80 80020 fallback via flashback table: 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 fallback table must support 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 CLERK 7902 17-DEC-80 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 30 7369 SMITH CLERK 7902 17 -DEC-80 80020 7499 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 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.--- fallback successful!

2. Based on timestamp (the time point and SCN of DML operation can be found 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 03-18 04 flashback table scott.emp1 to timestamp to_timestamp 50 hh34: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 1 250 500 30 7 566 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 00:00:00 2850 30 7782 CLARK MANAGER 7839 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 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 3 000 20 7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 1014 rows selected.1. Table recovery based on undo needs to pay attention to the influence of DDL operation

The third is to DDL the table after modifying and submitting the data, including:

Drop/modify columns, move tables, drop partitions (if any), truncate table/partition, these operations will invalidate the undo data in the undo tablespace, and applying flashback query to tables that have performed these operations will trigger an ORA-01466 error. Although some other table structure modification statements do not affect the undo records in the undo table space, it is possible that the redo records in undo cannot be applied due to table structure changes, for example, for additional constraints, and the undo records queried by flashback query no longer meet the new constraints, it is obviously impossible to restore directly at this time, you can either temporarily disable constraints, or through appropriate logic After the data to be recovered is processed, the recovery is performed.

In addition, flashback query is not valid for dynamic performance views such as vroomtables.xroomtables, but it is valid for data dictionaries such as dba_*,all_*,user_*. This feature also fully supports access to remote databases, such as select * from tbl@dblink as of scn 360;.

two。 In undo-based table recovery, flashback table actually does dml operations (dml locks are added to the tables being operated), so we also need to pay attention to the impact of triggers on it.

By default, flashback table to scn/timestamp automatically disable the triggers that differs from its operation table when it is executed. If you want the trigger to continue to function during this period, you can append it to flashback table.

ENABLE TRIGGERS clause.

II. Flashback Drop

In the actual development and maintenance, we sometimes encounter the situation of dropping the data table drop. In the past, we can only make incomplete backups through the backups we kept before. This kind of work is very heavy and troublesome. Starting from Oracle10g, the mechanism of the Recycle Bin is introduced to save the data tables dropped by drop in the Recycle Bin. When an erroneous deletion is found, the data table can be recycled through the Recycle Bin.

The recycle bin mechanism is similar to our recycle bin on Windows. In windows, when we choose to delete a file, we essentially don't delete the file from the hard drive, but just rename the file in a form so that we can see it in the Recycle Bin.

The same principle is used in Oracle's Recycle Bin. Let's do a simple experiment.

First, determine the system parameters. In Oracle10g, there is a parameter recyclebin that controls the startup and shutdown of the data table recycle bin mechanism.

Log in with your sys account and determine the recyclebin parameters:

SQL > show parameter recyclebin

NAME TYPE VALUE

Recyclebin string on / / when the value is on, the Recycle Bin feature is enabled

Case study:

1) 06:52:29 SQL > select * from tab TNAME TABTYPE CLUSTERID---DEPT TABLEEMP TABLEBONUS TABLESALGRADE TABLETEST TABLET01 TABLET02 TABLE7 rows selected.06:52:31 SQL > drop table T01 Table dropped. Check the Recycle Bin: 06:52:38 SQL > show recycle ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME -- T01 BIN$qrJLbL74ZgvgQKjA8Agb/A==$0 TABLE 2011-08-17 06RV 52Rd 38-except for the system tablespace The rest of the tablespaces have a similar windows Recycle Bin, where drop table actually renames table and puts it into 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---DEPT TABLEEMP TABLEBONUS TABLESALGRADE TABLETEST TABLET01 TABLET02 TABLE7 rows selected.06:54:11 SQL > drop table T02 purge / / purge will completely delete table Table dropped.06: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 -- T01 BIN$qrJLbL75ZgvgQKjA8Agb/A==$0 TABLE 2011-08-17 purge recyclebin 0615 551 SQL > purge recyclebin Recyclebin purged.06:55:57 SQL > show recycle 06:55:59 SQL >-how to restore table06:56:32 SQL > drop table test with the same name under the same schema 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---DEPT TABLEEMP TABLEBONUS TABLESALGRADE TABLEBIN$qrJLbL76ZgvgQKjA8Agb/A==$0 TABLETEST TABLE6 rows selected.06:56:50 SQL > show recycle ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME -- TEST BIN$qrJLbL76ZgvgQKjA8Agb/A==$0 TABLE 2011-08-17 flashback table test to before drop 0615 56 flashback table test to before drop 5606 56 SQL 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 users:-recyclebin does not exist in system tablespace The table is deleted directly 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 Flashback Table Recycle Bin-with regard to the use of three views, the flashback feature also focuses on two Recycle Bin views. User_recyclebin 、 all_recyclebin 、 dba_recyclebin .

The so-called flashback drop is a kind of object fake deletion technology. When the system parameter recyclebin is set to on, Oracle turns on the flashback drop function. When using drop on a datasheet, Oracle does not delete the object directly, but uses the object rename. Rename the deleted data table (logically) to an encoding at the beginning of BIN$. This code occupies all the resources of the original data table (including object information and storage information), but does not count as the equivalent of the original object. That is, if we directly manipulate the name change, the system will report an error because Oracle does not think the object is a data table. The data dictionary information of the data table T is deleted, while the renamed object information is not added.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.