526互联

dataguard环境主从库数据文件迁移

发布时间 2023-04-18 09:55:49作者: slnngk

环境:
OS:Centos 7
DB:12.2.0.1

 

主库:

alter session set container=pdb2;
column file_name format a64;
select file_name,file_id from dba_data_files;

SQL> select file_name,file_id from dba_data_files;

FILE_NAME                                                           FILE_ID
---------------------------------------------------------------- ----------
/u01/app/oracle/oradata/slnngk/pdbseed/pdb2/system01.dbf                 13
/u01/app/oracle/oradata/slnngk/pdbseed/pdb2/sysaux01.dbf                 14
/u01/app/oracle/oradata/slnngk/pdbseed/pdb2/undotbs01.dbf                15
/u01/app/oracle/oradata/slnngk/pdbseed/pdb2/tps_pdb01.dbf                21

 

迁移到新目录(目录需要提前创建好)

alter database move datafile 13 to '/u01/app/oracle/oradata/slnngk/pdb2/system01.dbf';
alter database move datafile 14 to '/u01/app/oracle/oradata/slnngk/pdb2/sysaux01.dbf';
alter database move datafile 15 to '/u01/app/oracle/oradata/slnngk/pdb2/undotbs01.dbf';
alter database move datafile 21 to '/u01/app/oracle/oradata/slnngk/pdb2/tps_pdb01.dbf';

 

临时文件

SQL> select file_id,file_name from dba_temp_files;

   FILE_ID FILE_NAME
---------- ----------------------------------------------------------------
         4 /u01/app/oracle/oradata/slnngk/pdbseed/pdb2/temp012023-04-16_21-50-13-004-PM.dbf

 

alter tablespace temp add tempfile '/u01/app/oracle/oradata/slnngk/pdb2/temp01.dbf' size 10M;
删除原来的临时文件
alter tablespace temp drop tempfile '/u01/app/oracle/oradata/slnngk/pdbseed/pdb2/temp012023-04-16_21-50-13-004-PM.dbf';

 

从库:

alter session set container=pdb2;
column file_name format a64;
SQL> select file_name,file_id from dba_data_files;

FILE_NAME                                                           FILE_ID
---------------------------------------------------------------- ----------
/u01/app/oracle/oradata/slavea/pdbseed/pdb2/system01.dbf                 13
/u01/app/oracle/oradata/slavea/pdbseed/pdb2/sysaux01.dbf                 14
/u01/app/oracle/oradata/slavea/pdbseed/pdb2/undotbs01.dbf                15
/u01/app/oracle/oradata/slavea/pdbseed/pdb2/tps_pdb01.dbf                21



alter database move datafile 13 to '/u01/app/oracle/oradata/slavea/pdb2/system01.dbf';
alter database move datafile 14 to '/u01/app/oracle/oradata/slavea/pdb2/sysaux01.dbf';
alter database move datafile 15 to '/u01/app/oracle/oradata/slavea/pdb2/undotbs01.dbf';
alter database move datafile 21 to '/u01/app/oracle/oradata/slavea/pdb2/tps_pdb01.dbf';

 

临时文件

SQL> select file_id,file_name from dba_temp_files;
   FILE_ID FILE_NAME
---------- ----------------------------------------------------------------
         4 /u01/app/oracle/oradata/slavea/pdbseed/pdb2/temp012023-04-16_21-50-13-004-PM.dbf


alter tablespace temp add tempfile '/u01/app/oracle/oradata/slavea/pdb2/temp01.dbf' size 10M;
删除原来的临时文件
alter tablespace temp drop tempfile '/u01/app/oracle/oradata/slavea/pdbseed/pdb2/temp012023-04-16_21-50-13-004-PM.dbf';