Oracle Incomplete数据库恢复: 使用restore point

2024-02-04 13:18
文章标签 oracle 使用 恢复 数据库 point restore incomplete

本文主要是介绍Oracle Incomplete数据库恢复: 使用restore point，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

环境: Oracle数据库12.2.0.1，CDB=orclcdb，PDB=orclpdb1

You can’t perform an incomplete database recovery on a subset of your database’s online data files

首先全备数据库：

RMAN> backup database plus archivelog;Starting backup at 25-APR-19
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=258 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=6 RECID=23 STAMP=1006429277
input archived log thread=1 sequence=7 RECID=24 STAMP=1006530361
input archived log thread=1 sequence=8 RECID=25 STAMP=1006530370
input archived log thread=1 sequence=9 RECID=26 STAMP=1006531303
channel ORA_DISK_1: starting piece 1 at 25-APR-19
channel ORA_DISK_1: finished piece 1 at 25-APR-19
piece handle=/u01/fra/ORCLCDB/backupset/2019_04_25/o1_mf_annnn_TAG20190425T160144_gd2tc8t7_.bkp tag=TAG20190425T160144 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 25-APR-19Starting backup at 25-APR-19
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/opt/oracle/oradata/ORCLCDB/system01.dbf
input datafile file number=00003 name=/opt/oracle/oradata/ORCLCDB/sysaux01.dbf
input datafile file number=00004 name=/opt/oracle/oradata/ORCLCDB/undotbs01.dbf
input datafile file number=00007 name=/opt/oracle/oradata/ORCLCDB/users01.dbf
channel ORA_DISK_1: starting piece 1 at 25-APR-19
channel ORA_DISK_1: finished piece 1 at 25-APR-19
piece handle=/u01/fra/ORCLCDB/backupset/2019_04_25/o1_mf_nnndf_TAG20190425T160146_gd2tcbc7_.bkp tag=TAG20190425T160146 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00010 name=/opt/oracle/oradata/ORCLCDB/ORCLPDB1/sysaux01.dbf
input datafile file number=00009 name=/opt/oracle/oradata/ORCLCDB/ORCLPDB1/system01.dbf
input datafile file number=00011 name=/opt/oracle/oradata/ORCLCDB/ORCLPDB1/undotbs01.dbf
input datafile file number=00012 name=/opt/oracle/oradata/ORCLCDB/ORCLPDB1/users01.dbf
channel ORA_DISK_1: starting piece 1 at 25-APR-19
channel ORA_DISK_1: finished piece 1 at 25-APR-19
piece handle=/u01/fra/ORCLCDB/85ED2A1B333E762BE0530100007F0333/backupset/2019_04_25/o1_mf_nnndf_TAG20190425T160146_gd2tcsnt_.bkp tag=TAG20190425T160146 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=/opt/oracle/oradata/ORCLCDB/pdbseed/sysaux01.dbf
input datafile file number=00005 name=/opt/oracle/oradata/ORCLCDB/pdbseed/system01.dbf
input datafile file number=00008 name=/opt/oracle/oradata/ORCLCDB/pdbseed/undotbs01.dbf
channel ORA_DISK_1: starting piece 1 at 25-APR-19
channel ORA_DISK_1: finished piece 1 at 25-APR-19
piece handle=/u01/fra/ORCLCDB/85ED1CD129746EA8E0530100007FAF27/backupset/2019_04_25/o1_mf_nnndf_TAG20190425T160146_gd2tcwz4_.bkp tag=TAG20190425T160146 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 25-APR-19Starting backup at 25-APR-19
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=10 RECID=27 STAMP=1006531328
channel ORA_DISK_1: starting piece 1 at 25-APR-19
channel ORA_DISK_1: finished piece 1 at 25-APR-19
piece handle=/u01/fra/ORCLCDB/backupset/2019_04_25/o1_mf_annnn_TAG20190425T160208_gd2td0ho_.bkp tag=TAG20190425T160208 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 25-APR-19Starting Control File and SPFILE Autobackup at 25-APR-19
piece handle=/u01/fra/ORCLCDB/autobackup/2019_04_25/o1_mf_s_1006531329_gd2td290_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 25-APR-19

然后创建恢复点：

RMAN> create restore point MY_RP;Statement processedRMAN> select current_scn from v$database;CURRENT_SCN
-----------2470073RMAN> select name, scn from v$restore_point;NAME
--------------------------------------------------------------------------------SCN
----------MY_RP2470052

插入一条新数据：

SQL> alter session set container=orclpdb1;Session altered.SQL> select * from foo;FOO
----------12SQL> insert into foo values(3);1 row created.SQL> commit;Commit complete.

进行恢复：

RMAN> shutdown immediate;database closed
database dismounted
Oracle instance shut downRMAN> startup mount;connected to target database (not started)
Oracle instance started
database mountedTotal System Global Area    1207959552 bytesFixed Size                     8792152 bytes
Variable Size                436209576 bytes
Database Buffers             754974720 bytes
Redo Buffers                   7983104 bytesRMAN> restore database until restore point MY_RP;Starting restore at 25-APR-19
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=255 device type=DISKskipping datafile 5; already restored to file /opt/oracle/oradata/ORCLCDB/pdbseed/system01.dbf
skipping datafile 6; already restored to file /opt/oracle/oradata/ORCLCDB/pdbseed/sysaux01.dbf
skipping datafile 8; already restored to file /opt/oracle/oradata/ORCLCDB/pdbseed/undotbs01.dbf
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /opt/oracle/oradata/ORCLCDB/system01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /opt/oracle/oradata/ORCLCDB/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /opt/oracle/oradata/ORCLCDB/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00007 to /opt/oracle/oradata/ORCLCDB/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/fra/ORCLCDB/backupset/2019_04_25/o1_mf_nnndf_TAG20190425T160146_gd2tcbc7_.bkp
channel ORA_DISK_1: piece handle=/u01/fra/ORCLCDB/backupset/2019_04_25/o1_mf_nnndf_TAG20190425T160146_gd2tcbc7_.bkp tag=TAG20190425T160146
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00009 to /opt/oracle/oradata/ORCLCDB/ORCLPDB1/system01.dbf
channel ORA_DISK_1: restoring datafile 00010 to /opt/oracle/oradata/ORCLCDB/ORCLPDB1/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00011 to /opt/oracle/oradata/ORCLCDB/ORCLPDB1/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00012 to /opt/oracle/oradata/ORCLCDB/ORCLPDB1/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/fra/ORCLCDB/85ED2A1B333E762BE0530100007F0333/backupset/2019_04_25/o1_mf_nnndf_TAG20190425T160146_gd2tcsnt_.bkp
channel ORA_DISK_1: piece handle=/u01/fra/ORCLCDB/85ED2A1B333E762BE0530100007F0333/backupset/2019_04_25/o1_mf_nnndf_TAG20190425T160146_gd2tcsnt_.bkp tag=TAG20190425T160146
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 25-APR-19RMAN> recover database until restore point MY_RP;Starting recover at 25-APR-19
using channel ORA_DISK_1starting media recovery
media recovery complete, elapsed time: 00:00:01Finished recover at 25-APR-19RMAN> alter database open resetlogs;Statement processedRMAN> alter session set container=orclpdb1;RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 04/25/2019 16:10:50
RMAN-06815: cannot change the container in RMAN session.

验证时间点恢复成功：

SQL> alter session set container=orclpdb1;Session altered.SQL> select * from foo;FOO
----------12

再做一次，这次在PDB中建立restore point:

SQL> show con_name;CON_NAME
------------------------------
ORCLPDB1
SQL> create restore point MY_RP;Restore point created.SQL> insert into foo values(3);1 row created.SQL> select * from foo;FOO
----------312SQL> select current_scn from v$database;CURRENT_SCN
-----------2471694SQL> select name, scn from v$restore_point;NAME
--------------------------------------------------------------------------------SCN
----------
MY_RP2470052MY_RP2471498

目前为止，我们有两个restore point了：

select * from v$restore_point;

以下为输出：在这里插入图片描述
开始恢复，注意是直接连接到PDB：

[oracle@oracle-12201-vagrant ~]$ rman target sys/Abcdef_123456@orclpdb1Recovery Manager: Release 12.2.0.1.0 - Production on Thu Apr 25 16:27:08 2019Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.connected to target database: ORCLCDB:ORCLPDB1 (DBID=2352397633)RMAN> shutdown immediate;using target database control file instead of recovery catalog
database closedRMAN> startup mount;RMAN> restore database until restore point MY_RP;Starting restore at 25-APR-19
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=38 device type=DISKchannel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00009 to /opt/oracle/oradata/ORCLCDB/ORCLPDB1/system01.dbf
channel ORA_DISK_1: restoring datafile 00010 to /opt/oracle/oradata/ORCLCDB/ORCLPDB1/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00011 to /opt/oracle/oradata/ORCLCDB/ORCLPDB1/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00012 to /opt/oracle/oradata/ORCLCDB/ORCLPDB1/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/fra/ORCLCDB/85ED2A1B333E762BE0530100007F0333/backupset/2019_04_25/o1_mf_nnndf_TAG20190425T160146_gd2tcsnt_.bkp
channel ORA_DISK_1: piece handle=/u01/fra/ORCLCDB/85ED2A1B333E762BE0530100007F0333/backupset/2019_04_25/o1_mf_nnndf_TAG20190425T160146_gd2tcsnt_.bkp tag=TAG20190425T160146
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:16
Finished restore at 25-APR-19RMAN> recover database until restore point MY_RP;Starting recover at 25-APR-19
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 04/25/2019 16:28:48
RMAN-07536: command not allowed when connected to a Pluggable Database

restore成功了，但recover失败。因为不允许在PDB中执行。
只好重新连接到根容器，此时recover成功：

[oracle@oracle-12201-vagrant ~]$ rlwrap rman target /Recovery Manager: Release 12.2.0.1.0 - Production on Thu Apr 25 16:32:22 2019Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.connected to target database: ORCLCDB (DBID=2776037359)RMAN> recover pluggable database orclpdb1 until restore point MY_RP;Starting recover at 25-APR-19
using target database control file instead of recovery catalog
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=17 device type=DISKstarting media recoveryarchived log for thread 1 with sequence 10 is already on disk as file /u01/fra/ORCLCDB/archivelog/2019_04_25/o1_mf_1_10_gd2td039_.arc
archived log for thread 1 with sequence 11 is already on disk as file /u01/fra/ORCLCDB/archivelog/2019_04_25/o1_mf_1_11_gd2tvbq3_.arc
archived log for thread 1 with sequence 1 is already on disk as file /u01/fra/ORCLCDB/archivelog/2019_04_25/o1_mf_1_1_gd2w5nsx_.arc
media recovery complete, elapsed time: 00:00:00
Finished recover at 25-APR-19

然后打开PDB：

RMAN> alter pluggable database orclpdb1 open resetlogs;Statement processed

验证恢复成功：

SQL> alter session set container=orclpdb1;Session altered.SQL> select * from foo;FOO
----------12

新插入的数据3缺省不见了。

实验结论为：

  1. 可以为每一个数据库，包括CDB和PDB创建各自的restore point
  2. 可以为单独的数据库进行incomplete恢复

这篇关于Oracle Incomplete数据库恢复: 使用restore point的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/677631。

相关文章

SQL Server修改数据库名及物理数据文件名操作步骤

SQL Server修改数据库名及物理数据文件名操作步骤

《SQLServer修改数据库名及物理数据文件名操作步骤》在SQLServer中重命名数据库是一个常见的操作,但需要确保用户具有足够的权限来执行此操作,：本文主要介绍SQLServer修改数据... 目录一、背景介绍二、操作步骤2.1 设置为单用户模式（断开连接）2.2 修改数据库名称2.3 查找逻辑文件名
阅读更多...
Python常用命令提示符使用方法详解

