控制文件的恢复(五)

    使用'create controlfile'命令里datafile子句不能声明只读表空间的数据文件，如果有只读数据文件，控制文件的恢复步骤和之前的不同，例如：
    DB：Oracle 11g 11.2.0.3.0 X86_64,OS RHEL 6.3 X86_64,未使用Recovery Catalog

1.建立只读表空间数据文件
SYS@orcl>create tablespace tstest datafile '/u01/app/oracle/oradata/orcl/tstest01.dbf' size 10m;
Tablespace created.

SYS@orcl>create table scott.emp01 tablespace tstest as select * from scott.emp;
Table created.

SYS@orcl>alter tablespace tstest read only;
Tablespace altered.

---查看tstet表空间状态
SYS@orcl>select tablespace_name,file_id,file_name,a.status from dba_tablespaces a join dba_data_files using(tablespace_name);
TABLESPACE    FILE_ID FILE_NAME                     STATUS
---------- ---------- --------------------------------------------- ---------
EXAMPLE         5 /u01/app/oracle/oradata/orcl/example01.dbf    ONLINE
USERS            4 /u01/app/oracle/oradata/orcl/users01.dbf        ONLINE
UNDOTBS1        3 /u01/app/oracle/oradata/orcl/undotbs01.dbf    ONLINE
SYSAUX            2 /u01/app/oracle/oradata/orcl/sysaux01.dbf     ONLINE
SYSTEM            1 /u01/app/oracle/oradata/orcl/system01.dbf     ONLINE
TSTEST            6 /u01/app/oracle/oradata/orcl/tstest01.dbf     READ ONLY

6 rows selected.

2.破坏控制文件
[oracle@ora dbs]$ dd if=/dev/zero of=/u01/app/oracle/oradata/orcl/control01.ctl bs=512K count=10;
10+0 records in
10+0 records out
5242880 bytes (5.2 MB) copied, 0.00820918 s, 639 MB/s
[oracle@ora dbs]$ dd if=/dev/zero of=/u01/app/oracle/fast_recovery_area/orcl/control02.ctl bs=512K count=10;
10+0 records in
10+0 records out
5242880 bytes (5.2 MB) copied, 0.00593637 s, 883 MB/s
[oracle@ora dbs]$ dd if=/dev/zero of=/u01/app/oracle/oradata/orcl/control03.ctl bs=512K count=10;
10+0 records in
10+0 records out
5242880 bytes (5.2 MB) copied, 0.00639398 s, 820 MB/s

3.启动实例到nomount状态
SYS@orcl>startup force nomount;
ORACLE instance started.

Total System Global Area 1887350784 bytes
Fixed Size            2229464 bytes
Variable Size         1107299112 bytes
Database Buffers      771751936 bytes
Redo Buffers            6070272 bytes

4.使用'create controlfile‘命令创建控制文件
SYS@orcl>CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/app/oracle/oradata/orcl/redo01.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/u01/app/oracle/oradata/orcl/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/u01/app/oracle/oradata/orcl/redo03.log'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/u01/app/oracle/oradata/orcl/system01.dbf',
  '/u01/app/oracle/oradata/orcl/sysaux01.dbf',
  '/u01/app/oracle/oradata/orcl/undotbs01.dbf',
  '/u01/app/oracle/oradata/orcl/users01.dbf',
  '/u01/app/oracle/oradata/orcl/example01.dbf'
  CHARACTER SET AL32UTF8 ;

5.执行catalog命令读取快速恢复区的备份和归档日志
RMAN> catalog db_recovery_file_dest;
using target database control file instead of recovery catalog
searching for all files in the recovery area

List of Files Unknown to the Database
=====================================
File Name: /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2013_01_05/o1_mf_1_19_8gjblxnc_.arc
……省略……
File Name: /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2013_01_05/o1_mf_1_16_8gjbj41k_.arc

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2013_01_05/o1_mf_1_19_8gjblxnc_.arc
……省略……
File Name: /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2013_01_05/o1_mf_1_16_8gjbj41k_.arc


6.执行'recover database'命令恢复数据库
RMAN> recover database;
Starting recover at 05-JAN-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=10 device type=DISK

starting media recovery

unable to find archived log
archived log thread=1 sequence=27
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 01/05/2013 21:31:28
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 27 and starting SCN of 1146830


---查出27号日志的路径
SYS@orcl>select group#,sequence#,a.status,member from v$log a join v$logfile using(group#);
    GROUP#  SEQUENCE# STATUS           MEMBER
---------- ---------- ---------------- ----------------------------------------
     1       25 INACTIVE           /u01/app/oracle/oradata/orcl/redo01.log
     3       27 CURRENT           /u01/app/oracle/oradata/orcl/redo03.log
     2       26 INACTIVE           /u01/app/oracle/oradata/orcl/redo02.log

---使用sqlplus中的‘recover database’命令恢复
SYS@orcl>recover database using backup controlfile;
ORA-00279: change 1146830 generated at 01/05/2013 21:29:02 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2013_01_05/o1_mf_1_27_%u_.arc
ORA-00280: change 1146830 for thread 1 is in sequence #27


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/orcl/redo03.log                            ---输入27号日志的路径
Log applied.
Media recovery complete.

7.以resetlogs打开数据库
SYS@orcl>alter database open resetlogs;
Database altered.

8.查看tetest表空间的数据文件
SYS@orcl>select file#,status,name from v$datafile;
FILE# STATUS  NAME
---------- ------- ------------------------------------------------------------
     1 SYSTEM  /u01/app/oracle/oradata/orcl/system01.dbf
     2 ONLINE  /u01/app/oracle/oradata/orcl/sysaux01.dbf
     3 ONLINE  /u01/app/oracle/oradata/orcl/undotbs01.dbf
     4 ONLINE  /u01/app/oracle/oradata/orcl/users01.dbf
     5 ONLINE  /u01/app/oracle/oradata/orcl/example01.dbf

     6 OFFLINE /u01/app/oracle/product/11.2.0/dbhome_1/dbs/MISSING00006

---6号数据文件和之前建立时保存的路径不一样

9.重命名表空间数据文件
SYS@orcl>alter database rename file '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/MISSING00006' to '/u01/app/oracle/oradata/orcl/tstest01.dbf';
Database altered.

SYS@orcl>select file#,status,name from v$datafile;
     FILE# STATUS  NAME
---------- ------- ------------------------------------------------------------
     1 SYSTEM  /u01/app/oracle/oradata/orcl/system01.dbf
     2 ONLINE  /u01/app/oracle/oradata/orcl/sysaux01.dbf
     3 ONLINE  /u01/app/oracle/oradata/orcl/undotbs01.dbf
     4 ONLINE  /u01/app/oracle/oradata/orcl/users01.dbf
     5 ONLINE  /u01/app/oracle/oradata/orcl/example01.dbf
     6 ONLINE  /u01/app/oracle/oradata/orcl/tstest01.dbf

6 rows selected.

10.将tetest表空间的数据文件online
SYS@orcl>alter tablespace tstest online;
Tablespace altered.

11.添加临时文件
SYS@orcl>alter tablespace temp add tempfile  '/u01/app/oracle/oradata/orcl/temp01.dbf' reuse;
Tablespace altered.