环境:
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 --
标签:11G,12C,数据文件,pdb02,app,datafile,dbf,oradata,slnngk From: https://www.cnblogs.com/hxlasky/p/17115360.html