当前位置:  数据库>oracle

在Oracle下我们如何正确的执行数据库恢复

    来源: 互联网  发布时间：2017-06-01

    本文导语: 当数据库需要进行介质恢复时，为了确保数据库能够顺利的执行恢复过程，恢复数据库到当前状态。我们要做的就是验证！验证什么呢？当然是验证备份集和归档是否能够进行有效的恢复。防止我们restore后，执行recover时却发现...

当数据库需要进行介质恢复时，为了确保数据库能够顺利的执行恢复过程，恢复数据库到当前状态。我们要做的就是验证！验证什么呢？当然是验证备份集和归档是否能够进行有效的恢复。防止我们restore后，执行recover时却发现归档缺少了一堆，顿时傻眼。

比方说，在数据库当前日志序列号为3时我们完全备份了数据库。在数据库当前联机日志序列号为13时数据库损坏需要恢复。假设数据库联机日志组为3组，则可以推断数据库联机日志序列号分别为11、12、13。因此当数据库执行restore database后，再执行recover时不难推断数据库需要应用归档3、4、5、6、7、8、9、10以及联机日志11、12、13来进行完全恢复。

为了能够顺利的执行完全恢复，我们在执行恢复前，需要对restore调用的备份集进行恢复验证（语句为：restorevalidate database）以及验证recover过程所需的归档3-10（语句为：restore validate archivelog sequence between 3 and10）。

以完全恢复为例，举例如下：

1数据库当前日志seq号为59，我们备份数据库

SQL> selectgroup#,archived,sequence#,status from v$log;


  GROUP# ARC  SEQUENCE# STATUS
---------- --- ---------- ----------------
      1 YES        58 INACTIVE
      2 NO        59 CURRENT
      3 YES        57 INACTIVE


RMAN> backup database format'/backup/fullbk-%T-%U.bak';     


Starting backup at 2014-02-17 12:03:28
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafilebackup set
channel ORA_DISK_1: specifying datafile(s)in backup set
input datafile file number=00004name=/Oracle/CRM/CRM/users01.dbf
input datafile file number=00001name=/oracle/CRM/CRM/system01.dbf
input datafile file number=00002name=/oracle/CRM/CRM/sysaux01.dbf
input datafile file number=00003name=/oracle/CRM/CRM/undotbs01.dbf
input datafile file number=00005name=/oracle/CRM/CRM/crm.dbf
input datafile file number=00006name=/oracle/CRM/test.dbf
input datafile file number=00008name=/oracle/CRM/jxc.dbf
input datafile file number=00007name=/oracle/CRM/user01.dbf
channel ORA_DISK_1: starting piece 1 at2014-02-17 12:03:29
channel ORA_DISK_1: finished piece 1 at2014-02-17 12:05:57
piecehandle=/backup/fullbk-20140217-3ep0rj0h_1_1.bak tag=TAG20140217T120328 comment=NONE
channel ORA_DISK_1: backup set complete,elapsed time: 00:02:28
channel ORA_DISK_1: starting full datafilebackup set
channel ORA_DISK_1: specifying datafile(s)in backup set
including current control file in backupset
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at2014-02-17 12:06:01
channel ORA_DISK_1: finished piece 1 at2014-02-17 12:06:02
piecehandle=/backup/fullbk-20140217-3fp0rj56_1_1.bak tag=TAG20140217T120328comment=NONE
channel ORA_DISK_1: backup set complete,elapsed time: 00:00:01
Finished backup at 2014-02-17 12:06:02


2 当数据库联机日志为69时数据库崩溃需要进行介质恢复


SQL> selectgroup#,archived,sequence#,status from v$Log;


  GROUP# ARC  SEQUENCE# STATUS
---------- --- ---------- ----------------
      1 YES        67 INACTIVE
      2 YES        68 INACTIVE
      3 NO          69 CURRENT


注意：这里其实我们可以推断，如果数据库需要恢复到当前状态，那么归档59到归档66的所有归档，必须能够进行有效的恢复。我们只需要发起restore database preview命令，Oracle便可以给出我们归档列表，继续往下看。

 


