ITPub博客

首页 > 数据库 > Oracle > Oracle 11G Rman备份ASM数据恢复到本地磁盘实战

Oracle 11G Rman备份ASM数据恢复到本地磁盘实战

原创 Oracle 作者:yangsir1 时间:2015-10-11 17:15:39 0 删除 编辑

在日常工作中,我们经常会遇到需要将使用ASM存储的数据迁移到本地磁盘中,迁移之后的数据库可用于测试等用途。可以选择的工具很多,exp/imp、 expdp/impdp、rman,前两种方法因为比较适合数据量较少时且之前已多次使用,所以此次选用rman来进行数据从ASM存储迁移到本地磁盘存储。

接上文,已经安装了1台 Oracle 11G 数据库,并将数据库升级补丁至 11.2.0.4.4,为什么是这个版本?因为要与源库版本保持一致,源库是跑在ASM存储上的单实例数据库,本文我们使用Rman备份恢复的方法将ASM存储的实例迁移到本地磁盘存储的实例。

1、源库进行RMAN备份

#!/bin/bash 
#level=0# 
export TMP=/tmp
export TMPDIR=/tmp 
export ORACLE_BASE=/u01/app/oracle 
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 
export ORACLE_SID=cmbusdw 
export PATH=$PATH:$ORACLE_HOME/bin: 
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib 
export BAKFILE=/u01/oraclebak/bak0.log
rman target / msglog $BAKFILE << EOF
run{ 
allocate channel c1 device type disk; 
allocate channel c2 device type disk; 
allocate channel c3 device type disk; 
allocate channel c4 device type disk; 
allocate channel c5 device type disk; 
CONFIGURE CONTROLFILE AUTOBACKUP ON; 
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/oraclebak/conf_%F_%T.tar.bz2'; 
configure device type disk parallelism 1 backup type to compressed backupset; 
shutdown immediate; 
startup mount; 
backup incremental level=0 filesperset 5 database format '/u01/oraclebak/bak_%T_%U_%t_%d.tar.bz2'; alter database open; release channel c1; release channel c2; release channel c3; release channel c4; release channel c5; } EOF

2、源库创建pfile文件

create pfile='/u01/pfile.ora' from spfile;

内容如下:

cmbusdw.__db_cache_size=3422552064 cmbusdw.__java_pool_size=50331648 cmbusdw.__large_pool_size=67108864 cmbusdw.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment cmbusdw.__pga_aggregate_target=2332033024 cmbusdw.__sga_target=4345298944 cmbusdw.__shared_io_pool_size=0 cmbusdw.__shared_pool_size=754974720 cmbusdw.__streams_pool_size=0 *.audit_file_dest='/u01/app/oracle/admin/cmbusdw/adump' *.audit_trail='db' *.compatible='11.2.0.4.0' *.control_file_record_keep_time=32 *.control_files='+DATA/cmbusdw/controlfile/current.264.863427043' *.db_block_size=8192 *.db_create_file_dest='+DATA' *.db_domain='' *.db_name='cmbusdw' *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=cmbusdwXDB)' *.log_archive_dest_1='location=+DATA' *.log_archive_format='arch_%t_%s_%r.arc' *.memory_target=6673137664 *.open_cursors=300 *.processes=500 *.remote_login_passwordfile='EXCLUSIVE' *.sessions=555 *.undo_tablespace='UNDOTBS1'

3、创建目标库实例【可选】
为了方便,我直接在目标库建立一个同名的实例,这样就不用自己建立那些目录等文件,我也经常使用这种方法来做异机恢复,感觉很省心。当然你也可以自己手动建立目录的方式,然后做数据库恢复。
4、从源库拷贝pfile文件
修改后内容如下: ##主要就是归档存储路径和控制文件的存储路径,修改完控制文件存储的路径后,就可以将控制文件备份恢复到新的路径了。

拷贝到/u01下

