oracle恢复数据到asm,oracle11gR2_64之ASM到文件系统的rman异机恢复完整案例

59 阅读 0 评论 39 点赞

我是靠谱客的博主和谐大炮，最近开发中收集的这篇文章主要介绍oracle恢复数据到asm,oracle11gR2_64之ASM到文件系统的rman异机恢复完整案例，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

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

oracle11gR2_64之ASM到文件系统的rman异机恢复完整案例

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

Rman的异机恢复的作用很多，比如数据库迁移，数据库恢复，基于数据库架构调整的还原恢复(比如从RAC--&gt>单实例，ASM--&gt>文件系统)，有时候搭建测试环境也会用到，不过，这种情况下相信选择数据库复制方式比较方便。

这次做的是做基于ASM存储的生产库rman备份到普通文件系统的还原恢复测试，我们原来的数据库架构是基于普通文件系统的单实例，后来改为基于ASM的单实例，所以这次的测试重要性不言而喻：一是测试备份的有效性，二是如果生产库异常崩溃，且恢复时间不确定，又不希望影响业务太多时，可以将生产库的备份还原恢复到以前的文件系统库上，i当然这属于不完全恢复，有数据丢失，当然概率也很小.

下面是异机还原恢复完整案例

(1)环境明细:

源库

目标库

操作系统

Redhat564位

主机名

Rac01

recover.com

192.168.11.112

192.168.11.113

数据库版本

oracle11.2.0.1.0

存储方式

ASM

文件系统

ORACLE_HOME

/u01/app/oracle/product/11.2.0/db_1

ORACLE_SID

bus

(2)源库备份明细:

------&gt>

备份周期为3天的rman的0级备份脚本如下:

----------------------------------&gt>

$RMAN nocatalog target / log $RMAN_LOG_FILE append <

RUN {

allocate channel c1 type disk;

allocate channel c2 type disk;

backup incremental level 0 DATABASE FORMAT '/archive/backup/rman_bus_dest/db_level_0_%U_%T' skip inaccessible TAG='inc_level_0';

sql 'alter system archive log current';

backup FORMAT '/archive/backup/rman_bus_dest/arch_%U_%T' skip inaccessible ARCHIVELOG ALL tag='bus_arch' DELETE ALL INPUT;

backup current controlfile tag='bus_ctl' format='/archive/backup/rman_bus_dest/ctl_%U_%T';

backup spfile tag='bus_spfile' format='/archive/backup/rman_bus_dest/spfile_%U_%T';

release channel c2;

release channel c1;

}

report obsolete;

delete noprompt obsolete;

crosscheck backup;

delete noprompt expired backup;

list backup summary;

EOF

--------&gt>备份出的文件列表:

这里选用的是20140522晚上2点开始的rman的0级备份，pfile是通过spfile生成，然后scp到本地的，没有通过restore pfile from‘’方式进行.

(3)还原恢复过程:

-------&gt>修改scp过来的pfile文件(这里应该注意，源库可能空间较大，关于sga,pga等酌情根据目标库修改)然后startup到nomount状态，restorecontrolfile,

接着通过rman>catalog start with‘’转换备份目录:

[oracle@recover admin]$ cat initbus.ora

bus.__db_cache_size=276824064

bus.__java_pool_size=4194304

bus.__large_pool_size=4194304

bus.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment

bus.__pga_aggregate_target=339738624

bus.__sga_target=503316480

bus.__shared_io_pool_size=0

bus.__shared_pool_size=209715200

bus.__streams_pool_size=0

*.aq_tm_processes=1

*.audit_file_dest='/u01/app/oracle/admin/bus/adump'

*.audit_trail='db'

*.compatible='11.2.0.0.0'

*.control_file_record_keep_time=14

*.control_files='/u01/app/oracle/oradata/bus/control01.ctl'

*.db_block_size=8192

*.db_create_file_dest='/u01/app/oracle/oradata/bus/'

*.db_domain=''

*.db_files=5000

*.db_name='bus'

*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'

