sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 故障案例 >

【案例】Oracle resize数据文件时报错ORA-03297原因和官方解决办法

时间:2016-11-01 21:54   来源:Oracle研究中心   作者:HTZ   点击:

天萃荷净 Oracle研究中心案例分析:运维DBA反映Oracle数据库在resize数据文件时报错ORA-03297,结合MOS官方文章分析。
resize数据文件的时候,常常会报ORA-03297错误,原来理解通过在相同表空间move一下对象,就会自动使用最小的未使用的空间,其实并不是这样的,在MOS文件中遇到ORA-03297错误也是直接给出的DROP对象,也不是在相同表空间中MOVE对象。

下面的测试没有实际意义,因为生产环境基本上不会做RESIZE操作。

1,数据库版本

oracleplus.net> select * from v$version where rownum=1;
BANNER
——————————————————————————–
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production

2,查询表空间中对象的大小

这里可以看到对象总的大小为271974400
oracleplus.net> select tablespace_name,sum(bytes) from dba_segments where tablespace_name=’USERS’ group by tablespace_name;
TABLESPACE_NAME SUM(BYTES)
—————————— ———-
USERS 271974400

然后表表空间中总分配的空间远远大于271974400
oracleplus.net> select tablespace_name,sum(bytes) total,sum(user_bytes) used from dba_data_files where tablespace_name=’USERS’ group by tablespace_name;
TABLESPACE_NAME TOTAL USED
—————- ———— ————————-
USERS 30703616000 30701518848

3 查询需要MOVE的对象

查看表空间中的数据文件系统
oracleplus.net> select file_id,file_name from dba_data_files where tablespace_name=’USERS’;
FILE_ID FILE_NAME
———- ——————————————————————————–
4 /oracle/app/oracle/oradata/orcl1123/users01.dbf
5 /oracle/app/oracle/oradata/orcl1123/users02.dbf

原来以为表空间中使用数据文件是均匀的使用,但是后面的测试并不是这样的,下面这样的SQL是之前以后均匀的使用数据文件而写的
oracleplus.net> select 271974400/2+128 from dual;
271974400/2+128
—————
135987328
由于查询dba_extents需要消耗大量的时间,特别是数据库越大的时候,所以这里我们通过一次性查询出来,并生成一个表来实现只查询一次dba_extents。
oracleplus.net> select *
2 from (select owner,
3 segment_name,
4 a.segment_type,
5 max(block_id + blocks) * 8192 max_size
6 from dba_extents a
7 where tablespace_name = ‘USERS’
8 group by owner, segment_name, a.segment_type)
9 where max_size > 135987328;

OWNER SEGMENT_NAME SEGMENT_TYPE MAX_SIZE
——————– ———————————– ————— ———-
HTZ SYS_LOB0000075782C00003$$ LOBSEGMENT 8798797824
HTZ HTZ4 TABLE 8798601216
HTZ SYS_IL0000075782C00003$$ LOBINDEX 8797552640
HTZ HTZ3 TABLE 8796504064
HTZ TEST TABLE 8801746944
HTZ SYS_IL0000075778C00002$$ LOBINDEX 8796700672
HTZ SYS_LOB0000075778C00002$$ LOBSEGMENT 8796635136
HTZ TEST_BFILE TABLE 8797421568

oracleplus.net> create table system.resize_datafile as select *
from (select owner,
segment_name,
a.segment_type,
max(block_id + blocks) * 8192 max_size
from dba_extents a
where tablespace_name = ‘USERS’
group by owner, segment_name, a.segment_type)
where max_size > 135987328;

Table created.

4 MOVE对象

oracleplus.net> select ‘alter table ‘ || owner || ‘.’ || segment_name || ‘ move;’
from system.resize_datafile a
where a.segment_type = ‘TABLE’;

‘ALTERTABLE’||OWNER||’.’||SEGMENT_NAME||’MOVE;’
————————————————
alter table HTZ.HTZ4 move;
alter table HTZ.HTZ3 move;
alter table HTZ.TEST move;
alter table HTZ.HTZ_LONG move;
alter table HTZ.TEST_BFILE move;

