Restore table accidentally deleted records, and delete table records by mistake

Restore table accidentally deleted records, and delete table records by mistake
Restore table accidentally deleted records

 

I recently handled a user's accidental deletion of the delete table. Here I made a simple summary. The content of this article is based on the essence of the masters in pub, I am using it for a while. Basic Solution:

1. if not submitted, use rollback. (It should be unlikely .)
2. If the submission time exceeds 5 minutes and is less than undo_retention, you can use the flashback function. Specific restrictions and operations can refer to: http://blog.itpub.net/post/468/15464
3. If neither of the above conditions is met, you can use logminer to recover from redo. The use restrictions and descriptions of logminer can be referred to: http://blog.itpub.net/post/468/13436

Here is a simple example of using logminer: http://blog.itpub.net/post/468/11764
4. If this log has been reused and archive mode is not enabled, you can only recover it from the backup.

 

 

 

2. Use flashback to restore table data

2.1 ---- flashback table

It is mainly about the content of the undo tablespace.

 

Note: You must enable row movement for the table.

 

---- Format time

SQL> alter session set nls_date_format = 'yyyy-mm-dd hh24: mi: ss ';

SQL> select sysdate from dual;

SYSDATE

-------------------

2014-07-27 20:23:37

 

---- Enable row movement

Alter table xxx enable row movement:

 

---- Flashback table based on scn:

SQL> flashback table kel. t1 to scn 896744;

 

--- Flashback table based on time points:

SQL> flashback table kel. t1 to timestamp to_timestamp ('2017-07-28 00:18:00 ', 'yyyy-mm-dd hh24: mi: ss ');

 

 

2.2 ---- flashback query:

Flashback query can only view the status of objects at a certain time point, but cannot see the status changes within a certain period of time.

 

Select * from tb as of timestamp to_timestamp ('2017-07-27 20:23:37 ', 'yyyy-mm-dd hh24: mi: ss ');

 

 

2.3 ----- flashback drop

Used to restore objects accidentally deleted by users, including tables and indexes, dependent on tablespace recycle bin and tablespace recycle bin

 

The recyclebin parameter is used to control whether recyclebin is enabled. The default value is ON.

In recycle bin, oracle can differentiate tables with the same names of different users.

Maintenance of recycle bin:

 

1. It can only be used for non-system tablespaces and locally managed tablespaces

2. If an object is created in a locally managed tablespace, and an associated object, such as an index, is created in a dictionary-managed tablespace, The tablespace associated object managed by the field cannot be restored.

3. Whether the object can be restored successfully depends on whether the object space is overwritten and reused.

4. You cannot perform DML or DDL operations on the content in recycle bin. You can only perform query operations.

5. Object Reference constraints cannot be restored, that is, foreign key constraints must be rebuilt.

 

Manually clear the space of recycle bin:

Purge tablespace kel

 

 

Db_flashback_retention_target Default value 1440 (minutes)

The Flash query uses the undo information.

 

 

UNDO_RETENTION:

 

The UNDO_RETENTION parameter is ignored for a fixed size undo tablespace. The database may overwrite unexpired undo information when tablespace space becomes low.

If the undo tablespace size is fixed, oracle ignores the setting of the undo_retention parameter. If the undo space is insufficient, oracle may overwrite undo information that has not expired.

 

For an undo tablespace with the AUTOEXTEND option enabled, the database attempts to honor the minimum retention period specified by UNDO_RETENTION. when space is low, instead of overwriting unexpired undo information, the tablespace auto-extends. if the MAXSIZE clause is specified for an auto-extending undo tablespace, when the maximum size is reached, the database may begin to overwrite unexpired undo information.

When undo is automatically extended, oracle will try its best to ensure the undo retention time set by undo_retention. When undo space is insufficient, oracle will automatically expand the undo tablespace first, instead of overwriting undo information that has not expired, if the maxsize parameter is set for undo tbs, oracle may overwrite undo information that has not expired when the maximum undo space is reached.

 

 

 

