Oracle Technologies Blog

By ASKM

11g – Flashback Data Archive

Posted by Srikrishna Murthy Annam on October 1, 2009

11g – Flashback Data Archive (Oracle Total Recall) :

=====================================================================
DEMO               DEMO                DEMO              DEMO              DEMO                  DEMO                      DEMO
=====================================================================

SQL> sho user
USER is “SYS”
SQL> select name,user from v$database;

NAME      USER
——— ——————————
ASKM      SYS

SQL> CREATE TABLESPACE fda_ts DATAFILE ‘/u01/app/oracle/oradata/askm/fda_ts01.dbf’ SIZE 24M;

Tablespace created.

SQL> GRANT FLASHBACK ARCHIVE ADMINISTER TO sh;

Grant succeeded.

SQL> GRANT FLASHBACK ANY TABLE TO sh;

Grant succeeded.

SQL> GRANT EXECUTE ON DBMS_FLASHBACK TO sh;

Grant succeeded.

SQL> conn sh/sh
Connected.
SQL> CREATE FLASHBACK ARCHIVE fda TABLESPACE fda_ts     QUOTA 1M RETENTION 7 DAY;

Flashback archive created.

SQL> create table rate_conv (currency varchar2(6),Rate number(15,4));

Table created.

SQL> ALTER TABLE sh.rate_conv FLASHBACK ARCHIVE  fda;

Table altered.

SQL> insert into rate_conv values (‘IND-Rs’,48.1012);

1 row created.

SQL> commit;

Commit complete.

SQL> update rate_conv set rate=49.0120;

1 row updated.

SQL> commit;

Commit complete.

SQL> update rate_conv set rate=48.2012;

1 row updated.

SQL> commit;

Commit complete.

SQL> delete rate_conv;

1 row deleted.

SQL> commit;

Commit complete.

SQL> insert into rate_conv values (‘IND-Rs’,50.0021);

1 row created.

SQL> commit;

Commit complete.

SQL> update rate_conv set rate=49.0120;

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from rate_conv;

CURREN       RATE
—— ———-
IND-Rs     49.012

SQL> select versions_starttime,versions_endtime,versions_xid,versions_operation,rate from rate_conv versions between timestamp minvalue and maxvalue order by versions_starttime;

VERSIONS_STARTTIME        VERSIONS_ENDTIME          VERSIONS_XID     V       RATE
————————- ————————- —————- – ———-
01-OCT-09 08.56.25 AM     01-OCT-09 08.56.28 AM     0A0020009B020000 I    48.1012
01-OCT-09 08.56.28 AM     01-OCT-09 08.56.31 AM     060011002A030000 U     49.012
01-OCT-09 08.56.31 AM     01-OCT-09 08.56.40 AM     03000F003A030000 U    48.2012
01-OCT-09 08.56.40 AM                               050005006D030000 D    48.2012
01-OCT-09 08.56.44 AM     01-OCT-09 08.56.48 AM     0900090027030000 I    50.0021
01-OCT-09 08.56.48 AM                               0200010018030000 U     49.012

6 rows selected.

SQL> desc dba_FLASHBACK_ARCHIVE_TABLES;
Name                                                                                Null?    Type
———————————————————————————– ——– ——————————————————–
TABLE_NAME                                                                          NOT NULL VARCHAR2(30)
OWNER_NAME                                                                          NOT NULL VARCHAR2(30)
FLASHBACK_ARCHIVE_NAME                                                              NOT NULL VARCHAR2(255)
ARCHIVE_TABLE_NAME                                                                           VARCHAR2(53)
STATUS                                                                                       VARCHAR2(8)

SQL> select TABLE_NAME, OWNER_NAME, FLASHBACK_ARCHIVE_NAME, ARCHIVE_TABLE_NAME from dba_FLASHBACK_ARCHIVE_TABLES;

TABLE_NAME                     OWNER_NAME
—————————— ——————————
FLASHBACK_ARCHIVE_NAME
——————————————————————————————————————————————————
ARCHIVE_TABLE_NAME
—————————————————–
RATE_CONV                      SH
FDA
SYS_FBA_HIST_74714

SQL> select TABLE_NAME, OWNER_NAME, FLASHBACK_ARCHIVE_NAME, ARCHIVE_TABLE_NAME from dba_FLASHBACK_ARCHIVE_TABLES;

