Restore table accidentally deleted records

Source: Internet
Author: User
The table deletion record recovery operation recently handled the fault of accidentally deleting deletetable by users. Here we 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 no submission is made, use rollback. (It should be unlikely .) 2. If the submission time exceeds 5 minutes

The table accidental deletion record recovery operation recently handled a user's accidental deletion of the delete table fault. Here we make a simple summary. The content of this article is sorted from the essence of the masters in pub, I am using it for a while. Basic Solution: 1. If no submission is made, use rollback. (It should be unlikely .) 2. If the submission time exceeds 5 minutes

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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.