Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to restore the drop lost table by SQL". Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "how to restore the table lost by drop by SQL".

If you delete data by mistake, in 10g or higher, you can use the Recycle Bin to recover tables dropped by drop.

First of all, make sure the recycling station is in use.

SQL > show parameter recycle

NAME TYPE VALUE

-

Buffer_pool_recycle string

Db_recycle_cache_size big integer 0

Recyclebin string on

Make sure there is a table dropped by drop in recyclebin

SQL > select owner,original_name from dba_recyclebin where original_name='BACKUPTEST'

OWNER ORIGINAL_NAME

SCOTT BACKUPTEST

Flashback table

SQL > flashback table scott.backuptest to before drop

Flashback complete.

SQL > select * from scott.backuptest

A

-

one

Restore deleted tables from other media

1. If you have a backup before the data change (exp,expdp,duplicate,rman backup), you can use this backup to restore the database or tablespace, etc., find the table before the change, and then transfer it to the database.

two。 Use parameters such as until scn,seq,time of rman to restore the database or tablespace to another environment, find the table before the change, and then transfer to the official database

Restore the database

RMAN > restore database

Starting restore at 2017-07-30 14:44:56

Allocated channel: ORA_DISK_1

Channel ORA_DISK_1: SID=18 device type=DISK

Channel ORA_DISK_1: starting datafile backup set restore

Channel ORA_DISK_1: specifying datafile (s) to restore from backup set

Channel ORA_DISK_1: restoring datafile 00001 to / u01/app/oracle/oradata/test/system01.dbf

Channel ORA_DISK_1: restoring datafile 00002 to / u01/app/oracle/oradata/test/sysaux01.dbf

Channel ORA_DISK_1: restoring datafile 00003 to / u01/app/oracle/oradata/test/undotbs01.dbf

Channel ORA_DISK_1: restoring datafile 00004 to / u01/app/oracle/oradata/test/users01.dbf

Channel ORA_DISK_1: reading from backup piece / home/oracle/1csal8ic_1_1

Channel ORA_DISK_1: piece handle=/home/oracle/1csal8ic_1_1 tag=TAG20170730T131244

Channel ORA_DISK_1: restored backup piece 1

Channel ORA_DISK_1: restore complete, elapsed time: 00:00:16

Finished restore at 2017-07-30 14:45:12

Recover to a specified point in time

RMAN > recover database until time '2017-07-30 14 0000'

Starting recover at 2017-07-30 14:51:57

Using channel ORA_DISK_1

Channel ORA_DISK_1: starting incremental datafile backup set restore

Channel ORA_DISK_1: specifying datafile (s) to restore from backup set

Destination for restore of datafile 00001: / u01/app/oracle/oradata/test/system01.dbf

Destination for restore of datafile 00002: / u01/app/oracle/oradata/test/sysaux01.dbf

Destination for restore of datafile 00003: / u01/app/oracle/oradata/test/undotbs01.dbf

Destination for restore of datafile 00004: / u01/app/oracle/oradata/test/users01.dbf

Channel ORA_DISK_1: reading from backup piece / home/oracle/1fsal9aj_1_1

Channel ORA_DISK_1: piece handle=/home/oracle/1fsal9aj_1_1 tag=TAG20170730T132538

Channel ORA_DISK_1: restored backup piece 1

Channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

Starting media recovery

Media recovery complete, elapsed time: 00:00:00

Finished recover at 2017-07-30 14:51:58

RMAN > alter database open resetlogs

Database opened

Just find the missing table and export and import to the target library.

Reference documentation

How to Recover From a DROP / TRUNCATE / DELETE TABLE with RMAN (document ID 223543.1)

At this point, I believe you have a deeper understanding of "how SQL recovers the drop lost table". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.