OraFAQ Forum: Server Administration » Recovery of deleted table

Home » RDBMS Server » Server Administration » Recovery of deleted table (Oracle8i Enterprise Edition Release 8.1.7.0.0, windows 7)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Recovery of deleted table [message #667249]

Mon, 18 December 2017 03:08

shawaj
Messages: 89
Registered: January 2016

Member

Hi everyone ,

SQL> DELETE FROM T1;

SQL> COMMIT ;

Now, what i can do so that my data can be available.(Oracle 8i)

Report message to a moderator

Re: Recovery of deleted table [message #667250 is a reply to message #667249]

Mon, 18 December 2017 03:13

John Watson
Messages: 8960
Registered: January 2010
Location: Global Village

Senior Member

Not possible. A requirement of a relational database is that a committed transaction cannot be lost.

Report message to a moderator

Re: Recovery of deleted table [message #667251 is a reply to message #667250]

Mon, 18 December 2017 03:24

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Perhaps you might get (some of?) data from backup.

Report message to a moderator

Re: Recovery of deleted table [message #667252 is a reply to message #667250]

Mon, 18 December 2017 03:26

shawaj
Messages: 89
Registered: January 2016

Member

ohhk

Thanks for quick reply..

Report message to a moderator

Re: Recovery of deleted table [message #667253 is a reply to message #667249]

Mon, 18 December 2017 03:58

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Here's a way to recover your table data using flashback query:

SQL> create table t1 as select level val from dual connect by level <= 10;

Table created.

SQL> commit;

Commit complete.

SQL> select * from t1;
       VAL
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

10 rows selected.

SQL> exec dbms_lock.sleep(120)

PL/SQL procedure successfully completed.

SQL> delete from t1;

10 rows deleted.

SQL> commit;

Commit complete.

SQL> select * from t1;

no rows selected

SQL> -- flashback query
SQL> select * from t1 as of timestamp systimestamp-numtodsinterval(1,'MINUTE');
       VAL
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

10 rows selected.

SQL> -- recover table data using this flashback query
SQL> insert into t1
  2  select * from t1 as of timestamp systimestamp-numtodsinterval(1,'MINUTE');

10 rows created.

SQL> commit;

Commit complete.

SQL> select * from t1;
       VAL
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

10 rows selected.

Oooops! Sorry, did not see your version: 8i! so flashback query is not available.

[Updated on: Mon, 18 December 2017 04:00]

Report message to a moderator

Re: Recovery of deleted table [message #667254 is a reply to message #667253]

Mon, 18 December 2017 06:22

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

In 8i you can use Log Miner.
Here's a simplified example:

SQL> @v

Oracle version: 8.1.7.4.1

SQL> create table t1 as
  2  select rownum val from (select 1 from dual group by cube(1,2,3,4)) where rownum<=10;

Table created.

SQL> commit;

Commit complete.

SQL> select * from t1;
       VAL
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

10 rows selected.

SQL> exec dbms_lock.sleep(120)

PL/SQL procedure successfully completed.

SQL> delete from t1;

10 rows deleted.

SQL> commit;

Commit complete.

SQL> select * from t1;

no rows selected

SQL> declare
  2     options pls_integer := sys.dbms_logmnr.skip_corruption;
  3  begin
  4    sys.dbms_logmnr_d.build (
  5      dictionary_filename=>'dictionary.ora',
  6      dictionary_location=>'E:\Oracle\admin\MIK8\Log'
  7    );
  8    sys.dbms_logmnr.add_logfile
  9      (logfilename=>'E:\ORACLE\BASES\MIK8\RL_G1_1.ORA', options=>sys.dbms_logmnr.NEW);
 10    sys.dbms_logmnr.add_logfile
 11      (logfilename=>'E:\ORACLE\BASES\MIK8\RL_G2_1.ORA', options=>sys.dbms_logmnr.ADDFILE);
 12    sys.dbms_logmnr.add_logfile
 13      (logfilename=>'E:\ORACLE\BASES\MIK8\RL_G3_1.ORA', options=>sys.dbms_logmnr.ADDFILE);
 14    sys.dbms_logmnr.add_logfile
 15      (logfilename=>'E:\ORACLE\BASES\MIK8\RL_G4_1.ORA', options=>sys.dbms_logmnr.ADDFILE);
 16    sys.dbms_logmnr.start_logmnr (
 17      dictFileName => 'E:\Oracle\admin\MIK8\Log\dictionary.ora',
 18      options => options
 19    );
 20  end;
 21  /

PL/SQL procedure successfully completed.

SQL> select sql_undo
  2  from V$LOGMNR_CONTENTS
  3  where seg_owner = 'MICHEL' and seg_name = 'T1' and seg_type_name = 'TABLE'
  4  /
SQL_UNDO
----------------------------------------------------------------------------------------------------
insert into "MICHEL"."T1"("VAL") values (1);
insert into "MICHEL"."T1"("VAL") values (2);
insert into "MICHEL"."T1"("VAL") values (3);
insert into "MICHEL"."T1"("VAL") values (4);
insert into "MICHEL"."T1"("VAL") values (5);
insert into "MICHEL"."T1"("VAL") values (6);
insert into "MICHEL"."T1"("VAL") values (7);
insert into "MICHEL"."T1"("VAL") values (8);
insert into "MICHEL"."T1"("VAL") values (9);
insert into "MICHEL"."T1"("VAL") values (10);

10 rows selected.

SQL> exec sys.dbms_logmnr.end_logmnr;

PL/SQL procedure successfully completed.

Then you just have to execute the displayed statements.

Report message to a moderator

Re: Recovery of deleted table [message #667264 is a reply to message #667254]

Tue, 19 December 2017 00:06

shawaj
Messages: 89
Registered: January 2016

Member

hi Michel Cadot,
Thanks for your interest.

I tried the following, but getting error .

SQL> declare
  2    options pls_integer := sys.dbms_logmnr.skip_corruption;
  3    begin
  4    sys.dbms_logmnr_d.build (dictionary_filename=>'dictionary.ora',dictionary_location=>'E:\Oracle');
  5       sys.dbms_logmnr.add_logfile
  6    (logfilename=>'E:\ORACLE\RL_G1_1.ORA', options=>sys.dbms_logmnr.NEW);
  7   sys.dbms_logmnr.add_logfile
  8   (logfilename=>'E:\ORACLE\RL_G2_1.ORA', options=>sys.dbms_logmnr.ADDFILE);
  9   sys.dbms_logmnr.add_logfile
 10   (logfilename=>'E:\ORACLE\RL_G3_1.ORA', options=>sys.dbms_logmnr.ADDFILE);
 11   sys.dbms_logmnr.add_logfile
 12   (logfilename=>'E:\ORACLE\RL_G4_1.ORA', options=>sys.dbms_logmnr.ADDFILE);
 13   sys.dbms_logmnr.start_logmnr ( dictFileName => 'E:\Oracle\dictionary.ora',options => options );
 14   end;
 15   /


ORA-01308: initialization parameter utl_file_dir is not set
ORA-06512: at "SYS.DBMS_LOGMNR_D", line 793
ORA-06512: at line 5

Report message to a moderator

Re: Recovery of deleted table [message #667265 is a reply to message #667264]

Tue, 19 December 2017 00:17

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Is your Oracle installation directory really E:\ORACLE (just like Michel's)?

(Everybody knows that) I'm not a DBA, but error suggests that you should set that parameter's value in initialization file; as you're on 8i, and if I recall correctly, that might be INIT.ORA.

Report message to a moderator

Re: Recovery of deleted table [message #667267 is a reply to message #667265]

Tue, 19 December 2017 00:51

shawaj
Messages: 89
Registered: January 2016

Member

Littlefoot wrote on Tue, 19 December 2017 00:17

Is your Oracle installation directory really E:\ORACLE (just like Michel's)?

YES, I installed in E:\oracle\.....

[Updated on: Tue, 19 December 2017 00:52]

Report message to a moderator

Re: Recovery of deleted table [message #667268 is a reply to message #667264]

Tue, 19 December 2017 01:33

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

A couple of comments on my code:

Line 6, the directory location must be listed in your "utl_file_dir" init.ora file. This parameter is static, so you have to set it in init.ora and restart the instance.
Lines 8-15, I listed the redo log files Log Miner will analyze to find the operations that have been executed against T1 table. These are my 4 online redo logs (the only ones I have and my database is in NOARCHIVELOG mode so have nothing more), you may have to add some archived logs if the deletion occurred some time ago, the more logs you specify the slower will be Log Miner so add the least you can to get the information you want.
Line 17, "dictFileName" parameter is the concatenation of "dictionary_location" and "dictionary_filename" ones you mentioned on "dbms_logmnr_d.build" call.
In the WHERE clause of the query of V$LOGMNR_CONTENTS you may add "and operation not in ('UNSUPPORTED','INTERNAL')" to remove some unwanted statements.

Report message to a moderator

Previous Topic:	Old Statspack Snap
Next Topic:	UnUsed column in the database

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Nov 28 11:50:49 CST 2024