只读表空间的备份可以采用备份优化的策略来节省开销。在rman中开启优化配置，如果POLICY TO REDUNDANCY是1，那么备份一次只读表空间后，下次在执行数据库备份就会自动排除该表空间，节省资源。

一，只读表空间的优化备份参数

1，新建一个表空间tbs1

create tablespace tbs1 datafile '+DATA' size 5m;

2，将表空间tbs1置为只读read only方式

alter tablespace tbs1 read only;

3，查看表空间tbs1的状态，显示read only，其它正常的表空间为online也就是read write状态。

select TABLESPACE_NAME,STATUS from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          ONLINE
TBS1                           READ ONLY

4，备份数据库，只读表空间tbs1被备份
RMAN> backup database include current controlfile plus archivelog;
...
Starting backup at 2014-10-08 10:02:29
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=00001 name=+DATA/prod/datafile/system.273.860406675
input datafile file number=00002 name=+DATA/prod/datafile/sysaux.262.860406701
input datafile file number=00003 name=+DATA/prod/datafile/undotbs1.263.860406701
input datafile file number=00004 name=+DATA/prod/datafile/users.261.860406675
input datafile file number=00005 name=+DATA/prod/datafile/tbs1.296.860406993
channel ORA_DISK_1: starting piece 1 at 2014-10-08 10:02:30
channel ORA_DISK_1: finished piece 1 at 2014-10-08 10:02:33
piece handle=+FRA/prod/backupset/2014_10_08/nnndf0_tag20141008t100230_0.265.860407351 tag=TAG20141008T100230
...
Finished backup at 2014-10-08 10:02:36

5，开启备份优化参数将不会再备份只读表空间tbs1
RMAN> CONFIGURE BACKUP OPTIMIZATION ON;

6，再次备份数据库时，只读表空间tbs1就不再备份了
RMAN> backup database include current controlfile plus archivelog;
...
Starting backup at 2014-10-08 10:11:26
using channel ORA_DISK_1
skipping datafile 5; already backed up 2 time(s)
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=+DATA/prod/datafile/system.273.860406675
input datafile file number=00002 name=+DATA/prod/datafile/sysaux.262.860406701
input datafile file number=00003 name=+DATA/prod/datafile/undotbs1.263.860406701
input datafile file number=00004 name=+DATA/prod/datafile/users.261.860406675 <==此处已经没有了tbs1
channel ORA_DISK_1: starting piece 1 at 2014-10-08 10:11:26
channel ORA_DISK_1: finished piece 1 at 2014-10-08 10:11:29
piece handle=+FRA/prod/backupset/2014_10_08/nnndf0_tag20141008t101126_0.263.860407887 tag=TAG20141008T101126
...
Finished backup at 2014-10-08 10:11:33

二，恢复只读表空间

情景一，实例被关闭了，并且丢失了只读表空间tbs1，实例无法启动。
这个情景比较简单，只需要restore丢失的数据文件，打开数据库即可。不需要做recover，因为只读表空间不产生日志。

1，查看表空间信息
RMAN> report schema;

Report of database schema for database with db_unique_name PROD

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    740      SYSTEM               ***     +DATA/prod/datafile/system.273.860406675
2    510      SYSAUX               ***     +DATA/prod/datafile/sysaux.262.860406701
3    70       UNDOTBS1             ***     +DATA/prod/datafile/undotbs1.263.860406701
4    5        USERS                ***     +DATA/prod/datafile/users.261.860406675
5    5        TBS1                 ***     +DATA/prod/datafile/tbs1.296.860406993

2,关闭数据库
RMAN> shutdown immediate;

3，删除只读表空间tbs1的数据文件

$ asmcmd

ASMCMD> cd data/prod/datafile

ASMCMD> rm TBS1.296.860406993

ASMCMD> ls -l
Type      Redund Striped Time             Sys Name
DATAFILE UNPROT COARSE   OCT 08 10:00:00 Y    SYSAUX.262.860406701
DATAFILE UNPROT COARSE   OCT 08 10:00:00 Y    SYSTEM.273.860406675
DATAFILE UNPROT COARSE   OCT 08 10:00:00 Y    UNDOTBS1.263.860406701
DATAFILE UNPROT COARSE   OCT 08 10:00:00 Y    USERS.261.860406675

4，启动实例报错，找不到5号数据文件，也即是只读表空间tbs1所在的数据文件。

RMAN> startup;

connected to target database (not started)
Oracle instance started
database mounted
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of startup command at 10/08/2014 10:28:44
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '+DATA/prod/datafile/tbs1.296.860406993'

5，还原5号数据文件
RMAN> restore datafile 5;

Starting restore at 2014-10-08 10:29:03
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=24 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00005 to +DATA/prod/datafile/tbs1.296.860406993
channel ORA_DISK_1: reading from backup piece +FRA/prod/backupset/2014_10_08/nnndf0_tag20141008t100230_0.265.860407351
channel ORA_DISK_1: piece handle=+FRA/prod/backupset/2014_10_08/nnndf0_tag20141008t100230_0.265.860407351 tag=TAG20141008T100230
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 2014-10-08 10:29:05

6，open数据库。不需要recover直接开库。
RMAN> alter database open;

7,验证恢复效果
RMAN> report schema;

Report of database schema for database with db_unique_name PROD

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    740      SYSTEM               ***     +DATA/prod/datafile/system.273.860406675
2    510      SYSAUX               ***     +DATA/prod/datafile/sysaux.262.860406701
3    70       UNDOTBS1             ***     +DATA/prod/datafile/undotbs1.263.860406701
4    5        USERS                ***     +DATA/prod/datafile/users.261.860406675
5    5        TBS1                 ***     +DATA/prod/datafile/tbs1.296.860408945

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    20       TEMP                 32767       +DATA/prod/tempfile/temp.293.860406731

ASMCMD> ls -l
Type      Redund Striped Time             Sys Name
DATAFILE UNPROT COARSE   OCT 08 10:00:00 Y    SYSAUX.262.860406701
DATAFILE UNPROT COARSE   OCT 08 10:00:00 Y    SYSTEM.273.860406675
DATAFILE UNPROT COARSE   OCT 08 10:00:00 Y    TBS1.296.860408945
DATAFILE UNPROT COARSE   OCT 08 10:00:00 Y    UNDOTBS1.263.860406701
DATAFILE UNPROT COARSE   OCT 08 10:00:00 Y    USERS.261.860406675

SYS@PROD >select TABLESPACE_NAME,STATUS from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          ONLINE
TBS1                           READ ONLY

情景二，实例在运行状态，只读表空间tbs1丢失或不可用的恢复。

RMAN> run{
2> sql 'alter database datafile 5 offline';
3> restore datafile 5 force;
4> sql 'alter database datafile 5 online';
5> }

总结来说，只读表空间的特点导致备份与恢复相对简单。只需要还原，不需要恢复就可以完成。如果遇到备份时tbs1是只读状态，后来又被修改成read write状态了。那么对该表空间的恢复就需要recover来追加状态到最新的read write。比如：
run{
sql 'alter database datafile 5 offline';
restore datafile 5;
recover datafile 5;
sql 'alter database datafile 5 online';
}

ITPub博客

备份与恢复十二只读表空间的备份与恢复

最新文章

ITPub博客

备份与恢复 十二 只读表空间的备份与恢复

最新文章

备份与恢复十二只读表空间的备份与恢复