*.db_recovery_file_dest_size=4070572032

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=busXDB)'

*.log_archive_format='%t_%s_%r.dbf'

*.memory_target=842006528

*.open_cursors=300

*.processes=500

*.remote_login_passwordfile='EXCLUSIVE'

*.sessions=555

*.undo_tablespace='UNDOTBS1'

----------&gt>startup 到nomount状态:

SQL>startup nomount;

> rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Sun Nov 2 14:22:39 2008

connected to target database: bus(not mounted)

--恢复controlfile

RMAN> restore controlfile from '/home/oracle/back_dest/archbak/ctl_sqp8u854_1_1_20140522';

Starting restore at 02-NOV-08

using channel ORA_DISK_1

channel ORA_DISK_1: restoring control file

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

output filename=/u01/app/oracle/oradata/bus/control01.ctl

Finished restore at 02-NOV-08

RMAN> sql 'alter database mount';

sql statement: alter database mount

released channel: ORA_DISK_1

[oracle@recover ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Fri May 23 18:58:06 2014

connected to target database: BUS (DBID=3983926832, not open)

RMAN> catalog start with '/home/oracle/back_dest/archbak/';

using target database control file instead of recovery catalog

searching for all files that match the pattern /home/oracle/back_dest/archbak/

List of Files Unknown to the Database

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

File Name: /home/oracle/back_dest/archbak/db_level_0_shp8ssd6_1_1_20140522

File Name: /home/oracle/back_dest/archbak/spfile_ssp8u85k_1_1_20140522

File Name: /home/oracle/back_dest/archbak/arch_smp8st1o_1_1_20140522

File Name: /home/oracle/back_dest/archbak/db_level_0_sip8ssdb_1_1_20140522

File Name: /home/oracle/back_dest/archbak/ctl_sqp8u854_1_1_20140522

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

cataloging files...

cataloging done

List of Cataloged Files

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

File Name: /home/oracle/back_dest/archbak/db_level_0_shp8ssd6_1_1_20140522

File Name: /home/oracle/back_dest/archbak/spfile_ssp8u85k_1_1_20140522

File Name: /home/oracle/back_dest/archbak/arch_smp8st1o_1_1_20140522

File Name: /home/oracle/back_dest/archbak/db_level_0_sip8ssdb_1_1_20140522

File Name: /home/oracle/back_dest/archbak/ctl_sqp8u854_1_1_20140522

RMAN> run{

2> set newname for datafile 1 to '/u01/app/oracle/oradata/bus/system.503.843163915';

3> set newname for datafile 2 to '/u01/app/oracle/oradata/bus/sysaux.504.843163917';

4> set newname for datafile 3 to '/u01/app/oracle/oradata/bus/undotbs1.505.843163917';

5> set newname for datafile 4 to '/u01/app/oracle/oradata/bus/users.506.843163917';

6> set newname for datafile 5 to '/u01/app/oracle/oradata/bus/example.263.843164031';

......

set newname for datafile 68 to '/u01/app/oracle/oradata/bus/flt_report_data_201410.dbf';

set newname for datafile 69 to '/u01/app/oracle/oradata/bus/flt_report_data_201411.dbf';

set newname for datafile 70 to '/u01/app/oracle/oradata/bus/flt_report_data_201412.dbf';

restore database;

switch datafile all;

Recover database;

67> 68> 69> 70> 71> 72> 73> 74> recover database;

75> }

executing command: SET NEWNAME

......

executing command: SET NEWNAME

Starting restore at 23-MAY-14

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=19 device type=DISK

allocated channel: ORA_DISK_2

channel ORA_DISK_2: SID=21 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

......

channel ORA_DISK_2: restoring datafile 00070 to /u01/app/oracle/oradata/bus/flt_report_data_201412.dbf

channel ORA_DISK_2: reading from backup piece /archive/backup/rman_bus_dest/db_level_0_shp8ssd6_1_1_20140522

channel ORA_DISK_1: piece handle=/archive/backup/rman_bus_dest/db_level_0_sip8ssdb_1_1_20140522 tag=INC_LEVEL_0

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:13:29

channel ORA_DISK_2: piece handle=/archive/backup/rman_bus_dest/db_level_0_shp8ssd6_1_1_20140522 tag=INC_LEVEL_0

channel ORA_DISK_2: restored backup piece 1

channel ORA_DISK_2: restore complete, elapsed time: 00:19:41

Finished restore at 23-MAY-14

datafile 1 switched to datafile copy

input datafile copy RECID=72 STAMP=848344761 file name=/u01/app/oracle/oradata/bus/system.503.843163915

datafile 2 switched to datafile copy

input datafile copy RECID=73 STAMP=848344762 file name=/u01/app/oracle/oradata/bus/sysaux.504.843163917

datafile 3 switched to datafile copy

input datafile copy RECID=74 STAMP=848344762 file name=/u01/app/oracle/oradata/bus/undotbs1.505.843163917

......

datafile 69 switched to datafile copy

input datafile copy RECID=140 STAMP=848344772 file name=/u01/app/oracle/oradata/bus/flt_report_data_201411.dbf

datafile 70 switched to datafile copy

input datafile copy RECID=141 STAMP=848344772 file name=/u01/app/oracle/oradata/bus/flt_report_data_201412.dbf

Starting recover at 23-MAY-14

using channel ORA_DISK_1

using channel ORA_DISK_2

starting media recovery

Oracle Error:

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below

ORA-01194: file 2 needs more recovery to be consistent

ORA-01110: data file 2: '/u01/app/oracle/oradata/bus/sysaux.504.843163917'

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

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

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

RMAN-03002: failure of recover command at 05/23/2014 19:19:38

RMAN-06053: unable to perform media recovery because of missing log

RMAN-06025: no backup of archived log for thread 1 with sequence 17274 and starting SCN of 1110262216 found to restore

RMAN-06025: no backup of archived log for thread 1 with sequence 17273 and starting SCN of 1110253282 found to restore

RMAN-06025: no backup of archived log for thread 1 with sequence 17272 and starting SCN of 1110247467 found to restore

RMAN-06025: no backup of archived log for thread 1 with sequence 17271 and starting SCN of 1110088491 found to restore

RMAN-06025: no backup of archived log for thread 1 with sequence 17270 and starting SCN of 1109910387 found to restore

RMAN-06025: no backup of archived log for thread 1 with sequence 17269 and starting SCN of 1109902919 found to restore

RMAN-06025: no backup of archived log for thread 1 with sequence 17268 and starting SCN of 1109895456 found to restore

RMAN-06025: no backup of archived log for thread 1 with sequence 17267 and starting SCN of 1109887730 found to restore

RMAN-06025: no backup of archived log for thread 1 with sequence 17266 and starting SCN of 1109751780 found to restore

RMAN-06025: no backup of archived log for thread 1 with sequence 17265 and starting SCN of 1109621814 found to restore

RMAN-06025: no backup of archived log for thread 1 with sequence 17264 and starting SCN of 1109617341 found to restore

RMAN-06025: no backup of archived log for thread 1 with sequence 17263 and starting SCN of 1109614193 found to restore

RMAN-06025: no backup of archived log for thread 1 with sequence 17262 and starting SCN of 1109608876 found to restore

RMAN-06025: no backup of archived log for thread 1 with sequence 17261 and starting SCN of 1109470180 found to restore

RMAN-06025: no backup of archived log for thread 1 with sequence 17260 and starting SCN of 1109384256 found to restore

RMAN-06025: no backup of archived log for thread 1 with sequence 17259 and starting SCN of 1109379300 found to restore

RMAN-06025: no backup of archived log for thread 1 with sequence 17258 and starting SCN of 1109374673 found to restore

RMAN-06025: no backup of archived log for thread 1 with sequence 17257 and starting SCN of 1109370814 found to restore

RMAN-06025: no backup of archived log for thread 1 with sequence 17256 and starting SCN of 1109299231 found to restore

RMAN-06025: no backup of archived log for thread 1 with sequence 17255 and starting SCN of 1109214476 found to restore

RMAN-06025: no backup of archived log for thread 1 with sequence 17254 and starting SCN of 1109208769 found to restore

RMAN-06025: no backup of archived log for thread 1 with sequence 17253 and starting SCN of 1109204568 found to restore

RMAN-06025: no backup of archived log for thread 1 with sequence 17252 and starting SCN of 1109199506 found to restore

RMAN>

------&gt>此时数据库启动到mount状态，但由于controlfile里日志路径还是源库基于ASM的设置，所以恢复找不到路径，这里通过重建controlfile来处理。注:源库没有catalog辅助库，rman设置及备份信息都是存储在controlfile里面，为了减少出错概率，这里忽略了rman的设置，其实意义也不大，毕竟相关目录存在差异.

--------------&gt>recreate ctl:

CREATE CONTROLFILE REUSE DATABASE "BUS" RESETLOGS ARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 3804

LOGFILE

GROUP 1 (

'/u01/app/oracle/oradata/bus/redo0101',

'/u01/app/oracle/oradata/bus/redo0102'

) SIZE 50M BLOCKSIZE 512,

GROUP 2 (

'/u01/app/oracle/oradata/bus/redo0201',

'/u01/app/oracle/oradata/bus/redo0202'

) SIZE 50M BLOCKSIZE 512,

GROUP 3 (

'/u01/app/oracle/oradata/bus/redo0301',

'/u01/app/oracle/oradata/bus/redo0302'

) SIZE 50M BLOCKSIZE 512,

GROUP 4 (

'/u01/app/oracle/oradata/bus/redo0401',

'/u01/app/oracle/oradata/bus/redo0402'

) SIZE 50M BLOCKSIZE 512,

GROUP 5 (

'/u01/app/oracle/oradata/bus/redo0501',

'/u01/app/oracle/oradata/bus/redo0502'

) SIZE 50M BLOCKSIZE 512

-- STANDBY LOGFILE

DATAFILE

'/u01/app/oracle/oradata/bus/system.dbf',

'/u01/app/oracle/oradata/bus/sysaux.dbf',

'/u01/app/oracle/oradata/bus/undotbs1.dbf',

'/u01/app/oracle/oradata/bus/users.dbf',

'/u01/app/oracle/oradata/bus/example.dbf',

'/u01/app/oracle/oradata/bus/flt.dbf',

'/u01/app/oracle/oradata/bus/fltidx.dbf',

'/u01/app/oracle/oradata/bus/flt_data.dbf',

......

'/u01/app/oracle/oradata/bus/flt_report_data_201410.dbf',

'/u01/app/oracle/oradata/bus/flt_report_data_201411.dbf',

'/u01/app/oracle/oradata/bus/flt_report_data_201412.dbf'

CHARACTER SET ZHS16GBK

;

----------------------&gt>recover database

RMAN> recover database;

Starting recover at 27-MAY-14

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

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=17251

channel ORA_DISK_1: reading from backup piece /home/oracle/back_dest/archbak/arch_smp8st1o_1_1_20140522

channel ORA_DISK_1: piece handle=/home/oracle/back_dest/archbak/arch_smp8st1o_1_1_20140522 tag=BUS_ARCH

channel ORA_DISK_1: restored backup piece 1

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

archived log file name=/u01/app/oracle/oradata/arch/1_17250_843164019.dbf thread=1 sequence=17250

archived log file name=/u01/app/oracle/oradata/arch/1_17251_843164019.dbf thread=1 sequence=17251

unable to find archived log

archived log thread=1 sequence=17252

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

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

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

RMAN-03002: failure of recover command at 05/27/2014 10:18:51

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

-----------------&gt>open resetlogs

[oracle@recover ~]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Tue May 27 10:27:12 2014

connected to target database: BUS (DBID=3983926832, not open)

RMAN> alter database open resetlogs;

using target database control file instead of recovery catalog

database opened

RMAN>

-----&gt>temp,undo表空间处理，收集统计信息:

SQL> show parameters undo;

NAME TYPE VALUE

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

undo_management string AUTO

undo_retention integer 900

undo_tablespace string UNDOTBS1

SQL> select tablespace_name from dba_tablespaces where contents='UNDO';

TABLESPACE_NAME

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

UNDOTBS1

SQL> select tablespace_name from dba_tablespaces where contents='TEMPORARY';

TABLESPACE_NAME

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

TEMP

SQL> create temporary tablespace TEMP1 tempfile '/u01/app/oracle/oradata/bus/temp01.dbf' size 500M autoextend off;

Tablespace created.

SQL> alter database default temporary tablespace TEMP1;

Database altered.

SQL> drop tablespace TEMP including contents and datafiles;

Tablespace dropped.

SQL> select tablespace_name from dba_tablespaces where contents='TEMPORARY';

TABLESPACE_NAME

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

TEMP1

SQL> begin

2 dbms_stats.gather_schema_stats(ownname => 'FLT',

3 estimate_percent => dbms_stats.auto_sample_size,

4 method_opt => 'for all columns size repeat',

5 degree => 7);

6 end;

7 /

PL/SQL procedure successfully completed.

SQL>

-------&gt>修改listener.ora,tnsnames.ora

[oracle@recover admin]$ more listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.113)(PORT = 1521))

)

