--======================
-- 只读表空间的备份与恢复
--======================

一、只读表空间的特性
   使用只读表空间避免对静态数据的频繁备份
   当使用alter tablespace tbs read only时,数据文件会执行检查点进程(将所有脏缓冲区的内容写至磁盘)，
       当前的SCN号会被标注,同时存储了SCN的数据文件头部被冻结.控制文件内也会记录该数据文件的冻结信息。
   可以清除只读表空间的对象

二、只读表空间的备份
   一般情况下，只读表空间只需要进行一次备份，即当表空间状态发生改变时应立即进行备份
   可以使用OS系统cp命令来备份或RMAN进行备份只读表空间
   使用RMAN时建议启用备份优化选项
       RMAN> CONFIGURE BACKUP OPTIMIZATION ON;

   只读表空间不支持热备
   SQL> alter tablespace tbs1 begin backup;
   alter tablespace tbs1 begin backup
   *
   ERROR at line 1:
   ORA-01642: begin backup not needed for read only tablespace 'TBS1'

三、只读表空间的还原与恢复
   还原与恢复只读表空间的问题在于控制文件如何控制只读表空间，分为下列三种情况：
   --------- --------------- ---------------- -------------------------------------
   case           backup 1     crash status                   recovery
   --------- --------------- ---------------- -------------------------------------
   case 1        Read-Only        Read-Only      将备份的只读表空间复制到目的地(Restore)
   case 2        Read-Only        Read-Write     先Restore backup1,后recover(applied log )
   case 3        Read-Write       Read-only     先Restore backup1,后recover(applied log )

   只读表空间恢复时需要考虑的问题
       重建一个控制文件时
       重命名数据文件时
       使用一个备份的控制文件时

   下面对表空间tbs1置为只读后对比前后生成的重建控制文件的脚本

[sql] view plain copy

print?

SQL> alter database backup controlfile to trace as '/tmp/rectl1.sql';



SQL> alter tablespace tbs1 read only;



SQL> alter database backup controlfile to trace as '/tmp/rectl2.sql';



SQL> ho diff /tmp/rectl1.sql /tmp/rectl2.sql

69,70c69

<   '/u01/app/oracle/oradata/orcl/example01.dbf',

<   '/u01/app/oracle/oradata/orcl/tbs01.dbf'

---

>   '/u01/app/oracle/oradata/orcl/example01.dbf'

97a97,102

> -- Files in read-only tablespaces are now named.

> ALTER DATABASE RENAME FILE 'MISSING00006'

>   TO '/u01/app/oracle/oradata/orcl/tbs01.dbf';

>

> -- Online the files in read-only tablespaces.

> ALTER TABLESPACE "TBS1" ONLINE;

    对比两者
       1.使用create controlfile命令时，datafile中未列出只读表空间的数据文件
       2.成功创建控制文件并打开后，使用alter database rename file命令重命名只读表空间的数据文件
       3.使用alter tablespace readonly_tablespacename online 将只读表空间联机

四、演示只读表空间变化的恢复过程
   1. 演示整个过程为只读表空间的情况(对应前面描述的case 1)

[sql] view plain copy

print?

SQL> create table scott.tb1 tablespace tbs1

  2  as select * from scott.emp;



SQL> commit;



SQL> alter tablespace tbs1 read only;



SQL> select file#,name,enabled from v$datafile where file#=6;



     FILE# NAME                                          ENABLED

---------- --------------------------------------------- ----------

                 6 /u01/app/oracle/oradata/orcl/tbs01.dbf       READ ONLY



SQL> ho cp /u01/app/oracle/oradata/orcl/tbs01.dbf /tmp/tbs01.dbf



SQL> insert into scott.tb1(empno,ename) values(3333,'Thomas');



SQL> update scott.tb1 set sal=sal*1.2 where ename='SCOTT';



SQL> delete from scott.tb1 where ename='SCOTT';

--执行上述三条命令，收到下列同样的错误提示

ORA-00372: file 6 cannot be modified at this time

ORA-01110: data file 6: '/u01/app/oracle/oradata/orcl/tbs01.dbf'

        从上面的演示可以看出对只读表空间内的数据作任何DML操作均不可用
       在 Oracle 表空间与数据文件   一文中，可以对只读表空间作delete操作(版本是10.2.0.1.0)，应该是数
       据库补丁的问题,   此版本为10.2.0.4.0。

[sql] view plain copy

print?

--使用vim 打开?/oradata/orcl/tbs01.dbf文件做任意操作来模拟破坏该只读表空间的数据文件

--重启数据库后收到下面的错误提示

ORA-01157: cannot identify/lock data file 6 - see DBWR trace file

ORA-01110: data file 6: '/u01/app/oracle/oradata/orcl/tbs01.dbf'



SQL> ho cp /tmp/tbs01.dbf /u01/app/oracle/oradata/orcl/tbs01.dbf



SQL> alter database open;



SQL> select count(1) from scott.tb1;



  COUNT(1)

----------

        16

2. 演示由只读变为读写后发生损坏且只有只读备份的恢复情况(对应前面描述的case 2)

[sql] view plain copy

print?

SQL> alter tablespace tbs1 read write;



