Oracle数据库搬家牵扯出的一些知识点记录


工作原因,对开发服务器的数据库进行了迁移,实际执行操作之前查了一下迁移oracle数据库的可行方案,最后用了 exp/imp 进行导出导入(这个比较简单),以及附带看了一些表空间相关的知识点(重点哟),下面是一些记录。

一、exp导出整个实例数据

exp ****/****@**** full=y compress=n file=G:\Share\compress_N\****.dmp log=G:\Share\compress_N\exp.log

上述命令是我导出时采用的命令,其中用户名,密码及实例名应根据需求自己修改。

full 参数代表导出的是整个实例的数据,若只想导出特定用户的相关数据,可通过owner参数指定。

compress参数默认为Y, 这里并不是压缩dmp文件的意思,而是代表对该表空间下的文件碎片进行整理(数据存放的时候可能会在磁盘产生的碎片文件),同时很重要的一点是指定为Y时,表的尺寸会定义为该表当前实际占用的空间尺寸。即:或许你曾经在某个表空间内存放了一千万的数据,使得文件占用了10G或者更大的空间。后来因为某些原因删除了数据,而且也没有对表空间进行压缩整理,此时使用exp导出并指定compress为y,在使用imp导入至新的实例后,你会发现,即使表空间内并未存储任何大数据,但是新生成的表空间文件仍然占用了10个G或者更大的空间。

grant参数是导出授权相关的信息,默认为Y,此处便省略了,但是这一点还是要知道的。

关于exp的其他参数,可以使用 exp -help 自行查阅,也可阅读 ColinJames--Oracle数据库exp和imp方式导数据 进行查看。

二、imp导入的相关操作

1、创建一个数据库实例,根据情况选择数据库存放位置,为了方便管理,建议不要使用默认的存放位置。

2、如果导出时使用了dba权限的用户,那么在导出时也应该使用具有dba权限的用户。所以,根据导出时的用户是否为数据库自带用户,是否具有dba权限判断是否需要创建用户并赋予DBA权限。如果导出时使用的是数据库自建用户,例如system,sys,而且也不想修改数据库文件所在的位置,请跳过步骤3、步骤4、步骤5创建表空间和用户的操作。

3、在新数据库下创建与原实例相同的表空间。(导入后补充:根据导入时的日志看,直接使用imp应该也可以导入,但是生成的表空间文件应该是与原来的存放位置相同,笔者未进行直接导入的测试,请自行测试。如果不想将表空间文件与原数据库的目录一致,还是需要创建好以后再导入)

​ 创建时可使用下面的命令在原数据库下执行,批量生成sql语句。

--推荐使用本条语句
--创建所有已存在的表空间
-- tsds 意指 tablespace definition statement
--v$tablespace是一个内置视图,可以查询本实例下的所有表空间,其余与上方类似。
select 'create tablespace ' || space ||
       ' Datafile "数据文件存放路径' || space ||
       '.dbf" size 20M autoextend on next 20M maxsize unlimited extent management local;' as tsds
  from (select name as space from v$tablespace where name not in('USERS','SYSTEM','SYSAUX','TEMP'));


--创建所有用户的默认表空间
--dba_users 内存放着用户及表空间的对应关系,产生的语句会创建所有用户的默认表空间,数据文件的初始大小及扩展容量可自行修改,另外生成的sql语句内需要自行指定文件存放位置并将 “ 替换为 ‘ 才可正常运行。(一般文本处理软件都可以批量替换)
select 'create tablespace ' || space ||
       ' Datafile "数据文件存放路径' || space ||
       '.dbf" size 20M autoextend on next 20M maxsize unlimited extent management local;' as tsds
  from (select distinct default_tablespace as space from dba_users u where u.default_tablespace not in('USERS','SYSTEM','SYSAUX','TEMP') );

--鉴于有些人建用户时忘记调整默认表空间,建表时却指定了某个表空间,所以最好使用第一条语句

4、创建用户并指定默认表空间。

​ 同样提供了如下生成sql的语句,需要在原数据库下执行

-- 此处需要自行指定用户密码
select 'create user ' || username ||
       ' identified by **** account unlock default tablespace ' ||
       spacename || ' ;' as userds
  from (select username as username, default_tablespace as spacename
          from dba_users         
         where username not in ('ANONYMOUS',
                                'CTXSYS',
                                'DBSNMP',
                                'DIP',
                                'DMSYS',
                                'EXFSYS',
                                'MDDATA',
                                'MDSYS',
                                'MGMT_VIEW',
                                'OLAPSYS',
                                'ORDPLUGINS',
                                'ORDSYS',
                                'OUTLN',
                                'SCOTT',
                                'SI_INFORMTN_SCHEMA',
                                'SYS',
                                'SYSMAN',
                                'SYSTEM',
                                'TSMSYS',
                                'WMSYS',
                                'XDB')
                                order by username );                             

5、登录新的数据库,执行生成的 tsds 和 user_ds 语句,注意执行顺序,先创建表空间,在创建用户。

6、使用imp执行导入。

imp ****/****@**** full=y file=G:\Share\compress_N\****.dmp log=G:\Share\compress_N\imp.log 