select ‘alter table ‘ || a.owner || ‘.’ || b.table_name || ‘ move lob(‘ ||
b.column_name || ‘) store as ‘ ||
decode(b.securefile, ‘NO’, ‘BASICFILE’, ‘YES’, ‘securefile’) ||
‘ (tablespace ‘ || b.tablespace_name || ‘);’ ddl
from system.resize_datafile a, dba_lobs b
where a.segment_type = ‘LOBSEGMENT’
AND a.owner = b.owner
and a.segment_name = b.segment_name;

alter table HTZ.TEST move lob(PHOTO) store as securefile (tablespace USERS);
alter table HTZ.HTZ_LONG move lob(SQL_FULLTEXT) store as BASICFILE (tablespace USERS);
alter table HTZ.HTZ4 move lob(SQL_FULLTEXT) store as securefile (tablespace USERS);

多次MOVE后,查看当前表空间使用情况
oracleplus.net> select max(block_id) from dba_extents where file_id=4;
MAX(BLOCK_ID)
————-
1074144
oracleplus.net> select max(block_id) from dba_extents where file_id=5;
MAX(BLOCK_ID)
————-
16896
这里看到两个数据文件中分配给对象的最大块的BLOCK_ID相差太多了,也说明数据文件中并不是平均使用数据文件的

oracleplus.net> select max(block_id+blocks)*8192+128*8192 from dba_extents where file_id=5;
MAX(BLOCK_ID+BLOCKS)*8192+128*8192
———————————-
147849216
oracleplus.net> select max(block_id+blocks)*8192+128*8192 from dba_extents where file_id=4;
MAX(BLOCK_ID+BLOCKS)*8192+128*8192
———————————-
8800501760
oracleplus.net> select tablespace_name,sum(bytes) from dba_segments where tablespace_name=’USERS’ group by tablespace_name;

TABLESPACE_NAME SUM(BYTES)
—————————— ———-
USERS 270073856
这里看到表空间分配的大小还是远远大于表空间中对象使用的大小

5 查询对表块的分布情况

oracleplus.net> select owner,segment_name,file_id,block_id+blocks from dba_extents where tablespace_name=’USERS’ order by file_id,4;

OWNER SEGMENT_NAME FILE_ID BLOCK_ID+BLOCKS
———- —————————— ———- —————
SCOTT DEPT 4 136
SCOTT PK_DEPT 4 144
SCOTT EMP 4 152
………………..
HTZ HTZ_4 4 376
HTZ TEST 4 384
HTZ HTZ4 4 1016064
…………………….
HTZ SYS_IL0000075818C00002$$ 4 1016152
HTZ SYS_LOB0000075782C00003$$ 5 5120
HTZ HTZ3 5 18048
HTZ HTZ3 5 18176
HTZ HTZ3 5 18304
HTZ SYS_LOB0000075782C00003$$ 5 19328
……………….
通过上面信息得到,中间其它有很多块并没有使用,并且相隔还很大

6 dump数据文件位图块

File Space Bitmap Block:
BitMap Control:
RelFno: 4, BeginBlock: 128, Flag: 0, First: 23, Free: 63456
FFFFFFFF00000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
………………….
File Space Bitmap Block:
BitMap Control:
RelFno: 4, BeginBlock: 508032, Flag: 0, First: 0, Free: 63488
0000000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
………………………..
File Space Bitmap Block:
BitMap Control:
RelFno: 4, BeginBlock: 1015936, Flag: 0, First: 46, Free: 59945
FFFFFFFFFF3FFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF
FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF
FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF
FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF
FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF
FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF
FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF
FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF
FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF
FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF
FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF
FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF
FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF FFFFFFFFFFFFFFFF
通过上面的信息可以得到每一个位图块都对应数据文件中一段连续的空间,在分配空间的时候,并不是依次使用位图块,而是同时在多个位图块中使用位图块,估计是为了减少块的挣用,提高并发性。

