oracle 12.1 迁移,Oracle 12C 在线重命名和迁移数据文件

1.熟悉環境

[oracle@DB01 ~]$ cd /u02/oradata/MPCDB/MPMON/

[oracle@DB01 MPMON]$ pwd

/u02/oradata/MPCDB/MPMON

[oracle@DB01 MPMON]$ ll

total 9931700

-rw-r----- 1 oracle oinstall 268443648 Sep 25 12:40 I_MPMON_UD_1.dbf

-rw-r----- 1 oracle oinstall 268443648 Sep 25 12:31 I_MPMON_UD_2.dbf

-rw-r----- 1 oracle oinstall 268443648 May 26 2019 MPMON_TD1_1.dbf

-rw-r----- 1 oracle oinstall 268443648 Sep 25 07:01 MPMON_TD1_2.dbf

-rw-r----- 1 oracle oinstall 1207967744 Sep 25 12:42 MPMON_UD_1.dbf

-rw-r----- 1 oracle oinstall 939532288 Sep 25 12:41 MPMON_UD_2.dbf

-rw-r----- 1 oracle oinstall 754982912 Sep 25 12:44 sysaux01.dbf

-rw-r----- 1 oracle oinstall 283123712 Sep 25 12:43 system01.dbf

-rw-r----- 1 oracle oinstall 6442459136 Sep 25 12:43 undotbs01.dbf

[oracle@DB01 MPMON]$ sql sys/MPCDBMPCDB@10.0.2.6:1521/MPMON as sysdba

SQL> SET SQLFORMAT ansiconsole

SQL> show pdbs;

CON_ID CON_NAME OPEN MODE RESTRICTED

6 MPMON READ WRITE NO

SQL> show con_name

CON_NAME

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

MPMON

SQL> select t.tablespace_name,t.file_name from dba_data_files t;

TABLESPACE_NAME FILE_NAME

SYSTEM /u02/oradata/MPCDB/MPMON/system01.dbf

SYSAUX /u02/oradata/MPCDB/MPMON/sysaux01.dbf

UNDOTBS1 /u02/oradata/MPCDB/MPMON/undotbs01.dbf

MPMON_UD /u02/oradata/MPCDB/MPMON/MPMON_UD_1.dbf

MPMON_UD /u02/oradata/MPCDB/MPMON/MPMON_UD_2.dbf

I_MPMON_UD /u02/oradata/MPCDB/MPMON/I_MPMON_UD_1.dbf

I_MPMON_UD /u02/oradata/MPCDB/MPMON/I_MPMON_UD_2.dbf

2.創建測試用途表空間 及 表

create tablespace TAB_UD datafile

'/u02/oradata/MPCDB/MPMON/TAB_1.dbf' size 8M AUTOEXTEND ON NEXT 8M MAXSIZE UNLIMITED,

'/u02/oradata/MPCDB/MPMON/TAB_2.dbf' size 8M AUTOEXTEND ON NEXT 8M MAXSIZE UNLIMITED

extent management local segment space management auto;

create table t1

(

aaa number

) tablespace TAB_UD;

insert into t1 values(1);

commit;

SQL> select t.tablespace_name,t.file_name from dba_data_files t where t.tablespace_name='TAB_UD';

TABLESPACE_NAME FILE_NAME

TAB_UD /u02/oradata/MPCDB/MPMON/TAB_1.dbf

TAB_UD /u02/oradata/MPCDB/MPMON/TAB_2.dbf

3.重命名数据文件 & 迁移数据文件 (同命名)

alter database move datafile '/u02/oradata/MPCDB/MPMON/TAB_1.dbf' to '/u02/oradata/MPCDB/MPMON/TAB_01.dbf';

alter database move datafile '/u02/oradata/MPCDB/MPMON/TAB_2.dbf' to '/u02/oradata/MPCDB/MPMON/TAB_02.dbf';

SQL> select t.tablespace_name,t.file_name from dba_data_files t where t.tablespace_name='TAB_UD';

TABLESPACE_NAME FILE_NAME

TAB_UD /u02/oradata/MPCDB/MPMON/TAB_02.dbf

TAB_UD /u02/oradata/MPCDB/MPMON/TAB_01.dbf

[oracle@DB01 MPMON]$ ll TAB*

-rw-r----- 1 oracle oinstall 8396800 Sep 25 12:56 TAB_01.dbf

-rw-r----- 1 oracle oinstall 8396800 Sep 25 12:57 TAB_02.dbf

4.迁移后保留原数据文件，源数据文件如果不需要可以在操作系统层面直接删除

alter database move datafile '/u02/oradata/MPCDB/MPMON/TAB_01.dbf' to '/u02/oradata/MPCDB/MPMON2/TAB_01.dbf' keep;

[oracle@DB01 MPMON2]$ pwd

/u02/oradata/MPCDB/MPMON2

[oracle@DB01 MPMON2]$ ll

total 8200

-rw-r----- 1 oracle oinstall 8396800 Sep 25 13:03 TAB_01.dbf

[oracle@DB01 MPMON]$ ll TAB*

-rw-r----- 1 oracle oinstall 8396800 Sep 25 13:03 TAB_01.dbf

-rw-r----- 1 oracle oinstall 8396800 Sep 25 12:57 TAB_02.dbf

SQL> select t.tablespace_name,t.file_name from dba_data_files t where t.tablespace_name='TAB_UD';

TABLESPACE_NAME FILE_NAME

TAB_UD /u02/oradata/MPCDB/MPMON/TAB_02.dbf

TAB_UD /u02/oradata/MPCDB/MPMON2/TAB_01.dbf