Oracle Tips and Tricks — David Fitzjarrell

January 9, 2008

Resurrecting The Dead (or Gee, I Thought That Data Was Gone Forever)

Filed under: flashback — dfitzjarrell @ 16:29

Occasionally an end-user does the unthinkable, and changes data permanently (or so it appears) without checking the results before issuing a commit. Ooops. It used to be that to restore that data one had basically two choices:

A) Restore the data from a known good copy made before the changes were enacted
B) Restore the table from the last known good export

Yes, there was a third choice, an incomplete tablespace recovery but that presumed the database to be in ARCHIVELOG mode and, gasp, there are databases running in NOARCHIVELOG mode where this wouldn’t work. So, what is a DBA to do? If you’re running 9i you can enable flashback query by using the dbms_flashback package, which was nice but rather cumbersome in implementation, as it only allowed you to query the flashback data while enabled so updates to existing tables (to restore known good data) were somewhat convoluted in execution; it was necesary to enable flashback mode, open a cursor, then disable flashback mode to effect the updates:

declare
  --
  -- Cursor to fetch the required data
  --
  cursor c1 is
  select flerg, snerm, anguplast
  from snangpiester;

  --
  -- A place to stash each row of the returned data
  --
  c1_rec c1%rowtype;

begin

  --
  -- We flashback the data image to before the problem began
  --
  dbms_flashback.enable_at_time(to_timestamp('16-SEP-2007 06:53:00 AM','DD-MON-RRRR HH:MI:SS AM'));

  --
  -- Open the cursor while in flashback mode
  --
  open c1;

  --
  -- Disable flashback mode so we can update the problem table
  --
  dbms_flashback.disable;

  --
  -- Go fetch the good data and update the problem table
  --
  loop
          fetch c1 into c1_rec;
          exit when c1%notfound;  -- say goodbye when there's no more data
          update snangpiester
          set anguplast = c1_rec.anguplast
          where flerg = c1_rec.flerg
          and snerm = c1_rec.snerm;
  end loop;

  --
  -- Commit the changes
  --

  commit;

end;
/

Enter Oracle 10g and later releases where flashback query is enabled and does not require the use of any additional packages. Flashback query can use the SCN or a timestamp value as a reference point, making the use of such queries much easier. Let’s see how using flashback query can resurrect data thought to be lost by committed user changes.

Flashback query syntax is as follows:

select [select list here]
from [table]
as of timestamp to_timestamp('date/time or timestamp string');