如果没有提前建立用户,则用户密码与原库相同。

imp有一个ignore的参数,代表忽略创建错误,默认为N,此处并未开启。在导入过程中会出现诸多表空间及用户的创建错误,此类错误可忽略。

其余参数及参数含义可在命令行使用 imp -help自行查阅。

至此,imp导入就算结束了。

三、数据库表空间物理文件缩小

会有这个小节是因为第一次导出的时候不知道加入 compress = n 的参数,导致只有很少数据量的一个库占用了30多G的磁盘空间,为了减少占用,看了一些跟压缩表空间相关的内容,在此做些记录。

*1. 总结提前声明

  • 有一些概念还没说到,如果你不懂什么意思,可以先了解一下,看完其他的内容以后再回来看第二遍。
  • 并不是物理文件过大就需要将其缩小,我这边进行处理是因为这只是个开发库备份,另外我对这个库也足够清楚,知道这个表空间文件不正常,而且也不会再向这个表空间内写入数据。
  • 在使用EXP 导出时指定 compress参数,可以有效的减小物理文件的大小。采用默认值导出导入后物理文件是30多G,设置参数后是15G多一些,减少了一半。至于为什么还有这么多,在我查看段信息后发现了几个异常的表和索引,这几个异常对象初始时分配了最高4G的空间,是主要祸首。以我自己的操作过程及现在的理解来看,如果没有那几个初始值异常大的表定义和索引定义,导出的结果是符合我的预期的。
  • 以缩小表空间为关键字搜索,很容易就会找到一些文章告诉你要先使用shrink space压缩段,然后通过 resize 命令缩小表空间的物理文件,不过在我实测后发现,这种方法的适用范围很苛刻,它要求你所操作的段数据刚好位于表空间的末尾,即你所操作的数据段刚好占据着已使用的最大块,此时对段进行压缩操作,然后resize,表空间文件才可以缩小。
  • 举例来说,假设一个表空间内有AB两张表,每十万数据占用10M磁盘空间,我们分两种情况来看。第一种情况,先向A表内写入100W数据,然后删除A表内的40W数据,占用空间少了40M,这时执行shrink spaceresize 60M,物理文件确实会按预期缩小。第二种情况,先向A表写入了100W数据,又向B表写入了10W数据,最后将A表内的数据删除了90W,此时对A段Shrink,对表空间Resize,然后就会触发 ORA-03297:文件包含在请求的RESIZE值以外使用的数据,这里面会涉及到数据块的概念,因为B表内写入的数据占用了表空间内更靠前的数据块,没办法调整物理文件大小,而且实际情况下,数据的写入是无法预期的,所以此方法无用。
  • Shrink Space做了什么?需要说明一下,这是在Oracle 10g中新增的功能,用来优化数据段的高水位(HWM)问题,高水位会导致查询时扫描的数据块过多,影响查询时的速度,所以需要优化。关于高水位的内容,可查看 arctic_fox的文章 - oracle 高水位线详解,另外需要注意的是shrink segment的操作会改变数据的rowid,也就是改变了数据的物理位置,该命令会自动重建索引,但是会导致已打开的游标失效,如果要在生产环境使用,必须要慎重。
  • EXP/IMP,这应该是最简单的方法了,也是我采用的方法。在对表空间内需要整理的段进行收缩整理后,查询dba_free_space可以看到处于未使用状态的区间编号,如果这个表空间不会再次插入新数据,可以指定当前用户重新导出,再次导入后,可以发现物理文件已经缩小到了自己可接受的程度。
  • move tablesapce 的方法只是看了看,未进行实操验证,如果您无法或不方便执行数据的导出导入。建议点击一泽涟漪 - Oracle收缩表空间查看原文了解相关内容,还有这篇菜鸟程序员 - ORACLE修改表空间方法

