Oracle Flashback Technology (II) - Flashback Transactions

Oracle Flashback technology
Logical Flashback (do not depend on RMAN, rely on undo data)
Physical flashback
New on Oracle 11g:

Oracle Flashback Transaction
  • Allow you to rollback changes made by one or more transactions.
  • Flashback Transaction generates and executes the DMLs necessary to undo a given transactions and (optionally) all its dependent transactions.
  • Requisites:
    • ARCHIVELOG mode
    • At least one archive log already generated
    • Minimal and PK (plus FK) supplemental logging enabled
    Privileges required:
    • FLASHBACK and SELECT on the object or

Configuring the database to use Flashback Transaction

(1) Check the database log mode

SQL> Select log_mode from v$database;


(2) Change Log mode to archivelog:


SQL> Select log_mode from v$database;


(3) Switch logfile


(4) Check log files

SQL> select group#, thread#, bytes/1024/1024 Mb, members, archived, status
     from v$log;

GROUP#                 THREAD#                MB                     MEMBERS                ARCHIVED STATUS           
---------------------- ---------------------- ---------------------- ---------------------- -------- ---------------- 
1                      1                      50                     1                      YES      ACTIVE           
2                      1                      50                     1                      NO       CURRENT          
3                      1                      50                     1                      YES      INACTIVE       

SQL> select name, archived, applied, status, completion_time, compressed
     from v$archived_log;

NAME                                                      ARCHIVED APPLIED   STATUS COMPLETION_TIME           COMPRESSED 
--------------------------------------------------------- -------- --------- ------ ------------------------- ---------- 
/u01/.../flash_recovery_area/SID/archivelog/date/name.arc YES      NO        A      08-DEC-10                 NO         

(5) Enable Minimal and PK (and FK) supplemental logging 


(3) Check the current setting for supplemental logging

SQL> Select supplemental_log_data_min, supplemental_log_data_all,
            supplemental_log_data_UI,  supplemental_log_data_FK
     from v$database;

------------------------- ------------------------- ------------------------ ------------------------ 
YES                       NO                        NO                       YES                     

Using Flashback Transaction
  • Use DBMS_FLASHBACK.TRANSACTION_BACKOUT procedure to rollback a transaction and its dependent transactions.
  • Can be done with the database online.
  • DDL operations cannot be flashed back
  • Transactions that use LOB datatypes (BFILE, BLOB, CLOB, NCLOB) cannot be flashed back
  • Transactions cannot use features or datatypes not supported by the Log Miner
  • Ensure that undo segments are large enough to maintain undo information for the period you may need to flashback.

Example 1: Flashback transactions that have no dependent transactions.
  1. Drop and create tables dept and emp
  2. Transaction 1 and Transaction 2 insert into dept and emp
  3. To flashback Transaction 2 you need:
    • Find out the transaction ID (XID): you can use OEM and Log Miner, or use a flashback transaction query.
    • Identify the transaction to be flashed back and use dbms_flashback.transaction_backout

(1) Drop and recreate dept and emp tables.
SQL> drop table emp;
SQL> drop table dept;

-- Check the current database SCN
SQL> select current_scn scn_before_tblcreation 
     from v$database;


SQL> create table dept
    (deptno number primary key,
     deptname varchar2(20));
SQL> create table emp
     (empno number primary key, 
      empname varchar2(10),
      deptno number, 
      salary number,
      constraint fk_dept foreign key(deptno)
                         references dept(deptno));

-- Check the current database SCN
SQL> select current_scn scn_before_transactions 
     from v$database;

(2) Insert data into emp and dept
-- Transaction 1: Insert new row (id=1)
insert into dept values (1, 'accounting');
insert into dept values (2, 'sales');
insert into emp values (1, 'John', 1, 2000);

-- Transaction 2: Insert new rows (ids=2,3)
insert into emp values (2, 'Mary', 1, 2500);
insert into emp values (3, 'Jack', 2, 2000);
insert into dept values (3, 'IT');

