备案控制台
开发者社区 数据库 文章 正文

20161114File Space Bitmap Block修复机制2

2016-11-14 662
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介： [20161114]File Space Bitmap Block修复机制2.txt --前几天在测试File Space Bitmap Block时执行了，execute dbms_space_admin.TABLESPACE_REBUILD_BITMAPS('SUGAR') --这样位图区全部设置为1,显示都是F。

[20161114]File Space Bitmap Block修复机制2.txt

--前几天在测试File Space Bitmap Block时执行了，execute dbms_space_admin.TABLESPACE_REBUILD_BITMAPS('SUGAR')
--这样位图区全部设置为1,显示都是F。

--上次提到，execute dbms_space_admin.TABLESPACE_REBUILD_BITMAPS('SUGAR')会修改位图区全部设置为1，显示都是F。
--而内存中相应数据块是正确的，只要执行alter system checkpoint;就会"纠正这个错误".我还做了1个极端测试，就是
--execute dbms_space_admin.TABLESPACE_REBUILD_BITMAPS('SUGAR')后，另外一个会话强制执行shutdown abort。
--测试这个时候备份的情况，忘记先drop表了，补充测试.

1.环境：
SCOTT@book> @ &r/ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

CREATE TABLESPACE SUGAR DATAFILE
  '/mnt/ramdisk/book/sugar01.dbf' SIZE 40M AUTOEXTEND ON NEXT 16M MAXSIZE UNLIMITED
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

create table t1 tablespace sugar as select rownum id ,lpad('A',32,'A') name from dual connect by level<=1e5;
--建立大小5M的表。

create table t2 tablespace sugar as select rownum id ,lpad('B',32,'B') name from dual connect by level<=2e5;
create table t3 tablespace sugar as select rownum id ,lpad('C',32,'C') name from dual connect by level<=2e5;
alter system checkpoint;

2.做一个极端测试:

drop table t1 purge ;
drop table t2 purge ;
drop table t3 purge ;

SYS@book> execute dbms_space_admin.TABLESPACE_REBUILD_BITMAPS('SUGAR');
PL/SQL procedure successfully completed.

SYS@book> shutdown abort
ORACLE instance shut down.

$ bvi -b 24576 -s 8192 /mnt/ramdisk/book/sugar01.dbf
00006000  1E A2 00 00 03 00 80 01 55 46 62 00 00 00 01 04 79 41 00 00 06 00 00 00 80 00 00 00 00 00 00 00 ........UFb.....yA..............
00006020  00 F8 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 FF FF FF FF FF FF FF FF ................................
00006040  FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF ................................
00006060  FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF ................................
00006080  FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF ................................
000060A0  FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF ................................
000060C0  FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF ................................
000060E0  FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF ................................
00006100  FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF ................................
00006120  FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF FF ................................

--可以发现是FF。然后重新启动数据库到mount状态。

SYS@book> startup mount
ORACLE instance started.

Total System Global Area  634732544 bytes
Fixed Size                  2255792 bytes
Variable Size             197133392 bytes
Database Buffers          427819008 bytes
Redo Buffers                7524352 bytes
Database mounted.

RMAN> backup datafile 6 format '/u01/backup/d6_A.bak' ;

Starting backup at 2016-11-14 14:56:06
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=/mnt/ramdisk/book/sugar01.dbf
channel ORA_DISK_1: starting piece 1 at 2016-11-14 14:56:07
channel ORA_DISK_1: finished piece 1 at 2016-11-14 14:56:08
piece handle=/u01/backup/d6_A.bak tag=TAG20161114T145607 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2016-11-14 14:56:08

$ ls -l /u01/backup/d6_A.bak
-rw-r----- 1 oracle oinstall 26066944 2016-11-14 14:56:07 /u01/backup/d6_A.bak

--大小26066944/1024/1024=24.859375M,可以发现备份时依赖这个位图区扫描。
--注：前面的分析有误，感谢别人纠正这个错误，做测试太不严谨了，我前面的测试实际上oracle对null block是不备份的的。

$ strings /u01/backup/d6_A.bak | grep  'AAAAAAAAAAAAA' |wc
100000  170040 3624269
$ strings /u01/backup/d6_A.bak | grep  'BBBBBBBBBBBBB' |wc
200000  340080 7243655
$ strings /u01/backup/d6_A.bak | grep  'CCCCCCCCCCCCC' |wc
200000  340080 7243655

SYS@book> alter database open ;
Database altered.

SYS@book> alter system checkpoint;
System altered.

$ bvi -b 24576 -s 8192 /mnt/ramdisk/book/sugar01.dbf
00006000  1E A2 00 00 03 00 80 01 57 46 62 00 00 00 01 04 79 41 00 00 06 00 00 00 80 00 00 00 00 00 00 00 ........WFb.....yA..............
00006020  00 00 00 00 00 F8 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................................
00006040  00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................................
00006060  00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................................
00006080  00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................................
000060A0  00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................................
000060C0  00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................................
000060E0  00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 ................................

--很奇怪这次位图区修复了。是否drop table t1 purge时没有写盘。重复测试在drop完成后加入检查点，这样建表是没有问题的。

RMAN> backup datafile 6 format '/u01/backup/d6_B.bak' ;
Starting backup at 2016-11-14 15:03:06
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=/mnt/ramdisk/book/sugar01.dbf
channel ORA_DISK_1: starting piece 1 at 2016-11-14 15:03:07
channel ORA_DISK_1: finished piece 1 at 2016-11-14 15:03:08
piece handle=/u01/backup/d6_B.bak tag=TAG20161114T150306 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2016-11-14 15:03:08

