恢复只读表空间
情况
备份时表空间的状态
要恢复时表空间的状态
恢复
1
只读
只读
将备份的数据文件恢复到目的地
2
只读
读写
先还原至备份状态，再应用日志恢复
3
读写
只读
先还原至备份状态，再应用日志恢复
情况1：只读-只读
1.准备环境，创建测试表空间TS_LOB
SYS@ORA11GR2>create tablespace ts_lob datafile '/u01/app/oracle/oradata/ORA11GR2/ts_lob01.dbf' size 20m;
Tablespace created.
2.查看ts_lob表空间状态：
SYS@ORA11GR2>select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
---------------- ---------
SYSTEM ONLINE
SYSAUX ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE
TS_ORA11GR2 ONLINE
TS_CATALOG ONLINE
TS_USERS ONLINE
UNDO1 ONLINE
TS_IDX_1 ONLINE
TS_LOB ONLINE
11 rows selected.
3.创建测试表，使用TS_LOB表空间，并插入测试数据
SYS@ORA11GR2>create table tchar(x char(500)) tablespace ts_lob;
Table created.
SYS@ORA11GR2>insert into tchar select rownum from dual connect by rownum <=10000;
10000 rows created.
SYS@ORA11GR2>commit;
Commit complete.
——查看表属于哪个表空间：
SYS@ORA11GR2>select table_name,tablespace_name from dba_tables where owner='SYS' and table_name='TCHAR';
TABLE_NAME TABLESPACE_NAME
------------------------------ ----------------
TCHAR TS_LOB
4.将表空间TS_LOB修改为只读模式
SYS@ORA11GR2>alter tablespace ts_lob read only;（触发ckpt)
Tablespace altered.
——查看表空间状态：
SYS@ORA11GR2>select tablespace_name,status from dba_tablespaces where tablespace_name='TS_LOB';
TABLESPACE_NAME STATUS
---------------- ---------
TS_LOB READ ONLY
SYS@ORA11GR2>select trunc(x) from tchar where rownum<=1;
TRUNC(X)
----------
15
SYS@ORA11GR2>
5.对只读表空间中的表进行DML会报如下错误
SYS@ORA11GR2>delete tchar;
delete tchar
*
ERROR at line 1:
ORA-00372: file 3 cannot be modified at this time
ORA-01110: data file 3: '/u01/app/oracle/oradata/ORA11GR2/ts_lob01.dbf'
6.查看表空间TS_LOB数据文件的路径及名称
SYS@ORA11GR2>select file_name,tablespace_name from dba_data_files where tablespace_name='TS_LOB';
FILE_NAME TABLESPACE_NAME
--------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORA11GR2/ts_lob01.dbf TS_LOB
7.以拷贝的方式备份TS_LOB表空间的数据文件
SYS@ORA11GR2>!cp /u01/app/oracle/oradata/ORA11GR2/ts_lob01.dbf /home/oracle/ts_lob01.dbf
——验证：
SYS@ORA11GR2>!ls /home/oracle/ts_lob01.dbf
/home/oracle/ts_lob01.dbf
8.删除原目录下的数据文件
SYS@ORA11GR2>!rm /u01/app/oracle/oradata/ORA11GR2/ts_lob01.dbf
——验证：
SYS@ORA11GR2>!ls /u01/app/oracle/oradata/ORA11GR2/ts_lob01.dbf
ls: /u01/app/oracle/oradata/ORA11GR2/ts_lob01.dbf: No such file or directory
9.当只读表空间的数据文件丢失，启动关闭数据库测试，我们会发现，一致性关闭是没有问题的，不过在启动的时候，只读表空间还是检查的，故只启动到了mount状态
SYS@ORA11GR2>shutdown immediate;(触发CKPT)
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@ORA11GR2>
SYS@ORA11GR2>startup
ORACLE instance started.
Total System Global Area 730714112 bytes
Fixed Size 2256832 bytes
Variable Size 452984896 bytes
Database Buffers 272629760 bytes
Redo Buffers 2842624 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: '/u01/app/oracle/oradata/ORA11GR2/ts_lob01.dbf'
10.将丢失的数据文件从备份中拷贝回来
SYS@ORA11GR2>!cp /home/oracle/ts_lob01.dbf /u01/app/oracle/oradata/ORA11GR2/ts_lob01.dbf
——验证：
SYS@ORA11GR2>!ls /u01/app/oracle/oradata/ORA11GR2/ts_lob01.dbf
/u01/app/oracle/oradata/ORA11GR2/ts_lob01.dbf
SYS@ORA11GR2>select status from v$instance;
STATUS
------------
MOUNTED
11.打开数据库
SYS@ORA11GR2>alter database open;
Database altered.
验证：
SYS@ORA11GR2>select count(*) from tchar;
COUNT(*)
----------
10000
SYS@ORA11GR2>select trunc(x) from tchar where rownum<=1;
TRUNC(X)
----------
15
——查看表空间状态：
SYS@ORA11GR2>select tablespace_name,status from dba_tablespaces where tablespace_name='TS_LOB';
TABLESPACE_NAME STATUS
---------------- ---------
TS_LOB READ ONLY
表空间恢复到只读模式；(因为在前一次的alter tablespace ts_lob read only的动作时实际上触发ckpt进程，保持了三大文件一致性，因此前面的操作都以落盘。而后才收工备份了ts_lob表空间，所以恢复后表空间状态也只能是read only）