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

When it comes to flashback technology, I rarely use it after working for so long that I almost forget what flashback technology has. If you are free today, let's review the flashback technology in the database.

Even if I don't read a book, there are several kinds of flashback technology in my impression: flashback database, flashback deletion, and flashback query. Compared with the incomplete recovery of the database, the flashback technology has the advantages of high speed and small impact.

Flash back to the database

Flashback database is to restore the overall state of the database to a certain point in the past or a system change number (SCN). It is a fast way to implement incomplete database recovery.

1.1 requirements for use:

1) must have the permission of SYSDBA

2) recovery area is enabled

3) the database is in FLASHBACK mode

4) the database is in mount state

In addition

5) the database must handle the archive mode

6) Control files cannot be backup control files or rebuilt control files.

7) the database does not contain tablespaces in FLASHBACK OFF.

1.2 Grammar

Description:

FLASHBACK DATABASE: when using the flashback database command, the database verifies that the required archive logs and online redo logs are available. If they are available, it restores all current online data files in the database to SCN or the time specified in this statement.

The amount of flashback data retained in the database is controlled by the DB_FLASHBACK_RETENTION_TARGET initialization parameters and the size of the fast recovery area. You can query the V$FLASHBACK_DATABASE_LOG view to determine how long it takes to flash back to the database.

STANDBY: specify STANDBY to restore the standby database to an earlier SCN or time. If the database is not a standby database, the database returns an error. If you omit this clause, the database can be a primary or standby database.

TO SCN statement:

TO SCN restores the database to its state at the specified SCN.

TO BEFORE SCN restores the database to the state of the system change number immediately before the specified SCN.

TO TIMESTAMP statement:

TO TIMESTAMP: restores the database to its state at the specified timestamp.

TO BEFORE TIMESTAMP: restores the database to a state one second before the specified timestamp.

TO RESTORE POINT statement: specify this clause to flash the database back to the specified restore point. Is the only statement that can be used when flashback databases are not enabled.

RESETLOGS:

Flash the database back just before the last resetlogs operation (ALTER DATABASE OPEN RESETLOGS).

Once you know the requirements and syntax of the flashback database, you can manipulate the flashback mode of the database. (test environment: ORACLE 11GR2)

1) Log in to the database using the database administrator

SQL > conn / as sysdba

2) check whether the database has flashback mode enabled

SQL > select flashback_on from vested database; FLASHBACK_ON-NO

If the database is not enabled, use the following command:

During ALTER DATABASE FLASHBACK ON; # actual testing, the database was not restarted when the command was executed.

3) check whether the database flashback area is enabled

SQL > show parameter recoveryNAME TYPE VALUE---db_recovery_file_dest string / u01/app/oracle/flash_recovery _ areadb_recovery_file_dest_size big integer 3882Mrecovery_parallelism integer 0

# modify method:

# adjust the size and position of the flashback area

# SQL > alter system set db_recovery_file_dest_size=5g scope=spfile

# set the location of the flash zone:

# SQL > alter system set db_recovery_file_dest='/app/flash_recovery_area' scope=spfile

4) View DB_FLASHBACK_RETENTION_TARGET parameters (in minutes)

SQL > show parameter db_flashback_retention_target NAME TYPE VALUE---db_flashback_retention_target integer 1440

Modification method:

# SQL > alter system set db_flashback_retention_target=7200 scope=spfile

5) check whether it is in archive mode

SQL > archive log list;Database log mode Archive ModeAutomatic archival EnabledArchive destination USE_DB_RECOVERY_FILE_DESTOldest online log sequence 10Next log sequence to archive 12Current log sequence 12

Archive mode enabled

3) and 4) the parameters in can be modified according to the actual needs.

6) check V$FLASHBACK_DATABASE_LOG to see if any data is generated

SQL > select * from V$FLASHBACK_DATABASE_LOG OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TIME RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE 1025623 2017-1-13 11:11:05 1440 8192000 0

7) flashback the database

Create a test table:

