循序漸進oracle第8章:Oracle的閃回特性之恢復drop表四種方法

mengzhaoliang發表於2008-06-09

/* 2008/06/09
*環境:Windows XP +Oracle10.2.0.1
*循序漸進oracle——資料庫管理、最佳化與備份恢復
*循序漸進oracle第8章:Oracle的閃回特性之恢復drop表四種方法
*恢復
*/

方法一:在非歸檔模式或者歸檔模式下,用flashback table來恢復。
C:\>sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on 星期一 6月 9 08:26:42 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


連線到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select instance_name,status from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
orcl             OPEN

非歸檔模式:
SQL> archive log list;
資料庫日誌模式             非存檔模式
自動存檔             禁用
存檔終點            USE_DB_RECOVERY_FILE_DEST
最早的聯機日誌序列     1
當前日誌序列           3
SQL>
SQL> show parameter flashback;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target        integer     1440
SQL> select dbid,name,flashback_on,current_scn from v$database;

      DBID NAME      FLASHBACK_ON       CURRENT_SCN
---------- --------- ------------------ -----------
1184709774 ORCL      NO                      578449

SQL> alter user scott account unlock;

使用者已更改。


SQL> create table empcopy
  2  as
  3  select * from emp;

表已建立。

SQL> select * from emp;

SQL> drop table emp;

表已刪除。


表已經刪除,怎麼恢復:

用flashback table閃回。(oracle 10g開始可使用該功能)
SQL> flashback table emp to before drop;

閃回完成。

SQL> select count(*) from emp;

  COUNT(*)
----------
        14

 

方法二:(如果啟用閃回資料庫時間記錄,可以用flashback database,啟用閃回資料庫時間記錄的前提是資料庫要執行在歸檔模式下)
SQL> connect sys/mzl as sysdba
已連線。
SQL> select dbid,name,flashback_on,current_scn from v$database;

      DBID NAME      FLASHBACK_ON       CURRENT_SCN
---------- --------- ------------------ -----------
1184709774 ORCL      NO                      584227

啟用閃回資料庫時間記錄
SQL> alter database flashback on;
alter database flashback on
*
第 1 行出現錯誤:
ORA-38706: 無法啟用 FLASHBACK DATABASE 事件記錄。
ORA-38707: 尚未啟用介質恢復。

需要在歸檔模式下才可以啟用 FLASHBACK DATABASE 事件記錄
SQL> alter database archivelog;

資料庫已更改。

SQL> alter database flashback on;

資料庫已更改。

SQL> alter database open;

資料庫已更改。

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

會話已更改。

SQL> select sysdate from dual;

SYSDATE
-------------------
2008-06-09 10:09:47

SQL> drop table emp;

表已刪除。

在資料庫mount狀態下用flashback database 恢復表:

SQL> connect sys/mzl as sysdba
已連線。
SQL> shutdown immediate
資料庫已經關閉。
已經解除安裝資料庫。
ORACLE 例程已經關閉。
SQL> startup mount
ORACLE 例程已經啟動。

Total System Global Area  289406976 bytes
Fixed Size                  1248576 bytes
Variable Size             100664000 bytes
Database Buffers          180355072 bytes
Redo Buffers                7139328 bytes
資料庫裝載完畢。
SQL> flashback database to timestamp
  2  to_timestamp('2008-06-09 10:09:47','yyyy-mm-dd hh24:mi:ss');

閃回完成。

(在上面語句,也可以用scn來進行恢復)
SQL> alter database open read only;

資料庫已更改。

如果資料恢復不夠理想,可以關閉資料庫繼續進行恢復。 如果用'alter database open resetlogs'開啟,在想關閉資料庫用flashback恢復就不行了。一旦resetlogs之後,將不能再flashback至resetlogs之前的時間點。

 

SQL> select count(*) from scott.emp;

  COUNT(*)
----------
        14

SQL> shutdown immediate
資料庫已經關閉。
已經解除安裝資料庫。
ORACLE 例程已經關閉。
SQL> startup
ORACLE 例程已經啟動。

Total System Global Area  289406976 bytes
Fixed Size                  1248576 bytes
Variable Size             100664000 bytes
Database Buffers          180355072 bytes
Redo Buffers                7139328 bytes
資料庫裝載完畢。
ORA-01589: 要開啟資料庫則必須使用 RESETLOGS 或 NORESETLOGS 選項


