情况3：读写-只读
环境遵循情况1、情况2
（一）使用只读后的备份文件进行恢复
1.当前表空间为ONLINE,可读写
——查询：
SYS@ORA11GR2>select tablespace_name,status from dba_tablespaces where tablespace_name='TS_LOB';
TABLESPACE_NAME STATUS
------------------------------ ---------
TS_LOB READ ONLY
——修改：
SYS@ORA11GR2>alter tablespace ts_lob read write;
Tablespace altered.
——再查看：
SYS@ORA11GR2>select tablespace_name,status from dba_tablespaces where tablespace_name='TS_LOB';
TABLESPACE_NAME STATUS
------------------------------ ---------
TS_LOB ONLINE
SYS@ORA11GR2>select file_id,file_name,status,online_status from dba_data_files where tablespace_name='TS_LOB';
FILE_ID FILE_NAME STATUS ONLINE_
---------- --------------------------------------------- --------- -------
3 /u01/app/oracle/oradata/ORA11GR2/ts_lob01.dbf AVAILABLE ONLINE
SYS@ORA11GR2>
2.备份TS_LOB表空间，当前为online（前提：数据库处于归档模式）
SYS@ORA11GR2>archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Next log sequence to archive 3
Current log sequence 3
SYS@ORA11GR2>
——开始备份动作：
SYS@ORA11GR2>alter tablespace ts_lob begin backup;（触发ckpt，保持文件一致性）
（备份的是online时的表空间ts_lob）
Tablespace altered.
——回到操作系统层备份：
SYS@ORA11GR2>!cp /u01/app/oracle/oradata/ORA11GR2/ts_lob01.dbf /u01/app/ts_lob01.dbf
——验证：
SYS@ORA11GR2>!ls /u01/app/ts_lob01.dbf
/u01/app/ts_lob01.dbf
SYS@ORA11GR2>
——结束备份动作：
SYS@ORA11GR2>alter tablespace ts_lob end backup;
Tablespace altered.
SYS@ORA11GR2>
3.删除部分测试数据
SYS@ORA11GR2>delete from tchar where x<=500;
499 rows deleted.
SYS@ORA11GR2>commit;
Commit complete.
SYS@ORA11GR2>select count(*) from tchar;
COUNT(*)
----------
9500
4.将表空间TS_LOB修改为read only模式
SYS@ORA11GR2>alter tablespace ts_lob read only;
Tablespace altered.
——查看表空间状态：
SYS@ORA11GR2>select tablespace_name,status from dba_tablespaces where tablespace_name='TS_LOB';
TABLESPACE_NAME STATUS
------------------------------ ---------
TS_LOB READ ONLY
5.删除TS_LOB表空间的数据文件
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
6.拷贝回利用刚刚备份的数据文件（备份的是online时的文件，而要恢复到readonly时的状态）
SYS@ORA11GR2>!cp /u01/app/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
7.对数据文件进行恢复，提示无法恢复（对数据文件的恢复要么offline，要么关库恢复）
SYS@ORA11GR2>recover datafile '/u01/app/oracle/oradata/ORA11GR2/ts_lob01.dbf';
ORA-00283: recovery session canceled due to errors
ORA-01124: cannot recover data file 3 - file is in use or recovery
ORA-01110: data file 3: '/u01/app/oracle/oradata/ORA11GR2/ts_lob01.dbf'
8.重启数据库，正常一致性关闭，启动时提示要对3号数据文件进行恢复
SYS@ORA11GR2>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
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-01113: file 3 needs media recovery
ORA-01110: data file 3: '/u01/app/oracle/oradata/ORA11GR2/ts_lob01.dbf'
9.执行恢复命令，恢复成功
SYS@ORA11GR2>recover datafile 3;（应用在线日志恢复到最新）
Media recovery complete.
10.开库：
SYS@ORA11GR2>alter database open;
Database altered.
11.查看表空间ts_lob状态依旧是read only模式：
SYS@ORA11GR2>select tablespace_name,status from dba_tablespaces where tablespace_name='TS_LOB';
TABLESPACE_NAME STATUS
------------------------------ ---------
TS_LOB READ ONLY
（恢复成功，因为是在readonly时删除物理文件的，所以恢复也必须要对应）
11.修改表空间为read write后，做DML操作，没有任何问题
SYS@ORA11GR2>alter tablespace ts_lob read write;
Tablespace altered.
——查看：
SYS@ORA11GR2>select tablespace_name,status from dba_tablespaces where tablespace_name='TS_LOB';
TABLESPACE_NAME STATUS
------------------------------ ---------
TS_LOB ONLINE
验证：
SYS@ORA11GR2>delete tchar where rownum<=10;
10 rows deleted.
SYS@ORA11GR2>commit;
Commit complete.
（二）使用备份时的控制文件进行恢复，如下图，即使用备份位置的控制文件进行恢复
1.创建测试表空间ts1及相关测试表（表空间为ts1）
SYS@ORA11GR2>create tablespace ts1 datafile '/u01/app/oracle/oradata/ORA11GR2/ts1.dbf' size 10m;
Tablespace created.
SYS@ORA11GR2>create table t(x int) tablespace ts1;
Table created.
SYS@ORA11GR2>insert into t select rownum from dual connect by rownum<=10;
10 rows created.
SYS@ORA11GR2>commit;
Commit complete.
——查看表空间ts1状态：
SYS@ORA11GR2>select tablespace_name,status from dba_tablespaces where tablespace_name='TS1';
TABLESPACE_NAME STATUS
------------------------------ ---------
TS1 ONLINE
SYS@ORA11GR2>select file_id,file_name,status,online_status from dba_data_files where tablespace_name='TS1';
FILE_ID FILE_NAME STATUS ONLINE_
---------- --------------------------------------------- --------- -------
11 /u01/app/oracle/oradata/ORA11GR2/ts1.dbf AVAILABLE ONLINE
SYS@ORA11GR2>
2.通过RMAN备份ts1表空间和控制文件
oracle@wang ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Sat Oct 1 20:32:36 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORA11GR2 (DBID=237843809)
RMAN> backup tablespace ts1 include current controlfile;
Starting backup at 01-OCT-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=43 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00011 name=/u01/app/oracle/oradata/ORA11GR2/ts1.dbf
output file name=/u01/app/FRA/ORA11GR2/datafile/o1_mf_ts1_cyzc9r19_.dbf tag=TAG20161001T203359 RECID=4 STAMP=924122040
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=/u01/app/FRA/ORA11GR2/controlfile/o1_mf_TAG20161001T203359_cyzc9s6l_.ctl tag=TAG20161001T203359 RECID=5 STAMP=924122041
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 01-OCT-16
Starting Control File and SPFILE Autobackup at 01-OCT-16
piece handle=/u01/app/FRA/ORA11GR2/autobackup/2016_10_01/o1_mf_s_924122042_cyzc9tc2_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 01-OCT-16
3.删除t表部分记录
SYS@ORA11GR2>delete t where x>=6;
5 rows deleted.
SYS@ORA11GR2>commit;
Commit complete.
4.切换几次日志，让上面的动作归档（对于测试来说，意义不是特别的大）
SYS@ORA11GR2>alter system switch logfile;
System altered.
SYS@ORA11GR2>/
System altered.
SYS@ORA11GR2>/
System altered.
5.在users表空间上创建tt表
SYS@ORA11GR2>create table tt (x int) tablespace users;
Table created.
SYS@ORA11GR2>insert into tt select rownum from dual connect by rownum<=2;
2 rows created.
SYS@ORA11GR2>commit;
Commit complete.
6.将表空间ts1修改为只读模式
SYS@ORA11GR2>alter tablespace ts1 read only;
Tablespace altered.
——查看表空间ts1状态：
SYS@ORA11GR2>select tablespace_name,status from dba_tablespaces where tablespace_name='TS1';
TABLESPACE_NAME STATUS
------------------------------ ---------
TS1 READ ONLY
——查看表空间ts1对应的物理文件路径，及文件状态：
SYS@ORA11GR2>select file_id,file_name,status,online_status from dba_data_files where tablespace_name='TS1';
FILE_ID FILE_NAME STATUS ONLINE_
---------- --------------------------------------------- --------- -------
11 /u01/app/oracle/oradata/ORA11GR2/ts1.dbf AVAILABLE ONLINE
7.删除ts1表空间数据文件和所有控制文件
SYS@ORA11GR2>!rm /u01/app/oracle/oradata/ORA11GR2/ts1.dbf
SYS@ORA11GR2>!rm /u01/app/oracle/oradata/ORA11GR2/*.ctl
——验证：
SYS@ORA11GR2>!ls /u01/app/oracle/oradata/ORA11GR2/ts1.dbf
ls: /u01/app/oracle/oradata/ORA11GR2/ts1.dbf: No such file or directory
SYS@ORA11GR2>!ls /u01/app/oracle/oradata/ORA11GR2/*.ctl
ls: /u01/app/oracle/oradata/ORA11GR2/*.ctl: No such file or directory
8.重启数据库到nomount状态
SYS@ORA11GR2>shutdown immediate;
Database closed.
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/ORA11GR2/control01.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
（无法正常关库，因为缺少了控制文件，无法保持一致性）
——只能强制关库：
SYS@ORA11GR2>shutdown abort;
ORACLE instance shut down.
SYS@ORA11GR2>
SYS@ORA11GR2>startup nomount;
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
SYS@ORA11GR2>
9.通过RMAN备份的控制文件完成控制文件的恢复，恢复完成，数据库修改为mount
[oracle@wang ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Sat Oct 1 20:59:55 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORA11GR2 (not mounted)
RMAN> restore controlfile from '/u01/app/FRA/ORA11GR2/controlfile/o1_mf_TAG20161001T203359_cyzc9s6l_.ctl';
Starting restore at 01-OCT-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
channel ORA_DISK_1: copied control file copy
output file name=/u01/app/oracle/oradata/ORA11GR2/control01.ctl
output file name=/u01/app/oracle/oradata/ORA11GR2/control02.ctl
Finished restore at 01-OCT-16
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
10.恢复ts1表空间
RMAN> restore tablespace ts1;
Starting restore at 01-OCT-16
using channel ORA_DISK_1
channel ORA_DISK_1: restoring datafile 00011
input datafile copy RECID=4 STAMP=924122040 file name=/u01/app/FRA/ORA11GR2/datafile/o1_mf_ts1_cyzc9r19_.dbf
destination for restore of datafile 00011: /u01/app/oracle/oradata/ORA11GR2/ts1.dbf
channel ORA_DISK_1: copied datafile copy of datafile 00011
output file name=/u01/app/oracle/oradata/ORA11GR2/ts1.dbf RECID=0 STAMP=0
Finished restore at 01-OCT-16
10.表空间恢复完毕，尝试open数据库
--当然，我们知道一定是无法open数据库的，因为控制文件恢复的是老版本的
SYS@ORA11GR2>!ls /u01/app/oracle/oradata/ORA11GR2/ts1.dbf
/u01/app/oracle/oradata/ORA11GR2/ts1.dbf
SYS@ORA11GR2>
SYS@ORA11GR2>alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
--提示resetlogs方式打开
SYS@ORA11GR2>alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/u01/app/oracle/oradata/ORA11GR2/system01.dbf'
--提示需要恢复1号数据文件
SYS@ORA11GR2>recover datafile 1;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
--提示using BACKUP CONTROLFILE方式完成恢复
12. using BACKUP CONTROLFILE 恢复数据库
（注：如果要恢复到控制文件SCN以后的时间。这时候，就需要用using backup controlfile. 恢复就不会受“当前controlfile所纪录的SCN”的限制。这时候的限制就来自于你的语句(until time , until scn)，或者可用的archive log(until cancel)）
SYS@ORA11GR2>recover database using backup controlfile until cancel;
ORA-00279: change 1977581 generated at 10/01/2016 20:55:49 needed for thread 1
ORA-00289: suggestion :
/u01/app/FRA/ORA11GR2/archivelog/2016_10_01/o1_mf_1_13_%u_.arc
ORA-00280: change 1977581 for thread 1 is in sequence #13
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
--此处回车则是利用下一个归档日志文件进行恢复。不过在这里也可以输入CANCEL,表示恢复到此结束，不再使用后面的归档日志文件
……
--在恢复的过程中又出现了如下警告
ORA-00308: cannot open archived log
'/u01/app/FRA/ORA11GR2/archivelog/2016_10_01/o1_mf_1_13_%u_.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/u01/app/oracle/oradata/ORA11GR2/system01.dbf'
13.尝试再次resetlogs打开数据库
SYS@ORA11GR2>alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/u01/app/oracle/oradata/ORA11GR2/system01.dbf'
14.采用极端办法
（注：此时只能采取极端手段：隐含参数_allow_resetlogs_corruption强制启动数据库，设置此参数之后，在数据库Open过程中，Oracle会跳过某些一致性检查，从而使数据库可能跳过不一致状态，Open打开:）
--查看隐藏参数_allow_resetlogs_corruption，默认值为false
SYS@ORA11GR2>select ksppstvl, ksppdesc from x$ksppi x, x$ksppcv y where x.indx = y.indx and ksppinm = '_allow_resetlogs_corruption';
KSPPSTVL KSPPDESC
---------- --------------------------------------------------
FALSE allow resetlogs even if it will cause corruption
--将隐藏参数设置为true
SYS@ORA11GR2>alter system set "_allow_resetlogs_corruption"=true scope=spfile;
System altered.
--由于是静态参数，重启数据库使其生效
SYS@ORA11GR2>shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
--数据库启动到mount状态
SYS@ORA11GR2>startup mount;
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.
15.再次尝试open数据库，提示RESETLOGS方式open
SYS@ORA11GR2>alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
16.数据库成功以resetlogs方式open
SYS@ORA11GR2>alter database open resetlogs;
ERROR:
ORA-03114: not connected to ORACLE
alter database open resetlogs
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [2662], [0], [1977588], [0],
[1977683], [4194432], [], [], [], [], [], []
Process ID: 21554
Session ID: 1 Serial number: 5
——重新连接实例：
SYS@ORA11GR2>conn / as sysdba
Connected to an idle instance.
——再次启库：（成功）
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.
Database opened.
——验证：
SYS@ORA11GR2>select count(*) from t;
COUNT(*)
----------
5
SYS@ORA11GR2>select count(*) from tt;
COUNT(*)
----------
2
17.恢复参数，重启数据
SYS@ORA11GR2>alter system set "_allow_resetlogs_corruption"=false scope=spfile;
System altered.
SYS@ORA11GR2>startup force;
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.
Database opened.
总结：如果在生产中使用了只读表空间，那么切记，在修改为只读以后，第一件事儿就是备份，这样会省去不必要的麻烦和损失。
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31397003/viewspace-2126521/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/31397003/viewspace-2126521/
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
