DBA Hub

📋Steps in this guide1/9

How to Recover Loss of DATA (Without a Backup!) usingOracle Flash Back Query

How to Recover Loss of DATA (Without a Backup!) using Oracle Flash Back Query  IT WILL WORK ONLY FOR DML DATA LOSS Issue Description: No Flashback Enable No Recyclebin No Restore Point No Backup Developer accidentally deleted all rows from table and then committed too. He want DBA to recover Loss of DATA Solution: It depends … Continue reading How to Recover Loss of DATA – (Without a Backup!) →

oracle backupintermediate
by OracleDba
11 views
1

Overview

How to Recover Loss of DATA (Without a Backup!) using Oracle Flash Back Query IT WILL WORK ONLY FOR DML DATA LOSS Issue Description:
2

Section 2

No Flashback Enable No Recyclebin No Restore Point No Backup No Flashback Enable No Recyclebin No Restore Point No Backup Developer accidentally deleted all rows from table and then committed too. He want DBA to recover Loss of DATA
3

Section 3

Developer accidentally deleted all rows from table and then committed too. He want DBA to recover Loss of DATA Solution: It depends upon on how much undo retention time you have specified. If you have set the UNDO_RETENTION parameter to 1 hour, then users can recover from their mistakes made since last 1 hour only. Lets do Practical……………..
4

Section 4

1. No Flashback <----------------------------- 2. No Recyclebin

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SQL>
select name,open_mode from v$database;
NAME      OPEN_MODE
--------- --------------------
DELL      READ WRITE

SQL>

SQL>
select flashback_on from v$database;
FLASHBACK_ON
------------------
NO
<-----------------------------
SQL>
5

Section 5

OFF <------ 3. No Restore Point no rows selected <------

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
SQL>
show parameter recyclebin
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
recyclebin                           string
OFF <------
SQL>

SQL>
select * from v$restore_point;
no rows selected <------
SQL>
6

Section 6

4. Archive Log On ARCHIVELOG <------ 5. Simulation

Code/Command (click line numbers to comment):

1
2
3
4
5
6
SQL>
select log_mode from v$database;
LOG_MODE
------------
ARCHIVELOG <------
SQL>
7

Section 7

Commit complete. <-------- 20-SEP-2018 08:39:43 <----------------- at this time Data available delete from sugi; <------- no rows selected <----- Action Plan:

Code/Command (click line numbers to comment):

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
SQL>
conn test/test;
Connected.
SQL>
SQL>
CREATE TABLE SUGI (NAME    VARCHAR2(20),ROLE VARCHAR2(20));
Table created.

SQL>
insert into sugi values ('SUGI','DBA');
1 row created.

SQL>
insert into sugi values ('TEJU','DBA');
1 row created.

SQL>
insert into sugi values ('RAJ','DBA');
1 row created.

SQL>
COMMIT;
Commit complete. <--------
SQL>
select * from sugi;
NAME                 ROLE
-------------------- --------------------
SUGI                 DBA
TEJU                 DBA
RAJ                  DBA

SQL>
SQL>
CONN / AS SYSDBA
Connected.
SQL>
ALTER SYSTEM FLUSH BUFFER_CACHE;
System altered.

SQL>
ALTER SYSTEM SWITCH LOGFILE;
System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL>

SQL>
conn test/test;
Connected.
SQL>
select * from sugi;
NAME                 ROLE
-------------------- --------------------
SUGI                 DBA
TEJU                 DBA
RAJ                  DBA

SQL>
select to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') "SYSDATE" from dual;
SYSDATE
--------------------
20-SEP-2018 08:39:43  <----------------- at this time Data available
6. Delete the rows

SQL>
delete from sugi;  <-------
3 rows deleted.
SQL>
commit;  <------
Commit complete.
SQL>
select * from sugi;
no rows selected <-----
SQL>
8

Section 8

Action Plan: * ERROR at line 1: ORA-08189: cannot flashback the table because row movement is not enabled ALTER TABLE TEST.SUGI ENABLE ROW MOVEMENT; FLASHBACK TABLE TEST.SUGI to timestamp TO_TIMESTAMP('20-SEP-2018 08:39:00','DD-MON-YYYY HH24:MI:SS');

Code/Command (click line numbers to comment):

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
SQL>
conn / as sysdba
Connected.

SQL>
FLASHBACK TABLE TEST.SUGI to timestamp TO_TIMESTAMP('20-SEP-2018 08:39:00','DD-MON-YYYY HH24:MI:SS');
FLASHBACK TABLE TEST.SUGI to timestamp TO_TIMESTAMP('20-SEP-2018 08:39:00','DD-MON-YYYY HH24:MI:SS')
*
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled
SQL>
ALTER TABLE TEST.SUGI ENABLE ROW MOVEMENT;
Table altered.

SQL>
FLASHBACK TABLE TEST.SUGI to timestamp TO_TIMESTAMP('20-SEP-2018 08:39:00','DD-MON-YYYY HH24:MI:SS');
Flashback complete.  <----
SQL>
SELECT * FROM TEST.SUGI;
NAME                 ROLE
-------------------- --------------------
SUGI                 DBA
TEJU                 DBA
RAJ                  DBA
SQL>
9

Section 9

SELECT * FROM TEST.SUGI; NAME ROLE -------------------- -------------------- SUGI DBA TEJU DBA RAJ DBA Caution : Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using. Caution : Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Comments (0)

Please to add comments

No comments yet. Be the first to comment!