oracle資料庫災難挽救應急方案之DDL誤操作恢復（drop）

龍山游龍發表於2022-10-20
Oracle資料庫

1、核心業務表誤drop恢復（no purge）

1.1模擬drop誤操作

建立jason業務表，如下：

SQL> create table jason as select * from dba_objects;
SQL> create index idx_jason_oid on jason(object_id);

使用者在刪除資料的時候，不小心誤刪jason業務表，如下：

SQL> drop table jason;

1.2透過回收站恢復誤刪資料

首先，檢查誤刪業務表當前所屬使用者的回收站，如下：

SQL> select object_name,original_name,operation,type,ts_name,droptime from user_recyclebin;

如果運氣較好，被誤刪的業務表還保留在回收站中，那麼可以透過回收站進行還原恢復，如下：

SQL> flashback table "BIN$ggkFrmhwKEngUwjSqMDkqA==$0" to before drop rename to JASON_NEW;
SQL> alter table JASON rename to JASON_OLD;
SQL> alter table JASON_NEW rename to JASON;

檢查回收站功能如果開啟，可選擇關閉，如下： 

SQL> ALTER SESSION SET recyclebin = OFF;
SQL> ALTER SYSTEM SET recyclebin = OFF SCOPE = SPFILE;

檢查回收站功能如果關閉，可選擇開啟，如下： 

SQL> ALTER SESSION SET recyclebin = ON;
SQL> ALTER SYSTEM SET recyclebin = ON SCOPE = SPFILE;

2、核心業務表誤drop恢復（purge）

2.1模擬drop誤操作

建立jason業務表，如下： 

SQL> create tablespace tabtest datafile '/home/oracle/test/oradata/tabtest01.dbf' size 10m autoextend on;
SQL> create table tabtest tablespace tabtest as select * from dba_objects;

使用者在刪除資料的時候，不小心誤刪jason業務表，如下： 

SQL> drop table tabtest purge;


2.2將誤刪表所在的表空間設定為只讀

由於表已經誤刪除，因此需要和使用者或業務開發等相關人員確認該業務表所在的表空間，並儘快將此業務表空間設定為只讀模式，在設定之前需要建立與tabtest表擁有相同表結構的tabtest_old表，如下：

SQL> alter system set deferred_segment_creation=false;
SQL> create table tabtest_old tablespace tabtest as select * from dba_objects where 1=2;
SQL> alter tablespace tabtest read only;


2.3臨時在SYSTEM表空間建立恢復表

由於tabtest表資料恢復，勢必要涉及到IO讀寫操作，如果資料量不大，可臨時設定恢復表的預設表空間為SYSTEM；如果資料量很大，建議為恢復表建立新的預設表空間，如下：

 SQL> create table tabtest_new tablespace system as select * from dba_objects where 1=2;


2.4透過日誌挖掘獲取誤刪表data_object_id

首先，手動切換歸檔日誌，並記錄切換後時間戳，如下：

SQL> alter system switch logfile;
SQL> alter system switch logfile;
SQL> select sysdate from dual;
SYSDATE
-------------------
2022-06-20 15:07:11

檢查誤操作發生至歸檔手動切換後系統生成的歸檔日誌，如下： 

col name for a60
set line 200
select unique NAME, THREAD#, SEQUENCE#, COMPLETION_TIME
  from gv$archived_log
 where thread# = 1
   and COMPLETION_TIME >=
       to_date('2022-05-09 13:15:03', 'yyyy-mm-dd hh24:mi:ss')
   and COMPLETION_TIME <=
       to_date('2022-05-09 13:24:47', 'yyyy-mm-dd hh24:mi:ss')
 order by SEQUENCE#;


新增第一個要載入的歸檔日誌檔案，如下：

SQL> execute dbms_logmnr.add_logfile(logfilename=>'/home/oracle/test/arch/1_84_1106134187.dbf',options=>dbms_logmnr.new);

多次新增要載入的歸檔日誌檔案，如下： 

SQL> execute dbms_logmnr.add_logfile(logfilename=>'/home/oracle/test/arch/1_85_1106134187.dbf',options=>dbms_logmnr.addfile);

執行日誌挖掘分析，如下： 

SQL> execute dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);

查詢v$logmnr_contents動態效能檢視，獲取歸檔日誌解析資訊，如下： 

SQL> select count(*) from v$logmnr_contents;
  COUNT(*)
----------
    93190
SQL> create table sys.hzmc_logmnr_contents_fordrop as select * from v$logmnr_contents;

關閉日誌挖掘分析，如下： 

