Today, we tested the restoration method for Oracle after deleting table data. There is a table in the maintenance test of the Oracle production system. The data in the table is deleted and commit. The service cannot be interrupted (the database cannot be stopped ), in addition, the system constantly writes data to the table. Now you need to restore the data deleted from the delete operation. You can use scn-based restore.
Today, we tested the restoration method for Oracle after deleting table data. There is a table in the maintenance test of the Oracle production system. The data in the table is deleted and commit. The service cannot be interrupted (the database cannot be stopped ), in addition, the system constantly writes data to the table. Now you need to restore the data deleted from the delete operation. You can use scn-based restore.
Today, we tested the restoration method for Oracle after deleting table data.
There is a table in the maintenance test of the Oracle production system. The data in the table is deleted and commit. The service cannot be interrupted (the database cannot be stopped ), in addition, the system constantly writes data to the table. Now you need to restore the data deleted from the delete operation. You can use scn-based recovery.
Before deleting the database, it is best to query the current scn of the database. The database will be restored based on this scn point.
Example:
Method 1:
1) create a test table;
Create table w as select * FROM dba_objects WHERE 1 = 2;
2) Insert test data into the table;
SQL> insert into w select * from dba_objects;
72558 rows created.
SQL> commit;
Commit complete.
3) SQL> select count (*) from w;
COUNT (*)
----------
72558
4) before deleting the database, obtain the scn of the database to find out when the data exists during recovery;
SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
117853514 ------ current scn number
Because the database has not stopped, the scn number is constantly changing. Therefore, you need to find out which scn point the data exists.
SQL> select count (*) from w as of scn 117854000; ------ check whether data exists when scn is 117854000
Select count (*) from w as of scn 117854000
*
ERROR at line 1:
ORA-08181: specified number is not a valid system change number ------ it should be that the system has not reached this scn number
SQL> select count (*) from w as of scn 117853000; ------ check whether data exists when scn is 117853000
COUNT (*)
----------
0 ------ nonexistent
SQL> select count (*) from w as of scn 117853500;
COUNT (*)
----------
0
SQL> select count (*) from w as of scn 117853510; no data exists until this scn is found, so it is restored based on this scn point.
COUNT (*)
----------
72558
5) simulate data deletion;
SQL> delete from w;
72558 rows deleted.
SQL> commit;
Commit complete.
SQL> SELECT COUNT (*) FROM w;
COUNT (*)
----------
0
6) The simulation database does not stop, the business is constantly inserting data into the table; [Linux community http://www.linuxidc.com]
SQL> insert into w select * from w as of scn 117844000;
72558 rows created.
SQL> select count (*) from w;
COUNT (*)
----------
72558
7) perform Restoration Based on the scn points that still exist in the data before deletion;
SQL> insert into w select * from w as of scn 117853510;
72558 rows created.
SQL> commit;
Commit complete.
8) check whether the deleted data is restored and the newly inserted data also exists;
SQL> select count (*) from w;
COUNT (*)
----------
145116
Success. The first 72558 data records deleted earlier + the new 72558 data records, a total of 145116 data records. Meet our requirements!
Here we will summarize the following:
Exercise caution before deleting a data table, especially when the production system cannot be shut down. You must back up the database or table before deleting it, in this way, you can use a backup for restoration. If no backup is performed during deletion, you must first check the current scn of the system before deletion, it also verifies the scn point in which the data exists (this is very important, if not found, the deleted data cannot be restored) and the data is restored based on this scn point.
Method 2:
1) Create the preceding test table and data;
SQL> select count (*) from w;
COUNT (*)
----------
72558
2) query the database time to determine when the deleted data exists;
SQL> select to_char (sysdate, 'yyyy-dd-mm hh24: mi: ss') from dual;
TO_CHAR (SYSDATE, 'yy
-------------------
2011-23-03 17:23:55
SQL> select count (*) from w;
COUNT (*)
----------
72558 ------- data still exists between 2011-23-03 17:23:55
3) simulate data deletion;
SQL> delete from w;
72558 rows deleted.
SQL> commit;
Commit complete.
4) convert the time before data deletion (the time when the data exists) to scn;
SQL> select timestamp_to_scn (to_timestamp ('2017-03-23 17:22:00 ', 'yyyy-mm-dd hh24: mi: ss') from dual;
TIMESTAMP_TO_SCN (TO_TIMESTAMP ('2017-03-2011: 22: 00', 'yyyy-MM-DDHH24: MI: ss '))
---------------------------------------------------------------------------
117872363
5) verify the existence of data at this scn point;
SQL> select count (*) from w as of scn 117872363;
COUNT (*)
----------
72558
6) create a temporary table when the original table data still exists;
SQL> create table q as select * from w as of scn 117872363;
Table created.
The preceding statement is composed of two statements:
SQL> create table q as select * from w where 1 = 0;
Table created.
SQL> insert into q select * from w as of scn 117872363;
72558 rows created.
SQL> select count (*) from q;
COUNT (*)
----------
72558
7) because the service is not interrupted, new data is also included in the original table. In this case, you only need to export the data of the newly created temporary table and then import it to the original table.