Note the highlighted text; the ‘as of’ syntax [described fully at http://docs.oracle.com] tells Oracle to use flashback mode to rebuild the data image as of the provided date and time utilising data found in the UNDO segments preserved by the undo_retention parameter setting. To illustrate the power of using flashback query let’s look at an example using the EMP table.

Koffi Cupps, assistant HR manager and part-time ankle model, wanted to increase the salaries of the sales team by 17 percent and add $85 to each salesman’s commission; unfortunately she updated the entire employee table without first making a copy of the original data and committed the changes before checking the results:

SQL> update emp
   2 set sal = sal*1.17, comm = nvl(comm, 0) + 85;

14 rows updated.

SQL> commit;

Commit complete.

SQL> select 
   2 from emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        936         85         20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1872        385         30
      7521 WARD       SALESMAN        7698 22-FEB-81     1462.5        585         30
      7566 JONES      MANAGER         7839 02-APR-81    3480.75         85         20
      7654 MARTIN     SALESMAN        7698 28-SEP-81     1462.5       1485         30
      7698 BLAKE      MANAGER         7839 01-MAY-81     3334.5         85         30
      7782 CLARK      MANAGER         7839 09-JUN-81     2866.5         85         10
      7788 SCOTT      ANALYST         7566 09-DEC-82       3510         85         20
      7839 KING       PRESIDENT            17-NOV-81       5850         85         10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1755         85         30
      7876 ADAMS      CLERK           7788 12-JAN-83       1287         85         20
      7900 JAMES      CLERK           7698 03-DEC-81     1111.5         85         30
      7902 FORD       ANALYST         7566 03-DEC-81       3510         85         20
      7934 MILLER     CLERK           7782 23-JAN-82       1521         85         10

14 rows selected.

SQL>

Fortunately her younger sister, Dixie, is the Oracle DBA for the company. Knowing the HR software was using Oracle 11.2.0.4 Dixie sprang into action to restore the data prior to the change (it was also a good thing this was early on a Monday morning and that the undo_retention was set to a sufficient size else Dixie could have received an ORA-01555 because the necessary undo blocks had been overwritten):

SQL> select *
   2  from emp as of timestamp to_timestamp(trunc(sysdate));

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20 
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30 
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30 
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20 
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30 
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30 
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10 
      7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20 
      7839 KING       PRESIDENT            17-NOV-81       5000                    10 
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30 
      7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30 
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20 
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

All Dixie needed to do was delete the incorrect data and insert the old values:

SQL> delete from emp;

14 rows deleted.

SQL> insert into emp
  2  select * from emp as of timestamp to_timestamp(trunc(sysdate));

14 rows created.

SQL> commit;

Commit complete.

Of course she also could have updated the ‘bad’ data using the flashback image:

SQL> update emp e
2 set (sal, comm) = (select sal, comm 
3 from emp 
4 as of timestamp to_timestamp(trunc(sysdate)) 
5 where empno = e.empno);

14 rows updated.

SQL> commit;

Commit complete.

SQL> select *
2 from emp;


     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20 
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30 
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30 
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20 
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30 
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30 
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10 
      7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20 
      7839 KING       PRESIDENT            17-NOV-81       5000                    10 
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30 
      7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30 
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20 
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

SQL>

As you can see the original state of the data has been restored, Dixie Cupps is a hero and Koffi Cupps can try her update again, being careful to not commit the changes until she checks that they are correct. Oh, and after making a copy of the table BEFORE she issues any update statements.

If you’re planning on using flashback query on a regular basis and you’re running Oracle version 10gR2 or later it might be a wise idea to alter the UNDO tablespace to provide a guaranteed undo_retention window:

SQL> alter tablespace undotbs1 retention guarantee;

Tablespace altered.

SQL>

The tradeoff for this is that multiple DML operations may fail due to a lack of writable UNDO space; Oracle will fail transactions if there is insufficient UNDO space to honor both the undo_retention and the current transactional load since guaranteeing the undo retention will cause Oracle to keep unexpired undo data for the duration of the undo_retention period, whereas not guaranteeing the retention will allow Oracle to overwrite unexpired undo data should the need arise. And while Oracle can overwrite unexpired undo data it will do its level best to maintain the configured undo_retention even without the retention guarantee. Testing this on a non-production database, with a comparable workload, would be a very good idea. Such actions can easily be undone:

SQL> alter tablespace undotbs1 retention noguarantee;

Tablespace altered.

SQL>

in the event a problem such as multiple DML operations failing rears its ugly head.

Flashback query can be a very useful tool to the DBA in such situations, and also for some reporting tasks as data can be queried as of a given timestamp (again, as long as the undo_retention setting provides the necessary window). Let’s see how a salary report can be generated after salaries for some employees have been adjusted:

SQL> --
SQL> -- Get reference timestamp before changes
SQL> --
SQL> select to_timestamp(to_char(sysdate, 'DD-MON-YYYY HH:MI:SS AM')) curr_timestamp from dual;

CURR_TIMESTAMP
---------------------------------------------------------------------------
09-JAN-08 11.54.40.000000000 AM

SQL>
SQL> --
SQL> -- Change the data in EMP
SQL> --
SQL> update emp
  2  set sal = sal+120, comm = nvl(comm, 0) + 85
  3  where job = 'SALESMAN';

4 rows updated.

SQL>
SQL> --
SQL> -- Commit and make the changes "permanent"
SQL> --
SQL> commit;

Commit complete.

SQL>
SQL> --
SQL> -- Show the current state of the data
SQL> --
SQL> select *
  2  from emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- 
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20 
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1720        385         30 
      7521 WARD       SALESMAN        7698 22-FEB-81       1370        585         30 
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20 
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1370       1485         30 
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30 
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10 
      7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20 
      7839 KING       PRESIDENT            17-NOV-81       5000                    10 
      7844 TURNER     SALESMAN        7698 08-SEP-81       1620         85         30 
      7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30 
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20 
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

SQL>
SQL>
SQL> --
SQL> -- Generate a report of employee number,
SQL> -- employee name, job, salary, percent increase,
SQL> -- commission, increase
SQL> --
SQL> with get_old as(
  2       select empno, ename, job, sal, nvl(comm,0) comm
  3       from emp
  4       as of timestamp to_timestamp('&curr_ts')
  5  ),
  6  get_curr as (
  7       select empno, ename, job, sal, comm
  8       from emp
  9  )
 10  select c.empno, c.ename, c.job, c.sal,
 11  (c.sal - o.sal)/o.sal*100 sal_incr,
 12  c.comm,
 13  (c.comm - o.comm) comm_incr
 14  from get_old o, get_curr c
 15  where c.empno = o.empno;

     EMPNO ENAME      JOB              SAL   SAL_INCR       COMM  COMM_INCR
---------- ---------- --------- ---------- ---------- ---------- ---------- 
      7369 SMITH      CLERK            800                     0 
      7499 ALLEN      SALESMAN        1720        7.5        385         85 
      7521 WARD       SALESMAN        1370        9.6        585         85 
      7566 JONES      MANAGER         2975                     0 
      7654 MARTIN     SALESMAN        1370        9.6       1485         85 
      7698 BLAKE      MANAGER         2850                     0 
      7782 CLARK      MANAGER         2450                     0 
      7788 SCOTT      ANALYST         3000                     0 
      7839 KING       PRESIDENT       5000                     0 
      7844 TURNER     SALESMAN        1620          8         85         85 
      7876 ADAMS      CLERK           1100                     0
      7900 JAMES      CLERK            950                     0 
      7902 FORD       ANALYST         3000                     0 
      7934 MILLER     CLERK           1300                     0

14 rows selected.

SQL>

Flashback query, in 10g and later releases, is one of the nicer enhancements to the database. How did we ever live without it? Oh, I guess by having copies of tables lying around that no one keeps track of …

Advertisements

2 Comments »

  1. […] Resurrecting The Dead flashback operations were discussed as an option to restore table data to before an errant update. […]

    Pingback by That Depends | Oracle Tips and Tricks -- David Fitzjarrell — May 20, 2015 @ 11:00 | Reply

  2. […] Resurrecting The Dead flashback operations were discussed as an option to restore table data to before an errant update. […]

    Pingback by That Depends - Oracle - Oracle - Toad World — May 20, 2015 @ 11:15 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: