ORACLE归档模式下的热备以及数据库打开状态状态下普通数据文件损坏后的还原

前提：归档模式下开启热备份模式

1.备份数据库
在PL/SQL下执行以下SQL产生启动表空间的热备模式：

select 'alter tablespace ' || tablespace_name || ' begin backup;' from dba_tablespaces where logging = 'LOGGING';

  
  

   
   [oracle@oracledb ~]$ sqlplus / as sysdba
  
  
  
  


  
  
  
  

   
   SQL*Plus: Release 11.2.0.4.0 Production on Fri Jan 20 07:35:51 2017
  
  
  
  


  
  
  
  

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


  
  
  
  

   
   
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
  
  
  
  


  
  
  
  

   
   SQL> alter tablespace SYSTEM begin backup;
alter tablespace SYSAUX begin backup;
alter tablespace UNDOTBS1 begin backup;
alter tablespace USERS begin backup;
alter tablespace BARD begin backup;
alter tablespace BARX begin backup;
Tablespace altered.
  
  
  
  


  
  
  
  

   
   SQL> 
Tablespace altered.
  
  
  
  


  
  
  
  

   
   SQL> 
Tablespace altered.
  
  
  
  


  
  
  
  

   
   SQL> 
Tablespace altered.
  
  
  
  


  
  
  
  

   
   SQL> 
Tablespace altered.

  
  

启动热备模式后复制相关文件进行存储
su - oracle
cp -R /u01/oracle/oradata/orcl/* /u01/oracle/orabak/hotbak/2017-01-19/

完成文件复制后进行end backup操作
su - oracle
sqlplus / as sysdba
alter tablespace SYSTEM end backup;
alter tablespace SYSAUX end backup;
alter tablespace UNDOTBS1 end backup;
alter tablespace USERS end backup;
alter tablespace BARD end backup;
alter tablespace BARX end backup;

2.删除部分数据文件
su - oracle
sqlplus / as sysdba

删除一个数据文件
!rm /u01/oracle/oradata/orcl/bard01.dbf
尝试查询这个表空间上的表，结果能访问数
select count(*) from nvl_user.tmp1;

SQL> !rm /u01/oracle/oradata/orcl/bard01.dbf;

SQL> select count(*) from nvl_user.tmp1;

  COUNT(*)
----------
    300100

做一个全局检查点
alter system checkpoint;

SQL> alter system checkpoint;

System altered.

再次尝试访问表，发现已经无法访问
select count(*) from nvl_user.tmp1;

SQL> select count(*) from nvl_user.tmp1;
select count(*) from nvl_user.tmp1
                              *
ERROR at line 1:
ORA-00376: file 6 cannot be read at this time
ORA-01110: data file 6: '/u01/oracle/oradata/orcl/bard01.dbf'

3.使用备份还原数据库
尝试offline表空间，但因文件丢失，会失败
alter tablespace bard offline;

需进入offline immediate状态：
alter tablespace bard offline immediate;

SQL> alter tablespace bard offline immediate;

Tablespace altered.

从热备中找到对应文件还原
!cp /u01/oracle/orabak/hotbak/2017-01-19/bard01.dbf  /u01/oracle/oradata/orcl/

进行表空间的恢复

SQL> recover tablespace bard;
Media recovery complete.

尝试访问表，发现还是不能访问

SQL> select count(*) from nvl_user.tmp1;
select count(*) from nvl_user.tmp1
                              *
ERROR at line 1:
ORA-00376: file 6 cannot be read at this time
ORA-01110: data file 6: '/u01/oracle/oradata/orcl/bard01.dbf'

将表空间进行online操作
alter tablespace bard online;

再次访问表，可以访问了

SQL> alter tablespace bard online;

Tablespace altered.

SQL> select count(*) from nvl_user.tmp1;

  COUNT(*)
----------
    300100