1.備份的控制文件之後創建表空間，控制文件全部丟失，使用備份控制文件如何恢復
步驟如下：
1.備份數據庫
rman target / catalog RC_ADMIN/RC_ADMIN@prod3
backup database plus archivelog delete all input;
2.創建表空間
SYS@PROD2>create tablespace indx
2 datafile '/u01/app/oracle/oradata/PROD2/indx01.dbf'
3 size 10m;
3.創建表，指定表空間為indx
SYS@PROD2>create table test tablespace indx as select * from scott.emp;
Table created.
SYS@PROD2>select * from test;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
SYS@PROD2>alter system switch logfile;
System altered.
--刪除部分記錄用於測試
SYS@PROD2>delete test where deptno=30;
6 rows deleted.
SYS@PROD2>commit;
Commit complete.
SYS@PROD2>select * from test;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7566 JONES MANAGER 7839 02-APR-81 2975 20
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
8 rows selected.
4.刪除全部的控制文件
[email protected]:/u01/app/oracle/oradata/PROD2> rm -rf control*
alert 日志裡面報找不到控制文件
Tue May 12 02:15:47 2015
Errors in file /u01/app/oracle/diag/rdbms/prod2/PROD2/trace/PROD2_m000_4995.trc:
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/PROD2/control01.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
5.關閉數據庫啟動到nomount
SYS@PROD2>shutdown abort;
ORACLE instance shut down.
SYS@PROD2>startup nomount;
ORACLE instance started.
Total System Global Area 630501376 bytes
Fixed Size 2215984 bytes
Variable Size 255856592 bytes
Database Buffers 364904448 bytes
Redo Buffers 7524352 bytes
6.登錄rman 恢復之前備份的控制文件
[email protected]:/u01/app/oracle/oradata/PROD2> rman target / catalog RC_ADMIN/RC_ADMIN@prod3
Recovery Manager: Release 11.2.0.1.0 - Production on Tue May 12 02:19:25 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: PROD2 (not mounted)
connected to recovery catalog database
RMAN> restore controlfile;
Starting restore at 12-MAY-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=10 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=180 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=11 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: reading from backup piece /home/oracle/rman_bk/control/c-1529226433-20150512-02
channel ORA_DISK_1: piece handle=/home/oracle/rman_bk/control/c-1529226433-20150512-02 tag=TAG20150512T020556
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/PROD2/control01.ctl
output file name=/u01/app/oracle/oradata/PROD2/control02.ctl
Finished restore at 12-MAY-15
RMAN> alter database mount;
RMAN> recover database;
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01245: offline file 6 will be lost if RESETLOGS is done
ORA-01111: name for data file 6 is unknown - rename to correct file
ORA-01110: data file 6: '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00006'
將數據文件6改名字
alter database create datafile 6 as '/u01/app/oracle/oradata/PROD2/indx01.dbf';
SYS@PROD2>alter database datafile 6 online;
Database altered.
RMAN> recover database;
Starting recover at 12-MAY-15
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
starting media recovery
archived log for thread 1 with sequence 5 is already on disk as file /u01/app/oracle/oradata/PROD2/redo02.log
archived log file name=/u01/app/oracle/oradata/PROD2/redo02.log thread=1 sequence=5
Oracle Error:
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01245: offline file 6 will be lost if RESETLOGS is done
ORA-01110: data file 6: '/u01/app/oracle/oradata/PROD2/indx01.dbf'
media recovery complete, elapsed time: 00:00:00
Finished recover at 12-MAY-15
注意不要使用rman恢復當using backup controlfile 使用sqlplus
sqlplus / as sysdba
SYS@PROD2>recover database using backup controlfile;
ORA-00279: change 1212936 generated at 05/12/2015 02:15:52 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/archive/1_5_879466155.dbf
ORA-00280: change 1212936 for thread 1 is in sequence #5
Specify log: {
auto
SYS@PROD2>select group#,sequence#,status from v$log;
GROUP# SEQUENCE# STATUS
---------- ---------- ----------------
1 4 CURRENT
3 3 ACTIVE
2 2 INACTIVE
sequence 為5 的還沒有歸檔，指定當前redo日志目錄
SYS@PROD2>recover database using backup controlfile;
ORA-00279: change 1212936 generated at 05/12/2015 02:15:52 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/archive/1_5_879466155.dbf
ORA-00280: change 1212936 for thread 1 is in sequence #5
Specify log: {
/u01/app/oracle/oradata/PROD2/redo02.log
Log applied.
Media recovery complete.
SYS@PROD2>select * from v$recover_file;
no rows selected
SYS@PROD2>alter database open resetlogs;
SYS@PROD2>select * from test;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7566 JONES MANAGER 7839 02-APR-81 2975 20
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
8 rows selected.
查看記錄正常，恢復成功！
注意事項：
1。需要重定向數據文件
alter database create datafile 6 as '/u01/app/oracle/oradata/PROD2/indx01.dbf';
2。數據文件修改為online
alter database datafile 6 online;
3。當using backup controlfile時，不能使用rman恢復
