12C New Feature : Move a Datafile Online (Doc ID 1566797.1)

In this Document
Applies to:
Oracle Database - Enterprise Edition - Version 12.1.0.1 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.
Goal
In this release, a data file can now be moved online while it is open
and being accessed, even for data files in system tablespace.
Being
able to move a data file online means that many maintenance operations,
such as moving data to another storage device or moving databases into
Oracle Automatic Storage Management (Oracle ASM), can be performed while
users are accessing the system. This ensures that continuity of service
and service-level agreements (SLA) on uptime can be met.
Solution
With Oracle 12C, you can now do the following operations while the datafile is online and being accessed: 1. Renaming an Online Data File 2. Relocating an Online Data File 3. Copying an Online Data File 4. Relocating an Online Data File and Overwriting an Existing File 5. Relocating an Online Data File to Oracle ASM The following is an example of how each operation is done: Renaming an Online Data File: ===============================
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO; 2 3
Tablespace created.
SQL> select file_name, status, online_status from dba_data_files;
FILE_NAME
----------------------------------------------------------------------------------------------------
STATUS ONLINE_
--------- -------
<PATH>/<SID>/system01.dbf
AVAILABLE SYSTEM
<PATH>/<SID>/sysaux01.dbf
AVAILABLE ONLINE
<PATH>/<SID>/undotbs01.dbf
AVAILABLE ONLINE
<PATH>/<SID>/users01.dbf
AVAILABLE ONLINE
<PATH>/<SID>/test.dbf
AVAILABLE ONLINE
SQL> ALTER DATABASE MOVE DATAFILE '<PATH>/<SID>/test.dbf'
TO '<PATH>/<SID>/test_renamed.dbf';
Database altered.
SQL> select file_name, status, online_status from dba_data_files where file_name='<PATH>/<SID>/test_renamed.dbf';
FILE_NAME
----------------------------------------------------------------------------------------------------
STATUS ONLINE_
--------- -------
<PATH>/<SID>/test_renamed.dbf
AVAILABLE ONLINE
2. Relocating an Online Data File: ===================================
TO '<PATH>/test_renamed.dbf';
Database altered.
SQL> select file_name, status, online_status from dba_data_files where file_name='<PATH>/<SID>/test_renamed.dbf';
no rows selected
SQL> select file_name, status, online_status from dba_data_files;
FILE_NAME
----------------------------------------------------------------------------------------------------
STATUS ONLINE_
--------- -------
<PATH>/<SID>/system01.dbf
AVAILABLE SYSTEM
<PATH>/<SID>/sysaux01.dbf
AVAILABLE ONLINE
<PATH>/<SID>/undotbs01.dbf
AVAILABLE ONLINE
<PATH>/<SID>/users01.dbf
AVAILABLE ONLINE
<PATH>/test_renamed.dbf
AVAILABLE ONLINE
3. Copying an Online Data File: =================================
TO '<PATH>/test_renamed.dbf' keep;
Database altered.
SQL> select file_name, status, online_status from dba_data_files where file_name='<PATH>/<SID>/test_renamed.dbf';
no rows selected
SQL> select file_name, status, online_status from dba_data_files;
FILE_NAME
----------------------------------------------------------------------------------------------------
STATUS ONLINE_
--------- -------
<PATH>/<SID>/system01.dbf
AVAILABLE SYSTEM
<PATH>/<SID>/sysaux01.dbf
AVAILABLE ONLINE
<PATH>/<SID>/undotbs01.dbf
AVAILABLE ONLINE
<PATH>/<SID>/users01.dbf
AVAILABLE ONLINE
<PATH>/test_renamed.dbf
AVAILABLE ONLINE
-- expect to see the entry:
-- <PATH>/<SID>/test_renamed.dbf
-- AVAILABLE ONLINE
-- However, when i try to move the datafile to the location <PATH>/<SID> i get the following error
-- which verifies that the datafile was indeed copied and kept available in the old location:
SQL> ALTER DATABASE MOVE DATAFILE '<PATH>/test_renamed.dbf'
TO '<PATH>/<SID>/test_renamed.dbf'; 2
ALTER DATABASE MOVE DATAFILE '<PATH>/test_renamed.dbf'
*
ERROR at line 1:
ORA-01119: error in creating database file
'<PATH>/<SID>/test_renamed.dbf'
ORA-27038: created file already exists
Additional information: 1
4. Example of relocating an Online Data File and Overwriting an Existing File: =====================================================================
TO '<PATH>/<SID>/test_renamed.dbf' REUSE;
Database altered.
SQL> select file_name, status, online_status from dba_data_files where file_name='<PATH>/<SID>/test_renamed.dbf';
FILE_NAME
----------------------------------------------------------------------------------------------------
STATUS ONLINE_
--------- -------
<PATH>/<SID>/test_renamed.dbf
AVAILABLE ONLINE
SQL> select file_name, status, online_status from dba_data_files;
FILE_NAME
----------------------------------------------------------------------------------------------------
STATUS ONLINE_
--------- -------
<PATH>/<SID>/system01.dbf
AVAILABLE SYSTEM
<PATH>/<SID>/sysaux01.dbf
AVAILABLE ONLINE
<PATH>/<SID>/undotbs01.dbf
AVAILABLE ONLINE
<PATH>/<SID>/users01.dbf
AVAILABLE ONLINE
<PATH>/<SID>/test_renamed.dbf
AVAILABLE ONLINE
5. Relocate an Online Data File to Oracle ASM: ================================================
This example moves the data file user1.dbf
from <PATH> directory to an Oracle ASM location.
ALTER DATABASE MOVE DATAFILE '<PATH>/user1.dbf' TO '+dg_group1/user1.dbf'; This example moves the data file from one Oracle ASM location to another Oracle ASM location.
ALTER DATABASE MOVE DATAFILE +dg_group1/user1.dbf' TO '+dg_group2/user1.dbf';
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 没有源码,如何修改代码逻辑?
· 一个奇形怪状的面试题:Bean中的CHM要不要加volatile?
· [.NET]调用本地 Deepseek 模型
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· .NET Core 托管堆内存泄露/CPU异常的常见思路
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 没有源码,如何修改代码逻辑?
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战