將資料庫從ASM遷移到檔案系統

leon830216發表於2014-03-01
資料庫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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章