關於資料區塊出現的游離壞塊的實驗:,資料游離實驗
用dbv 檢查資料檔案的情況
[oracle@orahost ~]$ dbv file=/oracle/oradata/orcl/users01.dbf
DBVERIFY: Release 10.2.0.5.0 - Production on Tue May 6 06:48:02 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
DBVERIFY - Verification starting : FILE = /oracle/oradata/orcl/users01.dbf
Page 310 is marked corrupt
Corrupt block relative dba: 0x01000136 (file 4, block 310)
Bad check value found during dbv:
Data in bad block:
type: 6 format: 2 rdba: 0x01000136
last change scn: 0x0000.00261975 seq: 0x1 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x19750601
check value in block header: 0xe1fd
computed block checksum: 0x570e
Page 334 is marked corrupt
Corrupt block relative dba: 0x0100014e (file 4, block 334)
Bad check value found during dbv:
Data in bad block:
type: 6 format: 2 rdba: 0x0100014e
last change scn: 0x0000.00261976 seq: 0x1 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x19760601
check value in block header: 0x735e
computed block checksum: 0x765b
Page 351 is marked corrupt
Corrupt block relative dba: 0x0100015f (file 4, block 351)
Bad check value found during dbv:
Data in bad block:
type: 6 format: 2 rdba: 0x0100015f
last change scn: 0x0000.00261975 seq: 0x1 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x19750601
check value in block header: 0x7148
computed block checksum: 0x3127
DBVERIFY - Verification complete
Total Pages Examined : 800
Total Pages Processed (Data) : 647
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 3
Total Pages Failing (Index): 0
Total Pages Processed (Other): 129
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 18
Total Pages Marked Corrupt : 3
Total Pages Influx : 0
Highest block SCN : 2531497 (0.2531497)
通過以上的記錄,看到資料庫出現了壞塊的情況
使用rman識別資料庫中損壞的對象,(ID 1623348.1)
[root@orahost ~]# su - oracle
[oracle@orahost ~]$ rman target /
Recovery Manager: Release 10.2.0.5.0 - Production on Tue May 6 06:54:39 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: ORCL (DBID=1327540369)
RMAN> backup validate check logical database;
Starting backup at 06-MAY-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=32 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/oracle/oradata/orcl/system01.dbf
input datafile fno=00003 name=/oracle/oradata/orcl/sysaux01.dbf
input datafile fno=00002 name=/oracle/oradata/orcl/undotbs01.dbf
input datafile fno=00006 name=/oracle/oradata/orcl/ggusr01.dbf
input datafile fno=00007 name=/oracle/oradata/orcl/testblock01.dbf
input datafile fno=00005 name=/oracle/oradata/orcl/testbak.dbf
input datafile fno=00008 name=/oracle/oradata/orcl/testbak2.dbf
input datafile fno=00004 name=/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:36
Finished backup at 06-MAY-08
壞塊資訊會被記錄在視圖 V$DATABASE_BLOCK_CORRUPTION 中。11g RMAN 會產生一個 trace 檔案
[oracle@orahost ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.5.0 - Production on Tue May 6 06:59:22 2008
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Welcome to Yau's oracle world !
this is a test environment
fighting deal friend !!!
rem****下面的查詢可以將視圖 v$database_block_corruption 中記錄的壞塊匹配到對應的段或者是空閑塊。****rem
SQL> set lines 200 pages 10000
SQL> col segment_name format a30
SQL>
SQL> SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#
2 , greatest(e.block_id, c.block#) corr_start_block#
3 , least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block#
4 , least(e.block_id+e.blocks-1, c.block#+c.blocks-1)
5 - greatest(e.block_id, c.block#) + 1 blocks_corrupted
6 , null description
7 FROM dba_extents e, v$database_block_corruption c
8 WHERE e.file_id = c.file#
9 AND e.block_id <= c.block# + c.blocks - 1
10 AND e.block_id + e.blocks - 1 >= c.block#
11 UNION
12 SELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file#
13 , header_block corr_start_block#
14 , header_block corr_end_block#
15 , 1 blocks_corrupted
16 , 'Segment Header' description
17 FROM dba_segments s, v$database_block_corruption c
18 WHERE s.header_file = c.file#
19 AND s.header_block between c.block# and c.block# + c.blocks - 1
20 UNION
21 SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#
22 , greatest(f.block_id, c.block#) corr_start_block#
23 , least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block#
24 , least(f.block_id+f.blocks-1, c.block#+c.blocks-1)
25 - greatest(f.block_id, c.block#) + 1 blocks_corrupted
26 , 'Free Block' description
27 FROM dba_free_space f, v$database_block_corruption c
28 WHERE f.file_id = c.file#
29 AND f.block_id <= c.block# + c.blocks - 1
30 AND f.block_id + f.blocks - 1 >= c.block#
31 ORDER BY file#, corr_start_block#;
no rows selected
rem***********上面沒有記錄,判斷壞塊是游離的,不存在任何的segment上面*************rem
SQL> alter database datafile '/oracle/oradata/orcl/users01.dbf' autoextend off;
SQL> alter database datafile '/oracle/oradata/orcl/users01.dbf' resize 7M;
SQL> show user
USER is "TEST"
SQL> alter user test default tablespace users;
User altered.
SQL> create table bb(a varchar(11));
Table created.
SQL> begin
2 for i in 1..1000 loop
3 insert into bb values('yau');
4 commit ;
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> select tablespace_name from dba_segments where segment_name='BB';
TABLESPACE_NAME
------------------------------
USERS
SQL> begin
2 for i in 1..1000 loop
3 insert into bb values('yau');
4 commit ;
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> /
PL/SQL procedure successfully completed.
SQL> select tablespace_name,file_id,sum(bytes)/1024/1024 size_m from dba_free_space where file_id=4 group by tablespace_name,file_id;
TABLESPACE_NAME FILE_ID SIZE_M
------------------------------ ---------- ----------
USERS 4 1.5625
SQL> begin
for i in 1..100000 loop
2 3 insert into bb values('yau');
4 commit ;
5 end loop;
6 end;
7 /
begin
*
ERROR at line 1:
ORA-01653: unable to extend table TEST.BB by 128 in tablespace USERS
ORA-06512: at line 3
使用dbv檢查資料檔案的情況
[oracle@orahost ~]$ dbv file=/oracle/oradata/orcl/users01.dbf
DBVERIFY: Release 10.2.0.5.0 - Production on Tue May 6 09:25:28 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
DBVERIFY - Verification starting : FILE = /oracle/oradata/orcl/users01.dbf
DBVERIFY - Verification complete
Total Pages Examined : 896
Total Pages Processed (Data) : 677
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 3
Total Pages Failing (Index): 0
Total Pages Processed (Other): 102
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 114
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Highest block SCN : 2897787 (0.2897787)
現在使用rman進行下全庫備份
RMAN> backup database format '/oracle_backup/bak0326_%t_%s';
Starting backup at 06-MAY-08
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/oracle/oradata/orcl/system01.dbf
input datafile fno=00003 name=/oracle/oradata/orcl/sysaux01.dbf
input datafile fno=00002 name=/oracle/oradata/orcl/undotbs01.dbf
input datafile fno=00006 name=/oracle/oradata/orcl/ggusr01.dbf
input datafile fno=00007 name=/oracle/oradata/orcl/testblock01.dbf
input datafile fno=00005 name=/oracle/oradata/orcl/testbak.dbf
input datafile fno=00008 name=/oracle/oradata/orcl/testbak2.dbf
input datafile fno=00004 name=/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: starting piece 1 at 06-MAY-08
channel ORA_DISK_1: finished piece 1 at 06-MAY-08
piece handle=/oracle_backup/bak0326_653995568_172 tag=TAG20080506T092608 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:55
Finished backup at 06-MAY-08
Starting Control File and SPFILE Autobackup at 06-MAY-08
piece handle=/oracle/recovery_dest/ORCL/autobackup/2008_05_06/o1_mf_s_653995683_41zf1775_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 06-MAY-08