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

Backup and Recovery
Administrator

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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
$ sqlplus [email protected]
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…

Post Views: 398
Administrator

http://www.databaselearners.com

Related Posts

How to recover database if system datafile lost

Administrator

RMAN Offline Backups

Administrator

RMAN – Cold Backup and Restore

Administrator