2、一些预备知识

  • oracle内置一些表和视图,以user_开头的可以查询当前用户拥有的所有对象,以all_开头的可以访问当前用户拥有访问权限的对象(可以是其他用户的对象),以dba_开头的需要dba权限,可以访问数据库内的所有对象。

  • 表空间的存储结构在逻辑上的数据结构如下:

    tablespace(表空间) - segment(段) - extent(区) - block(块)

    数据存储在Block数据块中,数据块对应在物理磁盘上;一个或多个连续的数据块组成区,区不能跨段,一个区只属于一个段;所以,区也只是一个逻辑上的概念,区与实际存储数据的块关联,段信息的汇总展示会更简单明了。

    段是由区组成,段中会存在一个初始区,用于存放数据,空间不够时会自动分配新的区,实际上就是分配了新的数据块存放数据,区号是按顺序排列的,块可以优先使用当前未分配的空间(可以在dba_free_space中查看)。

    表空间则是段的容器,一般oracle会为表或索引创建一个段,用于存放表或索引的数据,称为表段或索引段,每个分区表也是一个独立的段,关于段的具体类型,可以在dba_segments中查看 segment_type字段的标注。

    另外,在数据库安装时会有一个界面显示系统的块大小,默认为8K,也可以通过 select value from v$parameter where name='db_block_size' 自行查询数据块的默认值。

    关于表空间结构更详细的描述,请自行查看 oracle 物理结构(表空间,段区块)_tyhawk的博客-CSDN博客

  • 几个内置对象

    dba_data_files 可以在这里查看表空间的一些数据,比如物理文件位置,表空间大小,是否可用,是否自动扩展,扩展大小等信息,file_id可以在这里取(file_id)

    dba_segments 可以查看段的信息,比如段的所有者,段的类型,段的名字等

    dba_extents 和上面类似,可以查看所属段的信息,分区ID(extents_id)以及块ID(block_id,区的起始块),另外查询的时候最好指定file_id,不然会很慢。

