September 7, 2007
Oracle 11g新特性:Rman备份跳过自由区间
作者:eygle
出处:http://blog.eygle.com
在以前的Oracle版本中，如果一个Segment分配的空间被格式化，即使后来释放了这个空间，RMAN的备份仍然要备份这个空间.看一下Oracle10g中的测试：
SQL> create tablespace eygle datafile '/opt/oracle/oradata/test97/eygle01.dbf' size 50M;
Tablespace created.
SQL> alter user eygle default tablespace eygle;
User altered.
SQL> connect eygle/eygle
Connected.
SQL> create table eygle as select * from dba_objects;
Table created.
SQL> insert into eygle select * from dba_objects;
9969 rows created.
SQL> /
9969 rows created.
SQL> /
9969 rows created.
SQL> /
9969 rows created.
SQL> commit;
Commit complete.
SQL> col segment_name for a30
SQL> select segment_name,bytes/1024/1024 from dba_segments where segment_name='EYGLE';
SEGMENT_NAME BYTES/1024/1024
------------------------------ ---------------
EYGLE 5
这个对象占用的区间情况：
SQL> select segment_name,block_id,blocks from dba_extents where segment_name='EYGLE';
SEGMENT_NAME BLOCK_ID BLOCKS
------------------------------ ---------- ----------
EYGLE 9 8
EYGLE 17 8
EYGLE 25 8
EYGLE 33 8
EYGLE 41 8
EYGLE 49 8
EYGLE 57 8
EYGLE 65 8
EYGLE 73 8
EYGLE 81 8
EYGLE 89 8
SEGMENT_NAME BLOCK_ID BLOCKS
------------------------------ ---------- ----------
EYGLE 97 8
EYGLE 105 8
EYGLE 113 8
EYGLE 121 8
EYGLE 129 8
EYGLE 137 128
EYGLE 265 128
EYGLE 393 128
EYGLE 521 128
20 rows selected.
我们备份一下这个数据文件：
RMAN> backup datafile 9 format '/opt/oracle/obak/%U';
Starting backup at 07-SEP-07
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=139 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00009 name=/opt/oracle/oradata/test97/eygle01.dbf
channel ORA_DISK_1: starting piece 1 at 07-SEP-07
channel ORA_DISK_1: finished piece 1 at 07-SEP-07
piece handle=/opt/oracle/obak/01irbtb1_1_1 tag=TAG20070907T165703 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 07-SEP-07
RMAN> exit
这个备份文件大约有5M左右：
$ ls -l obak/*
-rw-r----- 1 oracle dba 5341184 Sep 7 16:57 obak/01irbtb1_1_1
我们进行一下Move操作：
SQL> alter table eygle move tablespace eygle;
Table altered.
该测试表的存储空间此时发生了变化：
SQL> select segment_name,block_id,blocks from dba_extents where segment_name='EYGLE';
SEGMENT_NAME BLOCK_ID BLOCKS
------------------------------ ---------- ----------
EYGLE 649 8
EYGLE 657 8
EYGLE 665 8
EYGLE 673 8
EYGLE 681 8
EYGLE 689 8
EYGLE 697 8
EYGLE 705 8
EYGLE 713 8
EYGLE 721 8
EYGLE 729 8
SEGMENT_NAME BLOCK_ID BLOCKS
------------------------------ ---------- ----------
EYGLE 737 8
EYGLE 745 8
EYGLE 753 8
EYGLE 761 8
EYGLE 769 8
EYGLE 777 128
EYGLE 905 128
EYGLE 1033 128
EYGLE 1161 128
EYGLE 1289 128
21 rows selected.
再作一次备份：
$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Fri Sep 7 16:57:59 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: TEST97 (DBID=765011863)
RMAN> backup datafile 9 format '/opt/oracle/obak/%U';
Starting backup at 07-SEP-07
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=159 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00009 name=/opt/oracle/oradata/test97/eygle01.dbf
channel ORA_DISK_1: starting piece 1 at 07-SEP-07
channel ORA_DISK_1: finished piece 1 at 07-SEP-07
piece handle=/opt/oracle/obak/02irbtd5_1_1 tag=TAG20070907T165813 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:08
Finished backup at 07-SEP-07
此时的备份集大小变为10M，也就是说Move之前和之后的空间都被RMAN备份了下来：
$ ls -l obak/*再来看一下RMAN的报告:
-rw-r----- 1 oracle dba 5341184 Sep 7 16:57 obak/01irbtb1_1_1
-rw-r----- 1 oracle dba 10608640 Sep 7 16:58 obak/02irbtd5_1_1
$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Fri Sep 7 17:32:26 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: TEST97 (DBID=765011863)
RMAN> list backup of tablespace eygle;
using target database control file instead of recovery catalog
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1 Full 5.09M DISK 00:00:07 07-SEP-07
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20070907T165703
Piece Name: /opt/oracle/obak/01irbtb1_1_1
List of Datafiles in backup set 1
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
9 Full 3167484 07-SEP-07 /opt/oracle/oradata/test97/eygle01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2 Full 10.11M DISK 00:00:07 07-SEP-07
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20070907T165813
Piece Name: /opt/oracle/obak/02irbtd5_1_1
List of Datafiles in backup set 2
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
9 Full 3167631 07-SEP-07 /opt/oracle/oradata/test97/eygle01.dbf
这显然不是我们期望的结果，如果释放的空间能够跳过，那将是一个理想的状态。
我们看看Oracle11g中Oracle的行为。
首先创建测试用户和测试表：
[oracle@test126 obak]$ sqlplus "/ as sysdba"
SQL*Plus: Release 11.1.0.6.0 - Production on Fri Sep 7 16:43:27 2007
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> create user eygle identified by eygle default tablespace eygle;
User created.
SQL> grant connect,resource,dba to eygle;
Grant succeeded.
SQL> connect eygle/eygle
Connected.
SQL> create table eygle as select * from dba_objects;
Table created.
SQL> insert into eygle select * from dba_objects;
12063 rows created.
SQL> insert into eygle select * from dba_objects;
12063 rows created.
SQL> insert into eygle select * from dba_objects;
12063 rows created.
SQL> insert into eygle select * from dba_objects;
12063 rows created.
SQL> commit;
Commit complete.
当前空间使用大约7M：
SQL> col segment_name for a30
SQL> select segment_name,bytes/1024/1024 from dba_segments where segment_name='EYGLE';
SEGMENT_NAME BYTES/1024/1024
------------------------------ ---------------
EYGLE 7
SQL> select segment_name,block_id,blocks from dba_extents where segment_name='EYGLE';
SEGMENT_NAME BLOCK_ID BLOCKS
------------------------------ ---------- ----------
EYGLE 9 8
EYGLE 17 8
EYGLE 25 8
EYGLE 33 8
EYGLE 41 8
EYGLE 49 8
EYGLE 57 8
EYGLE 65 8
EYGLE 73 8
EYGLE 81 8
EYGLE 89 8
SEGMENT_NAME BLOCK_ID BLOCKS
------------------------------ ---------- ----------
EYGLE 97 8
EYGLE 105 8
EYGLE 113 8
EYGLE 121 8
EYGLE 129 8
EYGLE 137 128
EYGLE 265 128
EYGLE 393 128
EYGLE 521 128
EYGLE 649 128
EYGLE 777 128
22 rows selected.
此时进行一次备份，备份大约占用了7M空间：
RMAN> backup datafile 5 format '/opt/oracle/obak/%U';
Starting backup at 07-SEP-07
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00005 name=/opt/oracle/oradata/wapdbs/eygle01.dbf
channel ORA_DISK_1: starting piece 1 at 07-SEP-07
channel ORA_DISK_1: finished piece 1 at 07-SEP-07
piece handle=/opt/oracle/obak/03irbsmv_1_1 tag=TAG20070907T164623 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 07-SEP-07
RMAN> exit
Recovery Manager complete.
[oracle@test126 obak]$ ll
total 6928
-rw-r----- 1 oracle dba 6955008 Sep 7 16:46 03irbsmv_1_1
对数据表进行Move操作：
SQL> alter table eygle move tablespace eygle;
Table altered.
SQL> select segment_name,block_id,blocks from dba_extents where segment_name='EYGLE';
SEGMENT_NAME BLOCK_ID BLOCKS
------------------------------ ---------- ----------
EYGLE 905 8
EYGLE 913 8
EYGLE 921 8
EYGLE 929 8
EYGLE 937 8
EYGLE 945 8
EYGLE 953 8
EYGLE 961 8
EYGLE 969 8
EYGLE 977 8
EYGLE 985 8
SEGMENT_NAME BLOCK_ID BLOCKS
------------------------------ ---------- ----------
EYGLE 993 8
EYGLE 1001 8
EYGLE 1009 8
EYGLE 1017 8
EYGLE 1025 8
EYGLE 1033 128
EYGLE 1161 128
EYGLE 1289 128
EYGLE 1417 128
EYGLE 1545 128
EYGLE 1673 128
22 rows selected.
再进行RMAN备份：
[oracle@test126 obak]$ rman target /
Recovery Manager: Release 11.1.0.6.0 - Production on Fri Sep 7 16:48:07 2007
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: WAPDBS (DBID=2306709702)
RMAN> backup datafile 5 format '/opt/oracle/obak/%U';
Starting backup at 07-SEP-07
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=139 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=00005 name=/opt/oracle/oradata/wapdbs/eygle01.dbf
channel ORA_DISK_1: starting piece 1 at 07-SEP-07
channel ORA_DISK_1: finished piece 1 at 07-SEP-07
piece handle=/opt/oracle/obak/04irbsqo_1_1 tag=TAG20070907T164823 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 07-SEP-07
RMAN> exit
Recovery Manager complete.
我们注意到新的备份大约占用了7M空间，和之前的备份大致相同：
[oracle@test126 obak]$ ll
total 13544
-rw-r----- 1 oracle dba 6955008 Sep 7 16:46 03irbsmv_1_1
-rw-r----- 1 oracle dba 6881280 Sep 7 16:48 04irbsqo_1_1
列举一下RMAN的备份集：
RMAN> list backup of tablespace eygle;
using target database control file instead of recovery catalog
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1 Full 104.00K DISK 00:00:01 07-SEP-07
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20070907T163650
Piece Name: /opt/oracle/obak/01irbs52_1_1
List of Datafiles in backup set 1
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
5 Full 216444 07-SEP-07 /opt/oracle/oradata/wapdbs/eygle01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2 Full 6.63M DISK 00:00:01 07-SEP-07
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20070907T164623
Piece Name: /opt/oracle/obak/03irbsmv_1_1
List of Datafiles in backup set 2
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
5 Full 216842 07-SEP-07 /opt/oracle/oradata/wapdbs/eygle01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3 Full 6.55M DISK 00:00:00 07-SEP-07
BP Key: 3 Status: AVAILABLE Compressed: NO Tag: TAG20070907T164823
Piece Name: /opt/oracle/obak/04irbsqo_1_1
List of Datafiles in backup set 3
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
5 Full 217011 07-SEP-07 /opt/oracle/oradata/wapdbs/eygle01.dbf
在Oracle11g中，Oracle能够真正的跳过哪些Free的空间，从而使得备份集大大缩小。
-The End-
Posted by eygle at 4:52 PM | Comments (8)
关于Shared Pool探索的补充之一
作者:eygle
出处:http://blog.eygle.com
在以前的一篇文章中，我介绍过x$ksmsp的一些知识。在x$ksmsp.ksmchcls的分类中，还有两外几个分类，这几个分类是R-free/R-freea/R-perm:
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production
SQL> select KSMCHCLS,sum(KSMCHSIZ) from x$ksmsp
2 group by ksmchcls;
KSMCHCLS SUM(KSMCHSIZ)
-------- -------------
R-free 24836016
R-freea 1946032
R-perm 1904160
free 32459264
freeabl 294999744
perm 70090848
recr 144187104
7 rows selected.
这里的R指的是reserved,也就是说这些Heap的信息是和shared_pool_reserved_size相关的。
至于free/freeable/perm就无需解释了。
shared_pool_reserved_size的另外一个视图展现是V$SHARED_POOL_RESERVED.
我们可以从这个视图中获得关于shared pool reserverd的信息：
SQL> select * from V$SHARED_POOL_RESERVED;
FREE_SPACE AVG_FREE_SIZE FREE_COUNT MAX_FREE_SIZE USED_SPACE AVG_USED_SIZE
---------- ------------- ---------- ------------- ---------- -------------
USED_COUNT MAX_USED_SIZE REQUESTS REQUEST_MISSES LAST_MISS_SIZE MAX_MISS_SIZE
---------- ------------- ---------- -------------- -------------- -------------
REQUEST_FAILURES LAST_FAILURE_SIZE ABORTED_REQUEST_THRESHOLD ABORTED_REQUESTS
---------------- ----------------- ------------------------- ----------------
LAST_ABORTED_SIZE
-----------------
24998976 99597.5139 109 843632 3684512 14679.3307
142 347560 2817185 0 0 0
0 0 2147483647 0
0
关于这些信息的另外一个来源是X$KSMSPR内部表：
SQL> select ksmchcom,ksmchcls,sum(ksmchsiz)
2 from x$ksmspr group by ksmchcom,ksmchcls;
KSMCHCOM KSMCHCLS SUM(KSMCHSIZ)
---------------- -------- -------------
ksfqpar R-freea 328616
free memory R-free 25216256
PL/SQL MPCODE R-freea 81128
PL/SQL SOURCE R-freea 4640
obj htab chunk R-freea 82176
character set o R-freea 61592
permanent memor R-perm 1904160
reserved stoppe R-freea 2720
session param v R-freea 1004920
9 rows selected.
这部分信息和x$ksmsp视图记录的Reserved信息是完全一致的：
SQL> select KSMCHCOM,KSMCHCLS,sum(KSMCHSIZ)
2 from x$ksmsp where KSMCHCLS like 'R%' group by ksmchcom,ksmchcls;
KSMCHCOM KSMCHCLS SUM(KSMCHSIZ)
---------------- -------- -------------
ksfqpar R-freea 328616
free memory R-free 24591576
PL/SQL MPCODE R-freea 81128
PL/SQL SOURCE R-freea 4640
obj htab chunk R-freea 82176
character set o R-freea 61592
permanent memor R-perm 1904160
reserved stoppe R-freea 2720
session param v R-freea 1629600
9 rows selected.
-The End-
Posted by eygle at 10:58 AM | Comments (0)