Chinaunix首页 | 论坛 | 博客

情系天下的ChinaUnix博客

首页　| 　博文目录　| 　关于我

qxtx2012

  • 博客访问： 25198
  • 博文数量： 18
  • 博客积分： 0
  • 博客等级： 民兵
  • 技术积分： 180
  • 用 户 组： 普通用户
  • 注册时间： 2015-11-02 14:00
文章分类

全部博文（18）

文章存档

2016年（4）

2015年（14）

我的朋友
最近访客
推荐博文
相关博文
在线移动数据文件

分类： Oracle

2015-11-08 17:46:59

基本语法：

点击(此处)折叠或打开

  1. ALTER DATABASE MOVE DATAFILE ( 'filename' | 'ASM_filename' | file_number )
  2.  [ TO ( 'filename' | 'ASM_filename' ) ]
  3.  [ REUSE ] [ KEEP ]
reuse移动数据文件不保留原始数据文件
keep移动数据文件保留原始数据文件
默认是reuse


1、查看当前数据文件位置

点击(此处)折叠或打开

  1. [oracle@test ~]$ sqlplus / as sysdba

  3. SQL*Plus: Release 12.1.0.2.0 Production on Sun Nov 8 03:45:56 2015

  5. Copyright (c) 1982, 2014, Oracle. All rights reserved.


  8. Connected to:
  9. Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
  10. With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

  12. SQL> show con_name

  14. CON_NAME
  15. ------------------------------
  16. CDB$ROOT
  17. SQL> col name for a45
  18. SQL> select file#,name from v$datafile order by file#;

  20.      FILE# NAME
  21. ---------- ---------------------------------------------
  22.      1 /oradata/cdb/system01.dbf
  23.      3 /oradata/cdb/sysaux01.dbf
  24.      4 /oradata/cdb/undotbs01.dbf
  25.      5 /oradata/cdb/pdbseed/system01.dbf
  26.      6 /oradata/cdb/users01.dbf
  27.      7 /oradata/cdb/pdbseed/sysaux01.dbf
  28.      8 /oradata/cdb/pdb1/system01.dbf
  29.      9 /oradata/cdb/pdb1/sysaux01.dbf
  30.     10 /oradata/cdb/pdb1/pdb1_users01.dbf
  31.     11 /oradata/cdb/pdbseed/users01.dbf
  32.     12 /oradata/cdb/pdb2/system01.dbf

  34.      FILE# NAME
  35. ---------- ---------------------------------------------
  36.     13 /oradata/cdb/pdb2/sysaux01.dbf
  37.     14 /oradata/cdb/pdb2/users01.dbf

  39. 13 rows selected.

  41. SQL> show pdbs

  43.     CON_ID CON_NAME             OPEN MODE RESTRICTED
  44. ---------- ------------------------------ ---------- ----------
  45.      2 PDB$SEED             READ ONLY NO
  46.      3 PDB1              READ WRITE NO
  47.      4 PDB2              READ WRITE NO
  48. SQL> col file_name for a45
  49. SQL> select file_id,file_name from dba_data_files;

  51.    FILE_ID FILE_NAME
  52. ---------- ---------------------------------------------
  53.      6 /oradata/cdb/users01.dbf
  54.      4 /oradata/cdb/undotbs01.dbf
  55.      1 /oradata/cdb/system01.dbf
  56.      3 /oradata/cdb/sysaux01.dbf
2、开始移动数据文件，使用了reuse参数

点击(此处)折叠或打开

  1. SQL> alter database move datafile 1 to '/oradata/cdbbak/system01.dbf' reuse;

  3. Database altered.

  5. SQL> select file_id,file_name from dba_data_files;

  7.    FILE_ID FILE_NAME
  8. ---------- ---------------------------------------------
  9.      6 /oradata/cdb/users01.dbf
  10.      4 /oradata/cdb/undotbs01.dbf
  11.      3 /oradata/cdb/sysaux01.dbf
  12.      1 /oradata/cdbbak/system01.dbf
查看存放数据目录是否有原始数据文件

点击(此处)折叠或打开

  1. SQL> host ls -al /oradata/cdb/system01.dbf
  2. ls: cannot access /oradata/cdb/system01.dbf: No such file or directory

  4. SQL> host ls -al /oradata/cdbbak/system01.dbf
  5. -rw-r----- 1 oracle oinstall 838868992 Nov 8 04:00 /oradata/cdbbak/system01.dbf
后台alert日志

点击(此处)折叠或打开

  1. alter database move datafile 1 to '/oradata/cdbbak/system01.dbf' reuse
  2. Sun Nov 08 03:50:02 2015
  3. Moving datafile /oradata/cdb/system01.dbf (1) to /oradata/cdbbak/system01.dbf
  4. Sun Nov 08 03:51:24 2015
  5. Move operation committed for file /oradata/cdbbak/system01.dbf
  6. Completed: alter database move datafile 1 to '/oradata/cdbbak/system01.dbf' reuse