SQL> alter database open resetlogs;

資料庫已更改。

SQL> select count(*) from scott.emp;

  COUNT(*)
----------
        14

 


方法三:歸檔模式下,用rman的備份基於時間的不完全恢復。
首先進行rman的全備份

C:\>rman target/

恢復管理器: Release 10.2.0.1.0 - Production on 星期一 6月 9 10:27:50 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

連線到目標資料庫: ORCL (DBID=1184709774)

RMAN> backup database tag='full' plus archivelog;


啟動 backup 於 09-6月 -08
當前日誌已存檔
使用目標資料庫控制檔案替代恢復目錄
分配的通道: ORA_DISK_1
通道 ORA_DISK_1: sid=131 devtype=DISK
通道 ORA_DISK_1: 正在啟動存檔日誌備份集
通道 ORA_DISK_1: 正在指定備份集中的存檔日誌
輸入存檔日誌執行緒 =1 序列 =1 記錄 ID=3 時間戳=656936149
輸入存檔日誌執行緒 =1 序列 =2 記錄 ID=1 時間戳=656936141
輸入存檔日誌執行緒 =1 序列 =3 記錄 ID=2 時間戳=656936143
通道 ORA_DISK_1: 正在啟動段 1 於 09-6月 -08
通道 ORA_DISK_1: 已完成段 1 於 09-6月 -08
段控制程式碼=E:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2008_06_09\O1
_MF_ANNNN_TAG20080609T102906_44S5CNDP_.BKP 標記=TAG20080609T102906 註釋=NONE
通道 ORA_DISK_1: 備份集已完成, 經過時間:00:00:08
通道 ORA_DISK_1: 正在啟動存檔日誌備份集
通道 ORA_DISK_1: 正在指定備份集中的存檔日誌
輸入存檔日誌執行緒 =1 序列 =1 記錄 ID=4 時間戳=656936946
通道 ORA_DISK_1: 正在啟動段 1 於 09-6月 -08
通道 ORA_DISK_1: 已完成段 1 於 09-6月 -08
段控制程式碼=E:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2008_06_09\O1
_MF_ANNNN_TAG20080609T102906_44S5CWJM_.BKP 標記=TAG20080609T102906 註釋=NONE
通道 ORA_DISK_1: 備份集已完成, 經過時間:00:00:02
完成 backup 於 09-6月 -08

啟動 backup 於 09-6月 -08
使用通道 ORA_DISK_1
通道 ORA_DISK_1: 啟動全部資料檔案備份集
通道 ORA_DISK_1: 正在指定備份集中的資料檔案
輸入資料檔案 fno=00001 name=E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF
輸入資料檔案 fno=00003 name=E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF
輸入資料檔案 fno=00005 name=E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBF
輸入資料檔案 fno=00002 name=E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF
輸入資料檔案 fno=00004 name=E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF
通道 ORA_DISK_1: 正在啟動段 1 於 09-6月 -08
通道 ORA_DISK_1: 已完成段 1 於 09-6月 -08
段控制程式碼=E:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2008_06_09\O1
_MF_NNNDF_FULL_44S5CYTV_.BKP 標記=FULL 註釋=NONE
通道 ORA_DISK_1: 備份集已完成, 經過時間:00:01:25
通道 ORA_DISK_1: 啟動全部資料檔案備份集
通道 ORA_DISK_1: 正在指定備份集中的資料檔案
備份集中包括當前控制檔案
在備份集中包含當前的 SPFILE
通道 ORA_DISK_1: 正在啟動段 1 於 09-6月 -08
通道 ORA_DISK_1: 已完成段 1 於 09-6月 -08
段控制程式碼=E:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2008_06_09\O1
_MF_NCSNF_FULL_44S5GN98_.BKP 標記=FULL 註釋=NONE
通道 ORA_DISK_1: 備份集已完成, 經過時間:00:00:02
完成 backup 於 09-6月 -08

