Search This Blog

Wednesday, January 22, 2014

Flashback Data Archive (Oracle Total Recall) 11g.

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             

EMP                            SCOTT                          FLASH_DATA                     SYS_FBA_HIST_73181             


SQL> select flashback_archive_name, status from dba_flashback_archive;

------------------------------ -------
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;


---------- ---------------------- ------------------ ----------------- ----------------------------------- 

---------------------------------- -------
SYS        FLASH_DATA                              1               365 21-JAN-14 PM     21-JAN-14 PM    DEFAULT

Values usable with systimestamp

systimestamp - interval '60' second/minute/day/month

SQL> select * from dba_flashback_archive_ts;

---------------------- ------------------ ------------------------------ ----------------------------------------
FLASH_DATA                              1 FDA                            1024

SQL> select sal from emp where empno=7934;


SQL> update emp set sal=2000 where empno=7934;

1 row updated.

SQL> commit;

Commit complete.

SQL> select sal from emp where empno=7934;


#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


Saturday, January 18, 2014

ADRCI Walkthrough.

Invoke adrci as the oracle user.

> adrci

ADRCI: Release - 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 home
ADR Homes:

Set the home

adrci> set home diag/rdbms/orcl/ORCL

To check the alert log use the following option.

adrci> show alert -tail -f
2014-01-17 22:28:35.440000 +00:00
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:


adrci> show alert -tail 


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 fetched

To check for incedents related to problems.

adrci> show incident

ADR Home = /opt/oracle/diag/rdbms/orcl/ORCL:
----------------- ------------------------- ---------------------------------
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 fetched

To get more details on the incident execute the following

adrci> show incident -mode detail -p "incident_id=3807"

adrci> show trace 

This 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 fetched

All 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 problem

ADR Home = /opt/oracle/diag/asm/+asm/+ASM1:
-------------------- -----------------------------------------------------------
1                    ORA 4030
1 rows fetched

ADR Home = /opt/oracle/diag/asm/+asm/+ASM1:
-------------------- -----------------------------------------------------------
1                    ORA 4030
1 rows fetched

adrci> ips create package problem 1 correlate all
Created 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/, mode complete

#Now create a zip file from the logical package