Shulou(Shulou.com)05/31 Report--

Editor to share with you based on Oracle flashback example analysis, I believe that most people do not know much about it, so share this article for your reference, I hope you will learn a lot after reading this article, let's go to know it!

Oracle 9i began to support flashback, Oracle10g began to fully support flashback function, and Oracle11g has been improved, which provides a great and convenient way for everyone to quickly recover data and query historical data.

This article mainly gives a detailed introduction to the common use of flashbacks in Oracle, but it will not introduce the transactions and version flashbacks that are not commonly used.

I. Overview of Oracle flashback

Flashback level flashback scene flashback technology object dependence affects data database table truncation, logic errors, other multi-table unexpected events flashback DATABASE flashback log, undo is DROP delete table flashback DROP recycle bin (recyclebin) is table update, delete, insert record flashback TABLE restore data, undo is query current data and historical data comparison flashback QUERY restore data, undo No version query compares row version flashback Version Query restore data Undo No transaction query compares flashback Transaction Query restore data, undo does not archive DDL, DML flashback Archive archive log is

2. Detailed explanation of Oracle flashback

1. Flashback on

(1) turn on flashback necessary conditions

a. Open the archive log

SQL > archivelog list;Database log mode Archive ModeAutomatic archival EnabledArchive destination / home/U01/app/oracle/oradata/testdb/archOldest online log sequence 844Next log sequence to archive 846Current log sequence 846 alter database archivelog # if not enabled, execute alter database archivelog in mount state

b. Set a reasonable flashback area

Db_recovery_file_dest: specify the location of the flashback recovery area

Db_recovery_file_dest_size: specifies the amount of free space in the flashback recovery area

Db_flashback_retention_target: specifies the time for the database to roll back (in minutes). The default is 1440 minutes (1 day), depending on the size of the flashback area.

(2) check whether flashback is enabled.

SQL > select flashback_on from vested database: FLASHBACKONRANO

(3) enable flashback

a. Open archiving

Mount status: alter database archivelog

b. Set the flashback area

SQL > alter system set db_recovery_file_dest='/home/U01/app/oracle/fast_recovery_area' scope=both;System altered.SQL > alter system set db_recovery_file_dest_size=60G scope=both;System altered.SQL > alter system set db_flashback_retention_target=4320 scope=both;System altered.

c. Enable flashback (10g on mount)

SQL > alter database flashback on;Database altered.

(4) make sure flashback is enabled.

SQL > select flashback_on from vested database: FLASHBACKONMY

(5) turn off flashback

SQL > alter database flashback off;Database altered.

2. Flashback use

(1) Flashback query

Flashback queries are mainly multi-version queries based on Undo tablespace data, which are not valid for v$ and x$ dynamic performance views, but are valid for DBA_, ALL_ and USER_.

a. Flashback query

Allows users to query data from a certain point in the past in order to reconstruct data that is accidentally deleted or changed so that the data will not change.

