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

Flashback

When the user inadvertently causes the wrong deletion of data, they very much hope that there is a simple and quick way to recover the data. Flashback technology is produced so that users can quickly deal with this kind of data logic corruption.

Flashback technology mainly completes the restoration of historical records by rolling back the UNDO records stored in the segment. After setting ENABLE_FLASHBACK to 1, turn on the flashback function. DM retains the rollback segment for a period of time, and the duration of the rollback segment represents the length of time that can be flashed back. Specified by the UNDO_RETENTION parameter.

SQL > select sf_get_para_value, LINEID SF_GET_PARA_VALUE: 1 0used time: 204.313 (ms). Execute id is 62.SQL > select sf_get_para_value (1 ms); LINEID SF_GET_PARA_VALUE (1 90used time: 6.236). Execute id is 63.

Let's modify the dynamic parameter ENABLE_FLASHBACK,scope=1 and modify both the memory and the dm.ini file

SQL > call sp_set_para_value, DMSQL executed successfullyused time: 13.216 (ms). Execute id is 64.SQL > select sf_get_para_value, LINEID SF_GET_PARA_VALUE: 1 1used time: 5.703 (ms). Execute id is 65.

The default time for the rollback segment is 90 seconds, and we want to modify it to 1 day.

SQL > select sf_get_para_value (1 ms); LINEID SF_GET_PARA_VALUE (1 90used time: 6.236). Execute id is 63.SQL > call sp_set_para_value, DMSQL executed successfullyused time: 7.155 (ms). Execute id is 74.SQL > select sf_get_para_value (1 ms); LINEID SF_GET_PARA_VALUE (1 86400used time: 5.877). Execute id is 75.

When flashback is enabled, DM records the start time and commit time of each transaction in memory. By querying the transaction number of the time specified by the user, and combining the current record and the UNDO record in the rollback segment, the record of the specific transaction number can be restored. That is, the recording status of the specified time. To complete the flashback query. Flashback query function completely depends on rollback segment management, and can not be recovered for misoperations such as DROP. The flashback feature can be applied in the following areas:

1. Repair during self-maintenance: when some important records are accidentally deleted, the user can move back to a point in time, view the missing rows and reinsert them into the current table for recovery

two。 Used to analyze data changes: you can view the changed data by linking queries at different flashbacks of the same table.

Flashback query

When the system INI parameter ENABLE_FLASHBACK is set to 1, the flashback function is turned on, and the flashback query can be performed. Flashback queries are not supported in the MPP environment.

SQL > select sf_get_para_value, LINEID SF_GET_PARA_VALUE: 1 1used time: 5.703 (ms). Execute id is 65. [root@shard1 jydm] # strings dm.ini | grep flashENABLE_FLASHBACK = 1 # Whether to enable flashback function

Flashback query clause

The syntax of the flashback query clause is to add the flashback query clause to the FROM clause on the basis of the data query statement (see Chapter 4).

Grammatical format

:: = WHEN |

Parameters.

1.time_exp A date expression, usually expressed as a string

2.trxid specifies the transaction ID number

Sentence function

The user can get the result set of the specified table at a certain time in the past by flashback query clause. The specified condition can be a time, or a transaction number

instructions

1. Flashback queries only support normal tables (including encrypted and compressed tables), temporary tables and heap tables, but not horizontal partitioned tables, vertical partitioned tables, column storage tables, external tables and views

2. The value of trxid in the flashback query generally needs to be determined by the pseudo column of the flashback version of the query (see the following section). The method of specified time is often used in practical use.

Example 1 flashes back to query the PERSON_ type table at a specific time.

Query the PERSON_ type table.

SQL > SELECT * FROM PERSON.PERSON_TYPE;LINEID PERSON_TYPEID NAME-1 1 Purchasing Manager 2 2 Purchasing Representative 3 3 sales Manager 4 4 sales Representative used time: 15.321 (ms). Execute id is 78.

