在日常工作中，我们经常会遇到需要将使用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/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/24162410/viewspace-1813773/
扫一扫
442
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
