迁移Oracle数据库文件到ASM

阅读导航

迁移数据文件到ASM
迁移日志文件到ASM
迁移控制文件到ASM

迁移数据文件到ASM数据库一致性情况下迁移:将数据库启动到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:NSOADPSYSTEM01.DBF" to "+oradata";
copy datafile "D:NSOADPSYSAUX01.DBF" to "+oradata";
copy datafile "F:NSOADPUNDOTBS01.DBF" to "+oradata";
copy datafile "D:NSOADPUSERS01.DBF" to "+oradata";
SQL> exit
D:>rman target /
RMAN> run
2> {
3> copy datafile "D:NSOADPSYSTEM01.DBF" to "+oradata";
4> copy datafile "D:NSOADPSYSAUX01.DBF" to "+oradata";
5> copy datafile "F:NSOADPUNDOTBS01.DBF" to "+oradata";
6> copy datafile "D:NSOADPUSERS01.DBF" to "+oradata";
7>}
启动 backup 于 2015-04-15 10:02:44
使用通道 ORA_DISK_1
通道 ORA_DISK_1: 启动数据文件副本
输入数据文件: 文件号=00001 名称=D:NSOADPSYSTEM01.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:NSOADPSYSTEM01.DBF" to "+ORADATA/nsoa/datafile/system.260.877082571";数据库非一致性情况下迁移（归档模式）:
使用 alter database datafile "" offline 命令将数据文件offline,然后同上面的方式一样处理,最后将数据文件onlineSQL> 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-15SQL> 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.
SQL> alter database datafile "+ORADATA/sydb/datafile/users.257.877803115" online;迁移日志文件到ASM可以在mount或open状态迁移非current或active日志文件到asm;方法将已有的日志文件（每个日志组必须有一个member）或日志组（数据库必须有两个日志组存在）删除,然后添加新的日志文件或日志组;alter database drop logfile member "E:NSOADPREDO07.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; #同时添加两个日志组迁移控制文件到ASM 首先确定好控制文件位置和名称SQL> show parameter control_file
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string F:NSOADPCONTROL01.CTL 修改control_files初始化参数值为asm,然后重启数据库到nomount状态:SQL> alter system set control_files="+oradata" scope=spfile;
SQL> shutdown immediate;
SQL> startup nomount使用rman复制当前的控制文件到asmrman target /
RMAN> restore controlfile from "F:NSOADPCONTROL01.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;
SQL> show parameter control_fileNAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
control_file_record_keep_time integer 7
control_files string +ORADATA/nsoa/controlfile/curr
ent.314.877686033
SQL> alter database open; --Then end如何将ASM中的数据文件复制到操作系统中 http://www.linuxidc.com/Linux/2013-08/88418.htmOracle 11g RAC ASM磁盘全部丢失后的恢复 http://www.linuxidc.com/Linux/2013-07/87000.htmOracle 11g从入门到精通 PDF+光盘源代码 http://www.linuxidc.com/Linux/2013-06/85670.htmRHEL6 ASM方式安装Oracle 11g R2 http://www.linuxidc.com/Linux/2013-06/86002.htmOracle 10g 手工创建ASM数据库 http://www.linuxidc.com/Linux/2013-01/78229.htmOracle 10g R2创建ASM实例Step By Step http://www.linuxidc.com/Linux/2014-05/101821.htm更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址