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

最新推荐文章于 2023-04-10 17:46:55 发布

clg10051

最新推荐文章于 2023-04-10 17:46:55 发布

阅读量438

点赞数

文章标签：数据库

结论

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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9240380/viewspace-1962323/