cmbusdw.__db_cache_size=16978542592 cmbusdw.__java_pool_size=402653184 cmbusdw.__large_pool_size=469762048 cmbusdw.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment cmbusdw.__pga_aggregate_target=6777995264 cmbusdw.__sga_target=20266876928 cmbusdw.__shared_io_pool_size=0 cmbusdw.__shared_pool_size=2281701376 cmbusdw.__streams_pool_size=0 *.audit_file_dest='/u01/app/oracle/admin/cmbusdw/adump' *.audit_trail='db' *.compatible='11.2.0.4.0' *.control_files='/u01/app/oracle/oradata/cmbusdw/control01.ctl','/u01/app/oracle/fast_recovery_area/cmbusdw/control02.ctl' *.db_block_size=8192 *.db_domain='' *.db_name='cmbusdw' *.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area' *.db_recovery_file_dest_size=4385144832 *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=cmbusdwXDB)' *.log_archive_dest_1='location=/u01/app/oracle/arch' *.log_archive_format='%t_%s_%r.dbf' *.nls_language='SIMPLIFIED CHINESE' *.nls_territory='CHINA' *.open_cursors=300 *.pga_aggregate_target=6742343680 *.processes=1000 *.remote_login_passwordfile='EXCLUSIVE' *.sessions=1105 *.sga_target=20227031040 *.undo_tablespace='UNDOTBS1'

5、目标库开始恢复

  • 关闭数据库
SQL> shutdown immediate;
  • 创建spfile文件
create spfile from pfile=‘/u01/pfile.ora’;
  • 启动数据库到nomount状态
SQL>startup nomount;
  • RMAN恢复控制文件
RMAN> restore controlfile from '/u01/bak/conf_c-1058246178-20150823-00_20150823.tar.bz2'; Starting restore at 2015-08-24 11:21:57 using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1473 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 output file name=/u01/app/oracle/oradata/cmbusdw/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/cmbusdw/control02.ctl Finished restore at 2015-08-24 11:21:58 启动数据库到mount状态 RMAN> startup mount; database is already started
database mounted
released channel: ORA_DISK_1 

查看恢复完的控制文件存储的数据库文件路径,可以看到还是ASM的存储路径 +DATA

RMAN> report schema; Starting implicit crosscheck backup at 2015-08-24 11:22:41 allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1473 device type=DISK Crosschecked 9 objects Finished implicit crosscheck backup at 2015-08-24 11:22:49 Starting implicit crosscheck copy at 2015-08-24 11:22:49 using channel ORA_DISK_1 Finished implicit crosscheck copy at 2015-08-24 11:22:49 searching for all files in the recovery area
cataloging files... no files cataloged

