只读表空间[转]-CSDN博客

顾名思义，只读表空间就是空间只可读，不可写，这一特性对备有有很大的好处，尤其是对数据仓库这种历史数据很少变化，而数据规模又非常庞大的场景。
下面通过一些例子来说明只读表空间如何影响备份与恢复。

1. 创建表空间及表
SQL> create tablespace tbs datafile 'E:ORACLEPRODUCT10.2.0ORADATAtbs01.dbf' size 100m;

Tablespace created

SQL> create table c2 nologging tablespace tbs as select * from dba_objects;

Table created

SQL> alter system checkpoint;

System altered

SQL> alter tablespace tbs read only;

2. 备份只读表空间

RMAN> backup tablespace tbs;

Starting backup at 25-NOV-10
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=00010 name=E:ORACLEPRODUCT10.2.0ORADATATBS01.DBF
channel ORA_DISK_1: starting piece 1 at 25-NOV-10
channel ORA_DISK_1: finished piece 1 at 25-NOV-10
piece handle=E:ORACLEPRODUCT10.2.0DB_1DATABASEOLTU4E7_1_1 tag=TAG20101125
T232247 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:04
Finished backup at 25-NOV-10

3. 对其他表空间上的表执行一些DML操作
SQL> delete from c2;

delete from c2

ORA-00372: 此时无法修改文件 10
ORA-01110: 数据文件 10: 'E:ORACLEPRODUCT10.2.0ORADATATBS01.DBF'

SQL> insert into pnologging select * from plogging where rownum<100;

99 rows inserted

SQL> commit;

Commit complete

SQL> alter system switch logfile;

System altered

SQL> alter system switch logfile;

4. 备份数据库
--注意：用skip readonly跳过只读表空间备份。不加这个关键词的话会连只读表空间一起备份。

RMAN> backup database skip readonly;

Starting backup at 25-NOV-10
using channel ORA_DISK_1
skipping read-only file 10
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=E:ORACLEPRODUCT10.2.0ORADATAORA10GSYSTEM01.D
BF
input datafile fno=00002 name=E:ORACLEPRODUCT10.2.0ORADATAORA10GUNDOTBS01.
DBF
input datafile fno=00003 name=E:ORACLEPRODUCT10.2.0ORADATAORA10GSYSAUX01.D
BF
input datafile fno=00004 name=E:ORACLEPRODUCT10.2.0ORADATAORA10GUSERS01.DB
F
input datafile fno=00005 name=E:ORACLEPRODUCT10.2.0ORADATAORA10GTS_STREAM0
1.DBF
input datafile fno=00006 name=E:ORACLEPRODUCT10.2.0ORADATAORA10GADMIN.DBF
input datafile fno=00007 name=E:ORACLEPRODUCT10.2.0ORADATASUK.DBF
input datafile fno=00008 name=E:ORACLEPRODUCT10.2.0ORADATAORA10GADMINISTRA
TOR_IDX01.DBF
input datafile fno=00009 name=E:ORACLEPRODUCT10.2.0ORADATAORA10GTEST.DBF
channel ORA_DISK_1: starting piece 1 at 25-NOV-10
......

5. 继续对其他表执行DML
SQL> delete from plogging;

21963 rows deleted

SQL> commit;

Commit complete

6. 模拟数据库故障，恢复数据库

删除所有数据文件，然后尝试恢复。
RMAN> restore database;

RMAN> restore database;

Starting restore at 25-NOV-10
using channel ORA_DISK_1