SQL> insert into scott.tb1(empno,ename) values(3333,'Thomas');



SQL> commit;



--使用vim 打开/u01/app/oracle/oradata/orcl/tbs01.dbf文件做任意操作来模拟破坏该读写表空间的数据文件

--重启数据库后未收到错误提示



SQL> insert into scott.tb1(empno,ename) values(4444,'Jackson');

insert into scott.tb1(empno,ename) values(4444,'Jackson')

                  *

ERROR at line 1:

ORA-00376: file 6 cannot be read at this time

ORA-01110: data file 6: '/u01/app/oracle/oradata/orcl/tbs01.dbf'



SQL> select * from v$recover_file;



     FILE# ONLINE  ONLINE_ ERROR               CHANGE# TIME

---------- ------- ------- ---------------- ---------- ---------

         6 OFFLINE OFFLINE FILE NOT FOUND            0



SQL> ho cp /tmp/tbs01.dbf /u01/app/oracle/oradata/orcl/tbs01.dbf



SQL> recover datafile 6;

Media recovery complete.



SQL> alter tablespace tbs1 online;



Tablespace altered.

SQL> select * from scott.tb1 where ename='Thomas';



     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

      3333 Thomas

3. 演示由读写表空间变为只读表空间，且仅有读写表空间备份的恢复(对应前面描述的case 3)

[sql] view plain copy

print?

SQL> select file#,name,enabled from v$datafile where file#=6;



     FILE# NAME                                          ENABLED

---------- --------------------------------------------- ----------

         6 /u01/app/oracle/oradata/orcl/tbs01.dbf        READ WRITE



SQL> alter tablespace tbs1 begin backup;



SQL> ho cp /u01/app/oracle/oradata/orcl/tbs01.dbf /tmp/tbs01.dbf



SQL> alter tablespace tbs1 end backup;



SQL> delete from scott.tb1 where empno=3333;



SQL> commit;



SQL> alter tablespace tbs1 read only;



--使用vim 打开?/oradata/orcl/tbs01.dbf文件做任意操作来模拟破坏该只读表空间的数据文件

--重启数据库后收到下面的错误提示



ORA-01157: cannot identify/lock data file 6 - see DBWR trace file

ORA-01110: data file 6: '/u01/app/oracle/oradata/orcl/tbs01.dbf'



SQL> ho cp /tmp/tbs01.dbf /u01/app/oracle/oradata/orcl/tbs01.dbf



SQL> recover datafile 6;



SQL> alter database open;



SQL> select * from scott.tb1 where ename='Thomas';



no rows selected

4. 演示数据文件状态发生多次变化且在变化时没有任何备份的恢复处理(实际上使用日志来重新构造该数据文件)

[sql] view plain copy

print?

SQL> select file#,name,enabled from v$datafile where file#=6;



     FILE# NAME                                          ENABLED

---------- --------------------------------------------- ----------

         6 /u01/app/oracle/oradata/orcl/tbs01.dbf        READ WRITE



SQL> drop table scott.tb1;



SQL> commit;



SQL> alter tablespace tbs1 read only;



SQL> alter tablespace tbs1 read write;



SQL> create table scott.tb2 tablespace tbs1 as select * from scott.emp;



SQL> commit;



SQL> alter system checkpoint;



[oracle@oradb orcl]$tail -n 50 $ORACLE_BASE/admin/orcl/bdump/alert_orcl.log

Errors in file /u01/app/oracle/admin/orcl/bdump/orcl_ckpt_4064.trc:

ORA-01171: datafile 6 going offline due to error advancing checkpoint

ORA-01122: database file 6 failed verification check

ORA-01110: data file 6: '/u01/app/oracle/oradata/orcl/tbs01.dbf'

ORA-01251: Unknown File Header Version read for file number 6



SQL> select * from v$recover_file;



     FILE# ONLINE  ONLINE_ ERROR              CHANGE# TIME

---------- ------- ------- --------------- ---------- ---------

         6 OFFLINE OFFLINE FILE NOT FOUND           0



SQL> select file#,name,status from v$datafile where file#=6;



     FILE# NAME                                          STATUS

---------- --------------------------------------------- -------

         6 /u01/app/oracle/oradata/orcl/tbs01.dbf        RECOVER



SQL> alter database create datafile 6;



SQL> recover datafile 6;

Media recovery complete.



SQL> alter database datafile 6 online;



SQL> select count(1) from scott.tb2;



  COUNT(1)

----------

        16

5. 演示删除只读表空间上的对象

[sql] view plain copy

print?

SQL> select file#,name,enabled from v$datafile where file#=6;



     FILE# NAME                                          ENABLED

---------- --------------------------------------------- ----------

         6 /u01/app/oracle/oradata/orcl/tbs01.dbf       READ ONLY



SQL> select segment_name,segment_type,tablespace_name,owner from dba_segments where

  2  tablespace_name='TBS1' and segment_name='TB2';



SEGMENT_NAME         SEGMENT_TYPE       TABLESPACE_NAME                OWNER

-------------------- ------------------ ------------------------------ ----------

TB2                  TABLE              TBS1                           SCOTT



SQL> drop table scott.tb2;



Table dropped.

ITPub博客

只读表空间的备份与恢复

最新文章