3 判定当前数据库恢复所需要备份集和归档条目


注意对于restore database preview列出的归档条目，recover执行完全恢复时并不会完全应用，因为完全恢复recover过程是：应用相关归档+ 所有联机日志，seq号从小到大依次应用。后面会抓取recover过程，这里先暂且提一下。


RMAN> restore database preview;


Starting restore at 2014-02-17 16:14:21
using target database control file insteadof recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=63 device type=DISK

 


List of Backup Sets
===================

 


BS Key Type LV Size      Device TypeElapsed Time Completion Time   
------- ---- -- ---------- ----------------------- -------------------
108    Full    2.03G      DISK        00:02:26    2014-02-17 12:05:38
      BP Key: 108  Status:AVAILABLE  Compressed: NO  Tag: TAG20140217T120328
Piece Name:/backup/fullbk-20140217-3ep0rj0h_1_1.bak 
注意：这里显示备份片总是rman资料库中记录的数据文件最新的备份
List of Datafiles in backup set 108
File LV Type Ckp SCN    CkpTime            Name
---- -- ---- ---------- ------------------- ----
1      Full 4028039    2014-02-17 12:03:29/oracle/CRM/CRM/system01.dbf
2      Full 4028039    2014-02-17 12:03:29/oracle/CRM/CRM/sysaux01.dbf
3      Full 4028039    2014-02-17 12:03:29/oracle/CRM/CRM/undotbs01.dbf
4      Full 4028039    2014-02-17 12:03:29/oracle/CRM/CRM/users01.dbf
5      Full 4028039    2014-02-17 12:03:29 /oracle/CRM/CRM/crm.dbf
6      Full 4028039    2014-02-17 12:03:29 /oracle/CRM/test.dbf
7      Full 4028039    2014-02-17 12:03:29 /oracle/CRM/user01.dbf
8      Full 4028039    2014-02-17 12:03:29 /oracle/CRM/jxc.dbf
using channel ORA_DISK_1


List of Archived Log Copies for databasewith db_unique_name CRM
=====================================================================


Key    Thrd Seq    S Low Time         
------- ---- ------- - -------------------
131    1    59      A 2014-02-17 11:55:37
Name:/oracle/archivelog/arch_1_59_839098938.arch


132    1    60      A 2014-02-17 12:10:20
Name:/oracle/archivelog/arch_1_60_839098938.arch


133    1    61      A 2014-02-17 12:10:21
Name:/oracle/archivelog/arch_1_61_839098938.arch


134    1    62      A 2014-02-17 12:10:26
Name:/oracle/archivelog/arch_1_62_839098938.arch


135    1    63      A 2014-02-17 12:10:30
Name:/oracle/archivelog/arch_1_63_839098938.arch


136    1    64      A 2014-02-17 12:10:31
Name:/oracle/archivelog/arch_1_64_839098938.arch


137    1    65      A 2014-02-17 12:10:32
Name:/oracle/archivelog/arch_1_65_839098938.arch


138    1    66      A 2014-02-17 12:10:33
Name:/oracle/archivelog/arch_1_66_839098938.arch


139    1    67      A 2014-02-17 12:10:34
 Name:/oracle/archivelog/arch_1_67_839098938.arch


140    1    68      A 2014-02-17 12:10:36
Name:/oracle/archivelog/arch_1_68_839098938.arch


Media recovery start SCN is 4028039
Recovery must be done beyond SCN 4028039 toclear datafile fuzziness
Finished restore at 2014-02-17 16:14:24


注意：
1 上面seq号这一列显示的最后一个归档seq为68（从前面可知数据库当前联机日志文件seq号为69）也就是说restore database preview显示的归档列表结果中最后一个归档seq号总是比当前联机日志（当前联机日志也就是查看v$log状态为currnt的日志组）文件seq号小于1.


2  结合当前数据库的联机日志组seq号分别为67 68 69，可以判断：在recover应用最后一个归档seq号为66后，oracle会读取seq号为67、68、69联机日志文件继续推进该数据库来实现整个数据库完全恢复过程。
下面将演示整个验证和恢复过程:


4 验证恢复时需要用到的备份集是否能够正常恢复。


RMAN> restore validate database;


注意：这条命令直接会去rman资料库中找最新的备份集进行验证，也就是restore database preview命令显示的备份集。


Starting restore at 2014-02-17 16:14:59
using channel ORA_DISK_1


channel ORA_DISK_1: starting validation ofdatafile backup set
channel ORA_DISK_1: reading from backuppiece /backup/fullbk-20140217-3ep0rj0h_1_1.bak
channel ORA_DISK_1: piecehandle=/backup/fullbk-20140217-3ep0rj0h_1_1.bak tag=TAG20140217T120328
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: validation complete,elapsed time: 00:00:36
Finished restore at 2014-02-17 16:15:35

 


5 验证恢复时应用的归档

 


RMAN> restore validate archivelogsequence between 59 and 66;


Starting restore at 2014-02-17 16:16:34
using channel ORA_DISK_1


channel ORA_DISK_1: scanning archived log /oracle/archivelog/arch_1_59_839098938.arch
channel ORA_DISK_1: scanning archived log/oracle/archivelog/arch_1_60_839098938.arch
channel ORA_DISK_1: scanning archived log/oracle/archivelog/arch_1_61_839098938.arch
channel ORA_DISK_1: scanning archived log/oracle/archivelog/arch_1_62_839098938.arch
channel ORA_DISK_1: scanning archived log/oracle/archivelog/arch_1_63_839098938.arch
channel ORA_DISK_1: scanning archived log/oracle/archivelog/arch_1_64_839098938.arch
channel ORA_DISK_1: scanning archived log/oracle/archivelog/arch_1_65_839098938.arch
channel ORA_DISK_1: scanning archived log/oracle/archivelog/arch_1_66_839098938.arch
Finished restore at 2014-02-17 16:16:37

 

 


6 执行restore和recover过程如下

 


RMAN> restore database;


Starting restore at 2014-02-17 16:36:23
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=63 device type=DISK


channel ORA_DISK_1: starting datafilebackup set restore
channel ORA_DISK_1: specifying datafile(s)to restore from backup set
channel ORA_DISK_1: restoring datafile 00001to /oracle/CRM/CRM/system01.dbf
channel ORA_DISK_1: restoring datafile00002 to /oracle/CRM/CRM/sysaux01.dbf
channel ORA_DISK_1: restoring datafile00003 to /oracle/CRM/CRM/undotbs01.dbf
channel ORA_DISK_1: restoring datafile00004 to /oracle/CRM/CRM/users01.dbf
channel ORA_DISK_1: restoring datafile00005 to /oracle/CRM/CRM/crm.dbf
channel ORA_DISK_1: restoring datafile00006 to /oracle/CRM/test.dbf
channel ORA_DISK_1: restoring datafile00007 to /oracle/CRM/user01.dbf
channel ORA_DISK_1: restoring datafile00008 to /oracle/CRM/jxc.dbf
channel ORA_DISK_1: reading from backuppiece /backup/fullbk-20140217-3ep0rj0h_1_1.bak
channel ORA_DISK_1: piecehandle=/backup/fullbk-20140217-3ep0rj0h_1_1.bak tag=TAG20140217T120328
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete,elapsed time: 00:02:08
Finished restore at 2014-02-17 16:38:35


注意：restore后我们通过查询x$kcvfh的redo字节地址(RBA)的seq号(也就是是FHRBA_SEQ字段)可以得到restore database 后数据文件头部记录的rba.seq号, 该值近一步表明recover过程需要从seq号为59归档开始应用。


或者也可以从restore database后数据文件头部的scn值，对比归档的first_change# 和 next_change# 推断出recover 需要应用归档开始。


SQL> select hxfil,fhscn,fhrba_seq fromx$kcvfh;


  HXFIL FHSCN            FHRBA_SEQ
