热门标签 | HotTags
当前位置:  开发笔记 > 数据库 > 正文

Oracle数据库恢复教程之resetlogs操作

这篇文章主要给大家介绍了关于Oracle数据库恢复教程之resetlogs操作的相关资料,文中通过示例代码介绍的非常详细,对大家学习或者使用Oracle数据库具有一定的参考学习价值,需要的朋友们下面来一起学习学习吧

实验环境:RHEL 5.4 + Oracle 11.2.0.3

如果是一名合格的Oracle DBA,对resetlogs这种关键字都应该是极其敏感的,当确认需要这种操作时一定要三思而后行,如果自己不是特别确认,哪怕多花些时间申请去让高级DBA人员协助你一起确认,也不要擅自去尝试执行,避免误操作造成既定损失后追悔莫及。

1.哪些场景可以resetlogs

首先要明确resetlogs操作非常危险的,也只有在进行不完全恢复开库时会使用到。

SQL> alter database open resetlogs;
-> open the database and reset the online logs

官方的描述如下:

Incomplete recovery, also called database point-in-time recovery, results in a noncurrent version of the database. In this case, you do not apply all of the redo generated after the restored backup. Typically, you perform point-in-time database recovery to undo a user error when Flashback Database is not possible.
To perform incomplete recovery, you must restore all data files from backups created before the time to which you want to recover and then open the database with the RESETLOGS option when recovery completes. Resetting the logs creates a new stream of log sequence numbers starting with log sequence 1.

官方的描述其实很清晰,但是实际很多初级DBA小伙伴们在实际工作中遇到这样的场景时却总是有些困惑,甚至误操作引发灾难。

我这里以一个实验来具体说明常见场景:

需求:A机数据库PROD1,现需在B机不同目录下用A机的备份集恢复出来;

A机:

--A机当前current redolog的sequence是57:
SQL> select * from v$log;

 GROUP# THREAD# SEQUENCE#  BYTES BLOCKSIZE MEMBERS ARC STATUS   FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
   1   1   55 52428800  512   1 YES INACTIVE    2051572 19-MAY-19  2060361 19-MAY-19
   2   1   56 52428800  512   1 YES INACTIVE    2060361 19-MAY-19  2060436 19-MAY-19
   3   1   57 52428800  512   1 NO CURRENT    2060436 19-MAY-19 2.8147E+14

--A机做了一次数据库备份:
RMAN> backup database include current controlfile plus archivelog delete all input;


Starting backup at 19-MAY-19
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=23 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=189 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=21 device type=DISK
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=57 RECID=3 STAMP=1008670991
channel ORA_DISK_1: starting piece 1 at 19-MAY-19
channel ORA_DISK_1: finished piece 1 at 19-MAY-19
piece handle=/home/oracle/backup/0cu1u68l_1_1.bak tag=TAG20190519T102315 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_57_860888149.dbf RECID=3 STAMP=1008670991
Finished backup at 19-MAY-19

Starting backup at 19-MAY-19
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00002 name=/u01/app/oracle/oradata/PROD1/sysaux01.dbf
channel ORA_DISK_1: starting piece 1 at 19-MAY-19
channel ORA_DISK_2: starting compressed full datafile backup set
channel ORA_DISK_2: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/PROD1/system01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/PROD1/users01.dbf
channel ORA_DISK_2: starting piece 1 at 19-MAY-19
channel ORA_DISK_3: starting compressed full datafile backup set
channel ORA_DISK_3: specifying datafile(s) in backup set
input datafile file number=00005 name=/u01/app/oracle/oradata/PROD1/example01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/PROD1/undotbs01.dbf
channel ORA_DISK_3: starting piece 1 at 19-MAY-19
channel ORA_DISK_3: finished piece 1 at 19-MAY-19
piece handle=/home/oracle/backup/0fu1u68p_1_1.bak tag=TAG20190519T102319 comment=NONE
channel ORA_DISK_3: backup set complete, elapsed time: 00:00:26
channel ORA_DISK_3: starting compressed full datafile backup set
channel ORA_DISK_3: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_3: starting piece 1 at 19-MAY-19
channel ORA_DISK_3: finished piece 1 at 19-MAY-19
piece handle=/home/oracle/backup/0gu1u69j_1_1.bak tag=TAG20190519T102319 comment=NONE
channel ORA_DISK_3: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: finished piece 1 at 19-MAY-19
piece handle=/home/oracle/backup/0du1u68p_1_1.bak tag=TAG20190519T102319 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:03
channel ORA_DISK_2: finished piece 1 at 19-MAY-19
piece handle=/home/oracle/backup/0eu1u68p_1_1.bak tag=TAG20190519T102319 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:01:23
Finished backup at 19-MAY-19

