Create a new user and
grant him the required privileges:
SQL> create user usr identified by usr;
User created.
SQL> grant connect, resource, flashback archive administer to usr;
Grant succeeded.
SQL>
Create a new separate tablespace for data archive
SQL> CREATE TABLESPACE tbs_arch DATAFILE D:\APP\ADMINISTRATOR\ORADATA\SRBUAT\f_archive.dbf' size 10m;
Tablespace created.
SQL>
SQL> create user usr identified by usr;
User created.
SQL> grant connect, resource, flashback archive administer to usr;
Grant succeeded.
SQL>
Create a new separate tablespace for data archive
SQL> CREATE TABLESPACE tbs_arch DATAFILE D:\APP\ADMINISTRATOR\ORADATA\SRBUAT\f_archive.dbf' size 10m;
Tablespace created.
SQL>
- Create flashback archive on this
tablespace using CREATE FLASHBACK ARCHIVE command as follows:
SQL> create flashback archive fl_archive
tablespace tbs_arch retention 1 year;
Flashback archive created.
SQL>
SQL> create flashback archive fl_archive
tablespace tbs_arch retention 1 year;
Flashback archive created.
SQL>
With above command we’ve created a Flashback Archive named
FL_ARCHIVE which resides in the tablespace TBS_ARCH and holds information for 1
year. It means that we can use any Flashback Query which contains 1 year
historical information regarding the table that assigned to this flashback
archive
- Now, create a table, insert one row
and assign it to this flashback archive:
SQL> create table tbl_fl_archive (id number, name varchar2(20));
Table created.
SQL> insert into tbl_fl_archive values(1,'Flashback Archive');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from tbl_fl_archive;
ID NAME
SQL> create table tbl_fl_archive (id number, name varchar2(20));
Table created.
SQL> insert into tbl_fl_archive values(1,'Flashback Archive');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from tbl_fl_archive;
ID NAME
---------- --------------------
1 Flashback
Archive
SQL> alter table tbl_fl_archive flashback archive
fl_archive;
Table altered.
SQL>
The historical change on the table TBL_FL_ARCHIVE is now will be written to the flashback archive named FL_ARCHIVE.
Table altered.
SQL>
The historical change on the table TBL_FL_ARCHIVE is now will be written to the flashback archive named FL_ARCHIVE.
- To test it, delete all rows and use
Flashback Query on that table. Remember, it will not look for the UNDO data, it
will look to the flashback archive file for the changes
SQL> select to_char(sysdate,'ddmmyyyy hh24:mi:ss')
ddate from dual;
DDATE
-----------------
13022010 12:46:49
DDATE
-----------------
13022010 12:46:49
SQL> delete from tbl_fl_archive;
1 row deleted.
SQL> commit;
Commit complete.
SQL> select * from tbl_fl_archive;
no rows selected
SQL> select * from tbl_fl_archive as of timestamp to_timestamp('30042012 2:46:00','ddmmyyyy hh24:mi:ss');
ID NAME
---------- --------------------
1 Flashback Archive
SQL> commit;
Commit complete.
SQL> select * from tbl_fl_archive;
no rows selected
SQL> select * from tbl_fl_archive as of timestamp to_timestamp('30042012 2:46:00','ddmmyyyy hh24:mi:ss');
ID NAME
---------- --------------------
1 Flashback Archive
SQL>
In order to show and proof that it doesn’t look to the UNDO tablespace for the historical information on the rows for the specific time, create new undo tablespace and make it default by dropping the old one. Then use Flashback Versions on that table:
In order to show and proof that it doesn’t look to the UNDO tablespace for the historical information on the rows for the specific time, create new undo tablespace and make it default by dropping the old one. Then use Flashback Versions on that table:
SQL> conn / as sysdba
Connected.
Connected.
SQL> show parameter undo_tablespace;
NAME TYPE VALUE
--------------------- ----------- --------- ------------
undo_tablespace string UNDOTBS1
--------------------- ----------- --------- ------------
undo_tablespace string UNDOTBS1
SQL> select a.name from v$datafile a, v$tablespace b
where a.ts#=b.ts# and b.name='UNDOTBS1';
NAME
------------------------------------------------
C:\APP\ADMINISTRATOR\ORADATA\DB2\UNDOTBS01.DBF
SQL> create undo tablespace undotbs2 datafile 'C:\APP\ADMINISTRATOR\ORADATA\DB2\UNDOTBS02.dbf' size 10m;
------------------------------------------------
C:\APP\ADMINISTRATOR\ORADATA\DB2\UNDOTBS01.DBF
SQL> create undo tablespace undotbs2 datafile 'C:\APP\ADMINISTRATOR\ORADATA\DB2\UNDOTBS02.dbf' size 10m;
Tablespace created.
SQL> alter system set undo_tablespace='UNDOTBS2';
System altered.
SQL> startup force
ORACLE instance started.
SQL> startup force
ORACLE instance started.
Total System Global Area
431038464 bytes
Fixed Size 1333676 bytes
Variable Size 251659860 bytes
Database Buffers 171966464 bytes
Redo Buffers 6078464 bytes
Database mounted.
Database opened.
Fixed Size 1333676 bytes
Variable Size 251659860 bytes
Database Buffers 171966464 bytes
Redo Buffers 6078464 bytes
Database mounted.
Database opened.
SQL> show parameter undo_tablespace;
NAME TYPE VALUE
--------------------------- ----------- ------------
undo_tablespace string UNDOTBS2
As you see, we’re currently using the different UNDO tablespace that hasn’t any information about before images of data blocks of the TBL_FL_ARCHIVE. Now, let’s use Flashback Query against to that table:
--------------------------- ----------- ------------
undo_tablespace string UNDOTBS2
As you see, we’re currently using the different UNDO tablespace that hasn’t any information about before images of data blocks of the TBL_FL_ARCHIVE. Now, let’s use Flashback Query against to that table:
SQL> conn us1/us1
Connected.
SQL> select * from tbl_fl_archive as of timestamp to_timestamp('13022010 12:45:30','ddmmyyyy hh24:mi:ss');
ID NAME
---------- --------------------
1 Flashback Archive
SQL>
This query gets the data from Flashback Data Archive
Modify the Flashback Data Archive
Use ALTER FLASHBACK ARCHIVE command to change the retention time, purge data or add or remove the tablespace. For this you need to have FLASHBACK ARCHIVE ADMINISTER privilege granted.
Connected.
SQL> select * from tbl_fl_archive as of timestamp to_timestamp('13022010 12:45:30','ddmmyyyy hh24:mi:ss');
ID NAME
---------- --------------------
1 Flashback Archive
SQL>
This query gets the data from Flashback Data Archive
Modify the Flashback Data Archive
Use ALTER FLASHBACK ARCHIVE command to change the retention time, purge data or add or remove the tablespace. For this you need to have FLASHBACK ARCHIVE ADMINISTER privilege granted.
- To change
the retention time, use:
SQL> alter flashback archive fl_archive modify
retention 6 month;
Flashback archive altered.
SQL>
- To change
tablespace quota of the tablespace that is used by a flashback data archive,
use:
SQL> alter flashback archive fl_archive add tablespace
tbs_arch quota 50m;
Flashback archive altered.
SQL>
- To add
another tablespace for flashback data archive, use:
SQL> create tablespace tbs_arch2 datafile
'c:\flashback_archive2.dbf' size 10m;
Tablespace created.
SQL> alter flashback archive fl_archive add
tablespace tbs_arch2 quota 10m;
Flashback archive altered.
SQL>
- To remove
the tablespace from use by flashback data archive, use:
SQL> alter flashback archive fl_archive remove
tablespace tbs_arch2;
Flashback archive altered.
SQL>
- To purge
the data that’s in Flashback Data Archive, use PURGE BEFORE SCN (TIMESTAMP) or
PURGE ALL as follows:
SQL> ALTER FLASHBACK ARCHIVE fl_archive PURGE ALL;
Flashback archive altered.
SQL> ALTER FLASHBACK ARCHIVE fl_archive purge before
timestamp to_timestamp('13022010 12:49:30','ddmmyyyy hh24:mi:ss');
Flashback archive altered.
SQL> ALTER FLASHBACK ARCHIVE fl_archive purge before
scn 988827;
Flashback archive altered.
SQL>
Dropping Flashback Data Archive
To drop flashback data archive use:
SQL> drop flashback archive fl_archive;
Flashback archive dropped.
SQL>
Using default Flashback Data Archive for the system
As default, Oracle doesn’t use any flashback data archive.
To set default flashback data archive, use the following command by connecting
as SYS user:
SQL> conn / as sysdba
Connected.
SQL> alter flashback archive fl_arc set default;
Flashback archive altered.
SQL>
By setting default flashback data archive, you will not need
to specify it manually on each table you want to keep changes
To disable flashback archive for a table, use:
SQL> alter table tbl_fl_archive no flashback archive;
Table altered.
SQL>
Query Flashback Data Archive
There’re mainly three views that are used to query the
information regarding Flashback Data Archive.
The first view is DBA_FLASHBACK_ARCHIVE which gives
information about Flashback Data Archive files
SQL> select * from dba_flashback_archive;
FLASHBACK_ARCHIVE_NAME FLASHBACK_ARCHIVE# RETENTION_IN_DAYS
CREATE_TIME
LAST_PURGE_TIME
---------------
--------------
------------ ----------
FL_ARCH
2 365 13-FEB-10
08.05.14.000000000 PM 13-FEB-10
08.05.14.000000000 PM
The second view is DBA_FLASHBACK_ARCHIVE_TS which gives
information about tablespace that contains flashback data archives:
SQL> select * from dba_flashback_archive_ts;
FLASHBACK_ARCHIVE_NAME
FLASHBACK_ARCHIVE#
TABLESPACE_NAME QUOTA_IN_MB
------------------
-----------------
------------- ----------
FL_ARCH 2 TBS_ARCH FL_ARC
The third view is FLASHBACK_ARCHIVE_TABLES which displays
the information of the tables that uses Flashback Data Archive:
SQL> select * from dba_flashback_archive_tables;
TABLE_NAME
OWNER_NAME
FLASHBACK_ARCHIVE_NAME
ARCHIVE_TABLE_NAME
----------
---------- ---------------------- ------------------
TBL_FL_ARCHIVE
US1 FL_ARC SYS_FBA_HIST_69845
As it was told above, Flashback Data Archie technology
stores data in Compressed and Partitioned format. It should be checked easily.
Get the name of the main table that’s used to store the data and query
USER_TAB_PARTITIONS view as follows:
SQL> select table_name,tablespace_name from
user_tables;
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
SYS_FBA_HIST_69845
SYS_FBA_TCRV_69845 TBS_ARCH
SYS_FBA_DDL_COLMAP_69845 TBS_ARCH
TBL_FL_ARCHIVE USERS
SQL> select table_name, partition_name, compression,
compress_for from user_tab_partitions where table_name='SYS_FBA_HIST_69845';
TABLE_NAME PARTITION_NAME COMPRESS
----------------
--------------
---------
SYS_FBA_HIST_69845 HIGH_PART ENABLED
SQL>
No comments:
Post a Comment