备案控制台
探索云世界
开发者社区 数据库 文章 正文

[20171225]没有备份数据文件的恢复.txt

2017-12-25 893
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介： [20171225]没有备份数据文件的恢复.txt --//别人问的问题,增加了数据文件没有备份,如何恢复,实际上很简单,因为当前控制文件有记录建立时间只要从建立数据文件开始的 --//归档日志都存在恢复是没有任何问题的.

[20171225]没有备份数据文件的恢复.txt

--//别人问的问题,增加了数据文件没有备份,如何恢复,实际上很简单,因为当前控制文件有记录建立时间只要从建立数据文件开始的
--//归档日志都存在恢复是没有任何问题的.我仅仅介绍rman的恢复:

1.环境:
SCOTT@book> @&r/ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

CREATE TABLESPACE SUGAR DATAFILE
  '/mnt/ramdisk/book/sugar01.dbf' SIZE 6M AUTOEXTEND OFF
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

SCOTT@book> create table tx tablespace sugar as select * from dba_objects where rownum<=1000;
Table created.

SCOTT@book> delete from tx where rownum<=10 ;
10 rows deleted.

SCOTT@book> commit ;
Commit complete.

SCOTT@book> select count(*) from tx ;
  COUNT(*)
----------
       990

SCOTT@book> alter system archive log current ;
System altered.

SCOTT@book> alter system archive log current ;
System altered.

SCOTT@book> alter system archive log current ;
System altered.

SCOTT@book> alter system archive log current ;
System altered.

2.测试:
--//人为删除数据文件:
$ rm /mnt/ramdisk/book/sugar01.dbf
/bin/rm: remove regular file `/mnt/ramdisk/book/sugar01.dbf'? y

SCOTT@book> select count(*) from tx ;
  COUNT(*)
----------
       990

SCOTT@book> alter system flush buffer_cache;
System altered.

SCOTT@book> select count(*) from tx ;
select count(*) from tx
                     *
ERROR at line 1:
ORA-01116: error in opening database file 7
ORA-01110: data file 7: '/mnt/ramdisk/book/sugar01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

SCOTT@book> SELECT file#, CREATION_TIME,CHECKPOINT_CHANGE#,CHECKPOINT_TIME,UNRECOVERABLE_CHANGE#,LAST_CHANGE#,LAST_TIME, OFFLINE_CHANGE#, ONLINE_CHANGE#,FIRST_NONLOGGED_SCN,FIRST_NONLOGGED_TIME,status,name FROM v$datafile where file#=7;
FILE# CREATION_TIME       CHECKPOINT_CHANGE# CHECKPOINT_TIME     UNRECOVERABLE_CHANGE# LAST_CHANGE# LAST_TIME           OFFLINE_CHANGE# ONLINE_CHANGE# FIRST_NONLOGGED_SCN FIRST_NONLOGGED_TIM STATUS  NAME
----- ------------------- ------------------ ------------------- --------------------- ------------ ------------------- --------------- -------------- ------------------- ------------------- ------- --------------------------------------------------
    7 2017-12-25 15:53:23        13277924712 2017-12-25 15:54:52                     0  13277925346 2017-12-25 16:00:21               0              0                   0                     RECOVER /mnt/ramdisk/book/sugar01.dbf
--//在控制文件里面记录了建立数据文件的时间(2017-12-25 15:53:23).只要这些归档在问题就不大.

RMAN> report schema ;
Report of database schema for database with db_unique_name BOOK
List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    760      SYSTEM               ***     /mnt/ramdisk/book/system01.dbf
2    940      SYSAUX               ***     /mnt/ramdisk/book/sysaux01.dbf
3    865      UNDOTBS1             ***     /mnt/ramdisk/book/undotbs01.dbf
4    128      USERS                ***     /mnt/ramdisk/book/users01.dbf
5    346      EXAMPLE              ***     /mnt/ramdisk/book/example01.dbf
6    6        TEA                  ***     /mnt/ramdisk/book/tea01.dbf
7    0        SUGAR                ***     /mnt/ramdisk/book/sugar01.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    414      TEMP                 32767       /mnt/ramdisk/book/temp01.dbf

RMAN> list failure ;
List of Database Failures
=========================
Failure ID Priority Status    Time Detected       Summary
---------- -------- --------- ------------------- -------
71648      HIGH     OPEN      2017-12-25 15:56:41 One or more non-system datafiles are missing

RMAN> list failure 71648 detail;
List of Database Failures
=========================
Failure ID Priority Status    Time Detected       Summary
---------- -------- --------- ------------------- -------
71648      HIGH     OPEN      2017-12-25 15:56:41 One or more non-system datafiles are missing
  Impact: See impact for individual child failures
  List of child failures for parent failure ID 71648
  Failure ID Priority Status    Time Detected       Summary
  ---------- -------- --------- ------------------- -------
  86741      HIGH     OPEN      2017-12-25 15:56:41 Datafile 7: '/mnt/ramdisk/book/sugar01.dbf' is missing
    Impact: Some objects in tablespace SUGAR might be unavailable

RMAN> advise failure ;

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

Failure ID Priority Status    Time Detected       Summary
---------- -------- --------- ------------------- -------
71648      HIGH     OPEN      2017-12-25 15:56:41 One or more non-system datafiles are missing
  Impact: See impact for individual child failures
  List of child failures for parent failure ID 71648
  Failure ID Priority Status    Time Detected       Summary
  ---------- -------- --------- ------------------- -------
  86741      HIGH     OPEN      2017-12-25 15:56:41 Datafile 7: '/mnt/ramdisk/book/sugar01.dbf' is missing
    Impact: Some objects in tablespace SUGAR might be unavailable

analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=119 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=132 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=161 device type=DISK
analyzing automatic repair options complete

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

Optional Manual Actions
=======================
1. If file /mnt/ramdisk/book/sugar01.dbf was unintentionally renamed or moved, restore it
2. Shut down, mount the database and try flush redo using ALTER SYSTEM FLUSH REDO TO 'standby name' command.  Then
   perform a Data Guard role change (failover).  Available standbys: bookdg.

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

$ cat /u01/app/oracle/diag/rdbms/book/book/hm/reco_1003154902.hm
   # restore and recover datafile
   sql 'alter database datafile 7 offline';
   restore datafile 7;
   recover datafile 7;
   sql 'alter database datafile 7 online';

--//按照命令文本介绍执行如下:
RMAN> sql 'alter database datafile 7 offline';
sql statement: alter database datafile 7 offline

RMAN> restore datafile 7;

Starting restore at 2017-12-25 16:00:26
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3

creating datafile file number=7 name=/mnt/ramdisk/book/sugar01.dbf
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
restore not done; all files read only, offline, or already restored
Finished restore at 2017-12-25 16:00:26

--//注意看下划线内容,数据文件建立.
RMAN> recover datafile 7;
Starting recover at 2017-12-25 16:05:20
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
starting media recovery
archived log for thread 1 with sequence 797 is already on disk as file /u01/app/oracle/archivelog/book/1_797_896605872.dbf
archived log for thread 1 with sequence 798 is already on disk as file /u01/app/oracle/archivelog/book/1_798_896605872.dbf
archived log for thread 1 with sequence 799 is already on disk as file /u01/app/oracle/archivelog/book/1_799_896605872.dbf
archived log for thread 1 with sequence 800 is already on disk as file /u01/app/oracle/archivelog/book/1_800_896605872.dbf
archived log file name=/u01/app/oracle/archivelog/book/1_797_896605872.dbf thread=1 sequence=797
archived log file name=/u01/app/oracle/archivelog/book/1_798_896605872.dbf thread=1 sequence=798
media recovery complete, elapsed time: 00:00:00
Finished recover at 2017-12-25 16:05:21

RMAN>  sql 'alter database datafile 7 online';
sql statement: alter database datafile 7 online


SCOTT@book> select count(*) from tx ;
    COUNT(*)
------------
         990

--//OK,数据没有丢失.

3.再介绍sqlplus来恢复:
--//重复操作:
$ rm /mnt/ramdisk/book/sugar01.dbf
/bin/rm: remove regular file `/mnt/ramdisk/book/sugar01.dbf'? y

