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

Oracle Study Learning-- Flashback Archive

CREATE FLASHBACK ARCHIVE

Purpose

Use the CREATE FLASHBACK ARCHIVE statement tocreate a flashback data archive, which provides the ability to automaticallytrack and archive transactional data changes to specified database objects. Aflashback data archive consists of multiple tablespaces and stores historicdata from all transactions against tracked tables.

Flashback dataarchives retain historical data for the time duration specified using the RETENTION parameter.Historical data can be queried using the Flashback Query AS OF clause.Archived historic data that has aged beyond the specified retention period isautomatically purged.

Flashback dataarchives retain historical data across data definition language (DDL) changesto tables enabled for flashback data archive. Flashback data archives supportsa lot of common DDL statements, including some DDL statements that alter tabledefinitions or incur data movement. DDL statements that are not supportedresult in error ORA-55610.

Syntax

Create_flashback_archive::=

First, establish flashbackarchive

1. Establish a flashback dedicated tablespace

14:33:13 SYS@ orcl > create tablespaceflash_tbs

14:33:26 2 datafile'/u01/app/oracle/oradata/orcl/flash_tbs01.dbf' size 100m

Tablespace created.

Elapsed: 00:00:22.03

2. Establish flashback archive

14:36:16 SYS@ orcl > create flashbackarchive farch2

14:36:54 2 tablespace flash_tbs

14:36:58 3 quota 20m

14:37:01 4 retention 1 month

Flashback archive created.

3. Modify the flashback archive attribute

14:37:09 SYS@ orcl > alter flashbackarchive farch2 set default

Flashback archive altered.

Elapsed: 00:00:00.15

4. View flashback archive properties

14:40:13 SYS@ orcl > colFLASHBACK_ARCHIVE_NAME for A20

14:40:25 SYS@ orcl > select FLASHBACK_ARCHIVE_NAME,RETENTION_IN_DAYS, STATUS fromdba_flashback_archive

FLASHBACK_ARCHIVE_NA RETENTION_IN_DAYSSTATUS

-

FARCH1 30 DEFAULT

Elapsed: 00:00:00.01

14:41:54 SYS@ orcl > col QUOTA_IN_MB fora10

14:42:02 SYS@ orcl > select * from dba_flashback_archive_ts

FLASHBACK_ARCHIVE_NA FLASHBACK_ARCHIVE# TABLESPACE_NAME QUOTA_IN_M

-

FARCH1 1FLASH_TBS 20

5. Add tablespace to flashback archive

14:42:03 SYS@ orcl > create tablespaceflash_tbs2

14:42:36 2 datafile'/u01/app/oracle/oradata/orcl/flash_tbs2a.dbf' size 100m

Tablespace created.

14:43:51 SYS@ orcl > alter flashbackarchive farch2 add tablespace flash_tbs2

Flashback archive altered.

Elapsed: 00:00:00.22

14:44:05 SYS@ orcl > select * fromdba_flashback_archive_ts

FLASHBACK_ARCHIVE_NA FLASHBACK_ARCHIVE#TABLESPACE_NAME QUOTA_IN_M

-

FARCH1 1 FLASH_TBS 20

FARCH1 1 FLASH_TBS2

Elapsed: 00:00:00.00

2. Enable flashbackarchive on the table

1. Grant permissions

15:00:11 SYS@ orcl > select * fromsystem_privilege_map where NAME like'% FLASHBACK%'

PRIVILEGE NAME PROPERTY

-243 FLASHBACK ANY TABLE 0

-350 FLASHBACK ARCHIVE ADMINISTER 0

Elapsed: 00:00:00.03

15:00:38 SYS@ orcl > grant flashbackarchive on farch2 to scott

Grant succeeded.

15:04:50 SCOTT@ orcl > alter table emp2flashback archive

Table altered.

15:11:13 SCOTT@ orcl > select * from tab

TNAME TABTYPE CLUSTERID

BONUS TABLE

DEPT TABLE

DEPT1 TABLE

EMP TABLE

EMP1 TABLE

EMP2 TABLE

EMP3 TABLE

SALGRADE TABLE

SYS_FBA_DDL_COLMAP_75889 TABLE

SYS_FBA_HIST_75889 TABLE

SYS_FBA_TCRV_75889 TABLE

III. Flashback archive application

1) access historical data

15 09WR 01SCOTT @ orcl > set autotrace on

15Rose 09WR 08SCOTT @ orcl > r

1 * select * from emp2 as of timestampto_timestamp ('2015-05-11 15 purl 05purl 04mm Murray hh34:mi:ss')

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