RMAN-06139: WARNING: control file is not current for REPORT SCHEMA Report of database schema for database with db_unique_name CMBUSDW List of Permanent Datafiles =========================== File Size(MB) Tablespace RB segs Datafile Name ---- -------- -------------------- ------- ------------------------ 1 0 SYSTEM *** +DATA/cmbusdw/datafile/system.266.863426983 2 0 SYSAUX *** +DATA/cmbusdw/datafile/sysaux.261.863426983 3 0 UNDOTBS1 *** +DATA/cmbusdw/datafile/undotbs1.260.863426983 4 0 USERS *** +DATA/cmbusdw/datafile/users.265.863426983 5 0 CM_BASE_DW_01 *** +DATA/cmbusdw/datafile/cm_base_dw_01.256.863451747 6 0 CM_PART_DW_01 *** +DATA/cmbusdw/datafile/cm_part_dw_01.268.863451779 7 0 CM_PART_DW_02 *** +DATA/cmbusdw/datafile/cm_part_dw_02.269.863451783 8 0 CM_PART_DW_03 *** +DATA/cmbusdw/datafile/cm_part_dw_03.270.863451787 9 0 CM_PART_DW_04 *** +DATA/cmbusdw/datafile/cm_part_dw_04.271.863451791 10 0 CM_PART_DW_05 *** +DATA/cmbusdw/datafile/cm_part_dw_05.272.863451795 11 0 CM_PART_DW_06 *** +DATA/cmbusdw/datafile/cm_part_dw_06.273.863451801 12 0 CM_PART_DW_07 *** +DATA/cmbusdw/datafile/cm_part_dw_07.274.863451805 13 0 CM_PART_DW_08 *** +DATA/cmbusdw/datafile/cm_part_dw_08.275.863451809 14 0 CM_PART_DW_09 *** +DATA/cmbusdw/datafile/cm_part_dw_09.276.863451813 15 0 CM_PART_DW_10 *** +DATA/cmbusdw/datafile/cm_part_dw_10.277.863451815 16 0 CM_BASE_SPT_01 *** +DATA/cmbusdw/datafile/cm_base_spt_01.278.863885921 17 0 TS_RMAN *** +DATA/cmbusdw/datafile/ts_rman.279.863887451 18 0 CM_BASE_DW_01 *** +DATA/cmbusdw/datafile/cm_base_dw_01.756.864722141 19 0 CM_BASE_DW_01 *** +DATA/cmbusdw/datafile/cm_base_dw_01.755.864722161 20 0 CM_BASE_GH_01 *** +DATA/cmbusdw/datafile/cm_base_gh_01.658.865780203 21 0 CM_BASE_DW_01 *** +DATA/cmbusdw/datafile/cm_base_dw_01.578.867322823 22 0 CM_BASE_DW_01 *** +DATA/cmbusdw/datafile/cm_base_dw_01.628.867749089 23 0 CM_BASE_DW_01 *** +DATA/cmbusdw/datafile/cm_base_dw_01.629.867749103 24 0 CM_BASE_DW_01 *** +DATA/cmbusdw/datafile/cm_base_dw_01.630.867749111 25 0 CM_BASE_DW_01 *** +DATA/cmbusdw/datafile/cm_base_dw_01.632.867749117 26 0 CM_BASE_DW_01 *** +DATA/cmbusdw/datafile/cm_base_dw_01.633.867749157 27 0 CM_BASE_DW_01 *** +DATA/cmbusdw/datafile/cm_base_dw_01.634.867749163 28 0 CM_BASE_DW_01 *** +DATA/cmbusdw/datafile/cm_base_dw_01.636.867749167 29 0 CM_PART_DW_06 *** +DATA/cmbusdw/datafile/cm_part_dw_06.637.867749187 30 0 CM_PART_DW_05 *** +DATA/cmbusdw/datafile/cm_part_dw_05.638.867749207 31 0 CM_PART_DW_07 *** +DATA/cmbusdw/datafile/cm_part_dw_07.639.867749225 32 0 CM_PART_DW_08 *** +DATA/cmbusdw/datafile/cm_part_dw_08.641.867749261 33 0 CM_BASE_DW_01 *** +DATA/cmbusdw/datafile/cm_base_dw_01.642.867749299 34 0 CM_PART_DW_04 *** +DATA/cmbusdw/datafile/cm_part_dw_04.465.869648727 35 0 CM_BASE_DW_01 *** +DATA/cmbusdw/datafile/cm_base_dw_01.2278.875109275 36 0 CM_BASE_DW_01 *** +DATA/cmbusdw/datafile/cm_base_dw_01.2277.875109295 List of Temporary Files ======================= File Size(MB) Tablespace Maxsize(MB) Tempfile Name ---- -------- -------------------- ----------- -------------------- 1 20 TEMP 32767 +DATA/cmbusdw/tempfile/temp.258.863427049 2 2048 CM_TEMP_DW 30720 +DATA/cmbusdw/tempfile/cm_temp_dw.267.863451751 3 2048 CM_TEMP_GH 30720 +DATA/cmbusdw/tempfile/cm_temp_gh.780.865780211
  • 注册备份文件存储的路径
    由于备份文件存储的路径也发生了变化,所以需要使用catalog start with
    类似的其他命令还有
