亚洲激情专区-91九色丨porny丨老师-久久久久久久女国产乱让韩-国产精品午夜小视频观看

溫馨提示×

溫馨提示×

您好，登錄后才能下訂單哦！

密碼登錄×
登錄注冊×
其他方式登錄
點擊 登錄注冊 即表示同意《億速云用戶服務條款》

Oracle數據塊損壞之10231內部事件不完全恢復

發布時間：2020-09-25 05:32:02 來源：腳本之家 閱讀：242 作者：AlfredZhao 欄目：數據庫

什么是塊損壞：

所謂損壞的數據塊，是指塊沒有采用可識別的 Oracle 格式，或者其內容在內部不一致。通常情況下，損壞是由硬件故障或操作系統問題引起的。Oracle 數據庫將損壞的塊標識為“邏輯損壞”或“介質損壞”。如果是邏輯損壞，則是 Oracle 內部錯誤。Oracle 數據庫檢測到不一致之后，就將邏輯損壞的塊標記為損壞。如果是介質損壞，則是塊格式不正確；從磁盤讀取的塊不包含有意義的信息。實驗：某個分區數據塊損壞，不完全恢復此分區表數據。

 背景：數據庫沒有有效備份，某個分區中有數據塊損壞。

 要求：最大限度恢復此分區數據。

 環境：RHEL 6.4 + Oracle 11.2.0.4

下面這篇文章主要給大家介紹了關于Oracle數據塊損壞之10231內部事件的相關內容，分享出來供大家參考學習，下面來看看詳細的介紹：

1. 初始化實驗環境

初始化創建模擬實驗環境用到的表空間、業務用戶、表，并導入測試數據。

本次實驗用到表空間DBS_D_JINGYU, 業務用戶JINGYU, 分區表T_PART(含兩個分區的測試數據)。

-- 數據表空間
create tablespace dbs_d_jingyu datafile '/u02/oradata/jingyu/dbs_d_jingyu01.dbf' size 30M autoextend off;
-- 臨時表空間
create temporary tablespace temp_jingyu tempfile '/u02/oradata/jingyu/temp_jingyu01.tmp' size 30M autoextend off;
-- 索引表空間(可選)
create tablespace dbs_i_jingyu datafile '/u02/oradata/jingyu/dbs_i_jingyu01.dbf' size 30M autoextend off;
-- 假設創建用戶 jingyu 密碼 jingyu，默認臨時表空間 temp_jingyu， 默認數據表空間 dbs_d_jingyu。
CREATE USER jingyu IDENTIFIED BY jingyu
 TEMPORARY TABLESPACE temp_jingyu
 DEFAULT TABLESPACE dbs_d_jingyu
 QUOTA UNLIMITED ON dbs_d_jingyu;
