12C新功能：在线移动数据文件 (Doc ID 1566797.1)

ZYLONG-SYS
关注文章
TinyMind专栏频道

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

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.在此版本中，数据文件可以在线(online)联机移动，即使对于系统表空间中的数据文件也是如此。
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. 能够在线移动数据文件意味着可以在用户访问系统时执行许多维护操作，例如将数据移动到另一个存储设备或将数据库移动到Oracle自动存储管理（Oracle ASM）。这确保了服务时间和服务水平协议（SLA）在正常运行时间方面的连续性。

SOLUTION

With Oracle 12C, you can now do the following operations while the datafile is online and being accessed:   使用Oracle 12C，您现在可以在数据文件处于联机状态且正在访问时执行以下操作：

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    更改在线数据文件的位置到Oracle ASM

The following is an example of how each operation is done:  下面是每个操作如何完成的示例
 
Renaming an Online Data File:  重命名在线数据文件
===============================
SQL> CREATE TABLESPACE test DATAFILE '/bugmnt17/em/celclnx14/SR3.5377673271/app/oracle/oradata/T12cB3/test.dbf' SIZE 50M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;

Tablespace created.

SQL> select file_name, status, online_status from dba_data_files;

FILE_NAME                                                                          STATUS    ONLINE_
------------------------------------------------------------------------------- --------- ----------
/bugmnt17/em/celclnx14/SR3.5377673271/app/oracle/oradata/T12cB3/system01.dbf    AVAILABLE SYSTEM
/bugmnt17/em/celclnx14/SR3.5377673271/app/oracle/oradata/T12cB3/sysaux01.dbf    AVAILABLE ONLINE
/bugmnt17/em/celclnx14/SR3.5377673271/app/oracle/oradata/T12cB3/undotbs01.dbf   AVAILABLE ONLINE
/bugmnt17/em/celclnx14/SR3.5377673271/app/oracle/oradata/T12cB3/users01.dbf     AVAILABLE ONLINE
/bugmnt17/em/celclnx14/SR3.5377673271/app/oracle/oradata/T12cB3/test.dbf        AVAILABLE ONLINE

SQL> ALTER DATABASE MOVE DATAFILE '/bugmnt17/em/celclnx14/SR3.5377673271/app/oracle/oradata/T12cB3/test.dbf'
TO '/bugmnt17/em/celclnx14/SR3.5377673271/app/oracle/oradata/T12cB3/test_renamed.dbf';

Database altered.

SQL> select file_name, status, online_status from dba_data_files where file_name='/bugmnt17/em/celclnx14/SR3.5377673271/app/oracle/oradata/T12cB3/test_renamed.dbf';

FILE_NAME                                                                           STATUS    ONLINE_
-------------------------------------------------------------------------------- --------- ----------
/bugmnt17/em/celclnx14/SR3.5377673271/app/oracle/oradata/T12cB3/test_renamed.dbf AVAILABLE ONLINE


2. Relocating an Online Data File:   更改在线数据文件的位置
===================================
SQL> ALTER DATABASE MOVE DATAFILE '/bugmnt17/em/celclnx14/SR3.5377673271/app/oracle/oradata/T12cB3/test_renamed.dbf'
TO '/bugmnt17/em/celclnx14/SR3.5377673271/app/oracle/oradata/test_renamed.dbf';
 
Database altered.

SQL> select file_name, status, online_status from dba_data_files where file_name='/bugmnt17/em/celclnx14/SR3.5377673271/app/oracle/oradata/T12cB3/test_renamed.dbf';

no rows selected

SQL> select file_name, status, online_status from dba_data_files;

FILE_NAME                                                                          STATUS    ONLINE_
------------------------------------------------------------------------------- --------- ----------
/bugmnt17/em/celclnx14/SR3.5377673271/app/oracle/oradata/T12cB3/system01.dbf    AVAILABLE SYSTEM
/bugmnt17/em/celclnx14/SR3.5377673271/app/oracle/oradata/T12cB3/sysaux01.dbf    AVAILABLE ONLINE
/bugmnt17/em/celclnx14/SR3.5377673271/app/oracle/oradata/T12cB3/undotbs01.dbf   AVAILABLE ONLINE
/bugmnt17/em/celclnx14/SR3.5377673271/app/oracle/oradata/T12cB3/users01.dbf     AVAILABLE ONLINE
/bugmnt17/em/celclnx14/SR3.5377673271/app/oracle/oradata/test_renamed.dbf       AVAILABLE ONLINE


3. Copying an Online Data File:   复制在线数据文件
=================================
SQL> ALTER DATABASE MOVE DATAFILE '/bugmnt17/em/celclnx14/SR3.5377673271/app/oracle/oradata/T12cB3/test_renamed.dbf'
TO '/bugmnt17/em/celclnx14/SR3.5377673271/app/oracle/oradata/test_renamed.dbf' keep;

Database altered.

SQL> select file_name, status, online_status from dba_data_files where file_name='/bugmnt17/em/celclnx14/SR3.5377673271/app/oracle/oradata/T12cB3/test_renamed.dbf';

no rows selected

SQL> select file_name, status, online_status from dba_data_files;