---------- ---------------- ----------
      1 4028039                  59
      2 4028039                59
      3 4028039                  59
      4 4028039                  59
      5 4028039                  59
      6 4028039                  59
      7 4028039                  59
      8 4028039                  59


8 rows selected.


当然restore database 后，我们也可以直接查询v$recvoery_log来得到recover过程需要应用的归档条目，如下所示：
select * from v$recovery_log;


 THREAD#  SEQUENCE# TIME      ARCHIVE_NAME
---------- ---------- --------- ------------------------------------------------------------
      1        59 17-FEB-14/oracle/archivelog/arch_1_59_839098938.arch
      1        60 17-FEB-14/oracle/archivelog/arch_1_60_839098938.arch
      1        61 17-FEB-14 /oracle/archivelog/arch_1_61_839098938.arch
      1        62 17-FEB-14/oracle/archivelog/arch_1_62_839098938.arch
      1        63 17-FEB-14/oracle/archivelog/arch_1_63_839098938.arch
      1        64 17-FEB-14/oracle/archivelog/arch_1_64_839098938.arch
      1        65 17-FEB-14/oracle/archivelog/arch_1_65_839098938.arch
      1        66 17-FEB-14/oracle/archivelog/arch_1_66_839098938.arch

 


RMAN> recover database;


Starting recover at 2014-02-17 16:45:01
using channel ORA_DISK_1


starting media recovery


archived log for thread 1 with sequence 59is already on disk as file /oracle/archivelog/arch_1_59_839098938.arch
archived log for thread 1 with sequence 60is already on disk as file /oracle/archivelog/arch_1_60_839098938.arch
archived log for thread 1 with sequence 61is already on disk as file /oracle/archivelog/arch_1_61_839098938.arch
archived log for thread 1 with sequence 62is already on disk as file /oracle/archivelog/arch_1_62_839098938.arch
archived log for thread 1 with sequence 63is already on disk as file /oracle/archivelog/arch_1_63_839098938.arch
archived log for thread 1 with sequence 64is already on disk as file /oracle/archivelog/arch_1_64_839098938.arch
archived log for thread 1 with sequence 65is already on disk as file /oracle/archivelog/arch_1_65_839098938.arch
archived log for thread 1 with sequence 66is already on disk as file /oracle/archivelog/arch_1_66_839098938.arch
archived log for thread 1 with sequence 67is already on disk as file /oracle/archivelog/arch_1_67_839098938.arch
archived log for thread 1 with sequence 68is already on disk as file /oracle/archivelog/arch_1_68_839098938.arch
archived log filename=/oracle/archivelog/arch_1_59_839098938.arch thread=1 sequence=59
archived log file name=/oracle/archivelog/arch_1_60_839098938.archthread=1 sequence=60
archived log filename=/oracle/archivelog/arch_1_61_839098938.arch thread=1 sequence=61
archived log filename=/oracle/archivelog/arch_1_62_839098938.arch thread=1 sequence=62
archived log filename=/oracle/archivelog/arch_1_63_839098938.arch thread=1 sequence=63
archived log filename=/oracle/archivelog/arch_1_64_839098938.arch thread=1 sequence=64
archived log filename=/oracle/archivelog/arch_1_65_839098938.arch thread=1 sequence=65
archived log filename=/oracle/archivelog/arch_1_66_839098938.arch thread=1 sequence=66
media recovery complete, elapsed time:00:00:08
Finished recover at 2014-02-17 16:45:16
注意：这里可以清楚的看到应用的归档条目（红色标记处）


7 跟踪recover过程内容如下：


alter database recoverlogfile '/oracle/archivelog/arch_1_59_839098938.arch'
Media Recovery Log/oracle/archivelog/arch_1_59_839098938.arch
Mon Feb 17 16:45:12 2014
ORA-279 signalled during: alter databaserecover logfile '/oracle/archivelog/arch_1_59_839098938.arch'...
alter database recoverlogfile '/oracle/archivelog/arch_1_60_839098938.arch'
Media Recovery Log/oracle/archivelog/arch_1_60_839098938.arch
ORA-279 signalled during: alter databaserecover logfile '/oracle/archivelog/arch_1_60_839098938.arch'...
alter database recoverlogfile '/oracle/archivelog/arch_1_61_839098938.arch'
Media Recovery Log/oracle/archivelog/arch_1_61_839098938.arch