7566 JONES MANAGER 7839 02-APR-81 2975 20

7900 JAMES CLERK 7698 03-DEC-81 950 30

7698 BLAKE MANAGER 7839 01-MAY-81 2850 30

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

7839 KING PRESIDENT 17-NOV-81 5000 10

7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30

7902 FORD ANALYST 7566 03-DEC-81 3000 20

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30

7788 SCOTT ANALYST 7566 19-APR-87 3000 20

7782 CLARK MANAGER 7839 09-JUN-81 2450 10

7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30

7934 MILLER CLERK 7782 23-JAN-82 1300 10

7369 SMITH CLERK 7902 17-DEC-80 800 20

7876 ADAMS CLERK 7788 23-MAY-87 1100 20

14 rowsselected.

Elapsed:00:00:00.03

ExecutionPlan

Plan hashvalue: 3389285906

-

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Pstart | Pstop |

-

| | 0 | SELECT STATEMENT | | 17 | 1479 | 11 (10) | 00:00:01 |

| | 1 | VIEW | | 17 | 1479 | 11 (10) | 00:00:01 |

| | 2 | UNION-ALL | | |

| | * 3 | FILTER | | |

| | 4 | PARTITION RANGE SINGLE | | 1 | 113 | 2 (0) | 00:00:01 | KEY | 1 |

| | * 5 | TABLE ACCESS FULL | SYS_FBA_HIST_75889 | 1 | 113 | 2 (0) | 00:00:01 | KEY | 1 |

| | * 6 | FILTER | | |

| | * 7 | HASH JOIN OUTER | | 16 | 34032 | 9 (12) | 00:00:01 |

| | * 8 | TABLE ACCESS FULL | EMP2 | 16 | 1584 | 5 (0) | 00:00:01 |

| | * 9 | TABLE ACCESS FULL | SYS_FBA_TCRV_75889 | 4 | 8112 | 3 (0) | 00:00:01 |

-

PredicateInformation (identified by operation id):

3-filter ("TIMESTAMP_TO_SCN" (TIMESTAMP' 2015-05-1115 TIMESTAMP' 05TIMESTAMP' 04.000000000') select * from emp2

15:14:53 2 versions

15:14:57 3 between timestamp

15:15:16 4 to_timestamp ('2015-05-11 15 purl 05purl 04mm Murray hh34:mi:ss')

15:15:29 5 andmaxvalue

15:15:34 6 where ename='SCOTT'

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

7788 SCOTT ANALYST 7566 19-APR-87 3000 20

Elapsed:00:00:00.01

15:15:45 SCOTT@ orcl >

3) Information life cycle management

15VOTT @ orcl > select count (*) from emp2

COUNT (*)

-

eighteen

Elapsed:00:00:00.00

15Rose 17 from emp2 22SCOTT @ orcl > insert into emp2 select * from emp2

18 rowscreated.

Elapsed:00:00:00.00

15VOTT @ orcl > 31Skett > commit

Commit complete.

Elapsed:00:00:00.09

15 from emp2 17 Switzerland 33 Scott @ orcl > select count (*) SCOTT

COUNT (*)

-

thirty-six

Elapsed:00:00:00.00

15Rose 17purl 37SCOTT @ orcl > select * from emp2

15:18:00 2 versions

15:18:06 3 between timestamp

15:18:13 4 to_timestamp ('2015-05-11 15 purl 05purl 04mm Murray hh34:mi:ss')

15:18:30 5 and

15:18:32 6 to_timestamp ('2015-05-11 15-15-17-15-17-15-13-12-12-12-12-12-12-11-15-15-15-17-15-17-15-17-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12, respectively. (' 2015-05-11 15-15 17-15-12-12-12-12-12-12-12-12-12-12-15)

15:19:06 7 where empno=7788

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

7788 SCOTT ANALYST 7566 19-APR-87 3000 20

Elapsed:00:00:00.00

15 from emp2 where empno=7788 1914 13 SCOTT @ orcl > select * from emp2 where empno=7788

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

7788 SCOTT ANALYST 7566 19-APR-87 3000 20

7788 SCOTT ANALYST 7566 19-APR-87 3000 20

Elapsed:00:00:00.00

15VOTT @ orcl > select * from emp2

15:19:42 2 versions

15:19:45 3 between timestamp

15:19:48 4 to_timestamp ('2015-05-0915 05lo 04mm hh34:mi:ss')

15:19:59 5 and

