Saturday, December 27, 2014

Oracle 12c #1 New feature - Extended Character data types

Oracle 12c increases the maximum size of below data types from 2K to 32K

  1. Varchar2
  2. Nvarchar2
  3. RAW

Column with a data value of 4K or less will be stored inline.
If a column data value greater than 4k will be stored out of line (Oracle LOB/CLOB)

Init parameter changes:
MAX_SQL_STRING_SIZE = EXTENDED ( Legacy is default value)
Compatible =12.0.*

Extended Character data types is not supported in clustered and index organized tables.

I am Back !!!


Suddenly one day morning i disabled my blog and went away from oracle community. It almost close to 2 years now.. There was no actual reason to disabled my blog and not writing anything..

I Personally and Professionally feel good. Both 2013 and 2014 years are awesome year in my career/personal...

I bought my first car , first home and finally married !!! 
Wow.. What a fantastic year 2014 ? 

Then what next?

Last few months I noticed "losing my technical skills and to convincing the oracle techie its very challenge to me nowadays..." 

I read my old articles since today morning and felt very bad/headache ..All are crap :-)

I am always serious about my career  and asking myself weekly once...

"What do you want to be doing five years from now?"

            I would like to see myself as Architect or Oracle Consultant..

So I am started again with fresh energy !!!

"It's important to know that at the end of the day it's not the medals you remember. What you remember is the process -- what you learn about yourself by challenging yourself, the experiences you share with other people, the honesty the training demands -- those are things nobody can take away from you whether you finish twelfth or you're an Olympic Champion."

Silken Laumann, Canadian Olympian

Thank you Everyone for your Support ...

Wednesday, February 6, 2013

Oracle Total Recall – Flashback Data Archive - Oracle 11gR2 Feature


Oracle Total Recall will help to keep the historical data in database.   Oracle Total Recall tracking the changes happen on database and keep this data’s in database based on our retention period.

Oracle Provides lot of flashback features in earlier version. 

DB version
Flashback Feature

Oracle 9i
Flashback Query
Oracle 10g
Flashback Version Query
Oracle 10g
Flashback Table
Oracle 10g
Flashback Database
Oracle 11g
Flashback Data Archive/Oracle Total Recall
Oracle 11g
Flashback Transaction Back out

Oracle Database 11g introduces Total Recall based on the Flashback Data Archive feature, which transparently tracks changes to database tables in a highly secure and efficient manner. How Total Recall is different from others. Let’s see in this article.

Already we have flashback features, How FDA is better than existing flashback feature?

Flashback query provides the old data’s from undo tablespace and Flashback based on below init parameters.


Suppose flashback retention is 24 hours and flashback  keeps 1 day flashback logs, if we need last 48 hrs old data, we don’t get the old data and received the below error. FDA will provide the solution to access the old data based on FDA table retention.


ORA-01466: unable to read data - table definition has changed

ORA-08180: no snapshot found based on specified time

ORA-01555: snapshot too old: rollback segment number 7 with
name "_SYSSMU7$“too small

How we are tracking the Historical Data now?

1.     Application Level: This one is very complex and keep the historical data with data integrity is very difficult. Based on business requirements they are using to track the historical data. As my little knowledge rarely using this approach in some environment.

2.     Database Level:

Database Triggers:

It will help to track the db changes and keep the changes on other change tables. Here we have maintain the data’s using partitions very easily and purge the old data’ from change tables based on our retention. These database triggers will impact the database performance. Oracle privileged users able to change the historical data.

Redo Log Mining:

Extract the redo log on readable format, create and stores this data using third party tool /Oracle log miner is very difficult and managing the historical data is also very tough.

Flashback Data Archive or Oracle Total Recall:

Flashback data archive provides the complete solution for managing the historical data.

Data Retention: FDA can be enabled on all tables and there is no limit to keep the data’s and retains the data based on business requirement.  These data’s are store in tablespace.  When a record exceeds the retention period, it is automatically purged the retention records from all FDA tables.

Easy to Implement: We can enable the Flashback Data Archive for one or more tables at any time and there is no need any application or database outage.

Easy to Access: We can access the historical data using FLASHBACK SQL query at any point of time and specify the specific interval also. 

Storage Maintenance: FDA automatically compresses and partitions the FDA internal history tables to optimize the storage and performance.

Centralized Management: FDA provides a centralized and policy based process is help to easily group tables and set a common retention policy for all group.

Security: FDA internal history tables are read only tables. Oracle privileged users also don’t allowed to do DML operations against the FDA history tables.

Flashback Data Archive Architecture:

If FDA enabled on table, all transaction on the table and respective undo records are marked as archived. The FBDA new background processes sleeps and wakes up at self tuned intervals (default is 5 minutes) and processes the undo data marked for archival.

