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

在日常工作中,我们经常会遇到需要将使用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存储备份恢复到本地磁盘的全部过程。

发表评论