TABLE_NAME OWNER_NAME FLASHBACK_ARCHIVE_NAME    ARCHIVE_TABLE_NAME
———- ———- ————————- ——————–
RATE_CONV  SH         FDA                       SYS_FBA_HIST_74714

SQL> desc SYS_FBA_HIST_74714
Name                                                  Null?    Type
—————————————————– ——– ————————————
RID                                                            VARCHAR2(4000)
STARTSCN                                                       NUMBER
ENDSCN                                                         NUMBER
XID                                                            RAW(8)
OPERATION                                                      VARCHAR2(1)
CURRENCY                                                       VARCHAR2(6)
RATE                                                           NUMBER(15,4)

SQL> select count(1) from SYS_FBA_HIST_74714;

COUNT(1)
———-
4

SQL> desc rate_conv
Name                                                  Null?    Type
—————————————————– ——– ————————————
CURRENCY                                                       VARCHAR2(6)
RATE                                                           NUMBER(15,4)

SQL> select * from SYS_FBA_HIST_74714;

RID                   STARTSCN    ENDSCN XID                       O CURREN       RATE
——————– ——— ——— ————————- – —— ———-
AAASPaAAEAABhUvAAA     1234564   1234570 0A0020009B020000          I IND-Rs    48.1012
AAASPaAAEAABhUvAAA     1234570   1234573 060011002A030000          U IND-Rs     49.012
AAASPaAAEAABhUvAAA     1234573   1234577 03000F003A030000          U IND-Rs    48.2012
AAASPaAAEAABhUvAAB     1234588   1234591 0900090027030000          I IND-Rs    50.0021

SQL> select * from rate_conv;

CURREN       RATE
—— ———-
IND-Rs     49.012

SQL> conn /as sysdba
Connected.
SQL> CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE ‘/u01/app/oracle/oradata/askm/undotbs2_01.dbf’ SIZE 2M;

Tablespace created.

SQL> ALTER SYSTEM CHECKPOINT;

System altered.

SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;

System altered.

SQL> ALTER SYSTEM SET UNDO_TABLESPACE = ‘UNDOTBS2’ SCOPE=BOTH;

System altered.

SQL> conn sh/sh
Connected.
SQL> select versions_starttime,versions_endtime,versions_xid,versions_operation,rate from rate_conv versions between timestamp minvalue and maxvalue order by versions_starttime;

VERSIONS_STARTTIME                  VERSIONS_ENDTIME                    VERSIONS_XID     V       RATE
———————————– ———————————– —————- – ———-
01-OCT-09 08.56.25.000000000 AM     01-OCT-09 08.56.28.000000000 AM     0A0020009B020000 I    48.1012
01-OCT-09 08.56.28.000000000 AM     01-OCT-09 08.56.31.000000000 AM     060011002A030000 U     49.012
01-OCT-09 08.56.31.000000000 AM     01-OCT-09 08.56.40.000000000 AM     03000F003A030000 U    48.2012
01-OCT-09 08.56.44.000000000 AM     01-OCT-09 08.56.48.000000000 AM     0900090027030000 I    50.0021
01-OCT-09 08.56.48.000000000 AM                                         0200010018030000 U     49.012

SQL> select rate from rate_conv as of timestamp to_timestamp(‘2009-10-01 08:56:29′,’yyyy-mm-dd hh24:mi:ss’);

RATE
———-
48.1012

SQL> explain plan for select rate from rate_conv as of timestamp to_timestamp(‘2009-10-01 08:56:29′,’yyyy-mm-dd hh24:mi:ss’);

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
——————————————————————————————————————————————————
Plan hash value: 3276248349

—————————————————————————————————————-
| Id  | Operation                 | Name               | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
—————————————————————————————————————-
|   0 | SELECT STATEMENT          |                    |     5 |    65 |     9  (12)| 00:00:01 |       |       |
|   1 |  VIEW                     |                    |     5 |    65 |     9  (12)| 00:00:01 |       |       |
|   2 |   UNION-ALL               |                    |       |       |            |          |       |       |
|*  3 |    FILTER                 |                    |       |       |            |          |       |       |
|   4 |     PARTITION RANGE SINGLE|                    |     1 |    39 |     3   (0)| 00:00:01 |   KEY |     1 |
|*  5 |      TABLE ACCESS FULL    | SYS_FBA_HIST_74714 | 1 |    39 |     3   (0)| 00:00:01 |   KEY |     1 |
|*  6 |    FILTER                 |                    |       |       |            |          |       |       |
|*  7 |     HASH JOIN OUTER       |                    |     4 |  8212 |     6  (17)| 00:00:01 |       |       |
|*  8 |      TABLE ACCESS FULL    | RATE_CONV          |     4 |   100 |     2   (0)| 00:00:01 |       |       |
|*  9 |      TABLE ACCESS FULL    | SYS_FBA_TCRV_74714 |     2 |  4056 |     3   (0)| 00:00:01 |       |       |
—————————————————————————————————————-

