IT博客汇
  • 首页
  • 精华
  • 技术
  • 设计
  • 资讯
  • 扯淡
  • 权利声明
    • 登录 注册

    Oracle12C版本开始支持的在线移动数据文件功能测试

    Guang Cai Li发表于 2023-05-28 14:40:24
    love 0

    在ORACLE 10G/11G版本时，如果要将数据库的数据文件，移动到不同的磁盘目录（即修改磁盘路径），如果数据库在OPEN状态需要将数据文件OFFLINE，或者在数据库MOUNT状态下进行修补。

    在12C及以上版本时，可以在线移动一个正在被访问的数据文件；即是system表空间中的数据文件也可以。
    可以在线移动数据文件，表示当用户正在访问系统的时候，很多维护操作可以在线执行；例如，将数据文件移动到其他存储设备或者移动到Oracle ASM。这确保了服务的连续性，并且满足正常运行时的服务水平协议（SLA）。

    在 12C，当数据文件处于在线状态并且正在被访问的时候，可以执行下面的操作：

1. 重命名在线数据库文件
2. 迁移在线数据库文件
3. 拷贝在线数据文件
4. 迁移在线数据文件并且覆盖现有文件
5. 迁移在线数据文件到 oracle ASM

    如下为对这些功能的测试案例：

    1.在线移动数据文件-文件系统-ASM

     

     

    –可用于数据文件重命令、从一个目录移动到另一目录(可能涉及不同的磁盘或分区、LV)

    –直接使用MOVE DATAFILE参数相当于是移动或者重命令，不会保留原数据文件

    –keep关键字，可以实现拷贝数据文件到新位置，原文件保留

    移动PDB中数据文件需要在指定 的PDB中进行操作：

     

    show con_name

     

    CON_NAME

    ——————————

    CDBPDB2

    C##Luda@cdbpdb2>select name from v$dbfile;

     

    NAME

    ————————————————————————————————————————————————————————————

    /u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/4FDB7936712F5A06E053D339A8C06FAC/datafile/o1_mf_sysaux_dkx3hppr_.dbf

    /u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/4FDB7936712F5A06E053D339A8C06FAC/datafile/o1_mf_undotbs1_dkx3hppt_.dbf

    /u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/4FDB7936712F5A06E053D339A8C06FAC/datafile/o1_mf_pdb2_dkx9pq4r_.dbf

    /u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/system01.dbf

    C##Luda@cdbpdb2>alter database  MOVE DATAFILE ‘/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/4FDB7936712F5A06E053D339A8C06FAC/datafile/o1_mf_sysaux_dkx3hppr_.dbf’ to ‘/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/sysaux01.dbf’;

     

    Database altered.

     

    C##Luda@cdbpdb2>!ls /u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/4FDB7936712F5A06E053D339A8C06FAC/datafile/o1_mf_sysaux_dkx3hppr_.dbf

    ls: cannot access /u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/4FDB7936712F5A06E053D339A8C06FAC/datafile/o1_mf_sysaux_dkx3hppr_.dbf: No such file or directory

     

    C##Luda@cdbpdb2>!ls /u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/sysaux01.dbf

    /u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/sysaux01.dbf

    C##Luda@cdbpdb2>select file_name, status, online_status from dba_data_files;

     

    FILE_NAME                                                                        STATUS    ONLINE_

    ——————————————————————————– ——— ——-

    /u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/sysaux01.dbf                           AVAILABLE ONLINE

    /u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/4FDB7936712F5A06E053D339A8C06FA AVAILABLE ONLINE

    C/datafile/o1_mf_undotbs1_dkx3hppt_.dbf

     

    /u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/4FDB7936712F5A06E053D339A8C06FA AVAILABLE ONLINE

    C/datafile/o1_mf_pdb2_dkx9pq4r_.dbf

     

    /u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/system01.dbf                           AVAILABLE SYSTEM

    观察此时的ALERT日志：

    2017-05-23T14:43:17.122383+08:00

    CDBPDB2(4):alter database  MOVE DATAFILE ‘/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/4FDB7936712F5A06E053D339A8C06FAC/datafile/o1_mf_sysaux_dkx3hppr_.dbf’ to ‘/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/sysaux01.dbf’

    2017-05-23T14:43:17.146362+08:00

    Moving datafile /u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/4FDB7936712F5A06E053D339A8C06FAC/datafile/o1_mf_sysaux_dkx3hppr_.dbf (22) to /u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/sysaux01.dbf

    2017-05-23T14:43:55.724994+08:00

    Move operation committed for file /u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/sysaux01.dbf

    2017-05-23T14:43:58.166391+08:00

    CDBPDB2(4):Completed: alter database  MOVE DATAFILE ‘/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/4FDB7936712F5A06E053D339A8C06FAC/datafile/o1_mf_sysaux_dkx3hppr_.dbf’ to ‘/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/sysaux01.dbf’

    使用KEEP关键字的测试：–OMF管理的不会保留原文件，

    C##Luda@cdbpdb2>alter database  MOVE DATAFILE ‘/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/4FDB7936712F5A06E053D339A8C06FAC/datafile/o1_mf_pdb2_dkx9pq4r_.dbf’ to ‘/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/pdb201.dbf’ keep;

     

    Database altered.

     

    C##Luda@cdbpdb2>select file_name, status, online_status from dba_data_files;

     

    FILE_NAME                                                                        STATUS    ONLINE_

    ——————————————————————————– ——— ——-

    /u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/sysaux01.dbf                           AVAILABLE ONLINE

    /u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/pdb201.dbf                             AVAILABLE ONLINE

    /u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/4FDB7936712F5A06E053D339A8C06FA AVAILABLE ONLINE

    C/datafile/o1_mf_undotbs1_dkx3hppt_.dbf

     

    /u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/system01.dbf                           AVAILABLE SYSTEM

     

    C##Luda@cdbpdb2>!ls /u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/4FDB7936712F5A06E053D339A8C06FAC/datafile/o1_mf_pdb2_dkx9pq4r_.dbf

    ls: cannot access /u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/4FDB7936712F5A06E053D339A8C06FAC/datafile/o1_mf_pdb2_dkx9pq4r_.dbf: No such file or directory

     

    C##Luda@cdbpdb2>!ls /u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/pdb201.dbf

    /u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/pdb201.dbf

     

     

    C##Luda@cdbpdb2>alter database  MOVE DATAFILE ‘/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/pdb201.dbf’ to ‘/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/pdb201.dbf’ keep;

     

    Database altered.

     

     

    C##Luda@cdbpdb2>!ls /u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/pdb201.dbf

    /u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/pdb201.dbf

     

    C##Luda@cdbpdb2>!ls /u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/pdb201.dbf

    /u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/pdb201.dbf

     

     

    使用REUSE关键字覆盖

    C##Luda@cdbpdb2>!ls /u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/pdb201.dbf

    /u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/pdb201.dbf

     

    C##Luda@cdbpdb2>!ls /u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/pdb201.dbf

    /u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/pdb201.dbf

     

    C##Luda@cdbpdb2>alter database  MOVE DATAFILE ‘/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/pdb201.dbf’ to ‘/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/pdb201.dbf’ keep;

    alter database  MOVE DATAFILE ‘/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/pdb201.dbf’ to ‘/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/pdb201.dbf’ keep

    *

    ERROR at line 1:

    ORA-01119: error in creating database file ‘/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/pdb201.dbf’

    ORA-27038: created file already exists

    Additional information: 1

     

     

    C##Luda@cdbpdb2>alter database  MOVE DATAFILE ‘/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/pdb201.dbf’ to ‘/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/pdb201.dbf’ reuse;

    C##Luda@cdbpdb2>!ls /u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/pdb201.dbf

    ls: cannot access /u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/pdb201.dbf: No such file or directory

     

    C##Luda@cdbpdb2>!ls /u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/pdb201.dbf

    /u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/pdb201.dbf

     



沪ICP备19023445号-2号
友情链接