7 MOVE到其它表空间

其实通过MOS文档可以发现,ORACLE官方并没有说通过MOVE方式,而是通过DROP方式来实现,下面我们通过MOVE到其它的表空间来使用。
alter table HTZ.HTZ5 move tablespace sysaux;
alter table HTZ.HTZ3 move tablespace sysaux;
alter table HTZ.TEST move tablespace sysaux;
alter table HTZ.NL1 move tablespace sysaux;
alter table HTZ.HTZ4 move tablespace sysaux;
alter table HTZ.HTZ_LONG1 move tablespace sysaux;
alter table HTZ.NL2 move tablespace sysaux;
alter table HTZ.TEST move lob(PHOTO) store as securefile (tablespace SYSAUX);
alter table HTZ.HTZ_LONG1 move lob(SQL_FULLTEXT) store as BASICFILE (tablespace SYSAUX);
alter table HTZ.HTZ4 move lob(SQL_FULLTEXT) store as securefile (tablespace SYSAUX);

8 RESIZE数据文件

查询数据分配给对象的最大块的位置
oracleplus.net> select max(block_id+blocks)*8192+128*8192 from dba_extents where file_id=5;
MAX(BLOCK_ID+BLOCKS)*8192+128*8192
———————————-
8126464
oracleplus.net> select max(block_id+blocks)*8192+128*8192 from dba_extents where file_id=4;
MAX(BLOCK_ID+BLOCKS)*8192+128*8192
———————————-
4128768
这里看到已经除下面了。
oracleplus.net> alter database datafile 5 resize 8126464;
Database altered.
oracleplus.net> alter database datafile 4 resize 4128768;
Database altered.
已经resize成功

9 将对象MOVE回来

alter table HTZ.HTZ5 move tablespace USERS;
alter table HTZ.HTZ3 move tablespace USERS;
alter table HTZ.TEST move tablespace USERS;
alter table HTZ.NL1 move tablespace USERS;
alter table HTZ.HTZ4 move tablespace USERS;
alter table HTZ.HTZ_LONG1 move tablespace USERS;
alter table HTZ.NL2 move tablespace USERS;
alter table HTZ.TEST move lob(PHOTO) store as securefile (tablespace USERS);
alter table HTZ.HTZ_LONG1 move lob(SQL_FULLTEXT) store as BASICFILE (tablespace USERS);
alter table HTZ.HTZ4 move lob(SQL_FULLTEXT) store as securefile (tablespace USERS);

查看当前数据文件分配的大小
oracleplus.net> select max(block_id+blocks)*8192+128*8192 from dba_extents where file_id=4;
MAX(BLOCK_ID+BLOCKS)*8192+128*8192
———————————-
139460608
oracleplus.net> select max(block_id+blocks)*8192+128*8192 from dba_extents where file_id=5;
MAX(BLOCK_ID+BLOCKS)*8192+128*8192
———————————-
147849216http://www.oracleplus.net

oracleplus.net> !ls -l /oracle/app/oracle/oradata/orcl1123/users02.dbf
-rw-r—– 1 oracle oinstall 153034752 Nov 29 23:02 /oracle/app/oracle/oradata/orcl1123/users02.dbf

oracleplus.net> !ls -l /oracle/app/oracle/oradata/orcl1123/users01.dbf
-rw-r—– 1 oracle oinstall 141762560 Nov 29 23:02 /oracle/app/oracle/oradata/orcl1123/users01.dbf

本文固定链接: http://www.htz.pw/2014/11/29/%e5%85%b3%e4%ba%8eresize-datafile%e7%90%86%e8%a7%a3%e7%9a%84%e9%94%99%e8%af%af.html | 认真就输

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【案例】Oracle resize数据文件时报错ORA-03297原因和官方解决办法

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1139.html

Oracle研究中心

关键词:

如何理解Oracle resize datafile工作原理

Oracle报错ORA-03297原因和解决办法