Record time before inserting data and use it when flashback queries

SQL > select sysdate;LINEID SYSDATE-1 2019-12-01 23:34:12.761683used time: 0.480 (ms). Execute id is 79.SQL > INSERT INTO PERSON.PERSON_TYPE (NAME) VALUES; affect rows 1used time: 0.615 (ms). Execute id is 80.SQL > INSERT INTO PERSON.PERSON_TYPE (NAME) VALUES ('cleaner'); affect rows 1used time: 0.562 (ms). Execute id is 81.SQL > commit;executed successfullyused time: 16.237 (ms). Execute id is 82.SQL > SELECT * FROM PERSON.PERSON_TYPE LINEID PERSON_TYPEID NAME-1 1 Purchasing Manager 2 2 Purchasing Representative 3 3 sales Manager 4 4 sales Representative 5 7 loss Prevention Member 6 8 cleaner 6 rows gotused time: 0.626 (ms). Execute id is 83.

Use a flashback query to get data for the 23:34:12 time of 2019-12-01. At this time, before the operation of inserting data, it can be seen that the result set at this time should not have data inserted after 23:34:12 on 2019-12-01.

SQL > SELECT * FROM PERSON.PERSON_TYPE WHEN TIMESTAMP '2019-12-01 23 purl 3414 12' LINEID PERSON_TYPEID NAME-11 Purchasing Manager 2 2 Purchasing Representative 3 3 sales Manager 4 4 sales Representative used time: 1.416 (ms). Execute id is 84.

Delete the data at 23:39:26 on 2019-12-01 and submit.

SQL > select sysdate;LINEID SYSDATE-1 2019-12-01 23:39:26.865328used time: 0.580 (ms). Execute id is 85.SQL > DELETE FROM PERSON.PERSON_TYPE WHERE PERSON_TYPEID > 5 * affect rows 2used time: 1.797 (ms). Execute id is 86.SQL > commit;executed successfullyused time: 19.834 (ms). Execute id is 87.SQL > SELECT * FROM PERSON.PERSON_TYPE LINEID PERSON_TYPEID NAME-11 Purchasing Manager 2 2 Purchasing Representative 3 3 sales Manager 4 4 sales Representative 55 damage Prevention staff used time: 0.691 (ms). Execute id is 88.

Use a flashback query to get the data before deletion.

SQL > SELECT * FROM PERSON.PERSON_TYPE WHEN TIMESTAMP '2019-12-01 23 purl 39 purl 26' LINEID PERSON_TYPEID NAME-1 1 Purchasing Manager 2 2 Purchasing Representative 3 3 sales Manager 4 4 sales Representative 5 5 damage prevention Member 6 6 cleaner 6 rows gotused time: 1.262 (ms). Execute id is 89.

Flashback queries the PERSON_ type table of the specified TRXID.

To get the TRXID information, you need to query the pseudo column VERSIONS_ENDTRXID through the flashback version.

Modify the data at 23:45:27 on 2019-12-01 and submit it.

SQL > select sysdate;LINEID SYSDATE-1 2019-12-01 23:48:27.983996used time: 0.509 (ms). Execute id is 90.SQL > SELECT * FROM PERSON.PERSON_TYPE LINEID PERSON_TYPEID NAME-1 1 Purchasing Manager 2 2 Purchasing Representative 3 3 sales Manager 4 4 sales Representative 5 9 loss prevention Member 6 10 Cleaner 6 rows gotused time: 0.515 (ms). Execute id is 97.SQL > UPDATE PERSON.PERSON_TYPE SET NAME=' Security Guard 'WHERE PERSON_TYPEID=9;affect rows 1used time: 1.460 (ms). Execute id is 99.SQL > commit;executed successfullyused time: 16.640 (ms). Execute id is 100.SQL > UPDATE PERSON.PERSON_TYPE SET NAME=' cashier 'WHERE PERSON_TYPEID=9;affect rows 1used time: 1.851 (ms). Execute id is 101.SQL > commit;executed successfullyused time: 16.781 (ms). Execute id is 102.SQL > SELECT * FROM PERSON.PERSON_TYPE LINEID PERSON_TYPEID NAME-1 1 Purchasing Manager 2 2 Purchasing Representative 3 3 sales Manager 4 4 sales Representative 5 9 cashier Member 6 10 Cleaner 6 rows gotused time: 0.516 (ms). Execute id is 103.

