How to fast recover data lost due to user errors (without RMAN)

To a DBA time is money. In cases of downtime …. more time stoped, less money and less time stoped, more money.

Many times we make mistakes for not knowing all the resources available in certain situations that require quick action.

When someone calls you and says that a someone mistakenly deleted data, usually the first thought that comes is that we need to perform the restore of the data.

Here comes an important issue. How to restore data in a short period to cause less impact and less downtime.

Below is the request of a colleague who was in this situation, read:
Version:10g R2
One of our DBA colleagues accidently updated all the records of a table to a particular values without a WHERE clause.I need to revert it back to its previous state (as of 11 am today morning)
Since this is 3 TB schema, we don’t take logical backup for this schema. Flashback feauture is not enabled either.
I have LEVEL0 backup from Monday morning and LEVEL1 backup from Tuesday (today) morning. How can I restore the table to the previous state?

We have many options, but what option you decide to use will make all difference.

The first feature that I will try to use is the FLASHBACK QUERY.  Although this feature exists since version 9i unfortunately for many people is still a novelty.

Let’s test:

$ sqlplus user_test@db11g

SQL*Plus: Release 11.2.0.2.0 Production on Fri Feb 3 14:32:56 2012
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
Enter password:

Connected to:
Oracle Database 11g Release 11.2.0.1.0 - Production
With the Automatic Storage Management option

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO

### CREATING TABLE############
SQL>
CREATE TABLE USER_ERROR AS SELECT * FROM DBA_OBJECTS;
Table created.
##############################

### Populating Data ##########
INSERT INTO USER_ERROR SELECT * FROM USER_ERROR;
COMMIT;
76416 rows inserted
commited

INSERT INTO USER_ERROR SELECT * FROM USER_ERROR;
COMMIT;
152832 rows inserted
commited

INSERT INTO USER_ERROR SELECT * FROM USER_ERROR;
COMMIT;
305664 rows inserted
commited

INSERT INTO USER_ERROR SELECT * FROM USER_ERROR;
COMMIT;
611328 rows inserted
commited
##############################

### Checking Data
SELECT COUNT(1) FROM USER_ERROR;

COUNT(1)
----------------------
1222656

SQL> select count(object_type) qtd,object_type
from user_error
group by object_type
order by 1;

       QTD OBJECT_TYPE
---------- -------------------
        16 LOB PARTITION
        16 EDITION
        16 MATERIALIZED VIEW
        16 RULE
        32 DESTINATION
        48 JAVA SOURCE
        48 SCHEDULE
        64 SCHEDULER GROUP
       112 CONTEXT
       144 INDEXTYPE
       144 WINDOW
       144 UNDEFINED
       160 RESOURCE PLAN
       160 CLUSTER
       208 EVALUATION CONTEXT
       208 JOB CLASS
       256 DIRECTORY
       304 RULE SET
       304 PROGRAM
       400 CONSUMER GROUP
       448 JOB
       576 QUEUE
       816 XML SCHEMA
       880 OPERATOR
      2928 LIBRARY
      3552 PROCEDURE
      3824 TYPE BODY
      3984 SEQUENCE
      4448 TABLE PARTITION
      4864 INDEX PARTITION
      4880 JAVA DATA
      5392 FUNCTION
     13344 JAVA RESOURCE
     17168 TRIGGER
     17296 LOB
     23680 PACKAGE BODY
     24752 PACKAGE
     44624 TYPE
     58752 TABLE
     84384 INDEX
     86384 VIEW
    366768 JAVA CLASS
    446112 SYNONYM

43 rows selected.

SQL> SELECT CURRENT_TIMESTAMP FROM DUAL;

CURRENT_TIMESTAMP
---------------------------------------------------------------------------
03-FEB-12 02.38.08.300885 PM -02:00

### UPDATE WITHOUT WHERE CLAUSE

SQL> UPDATE USER_ERROR SET OBJECT_TYPE='UNKNOWN';

1222656 rows updated.

SQL> COMMIT;

Commit complete.

SQL> select count(object_type) qtd,object_type
from user_error
group by object_type
order by 1;

       QTD OBJECT_TYPE
---------- -------------------
   1222656 UNKNOWN

###### RECOVERING DATA QUICKLY #####

SQL>  CREATE TABLE USER_ERROR_RECOVERED
      AS
      SELECT * FROM USER_ERROR
      AS OF TIMESTAMP TO_TIMESTAMP('03-02-2012 14:38:08','DD-MM-YYYY HH24:MI:SS');

Table created.

SQL> SELECT COUNT(1) FROM USER_ERROR_RECOVERED;

  COUNT(1)
----------
   1222656

SQL> select count(object_type) qtd,object_type
from user_error_recovered
group by object_type
order by 1;
       QTD OBJECT_TYPE