SQL > create table scott.test_1113_1 as select * from vault from dual; Table created SQL > create table scott.test_1113_2 as select * from vault logfile; Table created SQL > select dbms_flashback.get_system_change_number,SCN_TO_TIMESTAMP (dbms_flashback.get_system_change_number) log GET_SYSTEM_CHANGE_NUMBER SCN_TO_TIMESTAMP (DBMS_FLASHBAC -- 1026334 13-January-17 11.21.50.000000000 a.m.

At this point, the system change point is 1026334, and then the database is restored to this point. Tables scott.test_1113_1 and scott.test_1113_2 should both exist.

SQL > drop table scott.test_1113_1; Table dropped SQL > drop table scott.test_1113_2; Table dropped SQL > shutdown abort;ORACLE instance shut down.SQL > startup mount;ORACLE instance started.Total System Global Area 776646656 bytesFixed Size 2217384 bytesVariable Size 511707736 bytesDatabase Buffers 260046848 bytesRedo Buffers 2674688 bytesDatabase mounted.SQL > flashback database to scn 1026334X Flashback complete.SQL > alter database open resetlogs;Database altered.SQL > select count (1) from scott.test_1113_1; COUNT (1)-3 SQL > select count (1) from scott.test_1113_2 COUNT (1)-3

After flashing back to the database, both scott.test_1113_1 and scott.test_1113_2 exist.

Second, flashback table

Using the flashback table command, you can restore the table of the database to its previous state at some point in time, depending on the amount of data in the rollback segment. The flashback table operation cannot be rolled back.

2.1 use requirements

Have FLASHBACK permissions for this table or FLASHBACK ANY TABLE permissions. In addition, you must have SELECT, INSERT, DELETE, and ALTER permissions.

Tables that use flashback table must have row movement turned on (except for tables flashback from the Recycle Bin).

To flash the table back to the restore point, you must have the system privileges of SELECT ANY DICTIONARY or FLASHBACK ANY TABLE or the SELECT_CATALOG_ROLE role.

2.2 Grammar

During the Oracle flashback table operation, the Oracle database acquires exclusive DML locks on all tables specified in the flashback list. When these tables are restored to their previous state, these locks prevent any operation on the table.

Flashback table operations are performed in a single transaction, regardless of the number of tables specified in the flashback list. All tables are restored to their early state, or none of them are restored. If the flashback table operation fails on any table, the entire statement fails.

When the flashback table operation is completed, the data in the table is consistent with the earlier table. However, FLASHBACK TABLE TO SCN or TIMESTAMP does not retain rowid, and FLASHBACK TABLE TO BEFORE DROP does not restore previously referenced constraints.

The Oracle database does not restore statistics associated with the table to the previous form. The index on the currently existing table is restored and reflects the state of the table at the flashback point. If the index exists now, but not at the flash point, the database updates the index to reflect the status of the table at the flash point. However, indexes dropped during the interval between the flashback point and the current time are not restored.

Schema: the owner of the table

Table: specifies the name of one or more tables that contain the data to be restored to an earlier version.

Restrictions:

1) Flashback table operations are not valid for the following types of objects: tables that are part of the cluster, materialized views, advanced queuing (AQ) tables, static data dictionary tables, system tables, remote tables, object tables, nested tables or individual table partitions or sub-partitions.

2) the following DDL operations change the structure of the table so that you cannot later use the TO SCN or TO TIMESTAMP clause to flash the table back to a certain time before the operation: upgrade, move or truncate the table; add constraints to the table and add tables to the cluster; modify or discard columns; change column encryption keys; add, delete, merge, split, merge or truncate partitions or subpartitions (except for adding range partitions). (upgrading, moving, or truncating a table; adding a constraint to a table, adding a table to a cluster; modifying or dropping a column; changing a column encryption key; adding, dropping, merging, splitting, coalescing, or truncating a partition or subpartition (with the exception of adding a range partition).

TO RESTORE POINT: specify the recovery point where you want to flash the table. The restore point must have been created.

ENABLE | DISABLE TRIGGERS: disable trigger by default

TO BEFORE DROP: restore the table from the Recycle Bin.

You can specify the original name of the table or the system name assigned to the object

If there is more than one table with the same name, the last deleted table, that is, last in, first out, is restored.

RENAME TO: rename.

2.3 exampl

1) create a new test table