CATALOG ARCHIVELOG '?/oradata/archive1_30.dbf', '?/oradata/archive1_31.dbf', '?/oradata/archive1_32.dbf'; CATALOG DATAFILECOPY '?/oradata/users01.bak' LEVEL 0; CATALOG START WITH '/tmp/arch_logs'; CATALOG RECOVERY AREA NOPROMPT; CATALOG BACKUPPIECE '?/oradata/01dmsbj4_1_1.bcp'; 
RMAN> catalog start with '/u01/bak/'; searching for all files that match the pattern /u01/bak/ List of Files Unknown to the Database ===================================== File Name: /u01/bak/bak_20150822_6vqf9aj9_1_1_888449641_CMBUSDW.tar.bz2 File Name: /u01/bak/bak_20150822_6pqf95qh_1_1_888444753_CMBUSDW.tar.bz2 File Name: /u01/bak/bak_20150822_6oqf95qh_1_1_888444753_CMBUSDW.tar.bz2 File Name: /u01/bak/bak_20150822_6sqf95qh_1_1_888444753_CMBUSDW.tar.bz2 File Name: /u01/bak/bak_20150822_6rqf95qh_1_1_888444753_CMBUSDW.tar.bz2 File Name: /u01/bak/bak_20150822_6uqf9ad5_1_1_888449445_CMBUSDW.tar.bz2 File Name: /u01/bak/bak_20150822_6tqf99aa_1_1_888448330_CMBUSDW.tar.bz2 File Name: /u01/bak/conf_c-1058246178-20150823-00_20150823.tar.bz2 File Name: /u01/bak/bak_20150822_6qqf95qh_1_1_888444753_CMBUSDW.tar.bz2 Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files... cataloging done
  • 开始恢复数据库 使用set newname更新控制文件中数据文件名称(路径)
RMAN> run{ allocate channel c1 type disk; allocate channel c2 type disk; allocate channel c3 type disk; allocate channel c4 type disk; allocate channel c5 type disk; set newname for datafile 1 to '/u01/app/oracle/oradata/cmbusdw/system.266.863426983'; set newname for datafile 2 to '/u01/app/oracle/oradata/cmbusdw/sysaux.261.863426983'; set newname for datafile 3 to '/u01/app/oracle/oradata/cmbusdw/undotbs1.260.863426983'; set newname for datafile 4 to '/u01/app/oracle/oradata/cmbusdw/users.265.863426983'; set newname for datafile 5 to '/u01/app/oracle/oradata/cmbusdw/cm_base_dw_01.256.863451747'; set newname for datafile 6 to '/u01/app/oracle/oradata/cmbusdw/cm_part_dw_01.268.863451779'; set newname for datafile 7 to '/u01/app/oracle/oradata/cmbusdw/cm_part_dw_02.269.863451783'; set newname for datafile 8 to '/u01/app/oracle/oradata/cmbusdw/cm_part_dw_03.270.863451787'; set newname for datafile 9 to '/u01/app/oracle/oradata/cmbusdw/cm_part_dw_04.271.863451791'; set newname for datafile 10 to '/u01/app/oracle/oradata/cmbusdw/cm_part_dw_05.272.863451795'; set newname for datafile 11 to '/u01/app/oracle/oradata/cmbusdw/cm_part_dw_06.273.863451801'; set newname for datafile 12 to '/u01/app/oracle/oradata/cmbusdw/cm_part_dw_07.274.863451805'; set newname for datafile 13 to '/u01/app/oracle/oradata/cmbusdw/cm_part_dw_08.275.863451809'; set newname for datafile 14 to '/u01/app/oracle/oradata/cmbusdw/cm_part_dw_09.276.863451813'; set newname for datafile 15 to '/u01/app/oracle/oradata/cmbusdw/cm_part_dw_10.277.863451815'; set newname for datafile 16 to '/u01/app/oracle/oradata/cmbusdw/cm_base_spt_01.278.863885921'; set newname for datafile 17 to '/u01/app/oracle/oradata/cmbusdw/ts_rman.279.863887451'; set newname for datafile 18 to '/u01/app/oracle/oradata/cmbusdw/cm_base_dw_01.756.864722141'; set newname for datafile 19 to '/u01/app/oracle/oradata/cmbusdw/cm_base_dw_01.755.864722161'; set newname for datafile 20 to '/u01/app/oracle/oradata/cmbusdw/cm_base_gh_01.658.865780203'; set newname for datafile 21 to '/u01/app/oracle/oradata/cmbusdw/cm_base_dw_01.578.867322823'; set newname for datafile 22 to '/u01/app/oracle/oradata/cmbusdw/cm_base_dw_01.628.867749089'; set newname for datafile 23 to '/u01/app/oracle/oradata/cmbusdw/cm_base_dw_01.629.867749103'; set newname for datafile 24 to '/u01/app/oracle/oradata/cmbusdw/cm_base_dw_01.630.867749111'; set newname for datafile 25 to '/u01/app/oracle/oradata/cmbusdw/cm_base_dw_01.632.867749117'; set newname for datafile 26 to '/u01/app/oracle/oradata/cmbusdw/cm_base_dw_01.633.867749157'; set newname for datafile 27 to '/u01/app/oracle/oradata/cmbusdw/cm_base_dw_01.634.867749163'; set newname for datafile 28 to '/u01/app/oracle/oradata/cmbusdw/cm_base_dw_01.636.867749167'; set newname for datafile 29 to '/u01/app/oracle/oradata/cmbusdw/cm_part_dw_06.637.867749187'; set newname for datafile 30 to '/u01/app/oracle/oradata/cmbusdw/cm_part_dw_05.638.867749207'; set newname for datafile 31 to '/u01/app/oracle/oradata/cmbusdw/cm_part_dw_07.639.867749225'; set newname for datafile 32 to '/u01/app/oracle/oradata/cmbusdw/cm_part_dw_08.641.867749261'; set newname for datafile 33 to '/u01/app/oracle/oradata/cmbusdw/cm_base_dw_01.642.867749299'; set newname for datafile 34 to '/u01/app/oracle/oradata/cmbusdw/cm_part_dw_04.465.869648727'; set newname for datafile 35 to '/u01/app/oracle/oradata/cmbusdw/cm_base_dw_01.2278.875109275'; set newname for datafile 36 to '/u01/app/oracle/oradata/cmbusdw/cm_base_dw_01.2277.875109295'; restore database; switch datafile all; release channel c1; release channel c2; release channel c3; release channel c4; release channel c5; }

