Online Data Files move

简介: online data files move,move online,

12C新特性: online data files move

参考手册:

Oracle Database Online Documentation 12c Release 1 (12.1) / Database Administration

14.5.1 Renaming and Relocating Online Data Files
You can use the ALTER DATABASE MOVE DATAFILE SQL statement to rename or relocate online data files. This statement enables you to rename or relocate a data file while the database is open and users are accessing the data file.

When you rename or relocate online data files, the pointers to the data files, as recorded in the database control file, are changed. The files are also physically renamed or relocated at the operating system level.

You might rename or relocate online data files because you want to allow users to access the data files when you perform one of the following tasks:

Move the data files from one type of storage to another

Move data files that are accessed infrequently to lower cost storage

Make a tablespace read-only and move its data files to write-once storage

Move a database into Oracle Automatic Storage Management (Oracle ASM)

移动本地文件到asm

SQL> alter database move datafile 16 to '+data/cdb/datafile/system02.dbf';

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

     FILE# NAME
---------- --------------------------------------------------------------------------------
     1 +DATA/CDB/DATAFILE/system.290.985011811
     3 +DATA/CDB/DATAFILE/sysaux.289.985011873
     4 +DATA/CDB/DATAFILE/undotbs1.293.985011907
     5 +DATA/CDB/64A52F53A7693286E053CDA9E80AED76/DATAFILE/system.300.985012289
     6 +DATA/CDB/64A52F53A7693286E053CDA9E80AED76/DATAFILE/sysaux.299.985012289
     7 +DATA/CDB/DATAFILE/users.294.985011909
     8 +DATA/CDB/64A52F53A7693286E053CDA9E80AED76/DATAFILE/undotbs1.301.985012289
     9 +DATA/CDB/DATAFILE/undotbs2.303.985012575
    10 +DATA/CDB/7429AF9E7B2A4EF9E053CB00A8C08F1D/DATAFILE/system.308.985012979
    11 +DATA/CDB/7429AF9E7B2A4EF9E053CB00A8C08F1D/DATAFILE/sysaux.309.985012979
    12 +DATA/CDB/7429AF9E7B2A4EF9E053CB00A8C08F1D/DATAFILE/undotbs1.307.985012979
    13 +DATA/CDB/7429AF9E7B2A4EF9E053CB00A8C08F1D/DATAFILE/undo_2.311.985013015
    14 +DATA/CDB/7429AF9E7B2A4EF9E053CB00A8C08F1D/DATAFILE/users.312.985013017
    15 +DATA/CDB/DATAFILE/user02.dbf
    16 /soft/system02.dbf

15 rows selected.

SQL> alter  database move datafile 16 to '+data/cdb/datafile/system02.dbf';

Database altered.

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

     FILE# NAME
---------- --------------------------------------------------------------------------------
     1 +DATA/CDB/DATAFILE/system.290.985011811
     3 +DATA/CDB/DATAFILE/sysaux.289.985011873
     4 +DATA/CDB/DATAFILE/undotbs1.293.985011907
     5 +DATA/CDB/64A52F53A7693286E053CDA9E80AED76/DATAFILE/system.300.985012289
     6 +DATA/CDB/64A52F53A7693286E053CDA9E80AED76/DATAFILE/sysaux.299.985012289
     7 +DATA/CDB/DATAFILE/users.294.985011909
     8 +DATA/CDB/64A52F53A7693286E053CDA9E80AED76/DATAFILE/undotbs1.301.985012289
     9 +DATA/CDB/DATAFILE/undotbs2.303.985012575
    10 +DATA/CDB/7429AF9E7B2A4EF9E053CB00A8C08F1D/DATAFILE/system.308.985012979
    11 +DATA/CDB/7429AF9E7B2A4EF9E053CB00A8C08F1D/DATAFILE/sysaux.309.985012979
    12 +DATA/CDB/7429AF9E7B2A4EF9E053CB00A8C08F1D/DATAFILE/undotbs1.307.985012979
    13 +DATA/CDB/7429AF9E7B2A4EF9E053CB00A8C08F1D/DATAFILE/undo_2.311.985013015
    14 +DATA/CDB/7429AF9E7B2A4EF9E053CB00A8C08F1D/DATAFILE/users.312.985013017
    15 +DATA/CDB/DATAFILE/user02.dbf
    16 +DATA/cdb/datafile/system02.dbf

15 rows selected.

移动部分文件到新目录