Note: For objects in the system tablespace, flash-back drop is invalid because the objects to be dropped are not placed in recyclebin:

 

---- Confirm whether recyclebin is enabled

SQL> show parameter recyclebin

 

---- You can view the Object List in recycle bin through the View:

Select object_name as recycle_name, original_name from recyclebin;

 

---- You can view the content of objects in recycle bin like a normal table:

Select count (*) from "BIN $/ti6DA4axIDgQKjAbgFLmA ==$ 0 ";

 

To use the flashback drop function to restore tables accidentally deleted, you need to do the following:

1. confirm that the deleted table is in recycle bin.

2. Use flashback table for restoration

If a table with the same name exists in the recycle bin, you can restore it to the expected table:

Select count (*) from "BIN $/ti6DA4cxIDgQKjAbgFLmA = $0"; -- view which table is to be restored

 

--- Restore table

Flashback table "BIN $/ti6DA4exIDgQKjAbgFLmA = $0" to before drop;

 

--- Restore the table and rename it kel1

Flashback table kel to before drop rename to kel1;

 

 

 

 

 

 

 

3. Use oracle logminer for data recovery:

Logmnr is actually a very useful tool for data recovery. Someone mentioned today that it has restored 7 million of the data, and I have recovered tens of thousands of financial data records.

-- Create a table to save the extracted SQL
Create table logmnr_content tablespace tools as select scn, cscn, TIMESTAMP, SQL _undo from v $ logmnr_contents where 1 = 0;

-- Query the object_id and data_object_id corresponding to the table through dba_objects, which is used to extract the SQL _undo related to the table from the log.
Select object_id, data_object_id from dba_objects where object_name = ???

-- Online extraction through the range of archived logs found in the OS
Insert undo SQL into a table. For example, the log serial number range is 5813 to 5850.
Archive log files one by one to prevent insufficient temporary space.


Begin
For I in 8 .. 24 loop

Dbms_logmnr.add_logfile (LogFileName => '/disk2/oradata/arch/crmcn/crmcn_1 _' | I | '. arc ');
Dbms_logmnr.start_logmnr (Options => sys. dbms_logmnr.DICT_FROM_ONLINE_CATALOG );
Dbms_logmnr.start_logmnr ();

Insert into logmnr_content (scn, cscn, TIMESTAMP, SQL _undo)
Select scn, cscn, TIMESTAMP, SQL _undo from v $ logmnr_contents
Where DATA_OBJD # = 52528;

Commit;
Dbms_logmnr.end_logmnr ();

End loop;
End

/



-- Insert the extracted SQL statement into the table through dynamic SQL Execution

Declare
SQL _str varchar2 (4000 );
Begin
For c in (select * from logmnr_content) loop
SQL _str: = replace (c. SQL _undo ,';','');
Execute immediate SQL _str;
End loop;
Commit;
End;


Note: if there are other operations on the table in this log, you can combine the OPERATION type OPERATION and submit the scn cscn to determine whether the data is restored.

 

 

 

 

 

 

 

 

 

 

 

How to restore accidentally deleted Excel table content? The machine has restarted

I can tell you responsibly that your table cannot be recovered.
If a file is deleted, there are multiple methods to restore the file. However, after the content is deleted and the disk is removed, the probability of restoring the file is almost zero.
If you have set timed saving, try to go to the path of the temporary storage disk to see if there are any files with automatic storage. Generally, automatic archiving is overwritten every time you manually archive the files. Find it.
Attachment, my automatic archive path for office2007
Remember to open it: display system files, hide files, and automatically archive files.
Good luck ~~!




How Does oracle restore accidentally deleted Table Record Data?

-- Enable the row movement function
Alter table name enable row movement;
-- Restore table data
Flashback table name to timestamp to_timestamp ('2017-05-19 09:40:00 ', 'yyyy-mm-dd hh24: mi: ss ');
The above time is the time when the data is deleted.
Note: Disable the row movement function after restoration.