-- Check the current database SCN
SQL> select current_scn scn_after_transactions 
     from v$database;


-- Check inserted data
SQL> select * from dept;

DEPTNO                 DEPTNAME             
---------------------- -------------------- 
1                      accounting           
2                      sales                
3                      IT                   

SQL> select * from emp;

EMPNO                  EMPNAME    DEPTNO                 SALARY                 
---------------------- ---------- ---------------------- ---------------------- 
1                      John       1                      2000                   
2                      Mary       1                      2500                   
3                      Jack       2                      2000  
(3) Now use the flashback transaction query to find information about all transactions in a time or SCN interval
SQL> select versions_xid xid, versions_startscn start_scn,
            versions_endscn end_scn, versions_operation operation,
            empno, empname, deptno, salary
     from emp
        versions between scn 1473743 and 1473781;

---------------- ----------- -------- --------- ------- ---------- -------- -------- 
05000D0099040000 1473777              I         3       Jack       2         2000   
05000D0099040000 1473777              I         2       Mary       1         2500   -- Transaction 2
07000A006A030000 1473747              I         1       John       1         2000   

Identify the transaction you need to flashback. For example, if you choose to flashback transaction 2 only its three insert statements will be flashed back. Also, transaction 2 has no dependents, since:
  • No other subsequent transaction modified any data touched by transaction 2
  • No other subsequent transaction modified data linked through referential integrity with the rows touched by transaction 2.

To flashback a transaction, use DBMS_FLASHBACK.TRANSACTION_BACKOUT (numtxns, xids, options).
The procedure accepts one or more transaction IDs (XIDs).
It has also an OPTIONS parameter to indicate how dependent transactions should be treated. The possible values are:
  • NOCASCADE - The transaction should have no dependencies. If a dependency is found, this raises an error.
  • NOCASCADE_FORCE - The transaction is backed out without affecting dependent transactions. This option succeeds only if no constraints are violated in the process.
  • NONCONFLICT_ONLY - This option backout only the changes to the non-conflicting rows of the given transactions.
  • CASCADE - The transaction and all it's dependencies will be removed.
SQL> conn / as sysdba;

SQL> declare
 v_xid sys.xid_array;
 v_xid := sys.xid_array('05000D0099040000');
 dbms_flashback.transaction_backout( numtxns => 1,
                                     xids    => v_xid,
                                     options => dbms_flashback.cascade);
anonymous block completed
  • TRANSACTION_BACKOUT analyzes the transactional dependencies, performs DML operations, and generates reports.
  • It also DOES NOT commit the actions performed.
  • To make the transaction backout permanent, you need to explicitly commit the transaction.
(4) Check the effects of the flashback. Query the table contents. Commit the flashback transaction.
SQL> select * from dept;

DEPTNO                 DEPTNAME             
---------------------- -------------------- 
1                      accounting           
2                      sales                

SQL> select * from emp;

EMPNO                  EMPNAME    DEPTNO                 SALARY                 
---------------------- ---------- ---------------------- ---------------------- 
1                      John       1                      2000           

SQL> commit;

The backout procedure populates two static data dictionary views.
  • Shows information about the transactions backedout by the procedure.
    • COMPENSATING_XID - Transaction of ID of the compensating transaction. You will need to explictly commit this transaction in order to make the backout permanent.
    • XID - Backedout transaction
    • DEPENDENT_XID - Dependent transaction

SQL> select *
from dba_flashback_txn_state
where xid = '05000D0099040000';

COMPENSATING_XID XID              DEPENDENT_XID    BACKOUT_MODE     USERNAME                       
---------------- ---------------- ---------------- ---------------- ------------------------------ 
0800160056050000 05000D0099040000                  CASCADE          SYSTEM                

(b) Use the compensating_xid returned to query the DBA_FLASHBACK_TXN_REPORT view:

sql> select xid_report
     from dba_flashback_txn_report
     where compensating_xid = '0800160056050000';

