oracle 12c new feature: moving online datafiles(在线移动数据文件)
在12c以前如果想移动表空间的数据文件首先要offline.再OS mv命令移动离线的数据文件到新路径，Recover datafile[ or tablespace or database] 再Online.从12c 开始可以移动Online 的数据文件了，就是可以在线不停业务移动datafile了。
1)Move a data file into ORACLE ASM
2)Move datafiles from one type of storage to another
3)Make a tablespace read-only and move its datafiles to write-once storage
4)Renames or relocates an online datafile data file at operating system level
5)Enables users to access the file during move datafile operation
6)When you relocate a data file on the Windows platform, the original data file might be retained in the old location, even when the KEEP option is omitted.
…
take a example.
sys@ORA12C>select status,enabled,name,con_id from v$datafile; STATUS ENABLED NAME CON_ID ------- ---------- ------------------------------------------------------- -------------------- ONLINE READ WRITE /u01/app/oracle/oradata/ora12c/undotbs01.dbf 0 SYSTEM READ WRITE /u01/app/oracle/oradata/ora12c/pdb1/system01.dbf 3 ONLINE READ WRITE /u01/app/oracle/oradata/ora12c/pdb1/sysaux01.dbf 3 ONLINE READ WRITE /u01/app/oracle/oradata/ora12c/pdb1/pdb1_users01.dbf 3 sys@ORA12C>alter database move datafile '/u01/app/oracle/oradata/ora12c/pdb1/pdb1_users01.dbf' to '/u01/app/oracle/oradata/ora12c/pdb1/ora_pdb1_users01.dbf' 2 ; Database altered. sys@ORA12C>ho ls /u01/app/oracle/oradata/ora12c/pdb1/ ora_pdb1_users01.dbf sysaux01.dbf system01.dbf temp01.dbf sys@ORA12C>select status,enabled,name,con_id from v$datafile; STATUS ENABLED NAME CON_ID ------- ---------- ------------------------------------------------------- ------------------------- ONLINE READ WRITE /u01/app/oracle/oradata/ora12c/undotbs01.dbf 0 SYSTEM READ WRITE /u01/app/oracle/oradata/ora12c/pdb1/system01.dbf 3 ONLINE READ WRITE /u01/app/oracle/oradata/ora12c/pdb1/sysaux01.dbf 3 ONLINE READ WRITE /u01/app/oracle/oradata/ora12c/pdb1/ora_pdb1_users01.dbf 3 sys@ORA12C>alter database move datafile '/u01/app/oracle/oradata/ora12c/pdb1/ora_pdb1_users01.dbf' to '/u01/app/oracle/oradata/ora12c/pdb1/pdb1_users01.dbf' keep; Database altered. sys@ORA12C>ho ls /u01/app/oracle/oradata/ora12c/pdb1/ ora_pdb1_users01.dbf pdb1_users01.dbf sysaux01.dbf system01.dbf temp01.dbf c##anbob@ORA12C>conn / as sysdba Connected. sys@ORA12C>alter database move datafile '/u01/app/oracle/oradata/ora12c/pdb1/pdb1_users01.dbf' to '/u01/app/oracle/oradata/ora12c/pdb1/ora_pdb1_users01.dbf'; alter database move datafile '/u01/app/oracle/oradata/ora12c/pdb1/pdb1_users01.dbf' to '/u01/app/oracle/oradata/ora12c/pdb1/ora_pdb1_users01.dbf' * ERROR at line 1: ORA-01516: nonexistent log file, data file, or temporary file "10" sys@ORA12C>select status,enabled,name,con_id from v$datafile; STATUS ENABLED NAME CON_ID ------- ---------- -------------------------------------------------------------------------- SYSTEM READ WRITE /u01/app/oracle/oradata/ora12c/system01.dbf 1 ONLINE READ WRITE /u01/app/oracle/oradata/ora12c/sysaux01.dbf 1 ONLINE READ WRITE /u01/app/oracle/oradata/ora12c/undotbs01.dbf 1 SYSTEM READ WRITE /u01/app/oracle/oradata/ora12c/pdbseed/system01.dbf 2 ONLINE READ WRITE /u01/app/oracle/oradata/ora12c/users01.dbf 1 ONLINE READ WRITE /u01/app/oracle/oradata/ora12c/pdbseed/sysaux01.dbf 2 SYSTEM READ WRITE /u01/app/oracle/oradata/ora12c/pdb1/system01.dbf 3 ONLINE READ WRITE /u01/app/oracle/oradata/ora12c/pdb1/sysaux01.dbf 3 ONLINE READ WRITE /u01/app/oracle/oradata/ora12c/pdb1/pdb1_users01.dbf 3 SYSTEM READ WRITE /u01/app/oracle/oradata/ora12c/pdb2/system01.dbf 4 ONLINE READ WRITE /u01/app/oracle/oradata/ora12c/pdb2/sysaux01.dbf 4 SYSTEM READ WRITE /u01/app/oracle/oradata/ora12c/drmdb/system01.dbf 5 ONLINE READ WRITE /u01/app/oracle/oradata/ora12c/drmdb/sysaux01.dbf 5 13 rows selected. sys@ORA12C>alter session set container=pdb1; Session altered. sys@ORA12C>select status,enabled,name,con_id from v$datafile; STATUS ENABLED NAME CON_ID ------- ---------- -------------------------------------------------------------------------- ONLINE READ WRITE /u01/app/oracle/oradata/ora12c/undotbs01.dbf 0 SYSTEM READ WRITE /u01/app/oracle/oradata/ora12c/pdb1/system01.dbf 3 ONLINE READ WRITE /u01/app/oracle/oradata/ora12c/pdb1/sysaux01.dbf 3 ONLINE READ WRITE /u01/app/oracle/oradata/ora12c/pdb1/pdb1_users01.dbf 3 sys@ORA12C>alter database move datafile '/u01/app/oracle/oradata/ora12c/pdb1/pdb1_users01.dbf' to '/u01/app/oracle/oradata/ora12c/pdb1/ora_pdb1_users01.dbf'; alter database move datafile '/u01/app/oracle/oradata/ora12c/pdb1/pdb1_users01.dbf' to '/u01/app/oracle/oradata/ora12c/pdb1/ora_pdb1_users01.dbf' * ERROR at line 1: ORA-01119: error in creating database file '/u01/app/oracle/oradata/ora12c/pdb1/ora_pdb1_users01.dbf' ORA-27038: created file already exists Additional information: 1 sys@ORA12C>alter database move datafile '/u01/app/oracle/oradata/ora12c/pdb1/pdb1_users01.dbf' to '/u01/app/oracle/oradata/ora12c/pdb1/ora_pdb1_users01.dbf' reuse; Database altered. sys@ORA12C>select * from v$version; BANNER CON_ID -------------------------------------------------------------------------------- -------------------- Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0 PL/SQL Release 12.1.0.1.0 - Production 0 CORE 12.1.0.1.0 Production 0 TNS for Linux: Version 12.1.0.1.0 - Production 0 NLSRTL Version 12.1.0.1.0 - Production 0
在oracle dataguard 的standby端也是这样的，对于使用ASM 的数据库，如果增加数据文件时，忘记了带’+’号, 增加数据文件到本地文件系统，而导致另一个实例无法访问，出现ora-1157 访问数据文件失败的错误， 可以考虑使用online move datafile 解决。
在2021年APACOUC活动中，我有一个分享，如datafile online move，使用调用了double write during mvoing双写机制，GDB 可以break on 在 kcffo_mv_copy_loop和kcffo_mv_write_hdr 函数。
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。