閃回資料庫之後匯入資料實驗
前面已經講了閃回資料庫，閃回刪除和三種形式的閃回查詢，下面進行總結。
當我們對錶進行了truncate操作之後是無法使用閃回刪除來進行恢復的，此時我們才會去考慮使用閃回資料庫。
但是當我們使用閃回資料庫將資料庫回退到某個時間點的時候也是要付出一些代價的。比如在下午兩點的時候你不小心truncate掉一個很重要的表，而你到兩點半的時候才發現這個誤操作，在兩點到兩點半這段時間也已經進行很多其他的資料庫操作，那麼這時候，如果我們使用閃回資料庫來將資料庫回退到下午兩點時，兩點到兩點半這段時間的資料就會丟失，那麼面對這種問題應該如何解決呢？下面我們透過實驗來演示：
1.新建一個使用者hh，並賦予dba許可權，在hh模式下建立一張測試表flash_t:
HH@orcl 2014-10-12 19:32:02>create table flash_t as select * from scott.dept;
Table created.
HH@orcl 2014-10-12 19:34:45>select * from flash_T;
DEPTNO DNAME LOC
---------- -------------- -------------
80 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
Table created.
HH@orcl 2014-10-12 19:34:45>select * from flash_T;
DEPTNO DNAME LOC
---------- -------------- -------------
80 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
2. 記錄此時的系統時間：
HH@orcl 2014-10-12 19:37:11>select sysdate from dual;
SYSDATE
-------------------
2014-10-12 19:37:41
SYSDATE
-------------------
2014-10-12 19:37:41
3.把表flash_t truncate掉：
4.建立一張表flash_t_1:
HH@orcl 2014-10-12 19:38:59>create table flash_t_1 as select * from dba_users;
Table created.
HH@orcl 2014-10-12 19:39:11>select count(1) from flash_t_1;
COUNT(1)
----------
44
Table created.
HH@orcl 2014-10-12 19:39:11>select count(1) from flash_t_1;
COUNT(1)
----------
44
現在假設我們發現了誤truncate掉表flash_t了，這裡就存在兩種做法。
第一種是直接將資料庫閃回到2014-10-12 19:37:41時間點，而在這個時間點到發現錯誤的時間點之間的資料不要了，然後直接開啟資料庫，那麼誤truncate掉的flash_t就可以恢復了。
第二種是在我們將資料庫閃回到2014-10-12 19:37:41時間點之後，希望這個時間點之後更新的資料可以保留下來。
首先我們看看第一種做法：
把資料庫閃回到2014-10-12 19:37:41時間點：
在這之前我們驗證下如果表被truncate之後是無法透過閃回刪除來恢復的：
HH@orcl 2014-10-12 19:44:31>alter table flash_t enable row movement;
Table altered.
HH@orcl 2014-10-12 19:44:51>flashback table flash_t to timestamp to_timestamp('2014-10-12 19:37:41','yyyy-mm-dd hh24:mi:ss');
flashback table flash_t to timestamp to_timestamp('2014-10-12 19:37:41','yyyy-mm-dd hh24:mi:ss')
*
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed
Table altered.
HH@orcl 2014-10-12 19:44:51>flashback table flash_t to timestamp to_timestamp('2014-10-12 19:37:41','yyyy-mm-dd hh24:mi:ss');
flashback table flash_t to timestamp to_timestamp('2014-10-12 19:37:41','yyyy-mm-dd hh24:mi:ss')
*
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed
（錯誤顯示錶的定義已經被改變了，所以無法進行閃回刪除）
使用閃回資料庫的功能將資料庫閃回到特定時間點（注意使用閃回資料庫功能進行閃回一定要將資料庫處於mount）：
HH@orcl 2014-10-12 19:44:58>conn /as sysdba
Connected.
SYS@orcl 12-OCT-14>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@orcl 12-OCT-14>startup mount;
ORACLE instance started.
Total System Global Area 849530880 bytes
Fixed Size 1339824 bytes
Variable Size 641732176 bytes
Database Buffers 201326592 bytes
Redo Buffers 5132288 bytes
Database mounted.
SYS@orcl 12-OCT-14>flashback database to timestamp to_timestamp('2014-10-12 19:37:41','yyyy-mm-dd hh24:mi:ss');
Flashback complete.
Connected.
SYS@orcl 12-OCT-14>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@orcl 12-OCT-14>startup mount;
ORACLE instance started.
Total System Global Area 849530880 bytes
Fixed Size 1339824 bytes
Variable Size 641732176 bytes
Database Buffers 201326592 bytes
Redo Buffers 5132288 bytes
Database mounted.
SYS@orcl 12-OCT-14>flashback database to timestamp to_timestamp('2014-10-12 19:37:41','yyyy-mm-dd hh24:mi:ss');
Flashback complete.
使用resetlogs開啟資料庫：
SYS@orcl 12-OCT-14>alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
查詢表flash_t的資料庫是否恢復：
HH@orcl 12-OCT-14>select * from flash_t;
DEPTNO DNAME LOC
---------- -------------- -------------
80 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
DEPTNO DNAME LOC
---------- -------------- -------------
80 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
可以看到，在時間點2014-10-12 19:37:41之後進行的操作已經失效：
HH@orcl 12-OCT-14>select count(1) from flash_t_1;
select count(1) from flash_t_1
*
ERROR at line 1:
ORA-00942: table or view does not exist
select count(1) from flash_t_1
*
ERROR at line 1:
ORA-00942: table or view does not exist
下面我們進行如下的演示看看結果：
（前四個步驟是一樣的）
記錄系統時間：
HH@orcl 12-OCT-14>alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
HH@orcl 2014-10-12 19:54:23>select sysdate from dual;
SYSDATE
-------------------
2014-10-12 19:54:27
Session altered.
HH@orcl 2014-10-12 19:54:23>select sysdate from dual;
SYSDATE
-------------------
2014-10-12 19:54:27
把表flash_t truncate掉：
將資料庫閃回到2014-10-12 19:54:27 ：
HH@orcl 2014-10-12 19:56:42>conn /as sysdba
Connected.
SYS@orcl 12-OCT-14>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@orcl 12-OCT-14>startup mount;
ORACLE instance started.
Total System Global Area 849530880 bytes
Fixed Size 1339824 bytes
Variable Size 641732176 bytes
Database Buffers 201326592 bytes
Redo Buffers 5132288 bytes
Database mounted.
SYS@orcl 12-OCT-14>flashback database to timestamp to_timestamp('2014-10-12 19:54:27','yyyy-mm-dd hh24:mi:ss');
Flashback complete.
應用歸檔將資料庫進行恢復：
SYS@orcl 12-OCT-14>recover database;
Media recovery complete.
Connected.
SYS@orcl 12-OCT-14>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@orcl 12-OCT-14>startup mount;
ORACLE instance started.
Total System Global Area 849530880 bytes
Fixed Size 1339824 bytes
Variable Size 641732176 bytes
Database Buffers 201326592 bytes
Redo Buffers 5132288 bytes
Database mounted.
SYS@orcl 12-OCT-14>flashback database to timestamp to_timestamp('2014-10-12 19:54:27','yyyy-mm-dd hh24:mi:ss');
Flashback complete.
應用歸檔將資料庫進行恢復：
SYS@orcl 12-OCT-14>recover database;
Media recovery complete.
檢視資料是否都恢復了：
HH@orcl 12-OCT-14>select * from flash_T;
no rows selected
HH@orcl 12-OCT-14>select count(1) from flash_t_1;
COUNT(1)
----------
44
no rows selected
HH@orcl 12-OCT-14>select count(1) from flash_t_1;
COUNT(1)
----------
44
可以看到，這個結果和沒有進行閃回之前是一樣的，因為我們進行了應用歸檔日誌進行恢復的操作，那麼oracle就會根據歸檔中的記錄將資料庫恢復到最新的狀態，所以結果和沒有閃回是一樣的。因此這是一次失敗的恢復。
接下來進入第二種方式恢復資料：
當我們發現有誤操作了之後，我們可以把之後進行的插入操作的表匯出來，將資料庫閃回到特定時間點之後再將表進行匯入操作：
當我們發現有誤操作了之後，我們可以把之後進行的插入操作的表匯出來，將資料庫閃回到特定時間點之後再將表進行匯入操作：
建立表flash_t:
HH@orcl 2014-10-12 20:20:51>select * from tab;
no rows selected
HH@orcl 2014-10-12 20:20:54>create table flash_t as select * from scott.dept;
Table created.
HH@orcl 2014-10-12 20:21:26>select * from flash_t;
DEPTNO DNAME LOC
---------- -------------- -------------
80 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
no rows selected
HH@orcl 2014-10-12 20:20:54>create table flash_t as select * from scott.dept;
Table created.
HH@orcl 2014-10-12 20:21:26>select * from flash_t;
DEPTNO DNAME LOC
---------- -------------- -------------
80 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
記錄當前系統時間：
HH@orcl 2014-10-12 20:21:33>select sysdate from dual;
SYSDATE
-------------------
2014-10-12 21:15:51
SYSDATE
-------------------
2014-10-12 21:15:51
truncate表flash_t:
建立表flash_t_1:
HH@orcl 2014-10-12 20:23:30>create table flash_t_1 as select * from dba_users;
Table created.
HH@orcl 2014-10-12 20:24:05>select count(1) from flash_t_1;
COUNT(1)
----------
44
Table created.
HH@orcl 2014-10-12 20:24:05>select count(1) from flash_t_1;
COUNT(1)
----------
44
此時，你發現了truncate掉flash_t這張表的誤操作，於是想透過閃回資料庫把資料庫閃回到truncate操作之前，但是在這之前，要先把表flash_t_1匯出：
[oracle@localhost app]$ exp hh/hh file=/u01/app/expimp/flash_t_1.dmp log=/u01/app/expimp/flash_t_1_exp.log tables=flash_t_1;
Export: Release 11.2.0.1.0 - Production on Sun Oct 12 21:19:26 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 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 WE8MSWIN1252 character set (possible charset conversion)
About to export specified tables via Conventional Path ...
. . exporting table FLASH_T_1 44 rows exported
Export terminated successfully without warnings.
Export: Release 11.2.0.1.0 - Production on Sun Oct 12 21:19:26 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 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 WE8MSWIN1252 character set (possible charset conversion)
About to export specified tables via Conventional Path ...
. . exporting table FLASH_T_1 44 rows exported
Export terminated successfully without warnings.
[oracle@localhost expimp]$ ls
flash_t_1.dmp flash_t_1_exp.log
flash_t_1.dmp flash_t_1_exp.log
將資料庫閃回到2014-10-12 21:15:51：
SYS@orcl 12-OCT-14>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@orcl 12-OCT-14>startup mount;
ORACLE instance started.
Total System Global Area 849530880 bytes
Fixed Size 1339824 bytes
Variable Size 641732176 bytes
Database Buffers 201326592 bytes
Redo Buffers 5132288 bytes
Database mounted.
SYS@orcl 12-OCT-14>flashback database to timestamp to_timestamp('2014-10-12 21:15:51','yyyy-mm-dd hh24:mi:ss');
Flashback complete.
SYS@orcl 12-OCT-14>alter database open resetlogs;
Database altered.
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@orcl 12-OCT-14>startup mount;
ORACLE instance started.
Total System Global Area 849530880 bytes
Fixed Size 1339824 bytes
Variable Size 641732176 bytes
Database Buffers 201326592 bytes
Redo Buffers 5132288 bytes
Database mounted.
SYS@orcl 12-OCT-14>flashback database to timestamp to_timestamp('2014-10-12 21:15:51','yyyy-mm-dd hh24:mi:ss');
Flashback complete.
SYS@orcl 12-OCT-14>alter database open resetlogs;
Database altered.
查詢flash_t的資料是否恢復：
SYS@orcl 12-OCT-14>conn hh/hh
Connected.
HH@orcl 12-OCT-14>select * from flash_t;
DEPTNO DNAME LOC
---------- -------------- -------------
80 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
Connected.
HH@orcl 12-OCT-14>select * from flash_t;
DEPTNO DNAME LOC
---------- -------------- -------------
80 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
HH@orcl 12-OCT-14>select * from flash_t_1;
select * from flash_t_1
*
ERROR at line 1:
ORA-00942: table or view does not exist
select * from flash_t_1
*
ERROR at line 1:
ORA-00942: table or view does not exist
下面應用Imp把flash_t_1匯入到資料庫中：
[oracle@localhost expimp]$ imp hh/hh file=/u01/app/expimp/flash_t_1.dmp log=/u01/app/expimp/flash_t_1_imp.log full=y;
Import: Release 11.2.0.1.0 - Production on Sun Oct 12 21:27:02 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V11.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8MSWIN1252 character set (possible charset conversion)
. importing HH's objects into HH
. importing HH's objects into HH
. . importing table "FLASH_T_1" 44 rows imported
Import terminated successfully without warnings.
Import: Release 11.2.0.1.0 - Production on Sun Oct 12 21:27:02 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V11.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8MSWIN1252 character set (possible charset conversion)
. importing HH's objects into HH
. importing HH's objects into HH
. . importing table "FLASH_T_1" 44 rows imported
Import terminated successfully without warnings.
可以看到表flash_t_1已經成功匯入到hh模式中了。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29800581/viewspace-1314524/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 實驗-閃回資料庫資料庫
- dg_閃回資料庫實驗資料庫
- [Flashback]Flashback Database閃回資料庫實驗Database資料庫
- Oracle閃回技術之閃回資料庫Oracle資料庫
- 【Flashback】Flashback Database閃回資料庫功能實驗Database資料庫
- 閃回資料庫資料庫
- 閃回（關於閃回資料庫）資料庫
- 【Oracle 12c資料庫支援閃回庫功能】實驗Oracle資料庫
- Oracle資料庫閃回Oracle資料庫
- Oracle閃回資料庫Oracle資料庫
- 資料庫的閃回資料庫
- FlashBack總結之閃回資料庫與閃回刪除資料庫
- 【備份恢復】閃回資料庫(一）閃回資料庫的管理資料庫
- 【備份恢復】閃回資料庫（五）RMAN 命令列閃回資料庫資料庫命令列
- 【備份恢復】閃回資料庫（二） 基於 SCN 閃回資料庫資料庫
- 閃回資料庫的事情資料庫
- Flashback Database 閃回資料庫Database資料庫
- 監視閃回資料庫資料庫
- [Flashback]開啟資料庫閃回資料庫功能資料庫
- Backup And Recovery User's Guide-使用閃回資料庫來回退資料庫-監控閃回資料庫GUIIDE資料庫
- 【備份恢復】閃回資料庫（三）基於時間戳閃回資料庫資料庫時間戳
- Backup And Recovery User's Guide-使用閃回資料庫-開啟閃回資料庫GUIIDE資料庫
- Backup And Recovery User's Guide-使用閃回資料庫來回退資料庫-執行閃回資料庫操作GUIIDE資料庫
- 資料庫 MySQL 資料匯入匯出資料庫MySql
- Oracle 閃回資料庫測試Oracle資料庫
- 詳解oracle資料庫閃回Oracle資料庫
- 基於SCN閃回資料庫資料庫
- 【Flashback】Flashback Database閃回資料庫功能實踐Database資料庫
- SQLServer 異構資料庫之間資料的匯入匯出SQLServer資料庫
- 用EXP/IMP從高版本資料庫匯出至低版本資料庫匯入實驗資料庫
- Oracle閃回技術 為Oracle閃回配置資料庫Oracle資料庫
- Oracle 閃回技術 概覽 資料庫閃回功能Oracle資料庫
- 【備份恢復】閃回資料庫（四）基於可靠還原點閃回資料庫資料庫
- 批次Insert匯入資料實驗
- 大文字資料，匯入匯出到資料庫資料庫
- Backup And Recovery User's Guide-使用閃回資料庫來回退資料庫-閃回資料庫的先決條件GUIIDE資料庫
- Backup And Recovery User's Guide-使用閃回資料庫和還原點-閃回資料庫GUIIDE資料庫
- 巧用閃回資料庫來檢視歷史資料資料庫