SQL > create table scott.test_1114_1 as select * from vault logfileleading SQL > select * from scott.test_1114_1 GROUP# STATUS TYPE MEMBER IS_RECOVERY_DEST_FILE -- 3 ONLINE / u01/app/oracle/oradata/orcl/redo03.log NO2 ONLINE / u01/app/oracle/oradata/orcl/redo02.log NO1 ONLINE / u01/app/oracle/oradata/orcl/redo01.log NO

2) get the current SCN and timestamp, and finally the table data will be restored to the current time point.

SQL > select dbms_flashback.get_system_change_number,SCN_TO_TIMESTAMP (dbms_flashback.get_system_change_number) timestamp from dual GET_SYSTEM_CHANGE_NUMBER TIMESTAMP-----1031289 14-January-17 09.13.59.0000000 a.m.

3) Delete the records in the table

SQL > delete from scott.test_1114_1; SQL > select count (1) from scott.test_1114_1; COUNT (1)-0

4) flashback table

SQL > alter table scott.test_1114_1 enable row movement; Table alteredSQL > flashback table scott.test_1114_1 to scn 1031289; Done SQL > select * from scott.test_1114_1 GROUP# STATUS TYPE MEMBER IS_RECOVERY_DEST_FILE- 3 ONLINE / u01/app/oracle/oradata/orcl/redo03.log NO2 ONLINE / u01/app/oracle/oradata/orcl/redo02.log NO1 ONLINE / u01/app/oracle/oradata/orcl/redo01.log NO

Table restored

5) Delete and create the table multiple times

SQL > drop table scott.test_1114_1; Table dropped SQL > create table scott.test_1114_1 as select * from vault log file; Table created SQL > drop table scott.test_1114_1; Table dropped SQL > create table scott.test_1114_1 as select * from vault log file; Table created SQL > drop table scott.test_1114_1; Table dropped

6) View the Recycle Bin

SQL > select object_name,original_name,droptime from dba_recyclebin OBJECT_NAME ORIGINAL_NAME DROPTIME---BIN$RgTFmsLNhcHgUKjAyX44MA==$0 TEST_1114_1 2017-01-14 : 09:21:37BIN$RgTFmsLOhcHgUKjAyX44MA==$0 TEST_1114_1 2017-01-14:09:21:58BIN$RgTFmsLPhcHgUKjAyX44MA==$0 TEST_1114_1 2017-01-14 purl 0922purl 01

7) restore the table

SQL > flashback table scott.test_1114_1 to before drop; Done SQL > select object_name,original_name,droptime from dba_recyclebin OBJECT_NAME ORIGINAL_NAME DROPTIME---BIN$RgTFmsLNhcHgUKjAyX44MA==$0 TEST_1114_1 2017-01-14 : 09:21:37BIN$RgTFmsLOhcHgUKjAyX44MA==$0 TEST_1114_1 2017-01-14 09V 21V 58

The latest deleted table is restored.

SQL > flashback table scott.test_1114_1 to before drop rename to test_1114_2; Done SQL > select object_name,original_name,droptime from dba_recyclebin OBJECT_NAME ORIGINAL_NAME DROPTIME-- BIN$RgTFmsLNhcHgUKjAyX44MA==$0 TEST_1114_1 2017-01-14 Fraser 09RV 21VO 37

3. Flashback query

To use a flashback query, you must have query permissions for the table and FLASHBACK permissions for the table or system permissions for FLASHBACK ANY TABLE.

There are two kinds of flashback queries, one is to query data at a certain point in time (as of), and the other is to query data within a certain period of time (versions between). Flashback queries do not affect the data in the current table.