-- 賦予普通業務用戶權限
grant resource, connect to jingyu;
-- 賦予DBA用戶權限
grant dba to jingyu;
-- 業務用戶登錄
conn jingyu/jingyu 
-- 1.1 創建分區表
create table t_part(
id number, 
name varchar2(20), 
start_time date, 
content varchar2(200)
)partition by range(start_time)
(
 partition P20150101 values less than (TO_DATE(' 2015-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
 tablespace dbs_d_jingyu,
 partition P20150102 values less than (TO_DATE(' 2015-01-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
 tablespace dbs_d_jingyu,
 partition P20150103 values less than (TO_DATE(' 2015-01-03 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
 tablespace dbs_d_jingyu
);

-- 1.2 插入測試數據
--分區P20150102插入10000行數據
begin
 for i in 1..10000 loop
 insert into t_part values (i,'alfred'||i, to_date('2015-01-01','yyyy-mm-dd'), 'AAAAAAAAAA');
 end loop;
 commit;
end;
/
--分區P20150103插入20000行數據
begin
 for i in 10001..30000 loop
 insert into t_part values (i,'alfred'||i, to_date('2015-01-02','yyyy-mm-dd'), 'AAAAAAAAAA');
 end loop;
 commit;
end;
/

-- 1.3查詢表數據量和大小
select count(1) from t_part; 
--result: 30000
select count(1) from t_part partition(P20150102); 
--result: 10000
select count(1) from t_part partition(P20150103); 
--result: 20000
--普通表/分區表的每個分區大約__G大小
set linesize 160
col segment_name for a30
select (t.bytes/1024/1024) "MB", t.owner, t.segment_name, t.partition_name, t.tablespace_name from dba_segments t where segment_name = 'T_PART';
 MB OWNER  SEGMENT_NAME  PARTITION_NAME  TABLESPACE_NAME
---------- ------------------------------ ------------------------------ ------------------------------ ------------------------------
 8 JINGYU  T_PART  P20150102  DBS_D_JINGYU
 8 JINGYU  T_PART  P20150103  DBS_D_JINGYU

2. 模擬分區中有數據塊損壞情景

我這里使用BBED制造壞塊，修改t_part分區表的分區P20150103中的某個塊內容，模擬真實環境中有數據塊損壞的情景。

--查詢分區P20150103的HEADER_BLOCK
select header_file,header_block from dba_segments where segment_name='T_PART' and partition_name='P20150103' and owner='JINGYU';
SQL> select header_file,header_block from dba_segments where segment_name='T_PART' and partition_name='P20150103' and owner='JINGYU';

HEADER_FILE HEADER_BLOCK
----------- ------------
  5  1169

--查詢某一行記錄所在的塊
select
 rowid,
 dbms_rowid.rowid_relative_fno(rowid)rel_fno,
 dbms_rowid.rowid_block_number(rowid)blockno,
 dbms_rowid.rowid_row_number(rowid) rowno
 from t_part where id = 20000; 

SQL> select
 2 rowid,
 3 dbms_rowid.rowid_relative_fno(rowid)rel_fno,
 4 dbms_rowid.rowid_block_number(rowid)blockno,
 5 dbms_rowid.rowid_row_number(rowid) rowno
 6 from t_part where id = 20000;

ROWID   REL_FNO BLOCKNO ROWNO
------------------ ---------- ---------- ----------
AAAVveAAFAAAATBABX  5 1217  87

使用bbed工具破壞5號文件1217塊內容，

BBED工具：https://www.jb51.net/article/118349.htm

[oracle@JY-DB01 ~]$ bbed parfile=/tmp/bbed.par
Password:

BBED: Release 2.0.0.0.0 - Limited Production on Tue Jan 19 11:37:59 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

************* !!! For Oracle Internal Use only !!! ***************

BBED> set dba 5,1217
 DBA  0x014004c1 (20972737 5,1217)

BBED> map
 File: /u02/oradata/jingyu/dbs_d_jingyu01.dbf (5)
 Block: 1217     Dba:0x014004c1
------------------------------------------------------------
 KTB Data Block (Table/Cluster)

 struct kcbh, 20 bytes   @0 

 struct ktbbh, 72 bytes   @20 

 struct kdbh, 14 bytes   @100 

 struct kdbt[1], 4 bytes   @114 

 sb2 kdbr[177]    @118 

 ub1 freespace[815]    @472 

 ub1 rowdata[6901]    @1287 

 ub4 tailchk    @8188 


BBED> d /v offset 0 count 128
 File: /u02/oradata/jingyu/dbs_d_jingyu01.dbf (5)
 Block: 1217 Offsets: 0 to 127 Dba:0x014004c1
-------------------------------------------------------
 06a20000 c1044001 52733100 00000106 l ......@.Rs1.....
 a18b0000 01000c00 de5b0100 4d733100 l .........[..Ms1.
 0000e81f 021f3200 81044001 02001b00 l ......2...@.....
 5d0b0000 fc0fc000 df030600 b1200000 l ]............ ..
 52733100 00000000 00000000 00000000 l Rs1.............
 00000000 00000000 00000000 00000000 l ................
 00000000 0001b100 ffff7401 a3042f03 l ..........t.../.
 2f030000 b100711f 4a1f231f fc1ed51e l /.....q.J.#.....

 <16 bytes per line>

BBED> modify /x 19901010 offset 0
 File: /u02/oradata/jingyu/dbs_d_jingyu01.dbf (5)
 Block: 1217  Offsets: 0 to 127  Dba:0x014004c1
------------------------------------------------------------------------
 19901010 c1044001 52733100 00000106 a18b0000 01000c00 de5b0100 4d733100 
 0000e81f 021f3200 81044001 02001b00 5d0b0000 fc0fc000 df030600 b1200000 
 52733100 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
 00000000 0001b100 ffff7401 a3042f03 2f030000 b100711f 4a1f231f fc1ed51e

 <32 bytes per line>

BBED> sum apply
Check value for File 5, Block 1217:
current = 0xa9ae, required = 0xa9ae

BBED>

至此破壞了5號文件，1217塊。

查詢v$database_block_corruption

select * from v$database_block_corruption;

SQL> select * from v$database_block_corruption;

 FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
  5 1217  1   0 CORRUPT

--此時查詢分區表T_PART
alter system flush buffer_cache;
select count(1) from t_part;
--查詢報錯ORA-01578
select count(1) from t_part partition(P20150102);
--查詢正常，即分區P20150102未受影響
select count(1) from t_part partition(P20150103);
--查詢報錯ORA-01578

--嘗試邏輯導出表數據失敗
[oracle@JY-DB01 ~]$ exp jingyu/jingyu tables=t_part file=t_part.dmp log=exp_t_part.log

Export: Release 11.2.0.4.0 - Production on Tue Jan 19 11:52:21 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table    T_PART
. . exporting partition   P20150101  0 rows exported
. . exporting partition   P20150102 10000 rows exported
. . exporting partition   P20150103
EXP-00056: ORACLE error 1578 encountered
ORA-01578: ORACLE data block corrupted (file # 5, block # 1217)
ORA-01110: data file 5: '/u02/oradata/jingyu/dbs_d_jingyu01.dbf'
Export terminated successfully with warnings.
[oracle@JY-DB01 ~]$

3. 嘗試使用Oracle內部事件10231進行不完全恢復

使用Oracle 10231內部事件可以跳過壞塊

--啟用10231內部事件
alter system set events='10231 trace name context forever,level 10';
--關閉10231內部事件
alter system set events='10231 trace name context off';

測試設置10231事件后是否可以邏輯導出：

[oracle@JY-DB01 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Jan 19 14:01:43 2016

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> alter system set events='10231 trace name context forever,level 10';

System altered.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
[oracle@JY-DB01 ~]$ exp jingyu/jingyu tables=t_part file=t_part.dmp log=exp_t_part.log

Export: Release 11.2.0.4.0 - Production on Tue Jan 19 14:01:57 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table    T_PART
. . exporting partition   P20150101  0 rows exported
. . exporting partition   P20150102 10000 rows exported
. . exporting partition   P20150103 19823 rows exported
Export terminated successfully without warnings.

--成功導出后記得要關閉10231內部事件
alter system set events='10231 trace name context off';

20000 - 19823 = 177行，也就是說該數據塊損壞直接導致了177行數據丟失。不過還好，保住了大部分數據。

實際上設置10231內部事件后，如果上面邏輯導出沒問題，這種情況自然還可以把數據直接導出到臨時表，更加方便。

SQL> select count(1) from t_part;
select count(1) from t_part
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5, block # 1217)
ORA-01110: data file 5: '/u02/oradata/jingyu/dbs_d_jingyu01.dbf'

SQL> alter system set events='10231 trace name context forever,level 10';

System altered.

SQL> select count(1) from t_part;

 COUNT(1)
----------
 29823

SQL> create table temp_t_part_20150103 as select * from t_part partition(P20150103);

Table created.

SQL> alter system set events='10231 trace name context off';

System altered.

SQL> select count(1) from t_part partition(P20150103);
select count(1) from t_part partition(P20150103)
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5, block # 1217)
ORA-01110: data file 5: '/u02/oradata/jingyu/dbs_d_jingyu01.dbf'

SQL> select count(1) from temp_t_part_20150103;

 COUNT(1)
----------
 19823

Reference

•http://blog.csdn.net/tianlesoftware/article/details/5024966

•http://blog.csdn.net/seertan/article/details/8507045

•http://blog.csdn.net/coolyl/article/details/195919

總結

以上就是這篇文章的全部內容了，希望本文的內容對大家的學習或者工作能帶來一定的幫助，如果有疑問大家可以留言交流，謝謝大家對億速云的支持。

向AI問一下細節

免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。

AI
助
手
岳西县| 水富县| 白河县| 金华市| 台北县| 襄樊市| 阿图什市| 随州市| 澎湖县| 惠安县| 福贡县| 罗田县| 桑日县| 杭锦旗| 开平市| 和政县| 乌兰县| 南郑县| 玛多县| 道孚县| 疏附县| 古蔺县| 东明县| 松溪县| 枣强县| 观塘区| 内黄县| 龙江县| 视频| 津市市| 逊克县| 华阴市| 寿宁县| 洛浦县| 穆棱市| 陵水| 苍南县| 贞丰县| 闻喜县| 许昌市| 南乐县|