ORA-279 signalled during: alter databaserecover logfile '/oracle/archivelog/arch_1_61_839098938.arch'...
alter database recoverlogfile '/oracle/archivelog/arch_1_62_839098938.arch'
Media Recovery Log/oracle/archivelog/arch_1_62_839098938.arch
ORA-279 signalled during: alter databaserecover logfile '/oracle/archivelog/arch_1_62_839098938.arch'...
alter database recoverlogfile '/oracle/archivelog/arch_1_63_839098938.arch'
Media Recovery Log/oracle/archivelog/arch_1_63_839098938.arch
ORA-279 signalled during: alter databaserecover logfile '/oracle/archivelog/arch_1_63_839098938.arch'...
alter database recoverlogfile '/oracle/archivelog/arch_1_64_839098938.arch'
Media Recovery Log/oracle/archivelog/arch_1_64_839098938.arch
ORA-279 signalled during: alter databaserecover logfile '/oracle/archivelog/arch_1_64_839098938.arch'...
alter database recoverlogfile '/oracle/archivelog/arch_1_65_839098938.arch'
Media Recovery Log/oracle/archivelog/arch_1_65_839098938.arch
ORA-279 signalled during: alter databaserecover logfile '/oracle/archivelog/arch_1_65_839098938.arch'...
alter database recoverlogfile '/oracle/archivelog/arch_1_66_839098938.arch'
Media Recovery Log/oracle/archivelog/arch_1_66_839098938.arch
Mon Feb 17 16:45:14 2014
Recovery of Online RedoLog: Thread 1 Group 1 Seq 67 Reading mem 0
Mem# 0: /oracle/CRM/CRM/redo01.log
Recovery of Online RedoLog: Thread 1 Group 2 Seq 68 Reading mem 0
Mem# 0: /oracle/CRM/CRM/redo02.log
Recovery of Online RedoLog: Thread 1 Group 3 Seq 69 Reading mem 0
Mem# 0: /oracle/CRM/CRM/redo03.log
Media Recovery Complete(CRM)


注意：通过跟踪整个恢复过程，可以清楚的观察到在用recover进行完全恢复时，先应用归档，后再通过所有联机日志文件推进整个数据库来实现完全恢复的过程。

 


8 如果数据库进行不完全恢复如何获取恢复所需要的归档


以基于时间点恢复为例，我们可以这么使用得出恢复到这个时间点数据库需要的归档列表。


run{
sql 'alter session setnls_date_format="yyyy-mm-dd hh24:mi:ss"';
set until time='2013-12-09:05:50:12';
restore database preview;
}


总结：
1 在对数据库进行恢复的时，第一步先看看数据库是否归档，第二步看看数据库是否有备份，第三步验证备份和归档的有效性。最后执行整个恢复过程。


2 完全恢复时，通过对比restore database preview 显示的归档列表seq号和联机日志组的seq号，我们便可以清楚的推出数据库完全恢复时，recover需要应用的归档。


    
 
 

