【Migration】使用Backup as copy进行数据文件迁移

2013 年 5 月 11 日 by Leave a Comment



    • 有时可能会遇到磁盘空间满的情况，需要把部分表空间或者是数据文件迁移到新的位置。
    注意：使用此种方法时，需要启用归档。system和undo表空间无法使用此方法迁移，迁移它们时需要关闭数据库，拷贝数据文件，修改控制文件来完成。
    此种方法只有在switch时才需要offline数据文件，因此对数据文件的影响较小。
    1.首先查看表空间情况

    SQL> SELECT tablespace_name FROM dba_tablespaces;
    TABLESPACE_NAME
    ------------------------------
    SYSTEM
    UNDOTBS
    SYSAUX
    TEMPTS1
    EXAMPLE
    INDX
    TOOLS
    USERS
    OLTP

    2.查看准备迁移的数据文件

    SYS@PROD> SELECT file_id,file_name,tablespace_name FROM dba_data_files;

    FILE_ID FILE_NAME TABLESPACE_NAME
    ------- ------------------------------------------------- ---------------
    1 /u01/app/oracle/oradata/PROD/disk1/system01.dbf SYSTEM
    2 /u01/app/oracle/oradata/PROD/disk1/undotbs01.dbf UNDOTBS
    3 /u01/app/oracle/oradata/PROD/disk1/sysaux01.dbf SYSAUX
    4 /u01/app/oracle/oradata/PROD/disk1/example.dbf EXAMPLE
    5 /u01/app/oracle/oradata/PROD/disk1/indx.dbf INDX
    6 /u01/app/oracle/oradata/PROD/disk1/tools.dbf TOOLS
    7 /u01/app/oracle/oradata/PROD/disk1/users.dbf USERS
    8 /u01/app/oracle/oradata/PROD/disk1/oltp.dbf OLTP

    8 ROWS selected.

    3.使用RMAN工具进行迁移

    [oracle@secdb1 ~]$ rman target /

    Recovery Manager: Release 10.2.0.1.0 - Production ON Sat May 11 10:26:05 2013

    Copyright (c) 1982, 2005, Oracle. ALL rights reserved.

    connected TO target DATABASE: PROD (DBID=217811777)

    RMAN> backup AS copy datafile 8 format '/backup/oltp.dbf';

    Starting backup at 11-MAY-13
    USING target DATABASE control file instead OF recovery catalog
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: sid=320 devtype=DISK
    channel ORA_DISK_1: starting datafile copy
    INPUT datafile fno=00008 name=/u01/app/oracle/oradata/PROD/disk1/oltp.dbf
    output filename=/backup/oltp.dbf tag=TAG20130511T102731 recid=12 stamp=815135259
    channel ORA_DISK_1: datafile copy complete, elapsed TIME: 00:00:15
    Finished backup at 11-MAY-13

    Starting Control File AND SPFILE Autobackup at 11-MAY-13
    piece handle=/home/oracle/backup/control/ctl_c-217811777-20130511-00 comment=NONE
    Finished Control File AND SPFILE Autobackup at 11-MAY-13

    RMAN> SQL 'alter database datafile 8 offline';

    SQL statement: ALTER DATABASE datafile 8 offline

    RMAN> switch datafile 8 TO copy;

    datafile 8 switched TO datafile copy "/backup/oltp.dbf"

    RMAN> recover datafile 8;

    Starting recover at 11-MAY-13
    USING channel ORA_DISK_1

    starting media recovery
    media recovery complete, elapsed TIME: 00:00:02

    Finished recover at 11-MAY-13

    RMAN> SQL 'alter database datafile 8 online';

    SQL statement: ALTER DATABASE datafile 8 online

    RMAN>

    4.确定表空间是否已经ONLINE

    SYS@PROD> SELECT file_id,file_name,tablespace_name,online_status FROM dba_data_files;

    FILE_ID FILE_NAME TABLESPACE_NAME ONLINE_
    ------- ------------------------------------------------- ---------------- -----------
    1 /u01/app/oracle/oradata/PROD/disk1/system01.dbf SYSTEM SYSTEM
    2 /u01/app/oracle/oradata/PROD/disk1/undotbs01.dbf UNDOTBS ONLINE
    3 /u01/app/oracle/oradata/PROD/disk1/sysaux01.dbf SYSAUX ONLINE
    4 /u01/app/oracle/oradata/PROD/disk1/example.dbf EXAMPLE ONLINE
    5 /u01/app/oracle/oradata/PROD/disk1/indx.dbf INDX ONLINE
    6 /u01/app/oracle/oradata/PROD/disk1/tools.dbf TOOLS ONLINE
    7 /u01/app/oracle/oradata/PROD/disk1/users.dbf USERS ONLINE
    8 /backup/oltp.dbf OLTP ONLINE

    8 ROWS selected.
    Filed Under: Database, Oracle Tagged With:

    Speak Your Mind

    *

    京ICP备14059771号-2