Starting backup at 19-MAY-19
current log archived
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=58 RECID=4 STAMP=1008671084
channel ORA_DISK_1: starting piece 1 at 19-MAY-19
channel ORA_DISK_1: finished piece 1 at 19-MAY-19
piece handle=/home/oracle/backup/0hu1u6bg_1_1.bak tag=TAG20190519T102446 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch1_58_860888149.dbf RECID=4 STAMP=1008671084
Finished backup at 19-MAY-19

Starting Control File and SPFILE Autobackup at 19-MAY-19
piece handle=/home/oracle/backup/control/c-2082231315-20190519-01 comment=NONE
Finished Control File and SPFILE Autobackup at 19-MAY-19

RMAN> 

--可以看到备份数据库的日志前后都自动归档了当前的redolog(57和58),所以备份完成后,当前日志sequence变为59.
SQL> select * from v$log;

 GROUP# THREAD# SEQUENCE#  BYTES BLOCKSIZE MEMBERS ARC STATUS   FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
   1   1   58 52428800  512   1 YES INACTIVE    2060691 19-MAY-19  2060767 19-MAY-19
   2   1   59 52428800  512   1 NO CURRENT    2060767 19-MAY-19 2.8147E+14
   3   1   57 52428800  512   1 YES INACTIVE    2060436 19-MAY-19  2060691 19-MAY-19

此时把备份集传输到B机,比如/u03/backup目录下,期望恢复到/u03/oradata/PROD1目录下。如果最终只是根据这个备份集去恢复,那最多恢复完sequence 58就结束了,找不到sequence 59(因为59还是当前current的redolog)。Oracle认为这就是最基本的不完全恢复,需要resetlogs操作。

--指定恢复到/u03/oradata/
RMAN> run {
2> set newname for database to '/u03/oradata/PROD1/%U';
3> restore database;
4> }

--切换到上步恢复出来的copy复本:
RMAN> switch database to copy;

datafile 1 switched to datafile copy "/u03/oradata/PROD1/data_D-PROD1_TS-SYSTEM_FNO-1"
datafile 2 switched to datafile copy "/u03/oradata/PROD1/data_D-PROD1_TS-SYSAUX_FNO-2"
datafile 3 switched to datafile copy "/u03/oradata/PROD1/data_D-PROD1_TS-UNDOTBS1_FNO-3"
datafile 4 switched to datafile copy "/u03/oradata/PROD1/data_D-PROD1_TS-USERS_FNO-4"
datafile 5 switched to datafile copy "/u03/oradata/PROD1/data_D-PROD1_TS-EXAMPLE_FNO-5"

--尝试恢复数据库:
RMAN> recover database;

Starting recover at 19-MAY-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=102 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=9 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=112 device type=DISK

starting media recovery

channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=58
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/0hu1u6bg_1_1.bak
channel ORA_DISK_1: errors found reading piece handle=/home/oracle/backup/0hu1u6bg_1_1.bak
channel ORA_DISK_1: failover to piece handle=/u03/backup/0hu1u6bg_1_1.bak tag=TAG20190519T102446
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u01/app/oracle/product/11.2.0/db_1/dbs/arch1_58_860888149.dbf thread=1 sequence=58
unable to find archived log
archived log thread=1 sequence=59
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 05/19/2019 11:04:21
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 59 and starting SCN of 2060767

