Home » RDBMS Server » Server Administration » Recovery of deleted table (Oracle8i Enterprise Edition Release 8.1.7.0.0, windows 7)
Recovery of deleted table [message #667249] Mon, 18 December 2017 03:08 Go to next message
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)
Re: Recovery of deleted table [message #667250 is a reply to message #667249] Mon, 18 December 2017 03:13 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Not possible. A requirement of a relational database is that a committed transaction cannot be lost.
Re: Recovery of deleted table [message #667251 is a reply to message #667250] Mon, 18 December 2017 03:24 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Perhaps you might get (some of?) data from backup.
Re: Recovery of deleted table [message #667252 is a reply to message #667250] Mon, 18 December 2017 03:26 Go to previous messageGo to next message
shawaj
Messages: 89
Registered: January 2016
Member
ohhk

Thanks for quick reply..
Re: Recovery of deleted table [message #667253 is a reply to message #667249] Mon, 18 December 2017 03:58 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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.
Re: Recovery of deleted table [message #667264 is a reply to message #667254] Tue, 19 December 2017 00:06 Go to previous messageGo to next message
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
Re: Recovery of deleted table [message #667265 is a reply to message #667264] Tue, 19 December 2017 00:17 Go to previous messageGo to next message
Littlefoot
Messages: 21806
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.
Re: Recovery of deleted table [message #667267 is a reply to message #667265] Tue, 19 December 2017 00:51 Go to previous messageGo to next message
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 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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.
Previous Topic: Old Statspack Snap
Next Topic: UnUsed column in the database
Goto Forum:
  


Current Time: Thu Mar 28 15:12:28 CDT 2024