ORACLE 11G rman恢复数据库实战纪实

Oracle 前端之家
前端之家收集整理的这篇文章主要介绍了ORACLE 11G rman恢复数据库实战纪实，前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。

今天领导需要将某天的备份恢复过来到临时库，因为线上采用的是rman备份，所以会有rman备份集合，但是rman出来的备份集合，每次恢复过程中都会出现各种各样的细节问题，都记录下来，以备后来参考，大概流程是

（1）copy备份集合到临时库；

（2）关闭临时库

（3）恢复控制文件

（4）注册备份集合目录

（5）restore database、recovery database；

（6）open打开数据库

1、去生产环境中copy备份文件

去备份文件服务器上，拷贝，需要拷贝数据集、控制文件到临时库

拷贝数据集合：scp -r 2017-02-20 10.10.10.104:/data/

拷贝控制文件：scp c-3391761643-20170220-00 c-3391761643-20170220-01c-3391761643-20170221-00 c-3391761643-20170221-0110.10.10.104:/data/2017-02-20/

然后到临时库服务器，赋予oracle执行权限：chown -R oracle.oinstall *

2、关闭临时库，并以nomount方式打开

shutown immediate;

startup nomount;

3、在临时库上恢复控制文件

去生产环境查询控制文件路径

sql> select name from v$controlfile;

NAME

--------------------------------------------------------------------------------

/home/oradata/powerdes/control01.ctl

/oracle/app/oracle/flash_recovery_area/powerdes/control02.ctl

sql>

需要恢复控制文件到如下2个地方

/home/oradata/powerdes/control01.ctl

/oracle/app/oracle/flash_recovery_area/powerdes/control02.ctl

去临时库上，恢复控制文件，在rman命令行界面，执行如下命令

RMAN> restore controlfile to '/home/oradata/powerdes/control01.ctl' from '/data/2017-02-20/c-3391761643-20170221-01' ;

RMAN> restore controlfile to '/oracle/app/oracle/flash_recovery_area/powerdes/control02.ctl' from '/data/2017-02-20/c-3391761643-20170221-01' ;

4、将临时库启动到mount

alter database mount;

RMAN> alter database mount;

database mounted

released channel: ORA_DISK_1

RMAN>

5、开始注册备份集

命令：catalog start with '/data/2017-02-20/';

注册过程如下：

RMAN> catalog start with '/data/2017-02-20/';

searching for all files that match the pattern /data/2017-02-20/

List of Files Unknown to the Database

=====================================

File Name: /data/2017-02-20/rman_backup.log

File Name: /data/2017-02-20/c-3391761643-20170220-00

File Name: /data/2017-02-20/arch_POWERDES_20170220_8282.bak

File Name: /data/2017-02-20/full_POWERDES_20170220_8281.bak

File Name: /data/2017-02-20/arch_POWERDES_20170220_8280.bak

File Name: /data/2017-02-20/c-3391761643-20170221-01

File Name: /data/2017-02-20/c-3391761643-20170221-00

File Name: /data/2017-02-20/c-3391761643-20170220-01

Do you really want to catalog the above files (enter YES or NO)? YES

cataloging files...

cataloging done

List of Cataloged Files

=======================

File Name: /data/2017-02-20/c-3391761643-20170220-00

File Name: /data/2017-02-20/arch_POWERDES_20170220_8282.bak

File Name: /data/2017-02-20/full_POWERDES_20170220_8281.bak

File Name: /data/2017-02-20/arch_POWERDES_20170220_8280.bak

File Name: /data/2017-02-20/c-3391761643-20170221-01

File Name: /data/2017-02-20/c-3391761643-20170221-00

File Name: /data/2017-02-20/c-3391761643-20170220-01

List of Files Which Where Not Cataloged

=======================================

File Name: /data/2017-02-20/rman_backup.log

RMAN-07517: Reason: The file header is corrupted

RMAN>

6、进行restore database恢复操作

接下来开始执行restore恢复操作，将数据从备份集写入到磁盘上的数据文件里面，还原已经备份的数据文件

命令：restore database;

执行过程：

RMAN> restore database;

Starting restore at 22-FEB-17