datafile 10 not processed because file is read-only
skipping datafile 7; already restored to file E:ORACLEPRODUCT10.2.0ORADATASUK.DBF
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to E:ORACLEPRODUCT10.2.0ORADATAORA10GSYSTEM01.DBF
restoring datafile 00002 to E:ORACLEPRODUCT10.2.0ORADATAORA10GUNDOTBS01.DBF
restoring datafile 00003 to E:ORACLEPRODUCT10.2.0ORADATAORA10GSYSAUX01.DBF
restoring datafile 00004 to E:ORACLEPRODUCT10.2.0ORADATAORA10GUSERS01.DBF
restoring datafile 00005 to E:ORACLEPRODUCT10.2.0ORADATAORA10GTS_STREAM01.DBF
restoring datafile 00006 to E:ORACLEPRODUCT10.2.0ORADATAORA10GADMIN.DBF
restoring datafile 00008 to E:ORACLEPRODUCT10.2.0ORADATAORA10GADMINISTRATOR_IDX01.DBF
restoring datafile 00009 to E:ORACLEPRODUCT10.2.0ORADATAORA10GTEST.DBF
channel ORA_DISK_1: reading from backup piece E:ORACLEPRODUCT10.2.0DB_1DATABASEPLTU4F7_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=E:ORACLEPRODUCT10.2.0DB_1DATABASEPLTU4F7_1_1 tag=TAG20101125T232317
channel ORA_DISK_1: restore complete, elapsed time: 00:01:36
Finished restore at 25-NOV-10

注意：从上面可以看到数据文件10因为是只读的，没有被恢复，此时需要单独恢复这个文件：
RMAN> restore tablespace tbs;

Starting restore at 25-NOV-10
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00010 to E:ORACLEPRODUCT10.2.0ORADATATBS01.DBF
channel ORA_DISK_1: reading from backup piece E:ORACLEPRODUCT10.2.0DB_1DATABASEOLTU4E7_1_1

RMAN> recover database;

Starting recover at 25-NOV-10
using channel ORA_DISK_1
datafile 10 not processed because file is read-only

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 25-NOV-10

因为数据文件10(tbs)是只读的，且从上次备份后这个数据文件没有发生修改，所以recover进程不需要恢复它。

7. 验证数据

SQL> select count(1) from c2;

COUNT(1)
----------
10463

SQL> select count(1) from pnologging;

COUNT(1)
----------
999

SQL> select count(1) from plogging;

COUNT(1)
----------
0

可以看到，数据一切正常。

从上面实验可以得出结论：

1. 把表空间设成只读后，该表中间中的数据无法修改。
2. 只读表空间只需要备份一次，在大型数据仓库中可以大量地减少备份数据量，提高备份效率。如果数据库崩溃，需要全库恢复，只要原来磁盘上的只读表空间文件没有损坏，就可以跳过只读表空间的恢复，提高恢复效率。
3. 只读表空间对其他表空间的数据没有任何影响。

在使用只读表空间时，有几点需要注意：

1. 如果有事物在用户发出'alter tablespace xxx read only'前开始，且没有提交，不管这些事物是否与这个表空间有关，'alter xxx read only'会被阻塞直到那些事物提交或回滚。
--可以用以SQL找出阻塞read only的会话，必要时可kill这些会话。
SELECT S.SID, S.SERIAL#
FROM (SELECT SES_ADDR, START_SCNB, S.SADDR, MIN(START_SCNB) KEEP(DENSE_RANK FIRST ORDER BY DECODE(SADDR, NULL, 2, 1), START_SCNB) OVER() MIN_SCN
FROM V$TRANSACTION T,
(SELECT S.SADDR
FROM V$SQLAREA SQ, V$SESSION S
WHERE SQ.SQL_ID = S.SQL_ID
AND LOWER(SQL_TEXT) LIKE 'alter tablespace%') S
WHERE T.SES_ADDR = S.SADDR(+)) B, V$SESSION S
WHERE B.SADDR IS NULL
AND START_SCNB < MIN_SCN
AND B.SES_ADDR = S.SADDR;
上述SQL原理很简单，就是找出'alter tablespace read only'的SCN，并找出活动事物中，开始SCN比该SCN还要小的会话。

2. 只读表空间只需备份一次，日常数据库备份可通过skip readonly选项来跳过对只读表空间的备份。
3. 在做全库恢复时(restore database)，默认是不恢复只读表空间的；如果需要restore 只读表空间，可用restore tablespace命令实现。
4. 一个好的习惯是，无论何时把表空间设为只读，要立刻备份该表空间。
5. 如果需要对只读表空间设置成read write模式，再修改数据，重新置为read only后要立刻备份该表空间，以免因为疏忽导致原来只读表空间因为时间过长，缺少归档日志而无法恢复。
6. 如果表(如分区表)的一部分数据在只读表空间中，一部分不在只读表空间中，只有在只读表空间的数据不能被修改，其余数据可以正常修改