啟動 backup 於 09-6月 -08
當前日誌已存檔
使用通道 ORA_DISK_1
通道 ORA_DISK_1: 正在啟動存檔日誌備份集
通道 ORA_DISK_1: 正在指定備份集中的存檔日誌
輸入存檔日誌執行緒 =1 序列 =2 記錄 ID=5 時間戳=656937045
通道 ORA_DISK_1: 正在啟動段 1 於 09-6月 -08
通道 ORA_DISK_1: 已完成段 1 於 09-6月 -08
段控制程式碼=E:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2008_06_09\O1
_MF_ANNNN_TAG20080609T103045_44S5GPJ3_.BKP 標記=TAG20080609T103045 註釋=NONE
通道 ORA_DISK_1: 備份集已完成, 經過時間:00:00:02
完成 backup 於 09-6月 -08

刪除表:
C:\>sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on 星期一 6月 9 10:32:15 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


連線到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select count(*) from emp;

  COUNT(*)
----------
        14

SQL> drop table emp;

表已刪除。


用備份來進行恢復:
C:\>rman target/

恢復管理器: Release 10.2.0.1.0 - Production on 星期一 6月 9 10:34:22 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

連線到目標資料庫: ORCL (DBID=1184709774)

RMAN> shutdown immediate

使用目標資料庫控制檔案替代恢復目錄
資料庫已關閉
資料庫已解除安裝
Oracle 例項已關閉

RMAN> startup mount

已連線到目標資料庫 (未啟動)
Oracle 例項已啟動
資料庫已裝載

系統全域性區域總計     289406976 位元組

Fixed Size                     1248576 位元組
Variable Size                104858304 位元組
Database Buffers             176160768 位元組
Redo Buffers                   7139328 位元組

RMAN> sql 'alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"';

sql 語句: alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss"

RMAN> restore database;

啟動 restore 於 09-6月 -08
使用通道 ORA_DISK_1

通道 ORA_DISK_1: 正在開始恢復資料檔案備份集
通道 ORA_DISK_1: 正在指定從備份集恢復的資料檔案
正將資料檔案00001恢復到E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF
正將資料檔案00002恢復到E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF
正將資料檔案00003恢復到E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF
正將資料檔案00004恢復到E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF
正將資料檔案00005恢復到E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBF
通道 ORA_DISK_1: 正在讀取備份段 E:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORC
L\BACKUPSET\2008_06_09\O1_MF_NNNDF_FULL_44S71ZMK_.BKP
通道 ORA_DISK_1: 已恢復備份段 1
段控制程式碼 = E:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2008_06_09\
O1_MF_NNNDF_FULL_44S71ZMK_.BKP 標記 = FULL
通道 ORA_DISK_1: 恢復完成, 用時: 00:01:55
完成 restore 於 09-6月 -08

RMAN> recover database until time '2008-06-09 11:00:00';

啟動 recover 於 09-6月 -08
使用通道 ORA_DISK_1

正在開始介質的恢復
介質恢復完成, 用時: 00:00:03

完成 recover 於 09-6月 -08

RMAN> alter database open resetlogs;

資料庫已開啟


C:\>sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on 星期一 6月 9 11:08:42 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


連線到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select count(*) from emp;

  COUNT(*)
----------
        14

 

方法四:歸檔模式下,用rman的備份基於SCN的不完全恢復。
SQL>connect scott/mzl;
SQL> drop table emp;

表已刪除。

用用rman的備份基於SCN的不完全恢復:
C:\>rman target/

恢復管理器: Release 10.2.0.1.0 - Production on 星期一 6月 9 12:44:27 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

連線到目標資料庫: ORCL (DBID=1184709774)

RMAN> shutdown immediate

使用目標資料庫控制檔案替代恢復目錄
資料庫已關閉
資料庫已解除安裝
Oracle 例項已關閉

RMAN> startup mount

已連線到目標資料庫 (未啟動)
Oracle 例項已啟動
資料庫已裝載

系統全域性區域總計     289406976 位元組

Fixed Size                     1248576 位元組
Variable Size                113246912 位元組
Database Buffers             167772160 位元組
Redo Buffers                   7139328 位元組

啟動到mount下,進一部確定drop emp前的scn值。
C:\>sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on 星期一 6月 9 12:46:47 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


連線到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select instance_name,status from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
orcl             MOUNTED

SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

會話已更改。

SQL> select thread#,first_change#,next_change#,first_time from v$log_history;

   THREAD# FIRST_CHANGE# NEXT_CHANGE# FIRST_TIME