Do a flashback version query to determine TRXID.

SQL > SELECT VERSIONS_ENDTRXID, NAME FROM PERSON.PERSON_TYPE VERSIONS BETWEEN TIMESTAMP '2019-12-01 23 purse 48mm 27.983996' AND SYSDATE LINEID VERSIONS_ENDTRXID NAME-1 NULL Purchasing Manager 2 NULL Purchasing Representative 3 NULL sales Manager 4 NULL sales Represents 5 NULL cashier 6 NULL cleaner 7 749195 security guard 7 rows gotused time: 1.384 (ms). Execute id is 104.

Determine the version based on TRXID.

SQL > SELECT * FROM PERSON.PERSON_TYPE WHEN TRXID 749195 LINEID PERSON_TYPEID NAME-1 1 Purchasing Manager 2 2 Purchasing Representative 3 3 sales Manager 4 4 sales Representative 5 9 Security Member 6 10 Cleaner 6 rows gotused time: 1.261 (ms). Execute id is 105.

The transaction ID of the second update is 749195, then the transaction ID of the first update is 749194

SQL > SELECT * FROM PERSON.PERSON_TYPE WHEN TRXID 749194 LINEID PERSON_TYPEID NAME-1 1 Purchasing Manager 2 2 Purchasing Representative 3 3 sales Manager 4 4 sales Representative 5 9 loss prevention Staff 6 10 cleaners 6 rows got

Flashback version query

Grammatical format

:: = VERSIONS BETWEEN |

Parameters.

1.time_exp date expression, usually expressed as a string. Time_exp1 represents the start time, and time_exp2 represents the end time

2. Trxid specifies the transaction ID number, which is expressed as an integer. Trxid1 means start trxid,trxid2 means end trxid

instructions

1. Flashback version queries support normal tables (including encrypted and compressed tables), temporary tables and heap tables, but not horizontal partition tables, vertical partition tables, column storage tables, external tables and views

2. Pseudo columns are supported as auxiliary information for flashback version queries.

Pseudo column description

VERSIONS_START {TRXID | TIME} start TRXID or timestamp

VERSIONS_END {TRXID | TIME} submit TRXID or timestamp. If the value is NULL, the row version is still the current version

The operation of VERSIONS_OPERATION on the line (Inotify insert Magazine deleted DeleteMagna Utility Update)

Sentence function

Through the flashback version query clause, the user can get all the records that the transaction caused the record change in a certain period of time in the past of the specified table. The specified condition can be a time, or a transaction number.

Example 1 flashback version queries the record changes of the PERSON_TYPE table within a specified period of time.

SQL > select sysdate;LINEID SYSDATE-1 2019-12-02 00:00:18.221877used time: 0.662 (ms). Execute id is 107.

Modify the data at 00:00:18 on 2019-12-02 and submit it.

SQL > UPDATE PERSON.PERSON_TYPE SET NAME=' typist 'WHERE PERSON_TYPEID=9;affect rows 1used time: 1.758 (ms). Execute id is 110.SQL > commit;executed successfullyused time: 16.964 (ms). Execute id is 111.SQL > SQL > UPDATE PERSON.PERSON_TYPE SET NAME=' hygienist 'WHERE PERSON_TYPEID=9;affect rows 1used time: 1.268 (ms). Execute id is 112.SQL > commit;executed successfullyused time: 15.983 (ms). Execute id is 113.SQL > SELECT * FROM PERSON.PERSON_TYPE LINEID PERSON_TYPEID NAME-1 1 Purchasing Manager 2 2 Purchasing Representative 3 3 sales Manager 4 4 sales Representative 5 9 Health Member 6 10 Cleaner 6 rows gotused time: 0.669 (ms). Execute id is 114.