SQL> execute dbms_logmnr.end_logmnr;

獲取drop purge動作執行的時間，如下： 

SQL> select scn,timestamp,sql_redo from sys.hzmc_logmnr_contents_fordrop where operation='DDL' and lower(sql_redo) like '%tabtest%' order by timestamp desc;
       SCN TIMESTAMP           SQL_REDO
---------- ------------------- ---------------------------
 266498191 2022-06-20 14:55:43 drop table tabtest purge;

獲取誤刪業務表data_object_id，如下： 

SQL> select scn,timestamp,sql_redo from sys.hzmc_logmnr_contents_fordrop where timestamp like '2022-06-20 14:55%' and sql_redo like 'delete%';

2.5透過ROWID嘗試找回誤刪表資料

DBMS_ROWID.ROWID_CREATE包的語法，如下：

DBMS_ROWID.ROWID_CREATE (rowid_type IN NUMBER,object_number IN NUMBER,relative_fno IN NUMBER,block_number IN NUMBER,row_number IN NUMBER) RETURN ROWID;

DBMS_ROWID.ROWID_CREATE傳參解析，如下：

rowid_type Type (restricted or extended). Set the rowid_type parameter to 0 for a restricted ROWID. Set it to 1 to create an extended ROWID. If you specify rowid_type as 0, then the required object_number parameter is ignored, and ROWID_CREATE returns a restricted ROWID.

object_number Data object number (rowid_object_undefined for restricted).

relative_fno Relative file number.

block_number Block number in this file.

row_number Returns row number in this block.


將tabtest_old表的dataobj#修改為2.2.4節獲取到的dataobj#，如下：

SQL> select obj#,dataobj# from obj$ where name='TABTEST_OLD';
SQL> update obj$ set DATAOBJ#=89741 where obj#=89744;
SQL> commit;

獲取tabtest_old表所在的資料檔案號和段頭塊號，如下： 

set linesize 200 pagesize 200
col owner for a10
col segment_name for a15
select owner, segment_name, header_file, header_block, segment_type
  from dba_segments
 where segment_name = 'TABTEST_OLD';
OWNER      SEGMENT_NAME    HEADER_FILE HEADER_BLOCK SEGMENT_TYPE
---------- --------------- ----------- ------------ ------------------
SYS        TABTEST_OLD               8          130 TABLE

先透過dbms_rowid.ROWID_CREATE包獲取第8號資料檔案的第131資料塊的第1行ROWID，再根據ROWID返回TABTEST表的object_id欄位值，透過與dba_objects查詢對比，發現一致，說明透過該方式可以嘗試找回誤刪表的資料，如下： 

alter system flush shared_pool;
alter system flush shared_pool;
alter system flush buffer_cache;
alter system flush buffer_cache;
SQL> select object_id from tabtest_old where rowid=dbms_rowid.ROWID_CREATE(1,89741,8,131,0);
 OBJECT_ID
----------
        20
SQL> select object_id from dba_objects where rownum<=1;
 OBJECT_ID
----------
        20


2.6透過PL/SQL嘗試找回所有誤刪表資料

SET SERVEROUTPUT ON
DECLARE
   nrows number;
   rid rowid;
   objd number;
   ROWSPERBLOCK number;
  BEGIN
   ROWSPERBLOCK:=1000;
   nrows:=0;
   for i in (
select file_id,block_id,blocks from dba_free_space where tablespace_name='TABTEST'
 union all                                                                    
select file_id,block_id,8 blocks from dba_extents where segment_name='TABTEST_OLD'
            )
   loop
     for fblkno in i.block_id..i.block_id+i.blocks-1 loop
      for fblkrow in 1..ROWSPERBLOCK loop
      begin
        rid := dbms_rowid.ROWID_CREATE(1,89741,i.file_id, fblkno,fblkrow-1);
        insert into sys.tabtest_new
        select *
        from sys.tabtest_old a
        where rowid = rid;
        if sql%rowcount = 1 then nrows:=nrows+1; end if;
        if (mod(nrows,1000)=0) then commit; end if;
      exception when others then null;
      end;
      end loop;
    end loop;
   end loop;
   COMMIT;
   dbms_output.put_line('Total rows: '||to_char(nrows));
  END;
  /
Total rows: 86726
PL/SQL procedure successfully completed.


待資料找回後，再將tabtest_new表重新命名為tabtest表，在業務低峰期再對tabtest表做表空間遷移，如下；

SQL> alter table tabtest_new rename to tabtest;
SQL> alter tablespace tabtest read write;
SQL> alter table tabtest move tablespace tabtes


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

相關文章