下面是没有使用reuse参数，从实验结果看和使用reuse参数是一致的，即原始数据文件目录已被移到新目录中

点击(此处)折叠或打开

  1. SQL> alter database move datafile 3 to '/oradata/cdbbak/sysaux01.dbf';

  3. Database altered.


  6. SQL> select file_id,file_name from dba_data_files where file_id=3;

  8.    FILE_ID FILE_NAME
  9. ---------- ---------------------------------------------
  10.      3 /oradata/cdbbak/sysaux01.dbf

  12. SQL> host ls -al /oradata/cdb/sysaux01.dbf
  13. ls: cannot access /oradata/cdb/sysaux01.dbf: No such file or directory

  15. SQL> host ls -al /oradata/cdbbak/sysaux01.dbf
  16. -rw-r----- 1 oracle oinstall 744497152 Nov 8 04:05 /oradata/cdbbak/sysaux01.dbf

使用keep参数移动数据文件

点击(此处)折叠或打开

  1. SQL> select file_id,file_name from dba_data_files;

  3.    FILE_ID FILE_NAME
  4. ---------- ---------------------------------------------
  5.      6 /oradata/cdb/users01.dbf
  6.      4 /oradata/cdb/undotbs01.dbf
  7.      3 /oradata/cdbbak/sysaux01.dbf
  8.      1 /oradata/cdbbak/system01.dbf

  10. SQL> alter database move datafile 4 to '/oradata/cdbbak/undotbs01.dbf' keep;

  12. Database altered.

  14. SQL> select file_id,file_name from dba_data_files where file_id=4;

  16.    FILE_ID FILE_NAME
  17. ---------- ---------------------------------------------
  18.      4 /oradata/cdbbak/undotbs01.dbf

  20. SQL> host ls -al /oradata/cdb/undotbs01.dbf
  21. -rw-r----- 1 oracle oinstall 225452032 Nov 8 04:08 /oradata/cdb/undotbs01.dbf

  23. SQL> host ls -al /oradata/cdbbak/undotbs01.dbf
  24. -rw-r----- 1 oracle oinstall 225452032 Nov 8 04:08 /oradata/cdbbak/undotbs01.dbf
从上面的实验结果可以看出，使用了keep参数之后，则保留了原文件

下面演示使用OMF参数移动数据文件

点击(此处)折叠或打开

  1. SQL> alter system set db_create_file_dest='/oradata/cdbbak/' scope=both;

  3. System altered.

  5. SQL> select file_id,file_name from dba_data_files;

  7.    FILE_ID FILE_NAME
  8. ---------- ---------------------------------------------
  9.      6 /oradata/cdb/users01.dbf
  10.      3 /oradata/cdbbak/sysaux01.dbf
  11.      4 /oradata/cdbbak/undotbs01.dbf
  12.      1 /oradata/cdbbak/system01.dbf

  14. SQL> alter database move datafile '/oradata/cdb/users01.dbf';

  16. Database altered.

  18. SQL> select file_id,file_name from dba_data_files where file_id=6;

  20.    FILE_ID FILE_NAME
  21. ---------- ---------------------------------------------
  22.      6 /oradata/cdbbak/CDB/datafile/o1_mf_users_c3wp
  23.      vsnj_.dbf

这个实验是在数据文件使用了OMF，移动数据文件使用了keep参数，但会被忽略。

点击(此处)折叠或打开

  1. SQL> select file_id,file_name from dba_data_files;

  3.    FILE_ID FILE_NAME
  4. ---------- ---------------------------------------------
  5.      6 /oradata/cdbbak/CDB/datafile/o1_mf_users_c3wp
  6.      vsnj_.dbf

  8.      3 /oradata/cdbbak/sysaux01.dbf
  9.      4 /oradata/cdbbak/undotbs01.dbf
  10.      1 /oradata/cdbbak/system01.dbf

  12. SQL> alter database move datafile '/oradata/cdbbak/CDB/datafile/o1_mf_users_c3wpvsnj_.dbf' to '/oradata/cdb/users01.dbf' keep;

  14. Database altered.

  16. SQL> select file_id,file_name from dba_data_files;

  18.    FILE_ID FILE_NAME
  19. ---------- ---------------------------------------------
  20.      6 /oradata/cdb/users01.dbf
  21.      3 /oradata/cdbbak/sysaux01.dbf
  22.      4 /oradata/cdbbak/undotbs01.dbf
  23.      1 /oradata/cdbbak/system01.dbf

  25. SQL> host ls -al /oradata/cdbbak/o1_mf_users_c3wpvsnj_.dbf
  26. ls: cannot access /oradata/cdbbak/o1_mf_users_c3wpvsnj_.dbf: No such file or directory

  28. SQL> host ls -al /oradata/cdb/users01.dbf
  29. -rw-r----- 1 oracle oinstall 5251072 Nov 8 04:16 /oradata/cdb/users01.dbf