恢复完成后再次查看report schmea 数据文件名称(路径)已经改过来了

RMAN> report schema; RMAN-06139: WARNING: control file is not current for REPORT SCHEMA Report of database schema for database with db_unique_name CMBUSDW List of Permanent Datafiles =========================== File Size(MB) Tablespace RB segs Datafile Name ---- -------- -------------------- ------- ------------------------ 1 2640 SYSTEM *** /u01/app/oracle/oradata/cmbusdw/system.266.863426983 2 980 SYSAUX *** /u01/app/oracle/oradata/cmbusdw/sysaux.261.863426983 3 26780 UNDOTBS1 *** /u01/app/oracle/oradata/cmbusdw/undotbs1.260.863426983 4 5 USERS *** /u01/app/oracle/oradata/cmbusdw/users.265.863426983 5 30720 CM_BASE_DW_01 *** /u01/app/oracle/oradata/cmbusdw/cm_base_dw_01.256.863451747 6 2048 CM_PART_DW_01 *** /u01/app/oracle/oradata/cmbusdw/cm_part_dw_01.268.863451779 7 2048 CM_PART_DW_02 *** /u01/app/oracle/oradata/cmbusdw/cm_part_dw_02.269.863451783 8 17248 CM_PART_DW_03 *** /u01/app/oracle/oradata/cmbusdw/cm_part_dw_03.270.863451787 9 30720 CM_PART_DW_04 *** /u01/app/oracle/oradata/cmbusdw/cm_part_dw_04.271.863451791 10 30720 CM_PART_DW_05 *** /u01/app/oracle/oradata/cmbusdw/cm_part_dw_05.272.863451795 11 30720 CM_PART_DW_06 *** /u01/app/oracle/oradata/cmbusdw/cm_part_dw_06.273.863451801 12 30720 CM_PART_DW_07 *** /u01/app/oracle/oradata/cmbusdw/cm_part_dw_07.274.863451805 13 30720 CM_PART_DW_08 *** /u01/app/oracle/oradata/cmbusdw/cm_part_dw_08.275.863451809 14 2090 CM_PART_DW_09 *** /u01/app/oracle/oradata/cmbusdw/cm_part_dw_09.276.863451813 15 100 CM_PART_DW_10 *** /u01/app/oracle/oradata/cmbusdw/cm_part_dw_10.277.863451815 16 100 CM_BASE_SPT_01 *** /u01/app/oracle/oradata/cmbusdw/cm_base_spt_01.278.863885921 17 100 TS_RMAN *** /u01/app/oracle/oradata/cmbusdw/ts_rman.279.863887451 18 30720 CM_BASE_DW_01 *** /u01/app/oracle/oradata/cmbusdw/cm_base_dw_01.756.864722141 19 30720 CM_BASE_DW_01 *** /u01/app/oracle/oradata/cmbusdw/cm_base_dw_01.755.864722161 20 100 CM_BASE_GH_01 *** /u01/app/oracle/oradata/cmbusdw/cm_base_gh_01.658.865780203 21 30720 CM_BASE_DW_01 *** /u01/app/oracle/oradata/cmbusdw/cm_base_dw_01.578.867322823 22 30720 CM_BASE_DW_01 *** /u01/app/oracle/oradata/cmbusdw/cm_base_dw_01.628.867749089 23 30720 CM_BASE_DW_01 *** /u01/app/oracle/oradata/cmbusdw/cm_base_dw_01.629.867749103 24 30720 CM_BASE_DW_01 *** /u01/app/oracle/oradata/cmbusdw/cm_base_dw_01.630.867749111 25 30720 CM_BASE_DW_01 *** /u01/app/oracle/oradata/cmbusdw/cm_base_dw_01.632.867749117 26 30720 CM_BASE_DW_01 *** /u01/app/oracle/oradata/cmbusdw/cm_base_dw_01.633.867749157 27 30720 CM_BASE_DW_01 *** /u01/app/oracle/oradata/cmbusdw/cm_base_dw_01.634.867749163 28 30720 CM_BASE_DW_01 *** /u01/app/oracle/oradata/cmbusdw/cm_base_dw_01.636.867749167 29 29148 CM_PART_DW_06 *** /u01/app/oracle/oradata/cmbusdw/cm_part_dw_06.637.867749187 30 24248 CM_PART_DW_05 *** /u01/app/oracle/oradata/cmbusdw/cm_part_dw_05.638.867749207 31 24048 CM_PART_DW_07 *** /u01/app/oracle/oradata/cmbusdw/cm_part_dw_07.639.867749225 32 25448 CM_PART_DW_08 *** /u01/app/oracle/oradata/cmbusdw/cm_part_dw_08.641.867749261 33 30720 CM_BASE_DW_01 *** /u01/app/oracle/oradata/cmbusdw/cm_base_dw_01.642.867749299 34 10240 CM_PART_DW_04 *** /u01/app/oracle/oradata/cmbusdw/cm_part_dw_04.465.869648727 35 32767 CM_BASE_DW_01 *** /u01/app/oracle/oradata/cmbusdw/cm_base_dw_01.2278.875109275 36 32767 CM_BASE_DW_01 *** /u01/app/oracle/oradata/cmbusdw/cm_base_dw_01.2277.875109295 List of Temporary Files ======================= File Size(MB) Tablespace Maxsize(MB) Tempfile Name ---- -------- -------------------- ----------- -------------------- 1 20 TEMP 32767 +DATA/cmbusdw/tempfile/temp.258.863427049 2 2048 CM_TEMP_DW 30720 +DATA/cmbusdw/tempfile/cm_temp_dw.267.863451751 3 2048 CM_TEMP_GH 30720 +DATA/cmbusdw/tempfile/cm_temp_gh.780.865780211
  • 重建controlfile文件  ###修改redolog路径等

