遷移資料庫到ASM

有個遷移到ASM儲存的需求，今天研究了

覺得用RMAN的COPY+SWITCH應該是個能最短化停機時間的方法

下面記錄下過程

SYS@SOURCE10 > select file# from v$datafile;

     FILE#
----------
         1
         2
         3
         5
         6

copy datafile 1 to '+DG1';
copy datafile 2 to '+DG1';
copy datafile 3 to '+DG1';
copy datafile 5 to '+DG1';
copy datafile 6 to '+DG1';

shutdown immediate;
startup mount

switch database to copy;

alter database add logfile group 4 ('+DG1');
alter database add logfile group 5 ('+DG1');
alter database add logfile group 6 ('+DG1');

copy current controlfile to '+DG1';
alter system set control_files='+DG1/source10/controlfile/backup.261.695999269' scope=spfile;

shutdown immediate;
startup mount;
recover database;
alter database open resetlogs;

SYS@SOURCE10 > select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
         1          1          1   52428800          1 NO  CURRENT                3117688 2009-08-27 13:11:09
         2          1          0   52428800          1 YES UNUSED                       0
         3          1          0   52428800          1 YES UNUSED                       0
         4          1          0  104857600          1 YES UNUSED                       0
         5          1          0  104857600          1 YES UNUSED                       0
         6          1          0  104857600          1 YES UNUSED                       0
        
alter system archive log current ;
alter system archive log current ;
alter system archive log current ;
alter system archive log current ;
alter system archive log current ;

SYS@SOURCE10 > select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------------------
         1          1          1   52428800          1 YES ACTIVE                 3117688 2009-08-27 13:11:09
         2          1          2   52428800          1 YES ACTIVE                 3117986 2009-08-27 13:15:40
         3          1          3   52428800          1 YES ACTIVE                 3117989 2009-08-27 13:15:42
         4          1          4  104857600          1 YES ACTIVE                 3118000 2009-08-27 13:15:44
         5          1          5  104857600          1 YES ACTIVE                 3118033 2009-08-27 13:16:51
         6          1          6  104857600          1 NO  CURRENT                3118038 2009-08-27 13:16:53

alter database drop logfile group 1;
alter database drop logfile group 2;
alter database drop logfile group 3;

CREATE SMALLFILE TEMPORARY TABLESPACE TMP TEMPFILE '+DG1' SIZE 20M;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TMP;

drop tablespace temp;

SYS@SOURCE10 > select name from v$datafile
  2  union all
  3  select name from v$tempfile;

NAME
--------------------------------------------------------
+DG1/source10/datafile/system.258.695998419
+DG1/source10/datafile/undotbs1.257.695998465
+DG1/source10/datafile/sysaux.256.695998483
+DG1/source10/datafile/zhangqiaoc.259.695998517
+DG1/source10/datafile/zhangqiaoc_test.260.695998543
+DG1/source10/tempfile/tmp.265.696000131

SYS@SOURCE10 > select member from v$logfile;

MEMBER
--------------------------------------------------------
+DG1/source10/onlinelog/group_4.262.695999173
+DG1/source10/onlinelog/group_5.263.695999213
+DG1/source10/onlinelog/group_6.264.695999221

SYS@SOURCE10 > SELECT NAME FROM v$controlfile;

NAME
--------------------------------------------------------
+DG1/source10/controlfile/backup.261.695999269

停機時間應該主要是恢復的時間

日誌檔案，臨時檔案這些都是可以聯機做的。不過測試的時候沒想到這些

以前的資料檔案可以catalog下進來當備份用

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/8242091/viewspace-613388/，如需轉載，請註明出處，否則將追究法律責任。