fix:
Since there is no undo command for a truncate, a recovery of the database is
required to get the data back in the table. This document outlines the steps to
follow to recover from the truncate command.
This document is intended to give a step by step guide for experienced
users. It assumes that the some basic knowledge about backup and restore of an
Oracle database is available.
Step by Step solution
-----------------------------
*NOTE: Always backup the database before starting to recover or restore old
backups.
1. Is there an export dump of the table which I can use to restore the data?
a. yes -> import the file
b. no -> go to step 2.
2. Is the database is archivelog mode ?
a. yes -> go to step 3.
b. no -> go to step 4
3. The database is in archivelog mode. This makes it possible to recover the
database until a moment just before the truncate command.
a. restore a backup of the database to another location.
b. recover this database to a moment in time BEFORE the truncate command
RECOVER DATABASE UNTIL TIME .....
c. export the table data from this recovered database.
d. import the data back into the original database
e. remove the restored database.
4. Since the database is not in archivelog mode, there is no way to recover
the database to a time just before the truncate command.
Contact Oracle Support to discuss the possible options left.
You can also use FLASHBACK to rollback a TRUNCATE
This scenario will show how we can use a combination of FLASHBACK database and also recovery to take a database back in time to undo a TRUNCATE operation and then roll forward the database after the flashback operation to bring it to the current point in time.
INSERT ROWS INTO TABLE MYOBJ – THIS WILL BE TRUNCATED
SQL> insert into test.myobj select * from all_objects;
50496 rows created.
SQL> /
50496 rows created.
SQL> select count(*) from test.myobj;
COUNT(*)
----------
100992
OBTAIN THE CURRENT SCN – FLASHBACK WILL HAPPEN TO THIS SCN
SQL> select current_scn from v$database;
CURRENT_SCN
---------------------
15633908021
TRUNCATE THE TABLE
SQL> truncate table test.myobj;
Table truncated.
SQL> select count(*) from test.myobj;
COUNT(*)
----------
0
AT THE SAME TIME OTHER CHANGES ARE HAPPENING IN THE DATABASE AND THESE CHANGES WILL BE RECOVERED AFTER THE FLASHBACK IS DONE
SQL> insert into test.myobj2 select * from test.myobj2;
356874 rows created.
SQL> /
713748 rows created.
SQL> commit;
Commit complete.
SHUTDOWN THE DATABASE AND PERFORM THE FLASHBACK TO THE SCN BEFORE THE TRUNCATE WAS DONE
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 469762048 bytes
Fixed Size 2084880 bytes
Variable Size 377491440 bytes
Database Buffers 83886080 bytes
Redo Buffers 6299648 bytes
Database mounted.
SQL> FLASHBACK DATABASE TO SCN 15633908021;
Flashback complete.
OPEN THE DATABASE IN READ ONLY MODE AND EXPORT THE TABLE THAT WAS TRUNCATED EARLIER. THIS TABLE WILL BE IMPORTED AFTER THE RECOVERY IS DONE
SQL> alter database open read only;
Database altered.
SQL> select count(*) from test.myobj;
COUNT(*)
----------
100992
SQL> quit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
crashdb:/u03/oradata/crashdb/arch> exp file=test.dmp tables=myobj
Export: Release 10.2.0.4.0 - Production on Fri Feb 6 09:53:00 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Username: test
Password:
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)
About to export specified tables via Conventional Path ...
. . exporting table MYOBJ 100992 rows exported
Export terminated successfully without warnings.
NOW SHUTDOWN THE DATABASE,STARTUP MOUNT AND PERFORM THE RECOVERY
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 696254464 bytes
Fixed Size 2086616 bytes
Variable Size 184551720 bytes
Database Buffers 503316480 bytes
Redo Buffers 6299648 bytes
Database mounted.
SQL> recover database;-----contrilfile 不要动
Media recovery complete.
SQL> alter database open;
Database altered.
AS EXPECTED TABLE MYOBJ WHICH WAS TRUNCATED NOW AGAIN HAS 0 ROWS AFTER THE RECOVERY – WE CAN IMPORT THE DUMP WE TOOK AFTER THE FLASHBACK
SQL> select count(*) from test.myobj;
COUNT(*)
----------
0
CONFIRM THAT OTHER COMMITTED CHANGES IN THE DATABASE HAVE BEEN RECOVERED
SQL> select count(*) from test.myobj2;
COUNT(*)
----------
713748
RELATED DOCUMENTS
-----------------------------------
Oracle Server Backup and Recovery Guide文章来源地址https://www.toymoban.com/news/detail-683302.html
文章来源：https://www.toymoban.com/news/detail-683302.html
到了这里，关于flashback 后 恢复现场的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！
