oracle 修改数据文件路径(四种方式)

IT小Chen 2021-04-13

5397

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开始，简化了修改数据文件路径的方法，减少了修改路径过程中造成的数据不可用问题。

http://blog.itpub.net/29785807/viewspace-2565008/

例如：

ALTER DATABASE MOVE DATAFILE '/home/oracle/cjctbs06.dbf' TO '/u01/app/oracle11/oradata/chendb/cjctbs06.dbf';

2020-12-07 12:53 chenjuchao

oracle

文章转载自IT小Chen，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

oracle 修改数据文件路径(四种方式)

评论