<?xml version="1.0" encoding="ISO-8859-1"?> 
<COMP_XID_REPORT XID="0800160056050000" NAME="_SYS_COMP_TXN_1114255_TIM_1319522919"> 
 <TRANSACTION XID="05000D0099040000"> 
   delete from "SYSTEM"."DEPT" where "DEPTNO" = '3' and "DEPTNAME" = 'IT' 
  <USQL exec="yes">
   delete from "SYSTEM"."EMP" where "EMPNO" = '3' and "EMPNAME" = 'Jack' and "DEPTNO" = '2' and "SALARY" = '2000'
  <USQL exec="yes"> 
   delete from "SYSTEM"."EMP" where "EMPNO" = '2' and "EMPNAME" = 'Mary' and "DEPTNO" = '1' and "SALARY" = '2500' 
<EXEC_USQL>delete from "SYSTEM"."DEPT" where "DEPTNO" = '3' and "DEPTNAME" = 'IT' 
<EXEC_USQL>delete from "SYSTEM"."EMP" where "EMPNO" = '3' and "EMPNAME" = 'Jack' and "DEPTNO" = '2' and "SALARY" = '2000' 
<EXEC_USQL>delete from "SYSTEM"."EMP" where "EMPNO" = '2' and "EMPNAME" = 'Mary' and "DEPTNO" = '1' and "SALARY" = '2500'

Structured Storage approaches

(Extracts from a good discussion posted by James Hamilton in his blog. You can check the original article and comments here.)

Although a couple of years old, James Hamilton provided a good requisite-based breakdown of data storage systems. These are some of his points:
  • The world of structured storage extends far beyond relational (Oracle, DB2, SQL Server, MySQL, NoSQL, etc) systems.
  • Many applications do not need the rich programming model of relational systems and some are better seviced by lighter-weight, easier-to-administers, and easier-to-scale solutions.

  • Structured storage approaches can be classified based on customer major requirements.
  • These are Feature-first, scale-first, simple structured storage and purpose-optimized stores.

(1) Feature-First
  • Traditional Relational database management systems (RDBMS) are the structured storage system of choice here.
  • Driven by requirements for Enterprise financial systems, human resource systems, customer relationship management systems (FIN, HR, CRMs)
    • Examples here include Oracle, MySQL, SQL Server, PostgreSQL, Sybase, DB2.
  • Cloud solutions here include:

(2) Scale-First
  • This is the domain of very high scale website (i.e. facebook, Gmail, Amazon, Yahoo, etc)
  • Scaling capabilities are more important than more features and none could run on a single rdbms.
  • The problem here is that the full relational database model (including joins, aggregations, use of stored procedures) is difficult to scale (especially in distributed contexts).
  • Distributing data across tens to thousands of rdbms instances and still maintain support for the distributed data as if it were under a single rdbms engine is difficult.
  • As an alternative, very high scale may be supported with the use of key-value store solutions. These include HBase, Amazon SimpleDB (cloud-based), Project Valdemort, Cassandra, Hypertable, etc

(3) Simple Structure storage
  • Applications that have a structure storage requirement but do not need features, cost and complexity of RDBMSs neither have very high scalability requirements.
  • Some implementations include:
  • Facebook: email inbox search (Cassandra)
  • Amazon: retail shopping card (Dynamo)
  • Berkeey DB

(4) Purpose-Optimized stores
  • Mike Stonebraker argued that the existing commercial RDBMS offerings do not meet the needs of many important market segments
  • Some special purpose real-time, stream processing solutions (StreamBase, Vertica, VoltDB) have beat the RDBMS benchmart by +30x...

Mike Stonebraker, One Size fits all

Oracle Database Security

source: xkcd

Oracle default security features
User accounts
  • naming convention, password profiles, password policies
  • Unlike SQL Server (2005/2008), there is no clear separation between database user and database schema
Authentication methods
  • database level, OS level, network level
Privileges and roles
  • restrict data access