using channel ORA_DISK_1

skipping datafile 2; already restored to file /home/oradata/powerdes/sysaux01.dbf

skipping datafile 3; already restored to file /home/oradata/powerdes/undotbs01.dbf

skipping datafile 4; already restored to file /home/oradata/powerdes/users01.dbf

skipping datafile 7; already restored to file /home/oradata/powerdes/pl01.dbf

skipping datafile 8; already restored to file /home/oradata/powerdes/help01.dbf

skipping datafile 9; already restored to file /home/oradata/powerdes/adobelc01.dbf

skipping datafile 10; already restored to file /home/oradata/powerdes/sms01.dbf

skipping datafile 11; already restored to file /home/oradata/powerdes/plcrm01.dbf

skipping datafile 13; already restored to file /home/oradata/powerdes/datagm01.dbf

skipping datafile 15; already restored to file /home/oradata/powerdes/dwetl01.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 /home/oradata/powerdes/system01.dbf

channel ORA_DISK_1: restoring datafile 00005 to /home/oradata/powerdes/powerdesk01.dbf

channel ORA_DISK_1: restoring datafile 00006 to /home/oradata/powerdes/plas01.dbf

channel ORA_DISK_1: restoring datafile 00012 to /home/oradata/powerdes/powerdesk02.dbf

channel ORA_DISK_1: reading from backup piece /data/2017-02-20/full_POWERDES_20170220_8281.bak

channel ORA_DISK_1: piece handle=/data/2017-02-20/full_POWERDES_20170220_8281.bak tag=TAG20170220T030019

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete,elapsed time: 00:52:45

Finished restore at 22-FEB-17

RMAN>

这个过程比较漫长，可以查看后台alert日志，观察是否有异常状况出现：

Reading datafile '/home/oradata/powerdes/plas01.dbf' for corruption at rdba: 0x01800001 (file 6,block 1)

Reread (file 6,block 1) found same corrupt data

Wed Feb 22 15:44:00 2017

db_recovery_file_dest_size of 15360 MB is 0.21% used. This is a

user-specified limit on the amount of space that will be used by this

database for recovery-related files,and does not reflect the amount of

space available in the underlying filesystem or ASM diskgroup.

Wed Feb 22 15:54:24 2017

ALTER SYSTEM SET db_recovery_file_dest_size='30G' SCOPE=BOTH;

Wed Feb 22 15:55:45 2017

Full restore complete of datafile 6 /home/oradata/powerdes/plas01.dbf. Elapsed time: 0:24:53

checkpoint is 13990474255

last deallocation scn is 13972822308

Wed Feb 22 15:56:40 2017

Full restore complete of datafile 1 /home/oradata/powerdes/system01.dbf. Elapsed time: 0:25:47

checkpoint is 13990474255

last deallocation scn is 11565967595

Undo Optimization current scn is 13989882678

Wed Feb 22 15:59:06 2017

ALTER SYSTEM SET db_recovery_file_dest_size='50G' SCOPE=BOTH;

Wed Feb 22 16:21:34 2017

Full restore complete of datafile 12 /home/oradata/powerdes/powerdesk02.dbf. Elapsed time: 0:50:30

checkpoint is 13990474255

last deallocation scn is 13989700725

Wed Feb 22 16:23:31 2017

Full restore complete of datafile 5 /home/oradata/powerdes/powerdesk01.dbf. Elapsed time: 0:52:39

checkpoint is 13990474255

last deallocation scn is 13989700727

7、开始recover database操作

recover database应用归档日志恢复数据文件

RMAN> recover database;

Starting recover at 22-FEB-17

using channel ORA_DISK_1

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=23257

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=23258

channel ORA_DISK_1: reading from backup piece /data/2017-02-20/arch_POWERDES_20170220_8282.bak

channel ORA_DISK_1: piece handle=/data/2017-02-20/arch_POWERDES_20170220_8282.bak tag=TAG20170220T035456

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete,elapsed time: 00:00:03

archived log file name=/oracle/app/oracle/flash_recovery_area/POWERDES/archivelog/2017_02_22/o1_mf_1_23257_dbtm9y5y_.arc thread=1 sequence=23257