还有redolog的路径需要修改,这里采用trace 控制文件的方法修改。

SQL> alter database backup controlfile to trace; Database altered.

##因为只是需要resetlogs方式打开库,所以删除 Set #1. NORESETLOGS case 那部分,仅保留 Set #2. RESETLOGS case 部分
经过修改后,最终文件内容如下:

-- Set #2. RESETLOGS case -- -- The following commands will create a new control file and use it -- to open the database. -- Data used by Recovery Manager will be lost. -- The contents of online logs will be lost and all backups will -- be invalidated. Use this only if online logs are damaged. -- WARNING! The current control file needs to be checked against -- the datafiles to insure it contains the correct files. The -- commands printed here may be missing log and/or data files. -- Another report should be made after the database has been -- successfully opened. -- After mounting the created controlfile, the following SQL -- statement will place the database in the appropriate -- protection mode: -- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "CMBUSDW" RESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 23904 LOGFILE
  GROUP 1 '/u01/app/oracle/oradata/cmbusdw/redo01.log' SIZE 200M BLOCKSIZE 512, GROUP 2 '/u01/app/oracle/oradata/cmbusdw/redo02.log' SIZE 200M BLOCKSIZE 512, GROUP 3 '/u01/app/oracle/oradata/cmbusdw/redo03.log' SIZE 200M BLOCKSIZE 512, -- STANDBY LOGFILE