Python常用命令提示符使用方法详解

《Python常用命令提示符使用方法详解》在学习python的过程中,我们需要用到命令提示符(CMD)进行环境的配置,：本文主要介绍Python常用命令提示符使用方法的相关资料,文中通过代码介绍的... 目录一、python环境基础命令【Windows】1、检查Python是否安装2、 查看Python的安
阅读更多...
SQL Server数据库死锁处理超详细攻略

SQL Server数据库死锁处理超详细攻略

《SQLServer数据库死锁处理超详细攻略》SQLServer作为主流数据库管理系统,在高并发场景下可能面临死锁问题,影响系统性能和稳定性,这篇文章主要给大家介绍了关于SQLServer数据库死... 目录一、引言二、查询 Sqlserver 中造成死锁的 SPID三、用内置函数查询执行信息1. sp_w
阅读更多...
Python并行处理实战之如何使用ProcessPoolExecutor加速计算

Python并行处理实战之如何使用ProcessPoolExecutor加速计算

《Python并行处理实战之如何使用ProcessPoolExecutor加速计算》Python提供了多种并行处理的方式,其中concurrent.futures模块的ProcessPoolExecu... 目录简介完整代码示例代码解释1. 导入必要的模块2. 定义处理函数3. 主函数4. 生成数字列表5.
阅读更多...
Python中help()和dir()函数的使用

Python中help()和dir()函数的使用

《Python中help()和dir()函数的使用》我们经常需要查看某个对象（如模块、类、函数等）的属性和方法,Python提供了两个内置函数help()和dir(),它们可以帮助我们快速了解代... 目录1. 引言2. help() 函数2.1 作用2.2 使用方法2.3 示例(1) 查看内置函数的帮助(
阅读更多...
Linux脚本(shell)的使用方式

Linux脚本(shell)的使用方式

《Linux脚本(shell)的使用方式》：本文主要介绍Linux脚本(shell)的使用方式,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录概述语法详解数学运算表达式Shell变量变量分类环境变量Shell内部变量自定义变量：定义、赋值自定义变量：引用、修改、删
阅读更多...
Java使用HttpClient实现图片下载与本地保存功能

Java使用HttpClient实现图片下载与本地保存功能

《Java使用HttpClient实现图片下载与本地保存功能》在当今数字化时代,网络资源的获取与处理已成为软件开发中的常见需求,其中,图片作为网络上最常见的资源之一,其下载与保存功能在许多应用场景中都... 目录引言一、Apache HttpClient简介二、技术栈与环境准备三、实现图片下载与保存功能1.
阅读更多...
Python中使用uv创建环境及原理举例详解

Python中使用uv创建环境及原理举例详解

《Python中使用uv创建环境及原理举例详解》uv是Astral团队开发的高性能Python工具,整合包管理、虚拟环境、Python版本控制等功能,：本文主要介绍Python中使用uv创建环境及... 目录一、uv工具简介核心特点：二、安装uv1. 通过pip安装2. 通过脚本安装验证安装：配置镜像源（可
阅读更多...
LiteFlow轻量级工作流引擎使用示例详解

LiteFlow轻量级工作流引擎使用示例详解

《LiteFlow轻量级工作流引擎使用示例详解》：本文主要介绍LiteFlow是一个灵活、简洁且轻量的工作流引擎,适合用于中小型项目和微服务架构中的流程编排,本文给大家介绍LiteFlow轻量级工... 目录1. LiteFlow 主要特点2. 工作流定义方式3. LiteFlow 流程示例4. LiteF
阅读更多...
使用Python开发一个现代化屏幕取色器

使用Python开发一个现代化屏幕取色器

《使用Python开发一个现代化屏幕取色器》在UI设计、网页开发等场景中,颜色拾取是高频需求,：本文主要介绍如何使用Python开发一个现代化屏幕取色器,有需要的小伙伴可以参考一下... 目录一、项目概述二、核心功能解析2.1 实时颜色追踪2.2 智能颜色显示三、效果展示四、实现步骤详解4.1 环境配置4.
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2