---------- -------------------
        16 RULE
        16 LOB PARTITION
        16 MATERIALIZED VIEW
        16 EDITION
        32 DESTINATION
        48 JAVA SOURCE
        48 SCHEDULE
        64 SCHEDULER GROUP
       112 CONTEXT
       144 UNDEFINED
       144 WINDOW
       144 INDEXTYPE
       160 CLUSTER
       160 RESOURCE PLAN
       208 JOB CLASS
       208 EVALUATION CONTEXT
       256 DIRECTORY
       304 PROGRAM
       304 RULE SET
       400 CONSUMER GROUP
       448 JOB
       576 QUEUE
       816 XML SCHEMA
       880 OPERATOR
      2928 LIBRARY
      3552 PROCEDURE
      3824 TYPE BODY
      3984 SEQUENCE
      4448 TABLE PARTITION
      4864 INDEX PARTITION
      4880 JAVA DATA
      5392 FUNCTION
     13344 JAVA RESOURCE
     17168 TRIGGER
     17296 LOB
     23680 PACKAGE BODY
     24752 PACKAGE
     44624 TYPE
     58752 TABLE
     84384 INDEX
     86384 VIEW
    366768 JAVA CLASS
    446112 SYNONYM

43 rows selected.

### DROPPING TABLE #######

SQL> DROP TABLE USER_ERROR;

Table dropped.

SQL> SELECT COUNT(1) FROM USER_ERROR;
SELECT COUNT(1) FROM USER_ERROR
                     *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> FLASHBACK TABLE USER_ERROR TO BEFORE DROP;

Flashback complete.

SQL>  SELECT COUNT(1) FROM USER_ERROR;

  COUNT(1)
----------
   1222656

SQL>

In the example above I was able to restore data in a short time, saving work and time.

For this procedure succeeds the data must still be in the UNDO tablespace, then when more quickly you identify the error and try to fix it, Will increase the probability of success.

Oracle used the term “flashback” to cover very different things, I don’t like this because it confuse which feature we can use.

To use Flasback Table or Flasback Query you don’t need the “flashback feature” enabled, because flashback query is based on undo information, unlike flashback database which needs flashback logs + redo logs.

What Oracle Editions I can use feature flasback query?
Flashback Query – Is enabled to use in all Oracle Editions no additional cost.
http://www.oracle.com/us/products/database/product-editions-066501.html

What Oracle Editions I can use feature flasback table?
Flashback Table – Is enabled to use only in Oracle Enterprise Edition. (Works in SE edition)
http://www.oracle.com/us/products/database/product-editions-066501.html

Enjoy…

Google

Advertisements

9 Comments on “How to fast recover data lost due to user errors (without RMAN)”

  1. krish says:

    Hi,

    I like the way you explained, considering the same scenario where I accidentally dropped a table and want to recover the table without using FLASHBACK. But, however I have a RMAN backup of before table drop. Can you please guide me with the steps to recover the dropped table with less downtime.

    Thanks in advance,
    krish

    Like

    • Levi Pereira says:

      Hi Krish,
      As soon as possible I will create post explaining how to explore all possible options to perform data recovery without restoring the database.
      Finally I will explain using restore the database (using RMAN) the fastest ways to complete specific types of restore.

      Regards,
      Levi Pereira

      Like

  2. Mr.Levi, i like your way of explanation. Simply excellent !!

    Like

  3. Hi
    I also like the way you explained. one thing Flashback Table needs recyclebin on . In a big system its a problem.

    Like

    • Levi Pereira says:

      Hi Muhammad,
      You’re right, This post was created to try help small issue wich usually occur in small occasional as drop small tables or errors in data manipulation in a table. In case of drop/user_error in big tables this post does not apply.

      Thank you,
      Levi Pereira

      Like

  4. Rafael Poch says:

    Fala Levi, tudo bom ?
    Como sempre otimos artigos com passo a passo, abordando todos os detalhes, parabens !
    A minha consideração é a seguinte:

    Porque no passo da linha 209 voce utilizou o FLASHBACK TABLE USER_ERROR TO BEFORE DROP ? Não bastava renomear a tabela user_error_recovered” para user_error já que você tinha criado com os dados “consistentes” ?

    CREATE TABLE USER_ERROR_RECOVERED
    AS
    SELECT * FROM USER_ERROR
    AS OF TIMESTAMP TO_TIMESTAMP(’03-02-2012 14:38:08′,’DD-MM-YYYY HH24:MI:SS’);

    Fazendo o flashback table não voltariamos com os dados após o update sem o WHERE ?

    Um grande abraco.

    Like

    • Levi Pereira says:

      Olá Rafael,
      Desculpe pela demora em responder. Obrigado pelos elogios.
      No ponto que você observou, seria mais simples sim.
      Porém eu realizei dois tipos diferentes de teste:
      1 – Recuperar a tabela em um ponto no tempo
      2 – Recuperar a tabela depois de ser excluída, sem copia da mesma

      Portanto o teste 2 é independente do 1, estava partindo do ponto que a tabela foi apenas removida e não tínhamos copia da mesma.

      Foi um erro meu colocar tudo na mesma seção, dando uma interpretação errada dos testes.

      Eu quis mostrar apenas as possibilidades de recuperar uma tabela sem usar o RMAN.

      Abraços,
      Levi Pereira

      Like

  5. […] How to fast recover data lost due to user errors (without … – Feb 03, 2012 · How to fast recover data lost due to user errors (without RMAN) Posted: 03/02/2012 | Author: Levi Pereira | Filed under: 10g R1, 10g R2, 11g R1, 11g R2 …… […]

    Like


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