move 完成之后,源目录文件自动清除。

SQL> alter database move datafile 1 to '/soft/system01.dbf';
SQL> alter database move datafile 3 to '/soft/sysaux01.dbf';

[oracle@host01 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Sep 19 22:36:24 2018

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


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

SQL> col name format a50;
set linesize 800;
set pagesize 900; 
select file#,name from v$datafile;SQL> SQL> SQL> 

     FILE# NAME
---------- --------------------------------------------------
     1 /u01/app/oracle/oradata/PRODCDB/system01.dbf
     2 /u01/app/oracle/oradata/PRODCDB/pdbseed/system01.dbf
     3 /u01/app/oracle/oradata/PRODCDB/sysaux01.dbf
     4 /u01/app/oracle/oradata/PRODCDB/pdbseed/sysaux01. bf
     5 /u01/app/oracle/oradata/PRODCDB/undotbs01.dbf
     6 /u01/app/oracle/oradata/PRODCDB/users01.dbf
     7 /u01/app/oracle/oradata/PRODCDB/PDBPROD1/system01.dbf
     8 /u01/app/oracle/oradata/PRODCDB/PDBPROD1/sysaux01.dbf
     9 /u01/app/oracle/oradata/PRODCDB/PDBPROD1/PDBPROD1_users01.dbf
    10 /u01/app/oracle/oradata/PRODCDB/PDBPROD2/system01. dbf
    11 /u01/app/oracle/oradata/PRODCDB/PDBPROD2/sysaux01.dbf
    12 /u01/app/oracle/oradata/PRODCDB/PDBPROD2/PDBPROD2_users01.dbf
    13 /u01/app/oracle/oradata/PRODCDB/PDBPROD1/example01.dbf
       
13 rows selected.

SQL> alter database move datafile 1 to '/soft/system01.dbf';
                                           
Database altered.

SQL> alter database move datafile 3 to '/soft/sysaux01.dbf';

Database altered.

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

     FILE# NAME
---------- --------------------------------------------------------------------------------
     1 /soft/system01.dbf
     2 /u01/app/oracle/oradata/PRODCDB/pdbseed/system01.dbf
     3 /soft/sysaux01.dbf
     4 /u01/app/oracle/oradata/PRODCDB/pdbseed/sysaux01.dbf
     5 /u01/app/oracle/oradata/PRODCDB/undotbs01.dbf
     6 /u01/app/oracle/oradata/PRODCDB/users01.dbf
     7 /u01/app/oracle/oradata/PRODCDB/PDBPROD1/system01.dbf
     8 /u01/app/oracle/oradata/PRODCDB/PDBPROD1/sysaux01.dbf
     9 /u01/app/oracle/oradata/PRODCDB/PDBPROD1/PDBPROD1_users01.dbf
    10 /u01/app/oracle/oradata/PRODCDB/PDBPROD2/system01.dbf
    11 /u01/app/oracle/oradata/PRODCDB/PDBPROD2/sysaux01.dbf
    12 /u01/app/oracle/oradata/PRODCDB/PDBPROD2/PDBPROD2_users01.dbf
    13 /u01/app/oracle/oradata/PRODCDB/PDBPROD1/example01.dbf

13 rows selected.
相关文章
|
11月前
|
安全 关系型数据库 MySQL
xtrabackup 问题“Too many open files”system error number 24
一个MySQL数据库备份的时候出现下面的错误
|
12月前
|
数据库
ORA-01113: file 1 needs media recovery ORA-01110: data file 1:
把下面两个隐含参数加入到pfile中
143 0
|
12月前
|
存储
rman备份失败:ORA-00245: control file backup failed; target is likely on a local file system
rman备份失败:ORA-00245: control file backup failed; target is likely on a local file system
100 0
|
12月前
|
Oracle 关系型数据库 数据库
change backup ... for db_unique_name不同步到control file
change backup … for db_unique_name 可以改变备份集所属的db_unique_name,但oracle官方文档里面没有说会不会同步到db_unique_name对应的数据库的control file。我自己测试发现不会同步到control file。
|
SQL 关系型数据库 Oracle
ORA-01466: unable to read data - table definition has changed
1. Oracle建议我们等待大约5分钟之后再进行flashback query新创建的表,否则可能会碰到这个错误ORA-01466: unable to read data - table definition has changed.
1725 0
|
MySQL 关系型数据库
|
关系型数据库 数据库管理 Oracle
|
关系型数据库 MySQL SQL