合 Oracle只读数据文件备份和恢复
简介
只读数据文件是只读表空间的数据文件,其数据块包括文件头在内不允许更改(少数管理性命令除外)。
将表空间设置为只读状态的命令:
SQL> alter tablespace TBS_READ read only;
Tablespace altered.
将表空间重新设置为常规的读/写状态的命令:
SQL> alter tablespace TBS_READ read write;
Tablespace altered.
获得只读表空间及其数据文件的sql语句:
SQL> set line 9999
SQL> col file_name format a50
SQL> select t.TABLESPACE_NAME, d.FILE_ID, d.FILE_NAME
2 from dba_tablespaces t, dba_data_files d
3 WHERE t.TABLESPACE_NAME = d.TABLESPACE_NAME
4 and t.STATUS = 'READ ONLY' ;
TABLESPACE_NAME FILE_ID FILE_NAME
----------------------- ---------- --------------------------------------------------
TBS_READ 5 /u01/app/oracle/oradata/utf8test/tbs_read01.dbf
SQL>
只读表空间的特性
使用只读表空间避免对静态数据的频繁备份
当使用alter tablespace tbs read only时,数据文件会执行检查点进程(将所有脏缓冲区的内容写至磁盘),当前的SCN号会被标注,同时存储了SCN的数据文件头部被冻结.控制文件内也会记录该数据文件的冻结信息。
可以清除只读表空间的对象
只读文件损坏的后果
当一个表空间从读/写状态更改为只读状态时,其数据文件中的脏数据块必须由DBWn进程悉数写回磁盘,完成一次不完整的完全检查点,该表空间内数据文件即称为只读数据文件,其数据块及文件头信息包括检查点在内从此均不再更新,每次打开数据库实例也不会在乎只读文件头的检查点SCN是否和其他数据文件的活在线日志同步,但各种错误(ORA-01116,ORA-01110,ORA-01578,ORA-01157等等错误)仍然会发生。以下图片为从电子书上截取过来的:
不像其他类型的数据文件,在只读文件头损坏后,在发生检查点时,所有进程视其为无物,实例不会崩溃(关键数据文件头损坏的后果),文件也不会自动下线(普通数据文件头损坏的后果),总体上只读文件安然无恙,只是当执行需要访问头部的操作时才在告警日志和追踪文件中留下痕迹而已,比如:
SQL> select checkpoint_change# from v$datafile where file#=5;
CHECKPOINT_CHANGE#
------------------
1865187
但告警日志报错:
ORA-19563: datafile header validation failed for file /u01/app/oracle/oradata/utf8test/tbs_read01.dbf
ORA-01251: Unknown File Header Version read for file number 5
ORA-01578: ORACLE data block corrupted (file # 5, block # 130)
ORA-01110: data file 5: '/u01/app/oracle/oradata/utf8test/tbs_read01.dbf'
此刻其内部的所有队形还是可以查询的(只要对应的数据块没有损坏),但是 alter tablspace ... read write 和 alter tablespace ... offline 之后的online回报ora-01210数据文件头损坏错误。
只读表空间的备份
由于只读数据文件内没有一个数据块能够被修改,所以,一般情况下,只读表空间只需要进行一次备份,尤其是当只读数据文件占用很大空间的时候,这样做可以节省备份数据库的时间。即当表空间状态发生改变时应立即进行备份。可以使用OS系统cp命令来备份或RMAN进行备份只读表空间。备份其他类型数据文件的方式均适用于只读数据文件,比如:
- backup as backupset tablespace TBS_READ;
- backup as copy tablespace TBS_READ;
- backup as backupset datafile 5;
使用RMAN时建议启用备份优化选项,具体保留几份备份由备份保留策略决定:使用冗余度时保留数量为冗余度加1,使用恢复窗口时保留数量为1.
RMAN> CONFIGURE BACKUP OPTIMIZATION ON;
以下例子演示备份优化功能,初始状态下是没有任何备份的:
首先启用备份优化:
[oracle@rhel6_lhr dbs]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Tue Feb 3 10:02:05 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: lilove (DBID=888888)
RMAN> CONFIGURE BACKUP OPTIMIZATION ON;
using target database control file instead of recovery catalog
new RMAN configuration parameters:
CONFIGURE BACKUP OPTIMIZATION ON;
new RMAN configuration parameters are successfully stored
设置备份保留策略,使用恢复窗口3天:
RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 3 DAYS;
new RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 3 DAYS;
new RMAN configuration parameters are successfully stored
RMAN> show retention policy;
RMAN configuration parameters for database with db_unique_name LILOVE are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 3 DAYS;
使用backup database 命令备份数据库内所有的数据文件,注意有tbs_read01.dbf文件:
RMAN> backup database;
Starting backup at 2015-02-03 10:03:31
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=264 device type=DISK
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=/u01/app/oracle/oradata/utf8test/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/utf8test/sysaux01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/utf8test/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/utf8test/users01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/utf8test/tbs_read01.dbf
channel ORA_DISK_1: starting piece 1 at 2015-02-03 10:03:32
channel ORA_DISK_1: finished piece 1 at 2015-02-03 10:05:18
piece handle=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/1opuba7k_1_1 tag=TAG20150203T100332 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:46
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 2015-02-03 10:05:20
channel ORA_DISK_1: finished piece 1 at 2015-02-03 10:05:21
piece handle=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/1ppubaau_1_1 tag=TAG20150203T100332 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2015-02-03 10:05:21
RMAN>
当第二次执行backup database命令时,输出中是找不到tbs_read01.dbf文件的,rman认为没有必要反复备份只读文件:
RMAN> backup database;
Starting backup at 2015-02-03 10:08:48
using channel ORA_DISK_1
skipping datafile 5; already backed up 1 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=/u01/app/oracle/oradata/utf8test/system01.dbf