SCOTT@book> alter system flush buffer_cache;
System altered.

SCOTT@book> select count(*) from tx ;
select count(*) from tx
*
ERROR at line 1:
ORA-01116: error in opening database file 7
ORA-01110: data file 7: '/mnt/ramdisk/book/sugar01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

--//在sqlplus也可以执行,建立数据文件:
SCOTT@book> alter database create datafile 7 as '/mnt/ramdisk/book/sugar01.dbf';
alter database create datafile 7 as '/mnt/ramdisk/book/sugar01.dbf'
*
ERROR at line 1:
ORA-01182: cannot create database file 7 - file is in use or recovery
ORA-01110: data file 7: '/mnt/ramdisk/book/sugar01.dbf'

SCOTT@book> alter database datafile 7 offline ;
Database altered.

SCOTT@book> alter database create datafile 7 as '/mnt/ramdisk/book/sugar01.dbf';
Database altered.

SCOTT@book> recover datafile 7;
ORA-00279: change 13277924104 generated at 12/25/2017 15:53:23 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/archivelog/book/1_797_896605872.dbf
ORA-00280: change 13277924104 for thread 1 is in sequence #797
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 13277924687 generated at 12/25/2017 15:54:45 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/archivelog/book/1_798_896605872.dbf
ORA-00280: change 13277924687 for thread 1 is in sequence #798
Log applied.
Media recovery complete.

SCOTT@book> alter database datafile 7 online ;
Database altered.

SCOTT@book> select count(*) from tx ;

  COUNT(*)
----------
       990

--//只要概念清晰,这样的恢复很容易完成.

