存储替换的数据库迁移

最新推荐文章于 2021-10-19 07:02:22 发布

cmv71775

最新推荐文章于 2021-10-19 07:02:22 发布

阅读量581

点赞数

文章标签：数据库

存储替换的数据库迁移
需求：旧的存储设备使用年限太久，需要替换，数据库服务器不换，这就涉及将数据库迁移到新的存储上
测试环境：
　　AIX 7.1 ORACLE RAC 11.2.0.4 双节点
测试方案：
　　一. 使用ASM加盘踢盘的方式
　　二. 使用backup as copy+switch的方式

一. 使用ASM加盘踢盘的方式
    对于数据量小的库来说，使用加盘踢盘的方式是非常合适的。优点是不用停机，操作简单，影响可控。所有磁盘组都可以操作。
    1.检查当前测试环境磁盘与磁盘组信息
      SQL> select dg.name,dk.name,dk.group_number,dk.total_mb,failgroup,path,dk.MOUNT_STATUS,dk.HEADER_STATUS
             from v$asm_diskgroup dg,v$asm_disk dk where dg.group_number=dk.group_number order by dk.name;
        NAME     NAME          GROUP_NUMBER   TOTAL_MB FAILGROUP      PATH                 MOUNT_S HEADER_S
        -------- ------------- ------------ ---------- -------------- -------------------- ------- --------
        DATADG   DATADG_0000              1     102400 DATADG_0000    /dev/rhdiskpower4    CACHED MEMBER
        DATADG   DATADG_0001              1     102400 DATADG_0001    /dev/rhdiskpower5    CACHED MEMBER
        FRADG    FRADG_0000               4     102400 FRADG_0000     /dev/rhdiskpower6    CACHED MEMBER
        OCRDG    OCRDG_0000               2      10240 OCRDG_0000     /dev/rhdiskpower1    CACHED MEMBER
        OCRDG    OCRDG_0001               2      10240 OCRDG_0001     /dev/rhdiskpower2    CACHED MEMBER
        OCRDG    OCRDG_0002               2      10240 OCRDG_0002     /dev/rhdiskpower3    CACHED MEMBER

    2.检查可用的磁盘，确认磁盘状态、大小、路径
      SQL> select path,MOUNT_STATUS,HEADER_STATUS,os_mb from v$asm_disk where header_status='CANDIDATE';
        PATH                           MOUNT_S HEADER_STATU      OS_MB
        ------------------------------ ------- ------------ ----------
        /dev/rhdiskpower10             CLOSED CANDIDATE        102400
        /dev/rhdiskpower8              CLOSED CANDIDATE        102400
        /dev/rhdiskpower9              CLOSED CANDIDATE        102400
        /dev/rhdiskpower7              CLOSED CANDIDATE        102400

    3.一条简单的命令实现datadg磁盘组的加盘踢盘(11.2.0.3以上版本支持，其他磁盘组可使用相同操作方法)
      SQL> alter diskgroup datadg
         add disk '/dev/rhdiskpower7','/dev/rhdiskpower8'
         drop disk DATADG_0000,DATADG_0001 rebalance power 8;
      Diskgroup altered.

      观察磁盘rebalance操作是否完成：
      SQL> select * from v$asm_operation;
      GROUP_NUMBER OPERA STAT POWER ACTUAL SOFAR   EST_WORK   EST_RATE EST_MINUTES ERROR_CODE
      ------------ ----- ---- ------ ------- ------ ---------- ---------- ----------- -----------
                 1 REBAL RUN       8       8   6837      61419       6419           7

    这种方式的优点是只需要做一次rebalance，且能够保证有足够的空间。如果磁盘组数据量特别大，rebalance需要很长的时间。
　　几点需要注意的地方：
    1.Rebalance并行度根据系统资源与繁忙程度指定，避免对生产性能造成影响。此处power为8，约60G数据7分钟内完成。
    2.新磁盘加入时一定要先确保是空盘，可使用kfed read 命令检查
    3.旧的存储盘从磁盘组踢除之后，应尽早找机会重启主机，否则在以后进行磁盘维护时，
        可能会出现盘符或设备的Major/Minor number重用，进而引发一些奇怪的现象发生