SQL > select * from scott.dept;DEPTNO DNAME LOC- ACCOUNTING NEW YORKRESEARCH DALLASSALES CHICAGOOPERATIONS BOSTONSQL > delete from scott.dept where deptno=40;row deleted.SQL > commit;Commit complete.SQL > select * from scott.dept as of timestamp sysdate-10/1440 DEPTNO DNAME LOC- ACCOUNTING NEW YORKRESEARCH DALLASSALES CHICAGOOPERATIONS BOSTONSQL > select * from scott.dept as of timestamp to_timestamp ('2017-12-14 16-15-20-12-14 16-12-12-14 16-20-00-12-14-12-12-12-12-12-14 16-20-00-12-14-12-12-14 16-12-14 20-00-12-12-14 16-12-12-14 16-12-12-12-14 16-12-12-14 20, 00-12-14, 00-12, 12-14, 00, 000, hh34:mi:ss', mm, hh34:mi:ss'). DEPTNO DNAME LOC- ACCOUNTING NEW YORKRESEARCH DALLASSALES CHICAGOOPERATIONS BOSTONSQL > select * from scott.dept as of scn 16801523-- ACCOUNTING NEW YORKRESEARCH DALLASSALES CHICAGOOPERATIONS BOSTON

b. Flashback version query

A method for querying row-level databases changing over time

c. Flashback transaction query

Used to provide a method to view changes in the transaction-level database

(2) flashback table (update/insert/delete)

Flashback table is to roll back the data of the table to a certain point in time before. It uses the historical data of undo, which is related to the undo_retention setting. The default is 14400 minutes (1 day).

Similarly, sys user tablespaces do not support flashback tables. For table flashbacks, you need to allow tables to initiate row migration (row movement)

Flashback example:

SQL > flashback table scott.dept to timestamp to_timestamp (SQL > flashback table scott.dept to timestamp to_timestamp ('2017-12-14 16-12-14 16-12-14 16-12-14 16-12-12-14 16-12-12-14 16-12-12-14 16-12-14 20-12-14 12-12-14 16-12-14 20 hh34:mi:ss') ERROR at line 1:ORA-08189: cannot flashback the table because row movement is not enabledSQL > select row_movement from dba_tables where table_name='DEPT' and owner='SCOTT' ROW_MOVE-DISABLEDSQL > alter table scott.dept enable row movement;Table altered.SQL > flashback table scott.dept to timestamp to_timestamp ('2017-12-14 16-12-14 16-12-12-12-14 16-12-12-12-16-12-12-16-12-12-16-12-16-12-16-12-16-12-16-12-16-12-16-12-12-14 16-12-12-14 20-12-12-14 16-12-12-14 16-12-14 16-12-12-14 16-12-14 16-12-12-14 16-12-14-16-12-14-16-12-14-16-12-14-16-12-14-16-12-14-16-15-20-12-14-16-12-14-16-lu-20-12-14); Flashback complete.SQL > select * from scott.dept;DEPTNO DNAME LOC- ACCOUNTING NEW YORKRESEARCH DALLASSALES CHICAGOOPERATIONS BOSTON SQL > alter table scott.dept disable row movement) Table altered.

(3) flashback DROP (drop table)

When a table is dropped by drop, the table is placed in the recyclebin Recycle Bin, which can be flashed back through the Recycle Bin. Indexes, constraints, etc., on the table will also be restored

Sys/system user tablespace objects are not supported. The Recycle Bin function can be turned off through alter system set recyclebin=off;.

Flashback DROP example:

SQL > select * from t; ID NAME- 24SQL > drop table tincture Table dropped.SQL > show recyclebin ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME--T BIN$YEh3QcvZdJLgUxyAgQpnVQ==$0 TABLE 2017-12-14:15:02:06SQL > flashback table t to before drop;Flashback complete.SQL > select * from t ID NAME- 24

Note: even if you don't start flashback, as long as you turn on recyclebin, you can flash back the DROP table.

However, if you overwrite continuously, you need to specify the name of the restored table, and if the table already exists, you need to restore renaming.

SQL > show recyclebin;ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME- T BIN$YEh3QcvddJLgUxyAgQpnVQ==$0 TABLE 2017-12-14 BIN$YEh3QcvcdJLgUxyAgQpnVQ==$0 TABLE 1515 07BIN$YEh3QcvcdJLgUxyAgQpnVQ==$0 TABLE 54T BIN$YEh3QcvcdJLgUxyAgQpnVQ==$0 TABLE 2017-12-14:15:07:27SQL > flashback table "BIN$YEh3QcvcdJLgUxyAgQpnVQ==$0" to before drop Flashback complete.SQL > show recyclebin;ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME--T BIN$YEh3QcvddJLgUxyAgQpnVQ==$0 TABLE 2017-12-14:15:07:54SQL > flashback table t to before drop rename to tt;Flashback complete.

(4) flashback database (truncate/ multi-table data change)

Database flashback must be performed in mounted. Snapshot-based flashbacks can be performed in open.

Flashback database is mainly used to restore the database value to a certain point in time or SCN, which is used for open database resetlogs when logical errors occur in the database.

a. Full library flashback

Database flashback example

SQL > select * from scott.EMP EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO--SMITH CLERK 7902 1980-12-17 00:00:00 800 20ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30JONES MANAGER 7839 1981-04-02 00:00:00 2975 20MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30BLAKE MANAGER 7839 00:00:00 2850 30CLARK MANAGER 78391981-06-09 00:00:00 2450 10SCOTT ANALYST 7566 1981-04-19 00:00:00 3000 20KING PRESIDENT 7839 1981-04-02 00:00:00 2975 10TURNER SALESMAN 7698 1981-09-08 00 : 00:00 1500 0 30ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20JAMES CLERK 7698 1981-12-03 00:00:00 950 30FORD ANALYST 7566 1981-12-03 00:00:00 3000 20MILLER CLERK 7782 1100-01-23 00:00:00 1300 10rows selected.SQL > truncate table scott.EMP Table truncated.SQL > shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.SQL > startup mount;ORACLE instance started.Total System Global Area 9.4067E+10 bytesFixed Size 2263936 bytesVariable Size 9395242112 bytesDatabase Buffers 8.4557E+10 bytesRedo Buffers 112766976 bytesDatabase mounted.SQL > flashback database to timestamp to_timestamp (Flashback complete.SQL > alter database open resetlogs;Database altered.SQL > select * from scott.emp) EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO--SMITH CLERK 7902 1980-12-17 00:00:00 800 20ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30JONES MANAGER 7839 1981-04-02 00:00:00 2975 20MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30BLAKE MANAGER 7839 00:00:00 2850 30CLARK MANAGER 78391981-06-09 00:00:00 2450 10SCOTT ANALYST 7566 1981-04-19 00:00:00 3000 20KING PRESIDENT 7839 1981-04-02 00:00:00 2975 10TURNER SALESMAN 7698 1981-09-08 00 : 00:00 1500 0 30ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20JAMES CLERK 7698 1981-12-03 00:00:00 950 30FORD ANALYST 7566 1981-12-03 00:00:00 3000 20MILLER CLERK 7782 1100-01-23 00:00:00 1300 10rows selected.

b. Snapshot flashback

You can create a flashback snapshot point for both the primary library and the standby library, and then restore it to the specified snapshot point, but once the primary library is restored to the snapshot point, the synchronization of the standby library needs to be resynchronized.

SQL > select * from scott.dept; DEPTNO DNAME LOC ADDR- ACCOUNTING NEW YORKRESEARCH DALLASSALES CHICAGO SQL > create restore point before_201712151111 guarantee flashback database;Restore point created.SQL > create table scott.t as select * from scott.dept Table created.SQL > truncate table scott.t;Table truncated.SQL > shutdown immediate;Database closed.Database dismounted.ORACLE instance shut down.SQL > startup mount;ORACLE instance started.Total System Global Area 9.4067E+10 bytesFixed Size 2263936 bytesVariable Size 9663677568 bytesDatabase Buffers 8.4289E+10 bytesRedo Buffers 112766976 bytesDatabase mounted.SQL > flashback database to restore point before_201712151111;Flashback complete.SQL > alter database open resetlogs;Database altered. At this point, the master library scott.t no longer exists: SQL > select * from scott.t;select * from scott.t * ERROR at line 1:ORA-00942: table or view does not exist slave library scott at this time. Still exists, the master / slave synchronization termination solution: at the time when the master database creates a snapshot, the slave database automatically stops applying the log, and after the master database flashes back, the log can be reapplied. If you have already done this, you can choose to rebuild ALTER DATABASE REGISTER LOGFILE'/ xx/xx/archive.dbf' from the library

c. Flashback snapshot standby

This function is very useful in 11GR2. It can automatically create flashback points, open flashback logs, complete online data testing, and then do database flashbacks to restore the relationship between master and standby.

Select scn, STORAGE_SIZE, to_char (time,'yyyy-mm-dd hh34:mi:ss') time,NAME from vastly restorestable pointplace select database_role,open_mode,db_unique_name,flashback_on from vested database > set line 200 > set pagesize 2000 Singapore SQL > select database_role,open_mode,db_unique_name,flashback_on from v$database DATABASE_ROLE OPEN_MODE DB_UNIQUE_NAME FLASHBACK_ON--PHYSICAL STANDBY READ ONLY testdbms NOSQL > ALTER DATABASE CONVERT TO SNAPSHOT STANDBY Database altered.SQL > select database_role,open_mode,db_unique_name,flashback_on from v$database DATABASE_ROLE OPEN_MODE DB_UNIQUE_NAME FLASHBACK_ON--SNAPSHOT STANDBY MOUNTED testdbms RESTORE POINT ONLYSQL > alter database open Database altered.SQL > select open_mode from vault database WRITE open operation: SQL > select * from scott.emp at this time EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO--SMITH CLERK 7902 1980-12-17 00:00:00 800 20ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30JONES MANAGER 7839 1981-04-02 00:00:00 2975 20MARTIN SALESMAN 76981981-09-28 00:00:00 1250 1400 30BLAKE MANAGER 7839 1981-01 00:00:00 2850 30CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20KING PRESIDENT 1981-11-17 00:00:00 5000 10TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30ADAMS CLERK 7788 May-23 00:00:00 1100 20JAMES CLERK 7698 1981- 12-03 00:00:00 950 30FORD ANALYST 7566 1981-12-03 00:00:00 3000 20MILLER CLERK 7782 1982-01-23 00:00:00 1300 10rows selected.SQL > truncate table scott.emp Table truncated. Operation of the main library: SQL > create table scott.t as select * from scott.dept;Table created.SQL > select * from scott.t; DEPTNO DNAME LOC ADDR- ACCOUNTING NEW YORKRESEARCH DALLASSALES CHICAGO backup database is restored to physical standbySQL > shutdown immediate Database closed.Database dismounted.ORACLE instance shut down.SQL > startup mount;ORACLE instance started.Total System Global Area 9.4067E+10 bytesFixed Size 2263936 bytesVariable Size 9663677568 bytesDatabase Buffers 8.4289E+10 bytesRedo Buffers 112766976 bytesDatabase mounted.SQL > ALTER DATABASE CONVERT TO PHYSICAL STANDBY; Database altered.SQL > shutdown immediate;ORA-01507: database not mountedORACLE instance shut down.SQL > startup ORACLE instance started.Total System Global Area 9.4067E+10 bytesFixed Size 2263936 bytesVariable Size 9663677568 bytesDatabase Buffers 8.4289E+10 bytesRedo Buffers 112766976 bytesDatabase mounted.Database opened.## the data of the repository at this time has been restored to the snapshot standby time point SQL > select database_role,open_mode,db_unique_name,flashback_on from v$database DATABASE_ROLE OPEN_MODE DB_UNIQUE_NAME FLASHBACK_ON--PHYSICAL STANDBY READ ONLY testdbms NOSQL > select * from scott.emp EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO--SMITH CLERK 7902 1980-12-17 00:00:00 800 20ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30JONES MANAGER 7839 1981-04-02 00:00:00 2975 20MARTIN SALESMAN 76981981-09-28 00:00:00 1250 1400 30BLAKE MANAGER 7839 1981-01 00:00:00 2850 30CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20KING PRESIDENT 1981-11-17 00:00:00 5000 10TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30ADAMS CLERK 7788 May-23 00:00:00 1100 20JAMES CLERK 7698 1981- 12-03 00:00:00 950 30FORD ANALYST 7566 1981-12-03 00:00:00 3000 20MILLER CLERK 7782 1982-01-23 00:00:00 1300 10rows selected.SQL > alter database recover managed standby database using current logfile disconnect Database altered.SQL > select * from scott.t; DEPTNO DNAME LOC ADDR- ACCOUNTING NEW YORKRESEARCH DALLASSALES CHICAGO SQL > select database_role,open_mode,db_unique_name,flashback_on from v$database DATABASE_ROLE OPEN_MODE DB_UNIQUE_NAME FLASHBACK_ON- PHYSICAL STANDBY READ ONLY WITH APPLY testdbms NO

(5) Flash regression files (add, modify, rename, delete columns of tables, truncate tables, modify table constraints, and modify partition specifications of partition tables)

3. Flashback considerations

(1) Database flashback needs to be performed under mounted, and resetlogs is required for open.

(2) flashback DROP can only be used for non-system tablespaces and locally managed tablespaces. Foreign key constraints cannot be restored. Attention should be paid to overriding and renaming each other.

(3) Table DROP, the corresponding materialized view will be deleted completely, and the materialized view will not be stored in recyclebin.

(4) flashback table. If you have done dml, and then perform DDL operations such as table structure modification, truncate and so on, you cannot do flashback by adding / deleting structures.

(5) Flashback archiving must be carried out under assm management, tablespace and undo auto management.

(6) pay attention to the management of the flashback area to prevent the disk from being full, insufficient space in the flashback area, etc.

(7) the flashback of the main database will affect the backup database and need to be resynchronized.

(8) snapshot standby does not support maximum protection mode

III. Remarks

1. Related data dictionary

V$FLASHBACK_DATABASE_LOG # # View database flashable time points / SCN and other information V$flashback_database_stat # # View flashback log space record information

2. Common query statements

(1) View database status

SQL > select NAME,OPEN_MODE, DATABASE_ROLE,CURRENT_SCN,FLASHBACK_ON from vested database capacity name OPEN_MODE DATABASE_ROLE CURRENT_SCN FLASHBACK_ON--TESTDB READ WRITE PRIMARY 16812246 YES

(2) obtain the system time and SCN of the current database

SQL > select to_char (systimestamp,'yyyy-mm-dd HH24:MI:SS') as sysdt, dbms_flashback.get_system_change_number scn from dual;SYSDT SCN--2017-12-14 14:28:33 16813234

(3) View the time point at which the database can be recovered

SQL > select * from V$FLASHBACK_DATABASE_LOG OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TI RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE--16801523 2017-12-14 11:35:05 4320 104857600 244113408

(4) check the flashback log space

SQL > select * from V$flashback_database_stat BEGIN_TIME END_TIME FLASHBACK_DATA DB_DATA REDO_DATA ESTIMATED_FLASHBACK_SIZE -2017-12-14 14:34:53 2017-12-14 14:56:43 1703936 9977856 1487872 0

(5) query the replacement relationship between SCN and timestamp

Select scn,to_char (time_dp,'yyyy-mm-dd hh34:mi:ss') from sys.smon_scn_time

(6) View flashback restore_point

Select scn, STORAGE_SIZE, to_char (time,'yyyy-mm-dd hh34:mi:ss') time,NAME from v$restore_point

(7) flashback statement

a. Flashback database

FLASHBACK DATABASE TO TIMESTAMP to_timestamp ('2017-12-14 14-14-14-14-14-14-14-14-28-15-28-12-14-14-14-28-12-14-14-14-14-12-14-14-14-14-14-14-14-14-14-12-14-14-14-14-14-14-14-14-14-14-14-14-14-14-12-14-14-14-14-14-14-14-14-14-12-14-14-14-14-14-14-14-14-14-14-14-14-12-14-14-14-14-14-14-14-14-14-14-14-14-14-14-14-14-14-14-14-14-14-14-14-14-14-14-

b. Flashback DROP

Where table_name can be a delete table name or an alias

Flashback table table_name to before drop;flashback table table_name to before drop rename to table_name_new

c. Flashback table

Flashback table table_name to scn scn_number;flashback table table_name to timestamp to_timestamp ('2017-12-14 1414 1414 28 Swiss 33 mm flashback table table_name to scn scn_number;flashback table table_name to timestamp to_timestamp hh34:mi:ss')

d. Flashback query

Select * from table_name as of timestamp to_timestamp ('2017-12-14 14-14-14-14-14-14-28-14-28-15-28-12-14-14-14-14-28-14-28-14-14-28-14-14-28-14-14-28-14-28-14-14-28-14-14-28-14-28-14-14-14-14-28-14-14-14-14-14-14-14-14-14-14-14-14-14-14-14-14-14-14-14-14-14-14-14-14-14-14-14-14-14-14-14-14-14-14-14-14-14-14-14-14-14-14-14-14-14-14-14-14-14-14-14-14-

e. Flashback snapshot

Create restore point before_201712151111 guarantee flashback database;flashback database to restore point before_201712151111; above is all the content of this article "sample Analysis based on Oracle flashback". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.