$ ls -l /u01/backup/d6_B.bak
-rw-r----- 1 oracle oinstall 1089536 2016-11-14 15:03:07 /u01/backup/d6_B.bak
--可以发现这次备份不仅仅1M上下。

SCOTT@book> create table t4 tablespace sugar as select rownum id ,lpad('D',32,'D') name from dual connect by level<=1e5;
Table created.

3.重新测试再drop后加入检查点：
--步骤参考上面。仅仅在drop后加入alter system checkpoint;

--建立表空间，建表t1,t2,t3,

drop table t1 purge ;
drop table t2 purge ;
drop table t3 purge ;
alter system checkpoint;

SYS@book> execute dbms_space_admin.TABLESPACE_REBUILD_BITMAPS('SUGAR');
PL/SQL procedure successfully completed.

SYS@book> shutdown abort
ORACLE instance shut down.

--
SYS@book> startup
ORACLE instance started.
Total System Global Area  634732544 bytes
Fixed Size                  2255792 bytes
Variable Size             197133392 bytes
Database Buffers          427819008 bytes
Redo Buffers                7524352 bytes
Database mounted.
Database opened.

SCOTT@book> create table t4 tablespace sugar as select rownum id ,lpad('D',32,'D') name from dual connect by level<=1e5;
create table t4 tablespace sugar as select rownum id ,lpad('D',32,'D') name from dual connect by level<=1e5
                                                                                 *
ERROR at line 1:
ORA-00600: internal error code, arguments: [ktfbbssearch-9], [0], [624], [63488], [], [], [], [], [], [], [], []
--确实如此。

RMAN> backup datafile 6 format '/u01/backup/d6_C.bak' ;

Starting backup at 2016-11-14 15:23:58
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=46 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=/mnt/ramdisk/book/sugar01.dbf
channel ORA_DISK_1: starting piece 1 at 2016-11-14 15:23:59
channel ORA_DISK_1: finished piece 1 at 2016-11-14 15:24:00
piece handle=/u01/backup/d6_C.bak tag=TAG20161114T152359 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2016-11-14 15:24:00

$ ls -l /u01/backup/d6_C.bak
-rw-r----- 1 oracle oinstall 26066944 2016-11-14 15:24:00 /u01/backup/d6_C.bak

--可以发现备份达到25M。

SYS@book> execute dbms_space_admin.TABLESPACE_REBUILD_BITMAPS('SUGAR');
PL/SQL procedure successfully completed.

RMAN> backup datafile 6 format '/u01/backup/d6_D.bak' ;

Starting backup at 2016-11-14 15:25:05
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=/mnt/ramdisk/book/sugar01.dbf
channel ORA_DISK_1: starting piece 1 at 2016-11-14 15:25:05
channel ORA_DISK_1: finished piece 1 at 2016-11-14 15:25:06
piece handle=/u01/backup/d6_D.bak tag=TAG20161114T152505 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2016-11-14 15:25:06

$ ls -l /u01/backup/d6_D.bak
-rw-r----- 1 oracle oinstall 1089536 2016-11-14 15:25:05 /u01/backup/d6_D.bak

--修复后一切正常，再次说明备份通过读取数据文件的问题信息，确定读取的数据块。而前一次测试存在错误。

文章标签：
关系型数据库
数据库
Oracle
Perl
SQL
Linux
lfreeali
目录
相关文章
后端从入门到精通
|
存储 索引
File Space Header & xdes(5)FSP HDR独立表空间结构（三十一)
File Space Header & xdes(5)FSP HDR独立表空间结构（三十一)
后端从入门到精通
114 0
长沙红胖子Qt
|
算法 开发者
关于 加载图片"Corrupt JPEG data: premature end of data segment" 的解决方法
关于 加载图片"Corrupt JPEG data: premature end of data segment" 的解决方法
长沙红胖子Qt
1549 0
关于 加载图片"Corrupt JPEG data: premature end of data segment" 的解决方法
德哥,digoal
|
关系型数据库 Linux 数据库
PostgreSQL 单库对象过多，触发Linux系统限制 (ext4_dx_add_entry: Directory index full!) (could not create file "xx/xx/xxxxxx": No space left on device)
德哥,digoal
4444 0
技术小阿哥
|
Unix Linux PHP
No space left on device错误，df -h看磁盘并没有满（含inode原理）
技术小阿哥
4475 0
科技探索者
|
Linux 数据安全/隐私保护
Clonezilla制作镜像时报错：extfsclone.c:bitmap free count err解决办法
科技探索者
1835 0
科技小能手
|
存储 网络安全 文件存储
3PAR File Persona无法配置或提示 block-only 解决方法
科技小能手
2120 0
科技小能手
|
SQL
查询v$lock缓慢和direct path write temp等待
科技小能手
1090 0
技术小甜
|
SQL 存储 安全
SQL Server 环形缓冲区（Ring Buffer） -- RING_BUFFER_SECURITY_ERROR 诊断安全相
技术小甜
1480 0
lfreeali
|
Oracle 关系型数据库 数据库
[20161111File Space Bitmap Block修复机制
[20161111]File Space Bitmap Block修复机制.txt --前几天在测试File Space Bitmap Block时执行了，execute dbms_space_admin.TABLESPACE_REBUILD_BITMAPS('SUGAR') --这样位图区全部设置为1,显示都是F。
lfreeali
1153 0
lfreeali
|
测试技术
1110File Space Bitmap Block损坏能修复吗3
[20161110]File Space Bitmap Block损坏能修复吗3.txt --今天仔细检查才发现我原来的链接存在问题，http://blog.itpub.
lfreeali
838 0