ORACLE修改数据文件路径（四种方式）
【摘要】 DB:Oracle 11.2.0.4.0 方法一：datafile offline(1) 修改路径前offline数据文件，影响被offline数据文件中数据的读取和修改，不影响同一表空间下其他online数据文件内数据的读取和修改。(2) online datafile前，需要执行recover datafile操作,确保数据库处于归档模式下。 **方法二：tablepace offli...
DB:Oracle 11.2.0.4.0
方法一：datafile offline
(1) 修改路径前offline数据文件，影响被offline数据文件中数据的读取和修改，不影响同一表空间下其他online数据文件内数据的读取和修改。
(2) online datafile前，需要执行recover datafile操作,确保数据库处于归档模式下。
**方法二：tablepace offline **
(1) 修改路径前tablespace offline,影响offline tablespace下所有数据文件的读取和使用。
(2) online tablespace前，不需要执行recover tablespace操作。
方法三：restart instance
(1) 重启实例，影响所有操作。
方法四：ALTER DATABASE MOVE DATAFILE ‘原路径’ TO ‘新路径’;
(1) 12C开始支持在线修改数据文件路径，迁移过程中不影响数据使用。
实践：
方法一：datafile offline
查看数据库版本
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
创建表空间cjctbs
SQL> create tablespace cjctbs datafile '/u01/app/oracle11/oradata/chendb/cjctbs01.dbf' size 1m;
Tablespace created.
创建用户cjc并授权
SQL> create user cjc identified by cjc default tablespace cjctbs;
User created.
SQL> grant connect,resource,dba to cjc;
Grant succeeded.
查看数据文件信息
col file_name for a50
col tablespace_name for a20
set line 200
select tablespace_name,file_name from dba_data_files;
TABLESPACE_NAME FILE_NAME
-------------------- --------------------------------------------------
USERS /u01/app/oracle11/oradata/chendb/users01.dbf
UNDOTBS1 /u01/app/oracle11/oradata/chendb/undotbs01.dbf
SYSAUX /u01/app/oracle11/oradata/chendb/sysaux01.dbf
SYSTEM /u01/app/oracle11/oradata/chendb/system01.dbf
EXAMPLE /u01/app/oracle11/oradata/chendb/example01.dbf
CJCTBS /u01/app/oracle11/oradata/chendb/cjctbs01.dbf
6 rows selected.
创建测试数据
conn cjc/cjc
create table t1(id number,itime varchar2(200));
CREATE OR REPLACE PROCEDURE insert_pro is
begin
for i in 1 .. 1000000 loop
insert into t1 values (i, to_char(sysdate, 'yyyy/mm/dd hh24:mi:ss'));
commit;
end loop;
end;
/
SQL> exec insert_pro();
BEGIN insert_pro(); END;
*
ERROR at line 1:
ORA-01653: unable to extend table CJC.T1 by 8 in tablespace CJCTBS
ORA-06512: at "CJC.INSERT_PRO", line 4
ORA-06512: at line 1
SQL> select count(*) from t1;
COUNT(*)
----------
26943
模拟添加错路径添加数据文件
SQL> alter tablespace cjctbs add datafile '/home/oracle/cjctbs02.dbf' size 1m;
Tablespace altered.
查看数据文件信息
col file_name for a50
col tablespace_name for a20
set line 200
select tablespace_name,file_id,file_name from dba_data_files order by 2;
TABLESPACE_NAME FILE_ID FILE_NAME
-------------------- ---------- --------------------------------------------------
SYSTEM 1 /u01/app/oracle11/oradata/chendb/system01.dbf
SYSAUX 2 /u01/app/oracle11/oradata/chendb/sysaux01.dbf
UNDOTBS1 3 /u01/app/oracle11/oradata/chendb/undotbs01.dbf
USERS 4 /u01/app/oracle11/oradata/chendb/users01.dbf
EXAMPLE 5 /u01/app/oracle11/oradata/chendb/example01.dbf
CJCTBS 6 /u01/app/oracle11/oradata/chendb/cjctbs01.dbf
CJCTBS 7 /home/oracle/cjctbs02.dbf
7 rows selected.
插入数据
SQL> exec insert_pro();
BEGIN insert_pro(); END;
*
ERROR at line 1:
ORA-01653: unable to extend table CJC.T1 by 128 in tablespace CJCTBS
ORA-06512: at "CJC.INSERT_PRO", line 4
ORA-06512: at line 1
移动数据文件/home/oracle/cjctbs02.dbf到/u01/app/oracle11/oradata/chendb/cjctbs02.dbf
SQL> show user
USER is "CJC"
SQL> select count(*) from t1;
COUNT(*)
----------
28946
离线cjctbs02.dbf 数据文件
alter database datafile 7 offline;
查看数据文件状态
select file_name, status, online_status from dba_data_files where tablespace_name='CJCTBS';
FILE_NAME STATUS ONLINE_
-------------------------------------------------- --------- -------
/u01/app/oracle11/oradata/chendb/cjctbs01.dbf AVAILABLE ONLINE
/home/oracle/cjctbs02.dbf AVAILABLE RECOVER
查看控制文件中记录的数据文件检查点信息
SQL> select file#, CHECKPOINT_CHANGE#, OFFLINE_CHANGE# from v$datafile;
FILE# CHECKPOINT_CHANGE# OFFLINE_CHANGE#
---------- ------------------ ---------------
1 2325667 925701
2 2325667 925701
3 2325667 925701
4 2325667 925701
5 2325667 953748
6 2326617 0
7 2364389 0
7 rows selected.
查看数据文件头检查点信息
select file#, status, recover, fuzzy, CHECKPOINT_CHANGE# from v$datafile_header;
FILE# STATUS REC FUZ CHECKPOINT_CHANGE#
---------- ------- --- --- ------------------
1 ONLINE NO YES 2325667
2 ONLINE NO YES 2325667
3 ONLINE NO YES 2325667
4 ONLINE NO YES 2325667
5 ONLINE NO YES 2325667
6 ONLINE NO YES 2326617
7 OFFLINE YES YES 2364389
7 rows selected.
表空间状态
SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
-------------------- ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE
CJCTBS ONLINE
7 rows selected.
查看v$recover_file
SQL> select *from v$recover_file;
FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
---------- ------- ------- ----------------------------------------------------------------- ---------- ---------
7 OFFLINE OFFLINE 2364389 29-NOV-20
生成检查点
SQL> alter system checkpoint;
System altered.
查看数据文件头检查点信息
SQL> select file#, status, recover, fuzzy, CHECKPOINT_CHANGE# from v$datafile_header;
FILE# STATUS REC FUZ CHECKPOINT_CHANGE#
---------- ------- --- --- ------------------
1 ONLINE NO YES 2366991
2 ONLINE NO YES 2366991
3 ONLINE NO YES 2366991
4 ONLINE NO YES 2366991
5 ONLINE NO YES 2366991
6 ONLINE NO YES 2366991
7 OFFLINE YES YES 2364389
7 rows selected.
SQL> select file#, CHECKPOINT_CHANGE#, OFFLINE_CHANGE# from v$datafile;
FILE# CHECKPOINT_CHANGE# OFFLINE_CHANGE#
---------- ------------------ ---------------
1 2366991 925701
2 2366991 925701
3 2366991 925701
4 2366991 925701
5 2366991 953748
6 2366991 0
7 2364389 0
7 rows selected.
将数据文件移动到新路径
使用copy或rman都可以
RMAN> copy datafile '/home/oracle/cjctbs02.dbf' to '/u01/app/oracle11/oradata/chendb/cjctbs02.dbf';
Starting backup at 29-NOV-20
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00007 name=/home/oracle/cjctbs02.dbf
output file name=/u01/app/oracle11/oradata/chendb/cjctbs02.dbf tag=TAG20201129T213347 RECID=33 STAMP=1057786427
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 29-NOV-20
查看文件已经拷贝
[oracle@cjcos01 arch]$ cd /u01/app/oracle11/oradata/chendb
[oracle@cjcos01 chendb]$ ll -rth
total 2.0G
-rw-r----- 1 oracle oinstall 51M Nov 29 17:04 redo01.log
-rw-r----- 1 oracle oinstall 51M Nov 29 17:04 redo02.log
-rw-r----- 1 oracle oinstall 21M Nov 29 18:04 temp01.dbf
-rw-r----- 1 oracle oinstall 751M Nov 29 21:31 system01.dbf
-rw-r----- 1 oracle oinstall 561M Nov 29 21:31 sysaux01.dbf
-rw-r----- 1 oracle oinstall 5.1M Nov 29 21:31 users01.dbf
-rw-r----- 1 oracle oinstall 201M Nov 29 21:31 undotbs01.dbf
-rw-r----- 1 oracle oinstall 314M Nov 29 21:31 example01.dbf
-rw-r----- 1 oracle oinstall 1.1M Nov 29 21:31 cjctbs01.dbf
-rw-r----- 1 oracle oinstall 1.1M Nov 29 21:33 cjctbs02.dbf
-rw-r----- 1 oracle oinstall 51M Nov 29 21:34 redo03.log
-rw-r----- 1 oracle oinstall 9.3M Nov 29 21:34 control01.ctl
-rw-r----- 1 oracle oinstall 9.3M Nov 29 21:34 control02.ctl
修改控制文件中记录的位置
SQL> alter database rename file '/home/oracle/cjctbs02.dbf' to '/u01/app/oracle11/oradata/chendb/cjctbs02.dbf';
Database altered.
需要确保数据文件已经在新的路径下了，否则如下报错：
SQL> alter database rename file '/home/oracle/cjctbs02.dbf' to '/u01/app/oracle11/oradata/chendb/cjctbs02.dbf';
alter database rename file '/home/oracle/cjctbs02.dbf' to '/u01/app/oracle11/oradata/chendb/cjctbs02.dbf'
*
ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-01141: error renaming data file 7 - new file '/u01/app/oracle11/oradata/chendb/cjctbs02.dbf' not found
ORA-01110: data file 7: '/home/oracle/cjctbs02.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
查看数据文件路径
SQL> col file_name for a50
col tablespace_name for a20
set line 200
select tablespace_name,file_id,file_name from dba_data_files order by 2;SQL> SQL> SQL>
TABLESPACE_NAME FILE_ID FILE_NAME
-------------------- ---------- --------------------------------------------------
SYSTEM 1 /u01/app/oracle11/oradata/chendb/system01.dbf
SYSAUX 2 /u01/app/oracle11/oradata/chendb/sysaux01.dbf
UNDOTBS1 3 /u01/app/oracle11/oradata/chendb/undotbs01.dbf
USERS 4 /u01/app/oracle11/oradata/chendb/users01.dbf
EXAMPLE 5 /u01/app/oracle11/oradata/chendb/example01.dbf
CJCTBS 6 /u01/app/oracle11/oradata/chendb/cjctbs01.dbf
CJCTBS 7 /u01/app/oracle11/oradata/chendb/cjctbs02.dbf
7 rows selected.
datafile位offline状态下无法查询数据
SQL> select count(*) from t1;
select count(*) from t1
*
ERROR at line 1:
ORA-00376: file 7 cannot be read at this time
ORA-01110: data file 7: '/u01/app/oracle11/oradata/chendb/cjctbs02.dbf'
可以查询同一表空间下其他online状态的数据文件数据
SQL> select count(*) from t1 where rownum<=10;
COUNT(*)
----------
10
SQL> select count(*) from t1 where rownum<=100000000;
select count(*) from t1 where rownum<=100000000
*
ERROR at line 1:
ORA-00376: file 7 cannot be read at this time
ORA-01110: data file 7: '/u01/app/oracle11/oradata/chendb/cjctbs02.dbf'
数据库处于归档模式
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /arch
Oldest online log sequence 40
Next log sequence to archive 42
Current log sequence 42
不能直接online数据文件
SQL> alter database datafile 7 online;
alter database datafile 7 online
*
ERROR at line 1:
ORA-01113: file 7 needs media recovery
ORA-01110: data file 7: '/u01/app/oracle11/oradata/chendb/cjctbs02.dbf'
恢复datafile
SQL> recover datafile 7;
Media recovery complete.
**执行onlne datafile **
SQL> alter database datafile 7 online;
Database altered.
查询测试数据
SQL> conn cjc/cjc
Connected.
SQL> select count(*) from t1;
COUNT(*)
----------
28946
查看数据文件信息
SQL> col file_name for a50
col tablespace_name for a20
set line 200
select tablespace_name,file_id,file_name from dba_data_files order by 2;SQL> SQL> SQL>
TABLESPACE_NAME FILE_ID FILE_NAME
-------------------- ---------- --------------------------------------------------
SYSTEM 1 /u01/app/oracle11/oradata/chendb/system01.dbf
SYSAUX 2 /u01/app/oracle11/oradata/chendb/sysaux01.dbf
UNDOTBS1 3 /u01/app/oracle11/oradata/chendb/undotbs01.dbf
USERS 4 /u01/app/oracle11/oradata/chendb/users01.dbf
EXAMPLE 5 /u01/app/oracle11/oradata/chendb/example01.dbf
CJCTBS 6 /u01/app/oracle11/oradata/chendb/cjctbs01.dbf
CJCTBS 7 /u01/app/oracle11/oradata/chendb/cjctbs02.dbf
7 rows selected.
SQL> select file#, CHECKPOINT_CHANGE#, OFFLINE_CHANGE# from v$datafile;
FILE# CHECKPOINT_CHANGE# OFFLINE_CHANGE#
---------- ------------------ ---------------
1 2367288 925701
2 2367288 925701
3 2367288 925701
4 2367288 925701
5 2367288 953748
6 2367288 0
7 2367288 0
7 rows selected.
SQL> select file#, status, recover, fuzzy, CHECKPOINT_CHANGE# from v$datafile_header;
FILE# STATUS REC FUZ CHECKPOINT_CHANGE#
---------- ------- --- --- ------------------
1 ONLINE NO YES 2367288
2 ONLINE NO YES 2367288
3 ONLINE NO YES 2367288
4 ONLINE NO YES 2367288
5 ONLINE NO YES 2367288
6 ONLINE NO YES 2367288
7 ONLINE NO YES 2367288
7 rows selected.
[oracle@cjcos01 ~]$ mv cjctbs02.dbf cjctbs02.dbf.bak
**方法二：tablepace offline **
添加数据文件
模拟添加错误位置
SQL> conn cjc/cjc
Connected.
SQL> alter tablespace cjctbs add datafile '/home/oracle/cjctbs03.dbf' size 1m;
Tablespace altered.
查看数据文件信息
SQL> col file_name for a50
col tablespace_name for a20
set line 200
select tablespace_name,file_id,file_name from dba_data_files order by 2;SQL> SQL> SQL>
TABLESPACE_NAME FILE_ID FILE_NAME
-------------------- ---------- --------------------------------------------------
SYSTEM 1 /u01/app/oracle11/oradata/chendb/system01.dbf
SYSAUX 2 /u01/app/oracle11/oradata/chendb/sysaux01.dbf
UNDOTBS1 3 /u01/app/oracle11/oradata/chendb/undotbs01.dbf
USERS 4 /u01/app/oracle11/oradata/chendb/users01.dbf
EXAMPLE 5 /u01/app/oracle11/oradata/chendb/example01.dbf
CJCTBS 6 /u01/app/oracle11/oradata/chendb/cjctbs01.dbf
CJCTBS 7 /u01/app/oracle11/oradata/chendb/cjctbs02.dbf
CJCTBS 8 /home/oracle/cjctbs03.dbf
8 rows selected.
执行表空间offline
SQL> alter tablespace cjctbs offline;
Tablespace altered.
查看表空间状态
SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
-------------------- ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE
CJCTBS OFFLINE
7 rows selected.
查看v$recover_file
SQL> select *from v$recover_file;
FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
---------- ------- ------- ----------------------------------------------------------------- ---------- ---------
6 OFFLINE OFFLINE OFFLINE NORMAL 0
7 OFFLINE OFFLINE OFFLINE NORMAL 0
8 OFFLINE OFFLINE OFFLINE NORMAL 0
查看数据文件头信息
SQL> select file#, status, recover, fuzzy, CHECKPOINT_CHANGE# from v$datafile_header;
FILE# STATUS REC FUZ CHECKPOINT_CHANGE#
---------- ------- --- --- ------------------
1 ONLINE NO YES 2367431
2 ONLINE NO YES 2367431
3 ONLINE NO YES 2367431
4 ONLINE NO YES 2367431
5 ONLINE NO YES 2367431
6 OFFLINE 0
7 OFFLINE 0
8 OFFLINE 0
8 rows selected.
SQL> select file#, CHECKPOINT_CHANGE#, OFFLINE_CHANGE# from v$datafile;
FILE# CHECKPOINT_CHANGE# OFFLINE_CHANGE#
---------- ------------------ ---------------
1 2367431 925701
2 2367431 925701
3 2367431 925701
4 2367431 925701
5 2367431 953748
6 2367562 0
7 2367562 0
8 2367562 0
8 rows selected.
无法查询offline tablespace下所有数据
SQL> select count(*) from t1;
select count(*) from t1
*
ERROR at line 1:
ORA-00376: file 6 cannot be read at this time
ORA-01110: data file 6: '/u01/app/oracle11/oradata/chendb/cjctbs01.dbf'
SQL> select count(*) from t1 where rownum<=10;
select count(*) from t1 where rownum<=10
*
ERROR at line 1:
ORA-00376: file 6 cannot be read at this time
ORA-01110: data file 6: '/u01/app/oracle11/oradata/chendb/cjctbs01.dbf'
SQL> select * from t1;
select * from t1
*
ERROR at line 1:
ORA-00376: file 6 cannot be read at this time
ORA-01110: data file 6: '/u01/app/oracle11/oradata/chendb/cjctbs01.dbf'
拷贝数据文件到新位置
[oracle@cjcos01 ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Sun Nov 29 21:47:38 2020
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: CHENDB (DBID=1831901477)
RMAN> copy datafile '/home/oracle/cjctbs03.dbf' to '/u01/app/oracle11/oradata/chendb/cjctbs03.dbf';
Starting backup at 29-NOV-20
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=58 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00008 name=/home/oracle/cjctbs03.dbf
output file name=/u01/app/oracle11/oradata/chendb/cjctbs03.dbf tag=TAG20201129T214814 RECID=34 STAMP=1057787294
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 29-NOV-20
修改控制文件记录的数据文件位置
SQL> alter database rename file '/home/oracle/cjctbs03.dbf' to '/u01/app/oracle11/oradata/chendb/cjctbs03.dbf';
Database altered.
online表空间
不需要执行recover操作
SQL> alter tablespace cjctbs online;
Tablespace altered.
查询数据
SQL> select count(*) from t1;
COUNT(*)
----------
28946
查看下数据文件信息
SQL> col file_name for a50
col tablespace_name for a20
set line 200
select tablespace_name,file_id,file_name from dba_data_files order by 2;SQL> SQL> SQL>
TABLESPACE_NAME FILE_ID FILE_NAME
-------------------- ---------- --------------------------------------------------
SYSTEM 1 /u01/app/oracle11/oradata/chendb/system01.dbf
SYSAUX 2 /u01/app/oracle11/oradata/chendb/sysaux01.dbf
UNDOTBS1 3 /u01/app/oracle11/oradata/chendb/undotbs01.dbf
USERS 4 /u01/app/oracle11/oradata/chendb/users01.dbf
EXAMPLE 5 /u01/app/oracle11/oradata/chendb/example01.dbf
CJCTBS 6 /u01/app/oracle11/oradata/chendb/cjctbs01.dbf
CJCTBS 7 /u01/app/oracle11/oradata/chendb/cjctbs02.dbf
CJCTBS 8 /u01/app/oracle11/oradata/chendb/cjctbs03.dbf
8 rows selected.
查询表空间信息
SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
-------------------- ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE
CJCTBS ONLINE
7 rows selected.
SQL> select *from v$recover_file;
no rows selected
SQL> select file#, status, recover, fuzzy, CHECKPOINT_CHANGE# from v$datafile_header;
FILE# STATUS REC FUZ CHECKPOINT_CHANGE#
---------- ------- --- --- ------------------
1 ONLINE NO YES 2367431
2 ONLINE NO YES 2367431
3 ONLINE NO YES 2367431
4 ONLINE NO YES 2367431
5 ONLINE NO YES 2367431
6 ONLINE NO YES 2367730
7 ONLINE NO YES 2367730
8 ONLINE NO YES 2367730
8 rows selected.
SQL> select file#, CHECKPOINT_CHANGE#, OFFLINE_CHANGE# from v$datafile;
FILE# CHECKPOINT_CHANGE# OFFLINE_CHANGE#
---------- ------------------ ---------------
1 2367431 925701
2 2367431 925701
3 2367431 925701
4 2367431 925701
5 2367431 953748
6 2367730 2367562
7 2367730 2367562
8 2367730 2367562
8 rows selected.
方法三：restart instance
添加数据文件
模拟添加错误位置
SQL> alter tablespace cjctbs add datafile '/home/oracle/cjctbs05.dbf' size 1m;
Tablespace altered.
查看数据文件信息
SQL> col file_name for a50
col tablespace_name for a20
set line 200
select tablespace_name,file_id,file_name from dba_data_files order by 2;SQL> SQL> SQL>
TABLESPACE_NAME FILE_ID FILE_NAME
-------------------- ---------- --------------------------------------------------
SYSTEM 1 /u01/app/oracle11/oradata/chendb/system01.dbf
SYSAUX 2 /u01/app/oracle11/oradata/chendb/sysaux01.dbf
UNDOTBS1 3 /u01/app/oracle11/oradata/chendb/undotbs01.dbf
USERS 4 /u01/app/oracle11/oradata/chendb/users01.dbf
EXAMPLE 5 /u01/app/oracle11/oradata/chendb/example01.dbf
CJCTBS 6 /u01/app/oracle11/oradata/chendb/cjctbs01.dbf
CJCTBS 7 /u01/app/oracle11/oradata/chendb/cjctbs02.dbf
CJCTBS 8 /u01/app/oracle11/oradata/chendb/cjctbs03.dbf
CJCTBS 9 /home/oracle/cjctbs05.dbf
9 rows selected.
停止实例
SQL> conn / as sysdba
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
启动数据库到mout
SQL> startup mount
ORACLE instance started.
Total System Global Area 726540288 bytes
Fixed Size 2256792 bytes
Variable Size 448790632 bytes
Database Buffers 272629760 bytes
Redo Buffers 2863104 bytes
Database mounted.
拷贝数据文件到新位置
[oracle@cjcos01 ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Sun Nov 29 22:22:23 2020
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: CHENDB (DBID=1831901477, not open)
RMAN> copy datafile '/home/oracle/cjctbs05.dbf' to '/u01/app/oracle11/oradata/chendb/cjctbs05.dbf';
Starting backup at 29-NOV-20
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00009 name=/home/oracle/cjctbs05.dbf
output file name=/u01/app/oracle11/oradata/chendb/cjctbs05.dbf tag=TAG20201129T222227 RECID=35 STAMP=1057789348
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 29-NOV-20
修改控制文件记录的数据文件位置
SQL> alter database rename file '/home/oracle/cjctbs05.dbf' to '/u01/app/oracle11/oradata/chendb/cjctbs05.dbf';
Database altered.
启动数据库
SQL> alter database open;
Database altered.
查看数据文件信息
SQL> col file_name for a50
col tablespace_name for a20
set line 200
select tablespace_name,file_id,file_name from dba_data_files order by 2;SQL> SQL> SQL>
TABLESPACE_NAME FILE_ID FILE_NAME
-------------------- ---------- --------------------------------------------------
SYSTEM 1 /u01/app/oracle11/oradata/chendb/system01.dbf
SYSAUX 2 /u01/app/oracle11/oradata/chendb/sysaux01.dbf
UNDOTBS1 3 /u01/app/oracle11/oradata/chendb/undotbs01.dbf
USERS 4 /u01/app/oracle11/oradata/chendb/users01.dbf
EXAMPLE 5 /u01/app/oracle11/oradata/chendb/example01.dbf
CJCTBS 6 /u01/app/oracle11/oradata/chendb/cjctbs01.dbf
CJCTBS 7 /u01/app/oracle11/oradata/chendb/cjctbs02.dbf
CJCTBS 8 /u01/app/oracle11/oradata/chendb/cjctbs03.dbf
CJCTBS 9 /u01/app/oracle11/oradata/chendb/cjctbs05.dbf
9 rows selected.
方法四：ALTER DATABASE MOVE DATAFILE ‘原路径’ TO ‘新路径’;
12C开始，简化了修改数据文件路径的方法，减少了修改路径过程中造成的数据不可用问题。
例如：
ALTER DATABASE MOVE DATAFILE '/home/oracle/cjctbs06.dbf' TO '/u01/app/oracle11/oradata/chendb/cjctbs06.dbf';
【版权声明】本文为华为云社区用户原创内容，转载时必须标注文章的来源（华为云社区）、文章链接、文章作者等基本信息， 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱：
cloudbbs@huaweicloud.com
- 点赞
- 收藏
- 关注作者
评论（0）