DATAFILE '/u01/app/oracle/oradata/cmbusdw/system.266.863426983', '/u01/app/oracle/oradata/cmbusdw/sysaux.261.863426983', '/u01/app/oracle/oradata/cmbusdw/undotbs1.260.863426983', '/u01/app/oracle/oradata/cmbusdw/users.265.863426983', '/u01/app/oracle/oradata/cmbusdw/cm_base_dw_01.256.863451747', '/u01/app/oracle/oradata/cmbusdw/cm_part_dw_01.268.863451779', '/u01/app/oracle/oradata/cmbusdw/cm_part_dw_02.269.863451783', '/u01/app/oracle/oradata/cmbusdw/cm_part_dw_03.270.863451787', '/u01/app/oracle/oradata/cmbusdw/cm_part_dw_04.271.863451791', '/u01/app/oracle/oradata/cmbusdw/cm_part_dw_05.272.863451795', '/u01/app/oracle/oradata/cmbusdw/cm_part_dw_06.273.863451801', '/u01/app/oracle/oradata/cmbusdw/cm_part_dw_07.274.863451805', '/u01/app/oracle/oradata/cmbusdw/cm_part_dw_08.275.863451809', '/u01/app/oracle/oradata/cmbusdw/cm_part_dw_09.276.863451813', '/u01/app/oracle/oradata/cmbusdw/cm_part_dw_10.277.863451815', '/u01/app/oracle/oradata/cmbusdw/cm_base_spt_01.278.863885921', '/u01/app/oracle/oradata/cmbusdw/ts_rman.279.863887451', '/u01/app/oracle/oradata/cmbusdw/cm_base_dw_01.756.864722141', '/u01/app/oracle/oradata/cmbusdw/cm_base_dw_01.755.864722161', '/u01/app/oracle/oradata/cmbusdw/cm_base_gh_01.658.865780203', '/u01/app/oracle/oradata/cmbusdw/cm_base_dw_01.578.867322823', '/u01/app/oracle/oradata/cmbusdw/cm_base_dw_01.628.867749089', '/u01/app/oracle/oradata/cmbusdw/cm_base_dw_01.629.867749103', '/u01/app/oracle/oradata/cmbusdw/cm_base_dw_01.630.867749111', '/u01/app/oracle/oradata/cmbusdw/cm_base_dw_01.632.867749117', '/u01/app/oracle/oradata/cmbusdw/cm_base_dw_01.633.867749157', '/u01/app/oracle/oradata/cmbusdw/cm_base_dw_01.634.867749163', '/u01/app/oracle/oradata/cmbusdw/cm_base_dw_01.636.867749167', '/u01/app/oracle/oradata/cmbusdw/cm_part_dw_06.637.867749187', '/u01/app/oracle/oradata/cmbusdw/cm_part_dw_05.638.867749207', '/u01/app/oracle/oradata/cmbusdw/cm_part_dw_07.639.867749225', '/u01/app/oracle/oradata/cmbusdw/cm_part_dw_08.641.867749261', '/u01/app/oracle/oradata/cmbusdw/cm_base_dw_01.642.867749299', '/u01/app/oracle/oradata/cmbusdw/cm_part_dw_04.465.869648727', '/u01/app/oracle/oradata/cmbusdw/cm_base_dw_01.2278.875109275', '/u01/app/oracle/oradata/cmbusdw/cm_base_dw_01.2277.875109295' CHARACTER SET ZHS16GBK ; -- Configure RMAN configuration record 1 VARIABLE RECNO NUMBER; EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','ON'); -- Configure RMAN configuration record 2 VARIABLE RECNO NUMBER; EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE','DISK TO ''/u01/oraclebak/conf_%F_%T.tar.bz2'''); -- Configure RMAN configuration record 3 VARIABLE RECNO NUMBER; EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DEVICE TYPE','DISK PARALLELISM 1 BACKUP TYPE TO COMPRESSED BACKUPSET'); -- Commands to re-create incarnation table -- Below log names MUST be changed to existing filenames on -- disk. Any one log file from each branch can be used to -- re-create incarnation records. -- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/arch/1_1_824297850.dbf'; -- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/arch/1_1_863427044.dbf'; -- Recovery is required if any of the datafiles are restored backups, -- or if the last shutdown was not normal or immediate. RECOVER DATABASE; -- Database can now be opened zeroing the online logs. ALTER DATABASE OPEN RESETLOGS; -- Commands to add tempfiles to temporary tablespaces. -- Online tempfiles have complete space information. -- Other tempfiles may require adjustment. -- End of tempfile additions. -- 
  • 使用resetlogs方式启动数据库

