Oracle 12C 新特性之数据文件在线迁移

1   PDB数据文件的在线移动和重命名

在Oracle 12C之前版本中，数据文件的在线重命名与位置的变动，都需要在数据文件处在offline状态下进行（system表空间除外），在数据文件移动过程中会产生额外的归档日志用于在数据文件移动后恢复。而从Oracle 12C开始，数据文件均可在线进行移动和重命名，包括system数据文件，这大大降低了在数据文件移动过程中的风险，同时提高了正对较大数据文件移动中造成额外的归档压力。

1.  测试内容：

将ITAMpdb数据文件的路径/oracle/app/oracle/oradata/ITAM/0949345E60F736A4E0530101007F2940/datafile/ 变更为/oracle/app/oracle/oradata/ITAMpdb

2.  测试过程：

SQL> alter database move datafile '/oracle/app/oracle/oradata/ITAM/0949345E60F736A4E0530101007F2940/datafile/o1_mf_system_b7x420m8_.dbf' to '/oracle/app/oracle/oradata/ITAMpdb/o1_mf_system_b7x420m8_.dbf';

alter database move datafile '/oracle/app/oracle/oradata/ITAM/0949345E60F736A4E0530101007F2940/datafile/o1_mf_system_b7x420m8_.dbf' to '/oracle/app/oracle/oradata/ITAMpdb/o1_mf_system_b7x420m8_.dbf'

*

ERROR at line 1:

ORA-01276: Cannot add file /oracle/app/oracle/oradata/ITAMpdb/o1_mf_system_b7x420m8_.dbf.  File has an Oracle Managed Files file name.

 

说明在OMF管理方式下数据文件的移动不能用同样的文件名。

 

SQL> host oerr ora 1276

01276, 00000, "Cannot add file %s.  File has an Oracle Managed Files file name."

// *Cause: An attempt was made to add to the database a datafile, log file,

//         control file, snapshot control file, backup control file,

//         datafile copy, control file copy or backuppiece with an Oracle

//         Managed Files file name.

// *Action: Retry the operation with a new file name.

 

SQL> alter database move datafile '/oracle/app/oracle/oradata/ITAM/0949345E60F736A4E0530101007F2940/datafile/o1_mf_system_b7x420m8_.dbf' to '/oracle/app/oracle/oradata/ITAMpdb/system_01.dbf';

alter database move datafile '/oracle/app/oracle/oradata/ITAM/0949345E60F736A4E0530101007F2940/datafile/o1_mf_system_b7x420m8_.dbf' to '/oracle/app/oracle/oradata/ITAMpdb/system_01.dbf'

*

ERROR at line 1:

ORA-01135: file 8 accessed for DML/query is offline

ORA-01110: data file 8: '/oracle/app/oracle/oradata/ITAM/0949345E60F736A4E0530101007F2940/datafile/o1_mf_system_b7x420m8_.dbf'

 

说明：在PDB中移动数据文件需要PDB处于OPEN状态，否则移动出现错误。

SQL> conn /as sysdba

Connected.

SQL> select name,open_mode from v$pdbs;

 

NAME                    OPEN_MODE

---------------------------------------- ------------------------------

PDB$SEED                READ ONLY

ITAMPDB               MOUNTED

 

SQL> alter pluggable database itampdb open;

 

Pluggable database altered.

 

SQL> alter session set container=itampdb;

 

Session altered.

 

SQL> alter database move datafile '/oracle/app/oracle/oradata/ITAM/0949345E60F736A4E0530101007F2940/datafile/o1_mf_system_b7x420m8_.dbf' to '/oracle/app/oracle/oradata/ITAMpdb/system_01.dbf';

 

Database altered.

 

SQL> select file_name from dba_data_files;

 

FILE_NAME

----------------------------------------------------------------------------------------------------------------------------------------------------------------

/oracle/app/oracle/oradata/ITAMpdb/system_01.dbf    --------文件移动成功

/oracle/app/oracle/oradata/ITAM/0949345E60F736A4E0530101007F2940/datafile/o1_mf_sysaux_b7x420mk_.dbf

/oracle/app/oracle/oradata/ITAM/0949345E60F736A4E0530101007F2940/datafile/o1_mf_users_b7x425yb_.dbf

 

SQL> alter database move datafile '/oracle/app/oracle/oradata/ITAM/0949345E60F736A4E0530101007F2940/datafile/o1_mf_sysaux_b7x420mk_.dbf' to '/oracle/app/oracle/oradata/ITAMpdb/sysaux_01.dbf';

 

Database altered.

 

SQL> alter database move datafile '/oracle/app/oracle/oradata/ITAM/0949345E60F736A4E0530101007F2940/datafile/o1_mf_users_b7x425yb_.dbf' to '/oracle/app/oracle/oradata/ITAMpdb/users_01.dbf';

 

Database altered.

 

SQL> select file_name from dba_data_files;

 

FILE_NAME

----------------------------------------------------------------------------------------------------------------------------------------------------------------

/oracle/app/oracle/oradata/ITAMpdb/sysaux_01.dbf

/oracle/app/oracle/oradata/ITAMpdb/users_01.dbf

/oracle/app/oracle/oradata/ITAMpdb/system_01.dbf

结论：

1、在OMF管理方式下数据文件的移动不能用同样的文件名。

2、在PDB中移动数据文件需要PDB处于OPEN状态，否则移动出现错误。

3、即使在ASM磁盘下移动数据文件，如果文件路径不存在，同样也会报错。

4、在确保前面三项条件正确的情况下，数据文件的移动完全可以在线完成，包括system表空间的数据文件。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28612416/viewspace-1401858/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28612416/viewspace-1401858/