Oracle's recovery methods for mistakenly deleting data and tables include truncate

When we operate the database at work, a problem often occurs: the user accidentally deletes a very important table or table data and no backup, need to recover as soon as possible, the following is the solution:

Primarily leveraging the flashback characteristics of the Oracle Recycle Bin the Oracle Recycle Bin RecycleBin is a new feature of 10g, and when we drop table Cube_scope "purge", if purge is not specified, the system simply renames the table to bin$ Starting with the name, and in the data dictionary to modify the relevant data, the table occupied by the physical space is not a real recycling, the space occupied by the original table space, when the table space is not enough, Oracle will be dropscn# automatically to clean up the Recycle Bin in the space occupied by the image, 10g by default, the Recycle Bin feature is turned on.

Delete data deleted by mistake

1. The table structure has not changed after deleting data

Direct use of the table flashback, the table flash back requires that the user must have flash any table permission

Determine when to delete data (the time before the data is deleted, but preferably to the point in time when the data is deleted)

ALTER TABLE name enable row Movement//Turn on line move function

Flashback table name to timestamp to_timestamp (' Delete point in time ', ' yyyy-mm-dd hh24:mi:ss ')

Restore data to a state before deleting a point in time

Alter Table name Disable row movement//Turn Off line move function (must not forget)

2. The table structure has changed after deleting data

SELECT * from table name as of timestamp to_timestamp (' Delete point in time ', ' yyyy-mm-dd hh24:mi:ss ')

Find the data that was deleted

Insert into table name (SELECT * from table name as of timestamp to_timestamp (' Delete point in time ', ' yyyy-mm-dd hh24:mi:ss ')); Re-insert the deleted data back into the original table, but note that the primary key does not repeat

Ii. Drop command to delete a table

Because Oracle does not directly empty the blocks of the table when it deletes the table, Oracle places the information for the deleted tables in a virtual container "Recycle Bin", but only the data blocks of the table are marked for overwriting, so it can be resumed before the blocks are reused.

To view a table in the Recycle Bin

Select Object_name,original_name,partition_name,type,ts_name,createtime,droptimefrom RecycleBin;

You can see that the deleted table is named ' bin$0z+2ccjdsjqkx1nsp/re7w==$0 ' in the Recycle Bin

Flashback table name to before drop (rename to new table name)//Flash back to original table (rename new table name)

Or

Flashback table name in the Recycle Bin (for example: bin$0z+2ccjdsjqkx1nsp/re7w==$0) to before drop (rename to new table name)// Users may frequently create and delete the same table multiple times to find the Recycle Bin table name Flash back to the appropriate table version

But in this way we just got our watch back, and our indexes and constraints didn't come back. So we must re-establish indexes and constraints. If

The Oracle Flashback feature can also flash back to the entire database, bringing the database back to a previous state.

ALTER DATABASE Flashback on

Flashback database to SCN Scnno; Flash back using the SCN number or flash back using a timestamp

Flashback database to timestamp to_timestamp (' 2007-10-25 12:00:00 ', ' yyyy-mm-dd hh24:mi:ss ');

If you want to remove the occupied space without going through the Recycle Bin

drop table name purge//This command is equivalent to Truncate+drop operation and is generally not recommended.

Manually clear the objects in the Recycle Bin.

Prug table tablespace. Table name//Clear specific objects

Prug tablespace table Space//clears the specified Tablespace object

Purge tablespace table Space user username//Delete table space Specify all the images under the user

Purge RecycleBin//delete Recycle Bin

Purge Dba_recyclebin//delete data for all users in the Recycle Bin

Oracle Space Utilization Principles

1. Unused space using an existing table space

2. If there is no free space, check the Recycle Bin, for the object of the Recycle Bin according to the principle of FIFO, for the object that is deleted first, Oracle will be removed from the Recycle Bin to meet the demand of the newly allocated space when the space is insufficient.

3. If the Recycle Bin also has no objects to clean up, check to see if the tablespace is self-expanding, expand the table space if self-expanding, and then allocate the new space

4. If the table space is not self-expanding, or has not been self-expanding (reaching the maximum limit), the direct report space is insufficient error, the program terminates

* Drop tablespace: Clears all the recyclebin in the tablespace that belong to the

* Drop User: All RecycleBin that belong to the user will be cleared

* Drop cluster: All members belonging to the cluster in the RecycleBin will be cleared as

* Drop type: All dependent on the type in RecycleBin will be cleared

Also need to pay attention to a situation, like the table space to have enough space, or even drop off through recyclebin due to the lack of space Oracle will automatically delete Oh (remember)!

Third, truncate delete data

1. Using the Flashback feature

Because truncate is not a DML statement, it is a DDL statement and cannot be used to recover table data using a flashback query, which describes a way to recover data by Flashback database (flashback to the entire databases).

Under 11g, the command can be executed in mount and open mode

Select status from V$instance; View Database Schema

ALTER DATABASE open; or the mount.

ALTER DATABASE Flashback on

Flashback database to timestamp to_timestamp (' 2007-10-25 12:00:00 ', ' yyyy-mm-dd hh24:mi:ss ');

After you finish executing the Flashback database command, you can repair the databases in several ways:

1). Direct ALTER DATABASE open resetlogs the data that is generated after the SCN or timestamp time point is lost.

2). Execute the ALTER DATABASE open read Only command in read-only mode, to see if the recovered data meets the requirements, or to open the database through resetlogs if required, or to re-execute the flashback operation.

3). Execute the ALTER DATABASE open read Only command in read-only mode and then immediately export the data to the table in the form of a logical export and then execute the recover database command to re-apply the redo generated by the database, repair the database to the state before flashback database operation, and then re-import the table that was previously mistakenly manipulated by a logical import, so that the impact on existing data is minimal and there is no data loss.

The second method is shown here

alterdatabase open readonly;

查看数据是否符合要求不符合继续Flashback database to timestamp To_timestamp

If you meet

startup mount force；

alterdatabase openresetlog;

 

 

 

The third method of

After the flashback, ALTER DATABASE open Read only

EXP User name/user password file=t.dmp tables= table name; Exporting data from a TRUNCATE table if you are unfamiliar with the exp command, you can go to the EXP/EXPDP and IMP/IMPDP command import and Export database

Shutdown immediate//Restart the database to the point in time before flashback

startup Mount;

Recover database;

ALTER DATABASE open;

Then import the data that you just exported

IMP username/password file=t.dmp tables= table name ignore=y;

Second, the use of fy_recover_data bag

Fy_recover_data

Based on the evil, attached to the reference address, interested friends can go to see http://blog.chinaunix.net/uid-23284114-id-3754559.html

Oracle's recovery methods for mistakenly deleting data and tables include truncate