Skip to main content

Posts

Showing posts from April, 2013

Oracle Total Recall (Flashback Data Archive)

How hard and painful it used to be archiving historical data that required an Application intelligence or a dedicated database to keep history of tables, certainly a daunting task for DBAs and Application Developers. We needed to procure new Hardware or overuse of existing resources, it always used to happen at an additional unacceptable cost for IT Infrastructure and maintenance.

To overcome such issues, Oracle has introduced a feature "Total Recall" with version 11g, called Flashback Data Archive technically.

What is it and How it works?
Flashback Data Archive is an in-built archiving process that is taken care by Oracle background process called FBDA(FlashBack Data Archive). It keeps a track of all transactional changes occur on Flashabck Data Archive enabled tables over a specified period of time.
When a transaction is committed on a tracked table, FBDA snaps the before-image of the rows in the archive. FDBA maintains metadata on the current rows and tracks how much data ha…

Colored SQL

Ever troubled to capture a particular SQL statement which is found to be consuming significantly potential database resources, wanted to keep a record of its trend or behaviour over times upon various tuning attempts.  AWR snapshot does not include all SQLs in its report unless it is identified to be a TOP SQL Statement. In such cases, it never used to be possible to capture a desired SQL Statement to monitor its trend over various tuning attempts. 
To give DBA the ability to capture such desired SQL Statements, Oracle has introduced a procedure called "add_colored_sql()" with the package "dbms_workload_repository" in 11g that inturn marks the SQL as "colored", so that SQL ID which is marked as colored will be captured in every AWR snapshot, It does not need to be a top SQL Statement.

To color the SQL Statement, begin dbms_workload_repository.add_colored_sql( sql_id => 'f93g1utkcbzy1'
);end;
/

To remove the colored SQL Statement from AWR snaps…