ADR_BASE_LISTENER = /u01/app/oracle

[oracle@recover admin]$ more tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

BUS =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.11.113)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = bus)

)

(4)验证恢复库数据有效性:

--------&gt>数据结构对象对比:

---------- 源库------------------- ----------目标恢复库---------

SQL> select t.object_type,count(object_name) from user_objects t group by t.object_

OBJECT_TYPE COUNT(OBJECT_NAME) OBJECT_TYPE COUNT(OBJECT_NAME)

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

CLUSTER 3 CLUSTER 3

DATABASE LINK 1 DATABASE LINK 1

FUNCTION 7 FUNCTION 7

INDEX 119 INDEX 119

INDEX PARTITION 72 INDEX PARTITION 72

PACKAGE 15 PACKAGE 15

PACKAGE BODY 15 PACKAGE BODY 15

PROCEDURE 1 PROCEDURE 1

SEQUENCE 47 SEQUENCE 47

SYNONYM 40 SYNONYM 40

TABLE 143 TABLE 143

TABLE PARTITION 98 TABLE PARTITION 98

TABLE SUBPARTITION 850 TABLE SUBPARTITION 850

TRIGGER 38 TRIGGER 38

TYPE 3 TYPE 3

VIEW 13 VIEW 13

--------------------&gt>通过last_gps_position表数据验证数据的有效性,因为备份是有5月22号晚上2点进行，所以该表中数据插入日期应该在5月22号2点左右:

select TO_CHAR(t1.GATHER_DATE, 'YYYY-MM-DD hh24:mi:ss') from last_gps_position t1

where t1.GATHER_DATE>TO_DATE('2014-05-22 00:00:00','YYYY-MM-DD hh24:mi:ss')

order by 1;

-------&gt>

(5)遇到问题及解决方法:

A1.由于源库recovery_area_usage较大(5G),测试库空间不足，recovery_area_usage设置较小，导致在做RMAN> catalog start with '/home/oracle/back_dest/archbak/'时进行到中终，异常退出,最后通过在pfile中注释掉db_recovery_file_dest和db_recovery_file_dest_size才得以通过.

A2.做restore database时VirtualBox中目标库空间不足，异常退出，最后通过添加虚拟硬盘，重新restore database完成还原操作;

A3.在做recover database时由于存储方式不同，导致不能应用到archivelog进行recovery,所以通过recreate controlfile来进行解决.

(6)鸣谢，最后真诚感谢cuug-王楠的帮助，那家伙很厉害！当然，这案例中相信有很多不足和值得优化的地方，希望感兴趣的朋友多多交流沟通，谢谢！