If FBDA background process and slaves are too busy, archiving may be performed inline, which significantly affects the users response time. 


FDA Supports

Oracle 11g Release 1 supports add column DDL operations.

Oracle 11g Release 2 supports below DDL operations.

1.     Adds, Drops, Renames and Modifies a column
2.     Adds, Drops, Renames a constraint
3.     Drops & Truncates a Partition & Sub partitions
4.     Rename, Truncate tables
5.     Performs a Partition & Sub partition operations

Unsupported DDL operations

1.     Alter table statement that moves or exchanges a Partition or Subpartition.
2.     Drop table statement

Flashback Data Archive Requirements:
  1. FDA tablespace must be ASSM.
  2. Undo tablespace must be Auto. 

Whether Oracle Total Recall Impact the Database Performance?

FBDA background processes can spawn multiple parallel thread process while DML statement ran against the table and bulk archiving of small transactions. As of now no one report, FDA caused any performance impact on database.

Test Case:

Reduced the undo retention from 900 seconds to 300 seconds

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1

SQL> alter system set undo_retention= 300
  2  ;

System altered.

SQL> show parameter undo_rete

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_retention                       integer     300

Create a Separate tablespace for FDA

SQL> create tablespace fda_totalrecall
  2  datafile 'C:\APP\RAGA\ORADATA\TROY\fda_totalrecall01.dbf' size 500m;

Tablespace created.

Create Flashback archive and retention period is 2 years

SQL> create flashback archive fda_troy
  2  tablespace fda_totalrecall
  3  retention 2 year;

Flashback archive created.

SQL> select owner_name,flashback_archive_name,retention_in_days,create_time,last_purge_time from dba_flashback_archive;

----------- ------------------------- ----------------- -------------------------------- --------------------------------
SYS         FDA_TROY                                730 04-FEB-13 AM  04-FEB-13 AM

Create test schema troy

SQL> create user troy
  2  identified by troy
  3  default tablespace USERS
  4  quota unlimited on USERS
  5  temporary tablespace temp;

User created.

SQL> grant connect,resource,dba to troy;

Grant succeeded.

Create test table under troy schema

SQL> conn troy
Enter password:

SQL> create table test as select * from dba_objects;

Table created.

Alter the test table in FDA

SQL> alter table test flashback archive FDA_TROY;

Table altered.

SQL> select * from dba_flashback_archive_tables;

TABLE_NAME                     OWNER_NAME  FLASHBACK_ARCHIVE_NAME    ARCHIVE_TABLE_NAME                            STATUS
------------------------------ ----------- ------------------------- ----------------------------------------------------- --------
TEST                           TROY        FDA_TROY                  SYS_FBA_HIST_73104                            ENABLED

SQL> select object_id,owner,object_name,object_type from dba_objects where  object_name='TEST';

---------- -------------------- --------------- -------------------
     73104 TROY                 TEST            TABLE

Version Query against undo tablespace

SQL> SELECT object_id,owner,object_name,object_type
  3  AS OF TIMESTAMP TO_TIMESTAMP('04022013 18:30:21','ddmmyyyy hh24:mi:ss')
  4  WHERE object_id=73104;
ERROR at line 2:
ORA-08180: no snapshot found based on specified time

SQL>  SELECT object_id,owner,object_name,object_type
  2    FROM TEST
  4    TO_TIMESTAMP('04022013 18:30:21','ddmmyyyy hh24:mi:ss') AND
  5    TO_TIMESTAMP('04022013 18:35:21','ddmmyyyy hh24:mi:ss')
  6    WHERE object_id=73104;
ERROR at line 2:
ORA-01466: unable to read data - table definition has changed

SQL> select to_char(sysdate,'ddmmyyyy hh24:mi:ss') ddate from dual;

04022013 06:49:21

SQL> delete from test;

71772 rows deleted.

SQL> commit;

Commit complete.

SQL> select count(*) from test;


Retrieve the Data from FDA

SQL> select count(*) from test as of timestamp to_timestamp('04022013 06:49:21','ddmmyyyy hh24:mi:ss');


SQL> select object_id,owner,object_name,object_type from test as of timestamp to_timestamp('04022013 06:49:21','ddmmyyyy hh24:mi:ss') where object_id=73104;

---------- -------------------- --------------- -------------------
     73104 TROY                 TEST            TABLE


Flashback Archive Administer – New system privilege managing the FDA
Flashback Archive – New object Privilege for enabling flashback data archive.

Reference: Oracle Documentation and Oracle Total Recall white Paper

I Hope this article helped to you. I am expecting your suggestions/feedback.