使用11G的方式修改12C数据文件路径

环境:

OS:Centos 7

DB:12.2.0.1

从12C之后我们可以使用如下方式在线迁移数据文件
alter database move datafile '/path/A' to '/path/B'
但是使用原11G之前的方法迁移也是可以的,迁移例子如下,system文件比较特殊,需要关闭pdb,其他的数据文件offline即可

1.迁移系统文件system01

##查看数据文件

SQL> select file#,name from v$datafile;
 
        18 /u01/app/oracle/oradata/slnngk/pdb02/SLNNGK/F4536337D4BF5829E055CEFFD4D56E1F/datafile/o1_mf_system_kycz8o2p_.dbf
        19 /u01/app/oracle/oradata/slnngk/pdb02/SLNNGK/F4536337D4BF5829E055CEFFD4D56E1F/datafile/o1_mf_sysaux_kycz8o2s_.dbf
        20 /u01/app/oracle/oradata/slnngk/pdb02/SLNNGK/F4536337D4BF5829E055CEFFD4D56E1F/datafile/o1_mf_undotbs1_kycz8o2s_.dbf

##关闭pdb
SQL> alter session set container=pdb02;
SQL> shutdown immediate
Pluggable Database closed.

##拷贝文件到新目录
RMAN>copy datafile 18 to '/u01/app/oracle/oradata/slnngk/pdb02/system01.dbf';

##更新控制文件(sql模式下执行)
SQL> alter database rename file '/u01/app/oracle/oradata/slnngk/pdb02/SLNNGK/F4536337D4BF5829E055CEFFD4D56E1F/datafile/o1_mf_system_kycz8o2p_.dbf' to '/u01/app/oracle/oradata/slnngk/pdb02/system01.dbf';

##恢复文件
RMAN>recover datafile 18;

##启动数据库
SQL> alter session set container=pdb02;
SQL> startup
Pluggable Database opened.

2.普通文件sysaux01
##数据文件offline
SQL> alter session set container=pdb02;
SQL> alter database datafile 19 offline;
##拷贝数据文件
RMAN>copy datafile 19 to '/u01/app/oracle/oradata/slnngk/pdb02/sysaux01.dbf';
##这个时候数据库控制文件字典信息还没有修改,需要执行如下命令修改控制文件信息
RMAN> switch datafile 19 to copy;
datafile 19 switched to datafile copy "/u01/app/oracle/oradata/slnngk/pdb02/sysaux01.dbf
##这个时候查询数据字典,发现数据文件已经改变

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

     FILE# NAME
---------- ------------------------------------------------------------------------------------------------------------------------
        18 /u01/app/oracle/oradata/slnngk/pdb02/system01.dbf
        19 /u01/app/oracle/oradata/slnngk/pdb02/sysaux01.dbf
        20 /u01/app/oracle/oradata/slnngk/pdb02/SLNNGK/F4536337D4BF5829E055CEFFD4D56E1F/datafile/o1_mf_undotbs1_kycz8o2s_.dbf

##或者使用rename的方式更新控制文件
SQL> alter database rename file '/u01/app/oracle/oradata/slnngk/pdb02/SLNNGK/F4536337D4BF5829E055CEFFD4D56E1F/datafile/o1_mf_sysaux_kycz8o2s_.dbf' to '/u01/app/oracle/oradata/slnngk/pdb02/sysaux01.dbf';
然后恢复数据文件并online

##恢复数据文件并进行online
RMAN> recover datafile 19;
SQL> alter database datafile 19 online;

3.普通文件undotbs1

SQL> alter session set container=pdb02;
SQL> alter database datafile 20 offline;
alter database datafile 20 offline
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00376: file 20 cannot be read at this time
ORA-01110: data file 20: '/u01/app/oracle/oradata/slnngk/pdb02/SLNNGK/F4536337D4BF5829E055CEFFD4D56E1F/datafile/o1_mf_undotbs1_kycz8o2s_.dbf'
ORA-00376: file 20 cannot be read at this time
ORA-01110: data file 20: '/u01/app/oracle/oradata/slnngk/pdb02/SLNNGK/F4536337D4BF5829E055CEFFD4D56E1F/datafile/o1_mf_undotbs1_kycz8o2s_.dbf'
ORA-00376: file 20 cannot be read at this time
ORA-01110: data file 20: '/u01/app/oracle/oradata/slnngk/pdb02/SLNNGK/F4536337D4BF5829E055CEFFD4D56E1F/datafile/o1_mf_undotbs1_kycz8o2s_.dbf'
Process ID: 22569
Session ID: 75 Serial number: 27145

这个时候当前会话已经中断了,退出重新登录查看数据文件状态

SQL> alter session set container=pdb02;
SQL> set linesize 1000;
column file# format 99;
column name format a100;
column status format a10;
SQL> select file#,name,status from v$datafile;

     FILE# NAME                                                                                                          STATUS
---------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------
        18 /u01/app/oracle/oradata/slnngk/pdb02/system01.dbf                                                             SYSTEM
        19 /u01/app/oracle/oradata/slnngk/pdb02/sysaux01.dbf                                                             ONLINE
        20 /u01/app/oracle/oradata/slnngk/pdb02/SLNNGK/F4536337D4BF5829E055CEFFD4D56E1F/datafile/o1_mf_undotbs1_kycz8o2s_.dbf                                                                                                                 RECOVER

RMAN>copy datafile 20 to '/u01/app/oracle/oradata/slnngk/pdb02/undotbs01.dbf';
这个时候数据库控制文件字典信息还没有修改,需要执行如下命令修改控制文件信息
RMAN> switch datafile 20 to copy;

这个时候查询数据字典,发现数据文件已经改变

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

     FILE# NAME
---------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
        18 /u01/app/oracle/oradata/slnngk/pdb02/system01.dbf
        19 /u01/app/oracle/oradata/slnngk/pdb02/sysaux01.dbf
        20 /u01/app/oracle/oradata/slnngk/pdb02/undotbs01.dbf

或者
SQL> alter database rename file '/u01/app/oracle/oradata/slnngk/pdb02/SLNNGK/F4536337D4BF5829E055CEFFD4D56E1F/datafile/o1_mf_undotbs1_kycz8o2s_.dbf' to '/u01/app/oracle/oradata/slnngk/pdb02/undotbs01.dbf';
然后恢复数据文件并online
RMAN> recover datafile 20;
SQL> alter database datafile 20 online;

再次查看数据库文件状态

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

FILE# NAME                                                                                                 STATUS
----- ---------------------------------------------------------------------------------------------------- ----------
   18 /u01/app/oracle/oradata/slnngk/pdb02/system01.dbf                                                    SYSTEM
   19 /u01/app/oracle/oradata/slnngk/pdb02/sysaux01.dbf                                                    ONLINE
   20 /u01/app/oracle/oradata/slnngk/pdb02/undotbs01.dbf                                                   ONLINE

-- The End --