顾名思义,只读表空间就是空间只可读,不可写,这一特性对备有有很大的好处,尤其是对数据仓库这种历史数据很少变化,而数据规模又非常庞大的场景。
下面通过一些例子来说明只读表空间如何影响备份与恢复。
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. 如果表(如分区表)的一部分数据在只读表空间中,一部分不在只读表空间中,只有在只读表空间的数据不能被修改,其余数据可以正常修改
source;http://blog.itpub.net/post/12157/509405
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7583803/viewspace-717835/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/7583803/viewspace-717835/