Application security
  • User session information using Application CONTEXT
  • Application Context: name-value pair that holds session info.
  • You can retrieve info about a user (i.e., username/terminal, username/deptid) and restrict database and application access based on this information.
Virtual Private Database: restrict database access on the row and column levels.
  • VPD policy: dynamically imbeds a WHERE clause into SQL statements
Audit database

Additional database security resources
  1. Oracle Advanced Security
    • TDE, Wallet Mgmt, Network Encryption, RADIUS, Kerberos, Secure Sockets Layer authentication
  2. Oracle Label Security
    • Secures database tables at the row level
  3. Oracle Database Vault
    • Provides fine-grained access control to sensitive data
  4. Oracle Audit Vault
    • Collect data from audit trail tables, OS audit files and redo logs.
  5. Oracle Enterprise User Security
  6. Oracle Total Recall
  7. Oracle Database Firewall
  8. Oracle Data Masking

Managing Oracle Security
Database Security involves Authentication, Authorization, Data Access, and Audit

(1) Authentication Methods
  • Database Authentication (Default)
    • Oracle implements authentication through a user ID and password.
  • Operating System authentication
    • Sometimes referred as OPS$ accounts.
    • Oracle looks for a user ID that matches your OS login ID and prepends an OPS$ to it.
    • Additional benefit: OS scripts can log into the database without hard coding user names and passwords.
  • Third Party Authentication
    • You can tie authentication to third-party providers like Kerberos or DCE (called network authentication) (Needs EE+Security Pack)
    • or provide it from the middle tier (called multitier authentication).

(2) Authorization
  • Giving access to certain objects, processes or resources.
  • Implemented using GRANTS on objects to ROLES or USERS.
  • Oracle implements authorization via users, roles and permissions

(3) Securing Data Access
  • The ability to access specific data values associated with an object (across records or columns)
  • STORED PROCEDURES are commonly used to secure data access.
  • Benefits of using stored procedures for all programmatic data access: (a) secure the data; (b) provide consistent application access to the data; (c) Hide the data structure.
  • Secure VIEWS can also be used to restrict access. (use WITH CHECK OPTION Constraint)
  • Controlling access at the record level:
    • Previous versions (before Oracle 10g): using secure views
    • More recently:
  • (a) Use Virtual Private Databases (VPDs)
  • (b) Use Fine-Grained Access Control (FGAC) - uses DBMS_RLS package (Row Level Security)
  • With FGAC (DBMS_RLS), you can transparently include a WHERE clause on any combinations of DML and SELECT when a user queries the database.

(a) Option: use Secure Views
userA owns EMPLOYEES table.
userB needs access to EMPLOYEES but cant see SALARY or MANAGER records.
userA may create a view and grant access to userB on that view.

-- User A:
 SQL> create or replace view emp_no_sal as 
      select employee_id, last_name, first_name, email, job_id, manager_id
      from iranmr.employees a
      where employee_id NOT IN (
           select nvl(manager_id, -1)
           from iranmr.employees b);

 SQL> grant select on emp_no_sal to userB;

(b) Option: Use Virtual Private Database (VPD)
  • With Virtual Private Databases (VPDs), Oracle allows column masking to hide columns.
  • When you select the row, Oracle will only display NULL for the secure columns.
  • If you're securing at the row level and column level, it's probably easier to just implement VPDs and not the secure views.

What's a Virtual Private Databases (VPDs)?
  • A VPD is just asking Oracle to put a where clause on DML against an object with a security policy on it.
  • A security policy is defined with DBMS_RLS package.
  • A security policy is normally defined in a CONTEXT (a piece of data that says how the where clause should be built).

(4) Audit
  • Older versions: audit implemented through triggers.
  • Trigger limitations: (a) triggers can easily be disabled or modified; (b) triggers do not fire on SELECT; (c) require extensive coding and testing.
  • Newer versions: Use Fine Grained Auditing (FGA). (DBMS_FGA package)