二. 使用backup as copy+switch的方式
    如果数据量特别大，rebalance的时间会很长。也许我们想尝试更快一点的迁移方法，但这个操作略为复杂。
    思路是这样的：OCRDG和FRADG，仍然采用rebalance的方式，对于数据量巨大的DATADG，则使用backup as copy+switch的方式。
    　select name from v$controlfile
    　 union select name from v$datafile
    　 union select name from v$tempfile
    　 union select member from v$logfile;
    操作之前记录数据库中所有文件的名字，操作过程中会用到。

    1.首先在新存储盘上创建DG
      SQL> create diskgroup DATADGNEW external redundancy disk '/dev/rhdiskpower9','/dev/rhdiskpower10';
      Diskgroup created.

      SQL> select GROUP_NUMBER,name,state,type,total_mb,free_mb,usable_file_mb,offline_disks from v$asm_diskgroup;
      GROUP_NUMBER NAME                 STATE       TYPE     TOTAL_MB    FREE_MB USABLE_FILE_MB OFFLINE_DISKS
      ------------ -------------------- ----------- ------ ---------- ---------- -------------- -------------
                 1 DATADG               MOUNTED     EXTERN     204800     143377         143377             0
                 2 OCRDG                MOUNTED     NORMAL      30720      29796           9778             0
                 3 DATADGNEW            MOUNTED     EXTERN     204800     204748         204748             0
                 4 FRADG                MOUNTED     EXTERN     102400      69212          69212             0

    2.生成数据库备份
       a)创建参数文件到本地
          SQL> create pfile='/home/oracle/inittestdb.ora' from spfile;
            File created.
       b)创建一份控制文件到新磁盘组
          SQL> alter database backup controlfile to '+DATADGNEW';
            Database altered.
       c)创建数据库备份
          这里主要针对数据量非常大的场景，为了尽量减少关库进行恢复的时间，因此先做一次0级数据库备份：
          rman target /
          RMAN> backup incremental level 0 as copy tag 'incr_db' format '+DATADGNEW' database;
            Starting backup at 27-DEC-16
            using channel ORA_DISK_1    ...... --部分输出省略
            including current SPFILE in backup set
            channel ORA_DISK_1: starting piece 1 at 27-DEC-16
            channel ORA_DISK_1: finished piece 1 at 27-DEC-16
            piece handle=+DATADGNEW/testdb/backupset/2016_12_27/nnsnn0_incr_db_0.269.931729345 tag=INCR_DB comment=NONE
            channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
            Finished backup at 27-DEC-16
          RMAN> sql 'alter system switch logfile';

          插入一条测试数据标记一下：
          SQL> insert into t_test1 values(1,to_char(sysdate,'yyyymmdd hh24:mi:ss')||'between incremental backup');
          再做一次1级增量备份：
          RMAN> backup incremental level 1 for recover of copy tag 'incr_db' format '+DATADGNEW' database;
            ......
            channel ORA_DISK_1: piece handle=+DATADGNEW/testdb/backupset/2016_12_27/nnndn1_incr_db_0.272.931730015 tag=INCR_DB
            channel ORA_DISK_1: restored backup piece 1
            channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
            Finished recover at 27-DEC-16

          在全备上恢复增量备份，以追上全备时产生的数据
          RMAN> recover copy of database with tag 'incr_db';

    3.关闭数据库，切换数据文件
       a)关库之前先修改数据库控制文件指向新的磁盘组
          SQL> alter system set control_files='+DATADGNEW/TESTDB/CONTROLFILE/Backup.268.931729343' scope=spfile;
       b)关库，然后打开到nomount状态，恢复控制文件(如果是rac，把另外一个节点也关掉)
          RMAN> shutdown immediate;
          RMAN> startup nomount;
          RMAN> restore controlfile to '+DATADGNEW' from '+DATADG/testdb/controlfile/current.260.925228587';
            Starting restore at 27-DEC-16
            allocated channel: ORA_DISK_1
            channel ORA_DISK_1: SID=252 instance=testdb1 device type=DISK
            channel ORA_DISK_1: copied control file copy
            Finished restore at 27-DEC-16
       c)将数据库mount
          RMAN> alter database mount;
            database mounted
       d)切换数据文件
          RMAN> switch database to copy;
            RMAN-00571: ===========================================================
            RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
            RMAN-00571: ===========================================================
            RMAN-03002: failure of switch to copy command at 12/27/2016 22:42:56
            RMAN-06571: datafile 1 does not have recoverable copy
          报错了，数据文件找不到可恢复的copy。尝试注册一下：
          RMAN> catalog datafilecopy '+DATADGNEW/testdb/datafile/system.260.931729289';
          　catalog datafilecopy '+DATADGNEW/testdb/datafile/sysaux.261.931729303';
          　catalog datafilecopy '+DATADGNEW/testdb/datafile/undotbs1.258.931729259';
          　catalog datafilecopy '+DATADGNEW/testdb/datafile/users.257.931729123';
          　catalog datafilecopy '+DATADGNEW/testdb/datafile/undotbs2.262.931729319';
          　catalog datafilecopy '+DATADGNEW/testdb/datafile/testtb1.263.931729327';
          　catalog datafilecopy '+DATADGNEW/testdb/datafile/testtb1.264.931729333';
          　catalog datafilecopy '+DATADGNEW/testdb/datafile/testtb1.265.931729335';
          　catalog datafilecopy '+DATADGNEW/testdb/datafile/testtb1.266.931729337';
          　catalog datafilecopy '+DATADGNEW/testdb/datafile/testtb1.267.931729339';
          　catalog datafilecopy '+DATADGNEW/testdb/datafile/dbadata.259.931729273';
          　cataloged datafile copy
          RMAN> switch database to copy;
          　datafile 1 switched to datafile copy "+DATADGNEW/testdb/datafile/system.260.931729289"
          　datafile 2 switched to datafile copy "+DATADGNEW/testdb/datafile/sysaux.261.931729303"
          　datafile 3 switched to datafile copy "+DATADGNEW/testdb/datafile/undotbs1.258.931729259"
          　datafile 4 switched to datafile copy "+DATADGNEW/testdb/datafile/users.257.931729123"
          　datafile 5 switched to datafile copy "+DATADGNEW/testdb/datafile/undotbs2.262.931729319"
          　datafile 6 switched to datafile copy "+DATADGNEW/testdb/datafile/testtb1.263.931729327"
          　datafile 7 switched to datafile copy "+DATADGNEW/testdb/datafile/testtb1.264.931729333"
          　datafile 8 switched to datafile copy "+DATADGNEW/testdb/datafile/testtb1.265.931729335"
          　datafile 9 switched to datafile copy "+DATADGNEW/testdb/datafile/testtb1.266.931729337"
          　datafile 10 switched to datafile copy "+DATADGNEW/testdb/datafile/testtb1.267.931729339"
          　datafile 11 switched to datafile copy "+DATADGNEW/testdb/datafile/dbadata.259.931729273"
          切换成功。

    4.恢复并打开数据库
    　　　RMAN> recover database;
            archived log file name=+FRADG/testdb/archivelog/2016_12_27/thread_1_seq_133.1329.931729595 thread=1 sequence=133
            archived log file name=+FRADG/testdb/archivelog/2016_12_27/thread_2_seq_73.897.931730467 thread=2 sequence=73
            archived log file name=+FRADG/testdb/archivelog/2016_12_27/thread_1_seq_134.1337.931730465 thread=1 sequence=134
            archived log file name=+DATADG/testdb/onlinelog/group_3.266.925229575 thread=2 sequence=74
            media recovery complete, elapsed time: 00:00:04
            Finished recover at 27-DEC-16
          RMAN> alter database open resetlogs;
            database opened
       此时数据库可以正常使用了。但RMAN备份不包含临时文件和redo，因此还有一些事情要做。

    5.重建临时文件和redo文件
       a)在新的磁盘组中创建临时文件,删除旧的临时文件
          SQL> alter tablespace temp add tempfile '+DATADGNEW' size 100m;
          SQL> alter database tempfile '+DATADG/testdb/tempfile/temp.263.925228611' drop;
       b)为每一组redo log添加新磁盘组成员,删除原磁盘组上的成员
          SQL> alter database add logfile member '+DATADGNEW' to group 1;
          SQL> alter database add logfile member '+DATADGNEW' to group 2;
          SQL> alter database add logfile member '+DATADGNEW' to group 3;
          SQL> alter database drop logfile member '+DATADG/testdb/onlinelog/group_1.262.925229515';
          SQL> alter database drop logfile member '+DATADG/testdb/onlinelog/group_2.261.925229521';
          SQL> alter database drop logfile member '+DATADG/testdb/onlinelog/group_3.266.925229575';

    6.检查确认，删除原磁盘组
       a)检查所有文件已经迁移到新的磁盘组
          select name from v$controlfile
            union select name from v$datafile
            union select name from v$tempfile
            union select member from v$logfile;
       b)检查原磁盘组上的所有文件是否均可删除
          SELECT gnum, filnum, concat('+'||gname,sys_connect_by_path(aname, '/')) filename
            FROM (SELECT g.name gname, a.parent_index pindex, a.name aname,
                    a.reference_index rindex, a.group_number gnum,a.file_number filnum
                  FROM v$asm_alias a,v$asm_diskgroup g
                  WHERE g.name='DATADG' and a.group_number = g.group_number)
            START WITH (mod(pindex, power(2, 24))) = 0
                CONNECT BY PRIOR rindex = pindex;
       c)删除磁盘组。保险起见，可以先尝试将原磁盘组dismount，运行一段时间再做删除
          drop diskgroup DATADG including contents;

文档参考：
   How to ADD/DROP ASM DISK in SINGLE COMMAND (文档 ID 1910831.1)
   Moving User datafiles between ASM Diskgroups using Incrementally Updated Backups (文档 ID 1472959.1)
   How to move ASM database files from one diskgroup to another ? (文档 ID 330103.1)
   How To Move The Database To Different Diskgroup (Change Diskgroup Redundancy) (文档 ID 438580.1)
   How to collect the full path name of the files in ASM diskgroups (文档 ID 888943.1)

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

转载于:http://blog.itpub.net/21687111/viewspace-2131502/

cmv71775

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
存储替换的数据库迁移

存储替换的数据库迁移需求：旧的存储设备使用年限太久，需要替换，数据库服务器不换，这就涉及将数据库迁移到新的存储上测试环境：　　AIX 7.1 ORACLE RAC 11.2.0.4 双节点测试方案：　　...
复制链接

扫一扫