只读表空间的备份与恢复
对比两者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;
从上面的演示可以看出对只读表空间内的数据作任何DML操作均不可用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'
2. 演示由只读变为读写后发生损坏且只有只读备份的恢复情况(对应前面描述的case 2)--使用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
3. 演示由读写表空间变为只读表空间,且仅有读写表空间备份的恢复(对应前面描述的case 3)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
4. 演示数据文件状态发生多次变化且在变化时没有任何备份的恢复处理(实际上使用日志来重新构造该数据文件)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
5. 演示删除只读表空间上的对象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
五、总结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.
六、 快捷参考
有关性能优化请参考
有关ORACLE体系结构请参考
Oracle联机重做日志文件(ONLINE LOG FILE)
Oracle实例和Oracle数据库(Oracle体系结构)
有关闪回特性请参考
Oracle闪回特性(FLASHBACK DATABASE)
Oracle闪回特性(FLASHBACK DROP & RECYCLEBIN)
Oracle闪回特性(Flashback Query、FlashbackTable)
Oracle闪回特性(Flashback Version、Flashback Transaction)
有关基于用户管理的备份和备份恢复的概念请参考
Oracle基于用户管理恢复的处理(详细描述了介质恢复及其处理)
有关RMAN的备份恢复与管理请参考
RMAN 备份路径困惑(使用plus archivelog时)
有关ORACLE故障请参考
对参数FAST_START_MTTR_TARGET= 0 的误解及设定
有关ASM请参考
有关SQL/PLSQL请参考
SQL 基础--> 集合运算(UNION与UNION ALL)
SQL 基础--> 层次化查询(STARTBY ... CONNECT BY PRIOR)
SQL 基础--> ROLLUP与CUBE运算符实现数据汇总
有关ORACLE其它特性
使用OEM,SQL*Plus,iSQL*Plus 管理Oracle实例
日志记录模式(LOGGING、FORCE LOGGING 、NOLOGGING)
使用外部表管理Oracle 告警日志(ALAERT_$SID.LOG)
簇表及簇表管理(Index clustered tables)
ORACLE_SID、DB_NAME、INSTANCE_NAME、DB_DOMIAN、GLOBAL_NAME
Oracle补丁全集 (Oracle 9i 10g 11g Path)
网友评论