RMAN>

可以看到最后有报错信息,就是告诉你找不到sequence 59的日志,这是必然的,因为59还是A机current的redo日志。

2.resetlogs前必须确认路径正确

2.1 先查看控制文件和数据文件头记录的scn是否一致

SQL> select checkpoint_change# from v$datafile;

CHECKPOINT_CHANGE#
------------------
   2060767
   2060767
   2060767
   2060767
   2060767

SQL> select checkpoint_change# from v$datafile_header;

CHECKPOINT_CHANGE#
------------------
   2060767
   2060767
   2060767
   2060767
   2060767

2.2 此时如果尝试直接OPEN会报错

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

提示我们开库必须使用RESETLOGS或者NORESETLOGS选项。

2.3 重点来了,现在可以open resetlogs吗?

当然不行!记得一定要确认好路径!!

--查询发现临时文件以及redo日志的路径都不是我们所期望的:
SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u03/oradata/PROD1/data_D-PROD1_TS-SYSTEM_FNO-1
/u03/oradata/PROD1/data_D-PROD1_TS-SYSAUX_FNO-2
/u03/oradata/PROD1/data_D-PROD1_TS-UNDOTBS1_FNO-3
/u03/oradata/PROD1/data_D-PROD1_TS-USERS_FNO-4
/u03/oradata/PROD1/data_D-PROD1_TS-EXAMPLE_FNO-5

SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/PROD1/temp01.dbf

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/PROD1/redo03.log
/u01/app/oracle/oradata/PROD1/redo02.log
/u01/app/oracle/oradata/PROD1/redo01.log

--rename重命名为我们期望的目录:
SQL> alter database rename file '/u01/app/oracle/oradata/PROD1/temp01.dbf' to '/u03/oradata/PROD1/temp01.dbf';

Database altered.

SQL> alter database rename file '/u01/app/oracle/oradata/PROD1/redo01.log' to '/u03/oradata/PROD1/redo01.log';

Database altered.

SQL> alter database rename file '/u01/app/oracle/oradata/PROD1/redo02.log' to '/u03/oradata/PROD1/redo02.log';

Database altered.

SQL> alter database rename file '/u01/app/oracle/oradata/PROD1/redo03.log' to '/u03/oradata/PROD1/redo03.log';

Database altered.

--再次检查确认:
SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
/u03/oradata/PROD1/temp01.dbf

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/u03/oradata/PROD1/redo03.log
/u03/oradata/PROD1/redo02.log
/u03/oradata/PROD1/redo01.log

--最终尝试open开库:
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL> alter database open resetlogs;

Database altered.

总结:

很多初级人员有可能是对set newname for database这个有误解,以为这里的database包括了临时文件,redo日志文件,误以为自己已经把新库所有路径都指向到了期望位置。但实际并不是这样,这也说明了不确认的操作一定要在测试环境测试验证后才可以在生产环境操作。大家可以想象一下,如果是理解有误没确认日志路径直接执行了resetlogs,那么如果B机正好有别的库用到同名的这些路径,亦或是整个恢复操作就是直接在A机的本机其他目录临时基于某个时间点恢复出一套库,那将会是一场大的生产事故。

好了,以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,谢谢大家对的支持。


