顧名思義，只讀表空間就是空間只可讀，不可寫，這一特性對備有有很大的好處，尤其是對資料倉儲這種歷史資料很少變化，而資料規模又非常龐大的場景。
下面透過一些例子來說明只讀表空間如何影響備份與恢復。

[@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. 如果表(如分割槽表)的一部分資料在只讀表空間中，一部分不在只讀表空間中，只有在只讀表空間的資料不能被修改，其餘資料可以正常修改。

只讀表空間

相關文章