rman表空间丢失与离线恢复

rman单个数据文件丢失与恢复

一，用的是之间的备份
（1）切换用户
SQL> conn hai/hai    
Connected.
(2)复制表
SQL> create  table feng3 as  select  * from hai.feng;
Table created.


SQL> select  *  from feng3;


        ID NAME
---------- ------------------------------------------------------------
         2 haifeng
         3 haifeng2
         4 haifeng3
         5 haifeng4
         6 haifeng5
         7 haifeng5
         8 haifeng6
         9 haifeng8
        10 haifeng8


9 rows selected.
(3)切换日志
SQL> conn / as sysdba
Connected.


SQL> alter system switch logfile;


System altered.


SQL> alter  system checkpoint;


System altered.


（4）关闭数据库
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> 
SQL> exit
（5） 删除数据文件
Disconnected from Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
[oracle@oracle1 ~]$ cd  /oracle/app/oradata/
[oracle@oracle1 shpog1]$ rm -rf  hai01.dbf
[oracle@oracle1 shpog1]$ 
[oracle@oracle1 shpog1]$ 
[oracle@oracle1 shpog1]$ ll
total 9817148
-rw-r----- 1 oracle oinstall    9748480 Apr  2 23:13 control01.ctl
-rw-r----- 1 oracle oinstall    9748480 Apr  2 23:13 control02.ctl
-rw-r----- 1 oracle oinstall  104865792 Apr  2 23:13 example01.dbf
-rw-r----- 1 oracle oinstall   52429312 Apr  2 20:53 redo01.log
-rw-r----- 1 oracle oinstall   52429312 Apr  2 20:53 redo02.log
-rw-r----- 1 oracle oinstall   52429312 Apr  2 23:12 redo03.log
-rw-r----- 1 oracle oinstall   52429312 Apr  2 23:13 redo04.log
-rw-r----- 1 oracle oinstall   52429312 Apr  2 20:53 redo05.log
-rw-r----- 1 oracle oinstall 5368717312 Apr  2 23:13 rman_tbs.dbf
-rw-r----- 1 oracle oinstall 1073750016 Apr  2 23:13 sysaux01.dbf
-rw-r----- 1 oracle oinstall 1073750016 Apr  2 23:13 system01.dbf
-rw-r----- 1 oracle oinstall   40902656 Apr  2 15:35 temp01.dbf
-rw-r----- 1 oracle oinstall 1073750016 Apr  2 23:13 undotbs01.dbf
-rw-r----- 1 oracle oinstall 1073750016 Apr  2 23:13 users01.dbf
（6）启动数据库报错
[oracle@oracle1 shpog1]$ sqlplus  / as sysdba


SQL*Plus: Release 11.2.0.1.0 Production on Sun Apr 2 23:15:01 2017


Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to an idle instance.


SQL> startup;
ORACLE instance started.


Total System Global Area 1586708480 bytes
Fixed Size                  2213736 bytes
Variable Size            1191184536 bytes
Database Buffers          385875968 bytes
Redo Buffers                7434240 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 8 - see DBWR trace file
ORA-01110: data file 8: '/oracle/app/oradata/shpog1/hai01.dbf'


SQL> exit 
Disconnected from Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
二 ，
rman进行恢复
[oracle@oracle1 shpog1]$ rman  target /


Recovery Manager: Release 11.2.0.1.0 - Production on Sun Apr 2 23:16:34 2017


Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.


connected to target database: SHPOG1 (DBID=799280029, not open)


（1）restore 从上次备份的表空间里恢复
RMAN> restore  datafile 8;


Starting restore at 02-APR-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=572 device type=DISK


channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00008 to /oracle/app/oradata/shpog1/hai01.dbf
channel ORA_DISK_1: reading from backup piece /oracle.bacup/tablespace/0gs0mvs0.bak
channel ORA_DISK_1: piece handle=/oracle.bacup/tablespace/0gs0mvs0.bak tag=TAG20170402T201351    #从上次备份的表空间里恢复  
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 02-APR-17
（2）recover   
Recovery Manager complete.
[oracle@oracle1 shpog1]$ sqlplus  / as sysdba


SQL*Plus: Release 11.2.0.1.0 Production on Sun Apr 2 23:17:39 2017


Copyright (c) 1982, 2009, Oracle.  All rights reserved.




Connected to:
Oracle Database 11g Release 11.2.0.1.0 - 64bit Production


（3）打开数据库报错 要recover
SQL> alter  database  open;
alter  database  open
*
ERROR at line 1:
ORA-01113: file 8 needs media recovery
ORA-01110: data file 8: '/oracle/app/oradata/shpog1/hai01.dbf'


SQL> recover  datafile 8;
Media recovery complete.


SQL> alter  database  open;


Database altered.
SQL> conn hai/hai
Connected.


SQL> select  * from hai.feng3;


        ID NAME
---------- ------------------------------------------------------------
         2 haifeng
         3 haifeng2
         4 haifeng3
         5 haifeng4
         6 haifeng5
         7 haifeng5
         8 haifeng6
         9 haifeng8
        10 haifeng8


9 rows selected.


SQL> exit 
Disconnected from Oracle Database 11g Release 11.2.0.1.0 - 64bit Production
[oracle@oracle1 shpog1]$ ll
total 9919560
-rw-r----- 1 oracle oinstall    9748480 Apr  2 23:19 control01.ctl
-rw-r----- 1 oracle oinstall    9748480 Apr  2 23:19 control02.ctl
-rw-r----- 1 oracle oinstall  104865792 Apr  2 23:18 example01.dbf
-rw-r----- 1 oracle oinstall  104865792 Apr  2 23:18 hai01.dbf
-rw-r----- 1 oracle oinstall   52429312 Apr  2 23:18 redo01.log
-rw-r----- 1 oracle oinstall   52429312 Apr  2 23:18 redo02.log
-rw-r----- 1 oracle oinstall   52429312 Apr  2 23:18 redo03.log
-rw-r----- 1 oracle oinstall   52429312 Apr  2 23:19 redo04.log
-rw-r----- 1 oracle oinstall   52429312 Apr  2 23:18 redo05.log
-rw-r----- 1 oracle oinstall 5368717312 Apr  2 23:18 rman_tbs.dbf
-rw-r----- 1 oracle oinstall 1073750016 Apr  2 23:18 sysaux01.dbf
-rw-r----- 1 oracle oinstall 1073750016 Apr  2 23:18 system01.dbf
-rw-r----- 1 oracle oinstall   40902656 Apr  2 15:35 temp01.dbf
-rw-r----- 1 oracle oinstall 1073750016 Apr  2 23:18 undotbs01.dbf
-rw-r----- 1 oracle oinstall 1073750016 Apr  2 23:18 users01.dbf
[oracle@oracle1 shpog1]$