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://tahiti.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 10.2.0.3 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 ...
0 comments:
Post a Comment