Migration of a Database to ASM

记录一下Migration之前碰到的问题:

1. 昨天用oracleasm安装asm_home的版本是10.2.0.1, db instance认不到asm diskgroup, 升级asm_home到10.2.0.5.0, 和ORACLE_HOME一样的版本
2. 在asm diskgroup上创建tablespace出现以下错误,

create tablespace sidtest datafile '+DATA/asmtest_01.dbf' size 10m
*
ERROR at line 1:
ORA-01119: error in creating database file '+DATA/asmtest_01.dbf'
ORA-15012: ASM file 'asmtest_01.dbf' does not exist
ORA-17502: ksfdcre:5 Failed to create file +DATA/asmtest_01.dbf
ORA-15081: failed to submit an I/O operation to a disk

3. 怀疑oracle用户对/dev/raw/中的裸设备没有读写权限,

[oracleasm@CS-RHEL raw]$ ls -l
total 0
crw------- 1 oracleasm asmdba 162, 1 Jun  2 17:05 raw1
crw------- 1 oracleasm asmdba 162, 2 Jun  2 17:04 raw2
crw------- 1 oracleasm asmdba 162, 3 Jun  2 17:04 raw3
crw------- 1 oracleasm asmdba 162, 4 Jun  2 17:04 raw4

[oracleasm@CS-RHEL raw]$ chmod a+rw *

[oracleasm@CS-RHEL raw]$ ls -l
total 0
crw-rw-rw- 1 oracleasm asmdba 162, 1 Jun  2 17:24 raw1
crw-rw-rw- 1 oracleasm asmdba 162, 2 Jun  2 17:19 raw2
crw-rw-rw- 1 oracleasm asmdba 162, 3 Jun  2 17:19 raw3
crw-rw-rw- 1 oracleasm asmdba 162, 4 Jun  2 17:19 raw4

4. 重启asm instance, create tablespace依然失败,这次是600错误

idle> create tablespace sidtest datafile '+DATA/asmtest_01.dbf' size 10m;

ERROR at line 1:
ORA-00600: internal error code, arguments: [kfmsFindGrp00], [Unknown error in NM], [], [], [], [],
[], []

5. 重启database instance, tablespace 创建成功. asm diskgroup终于可用了.

Migrate 用到的脚本:

重启instance

SHUTDOWN IMMEDIATE
startup mount

backup datafile copy到ASM Diskgroup

CONFIGURE DEVICE TYPE DISK PARALLELISM 4;
BACKUP AS COPY DATABASE FORMAT '+DATA';

backup spfile 到ASM Diskgroup

run {
   BACKUP AS BACKUPSET SPFILE;
   RESTORE SPFILE TO "+DATA/spfile";
}

新建pfile指向+DATA上面的spfile

/tmp/pfile.ora
SPFILE=+DATA/spfile

重启instance, 设置新的controlfile位置

SHUTDOWN IMMEDIATE
startup nomount pfile='/tmp/pfile.ora'
alter system set control_file='+DATA/ct1.f' scope=spfile sid='*';

重启instance, restore controlfile到+DATA, switch database 到diskgroug上, 直接open database, 不用recover database;

SHUTDOWN IMMEDIATE
startup nomount pfile='/tmp/pfile.ora'
restore control file from '/home/u02/app/oracle/product/11.1.0/db_1/oradata/SIDGD/control01.ctl';
alter database mount;
switch database to copy;
alter database open;

最后, 将pfile搬到$ORACLE_HOME/dbs, 这样Oracle每次启动都可以识别到+DATA/spfile

[oracle@CS-RHEL dbs]$ mv /tmp/pfile.ora initasmsid.ora

Ref:
10R1: http://download.oracle.com/docs/cd/B13789_01/server.101/b10734/rcmasm.htm
10R2: http://download.oracle.com/docs/cd/B19306_01/backup.102/b14191/rcmasm.htm#BRADV12121 (利用increment backup, 减少停机时间)

PS: 最后记得把online redo log 和temp tablespace Migrate到ASK上面

declare
   cursor rlc is
      select group# grp, thread# thr, bytes/1024 bytes_k, 'NO' srl
        from v$log
      union
      select group# grp, thread# thr, bytes/1024 bytes_k, 'YES' srl
        from v$standby_log
      order by 1;
   stmt     varchar2(2048);
   swtstmt  varchar2(1024) := 'alter system switch logfile';
   ckpstmt  varchar2(1024) := 'alter system checkpoint global';
begin
   for rlcRec in rlc loop
      if (rlcRec.srl = 'YES') then
         stmt := 'alter database add standby logfile thread ' ||
                 rlcRec.thr || ' ''+DATA'' size ' || 
                 rlcRec.bytes_k || 'K';
         execute immediate stmt;
         stmt := 'alter database drop standby logfile group ' || rlcRec.grp;
         execute immediate stmt;
      else
         stmt := 'alter database add logfile thread ' ||
                 rlcRec.thr || ' ''+DATA'' size ' ||  
                 rlcRec.bytes_k || 'K';
         execute immediate stmt;
         begin
            stmt := 'alter database drop logfile group ' || rlcRec.grp;
            dbms_output.put_line(stmt);
            execute immediate stmt;
         exception
            when others then
               execute immediate swtstmt;
               execute immediate ckpstmt;
               execute immediate stmt;
         end;
      end if;
   end loop;
end;

*
ERROR at line 1:
ORA-00350: log 3 of instance asmsid (thread 1) needs to be archived
ORA-00312: online log 3 thread 1:
'/home/u02/app/oracle/product/11.1.0/db_1/oradata/asmsid/redo03.log'
ORA-06512: at line 34
ORA-01623: log 3 is current log for instance asmsid (thread 1) - cannot drop
ORA-00312: online log 3 thread 1:
'/home/u02/app/oracle/product/11.1.0/db_1/oradata/asmsid/redo03.log'


sys@ASMSID> alter system archive log sequence 24 to '/home/rman/asmsid/';

System altered.
sys@ASMSID> alter database drop logfile group 3;
Database altered.

idle> @log

    GROUP#    THREAD#  SEQUENCE#   FIRST_CHANGE# STATUS           ARC      BYTES
---------- ---------- ---------- --------------- ---------------- --- ----------
MEMBER
------------------------------------------------------------
         1          1         25          337049 CURRENT          NO    52428800
+DATA/asmsid/onlinelog/group_1.264.720657717

         2          1          0               0 UNUSED           YES   52428800
+DATA/asmsid/onlinelog/group_2.260.720657723

         4          1          0               0 UNUSED           YES   52428800
+DATA/asmsid/onlinelog/group_4.256.720657711


RMAN> run {
2> set newname for tempfile 1 to '+DATA';
3> switch tempfile all;
4> }

发表评论

电子邮件地址不会被公开。 必填项已用*标注

您可以使用这些HTML标签和属性: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>