1.迁移数据文件到asm
1)数据库一致性情况下迁移:
将数据库启动到mount状态,生成rman copy 语句,然后在rman中执行:
sql> startup mount
sql> select q'(copy datafile ')'||name||q'(' to '+oradata';)' from v$datafile;
q'(copydatafile')'||name||q'('to'+oradata';)'
-------------------------------------------------------------------------------
copy datafile 'd:\nsoadp\system01.dbf' to '+oradata';
copy datafile 'd:\nsoadp\sysaux01.dbf' to '+oradata';
copy datafile 'f:\nsoadp\undotbs01.dbf' to '+oradata';
copy datafile 'd:\nsoadp\users01.dbf' to '+oradata';
rman target /
rman> run
2> {
3> copy datafile 'd:\nsoadp\system01.dbf' to '+oradata';
4> copy datafile 'd:\nsoadp\sysaux01.dbf' to '+oradata';
5> copy datafile 'f:\nsoadp\undotbs01.dbf' to '+oradata';
6> copy datafile 'd:\nsoadp\users01.dbf' to '+oradata';
7>}
启动 backup 于 2015-04-15 10:02:44
使用通道 ora_disk_1
通道 ora_disk_1: 启动数据文件副本
输入数据文件: 文件号=00001 名称=d:\nsoadp\system01.dbf
输出文件名=+oradata/nsoa/datafile/system.260.877082571 标记=tag20150415t100250 recid=265 stamp=877082603
通道 ora_disk_1: 数据文件复制完毕, 经过时间: 00:00:35
完成 backup 于 2015-04-15 10:03:25
生成更改控制文件数据名的语句,然后执行更改:
sql> select --t.name,t.file#,c.file#,c.name
2 q'(alter database rename file ')'||c.name||q'(' to ')'||t.name||q'(';)'
3 from v$datafile_copy t left join v$datafile c
4 on t.file#=c.file#
5 where t.name is not null order by completion_time desc;
sql> alter database rename file 'd:\nsoadp\system01.dbf' to '+oradata/nsoa/datafile/system.260.877082571';
....
2)数据库非一致性情况下迁移(归档模式):
使用alterdatabase datafile '' offline命令将数据文件offline,然后同上面的方式一样处理,最后将数据文件online
sql> alter database datafile '/u01/oradata/sydb/user01.dbf' offline;
rman> copy datafile '/u01/oradata/sydb/user01.dbf' to '+oradata';
starting backup at 23-apr-15
using target database control file instead of recovery catalog
allocated channel: ora_disk_1
channel ora_disk_1: sid=180 device type=disk
channel ora_disk_1: starting datafile copy
input datafile file number=00004 name=/u01/oradata/sydb/user01.dbf
output file name=+oradata/sydb/datafile/users.257.877803115 tag=tag20150423t181155 recid=1 stamp=877803123
channel ora_disk_1: datafile copy complete, elapsed time: 00:00:15
finished backup at 23-apr-15
sql> alter database rename file '/u01/oradata/sydb/user01.dbf' to '+oradata/sydb/datafile/users.257.877803115';
sql> alter database datafile '+oradata/sydb/datafile/users.257.877803115' online;
alter database datafile '+oradata/sydb/datafile/users.257.877803115' online
*
error at line 1:
ora-01113: file 4 needs media recovery
ora-01110: data file 4: '+oradata/sydb/datafile/users.257.877803115'
sql> recover datafile '+oradata/sydb/datafile/users.257.877803115';
media recovery complete.
2.迁移日志文件到asm
可以在mount或open状态迁移非current或active日志文件到asm;方法将已有的日志文件(每个日志组必须有一个member)或日志组
(数据库必须有两个日志组存在)删除,然后添加新的日志文件或日志组;
alter database drop logfile member 'e:\nsoadp\redo07.log'; #删除日志文件
alter database drop logfile group 2; #删除日志组
alter database add logfile member '+oradata' to group 7; #添加日志文件
alter database add logfile ( '+oradata','oradata') size 50m; #添加有两个成员的日志组
alter database add logfile '+oradata','oradata' size 50m; #同时添加两个日志组
3.迁移控制文件到asm
首先确定好控制文件位置和名称
sql> show parameter control_file
name type value
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string f:\nsoadp\control01.ctl
修改control_files初始化参数值为asm,然后重启数据库到nomount状态:
sql> alter system set control_files='+oradata' scope=spfile;
sql> shutdown immediate;
sql> startup nomount
使用rman复制当前的控制文件到asm
rman> restore controlfile from 'f:\nsoadp\control01.ctl';
启动 restore 于 2015-04-22 09:40:30
使用目标数据库控制文件替代恢复目录
分配的通道: ora_disk_1
通道 ora_disk_1: sid=70 设备类型=disk
通道 ora_disk_1: 已复制控制文件副本
输出文件名=+oradata/nsoa/controlfile/current.314.877686033
完成 restore 于 2015-04-22 09:40:39
复制代码
注:如果使用备份的控制文件进行还原,而数据库以前有过resetlogs操作或创建控制文件时创建语句指定了resetlogs,则必须以resetlogs方式打开数据库。
sql> alter database mount;
name type value
------------------------------------ ---------------------- ------------------------------
control_file_record_keep_time integer 7
control_files string +oradata/nsoa/controlfile/curr
ent.314.877686033
sql> alter database open;