[数据迁移] 数据库从文件系统迁移到 ASM 存储

前提条件: 磁盘组 DG1 DG2 均以创建并挂载

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='+DG1' scope=spfile;

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

2-3. db_recovery_file_dest
SQL> alter system set db_recovery_file_dest='+DG2';


3. 迁移文件

3-1. 参数文件
SQL> create pfile from spfile;
SQL> create spfile='+DG1/asmdb/spfileasmdb.ora' from pfile;
SQL> shutdown immediate
$ mv initasmdb.ora initasmdb.ora.bak
$ cp initasmdb.ora.bak initasmdb.ora
$ vim initasmdb.ora
spfile='+DG1/asmdb/spfileasmdb.ora'

3-2. 控制文件
$ rman target /
RMAN> startup nomount
RMAN> restore controlfile from '/data/control01.ctl';
RMAN> alter database mount;

3-3. 数据文件
RMAN> backup as copy database format '+DG1';

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

3-4. 临时文件
SQL> select name,status,enabled from v$tempfile;
SQL> alter tablespace temp add tempfile '+DG1';
SQL> alter tablespace temp drop tempfile '/data/temp01.dbf';

3-5. 日志文件
SQL> alter database add logfile member '+DG1' to group 1;
SQL> alter database add logfile member '+DG1' to group 2;
SQL> alter database add logfile member '+DG1' to group 3;
SQL> alter system switch logfile;
SQL> alter database drop logfile member '/data/asmdb/redo01.log';
SQL> alter database drop logfile member '/data/asmdb/redo02.log';
SQL> alter database drop logfile member '/data/asmdb/redo03.log';


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';