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

最新推荐文章于 2022-04-19 10:28:49 发布
最新推荐文章于 2022-04-19 10:28:49 发布
阅读量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
关注
专栏目录
100天精通Oracle-实战系列（第16天）使用 RMAN 备份快速恢复误删数据表
LuciferLiu_DBA
03-21 1332
100天精通Oracle-实战系列（第16天）使用 RMAN 备份快速恢复误删数据表
rman恢复－system表空间恢复
ruilin215
08-22 155
恢复system表空间的数据文件 system表空间存放着数据字典的信息。当数据库处于open状态，如果system表空间所对应的数据文件出现介质失败，那么当在其数据文件上执行I/O操作时，数据库会自动关闭;当数据库处于关闭状态时，如果system表空间所对应的数据文件出现介质失败，数据库将不能打开。恢复system表空间必须在mount状态下进行。示例一system表空间的数据文件被误删除 ...
[RMAN]表空间的恢复
cuichou8903的博客
10-26 325
1. 恢复条件 恢复表空间实际上就是恢复其所对应的数据文件（一个表空间可以对应 1 个或多个数据文件）。前提是必须具有全库备份，数据库处于 ...
使用RMAN恢复表空间
七海琉璃的博客
04-19 1426
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
rman表空间丢失与离线恢复
makef的博客
04-02 521
rman单个数据文件丢失与恢复 一，用的是之间的备份 （1）切换用户 SQL> conn hai/hai     Connected. (2)复制表 SQL> create  table feng3 as  select  * from hai.feng; Table created. SQL> select  *  from feng3;         I
linux rman备份表空间,RMAN进行表空间的备份和恢复
weixin_39642619的博客
05-08 470
1、创建表空eygle2、创建用户eygle，隶属于eygle表空间；赋予用户DBA权限3、egyle用户登录DB，创建表test_egyle4、rman备份egyle表空间RMAN> backup tablespace eygle tag="ey_first";Starting backup at 15-FEB-14using target database control file in...
ORACLE数据库RMAN备份恢复[参照].pdf
10-11
ORACLE 数据库 RMAN 备份恢复 ORACLE 数据库 RMAN 备份恢复是指使用 ORACLE 的 Recovery Manager（RMAN）工具对数据库进行备份和恢复的过程。RMAN 是 ORACLE 数据库管理系统中的一种备份和恢复工具，能够对数据库...
ORACLE RMAN备份恢复指南
07-26
本文旨在为瑞航公司当前所使用的Oracle RMAN备份恢复机制提供详尽的指导，涵盖全量备份、增量备份、数据恢复等多个方面，尤其关注于数据丢失、控制文件丢失、参数文件丢失、密码文件丢失、redo文件丢失以及表空间...
ORACLE RMAN备份恢复单个表空间
pool_Sneaker的博客
05-14 2610
原因 单个库可能有很多的冗余数据，比如查询库，通过ogg等方式同的数据，当这台库上做其他的分析或者是另一个有业务的主库的时。就有需求做备份恢复。 但是本身是一个查询库，整库rman备份非常的慢。 通过nbu或者rman可以备份单个schema（一个schema对应一个表空间），这样速度快，恢复快。 步骤 1.准备好参数文件（略） 2.获取控制文件，启动到mount startup nomount;...
rman恢复system表空间实例
06-09
此文档是关于讲述rman备份后，模拟system表空间坏了，怎么使用rman恢复此表空间；
RMAN进行表空间的备份和恢复
ZWWDLL的专栏
02-15 755
1、创建表空eygle   2、创建用户eygle，隶属于eygle表空间；赋予用户DBA权限   3、egyle用户登录DB，创建表test_egyle   5、rman备份egyle表空间 RMAN> backup tablespace eygle tag="ey_first"; Starting backup at 15-FEB-14 using target databa
oracle rman恢复表空间,rman备份恢复表空间
weixin_42442019的博客
04-04 598
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恢复表空间,Oracle RMAN 表空间的完全恢复
weixin_30999777的博客
04-04 162
如果数据库打开，有部分数据文件损坏，可以在不影响其他数据文件的情况下，对损坏的数据文件进行恢复。--对数据库进行备份RMAN> BACKUP DATABASE PLUS ARCHIVELOG DELETE ALL INPUT;Starting backup at 2016-01-18 23:33:13current log archivedusing channel ORA_DISK_1ch...
RMAN备份恢复表空间
喝醉酒的小白
06-29 2961
备份恢复
oracle rman恢复表空间,使用RMAN进行基于表空间的恢复
weixin_42112658的博客
04-04 297
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备份与恢复—恢复有备份的表空间和数据文件
lufei051的专栏
10-31 436
首先模拟数据文件丢失 [sql] view plaincopyprint? SQL> select status from v$instance;    STATUS  ------------  OPEN    SQL> ! rm -rf /u01/app/oracle/oradata/orcl/tb1.dbf    SQL> select file_name,onl
RMAN备份表空间
congnao1988的博客
09-23 101
2、 备份数据库表空间usersC:/>rman target=backup_admin/backupuserpassword nocatalogRecovery Manager: Release 8.1.6.0.0 - ...
使用rman恢复部分表空间
异道的专栏
08-11 3947
±ðÔÚÖ÷»úÉÏÓÃrman ÖŽÐÐ £¬Íš¹ýcommvault»ÖžŽ žßŒ¶ ×Ô¶šÒåœÅ±ŸÖŽÐÐ SET DBID 3958247272; run { allocate channel ch1 type 'sbt_tape' PARMS="SBT_LIBRARY=/opt/simpana/Base64/libobk.a(shr.o),BLKSIZE=262
RMAN数据库恢复之恢复表空间和数据文件
weixin_33787529的博客
12-22 127
执行表空间或数据文件恢复时，数据库既可以是MOUNT状态，也可以是OPEN状态。1、恢复表空间在执行恢复之前，如果被操作的表空间未处理OFFLINE状态，必须首先通过ALTER TABLESPACE…OFFLINE语句将其置为脱机。然后再RESTORE-->RECOVER--->ONLINE即可。 RMAN> SQL 'ALTER TABLESPACE USERS OFFL...
Oracle RMAN备份恢复教程：从入门到实战
在Oracle数据库管理中，RMAN是Oracle公司提供的一个强大的备份和恢复工具，它能够执行数据库的完整备份、增量备份、表空间备份等，同时支持在出现数据丢失或系统故障时进行恢复操作。本教程以"一步一步学RMAN完整版...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值