v$datafile 可以查看偏向物理文件的一些信息,file_id可以在这里取(file#)

dba_free_space 可以查看当前表空间文件的未使用区间,如果查询到了过多的结果,其实也是当前表空间内碎片过多

  • 几个查询语句
--查询表空间及其物理文件位置
select t1.name,t2.name 
from v$tablespace t1,v$datafile t2
where t1.ts# = t2.ts#;

--查询数据库的 block_size
select value from v$parameter where name='db_block_size';

--查看指定表空间内指定段的分区信息
select * from dba_extents t where t.FILE_ID = 00 and t.segment_name = 'XXX';

  • 整理段的语句
--需要先打开行移动,否则有 ORA-10636 ROW MoVEMENT is not enabled的报错
alter table history.TB_FT_BALANCE enable row movement;   

alter table history.TB_FT_BALANCE shrink space;   
  --shrink segment的操作会改变数据的rowid
  --另外看到有人说执行shrink space时之前的游标会失效,生产上还是要慎重一点
alter table history.TB_FT_BALANCE deallocate unused;
 
alter table history.TB_FT_BALANCE disable row movement;

关于 Row Movement,可以看这里:Enmotech - 深入解析 Row Movement 的原理和性能影响与关联

上面说了,oracle一般会为表分配一个段,所以可以只操作曾经有过大量数据后来又被删除的表,压缩表段其实也就是整理表碎片

  • 改变表空间物理文件大小的语句如下
alter database datafile '/u01/test01/t11.dbf' resize 5m;  
--参数自行修改,另外如果指定的空间大小无法存放已有的数据,此处会报错

--至于具体应该指定的数值可使用如下语句获取
  --获取表空间文件编号
 select file#,name from v$datafile;  --此处取出表空间文件的file#编号
 --通过最大块确定指定文件占用的空间
  select (max(block_id) + blocks)*8/1024 from dba_extents where file_id={$file#}; --单位为 M,块大小采用默认值 8K。
  -- alter时resize的参数必须要大过查询结果
  

如果表空间已经被占用过,即使将表数据删除,上面的语句查询出来的结果也并不会差太多,因为被占用的空间并没有被释放。

3、个人操作

需要声明的是在进行此处的操作时,我并不知道加入 compress 参数的做法,此时的物理文件也有30多G。

先使用下方语句查看了一下占用空间较大的segment

--指定file编号,获取当前文件内的段信息
select t.owner,              
       t.segment_name,
       t.partition_name,   --分区名,一个段可能有多个分区
       t.segment_type,     --段类型
       t.tablespace_name,
       t.BYTES/(1024*1024) as MB, --段大小,单位是MB
       t.BYTES,            --段的大小,单位是 byte
       t.initial_extent    --初始化时分配的大小
  from dba_segments t
 where t.relative_fno = {$file#}
 order by t.BYTES desc;

找到了一个占用达1G,但实际没有数据的的表段,使用如下语句进行表段的整理:

alter table history.XXXX enable row movement;   
alter table history.XXXX shrink space;     --shrink segment的操作会改变数据的rowid,使已打开的游标失效
alter table history.XXXX deallocate unused;
alter table history.XXXX disable row movement;

操作后可再次执行段信息查询语句,会发现该段占用空间明显缩小。

发现上述操作确实可以使得表段占用空间减少后,我并没有去怀疑网上直接resize文件的做法是否可行,反而是在查看了dba_segements 的查询结果后,发现需要执行该套操作的表段实在有点多,出于偷懒的想法,才重新进行百度,并找到了加入 compress 参数的建议。在实操验证后,发现物理文件缩小到了15G,只是这个大小仍然远远超过我的承受范围,所以我还是准备采用上面说到的方法进行操作。

在一张几十万数据的测试表内删除了数据后,对该表段执行了压缩操作,此时,有些文章就会告诉你可以进行alter database datafile 'xxx' resize 0m 的操作,利用sql查询到当前实际占用的空间后,实操进行验证,过称中触发了ORA-03297的错误,当时猜测是因为释放出来的空间仍以碎片形式存在,数据库系统并不会将数据依次前移去填充空白碎片区间,于是自己分两种情况进行了验证,得出了这种方法并不通用的结论,验证时区分的两种情况以及对 shrink space 的说明请查看本节开篇的总结了解。

在明确知道压缩段无法满足我的需求后,整理得到的碎片空间会如何利用又引起了我的兴趣,于是在另外一个表空间内我对两张表分别写入了几百万数据,然后使用delete删除了一部分数据并整理得到了一部分未使用的碎片空间,查询dba_free_space后,得到未使用的block_id范围。在之前的两张表AB以及一个未整理过的C表内插入数据,发现碎片的block会按照使用者的顺序进行分配,也就代表在A表内整理得到的空间并不局限于只能A使用,其他的 B,C表也可以分配空白区域的block,其实到这里,我才明白了Extent存在的意义,段所代表的上层无需知道实际存储数据的地址,只需要知道区号即可,根据extent_id再去查找实际使用的block.

到这之后,无奈又得重新找方法,然后找到了 一泽涟漪 - Oracle收缩表空间这篇文章,博主的方法其实是将数据移到了新的表空间,然后将其指定为原用户的默认表空间。看过之后,因为担心 lob 字段的数据会不会丢失,也不知道当前表空间下的SP,Function会怎么样,所以我并没有按照这种方法操作。如果想使用move的方法,建议同时阅读一下这篇文章菜鸟程序员 - ORACLE修改表空间方法,这里面有对 lob的一些说明。

进展到这其实陷入了一种僵局,直到我无意中看到dba_segmentsinitial_extent字段,发现几个对象的初始值过大。于是使用工具 rebuild 了这几张表,重建之后查询段信息发现多了几条段名称乱码的数据,该工具重建时没有释放表占用的空间,如果读者有重建表的需求,建议先保留表相关的定义及表数据,然后删除重建。

delete、drop、truncate的区别可查看weixin_33871366 - oracle中delete drop truncate的用法和区别

对于未被释放的段,查找无果后,决定重新对该用户执行 exp/imp 的操作,操作后惊喜的发现,新的表空间内已经不存在那几个乱码的段信息,而且物理文件也缩小到了我可以接受的程度,至此,缩小表空间的物理文件终于是找到了一个合适的方法。在此之后也想到了这和move tablespace其实本质上是一样的。

4、Move Tablespace

本节是对上面提到的两篇文章的一些记录,move tablespace的方法我并没有测试,另外我感觉move更有用的点应该是可以移动表到新的表空间(建表时搞错表空间的经历,不会只有我有吧 >_<)

  • 移动数据段至新的表空间时,对表和索引的是一种处理方式,对lob字段是另一种处理方式

  • 系统会为 lob 字段分配一个segment 用于存放数据,关于两种处理方式可以看获取更多的说明。

    上方两条说明是为了下面的操作步骤做准备,下面开始使用move。

  • 新建一个表空间

    create tablespace TS_New Datafile 'E:\TS_New.dbf' 
    size 200M autoextend on next 100M maxsize unlimited extent management local;
    
  • 生成 table 的move语句

    select 'alter table ' || owner || '.' || segment_name || ' move  tablespace TS_New;' sqltext from dba_segments where  tablespace_name = 'TS_HISTORY' and segment_type='TABLE';
    
  • 生成 index 的move语句

     select 'alter index ' || owner || '.' || segment_name || ' rebuild tablespace TS_New;' sqltext from dba_segments where tablespace_name = 'TS_HISTORY' and segment_type='INDEX';
    
  • 生成 lob段的 move语句

    select 'alter table ' || owner || '.' || table_name || ' move lob(' || column_name || ') store as(tablespace TS_New);' sqltext from dba_lobs where tablespace_name = 'TS_HISTORY';
    

    lob段数据的迁移建议查看菜鸟程序员 - ORACLE修改表空间方法,里面对语句及参数介绍的比较明白,而且还考虑了表,分区表的情况,这里我就不搬过来了。

5、回顾一下

耗费了N多N多的时间,我得到了什么呢?最重要的其实倒不是缩小物理文件的方法,而是对表有了更深的理解。以前是在数据存放容器的层次看待数据库中的表,我只知道数据放进去了,用的时候来拿就好。现在倒是了解了数据表的一些更细节的知识点。Segment,Extent,Block这三个概念以及相关的几个系统视图,这就是收获。

与此相关的,也意外的了解了高水位,段整理,转移表空间的一些知识。

最后当然是本次问题的解决者,exp命令中的compress参数,若是第一次搜索资料时就找到了这个参数,或许我也就懒得看其他的内容了,说到底,我只是条懒狗。

本站声明:网站内容来源于网络,如有侵权,请联系我们,我们将及时处理。

  • 分享:
评论
还没有评论
    发表评论 说点什么