How To Using Flashback Data Archive (Oracle Total Recall)

yyp2009發表於2017-08-24
HiveOracle
First of all,How Recall?An end user recovers from erroneous transactions that were previously committed in the database. The undo data for the erroneous transactions is no longer available, but because the required historical information is available in the Flashback Data Archive, Flashback Query works seamlessly.
At first,A Flashback Data Archive provides the ability to track and store transactional changes to a table over its lifetime. A Flashback Data Archive is useful for compliance with record stage policies and audit reports.

A Flashback Data Archive consists of one or more tablespaces or parts thereof. You can have multiple Flashback Data Archives. If you are logged on as SYSDBA, you can specify a default Flashback Data Archive for the system. A Flashback Data Archive is configured with retention time. Data archived in the Flashback Data Archive is retained for the retention time.By default, flashback archiving is off for any table. You can enable flashback archiving for a table if all of these conditions are true:

  • You have the FLASHBACK ARCHIVE object privilege on the Flashback Data Archive to use for that table.

  • The table is neither nested, clustered, temporary, remote, or external.

  • The table contains neither LONG nor nested columns.

  • The table does not use any of these Flashback Data Archive reserved words as column names:

    • STARTSCN

    • ENDSCN

    • RID

    • XID

    • OP

    • OPERATION
      After flashback archiving is enabled for a table, you can disable it only if you either have the FLASHBACK ARCHIVE ADMINISTER system privilege or you are logged on as SYSDBA.When choosing a Flashback Data Archive for a specific table, consider the data retention requirements for the table and the retention times of the Flashback Data Archives on which you have the FLASHBACK ARCHIVE object privilege.

Most of the flashback functionalities rely on UNDO data which will error out with an ORA-1555 snapshot too old if the required before image cannot be read any more.  ORA-1555 occurs because it has already been overwritten in the undo tablespace. Furthermore, it is very unlikely that the old values can be reconstructed over a longer period of time, like months or even years, from data stored in the undo segments.

Luckily, this UNDO data time issue can now be remedied.  With the flashback data archive functionality of 11g the Oracle database is capable of automatically tracking transactional changes to data over very long periods. Oracle achieves this by storing UNDO information in special segments within dedicated tablespaces.

The usage of this feature is completely transparent for the application and the end user, who can view historical data from the flashback archive seamlessly with regular SQL statements.  This is done by utilizing traditional flashback functionalities, such as flashback query, flashback versions query, flashback transaction query, etc.

The historical information in the flashback data archive ages out automatically and Oracle automatically purges it after the specified retention period has exceeded. However, the flashback data archive provides the DBA with a central interface for the management of historical data and change tracking. 

% With a flashback data archive it is possible to view data as any point in time             since the flashback data archive was created. However, attempting to view             data as a timestamp before the data archive is created causes the following             error: ORA-01466: unable to read data - table definition has changed

The technology behind flashback data archive

With every Oracle 11g database startup, the new flashback data archiver background process, FBDA, is automatically started. This is what generates and archives the historical data.

Transactions encounter very little performance impact from flashback data archiving because Oracle only marks DML operations as candidates for archiving.  A special background process then generates and archives the history information asynchronously for tables enabled for flashback archival.


SQL> SELECT PNAME
  2  FROM   V$PROCESS
  3  WHERE  PNAME IS NOT NULL
  4  ORDER BY PNAME;
...........
FBDA
[root@ORACLERAC2 ~]# ps -ef|grep fbda
oracle    1719     1  0 11:03 ?        00:00:00 ora_fbda_PROD1
root     25475 25409  0 17:47 pts/4    00:00:00 grep fbda
[root@ORACLERAC2 ~]#
...........

SQL> conn / as sysdba;
Connected.
SQL> create tablespace fratas datafile '/u01/app/oracle/oradata/PROD1/fratbs.dbf'  size 50m;
Tablespace created.
SQL> create user fr_admin identified by fr_admin default tablespace fratas;
User created.
SQL>
SQL>
SQL> grant connect,resource  to fr_admin;
Grant succeeded.
SQL> grant flashback archive administer  to fr_admin;
Grant succeeded.
SQL> select PRIVILEGE from dba_sys_privs  where PRIVILEGE like '%FLASHBACK%';
PRIVILEGE
----------------------------------------
FLASHBACK ANY TABLE
FLASHBACK ANY TABLE
FLASHBACK ARCHIVE ADMINISTER
FLASHBACK ARCHIVE ADMINISTER
FLASHBACK ARCHIVE ADMINISTER
FLASHBACK ANY TABLE
6 rows selected.
SQL> conn fr_admin/fr_admin
Connected.
SQL>  CREATE FLASHBACK ARCHIVE   fra1 tablespace  fratas RETENTION 1 YEAR;
Flashback archive created.
SQL> desc dba_flashback_archive;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER_NAME                                         VARCHAR2(30)
 FLASHBACK_ARCHIVE_NAME                    NOT NULL VARCHAR2(255)
 FLASHBACK_ARCHIVE#                        NOT NULL NUMBER
 RETENTION_IN_DAYS                         NOT NULL NUMBER
 CREATE_TIME                                        TIMESTAMP(9)
 LAST_PURGE_TIME                                    TIMESTAMP(9)
 STATUS                                             VARCHAR2(7)

SQL> select OWNER_NAME,FLASHBACK_ARCHIVE_NAME,RETENTION_IN_DAYS  from dba_flashback_archive where owner_name='FR_ADMIN';

OWNER_NAME
------------------------------
FLASHBACK_ARCHIVE_NAME
--------------------------------------------------------------------------------
RETENTION_IN_DAYS
-----------------
FR_ADMIN
FRA1
              365
SQL> sho user;
USER is "FR_ADMIN"
SQL> grant flashback archive on fra1 to sh;
Grant succeeded.
SQL> conn sh/sh
Connected.
SQL> alter table promotions  flashback archive fra1;
SQL> conn / as sysdba;
Connected.
SQL> select * from dba_flashback_archive_tables;

TABLE_NAME                     OWNER_NAME
------------------------------ ------------------------------
FLASHBACK_ARCHIVE_NAME
--------------------------------------------------------------------------------
ARCHIVE_TABLE_NAME                                    STATUS
----------------------------------------------------- -------------
PROMOTIONS                     SH
FRA1
SYS_FBA_HIST_88064                                    ENABLED
SQL> set autot trace exp
SQL> select * from sh.promotions versions between timestamp  (systimestamp-interval '20' minute) and (systimestamp-interval '1' minute);
Execution Plan
----------------------------------------------------------
Plan hash value: 4106015420
--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |   503 | 49294 |    58   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| PROMOTIONS |   503 | 49294 |    58   (0)| 00:00:01 |
--------------------------------------------------------------------------------
SQL> ALTER TABLE  sh.promotions NO FLASHBACK ARCHIVE;
Table altered.




來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/13750068/viewspace-2144053/，如需轉載，請註明出處，否則將追究法律責任。

相關文章