FILE_NAME                                                                          STATUS    ONLINE_
------------------------------------------------------------------------------- --------- ----------
/bugmnt17/em/celclnx14/SR3.5377673271/app/oracle/oradata/T12cB3/system01.dbf    AVAILABLE SYSTEM
/bugmnt17/em/celclnx14/SR3.5377673271/app/oracle/oradata/T12cB3/sysaux01.dbf    AVAILABLE ONLINE
/bugmnt17/em/celclnx14/SR3.5377673271/app/oracle/oradata/T12cB3/undotbs01.dbf   AVAILABLE ONLINE
/bugmnt17/em/celclnx14/SR3.5377673271/app/oracle/oradata/T12cB3/users01.dbf     AVAILABLE ONLINE
/bugmnt17/em/celclnx14/SR3.5377673271/app/oracle/oradata/test_renamed.dbf       AVAILABLE ONLINE

-- expect to see the entry:
-- /bugmnt17/em/celclnx14/SR3.5377673271/app/oracle/oradata/T12cB3/test_renamed.dbf
-- AVAILABLE ONLINE

-- However, when i try to move the datafile to the location /bugmnt17/em/celclnx14/SR3.5377673271/app/oracle/oradata/T12cB3 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 '/bugmnt17/em/celclnx14/SR3.5377673271/app/oracle/oradata/test_renamed.dbf'
TO '/bugmnt17/em/celclnx14/SR3.5377673271/app/oracle/oradata/T12cB3/test_renamed.dbf';   2
ALTER DATABASE MOVE DATAFILE '/bugmnt17/em/celclnx14/SR3.5377673271/app/oracle/oradata/test_renamed.dbf'
*
ERROR at line 1:
ORA-01119: error in creating database file
'/bugmnt17/em/celclnx14/SR3.5377673271/app/oracle/oradata/T12cB3/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: 更改在线数据文件的位置并覆盖现有文件
=====================================================================
SQL> ALTER DATABASE MOVE DATAFILE '/bugmnt17/em/celclnx14/SR3.5377673271/app/oracle/oradata/test_renamed.dbf'
TO '/bugmnt17/em/celclnx14/SR3.5377673271/app/oracle/oradata/T12cB3/test_renamed.dbf' REUSE;

Database altered.

SQL> select file_name, status, online_status from dba_data_files where file_name='/bugmnt17/em/celclnx14/SR3.5377673271/app/oracle/oradata/T12cB3/test_renamed.dbf';

FILE_NAME                                                                           STATUS    ONLINE_
-------------------------------------------------------------------------------- --------- ----------
/bugmnt17/em/celclnx14/SR3.5377673271/app/oracle/oradata/T12cB3/test_renamed.dbf AVAILABLE ONLINE


SQL> select file_name, status, online_status from dba_data_files;

FILE_NAME                                                                           STATUS    ONLINE_
-------------------------------------------------------------------------------- --------- ----------
/bugmnt17/em/celclnx14/SR3.5377673271/app/oracle/oradata/T12cB3/system01.dbf     AVAILABLE SYSTEM
/bugmnt17/em/celclnx14/SR3.5377673271/app/oracle/oradata/T12cB3/sysaux01.dbf     AVAILABLE ONLINE
/bugmnt17/em/celclnx14/SR3.5377673271/app/oracle/oradata/T12cB3/undotbs01.dbf    AVAILABLE ONLINE
/bugmnt17/em/celclnx14/SR3.5377673271/app/oracle/oradata/T12cB3/users01.dbf      AVAILABLE ONLINE
/bugmnt17/em/celclnx14/SR3.5377673271/app/oracle/oradata/T12cB3/test_renamed.dbf AVAILABLE ONLINE


5. Relocate an Online Data File to Oracle ASM:    更改在线数据文件的位置到Oracle ASM
================================================
This example moves the data file user1.dbf from the /u01/oracle/rbdb1/ directory to an Oracle ASM location.

ALTER DATABASE MOVE DATAFILE '/u01/oracle/rbdb1/user1.dbf' 
  TO '+dgroup_01/data/orcl/datafile/user1.dbf';

This example moves the data file from one Oracle ASM location to another Oracle ASM location.
ALTER DATABASE MOVE DATAFILE '+dgroup_01/data/orcl/datafile/user1.dbf' 
  TO '+dgroup_02/data/orcl/datafile/user1.dbf';
For more information about moving a datafile online, please refer to:http://docs.oracle.com/cd/E16655_01/server.121/e17636/dfiles.htm#ADMIN13837 优质内容筛选与推荐>>
1、Dom操作xml的常用方法
2、mongo操作及相关资料
3、C++ CodeBlocks安装配置
4、Transactional 事务
5、网络攻防 第二周


长按二维码向我转账

受苹果公司新规定影响，微信 iOS 版的赞赏功能被关闭，可通过二维码转账支持公众号。

    阅读
    好看
    已推荐到看一看
    你的朋友可以在“发现”-“看一看”看到你认为好看的文章。
    已取消，“好看”想法已同步删除
    已推荐到看一看 和朋友分享想法
    最多200字，当前共字 发送

    已发送

    朋友将在看一看看到

    确定
    分享你的想法...
    取消

    分享想法到看一看

    确定
    最多200字，当前共字

    发送中

    网络异常，请稍后重试

    微信扫一扫
    关注该公众号

    0 │ 收藏 │ 举报




    联系我们

    欢迎来到TinyMind。

    关于TinyMind的内容或商务合作、网站建议，举报不良信息等均可联系我们。

    TinyMind客服邮箱：support@tinymind.net.cn

    TinyMind.net.cn，专业AI技术社区