关闭数据库

SQL>shutdown immediate;

使用修改后的trace文件启动数据库

SQL>@/u01/app/oracle/diag/rdbms/cmbusdw/cmbusdw/trace/cmbusdw_ora_4680.trc;

6、处理临时表空间

从之前的控制文件内容查看,本数据库有3个临时表空间,其中一个是默认的,而且路径仍然还是+DATA ASM的存储路径。
这里我们采用 删除之后再新建的方式处理临时表空间。

--删除临时表空间: DROP TABLESPACE CM_TEMP_DW INCLUDING CONTENTS AND DATAFILES; DROP TABLESPACE CM_TEMP_GH INCLUDING CONTENTS AND DATAFILES; --创建临时表空间: CREATE TEMPORARY TABLESPACE CM_TEMP_DW TEMPFILE '/u01/app/oracle/oradata/cmbusdw/cm_temp_dw.dbf' SIZE 200m; ##设置自动扩展和数据文件最大值 ALTER DATABASE TEMPFILE '/u01/app/oracle/oradata/cmbusdw/cm_temp_dw.dbf' AUTOEXTEND ON NEXT 10M MAXSIZE 20480M; CREATE TEMPORARY TABLESPACE CM_TEMP_GH TEMPFILE '/u01/app/oracle/oradata/cmbusdw/cm_temp_gh.dbf' SIZE 200m; ##设置自动扩展和数据文件最大值 ALTER DATABASE TEMPFILE '/u01/app/oracle/oradata/cmbusdw/cm_temp_gh.dbf' AUTOEXTEND ON NEXT 100M MAXSIZE 20480M; --更改默认临时表空间: ##因为还有一个TEMP的临时表空间需要处理,又不可以直接删除默认临时表空间,所以更改下默认临时表空间 ALTER DATABASE DEFAULT TEMPORARY TABLESPACE CM_TEMP_DW; --删除临时表空间: DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES; --创建临时表空间: CREATE TEMPORARY TABLESPACE TEMP TEMPFILE '/u01/app/oracle/oradata/cmbusdw/temp01.dbf' SIZE 200m; ##设置自动扩展和数据文件最大值 ALTER DATABASE TEMPFILE '/u01/app/oracle/oradata/cmbusdw/temp01.dbf' AUTOEXTEND ON NEXT 10M MAXSIZE 20480M; --再次更改默认临时表空间: ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;

至此就完成了使用Oracle Rman 工具从ASM存储备份恢复到本地磁盘的全部过程。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/24162410/viewspace-1813773/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2010-06-28

  • 博文量
    52
  • 访问量
    80506