Predicate Information (identified by operation id):
—————————————————

3 – filter(“TIMESTAMP_TO_SCN”(TIMESTAMP’ 2009-10-01 08:56:29.000000000′)<1242042)
5 – filter(“ENDSCN”<=1242042 AND “ENDSCN”>”TIMESTAMP_TO_SCN”(TIMESTAMP’ 2009-10-01
08:56:29.000000000′) AND (“STARTSCN” IS NULL OR “STARTSCN”<=”TIMESTAMP_TO_SCN”(TIMESTAMP’ 2009-10-01
08:56:29.000000000′)))
6 – filter(“STARTSCN”<=”TIMESTAMP_TO_SCN”(TIMESTAMP’ 2009-10-01 08:56:29.000000000′) OR “STARTSCN”
IS NULL)
7 – access(“T”.ROWID=CHARTOROWID(“RID”(+)))
8 – filter(“T”.”VERSIONS_STARTSCN” IS NULL)
9 – filter((“ENDSCN”(+) IS NULL OR “ENDSCN”(+)>1242042) AND (“STARTSCN”(+) IS NULL OR
“STARTSCN”(+)<1242042))

Note
—–
– dynamic sampling used for this statement (level=2)

34 rows selected.

SQL>

SQL> alter table rate_conv NO FLASHBACK ARCHIVE;

Table altered.

Flashback Data Archives information from Data Dictionary :

SQL> conn /as sysdba
Connected.
SQL> sho user
USER is “SYS”
SQL> SELECT table_name FROM dict WHERE  table_name LIKE ‘%FLASHBACK_ARCHIVE%’;

TABLE_NAME
——————————
DBA_FLASHBACK_ARCHIVE
DBA_FLASHBACK_ARCHIVE_TABLES
DBA_FLASHBACK_ARCHIVE_TS
USER_FLASHBACK_ARCHIVE
USER_FLASHBACK_ARCHIVE_TABLES

SQL> SELECT flashback_archive_name, create_time, status FROM   dba_flashback_archive;

FLASHBACK_ CREATE_TIME                                   STATUS
———- ——————————————— ———-
FDA        01-OCT-09 08.55.34.000000000 AM

SQL> SELECT * FROM   dba_flashback_archive_ts;

FLASHBACK_ FLASHBACK_ARCHIVE# TABLESPACE_NAME
———- —————— ——————————
QUOTA_IN_MB
—————————————-
FDA                         1 FDA_TS
1

SQL> SELECT * FROM   dba_flashback_archive_tables;

TABLE_NAME                     OWNER_NAME                     FLASHBACK_
—————————— —————————— ———-
ARCHIVE_TABLE_NAME                                    STATUS
—————————————————– ———-
RATE_CONV                      SH                             FDA
SYS_FBA_HIST_74714                                    ENABLED

Maintaining Flashback Data Archive (FBDAs):

SQL> ALTER FLASHBACK ARCHIVE fda MODIFY TABLESPACE fda_ts QUOTA 2M;

Flashback archive altered.

SQL> ALTER FLASHBACK ARCHIVE fda MODIFY RETENTION 90 DAY;

Flashback archive altered.

SQL> CREATE TABLESPACE fda_ts2 DATAFILE ‘/u01/app/oracle/oradata/askm/fda_ts02.dbf’ SIZE 16M;

Tablespace created.

SQL> ALTER FLASHBACK ARCHIVE fda ADD TABLESPACE fda_ts2;

Flashback archive altered.

SQL> ALTER FLASHBACK ARCHIVE fda PURGE BEFORE TIMESTAMP(SYSTIMESTAMP – INTERVAL ‘1’ DAY);

Flashback archive altered.

SQL> DROP FLASHBACK ARCHIVE fda;

Flashback archive dropped.



11g – Flashback Data Archive (Oracle Total Recall)

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: