DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Wednesday, September 30, 2015

STEPS TO CREATE FLASHBACK DATA ARCHIVE IN ORACLE DATABASE 12C RELEASE 1

Flashback Data Archive (FDA), also known as Flashback Archive (FBA),
was introduced in Oracle 11g to provide long term storage of undo data,
allowing undo-based flashback operations to be performed over an extended period of time.
Oracle database 12c includes a number of changes that will allow FDA to reach a wider audience.

Step 1: Create a new tablespace and an FDA with a 1 year retention period as below

CONN AS SYS

CREATE TABLESPACE fda_ts DATAFILE SIZE 1M AUTOEXTEND ON NEXT 1M;
ALTER USER test QUOTA UNLIMITED ON fda_ts;

CREATE FLASHBACK ARCHIVE DEFAULT fda_1year TABLESPACE fda_ts
QUOTA 10G RETENTION 1 YEAR;

Step 2: FLASHBACK ARCHIVE privilege granted on the FDA to the TEST user

GRANT FLASHBACK ARCHIVE ON fda_1year TO test;
GRANT FLASHBACK ARCHIVE ADMINISTER TO test;
GRANT EXECUTE ON DBMS_FLASHBACK_ARCHIVE TO test;
GRANT CREATE ANY CONTEXT TO test;

Step 3: Information about the FDA is displayed using the DBA_FLASHBACK_ARCHIVE and DBA_FLASHBACK_ARCHIVE_TS views

SET LINESIZE 150

COLUMN owner_name FORMAT A20
COLUMN flashback_archive_name FORMAT A22
COLUMN create_time FORMAT A20
COLUMN last_purge_time FORMAT A20

SELECT owner_name,
       flashback_archive_name,
       flashback_archive#,
       retention_in_days,
       TO_CHAR(create_time, 'DD-MON-YYYY HH24:MI:SS') AS create_time,
       TO_CHAR(last_purge_time, 'DD-MON-YYYY HH24:MI:SS') AS last_purge_time,
       status
FROM   dba_flashback_archive
ORDER BY owner_name, flashback_archive_name;

OWNER_NAME  FLASHBACK_ARCHIVE_NAME FLASHBACK_ARCHIVE# RETENTION_IN_DAYS CREATE_TIME          LAST_PURGE_TIME      STATUS
-------------------- ---------------------- ------------------ ----------------- -------------------- --------------------
SYS           FDA_1YEAR                        1               365       06-JAN-2015 19:30:57 06-JAN-2015 19:30:57 DEFAULT



SET LINESIZE 150

COLUMN flashback_archive_name FORMAT A22
COLUMN tablespace_name FORMAT A20
COLUMN quota_in_mb FORMAT A11

SELECT flashback_archive_name,
       flashback_archive#,
       tablespace_name,
       quota_in_mb
FROM   dba_flashback_archive_ts
ORDER BY flashback_archive_name;

FLASHBACK_ARCHIVE_NAME FLASHBACK_ARCHIVE# TABLESPACE_NAME      QUOTA_IN_MB
---------------------- ------------------ -------------------- -----------
FDA_1YEAR                               1 FDA_TS               10240

Step 4 : Create a table in the test user schema and associate it with the FDA

conn test/xxxx

CREATE TABLE tab1 (
  id           NUMBER,
  description  VARCHAR2(50),
  CONSTRAINT tab_1_pk PRIMARY KEY (id)
) FLASHBACK ARCHIVE fda_1year;

-- Use ALTER TABLE to associate an existing table.
-- ALTER TABLE tab1 FLASHBACK ARCHIVE fda_1year;

Step 5 : Information about the FDA and the associated table is displayed using the DBA_FLASHBACK_ARCHIVE_TABLES view

SET LINESIZE 150

COLUMN owner_name FORMAT A20
COLUMN table_name FORMAT A20
COLUMN flashback_archive_name FORMAT A22
COLUMN archive_table_name FORMAT A20

SELECT owner_name,
       table_name,
       flashback_archive_name,
       archive_table_name,
       status
FROM   dba_flashback_archive_tables
ORDER BY owner_name, table_name;

OWNER_NAME           TABLE_NAME           FLASHBACK_ARCHIVE_NAME ARCHIVE_TABLE_NAME   STATUS
-------------------- -------------------- ---------------------- -------------------- -------------
TEST                 TAB1                 FDA_1YEAR              SYS_FBA_HIST_95999   ENABLED

Step 6 : We need to use the DBMS_FLASHBACK_ARCHIVE.SET_CONTEXT_LEVEL procedure, passing one of the following parameter values.

((Although FDA has always been able to track the changes to data, it was not able to provide
a complete audit of the changes unless columns were present in the table to indicate who
made those changes. In Oracle 12c, the contents of the contexts available from SYS_CONTEXT calls, including USERENV, can be stored along with the data changes,
allowing for a complete audit of both the data changes and the user environment setting.))

TYPICAL : Only basic auditing attributes from the USERENV context are stored.
ALL : All contexts available to the user via the SYS_CONTEXT function are stored.
NONE : No context information is stored.

In this case use ALL, so we capture the USERENV and custom context values.

CONN AS SYS

EXEC DBMS_FLASHBACK_ARCHIVE.set_context_level('ALL');

Note: Remember to wait at least 15 seconds between creating the FDA associated table
and committing any DML or information may be lost.

Step 7 : Create a custom context and management package for use in the test

CONN test/xxxx

CREATE OR REPLACE CONTEXT test_context USING test_ctx_api;

CREATE OR REPLACE PACKAGE test_ctx_api AS

PROCEDURE set_value (p_name  IN VARCHAR2,
                     p_value IN VARCHAR2);

END test_ctx_api;
/

CREATE OR REPLACE PACKAGE BODY test_ctx_api AS

PROCEDURE set_value (p_name  IN VARCHAR2,
                     p_value IN VARCHAR2) AS
BEGIN
  DBMS_SESSION.set_context('test_context', LOWER(p_name), p_value);
END;

END test_ctx_api;
/

Step 8 : Insert and amend some data. Between each action we alter the V$SESSION.CLIENT_IDENTIFIER value and alter the value in our custom context

CONN test/xxxx

EXEC DBMS_SESSION.set_identifier('Peter Pan');
EXEC test.test_ctx_api.set_value('my_attribute','First Action');

INSERT INTO tab1 VALUES (1, 'ONE');
COMMIT;

EXEC DBMS_SESSION.set_identifier('Peter Parker');
EXEC test_ctx_api.set_value('my_attribute','Second Action');

UPDATE tab1
SET    description = 'TWO'
WHERE  id = 1;
COMMIT;

EXEC DBMS_SESSION.set_identifier('Peter Rabbit');
EXEC test_ctx_api.set_value('my_attribute','Third Action');

UPDATE tab1
SET    description = 'THREE'
WHERE  id = 1;
COMMIT;

Step 9 : Check the context information is working as expected

CONN test/xxxx

COLUMN versions_startscn FORMAT 99999999999999999
COLUMN versions_starttime FORMAT A24
COLUMN versions_endscn FORMAT 99999999999999999
COLUMN versions_endtime FORMAT A24
COLUMN versions_xid FORMAT A16
COLUMN versions_operation FORMAT A1
COLUMN description FORMAT A11
COLUMN session_user FORMAT A20
COLUMN client_identifier FORMAT A20
COLUMN my_attribute FORMAT A20
SET LINESIZE 200

SELECT versions_startscn,
       --versions_starttime,
       versions_endscn,
       --versions_endtime,
       versions_xid,
       versions_operation,
       description,
       DBMS_FLASHBACK_ARCHIVE.get_sys_context(versions_xid, 'USERENV','SESSION_USER') AS session_user,
       DBMS_FLASHBACK_ARCHIVE.get_sys_context(versions_xid, 'USERENV','CLIENT_IDENTIFIER') AS client_identifier,
       DBMS_FLASHBACK_ARCHIVE.get_sys_context(versions_xid, 'test_context','my_attribute') AS my_attribute
FROM   tab1
       VERSIONS BETWEEN TIMESTAMP SYSTIMESTAMP-(1/24/60) AND SYSTIMESTAMP
WHERE  id = 1
ORDER BY versions_startscn;

 VERSIONS_STARTSCN    VERSIONS_ENDSCN VERSIONS_XID     V DESCRIPTION SESSION_USER         CLIENT_IDENTIFIER    MY_ATTRIBUTE
------------------ ------------------ ---------------- - ----------- -------------------- -------------------- --------------------
           2536699            2536792 060010000B0A0000 I ONE         TEST                 Peter Pan            First Action
           2536792            2536826 0A00110076060000 U TWO         TEST                 Peter Parker         Second Action
           2536826                    020003005B080000 U THREE       TEST                 Peter Rabbit         Third Action

Step 10 : Remember, if you want to drop the table, you must first remove it from the FDA. Some of the following examples make use of this table, so delay dropping it until you have worked through them.

CONN  AS SYS

ALTER TABLE test.tab1 NO FLASHBACK ARCHIVE;
DROP TABLE test.tab1 PURGE;

Step 11: Export/Import Table History

The DBMS_FLASHBACK_ARCHIVE package contains routines to allow the history of a specified table to be exported and imported.

To export the history data, call the CREATE_TEMP_HISTORY_TABLE procedure
history you want to export.

CONN test/xxxx

BEGIN
  DBMS_FLASHBACK_ARCHIVE.create_temp_history_table(
    owner_name1 => 'TEST',
    table_name1 => 'TAB1');
END;
/

A table called TEMP_HISTORY is created containing the relevant data

DESC temp_history

 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 RID                                                            VARCHAR2(4000)
 STARTSCN                                                       NUMBER
 ENDSCN                                                         NUMBER
 XID                                                            RAW(8)
 OPERATION                                                      VARCHAR2(1)
 ID                                                             NUMBER
 DESCRIPTION                                                    VARCHAR2(50)

 Step 12 : Once exported, the table can be renamed if you wish

If the data needs to be loaded into another schema, it can be transferred using any of the usual methods,for example data pump.
You can also manually amend the contents to load information from alternative audit sources into the FDA.

The contents of a history table can be loaded into the FDA using the IMPORT_HISTORY procedure.
The owner and name of the FDA-backed table are specified, along with the name of the history
table and options for how any existing history data is handled

BEGIN
  DBMS_FLASHBACK_ARCHIVE.import_history (
    owner_name1       => 'TEST',
    table_name1       => 'TAB1',
    temp_history_name => 'TEMP_HISTORY', -- Default Setting.
    options           => DBMS_FLASHBACK_ARCHIVE.NODELETE); -- Allowable values: NODROP, NOCOMMIT, NODELETE
END;
/

No comments: