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

发布时间：2020-12-12 15:25:13 所属栏目：百科来源：网络整理

导读：前提：归档模式下开启热备份模式 1.备份数据库在PL/SQL下执行以下SQL产生启动表空间的热备模式： select 'alter tablespace ' || tablespace_name || ' begin backup;' from dba_tablespaces where logging = 'LOGGING'; [oracle@oracledb ~]$ sqlplus / as

前提：归档模式下开启热备份模式
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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!