将数据库从ASM迁移到文件系统

支持数据库版本：10gR2

1. 确认所要修改的文件的位置
SQL>
select name from v$controlfile
union all
select name from v$datafile
union all
select name from v$tempfile
union all
select member from v$logfile
union all
select value from v$parameter where name = 'spfile';


2. 修改参数文件

2-1. control_files
SQL> alter system set control_files='/data/control01.ctl' scope=spfile;

2-1. db_create_file_dest
SQL> alter system set db_create_file_dest='';

2-2. db_recovery_file_dest
SQL> alter system set db_recovery_file_dest='/backup';

2-3. db_create_online_log_dest


3. 迁移

3-1. 参数文件
SQL> create pfile from spfile;
SQL> create spfile from pfile;

3-2. 拷贝控制文件
$ rman target /
RMAN> startup nomount
RMAN> restore controlfile from '+DG1/asmdb/controlfile/current.256.791556801';

3-3. 数据文件
RMAN> copy datafile '+DG1/asmdb/datafile/system.260.791556937' to '/data/system01.dbf';
RMAN> copy datafile '+DG1/asmdb/datafile/undotbs1.261.791556989' to '/data/undotbs01.dbf';
RMAN> copy datafile '+DG1/asmdb/datafile/sysaux.262.791556993' to '/data/sysaux01.dbf';
RMAN> copy datafile '+DG1/asmdb/datafile/users.264.791557029' to '/data/users01.dbf';

RMAN> alter database mount;
RMAN> switch database to copy;
RMAN> recover database;
RMAN> alter database open;

3-4. 临时文件
SQL> create temporary tablespace temp tempfile '/data/temp01.dbf' size 20m;
SQL> alter tablespace temp drop tempfile '+DG1/asmdb/datafile/temp.260.791556937';

3-5. 日志文件
SQL> select member from v$logfile;
SQL> alter database add logfile member '/data/redo01.log' to group 1;
SQL> alter database add logfile member '/data/redo02.log' to group 2;
SQL> alter database add logfile member '/data/redo03.log' to group 3;
SQL> alter database drop logfile member '+DG1/asmdb/onlinelog/group_1.257.791556811';
SQL> alter database drop logfile member '+DG1/asmdb/onlinelog/group_2.258.791556849';
SQL> alter database drop logfile member '+DG1/asmdb/onlinelog/group_3.259.791556889';

4. 查看迁移结果
SQL> shutdown immediate
SQL> startup
SQL>
select name from v$datafile
union all
select name from v$tempfile
union all
select name from v$controlfile
union all
select member from v$logfile
union all
select value from v$parameter where name = 'spfile';

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22558114/viewspace-1097572/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22558114/viewspace-1097572/