5.1.1. 恢复只读表空间的三种情况
情况 | 备份时表空间的状态 | 要恢复时表空间的状态 | 恢复 |
1 | 只读 | 只读 | 将备份的数据文件恢复到目的地 |
2 | 只读 | 读写 | 先还原至备份状态,再应用日志恢复 |
3 | 读写 | 只读 | 先还原至备份状态,再应用日志恢复 |
5.1.2. 情况1:只读-只读
1) 准备环境,创建测试表空间TS_LOB
SYS@ORCL>create tablespace ts_lob datafile '/u01/app/oracle/oradata/ORCL/ts_lob01.dbf' size 20m;
Tablespace created.
SYS@ORCL>
2) TS_LOB为ONLINE状态
SYS@ORCL>select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
-------------------------- ---------
SYSTEM ONLINE
UNDOTBS1 ONLINE
SYSAUX ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE
TS_USER ONLINE
TS_LOB ONLINE
8 rows selected.
SYS@ORCL>
3) 创建测试表,使用TS_LOB表空间,并插入测试数据
SYS@ORCL>create table scott.t_char(x char(500)) tablespace ts_lob;
Table created.
SYS@ORCL>insert into scott.t_char select rownum from dual connect by rownum<=10000;
10000 rows created.
SYS@ORCL>commit;
Commit complete.
SYS@ORCL>
SYS@ORCL>select table_name,tablespace_name from dba_tables where owner='SCOTT' and table_name='T_CHAR';
TABLE_NAME TABLESPACE_NAME
-------------------------- ------------------------------
T_CHAR TS_LOB
SYS@ORCL>
4) 将表空间TS_LOB修改为只读模式
SYS@ORCL>alter tablespace ts_lob read only;
Tablespace altered.
SYS@ORCL>select tablespace_name,status from dba_tablespaces where tablespace_name='TS_LOB';
TABLESPACE_NAME STATUS
-------------------------- ---------
TS_LOB READ ONLY
SYS@ORCL>
SYS@ORCL>select trunc(x) from scott.t_char where rownum<=1;
TRUNC(X)
----------
155
SYS@ORCL>
5) 对只读表空间中的表进行DML会报如下错误
SYS@ORCL>delete scott.t_char;
delete scott.t_char
*
ERROR at line 1:
ORA-00372: file 7 cannot be modified at this time
ORA-01110: data file 7: '/u01/app/oracle/oradata/ORCL/ts_lob01.dbf'
SYS@ORCL>
6) 查看表空间TS_LOB数据文件的路径及名称
SYS@ORCL>select file_name,tablespace_name from dba_data_files where tablespace_name='TS_LOB';
FILE_NAME TABLESPACE_NAME
------------------------------------------------ -------------------
/u01/app/oracle/oradata/ORCL/ts_lob01.dbf TS_LOB
SYS@ORCL>
7) 以拷贝的方式备份TS_LOB表空间的数据文件
SYS@ORCL>!cp /u01/app/oracle/oradata/ORCL/ts_lob01.dbf /home/oracle/ts_lob01.dbf
SYS@ORCL>
SYS@ORCL>!ls /home/oracle/ts_lob*
/home/oracle/ts_lob01.dbf
SYS@ORCL>
8) 删除TS_LOB表空间的数据文件
SYS@ORCL>!ls /u01/app/oracle/oradata/ORCL/ts_lob01.dbf
/u01/app/oracle/oradata/ORCL/ts_lob01.dbf
SYS@ORCL>
SYS@ORCL>!rm /u01/app/oracle/oradata/ORCL/ts_lob01.dbf
SYS@ORCL>!ls /u01/app/oracle/oradata/ORCL/ts_lob01.dbf
ls: /u01/app/oracle/oradata/ORCL/ts_lob01.dbf: No such file or directory
SYS@ORCL>
9) 当只读表空间的数据文件丢失,启动关闭数据库测试,我们会发现,一致性关闭是没有问题的,不过在启动的时候,只读表空间还是检查的,故只启动到了mount状态
SYS@ORCL>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@ORCL>startup
ORACLE instance started.
Total System Global Area 272629760 bytes
Fixed Size 1218920 bytes
Variable Size 100664984 bytes
Database Buffers 167772160 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
ORA-01110: data file 7: '/u01/app/oracle/oradata/ORCL/ts_lob01.dbf'
SYS@ORCL>
10) 将丢失的数据文件从备份中拷贝回来
SYS@ORCL>!cp /home/oracle/ts_lob01.dbf /u01/app/oracle/oradata/ORCL/ts_lob01.dbf
SYS@ORCL>
11) 成功open数据库
SYS@ORCL>alter database open;
Database altered.
SYS@ORCL>select count(*) from scott.t_char;
COUNT(*)
----------
10000
SYS@ORCL>select trunc(x) from scott.t_char where rownum<=1;
TRUNC(X)
----------
155
SYS@ORCL>
5.1.3. 情况2:只读-读写
注,情况1的环境继续测试
1) 将只读表空间修改为读写
SYS@ORCL>alter tablespace ts_lob read write;
Tablespace altered.
SYS@ORCL>
2) 删除状态为read write的表空间TS_LOB
SYS@ORCL>!ls /u01/app/oracle/oradata/ORCL/ts_lob01.dbf
/u01/app/oracle/oradata/ORCL/ts_lob01.dbf
SYS@ORCL>!rm /u01/app/oracle/oradata/ORCL/ts_lob01.dbf
SYS@ORCL>!ls /u01/app/oracle/oradata/ORCL/ts_lob01.dbf
ls: /u01/app/oracle/oradata/ORCL/ts_lob01.dbf: No such file or directory
SYS@ORCL>
3) 利用之前的只读数据文件进行恢复
SYS@ORCL>!cp /home/oracle/ts_lob01.dbf /u01/app/oracle/oradata/ORCL/ts_lob01.dbf
SYS@ORCL>
4) Mount模式下恢复
SYS@ORCL>shutdown immediate;
ORA-03113: end-of-file on communication channel
SYS@ORCL>
SYS@ORCL>startup
ORACLE instance started.
Total System Global Area 272629760 bytes
Fixed Size 1218920 bytes
Variable Size 104859288 bytes
Database Buffers 163577856 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-01113: file 7 needs media recovery
ORA-01110: data file 7: '/u01/app/oracle/oradata/ORCL/ts_lob01.dbf'
SYS@ORCL>recover datafile 7;
Media recovery complete.
SYS@ORCL>
SYS@ORCL>alter database open;
Database altered.
SYS@ORCL>delete scott.t_char where x=1;
1 row deleted.
SYS@ORCL>commit;
Commit complete.
SYS@ORCL>
5) 在线恢复
--再次删除TS_LOB的数据文件
SYS@ORCL>!ls /u01/app/oracle/oradata/ORCL/ts_lob01.dbf
/u01/app/oracle/oradata/ORCL/ts_lob01.dbf
SYS@ORCL>!rm /u01/app/oracle/oradata/ORCL/ts_lob01.dbf
SYS@ORCL>!ls /u01/app/oracle/oradata/ORCL/ts_lob01.dbf
ls: /u01/app/oracle/oradata/ORCL/ts_lob01.dbf: No such file or directory
SYS@ORCL>
--将表空间改为离线模式
SYS@ORCL>alter tablespace ts_lob offline;
Tablespace altered.
SYS@ORCL>select file_id,file_name,status,online_status from dba_data_files where tablespace_name='TS_LOB';
FILE_ID FILE_NAME STATUS ONLINE_
---------- --------------------------------------------- --------- -------
7 /u01/app/oracle/oradata/ORCL/ts_lob01.dbf AVAILABLE OFFLINE
SYS@ORCL>select tablespace_name,status from dba_tablespaces where tablespace_name='TS_LOB';
TABLESPACE_NAME STATUS
------------------------------ ---------
TS_LOB OFFLINE
SYS@ORCL>
--从之前的只读备份中恢复丢失的数据文件
SYS@ORCL>!cp /home/oracle/ts_lob01.dbf /u01/app/oracle/oradata/ORCL/ts_lob01.dbf
SYS@ORCL>
--此时将表空间改为在线模式,此处一定是报错的,因为我们是用只读的数据文件进行恢复的
SYS@ORCL>alter tablespace ts_lob online;
alter tablespace ts_lob online
*
ERROR at line 1:
ORA-01113: file 7 needs media recovery
ORA-01110: data file 7: '/u01/app/oracle/oradata/ORCL/ts_lob01.dbf'
SYS@ORCL>
--恢复数据文件
SYS@ORCL>recover datafile 7;
Media recovery complete.
SYS@ORCL>
--此时可正常将表空间online
SYS@ORCL>alter tablespace ts_lob online;
Tablespace altered.
SYS@ORCL>select file_id,file_name,status,online_status from dba_data_files where tablespace_name='TS_LOB';
FILE_ID FILE_NAME STATUS ONLINE_
---------- --------------------------------------------- --------- -------
7 /u01/app/oracle/oradata/ORCL/ts_lob01.dbf AVAILABLE ONLINE
SYS@ORCL>select tablespace_name,status from dba_tablespaces where tablespace_name='TS_LOB';
TABLESPACE_NAME STATUS
------------------------------ ---------
TS_LOB ONLINE
SYS@ORCL>
--验证可用性
SYS@ORCL>delete scott.t_char where x=2;
1 row deleted.
SYS@ORCL>commit;
Commit complete.
SYS@ORCL>insert into scott.t_char values(2);
1 row created.
SYS@ORCL>commit;
Commit complete.
SYS@ORCL>
--再次修改为read only
SYS@ORCL>alter tablespace ts_lob read only;
Tablespace altered.
SYS@ORCL>select tablespace_name,status from dba_tablespaces where tablespace_name='TS_LOB';
TABLESPACE_NAME STATUS
------------------------------ ---------
TS_LOB READ ONLY
SYS@ORCL>
5.1.4. 情况3:读写-只读
(一) 使用只读后的控制文件进行恢复
1) 当前表空间为online,可读写
SYS@ORCL>select tablespace_name,status from dba_tablespaces where tablespace_name='TS_LOB';
TABLESPACE_NAME STATUS
-------------------------- ---------
TS_LOB ONLINE
SYS@ORCL>select file_id,file_name,status,online_status from dba_data_files where tablespace_name='TS_LOB';
FILE_ID FILE_NAME STATUS ONLINE_
---------- --------------------------------------------- --------- -------
7 /u01/app/oracle/oradata/ORCL/ts_lob01.dbf AVAILABLE ONLINE
SYS@ORCL>
2) 备份TS_LOB表空间(前提:数据库处于归档模式)
SYS@ORCL>alter tablespace ts_lob begin backup;
Tablespace altered.
SYS@ORCL>!cp /u01/app/oracle/oradata/ORCL/ts_lob01.dbf /u01/app/ts_lob01.dbf
SYS@ORCL>alter tablespace ts_lob end backup;
Tablespace altered.
SYS@ORCL>
3) 删除部分测试数据
SYS@ORCL>delete from scott.t_char where x<=500;
499 rows deleted.
SYS@ORCL>commit;
Commit complete.
SYS@ORCL>select count(*) from scott.t_char;
COUNT(*)
----------
9500
SYS@ORCL>
4) 将表空间TS_LOB修改为read only模式
SYS@ORCL>alter tablespace ts_lob read only;
Tablespace altered.
SYS@ORCL>select tablespace_name,status from dba_tablespaces where tablespace_name='TS_LOB';
TABLESPACE_NAME STATUS
-------------------------- ---------
TS_LOB READ ONLY
SYS@ORCL>
5) 删除TS_LOB表空间的数据文件
SYS@ORCL>!rm /u01/app/oracle/oradata/ORCL/ts_lob01.dbf
6) 拷贝回利用刚刚备份的数据文件
SYS@ORCL>!cp /u01/app/ts_lob01.dbf /u01/app/oracle/oradata/ORCL/ts_lob01.dbf
7) 对数据文件进行恢复,提示无法恢复
SYS@ORCL>recover datafile 7;
ORA-00283: recovery session canceled due to errors
ORA-01124: cannot recover data file 7 - file is in use or recovery
ORA-01110: data file 7: '/u01/app/oracle/oradata/ORCL/ts_lob01.dbf'
8) 重启数据库,正常一致性关闭,启动时提示要对7号数据文件进行恢复
SYS@ORCL>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@ORCL>startup
ORACLE instance started.
Total System Global Area 272629760 bytes
Fixed Size 1218920 bytes
Variable Size 117442200 bytes
Database Buffers 150994944 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-01113: file 7 needs media recovery
ORA-01110: data file 7: '/u01/app/oracle/oradata/ORCL/ts_lob01.dbf'
9) 执行恢复命令,恢复成功
SYS@ORCL>recover datafile 7;
Media recovery complete.
SYS@ORCL>
10) 成功打开数据库
SYS@ORCL>alter database open;
Database altered.
SYS@ORCL>
11) 表空间TS_LOB依旧为READ ONLY模式
SYS@ORCL>select tablespace_name,status from dba_tablespaces where tablespace_name='TS_LOB';
TABLESPACE_NAME STATUS
-------------------------- ---------
TS_LOB READ ONLY
SYS@ORCL>
12) 修改表空间为read write,做DML操作,没有任何问题
SYS@ORCL>alter tablespace ts_lob read write;
Tablespace altered.
SYS@ORCL>select tablespace_name,status from dba_tablespaces where tablespace_name='TS_LOB';
TABLESPACE_NAME STATUS
-------------------------- ---------
TS_LOB ONLINE
SYS@ORCL>delete scott.t_char where rownum<=10;
10 rows deleted.
SYS@ORCL>commit;
Commit complete.
SYS@ORCL>