您可能感兴趣的文章:

  • 怎么写一个Shell来执行这样的功能，访问Oracle数据库，然后执行一个SQL脚本，生成一个文件。急！
  • win2000+jbuilder6+oracle817编出的程序，在win2000下执行很好，在win98下却访问不了oracle数据库
  • oracle 可以在crontab 中定时执行吗？
  • oracle sql执行过程(流程图)
  • linux能够通过执行脚本添加oracle数据库的用户吗
  • ORACLE安装时/tmp/orainstRoot.sh 执行发生错误
  • 求救：HPUNIX下的ORACLE7执行select * from tablename提示权限不足！！
  • 为什么 export ORACLE_SID=test写在程序里面就不会执行？
  • 请问在 Linux 下如何用代码实现连接oracle数据库 并 执行 SQL 语句？
  • ORACLE安装时/tmp/orainstRoot.sh 执行发生错误 iis7站长之家
  • shell 执行oracle sql脚本的问题
  • oracle单库彻底删除干净的执行步骤
  • 查看Oracle的执行计划一句话命令
  • 关于ORACLE中执行批处理的问题
  • oracle中得到一条SQL语句的执行时间的两种方式
  • oracle导出sql语句的结果集和保存执行的sql语句(深入分析)
  • Nagios check_oracle_health 关于执行SQL问题
  • 执行Commit时Oracle做哪些工作
  • RedHat AS 4 安装oracle9i的时候，执行Disk1下的runInstaller后提示正在初始化虚拟机，请等待后就再无反应
  • JBUILDER如何执行ORACLE的储存过程
  • Oracle数据库恢复后心得
  • 关于系统重装后Oracle数据库完全恢复的解决办法
  • oracle drop table(表)数据恢复方法
  • Oracle数据库备份和恢复工具 RMan
  • oracle备份恢复的具体方法
  • oracle中误删除表后恢复语句(FLASHBACK)
  • oracle冷备份恢复和oracle异机恢复使用方法
  • 在Oracle 10gR2中设定指定的恢复点实现轻松闪回
  • Oracle重做日志文件损坏或丢失后的恢复
  • Oracle11g备份和恢复功能的提高
  • 实战Oracle数据库备份与恢复
    •  
    本站(WWW.)旨在分享和传播互联网科技相关的资讯和技术，将尽最大努力为读者提供更好的信息聚合和浏览方式。
    本站(WWW.)站内文章除注明原创外，均为转载、整理或搜集自网络。欢迎任何形式的转载，转载请注明出处。












  • 相关文章推荐
  • Oracle 数据库(oracle Database)Select 多表关联查询方式
  • Oracle 数据库开发工具 Oracle SQL Developer
  • Oracle数据库(Oracle Database)体系结构及基本组成介绍
  • 请问大家用oracle数据库, 用import oracle.*;下的东西么? 还是用标准库?
  • Oracle 数据库(oracle Database)性能调优技术详解
  • 关于JDBC连接Oracle数据库，是否必须有Oracle客户端
  • ORACLE数据库常用字段数据类型介绍
  • oracle数据库导出和oracle导入数据的二种方法(oracle导入导出数据)
  • Oracle发布Oracle SQL Developer 1.2数据库开发工具 帮助用户简化开发工作
  • 怎样调出ORACLE数据库中的数据,该如何连接?
  • Oracle收购TimesTen 提高数据库软件性能
  • 卸载oracle数据库
  • linux上安装oracle 数据库后，是否能写shell程序实现数据库的自动启动。
  • Linux下如何用C语言操作Oracle数据库相关的图书推荐
  • Oracle数据库运行Oracle form时避免出现提示信息
  • Oracle欲收购开源数据库MySQL未果
  • 如何在JBuilder中连接Oracle数据库？
  • Oracle数据库访问参数文件的顺序
  • 循序渐进学习Oracle数据库
  • 安装Oracle加载数据库错误areasQueries的解决
  • Jbuilder 7.0 连接 Oracle 数据库
  • Oracle 12c发布简单介绍及官方下载地址
  • 在linux下安装oracle,如何设置让oracle自动启动！也就是让oracle那个服务自动启动，不是手动的
  • oracle 11g最新版官方下载地址
  • 请问su oracle 和su - oracle有什么不同？
  • 如何设置让Oracle SQL Developer显示的时间包含时分秒
  • 虚拟机装Oracle R12与Oracle10g
  • Oracle 10g和Oracle 11g网格技术介绍
  • Oracle EBS R12 支持 Oracle Database 11g
  • oracle中如何把表中具有相同值列的多行数据合并成一行
  • SCO unix下安装oracle，但没有光盘，请大家推荐一个oracle下载站点（unix版本的）。谢谢！！！！


    • 站内导航:


    特别声明:169IT网站部分信息来自互联网,如果侵犯您的权利,请及时告知,本站将立即删除！

    ©2012-2021,