ITPub博客

首页 > 数据库 > 数据库开发技术 > 只读表空间

只读表空间

原创 数据库开发技术 作者:space6212 时间:2019-06-15 10:33:05 0 删除 编辑

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

[@more@]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. 如果表(如分区表)的一部分数据在只读表空间中,一部分不在只读表空间中,只有在只读表空间的数据不能被修改,其余数据可以正常修改。



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

请登录后发表评论 登录
全部评论

注册时间:2005-01-25

  • 博文量
    189
  • 访问量
    142156