使用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 --
