ORACLE 12C move datafile测试

发表于 2012 年 12 月 13 日由惜分飞

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：ORACLE 12C move datafile测试

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

通过一段时间的宣传,很多人都知道了ORACLE 12C可以实现在线move数据文件,使得我们从以前先offline相关文件任何系统级别mv,然后在rename file方便了很多。该功能的强大之处在于:
1.可以在库open的情况下move system表空间文件
2.对于其他表空间的数据文件move不用offline(意味着不用停业务)
3.大大简化了以前的操作步骤,很多初级dba对于原来的操作方法不理解,经常导致datafile最终异常
move datafile语法

move datafile之前操作

[oracle@xifenfei ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.0.2 Beta on Thu Dec 13 05:57:18 2012

Copyright (c) 1982, 2012, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.0.2 - 64bit     
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select *from v$version;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.0.2 - 64bit                         0
PL/SQL Release 12.1.0.0.2                                                                 0
CORE    12.1.0.0.2                                                                        0
TNS for Linux: Version 12.1.0.0.2                                                         0
NLSRTL Version 12.1.0.0.2                                                                 0

SQL> alter session set container = ff;

Session altered.

SQL> show con_name;

CON_NAME
------------------------------
FF

SQL> col name for a65
SQL> set lines 134
SQL> select file#,name from v$datafile;

     FILE# NAME
---------- -----------------------------------------------------------------
         5 /u01/app/oracle/oradata/xifenfei/undotbs01.dbf
        16 /u01/app/oracle/oradata/xifenfei/xffsystem01.dbf
        17 /u01/app/oracle/oradata/xifenfei/xffsysaux01.dbf
        18 /u01/app/oracle/oradata/xifenfei/xffSAMPLE_SCHEMA_users01.dbf
        19 /u01/app/oracle/oradata/xifenfei/xffexample01.dbf

SQL> !ls -l /u01/app/oracle/oradata/xifenfei/xff*
-rw-r----- 1 oracle oinstall 365699072 Dec 13 05:55 /u01/app/oracle/oradata/xifenfei/xffexample01.dbf
-rw-r----- 1 oracle oinstall   5251072 Dec 13 05:55 /u01/app/oracle/oradata/xifenfei/xffSAMPLE_SCHEMA_users01.dbf
-rw-r----- 1 oracle oinstall 597696512 Dec 13 05:55 /u01/app/oracle/oradata/xifenfei/xffsysaux01.dbf
-rw-r----- 1 oracle oinstall 283123712 Dec 13 05:55 /u01/app/oracle/oradata/xifenfei/xffsystem01.dbf
-rw-r----- 1 oracle oinstall  20979712 Dec 12 21:28 /u01/app/oracle/oradata/xifenfei/xfftemp01.dbf

/u01/app/oracle/oradata/xifenfei/xff:
total 0

执行move datafile操作

SQL> select sid from v$mystat where rownum=1;

       SID
----------
       259

SQL> alter database move datafile 16 to '/u01/app/oracle/oradata/xifenfei/xff/system01.dbf';

Database altered.

--new session 
SQL> select con_id,sid,event from v$session where sid=259;

    CON_ID        SID EVENT
---------- ---------- -----------------------------------------------
         1        259 db file single write
SQL> /

    CON_ID        SID EVENT
---------- ---------- ------------------------------------------------
         1        259 db file sequential read
SQL> /

    CON_ID        SID EVENT
---------- ---------- -------------------------------------------------
         1        259 db file single write
--通过新会话监控等待事件发现,move datafile的主要等待是
--db file sequential read和db file single write

--继续执行move datafile
SQL> alter database move datafile 
  2  '/u01/app/oracle/oradata/xifenfei/xffsysaux01.dbf' to
  3  '/u01/app/oracle/oradata/xifenfei/xff/sysaux01.dbf';

Database altered.

SQL> alter database move datafile 18 to 
  2  '/u01/app/oracle/oradata/xifenfei/xff/users01.dbf';

Database altered.

SQL> alter database move datafile 19 to 
  2  '/u01/app/oracle/oradata/xifenfei/xff/example01.dbf';

Database altered.

SQL>  select file#,name from v$datafile;

     FILE# NAME
---------- -----------------------------------------------------------------
         5 /u01/app/oracle/oradata/xifenfei/undotbs01.dbf
        16 /u01/app/oracle/oradata/xifenfei/xff/system01.dbf
        17 /u01/app/oracle/oradata/xifenfei/xff/sysaux01.dbf
        18 /u01/app/oracle/oradata/xifenfei/xff/users01.dbf
        19 /u01/app/oracle/oradata/xifenfei/xff/example01.dbf

此条目发表在 ORACLE 12C 分类目录，贴了 ORACLE 12C 标签。将固定链接加入收藏夹。

《ORACLE 12C move datafile测试》有 1 条评论

惜分飞说：

2012 年 12 月 13 日23:04

move datafile 也支持cdb中数据文件

SQL> select file#,name from v$datafile where file#=1;

     FILE# NAME
---------- -------------------------------------------------------
         1 /u01/app/oracle/oradata/lunar/system01.dbf

1 rows selected.

SQL> alter database move datafile 1 to '/tmp/system01.dbf';

Database altered.

SQL> select file#,name from v$datafile where file#=1;

     FILE# NAME
---------- -------------------------------------------------------
         1 /tmp/system01.dbf

1 rows selected.

17813235971