1、数据库迁移从 ASM 到文件系统环境：我的数据存储在 ASM 上，如何把所有的数据文件和日志文件,参数文件,控制文件,迁移到/u01/app/oracle/oradata/orcl 下。思路：首先建立迁移所需要的文件夹，注意权限，再依次迁移参数文件、控制文件、数据文件、日志文件、临时文件。1. 先备份数据库：backup database;2. 建立迁移所需要的文件夹：mkdir -p /u01/app/oracle/oradata/orcloracleyutian01 dbs$ ll -d /u01/app/oracle/oradata/orcl/drwxr-xr-x 2 oracle oi
2、nstall 4096 Jun 8 00:16 /u01/app/oracle/oradata/orcl/3. 参数文件修改，将控制文件的路径改为文件系统的路径control_files=/u01/app/oracle/oradata/orcl/control01.ctl,/u01/app/oracle/oradata/orcl/control02.ctl记住原控制文件的信息SQL select name from v$controlfile;NAME-+DATA/orcl/controlfile/current.262.848460253+FRA/orcl/controlfile/curre
3、nt.256.8484602594. 实现参数文件恢复到文件系统，关机用新的参数文件启动实例shutdown immediate;create spfile from pfile;5. 控制文件恢复到文件系统startup nomount;RMAN restore controlfile from +DATA/orcl/controlfile/current.262.848460253;alter datebase mount;从好的控制文件根据你参数文件制定的控制文件取恢复控制文件channel ORA_DISK_1: copied control file copyoutput file
4、name=/u01/app/oracle/oradata/orcl/control01.ctl （参数文件制定的，所以恢复）output file name=/u01/app/oracle/oradata/orcl/control02.ctlFinished restore at 11-OCT-136. 数据文件迁移到文件系统report schemaDatafile Name- - - - -1 690 SYSTEM * +DATA/orcl/datafile/system.260.8484597952 590 SYSAUX * +DATA/orcl/datafile/sysaux.276.
5、8484597953 90 UNDOTBS1 * +DATA/orcl/datafile/undotbs1.264.8484597994 5 USERS * /u01/app/oracle/users01.dbf5 100 EXAMPLE * +DATA/orcl/datafile/example.256.8484603537 20 USERS * +DATA/orcl/datafile/users.257.848513101List of Temporary Files=File Size(MB) Tablespace Maxsize(MB) Tempfile Name- - - - -2
6、10 TEMP1 10 +DATA/orcl/tempfile/temp1.263.848508943run SET NEWNAME FOR DATAFILE 2 TO /u01/app/oracle/oradata/orcl/system01.dbf;SET NEWNAME FOR DATAFILE 1 TO /u01/app/oracle/oradata/orcl/sysaux01.dbf;SET NEWNAME FOR DATAFILE 3 TO /u01/app/oracle/oradata/orcl/undotbs01.dbf;SET NEWNAME FOR DATAFILE 4 T
7、O /u01/app/oracle/oradata/orcl/users01.dbf;SET NEWNAME FOR DATAFILE 5 TO /u01/app/oracle/oradata/orcl/example01.dbf;SET NEWNAME FOR DATAFILE 7 TO /u01/app/oracle/oradata/orcl/test01.dbf;restore database ;SWITCH DATAFILE ALL;recover database;执行上面脚本恢复数据文件到文件系统，恢复完成后就可打开数据库检查数据文件所在地7. 临时文件恢复到文件系统添加一个文件
8、系统的 temp 表，删除+data 中的 temp 表alter tablespace temp add tempfile /u01/app/oracle/oradata/orcl/temp01.dbf size 10M;alter tablespace temp drop tempfile /u01/app/oracle/oradata/orcl/users01.dbf;8. 迁移日志文件SQL select GROUP# ,MEMBER from v$logfile;GROUP# MEMBER- -3 +DATA/orcl/onlinelog/group_3.258.8484602953
9、 +FRA/orcl/onlinelog/group_3.257.8484603052 +DATA/orcl/onlinelog/group_2.259.8484602812 +FRA/orcl/onlinelog/group_2.258.8484602871 +DATA/orcl/onlinelog/group_1.261.8484602671 +FRA/orcl/onlinelog/group_1.259.848460275关闭数据库 shutdown immediate;更改文件夹的权限rootyutian01 # chown oracle:oinstall /u01/app/oracl
10、e/oradata/orcl/*.logrootyutian01 # chmod 775 /u01/app/oracle/oradata/orcl/*.logsu - oracle cd /u01/app/oracle/chmod g=rwx /u01/app/oracle/oradata/orcl/将文件拷贝到文件系统目录su - gridasmcmdcp +DATA/orcl/onlinelog/group_3.258.848460295 /u01/app/oracle/oradata/orcl/red301.logcp +FRA/orcl/onlinelog/group_3.257.84
11、8460305 /u01/app/oracle/oradata/orcl/red302.logcp +DATA/orcl/onlinelog/group_2.259.848460281 /u01/app/oracle/oradata/orcl/red201.logcp +FRA/orcl/onlinelog/group_2.258.848460287 /u01/app/oracle/oradata/orcl/red202.logcp +DATA/orcl/onlinelog/group_1.261.848460267 /u01/app/oracle/oradata/orcl/red101.lo
12、gcp +FRA/orcl/onlinelog/group_1.259.848460275 /u01/app/oracle/oradata/orcl/red102.log刷新控制文件，startup mountalter database rename file +DATA/orcl/onlinelog/group_3.258.848460295 to /u01/app/oracle/oradata/orcl/red301.log;alter database rename file +FRA/orcl/onlinelog/group_3.257.848460305 to /u01/app/o
13、racle/oradata/orcl/red302.log;alter database rename file +DATA/orcl/onlinelog/group_2.259.848460281 to /u01/app/oracle/oradata/orcl/red201.log;alter database rename file +FRA/orcl/onlinelog/group_2.258.848460287 to /u01/app/oracle/oradata/orcl/red202.log;alter database rename file +DATA/orcl/onlinel
14、og/group_1.261.848460267 to /u01/app/oracle/oradata/orcl/red101.log;alter database rename file +FRA/orcl/onlinelog/group_1.259.848460275 to /u01/app/oracle/oradata/orcl/red102.log;alter database open;查看日志组信息SQL select member from v$logfile;MEMBER-/u01/app/oracle/oradata/orcl/red301.log/u01/app/oracle/oradata/orcl/red302.log/u01/app/oracle/oradata/orcl/red201.log/u01/app/oracle/oradata/orcl/red202.log/u01/app/oracle/oradata/orcl/red101.log/u01/app/oracle/oradata/orcl/red102.log迁移完成
