至顶网›软件频道 ›Oracle 11g新特性:Rman备份跳过自由区间

Oracle 11g新特性:Rman备份跳过自由区间

扫一扫
分享文章到微信
扫一扫
关注官方公众号
至顶头条

Oracle 11g新特性,使用Rman备份释放自由区间。

作者：盖国强来源：eygle.com 2007年9月10日

在以前的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/*
-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的报告:

$ 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