channel default: deleting archived log(s)

archived log file name=/oracle/app/oracle/flash_recovery_area/POWERDES/archivelog/2017_02_22/o1_mf_1_23257_dbtm9y5y_.arc RECID=127500 STAMP=936635712

archived log file name=/oracle/app/oracle/flash_recovery_area/POWERDES/archivelog/2017_02_22/o1_mf_1_23258_dbtm9ybt_.arc thread=1 sequence=23258

channel default: deleting archived log(s)

archived log file name=/oracle/app/oracle/flash_recovery_area/POWERDES/archivelog/2017_02_22/o1_mf_1_23258_dbtm9ybt_.arc RECID=127499 STAMP=936635710

unable to find archived log

archived log thread=1 sequence=23259

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of recover command at 02/22/2017 16:35:17

RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 23259 and starting SCN of 13990495654

RMAN>

看到有报错信息，需要将缺失的归档日志从云上的原始备份库copy到本地自己机房的oracle服务器上，或者直接恢复到SCN点13990495654上，这里因为是恢复到临时库，所以选择直接恢复到SCN的13990495654点上，命令为recover database until scn 13990495654;

操作过程如下：

RMAN> recover database until scn 13990495654;

Starting recover at 22-FEB-17

using channel ORA_DISK_1

starting media recovery

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

Finished recover at 22-FEB-17

RMAN>

附带recovery过程报错处理：

报错信息：

db_recovery_file_dest_size of 15360 MB is0.21% used. This is a

user-specified limit on the amount of spacethat will be used by this

database for recovery-related files,anddoes not reflect the amount of

space available in the underlyingfilesystem or ASM diskgroup.

处理方案：

alter system set db_recovery_file_dest_size= 50g scope=both;

8、打开数据库

#然后使用resetlogs打开数据库，成功了。

sql> alter database open resetlogs;

Database altered.

sql>

猜你在找的Oracle相关文章

[Oracle]记一次由sequence引发的enq sv-contention等待事件
数据库版本：11.2.0.4 RAC(1)问题现象从EM里面可以看到，在23号早上8：45~8：55时，数据库...
oracle统计信息的锁定与解锁
（一）问题背景最近在对一个大约200万行数据的表查看执行计划时，发现存在异常，理论上应该...
【转】RMAN删除过期备份或非过期备份
（一）删除备份--DELETE命令用于删除RMAN备份记录及相应的物理文件。当使用RMAN执行备份操...
数据恢复顾问（DRA）
（1）DRA介绍 数据恢复顾问（Data Recovery Advise）是一个诊断和修复数据库的工具，DRA能...
RMAN备份与恢复（一）--认识RMAN
RMAN（Recovery Manager）是Oracle恢复管理器的简称，是集数据库备份（backup）、修复（re...
RMAN备份与恢复（二）--RMAN常用操作学习
（1）连接目标数据库 在RMAN中可以建立与目标数据库或恢复目录数据库的连接。与目标数据库...
ORA-00245问题总结
（1）问题描述 在进行数据库归档备份时（备份归档日志文件和控制文件），有时成功，有时失...
使用RMAN对数据文件进行恢复
（1）备份数据库 在使用RMAN进行数据库恢复之前，先用RMAN进行全库备份 （2）删除数据文件...
Oracle使用fy_recover_data恢复truncate删除的数据
(一)truncate操作概述在生产中，truncate是使用较多的命令，在使用不当的情况下，往往会造...
备份与恢复（四）--RMAN使用恢复目录catalog来保存档案资料库
（一）恢复目录概述RMAN档案资料库用于存储数据库备份、修复以及恢复所需的信息。这些信息...
MySQLMsSQLOracleSqlitePostgre SQLMariadbMongoDBNoSQLHBaseJDBC
undo日志persistent-cmysql-error-postal-codesql-match-almysql-5.6mysql-8.0database-tri安装路径系统错误data_dir丢失文件主从同步sql_mode数据库目录匿名用户character_seID归零数据库位置查询表重复字段查询字段截断日志SUSPECT7391Remote ServeLinked Serve玄学问题登录不上开启远程访问