文章标签：
关系型数据库
数据库
Oracle
SQL
lfreeali
目录
相关文章
Multis
|
11月前
|
Oracle 关系型数据库 数据库
4、数据文件的备份与恢复
数据文件的备份与恢复
Multis
106 0
郭一军_guoyJoe
|
Oracle 关系型数据库 数据库
创建数据文件：恢复一个备份中没有的数据文件
备份恢复
郭一军_guoyJoe
700 0
lfreeali
|
Oracle 关系型数据库 数据库
[20171122]恢复数据文件块头5.txt
[20171122]恢复数据文件块头5.txt --//前几天做了恢复数据文件块头,通过备份文件直接取出文件块头,覆盖原来的数据块,然后修复. --//今天测试使用image copy来恢复.
lfreeali
1092 0
lfreeali
|
Oracle 关系型数据库 数据库管理
[20161101]rman备份与数据文件变化7.txt
[20161101]rman备份与数据文件变化7.txt --//想象一下，如果备份文件时间很长，而这个时候数据文件大小发生了变化，oracle的备份如何解决这个问题呢？ --//去年已经测试了建立备份集的情况,一直想做一次image copy的测试,一直脱,主要原因自己不想做这个测试.
lfreeali
1173 0
lfreeali
|
Oracle 关系型数据库 OLAP
[20171123]rman备份与数据文件变化6.txt
[20171123]rman备份与数据文件变化6.txt --//想象一下，如果备份文件时间很长，而这个时候数据文件大小发生了变化，oracle的备份如何解决这个问题呢？ --//去年已经测试了建立备份集的情况,一直想做一次image copy的测试,一直脱,主要原因自己不想做这个测试.
lfreeali
778 0
余二五
|
关系型数据库 MySQL 数据库
使用mysqlhotcopy命令备份的文件如何恢复
余二五
1025 0
lfreeali
|
Oracle 关系型数据库 数据库
[20171114]恢复数据文件块头2.txt
[20171114]恢复数据文件块头2.txt --//曾经写过一篇[20161111]数据库文件头的修复.txt,但是利用大小相似的数据文件头覆盖来恢复,那是属于特种恢复.
lfreeali
1054 0
lfreeali
|
关系型数据库 Linux 数据库
[20171116]11GR2控制文件自动备份.txt
20171116]11GR2控制文件自动备份.txt SYS@book> @ &r/ver1 PORT_STRING                    VERSION        BANNER ---------------------------...
lfreeali
941 0
科技小能手
|
Oracle 关系型数据库 数据库
备份控制文件之后，添加数据文件,数据文件无备份丢失后的恢复
科技小能手
1337 0
技术小美
|
Oracle 关系型数据库 数据库
用备份控制文件做不完全恢复下的完全恢复（数据文件备份<旧>--新建表空间--控制文件备份<次新>--日志归档文件<新>）
技术小美
1058 0

热门文章

最新文章

  • 1
    Mac安装并使用telnet命令操作
  • 2
    OSS回源的几种方式和应用场景
  • 3
    [剑指offer] 孩子们的游戏(圆圈中最后剩下的数)
  • 4
    网络安全系列之二十二 Windows用户账号加固
  • 5
    我理解的一个程序员如何学习前端开发
  • 6
    《社交网站界面设计（原书第2版）》——1.9　为设备之间的空间进行设计
  • 7
    《Microduino实战》——1.2　为什么要开源
  • 8
    .Net函数Math.Round你会用吗？
  • 9
    麻省理工大学新发明：暗黑WiFi透视技术
  • 10
    2014秋C++第19周 补充代码 哈希法的存储与查找
  • 1
    R语言关联规则模型(Apriori算法)挖掘杂货店的交易数据与交互可视化
    25
  • 2
    R语言近似贝叶斯计算MCMC（ABC-MCMC）轨迹图和边缘图可视化
    21
  • 3
    r语言中对LASSO回归，Ridge岭回归和弹性网络Elastic Net模型实现-4
    29
  • 4
    Sentieon | 每周文献-Multi-omics-第四十一期
    25
  • 5
    数据分享|R语言广义线性模型GLM：线性最小二乘、对数变换、泊松、二项式逻辑回归分析冰淇淋销售时间序列数据和模拟-2
    15
  • 6
    数据分享|R语言广义线性模型GLM：线性最小二乘、对数变换、泊松、二项式逻辑回归分析冰淇淋销售时间序列数据和模拟-1
    22
  • 7
    基于RT-Thread摄像头车牌图像采集系统
    22
  • 8
    R语言极值理论：希尔HILL统计量尾部指数参数估计可视化
    16
  • 9
    【视频】R语言中的分布滞后非线性模型（DLNM）与发病率，死亡率和空气污染示例
    20
  • 10
    sql语句创建数据库
    18

    • 相关电子书

    更多
  • 数据库异地备份及不还原快速查询备份集最佳实践
  • 低代码开发师（初级）实战教程
  • 阿里巴巴DevOps 最佳实践手册

    • 相关实验场景

    更多
  • 快照备份与恢复
    • 下一篇
    部署LAMP环境（Alibaba Cloud Linux 3）