Starting from 11g Oracle has added another tool to its bundle of flashback technologies called Flashback
Data Archive(Oracle Total Recall).
With this feature previous state of a table can be viewed. But Unlike Flashback query
and or Flashback transaction query this feature does not depend on the undo_retention
of the database.
Flashback Data Archive requires one or more tablespaces where it can store historical data
for one or several tables.
whenever the data in the table is modified its original unmodified value is written to the undo
tablespace. From there the Flashback Data Archiver Process(FBDA) will collect it and write
the data to flashback data archive.
#Create a Tablespace for the Flashback Data Archive
SQL> create tablespace fda datafile '/u01/fda01.dbf' size 500M
autoextend on next 100M
extent management local
segment space management auto;
SQL> select * from dba_sys_privs where privilege like '%FLASH%';
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
SCOTT FLASHBACK ARCHIVE ADMINISTER NO
SYS FLASHBACK ANY TABLE NO
DBA FLASHBACK ANY TABLE YES
SYS FLASHBACK ARCHIVE ADMINISTER NO
DBA FLASHBACK ARCHIVE ADMINISTER YES
MDSYS FLASHBACK ANY TABLE NO
6 rows selected.
SQL> grant flashback archive administer to scott;
Grant succeeded.
SQL> create flashback archive flash_data
tablespace fda
quota 4g
retention 5 year;
Flashback archive created.
#Quota is optional.It means that FDA can only use upto 4Gb space in the tablespace.
SQL> drop flashback archive flash_data;
Flashback archive dropped.
Modify commands
#Make it the default archive
SQL> alter flashback archive flash_data set default;
Flashback archive altered.
#Add space to it.
SQL> alter flashback archive flash_data add tablespace abc;
Flashback archive altered.
#Change its quota
SQL> alter flashback archive flash_data modify tablespace fda quota 1G;
Flashback archive altered.
#Change retention
SQL> alter flashback archive flash_data modify retention 1 year;
Flashback archive altered.
#Remove tablespace from flashback data archive
SQL> alter flashback archive flash_data
2 remove tablespace abc;
Flashback archive altered.
#Purge data from flashback data archive
SQL> alter flashback archive flash_data purge all;
Flashback archive altered.
#Purge using interval clause
SQL> alter flashback archive flash_data purge before
2 timestamp(systimestamp - interval '5' day);
Flashback archive altered.
#Purge using SCN Clause
SQL> alter flashback archive flash_data purge before scn 799684;
Flashback archive altered.
Playing with it.
SQL> create table t1
2 (id int,name varchar2(30))
3 flashback archive flash_data;
Table created.
SQL> alter table emp flashback archive;
Table altered.
#Below in the column ARCHIVE_TABLE_NAME tables which have been created in the FBA tablespace can be seen.
SQL> select * from dba_flashback_archive_tables;
TABLE_NAME OWNER_NAME FLASHBACK_ARCHIVE_NAME ARCHIVE_TABLE_NAME STATUS
------------------------------ ------------------------------ ------------------------------ ------------------------------
--------
T1 SCOTT FLASH_DATA SYS_FBA_HIST_74560
ENABLED
EMP SCOTT FLASH_DATA SYS_FBA_HIST_73181
ENABLED
SQL> select flashback_archive_name, status from dba_flashback_archive;
FLASHBACK_ARCHIVE_NAME STATUS
------------------------------ -------
FLASH_DATA DEFAULT
SQL> alter table emp no flashback archive;
Table altered.
SQL> alter table emp flashback archive flash_data;
Table altered.
SQL> select * from dba_flashback_archive;
OWNER_NAME FLASHBACK_ARCHIVE_NAME FLASHBACK_ARCHIVE# RETENTION_IN_DAYS CREATE_TIME LAST_PURGE_TIME
STATUS
---------- ---------------------- ------------------ ----------------- -----------------------------------
---------------------------------- -------
SYS FLASH_DATA 1 365 21-JAN-14 04.09.51.000000000 PM 21-JAN-14
04.23.59.000000000 PM DEFAULT
Values usable with systimestamp
systimestamp - interval '60' second/minute/day/month
SQL> select * from dba_flashback_archive_ts;
FLASHBACK_ARCHIVE_NAME FLASHBACK_ARCHIVE# TABLESPACE_NAME QUOTA_IN_MB
---------------------- ------------------ ------------------------------ ----------------------------------------
FLASH_DATA 1 FDA 1024
SQL> select sal from emp where empno=7934;
SAL
----------
1300
SQL> update emp set sal=2000 where empno=7934;
1 row updated.
SQL> commit;
Commit complete.
SQL> select sal from emp where empno=7934;
SAL
----------
2000
#After modifying the we can see the previous state with the as of clause.
SQL> select * from emp as of timestamp(systimestamp - interval '10' minute) where empno=7934;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7934 MILLER CLERK 7782 23-JAN-82 1300 10
Wednesday, January 22, 2014
Flashback Data Archive (Oracle Total Recall) 11g.
Saturday, January 18, 2014
ADRCI Walkthrough.
Invoke adrci as the oracle user. > adrciADRCI: Release 11.2.0.3.0 - Production on Fri Jan 17 22:38:57 2014 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. ADR base = "/opt/oracle"adrci> show homeADR Homes: diag/diagtool/user_oracle/host_365570786_11 diag/rdbms/orcl/ORCL diag/asm/+asm/+ASM4 diag/tnslsnr/orcl4/listener_cadSet the home adrci> set home diag/rdbms/orcl/ORCL To check the alert log use the following option. adrci> show alert -tail -f2014-01-17 22:28:35.440000 +00:00 ALTER SYSTEM ARCHIVE LOG 2014-01-17 22:28:38.414000 +00:00 Thread 4 advanced to log sequence 1392 (LGWR switch) Current log# 11 seq# 1392 mem# 0: +REDO/orcl/redot4g11f1.rdo Current log# 11 seq# 1392 mem# 1: +REDO/orcl/redot4g11f2.rdo Archived Log entry 10628 added for thread 4 sequence 1391 ID 0xc5495541 dest 1:or adrci> show alert -tail or adrci> show alert To check for severe problems in database. adrci> show problem ADR Home = /opt/oracle/diag/rdbms/orcl/ORCL:************************************************************************* PROBLEM_ID PROBLEM_KEY LAST_INCIDENT LASTINC_TIME -------------------- ----------------------------------------------------------- -------------------- ---------------------------------------- 2 ORA 600 [723] 3807 2007-06-18 21:35:47.862114 -07:00 1 ORA 29770 24129 2011-10-21 19:08:24.097000 +00:00 2 rows fetchedTo check for incedents related to problems. adrci> show incidentADR Home = /opt/oracle/diag/rdbms/orcl/ORCL: ***************************************************************************** INCIDENT_ID PROBLEM_KEY CREATE_TIME ----------------- ------------------------- --------------------------------- 3808 ORA 603 2007-06-18 21:35:49.322161 -07:00 3807 ORA 600 [723] 2007-06-18 21:35:47.862114 -07:00 3805 ORA 600 [723] 2007-06-18 21:35:25.012579 -07:00 3804 ORA 1578 2007-06-18 21:35:08.483156 -07:00 4 rows fetchedTo get more details on the incident execute the following adrci> show incident -mode detail -p "incident_id=3807" adrci> show traceThis statement lists the names of all trace files related to incident number 1681: adrci> SHOW TRACEFILE -I 1681 adrci> show control ADR Home = /opt/oracle/diag/rdbms/orcl/ORCL: ************************************************************************* ADRID SHORTP_POLICY LONGP_POLICY LAST_MOD_TIME LAST_AUTOPRG_TIME LAST_MANUPRG_TIME ADRDIR_VERSION ADRSCHM_VERSION ADRSCHMV_SUMMARY ADRALERT_VERSION CREATE_TIME -------------------- -------------------- -------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- -------------------- -------------------- -------------------- -------------------- ---------------------------------------- 3783208084 720 8760 2013-12-16 19:28:24.827055 +00:00 2014-01-17 21:09:07.527157 +00:00 2014-01-17 10:15:21.303696 +00:00 1 2 80 1 2013-12-16 19:28:24.827055 +00:00 1 rows fetchedAll the tracefiles will stay for 30 days (720 hours), but all the incident files would be kept for one year (8760 hours) by default. This can be changed by the following command adrci> set control (SHORTP_POLICY = 48) adrci> set control (LONGP_POLICY = 48) Purging manually Files can be purged manually by the following commands. The value "2880" is in minutes. adrci> purge -age 2880 -type incident adrci> purge -age 2880 -type ALERT adrci> purge -age 2880 -type TRACE adrci> purge -age 2880 -type CDUMP adrci> purge -age 2880 -type HM Creation of Packages & ZIP files to send to Oracle Support adrci> set home diag/asm/+asm/+ASM1 adrci> show problemADR Home = /opt/oracle/diag/asm/+asm/+ASM1: ************************************************************************* PROBLEM_ID PROBLEM_KEY -------------------- ----------------------------------------------------------- 1 ORA 4030 1 rows fetchedADR Home = /opt/oracle/diag/asm/+asm/+ASM1: ************************************************************************* PROBLEM_ID PROBLEM_KEY -------------------- ----------------------------------------------------------- 1 ORA 4030 1 rows fetchedadrci> ips create package problem 1 correlate allCreated package 1 based on problem id 1, correlation level all#Create logical package with correlation level all. adrci> ips generate package 1 in "/opt/oracle"Generated package 1 in file /opt/oracle/ORA4030_20140219202848_COM_1.zip, mode complete#Now create a zip file from the logical package
Subscribe to:
Posts (Atom)