不能在CDG容器库环境中移动PDB，下面是实验结果

点击(此处)折叠或打开

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

  3.      FILE# NAME
  4. ---------- ---------------------------------------------
  5.      1 /oradata/cdbbak/system01.dbf
  6.      3 /oradata/cdbbak/sysaux01.dbf
  7.      4 /oradata/cdbbak/undotbs01.dbf
  8.      5 /oradata/cdb/pdbseed/system01.dbf
  9.      6 /oradata/cdb/users01.dbf
  10.      7 /oradata/cdb/pdbseed/sysaux01.dbf
  11.      8 /oradata/cdb/pdb1/system01.dbf
  12.      9 /oradata/cdb/pdb1/sysaux01.dbf
  13.     10 /oradata/cdb/pdb1/pdb1_users01.dbf
  14.     11 /oradata/cdb/pdbseed/users01.dbf
  15.     12 /oradata/cdb/pdb2/system01.dbf

  17.      FILE# NAME
  18. ---------- ---------------------------------------------
  19.     13 /oradata/cdb/pdb2/sysaux01.dbf
  20.     14 /oradata/cdb/pdb2/users01.dbf

  22. 13 rows selected.

  24. SQL> alter database move datafile 14 to '/oradata/pdbbak/users01.dbf' reuse;
  25. alter database move datafile 14 to '/oradata/pdbbak/users01.dbf' reuse
  26. *
  27. ERROR at line 1:
  28. ORA-01516: nonexistent log file, data file, or temporary file "14"

移动pdb库数据文件

点击(此处)折叠或打开

  1. SQL> show pdbs

  3.     CON_ID CON_NAME             OPEN MODE RESTRICTED
  4. ---------- ------------------------------ ---------- ----------
  5.      2 PDB$SEED             READ ONLY NO
  6.      3 PDB1              READ WRITE NO
  7.      4 PDB2              READ WRITE NO
  8. SQL> alter session set container=pdb2;

  10. Session altered.

  12. SQL> select file_id,file_name from dba_data_files;

  14.    FILE_ID FILE_NAME
  15. ---------- ---------------------------------------------
  16.     12 /oradata/cdb/pdb2/system01.dbf
  17.     13 /oradata/cdb/pdb2/sysaux01.dbf
  18.         14 /oradata/cdb/pdb2/users01.dbf

  20. SQL> alter database move datafile 14 to '/oradata/pdbbak/users01.dbf' reuse;

  22. Database altered.

  24. SQL> select file_id,file_name from dba_data_files;

  26.    FILE_ID FILE_NAME
  27. ---------- ---------------------------------------------
  28.     12 /oradata/cdb/pdb2/system01.dbf
  29.     13 /oradata/cdb/pdb2/sysaux01.dbf
  30.     14 /oradata/pdbbak/users01.dbf

  32. SQL> alter session set container=cdb$root;

  34. Session altered.

  36. SQL> show pdbs

  38.     CON_ID CON_NAME             OPEN MODE RESTRICTED
  39. ---------- ------------------------------ ---------- ----------
  40.      2 PDB$SEED             READ ONLY NO
  41.      3 PDB1              READ WRITE NO
  42.      4 PDB2              READ WRITE NO

在线移动数文件功能，并不适合移动tempfile

点击(此处)折叠或打开

  1. SQL> show pdbs

  3.     CON_ID CON_NAME             OPEN MODE RESTRICTED
  4. ---------- ------------------------------ ---------- ----------
  5.      2 PDB$SEED             READ ONLY NO
  6.      3 PDB1              READ WRITE NO
  7.      4 PDB2              READ WRITE NO
  8. SQL> select file_id,file_name from dba_temp_files;

  10.    FILE_ID FILE_NAME
  11. ---------- ---------------------------------------------
  12.      1 /oradata/cdb/temp01.dbf

  14. SQL> alter database move datafile '/oradata/cdb/temp01.dbf' to '/oradata/cdbbak/temp01.dbf';
  15. alter database move datafile '/oradata/cdb/temp01.dbf' to '/oradata/cdbbak/temp01.dbf'
  16. *
  17. ERROR at line 1:
  18. ORA-01516: nonexistent log file, data file, or temporary file
  19. "/oradata/cdb/temp01.dbf"







阅读(316) | 评论(0) | 转发(0) |
0

上一篇：修改pdb种子库为读写状态

下一篇：oracle 12.1.0.2安装PSU补丁

给主人留下些什么吧！~~
关于我们 | 关于IT168 | 联系方式 | 广告合作 | 法律声明 | 免费注册

Copyright 2001-2010 ChinaUnix.net All Rights Reserved 北京皓辰网域网络信息技术有限公司. 版权所有

感谢所有关心和支持过ChinaUnix的朋友们

16024965号-6