Experiments on free and bad data blocks:

Experiments on free and bad data blocks:

Use dbv to check data files
[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,200 7, Oracle. All rights reserved.


DBVERIFY-Verification starting: FILE =/oracle/oradata/orcl/users01.dbf
Page 310 is marked upt
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 upt
Unzip upt 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 upt
Unzip upt 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 upt: 3
Total Pages Influx: 0
Highest block SCN: 2531497 (0.2531497)


The above records show that the database has bad blocks.


Use rman to identify corrupted objects in the database (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,200 7, 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


Bad block information is recorded in view V $ database_block_partition uption. 11 gb rman will generate a trace file


[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,201 0, Oracle. All Rights Reserved.




Connected:
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 !!!


The following query in rem *** can match the Bad blocks recorded in view v $ database_block_partition uption to the corresponding segments or idle blocks. * *** 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 #) pai_start_block #
3, least (e. block_id + e. blocks-1, c. block # + c. blocks-1) pai_end_block #
4, least (e. block_id + e. blocks-1, c. block # + c. blocks-1)
5-greatest (e. block_id, c. block #) + 1 blocks_upted
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 pai_start_block #
14, header_block pai_end_block #
15, 1 blocks_upted
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 #) pai_start_block #
23, least (f. block_id + f. blocks-1, c. block # + c. blocks-1) pai_end_block #
24, least (f. block_id + f. blocks-1, c. block # + c. blocks-1)
25-greatest (f. block_id, c. block #) + 1 blocks_upted
26, 'free Block' description
27 FROM dba_free_space f, v $ database_block_uption 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 #, pai_start_block #;


No rows selected
There is no record above rem **********, and it is determined that the bad block is free, no segment above *************** rem


SQL> alter database datafile '/oracle/oradata/orcl/users01.dbf' autoextend off;


SQL> alter database datafile '/oracle/oradata/orcl/users01.dbf' resize 7 M;




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 ('yunau ');
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 ('yunau ');
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 ('yunau ');
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


Use dbv to check data files


[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,200 7, 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 success upt: 0
Total Pages Influx: 0
Highest block SCN: 2897787 (0.2897787)


Now use rman for full-Database Backup


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