推荐阅读
  • Hadoop入门与核心组件详解
    本文详细介绍了Hadoop的基础知识及其核心组件,包括HDFS、MapReduce和YARN。通过本文,读者可以全面了解Hadoop的生态系统及应用场景。 ... [详细]
  • 构建基于BERT的中文NL2SQL模型:一个简明的基准
    本文探讨了将自然语言转换为SQL语句(NL2SQL)的任务,这是人工智能领域中一项非常实用的研究方向。文章介绍了笔者在公司举办的首届中文NL2SQL挑战赛中的实践,该比赛提供了金融和通用领域的表格数据,并标注了对应的自然语言与SQL语句对,旨在训练准确的NL2SQL模型。 ... [详细]
  • 深入理解 SQL 视图、存储过程与事务
    本文详细介绍了SQL中的视图、存储过程和事务的概念及应用。视图为用户提供了一种灵活的数据查询方式,存储过程则封装了复杂的SQL逻辑,而事务确保了数据库操作的完整性和一致性。 ... [详细]
  • 本文详细介绍了如何使用ActionScript 3.0 (AS3) 连接并操作MySQL数据库。通过具体的代码示例和步骤说明,帮助开发者理解并实现这一过程。 ... [详细]
  • 在即将迎来26岁生日之际,作者的人生陷入了低谷。经过近三年的硕士学习后,最终决定退学,并且面临没有工作经验的困境。尽管如此,作者依然坚定地选择为自己的人生负责。 ... [详细]
  • 使用Python在SAE上开发新浪微博应用的初步探索
    最近重新审视了新浪云平台(SAE)提供的服务,发现其已支持Python开发。本文将详细介绍如何利用Django框架构建一个简单的新浪微博应用,并分享开发过程中的关键步骤。 ... [详细]
  • 本文详细介绍了美国最具影响力的十大财团,包括洛克菲勒、摩根、花旗银行等。这些财团在历史发展过程中逐渐形成,并对美国的经济、政治和社会产生深远影响。 ... [详细]
  • 基于KVM的SRIOV直通配置及性能测试
    SRIOV介绍、VF直通配置,以及包转发率性能测试小慢哥的原创文章,欢迎转载目录?1.SRIOV介绍?2.环境说明?3.开启SRIOV?4.生成VF?5.VF ... [详细]
  • 深入探讨CPU虚拟化与KVM内存管理
    本文详细介绍了现代服务器架构中的CPU虚拟化技术,包括SMP、NUMA和MPP三种多处理器结构,并深入探讨了KVM的内存虚拟化机制。通过对比不同架构的特点和应用场景,帮助读者理解如何选择最适合的架构以优化性能。 ... [详细]
  • 解决JAX-WS动态客户端工厂弃用问题并迁移到XFire
    在处理Java项目中的JAR包冲突时,我们遇到了JaxWsDynamicClientFactory被弃用的问题,并成功将其迁移到org.codehaus.xfire.client。本文详细介绍了这一过程及解决方案。 ... [详细]
  • 本题通过将每个矩形视为一个节点,根据其相对位置构建拓扑图,并利用深度优先搜索(DFS)或状态压缩动态规划(DP)求解最小涂色次数。本文详细解析了该问题的建模思路与算法实现。 ... [详细]
  • 本题探讨如何通过最大流算法解决农场排水系统的设计问题。题目要求计算从水源点到汇合点的最大水流速率,使用经典的EK(Edmonds-Karp)和Dinic算法进行求解。 ... [详细]
  • 在网页开发中,页面加载速度是一个关键的用户体验因素。为了提升加载效率,避免在PageLoad事件中进行大量数据绑定操作,可以采用异步加载和特定控件来优化页面加载过程。 ... [详细]
  • 本文介绍了一种根据用户选择动态切换屏幕界面的方法,通过定义不同的选择块(Selection Block),实现灵活的用户交互体验。 ... [详细]
  • 本题探讨了在一个有向图中,如何根据特定规则将城市划分为若干个区域,使得每个区域内的城市之间能够相互到达,并且划分的区域数量最少。题目提供了时间限制和内存限制,要求在给定的城市和道路信息下,计算出最少需要划分的区域数量。 ... [详细]
author-avatar
mobiledu2502853397
这个家伙很懒,什么也没留下!
PHP1.CN | 中国最专业的PHP中文社区 | DevBox开发工具箱 | json解析格式化 |PHP资讯 | PHP教程 | 数据库技术 | 服务器技术 | 前端开发技术 | PHP框架 | 开发工具 | 在线工具
Copyright © 1998 - 2020 PHP1.CN. All Rights Reserved | 京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有