oracle 使用备份的控制文件恢复，并且之后新建了表空间的恢复

浏览数：26 / 时间：2015年06月12日

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

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: {<RET>=suggested | filename | AUTO | CANCEL}
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: {<RET>=suggested | filename | AUTO | CANCEL}
/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;

8 rows selected.

查看记录正常，恢复成功！

注意事项：

1。需要重定向数据文件

alter database create datafile 6 as ‘/u01/app/oracle/oradata/PROD2/indx01.dbf‘;

2。数据文件修改为online

alter database datafile 6 online;