1) flashback time point query

Select * from as of timestamp to_timestamp (timestamp,'yyyy-mm-dd hh34:mi:ss'); select * from as of scn scn_number

2) flashback version query

SELECT versions_startscn, versions_starttime, versions_endscn, versions_endtime, versions_xid, versions_operation, t.*FROM tVERSIONS BETWEEN TIMESTAMP BeginTimestamp and EndTimestSELECT versions_startscn, versions_starttime, versions_endscn, versions_endtime, versions_xid, versions_operation, t.*FROM tVERSIONS BETWEEN scn begin_scn and end_scn

Example:

1) get the current SCN

SQL > select dbms_flashback.get_system_change_number,SCN_TO_TIMESTAMP (dbms_flashback.get_system_change_number) timestamp from dual GET_SYSTEM_CHANGE_NUMBER TIMESTAMP-----1032595 14-January-17 09.47.57.0000000 a.m.

2) add data in test_1114_1 table

SQL > insert into scott.test_1114_1 select * from scott.test_1114_1;3 rows inserted

3) flashback time point query

SQL > select * from scott.test_1114_1 as of scn 1032595 GROUP# STATUS TYPE MEMBER IS_RECOVERY_DEST_FILE- 3 ONLINE / u01/app/oracle/oradata/orcl/redo03.log NO2 ONLINE / u01/app/oracle/oradata/orcl/redo02.log NO1 ONLINE / u01/app/oracle/oradata/orcl/redo01.log NO SQL > select * from scott.test_1114_1 GROUP# STATUS TYPE MEMBER IS_RECOVERY_DEST_FILE- 3 ONLINE / u01/app/oracle/oradata/orcl/redo03.log NO2 ONLINE / u01/app/oracle/oradata/orcl/redo02.log NO1 ONLINE / u01/app/oracle/oradata/orcl/redo01.log NO3 ONLINE / u01/app/oracle/oradata/orcl/redo03.log NO2 ONLINE / u01/app/oracle/oradata/orcl/redo02.log NO1 ONLINE / u01/app/oracle/oradata/orcl/redo01.log NO 6 rows selected

4) flashback version query

SQL > select dbms_flashback.get_system_change_number,SCN_TO_TIMESTAMP (dbms_flashback.get_system_change_number) timestamp from dual GET_SYSTEM_CHANGE_NUMBER TIMESTAMP-----1032939 14-January-17 09.52.42.0000000 AM SQL > SELECT versions_startscn Versions_starttime, versions_endscn, versions_endtime, versions_xid, versions_operation, group#,status,type,member, is_recovery_dest_file 2 FROM scott.test_1114_1 3 VERSIONS BETWEEN scn 1032595 and 1032939 VERSIONS_STARTSCN VERSIONS_STARTTIME VERSIONS_ENDSCN VERSIONS_ENDTIME VERSIONS_XID VERSIONS_OPERATION GROUP# STATUS TYPE MEMBER IS_RECOVERY_DEST_FILE - -3 ONLINE / u01/app/oracle/oradata/orcl/redo03.log NO 2 ONLINE / u01/app/oracle/oradata / orcl/redo02.log NO1 ONLINE / u01/app/oracle/oradata/orcl/redo01.log NO1032882 14-January-17 09.50.06 AM 030001002F030000 I 3 ONLINE / u01/app / oracle/oradata/orcl/redo03.log NO1032882 14-January-17 09.50.06 AM 030001002F030000 I 2 ONLINE / u01/app/oracle/oradata/orcl/redo02.log NO1032882 14-January-17 09.50.06 AM 030001002F030000 I 1 ONLINE / u01/app/oracle/oradata/orcl/redo01.log NO 6 rows selected

The flashback version query found the data status of table 1032595 with SCN and three more insert records.

IV. Summary

So far, these are the technologies we have been exposed to about flashback. They are flashback database [1], flashback table [2], and flashback query [2].

Reference:

1. "Database SQL Language Reference"

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.