ITPub博客

首页 > 数据库 > Oracle > 表空间数据文件创建大小与实际使用大小以及rman备份集的关系系列一

表空间数据文件创建大小与实际使用大小以及rman备份集的关系系列一

原创 Oracle 作者:wisdomone1 时间:2015-12-25 18:16:44 0 删除 编辑

结论

1,rman产生的备份集不会是创建数据库的分配大小,而是实际使用的大小
  比如为表空间分配了1G,但实际使用了50M,产生的RMAN备份集则为50M左右
2,表空间的分配大小与实际使用大小不一样,请参考dba_data_files与dba_free_space




测试

SQL> select * from v$version where rownum=1;


BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production


SQL> create tablespace tbs_true datafile '/oracle/oradata/guowang/tbs_true01.dbf' size 1g autoextend off;


Tablespace created.


SQL> select tablespace_name,file_name,bytes/1024/1024/1024 gb,maxbytes/1024/1024/1024 max_gb from dba_data_files where tablespace_name=upper('tbs_true');


TABLESPACE_NAME                FILE_NAME                                                  GB     MAX_GB
------------------------------ -------------------------------------------------- ---------- ----------
TBS_TRUE                       /oracle/oradata/guowang/tbs_true01.dbf                      1          0


SQL> select tablespace_name,bytes/1024/1024/1024 free_gb from dba_free_space where tablespace_name='TBS_TRUE';


TABLESPACE_NAME                   FREE_GB
------------------------------ ----------
TBS_TRUE                       .999023438


SQL> create table t_space(a int,b int) tablespace tbs_true;


Table created.


SQL> insert into t_space select * from t_space;


100000 rows created.


SQL> insert into t_space select * from t_space;


200000 rows created.


SQL> insert into t_space select * from t_space;


400000 rows created.


SQL> insert into t_space select * from t_space;


800000 rows created.


SQL> commit;


Commit complete.


SQL> select tablespace_name,bytes/1024/1024/1024 free_gb from dba_free_space where tablespace_name='TBS_TRUE';


TABLESPACE_NAME                   FREE_GB
------------------------------ ----------
TBS_TRUE                       .973632813




SQL> select (.999023438-.973632813)*1024 as use_mb from dual;


    USE_MB
----------
        26




---可见dba_free_space的前后差值刚好即dba_segments的大小     
SQL> select segment_name,bytes/1024/1024 mb from dba_segments where segment_name=upper('t_space');


SEGMENT_NAME                                               MB
-------------------------------------------------- ----------
T_SPACE                                                    26        




RMAN> backup tablespace tbs_true format '/home/oracle/rman_bak/%u_%p.bak';


Starting backup at 24-DEC-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1152 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=00010 name=/oracle/oradata/guowang/tbs_true01.dbf
channel ORA_DISK_1: starting piece 1 at 24-DEC-15
channel ORA_DISK_1: finished piece 1 at 24-DEC-15
piece handle=/home/oracle/rman_bak/10qpka14_1.bak tag=TAG20151224T121427 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 24-DEC-15


---可见rman备份集产生的大小与表空间实际使用的大小一致
[oracle@seconary ~]$ ll -lh /home/oracle/rman_bak/10qpka14_1.bak 
-rw-r----- 1 oracle oinstall 27M Dec 24 12:14 /home/oracle/rman_bak/10qpka14_1.bak






SQL> select sum(bytes/1024/1024/1024) total_db_gb from dba_segments;


TOTAL_DB_GB
-----------
 4.27056885




SQL> insert into t_space select * from t_space;


1600000 rows created.


SQL> insert into t_space select * from t_space;


3200000 rows created.


SQL> commit;


Commit complete.




SQL> select sum(bytes/1024/1024/1024) total_db_gb from dba_segments;


TOTAL_DB_GB
-----------
 4.35827637


---可见数据增量为
 SQL> select 4.35827637- 4.27056885 from dual;


4.35827637-4.27056885
---------------------
            .08770752




SQL> select (4.35827637- 4.27056885)*1024 add_mb from dual;


    ADD_MB
----------
89.8125005






SQL> select count(*) from dba_high_water_mark_statistics;


  COUNT(*)
----------
        16


---查看最大一些性能统计指标
SQL> select * from dba_high_water_mark_statistics;


      DBID NAME                                VERSION          HIGHWATER LAST_VALUE DESCRIPTION
---------- ----------------------------------- --------------- ---------- ---------- --------------------------------------------------
3254377352 ACTIVE_SESSIONS                     11.2.0.1.0      243.454989  .29868718 Maximum Number of Active Sessions seen in the syst
                                                                                     em


3254377352 CPU_COUNT                           11.2.0.1.0               8          8 Maximum Number of CPUs
3254377352 DATAFILES                           11.2.0.1.0               9          9 Maximum Number of Datafiles
3254377352 DB_SIZE                             11.2.0.1.0      5835587584 5835587584 Maximum Size of the Database (Bytes)
3254377352 EXADATA_DISKS                       11.2.0.1.0                            Number of physical disks
3254377352 INSTANCES                           11.2.0.1.0               1          1 Oracle Database instances
3254377352 PART_INDEXES                        11.2.0.1.0               0          0 Maximum Number of Partitions belonging to an User
                                                                                     Index




      DBID NAME                                VERSION          HIGHWATER LAST_VALUE DESCRIPTION
---------- ----------------------------------- --------------- ---------- ---------- --------------------------------------------------
3254377352 PART_TABLES                         11.2.0.1.0               2          2 Maximum Number of Partitions belonging to an User
                                                                                     Table


3254377352 QUERY_LENGTH                        11.2.0.1.0             926          0 Maximum Query Length
3254377352 SEGMENT_SIZE                        11.2.0.1.0      1214251008 1214251008 Size of Largest Segment (Bytes)
3254377352 SESSIONS                            11.2.0.1.0             159          8 Maximum Number of Concurrent Sessions seen in the
                                                                                     database


3254377352 SQL_NCHAR_COLUMNS                   11.2.0.1.0              31         31 Maximum Number of SQL NCHAR Columns
3254377352 TABLESPACES                         11.2.0.1.0               9          9 Maximum Number of Tablespaces
3254377352 USER_INDEXES                        11.2.0.1.0            3283       3283 Number of User Indexes


      DBID NAME                                VERSION          HIGHWATER LAST_VALUE DESCRIPTION
---------- ----------------------------------- --------------- ---------- ---------- --------------------------------------------------
3254377352 USER_MV                             11.2.0.1.0               1          1 Maximum Number of Materialized Views (User)
3254377352 USER_TABLES                         11.2.0.1.0            1682       1682 Number of User Tables


16 rows selected.




来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9240380/viewspace-1962323/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
提供针对oracle初学者及进阶的数据库培训,欢迎大家咨询: 微信: wisdomone 微信公众号: lovedb qq: 305076427 微博: wisdomone9

注册时间:2008-04-04

  • 博文量
    2149
  • 访问量
    11891684