facebook twitter youtube facebook facebook facebook

E-Mail : info@askmlabs.com

Phone : +1.215.353.8306

Home » » 11g - Flashback Data Archive

11g - Flashback Data Archive

Written By askMLabs on Thursday, October 1, 2009 | 4:43 PM

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)



Share this article :

Related Articles By Category



Post a Comment

Thank you for visiting our site and leaving your valuable comment.

 
Support :
Copyright © 2013. askMLabs - All Rights Reserved
Proudly powered by Blogger