表空间处于read only时，如何进行全库恢复(一)

本次案例仅测试表空间处于read only时，如何进行全库恢复。为简化测试流程，数据库采用冷备份，控制文件采用rman在线备份
[oracle@linuxsvr ora10r2]$ cp *.dbf ../orabak

业务表空间没有增加数据文件
故障发生时业务表空间处于read only状态。
1、controlfile备份在业务表空间read only之前
14:18:38 SQL> select STATUS from dba_tablespaces where tablespace_name='ZHOU';

STATUS
---------
ONLINE

14:18:59 SQL> create user zhou identified by zhou default tablespace zhou;

User created.

14:19:33 SQL> grant dba to zhou;

Grant succeeded.

14:19:42 SQL> alter system switch logfile;

System altered.

14:19:51 SQL> /

System altered.

14:19:51 SQL> conn zhou/zhou
Connected.
14:19:56 SQL> create table test1 as select * from sys.obj$;

Table created.

14:20:15 SQL> alter system switch logfile;

System altered.

14:20:27 SQL> delete from test1 where rownum<100;

99 rows deleted.

14:20:38 SQL> commit;

Commit complete.

14:28:30 SQL> select count(*) from test1;

  COUNT(*)
----------
     50609
    
RMAN> copy current controlfile to '/tmp/before_readonly.ctl';

Starting backup at 19-DEC-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying current control file
output filename=/tmp/before_readonly.ctl tag=TAG20101219T142748 recid=1 stamp=738167269
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 19-DEC-10

RMAN> exit


14:28:35 SQL> alter system switch logfile;

System altered.

14:28:48 SQL> alter tablespace zhou read only;

Tablespace altered.


14:29:08 SQL> conn /as sysdba
Connected.
14:29:11 SQL> shutdown abort
ORACLE instance shut down.

进行datafile恢复
[oracle@linuxsvr oradata]$ mv ora10r2 ora10r2_new
[oracle@linuxsvr oradata]$ mv orabak oradata

进行controlfile恢复
cp /tmp/before_readonly.ctl ./ora10r2/control01.ctl

注意到虽然控制文件比数据文件要新，但由于controlfile为rman所备份，所以需要加后缀using backup controlfile。此部分测试将在本测试完成后再做测试
14:35:44 SQL> select checkpoint_change# from v$datafile;

CHECKPOINT_CHANGE#
------------------
            526338
            526338
            526338
            526338
            526534

14:36:19 SQL> select checkpoint_change# from v$datafile_header;

CHECKPOINT_CHANGE#
------------------
            505976
            505976
            505976
            505976
            505976
           
14:34:52 SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

进行数据库恢复
14:36:28 SQL> recover database using backup controlfile;
ORA-00279: change 505976 generated at 12/19/2010 14:11:04 needed for thread 1
ORA-00289: suggestion : /oradata/archlog/1_5_728482772.dbf
ORA-00280: change 505976 for thread 1 is in sequence #5


14:38:20 Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
。。。

ORA-00279: change 526655 generated at 12/19/2010 14:28:48 needed for thread 1
ORA-00289: suggestion : /oradata/archlog/1_15_728482772.dbf
ORA-00280: change 526655 for thread 1 is in sequence #15
ORA-00278: log file '/oradata/archlog/1_14_728482772.dbf' no longer needed for
this recovery


ORA-00308: cannot open archived log '/oradata/archlog/1_15_728482772.dbf'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

注意到Oracle找不到1_15_728482772.dbf，这是由于redolog尚未归档所致，要进行完全恢复，只要将redolog输进即可，Oracle会进行自己匹配恢复
14:38:48 SQL> recover database using backup controlfile;
ORA-00279: change 526655 generated at 12/19/2010 14:28:48 needed for thread 1
ORA-00289: suggestion : /oradata/archlog/1_15_728482772.dbf
ORA-00280: change 526655 for thread 1 is in sequence #15


14:38:57 Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/oradata/ora10r2_new/redo02.log
Log applied.
Media recovery complete.

由于用了using backup controlfile，必须要用 resetlogs选项打开。
14:40:53 SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


14:41:28 SQL> alter database open NORESETLOGS;
alter database open NORESETLOGS
*
ERROR at line 1:
ORA-01588: must use RESETLOGS option for database open


14:41:33 SQL> alter database open RESETLOGS;

Database altered

查看表空间状态和业务数据，并未丢失数据。
14:42:20 SQL> select status from dba_tablespaces where tablespace_name='ZHOU';

STATUS
---------
READ ONLY

14:56:00 SQL> conn zhou/zhou
Connected.
14:56:09 SQL> select count(*) from test1;

  COUNT(*)
----------
     50609