RMAN备份恢复系列之系统表空间恢复

最新推荐文章于 2024-01-08 12:17:47 发布
最新推荐文章于 2024-01-08 12:17:47 发布
阅读量1k 收藏 1
点赞数
分类专栏： OCM Training RMAN备份与恢复 文章标签： oracle 数据库 rman
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/zhuke0203/article/details/71307912
版权

实验环境

  • 操作系统 Redhat5.4 x86
  • 数据库版本 oracle 11gR2 (11.2.0.1.0)
  • 实验前已经做了RMAN全量备份包括controlfile、spfile

实验模拟

系统表空间文件损坏或丢失

案例模拟

模拟system表空间数据文件损坏或丢失情况：

[oracle@node1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat May 6 07:26:42 2017

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> col tablespace_name for a30
SQL> col file_name for a60
SQL> set line 180
SQL> select tablespace_name, file_name from dba_data_files;

TABLESPACE_NAME            FILE_NAME
------------------------------ ------------------------------------------------------------
SYSTEM                 /u01/app/oracle/oradata/PROD/disk5/system01.dbf
SYSAUX                 /u01/app/oracle/oradata/PROD/disk1/sysaux01.dbf
UNDOTBS1               /u01/app/oracle/oradata/PROD/disk4/undotbs01.dbf
USERS                  /u01/app/oracle/oradata/PROD/disk2/users01.dbf
EXAMPLE                /u01/app/oracle/oradata/PROD/disk5/example_01.dbf
EXAM                   /u01/app/oracle/oradata/PROD/disk1/exam_01.dbf
TEST                   /u01/app/oracle/oradata/PROD/disk5/test
FREE_LIST              /u01/app/oracle/oradata/PROD/disk1/free_list
USERSS                 /u01/app/oracle/oradata/PROD/disk2/users_01.dbf

9 rows selected.

SQL> !rm /u01/app/oracle/oradata/PROD/disk5/system01.dbf

SQL> drop table test;

Table dropped.

SQL> create table test tablespace system as select username from dba_users;
create table test tablespace system as select username from dba_users
                                                            *
ERROR at line 1:
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/u01/app/oracle/oradata/PROD/disk5/system01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
案例恢复
--尝试直接进行rman恢复：
[oracle@node1 ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Sat May 6 07:30:57 2017

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: PROD (DBID=352761597)

RMAN> list failure;

using target database control file instead of recovery catalog
List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
1604       CRITICAL OPEN      06-MAY-17     System datafile 1: '/u01/app/oracle/oradata/PROD/disk5/system01.dbf' is missing

RMAN> advise failure;

List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
1604       CRITICAL OPEN      06-MAY-17     System datafile 1: '/u01/app/oracle/oradata/PROD/disk5/system01.dbf' is missing

analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=51 device type=DISK
analyzing automatic repair options complete

Mandatory Manual Actions
========================
1. If file /u01/app/oracle/oradata/PROD/disk5/system01.dbf was unintentionally renamed or moved, restore it
2. Automatic repairs may be available if you shutdown the database and restart it in mount mode
3. Contact Oracle Support Services if the preceding recommendations cannot be used, or if they do not fix the failures selected for repair

Optional Manual Actions
=======================
no manual actions available

Automated Repair Options
========================
no automatic repair options available   

-- RMAN提示没有自动修复选项

RMAN> exit


Recovery Manager complete.

--将数据库重启后再次尝试rman恢复：
[oracle@node1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat May 6 07:31:42 2017

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shutdown immediate;
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/u01/app/oracle/oradata/PROD/disk5/system01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area  836976640 bytes
Fixed Size          1339740 bytes
Variable Size         662703780 bytes
Database Buffers      167772160 bytes
Redo Buffers            5160960 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/u01/app/oracle/oradata/PROD/disk5/system01.dbf'


SQL> select status from v$instance;

STATUS
------------
MOUNTED

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@node1 ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Sat May 6 07:32:26 2017

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: PROD (DBID=352761597, not open)

RMAN> list failure;

using target database control file instead of recovery catalog
List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
1604       CRITICAL OPEN      06-MAY-17     System datafile 1: '/u01/app/oracle/oradata/PROD/disk5/system01.dbf' is missing

RMAN> advise failure;

List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
1604       CRITICAL OPEN      06-MAY-17     System datafile 1: '/u01/app/oracle/oradata/PROD/disk5/system01.dbf' is missing

analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=21 device type=DISK
analyzing automatic repair options complete

Mandatory Manual Actions
========================
no manual actions available

Optional Manual Actions
=======================
1. If file /u01/app/oracle/oradata/PROD/disk5/system01.dbf was unintentionally renamed or moved, restore it

Automated Repair Options
========================
Option Repair Description
------ ------------------
1      Restore and recover datafile 1  
  Strategy: The repair includes complete media recovery with no data loss
  Repair script: /u01/app/oracle/diag/rdbms/prod/PROD/hm/reco_3184259017.hm

RMAN> repair failure;

Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/prod/PROD/hm/reco_3184259017.hm

contents of repair script:
   # restore and recover datafile
   restore datafile 1;
   recover datafile 1;

Do you really want to execute the above repair (enter YES or NO)? yes
executing repair script

Starting restore at 06-MAY-17
using channel ORA_DISK_1

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 /u01/app/oracle/oradata/PROD/disk5/system01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/PROD/backupset/2017_05_06/o1_mf_nnndf_TAG20170506T072607_djt2jj5l_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/PROD/backupset/2017_05_06/o1_mf_nnndf_TAG20170506T072607_djt2jj5l_.bkp tag=TAG20170506T072607
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 06-MAY-17

Starting recover at 06-MAY-17
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 06-MAY-17
repair failure complete

Do you want to open the database (enter YES or NO)? yes
database opened

RMAN> exit


Recovery Manager complete.
[oracle@node1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat May 6 07:33:37 2017

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select status from v$instance;

STATUS
------------
OPEN

SQL> create table test tablespace system as select username from dba_users;

Table created.

SQL> select count(1) from test;

  COUNT(1)
----------
    10

SQL> 

--至此数据system表空数据文件恢复已经完成。

从上面的测试发现，当数据文件损坏的是系统表空间的时候，无法进行在线恢复，需要将数据库重启，并且重启后由于损坏的是系统表空间，数据库无法启动到open状态，只能启动到mount状态，在mount状态下使用RMAN 可以通过repair advise获得自动恢复方法.

kevin-ke
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
ORACLE RMAN备份恢复指南
07-26
本文旨在为瑞航公司当前所使用的Oracle RMAN备份恢复机制提供详尽的指导，涵盖全量备份、增量备份、数据恢复等多个方面，尤其关注于数据丢失、控制文件丢失、参数文件丢失、密码文件丢失、redo文件丢失以及表空间...
oracle rman恢复表空间,rman恢复部分表空间
weixin_31008853的博客
04-04 432
SQL> conn / as sysdbaConnected.SQL> select name from v$datafile;NAME--------------------------------------------------------------------------------/u01/oracle/oradata/orcl/system01.dbf/u01/orac...
使用RMAN备份与恢复数据库（5）——表空间的备份与恢复
睿思达DBA
04-09 627
使用RMAN备份与恢复数据库（5）——表空间的备份与恢复 一、使用RMAN备份表空间 1、备份表空间 RMAN> backup as compressed backupset tablespace data01 format '/home/oracle/rmanbak/ts_%d_%I_%s_%p_%T'; Starting backup at 2020-04-09 19:35:35 us...
[RMAN]表空间的恢复
cuichou8903的博客
10-26 323
1. 恢复条件 恢复表空间实际上就是恢复其所对应的数据文件（一个表空间可以对应 1 个或多个数据文件）。前提是必须具有全库备份，数据库处于 ...
使用RMAN恢复表空间
七海琉璃的博客
04-19 1410
RMAN表空间恢复1.前期准备1.1 添加一个新的表空间进行试验1.2 验证创建1.3 使用rman进行备份2.删除表空间3.恢复表空间3.1查看日志，找到删除的时间点3.2 使用rman进行恢复3.3 验证恢复4.也可以查询scn来进行恢复操作4.1 登录数据库查询4.2 登录RMAN恢复 1.前期准备 1.1 添加一个新的表空间进行试验 SYS@hfdr>create tablespace zhangyun datafile '/u01/app/oracle/oradata/recover
Oracle-RMAN增量备份恢复测试记录
12-28
Oracle-RMAN增量备份恢复测试记录 Oracle-RMAN增量备份恢复测试记录是指使用Oracle Recovery Manager（RMAN）工具对Oracle数据库进行增量备份和恢复的测试记录。本记录涵盖了增量备份的基本概念、备份策略及恢复、...
catalog方式的rman备份与恢复示例
09-10
通过catalog方式进行RMAN备份与恢复，就是通过创建一个专门的恢复目录来管理备份信息，而不是直接在控制文件（controlfile）中记录。以下是从给定文件中提取的知识点： 1. Catalog方式与NoCatalog方式的对比 - ...
oracle 11g rman 备份与恢复
09-29
它可以处理各种类型的恢复场景，如单个文件恢复、表空间恢复，甚至整个数据库的恢复。 3. **归档日志管理**：RMAN能够管理数据库的归档日志，确保在进行恢复时有完整的事务历史记录。它还能自动删除不再需要的归档...
rman备份恢复详解
02-08
rman备份恢复详解 RMAN（Recovery Manager）是一种Oracle数据库备份和恢复工具，能够帮助DBA快速备份和恢复数据库。下面将详细介绍RMAN自动备份和恢复的相关知识点。 一、为什么需要设置自动归档模式？ 在使用...
rman恢复system表空间实例
06-09
此文档是关于讲述rman备份后，模拟system表空间坏了，怎么使用rman恢复此表空间；
诊断和响应故障_执行RMAN表空间时间点恢复
jetliu05的博客
12-21 543
执行RMAN表空间时间点恢复
RMAN基于表空间的不完全恢复（TSPITR)
weixin_34235105的博客
06-24 225
RMAN基于表空间的不完全恢复（TSPITR)表空间时间点恢复(TSPITR)：概念 通过执行TSPITR 可将一个或多个表空间快速恢复到以前的某个时间。 执行TSPITR 不会影响数据库中其它表空间或对象的状态。使用RMAN 自动表空间时间点恢复(TSPITR) 可将Oracle DB 中的一个或多个表空间快速恢复到以前的某个时间，而不会影响数据库中其它表空间和对象的状态。...
RMAN数据库恢复之恢复表空间和数据文件
weixin_33787529的博客
12-22 123
执行表空间或数据文件恢复时，数据库既可以是MOUNT状态，也可以是OPEN状态。1、恢复表空间在执行恢复之前，如果被操作的表空间未处理OFFLINE状态，必须首先通过ALTER TABLESPACE…OFFLINE语句将其置为脱机。然后再RESTORE-->RECOVER--->ONLINE即可。 RMAN> SQL 'ALTER TABLESPACE USERS OFFL...
RMAN恢复某张表的方法
loveLAxin的博客
06-25 1541
环境模拟：删除 test 用户下 t1 表中的数据。
Oracle11.2.0.4从RMAN备份中快速恢复单个表的方法
最新发布
zongzizz的博客
01-08 2009
Oracle11.2.0.4从RMAN备份中快速恢复单个表的方法
oracle rman恢复表空间,rman备份恢复表空间
weixin_42442019的博客
04-04 597
rman备份恢复表空间1 创建数据表空间SQL> create tablespace test_tablespace logging datafile '/u01/app/oracle/oradata/orcl/test_tablespace.dbf'size 10M autoextend on next 10M maxsize 100M;2 创建用户test_user，隶属于test_ta...
oracle rman恢复表空间,使用RMAN进行基于表空间的恢复
weixin_42112658的博客
04-04 296
RMAN> recover tablespace system;Starting recover at 30-MAR-05using channel ORA_DISK_1starting media recoveryarchive log thread 1 sequence 1760 is already on disk as file /data5/flash_recovery_area/...
rman恢复－system表空间恢复
ruilin215
08-22 154
恢复system表空间的数据文件 system表空间存放着数据字典的信息。当数据库处于open状态，如果system表空间所对应的数据文件出现介质失败，那么当在其数据文件上执行I/O操作时，数据库会自动关闭;当数据库处于关闭状态时，如果system表空间所对应的数据文件出现介质失败，数据库将不能打开。恢复system表空间必须在mount状态下进行。示例一system表空间的数据文件被误删除 ...
Oracle RMAN备份恢复教程：从入门到实战
在Oracle数据库管理中，RMAN是Oracle公司提供的一个强大的备份和恢复工具，它能够执行数据库的完整备份、增量备份、表空间备份等，同时支持在出现数据丢失或系统故障时进行恢复操作。本教程以"一步一步学RMAN完整版...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值