---------- ------------- ------------ -------------------
         1        534907       567823 2008-06-09 08:17:21
         1        567823       573653 2008-06-09 08:19:42
         1        573653       587297 2008-06-09 08:20:00
         1        586670       588420 2008-06-09 10:15:38
         1        588420       588487 2008-06-09 10:29:06
         1        589360       589820 2008-06-09 10:51:11
         1        589820       589874 2008-06-09 10:57:54
         1        589898       590299 2008-06-09 11:07:52
         1        590299       590367 2008-06-09 11:13:31

已選擇9行。

或者:
SQL> select name,first_change#,next_change#,first_time from v$archived_log;

RMAN> restore database;

啟動 restore 於 09-6月 -08
分配的通道: ORA_DISK_1
通道 ORA_DISK_1: sid=154 devtype=DISK

通道 ORA_DISK_1: 正在開始恢復資料檔案備份集
通道 ORA_DISK_1: 正在指定從備份集恢復的資料檔案
正將資料檔案00001恢復到E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF
正將資料檔案00002恢復到E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF
正將資料檔案00003恢復到E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF
正將資料檔案00004恢復到E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF
正將資料檔案00005恢復到E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBF
通道 ORA_DISK_1: 正在讀取備份段 E:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORC
L\BACKUPSET\2008_06_09\O1_MF_NNNDF_ORCL_44S7ZB6T_.BKP
通道 ORA_DISK_1: 已恢復備份段 1
段控制程式碼 = E:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2008_06_09\
O1_MF_NNNDF_ORCL_44S7ZB6T_.BKP 標記 = ORCL
通道 ORA_DISK_1: 恢復完成, 用時: 00:01:55
完成 restore 於 09-6月 -08

RMAN> recover database until scn 590367;

啟動 recover 於 09-6月 -08
使用通道 ORA_DISK_1

正在開始介質的恢復
介質恢復完成, 用時: 00:00:03

完成 recover 於 09-6月 -08

RMAN> alter database open resetlogs;

資料庫已開啟


檢查資料庫的表資料:

SQL> select count(*) from scott.emp;

  COUNT(*)
----------
        14

還有一個不完全恢復是基於取消的不完全恢復,一般應用於聯機重做日誌或者歸檔日誌損壞了。

 

**********************************************************************

1  Oracle10g中時間和scn對映關係函式
SQL> select dbms_flashback.get_system_change_number scn from dual;

       SCN
----------
    592854

SQL> select timestamp_to_scn(to_timestamp('2008-06-09 13:50:00','yyyy-mm-dd hh24
:mi:ss')) time from dual;

      TIME
----------
    592844

SQL> select scn_to_timestamp(592054) "TIME" from dual;

TIME
---------------------------------------------------------------------------
09-6月 -08 01.26.50.000000000 下午

SQL> select timestamp_to_scn(to_timestamp('2008-06-09 13:50:00','yyyy-mm-dd hh24
:mi:ss')) "SCN" from dual;

       SCN
----------
    592844

SQL> select timestamp_to_scn(scn_to_timestamp(592844)) "SCN" from dual;

       SCN
----------
    592844

 

2 、表空間的剩餘狀態
SQL> select tablespace_name,sum(bytes)/1024/1024 "Free MB"
  2      from dba_free_space
  3  group by tablespace_name order by 2;

TABLESPACE_NAME                   Free MB
------------------------------ ----------
SYSAUX                                1.5
USERS                              1.5625
UNDOTBS1                           5.0625
SYSTEM                               5.75
EXAMPLE                           22.3125

****************************************************************************

 

 

 

總結:如果刪除一個表,最簡單的莫過於用oracle10g中的flashback table功能,就一條語句“flashback table emp to before drop”;如果資料是oracle9i資料庫,則最好執行在歸檔模式下,啟動閃回資料庫時間記錄(flashback),在mount狀態下用flashback database來恢復;最後沒有辦法啟用備份的不完全恢復來恢復資料。恢復時基於scn的恢復,可以查詢v$log_history或者v$archived_log檢視。select thread#,first_change#,next_change#,first_time from v$log_history;
select name,first_change#,next_change#,first_time from v$archived_log;
不完全恢復有三種:基於時間、SCN、取消
recover database until time '2008-06-08 22:10:00';
recover database until scn  '227799';
recover database until cancel;
如果使用舊的控制檔案還要加using backup controlfile;


 

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/12778571/viewspace-343098/,如需轉載,請註明出處,否則將追究法律責任。

相關文章