Make a flashback version query to get a record of the changes within a specified period of time.

SQL > SELECT VERSIONS_ENDTRXID, NAME FROM PERSON.PERSON_TYPE VERSIONS BETWEEN TIMESTAMP '2019-12-02 00 NAME FROM PERSON.PERSON_TYPE VERSIONS BETWEEN TIMESTAMP 0015 17' AND SYSDATE LINEID VERSIONS_ENDTRXID NAME-1 NULL Purchasing Manager 2 NULL Purchasing Representative 3 NULL sales Manager 4 NULL sales Represents 5 NULL hygienist 6 NULL cleaner 7 749197 typist 7 rows gotused time: 1.412 (ms). Execute id is 115.

The transaction ID for the second update is 749197

SQL > SELECT * FROM PERSON.PERSON_TYPE WHEN TRXID 749197 LINEID PERSON_TYPEID NAME-1 1 Purchasing Manager 2 2 Purchasing Representative 3 3 sales Manager 4 4 sales Representative 5 9 typing Member 6 10 Cleaner 6 rows gotused time: 1.371 (ms). Execute id is 120.

The transaction ID for the first update is 749196

SQL > SELECT * FROM PERSON.PERSON_TYPE WHEN TRXID 749196 LINEID PERSON_TYPEID NAME-1 1 Purchasing Manager 2 2 Purchasing Representative 3 3 sales Manager 4 4 sales Representative 5 9 cashier Member 6 10 Cleaner 6 rows gotused time: 0.585 (ms). Execute id is 121.

Flashback transaction query

Flashback transaction queries provide a system view V$FLASHBACK_TRX_INFO for users to view changes made to the database at the transaction level. Based on the view information, you can determine how to restore changes within a specified transaction or within a specified time period.

instructions

The system view is named V$FLASHBACK_TRX_INFO and is defined as shown below.

SQL > desc V$FLASHBACK_TRX_INFOLINEID NAME TYPE$ NULLABLE--1 START_TRXID BIGINT Y transaction in the TRXID2 START_TIMESTAMP DATETIME (6) Y transaction of the first DML Timestamp of the first DML 3 COMMIT_TRXID BIGINT Y commit transaction TRXID4 COMMIT_TIMESTAMP DATETIME (6) Y commit transaction timestamp 5 LOGIN_USER VARCHAR (256) Y owns user 6 UNDO_CHANGE# INTEGER Y record modification sequence number 7 OPERATION CHAR (1) Y DML Operation Type D: delete U: modify; I: insert; N: update insert (specifically for CLUSTER PRIMARY KEY insertion); C: transaction commit; P: pre-commit record O:default8 TABLE_NAME VARCHAR (256Y DML modified Table 9 TABLE_OWNER VARCHAR (256Y DML) modify the owner of the table 10 ROW_ID BIGINT Y DML modify row ROWID11 UNDO_SQL VARCHAR (3900) Y undo DML operation SQL statement 11 rows gotused time: 88.801 (ms). Execute id is 122.

Query the transaction information after the specified time, which can provide reference for flashback query operation

SQL > SELECT * FROM V$FLASHBACK_TRX_INFO WHERE COMMIT_TIMESTAMP > '2019-12-01 23 purl 1328' LINEID START_TRXID START_TIMESTAMP COMMIT_TRXID COMMIT_TIMESTAMP LOGIN_USER UNDO_CHANGE# OPERATION TABLE_NAME TABLE_OWNER ROW_ID UNDO_SQL - -- 1 749189 2019-12-01 23 ms 13 purl 28.000000 749190 2019-12-01 23 JV 33 ms 57.588000 16 C NULL NULLused time: 1.437 (ms) Execute id is 126.

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.