学习笔记:Oracle 12C 在线进行move datafile数据文件案例

时间:2016-06-15 16:04 来源:未知作者:代某人点击: 次

天萃荷净 数据库版本为Oracle 12C，生产环境需要进行移动数据文件，12C新特性不关闭数据库在线迁移move datafile数据文件

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

1.Oracle12C新特性move datafile操作

[oracle@oracleplus ~]$ 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/oracleplus/undotbs01.dbf
        16 /u01/app/oracle/oradata/oracleplus/xffsystem01.dbf
        17 /u01/app/oracle/oradata/oracleplus/xffsysaux01.dbf
        18 /u01/app/oracle/oradata/oracleplus/xffSAMPLE_SCHEMA_users01.dbf
        19 /u01/app/oracle/oradata/oracleplus/xffexample01.dbf

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

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

2.执行move datafile操作

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

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

SQL> alter database move datafile 16 to '/u01/app/oracle/oradata/oracleplus/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/oracleplus/xffsysaux01.dbf' to
  3  '/u01/app/oracle/oradata/oracleplus/xff/sysaux01.dbf';

Database altered.

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

Database altered.

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

Database altered.

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

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

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之学习笔记:Oracle 12C 在线进行move datafile数据文件案例

本文由大师惜分飞原创分享，网址：http://www.oracleplus.net/arch/396.html