前提:归档模式下开启热备份模式
1.备份数据库
在PL/SQL下执行以下SQL产生启动表空间的热备模式:
在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/
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;
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;
!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;
alter system checkpoint;
SQL> alter system checkpoint;
System altered.
再次尝试访问表,发现已经无法访问
select count(*) from nvl_user.tmp1;
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;
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/
!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;
alter tablespace bard online;
再次访问表,可以访问了
SQL> alter tablespace bard online;
Tablespace altered.
SQL> select count(*) from nvl_user.tmp1;
COUNT(*)
----------
300100