15:20:02 6 to_timestamp ('2015-05-11 15-15-17-15-17-15-13-12-12-12-12-12-12-11-15-15-15-17-15-17-15-17-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12, respectively. (' 2015-05-11 15-15 17-15-12-12-12-12-12-12-12-12-12-12-15)

15:20:06 7 where empno=7788

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

7788 SCOTT ANALYST 7566 19-APR-87 3000 20

Elapsed:00:00:00.02

1520 orcl 09SCOTT @ orcl > select * from emp2

15:20:27 2 versions

15:20:30 3 between timestamp

15:20:37 4 to_timestamp ('2015-05-09 15 purl 05purl 04mm Murray hh34:mi:ss')

15:20:41 5 and

15:20:44 6 to_timestamp ('2015-05-11 15-15-15-15-15-15-20-5-12-12-12-12-12-12-12-12-12-12-12-15-15-15-15-15-12-12-12-15-15-15-20-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12-12, 2015-05-11, 15, 15, 15, 15, 15, 15, 15, 15, 15, 15, 15, 15, 15, 15, 15, 15, 15, 15, 15, 15, 15, 15, 15, 15, 15, 15, 15, 15, 15, 12, 15, 15, 15, 15, 15, 15, 15, 20,

15:21:01 7 where empno=7788

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

7788 SCOTT ANALYST 7566 19-APR-87 3000 20

Elapsed:00:00:00.01

Case test:

Flashback archive:

1. Establish a dedicated tablespace for flashback archive

11:15:32 SYS@ orcl > create tablespace ftbs1

11:15:40 2 datafile'/ u01 size size

Tablespace created.

Elapsed: 00:00:25.14

2. Establish flashback archive

11:16:17 SYS@ orcl > create flashback archive farch2

11:16:41 2 tablespace ftbs1

11:17:14 3 quota 20m

11:17:26 4 retention 7 day

Flashback archive created.

Elapsed: 00:00:00.28

3. Query attributes

11:17:49 SYS@ orcl > select FLASHBACK_ARCHIVE_NAME,RETENTION_IN_DAYS, STATUS from dba_flashback_archive

11:18:07 2

FLASHBACK_ RETENTION_IN_DAYS STATUS

FARCH1 7

Elapsed: 00:00:00.01

11:18:08 SYS@ orcl > select * from dba_flashback_archive_ts

FLASHBACK_ FLASHBACK_ARCHIVE# TABLESPACE_NAME QUOTA_IN_M

-

FARCH1 1 FTBS1 20

Elapsed: 00:00:00.01

11:18:37 SYS@ orcl > alter flashback archive farch2 set default

Flashback archive altered.

Elapsed: 00:00:00.19

11:18:56 SYS@ orcl > select FLASHBACK_ARCHIVE_NAME,RETENTION_IN_DAYS, STATUS from dba_flashback_archive

FLASHBACK_ RETENTION_IN_DAYS STATUS

FARCH1 7 DEFAULT

Elapsed: 00:00:00.01

4. Authorized users enable flashback archive on table

11:19:11 SYS@ orcl > grant flashback archive on farch2 to scott

Grant succeeded.

11:20:56 SCOTT@ orcl > alter table emp2 flashback archive farch2

Table altered.

Elapsed: 00:00:00.31

11:21:14 SCOTT@ orcl > select * from tab

TNAME TABTYPE CLUSTERID

BONUS TABLE

DEPT TABLE

EMP TABLE

EMP2 TABLE

SALGRADE TABLE

T1 TABLE

6 rows selected.

Elapsed: 00:00:00.01

11:21:46 SCOTT@ orcl > delete from emp2

36 rows deleted.

Elapsed: 00:00:00.03

11:22:05 SCOTT@ orcl > commit

Commit complete.

Elapsed: 00:00:00.19

11:22:08 SCOTT@ orcl > select * from tab

TNAME TABTYPE CLUSTERID

BONUS TABLE

DEPT TABLE

EMP TABLE

EMP2 TABLE

SALGRADE TABLE

T1 TABLE

6 rows selected.

Elapsed: 00:00:00.00

11:22:12 SCOTT@ orcl > select * from emp2

No rows selected

Elapsed: 00:00:00.00

4. Query flashback archive data

By default, Oracle first reads historical data from undo block, and then accesses flashback archive if the undo block data does not exist.

11:24:06 SCOTT@ orcl > set autotrace on

11:24:16 SCOTT@ orcl > select * from emp2 as of timestamp to_timestamp ('2015-05-12 11-11-21-14-21-19-12-12-12-16-12-16-12-12-11)

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

--

7369 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 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 10

7369 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 30

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

--

7566 JONES MANAGER 7839 02-APR-81 2975 20

7369 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 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 10

7369 SMITH CLERK 7902 17-DEC-80 800 20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

--

7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30

7566 JONES MANAGER 7839 02-APR-81 2975 20

36 rows selected.

Elapsed: 00:00:00.01

Execution Plan

Plan hash value: 2941272003

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |

| | 0 | SELECT STATEMENT | | 654 | 56898 | 9 (0) | 00:00:01 |

| | 1 | TABLE ACCESS FULL | EMP2 | 654 | 56898 | 9 (0) | 00:00:01 |

Statistics

4 recursive calls

0 db block gets

10 consistent gets

0 physical reads

0 redo size

2655 bytes sent via SQL*Net to client

437 bytes received via SQL*Net from client

4 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

36 rows processed

As you can see from the above sql, the data is read from undo data.

11:25:16 SCOTT@ orcl > set autotrace off

11:25:23 SCOTT@ orcl > select * from tab

TNAME TABTYPE CLUSTERID

BONUS TABLE

DEPT TABLE

EMP TABLE

EMP2 TABLE

SALGRADE TABLE

SYS_FBA_DDL_COLMAP_75889 TABLE

SYS_FBA_HIST_75889 TABLE

SYS_FBA_TCRV_75889 TABLE

T1 TABLE

9 rows selected.

Elapsed: 00:00:00.01

6. Create a new undo tablespace, and then switch

11:19:42 SYS@ orcl > create undo tablespace undotbs1

11:25:57 2 datafile'/ u01max app size oracle size 100m

Tablespace created.

Elapsed: 00:00:23.73

11:26:41 SYS@ orcl > show parameter undo

NAME TYPE VALUE

-

Undo_management string AUTO

Undo_retention integer 3600

Undo_tablespace string UNDOTBS2

11:26:45 SYS@ orcl > alter system set undo_tablespace=undotbs1

System altered.

Elapsed: 00:00:00.27

Restart the database:

11:27:20 SYS@ orcl > startup force

ORACLE instance started.

Total System Global Area 205049856 bytes

Fixed Size 1335500 bytes

Variable Size 113250100 bytes

Database Buffers 88080384 bytes

Redo Buffers 2383872 bytes

Database mounted.

Database opened.

Query the historical data again. Because the undo tablespace is switched, the previous undo block cannot be accessed, so the data will be read from flashback archive

11:27:50 @ > conn scott/tiger

Connected.

11:27:58 SCOTT@ orcl > set autotrace on

11:28:02 SCOTT@ orcl > select * from emp2 as of timestamp to_timestamp ('2015-05-12 11-11-21-14-21-19-12-12-12-16-12-16-12-12-11)

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

--

7369 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 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 10

7369 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 30

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

--

7566 JONES MANAGER 7839 02-APR-81 2975 20

7369 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 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 10

7369 SMITH CLERK 7902 17-DEC-80 800 20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

--

7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30

7566 JONES MANAGER 7839 02-APR-81 2975 20

36 rows selected.

Elapsed: 00:00:00.13

Execution Plan

Plan hash value: 3389285906

-

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Pstart | Pstop |

-

| | 0 | SELECT STATEMENT | | 34 | 2958 | 16 (7) | 00:00:01 |

| | 1 | VIEW | | 34 | 2958 | 16 (7) | 00:00:01 |

| | 2 | UNION-ALL | | |

| | * 3 | FILTER | | |

| | 4 | PARTITION RANGE SINGLE | | 1 | 113 | 3 (0) | 00:00:01 | KEY | 1 |

| | * 5 | TABLE ACCESS FULL | SYS_FBA_HIST_75889 | 1 | 113 | 3 (0) | 00:00:01 | KEY | 1 |

| | * 6 | FILTER | | |

| | * 7 | HASH JOIN OUTER | | 33 | 70191 | 13 (8) | 00:00:01 |

| | * 8 | TABLE ACCESS FULL | EMP2 | 33 | 3267 | 9 (0) | 00:00:01 |

| | * 9 | TABLE ACCESS FULL | SYS_FBA_TCRV_75889 | 36 | 73008 | 3 (0) | 00:00:01 |

-

Predicate Information (identified by operation id):

3-filter ("TIMESTAMP_TO_SCN" (TIMESTAMP' 2015-05-12 11 TIMESTAMP' 21) 46.000000000')

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.