oracle 修改資料檔案路徑(四種方式)
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
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29785807/viewspace-2739953/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 修改Oracle資料檔名及資料檔案存放路徑Oracle
- oracle11g修改資料檔案路徑Oracle
- oracle 修改資料檔案位置（路徑）（移動）Oracle
- 【Oracle】如何修改資料檔案和日誌檔案的路徑Oracle
- oracle11g修改控制檔案路徑Oracle
- CentOS修改Mariadb資料庫檔案儲存路徑CentOS資料庫
- win10 onenote如何修改檔案路徑_win10 onenote怎麼修改檔案路徑Win10
- oracle升級後資料檔案路徑變為大寫Oracle
- PbootCMS後臺檔案修改路徑位置boot
- NodeJs 的幾種檔案路徑NodeJS
- 更改資料檔案路徑的方法
- 監聽檔案修改的四種方法
- oracle 資料庫lsnrctl監聽的日誌路徑和trace檔案Oracle資料庫
- Spring Boot更改上下文路徑的四種方式Spring Boot
- 修改資料檔案的位置的兩種方法
- HTML中相對路徑的方式引用檔案HTML
- Spring boot 讀取properties檔案的四種方式Spring Boot
- Python科研武器庫 - 檔案/路徑操作 - 判斷檔案或資料夾Python
- 修改chrome使用者資料的路徑Chrome
- Sqlserver資料庫儲存路徑的修改SQLServer資料庫
- 修改NLS_DATE_FORMAT的四種方式ORM
- nodejs遞迴資料夾獲取所有檔案路徑NodeJS遞迴
- 【kingsql分享】使用BBED修改Oracle資料檔案頭推進SCNSQLOracle
- Oracle 資料檔案回收Oracle
- [重慶思莊每日技術分享]-ORACLE DG物理備庫使用別名資料檔案改變路徑到OMF路徑Oracle
- 桌面檔案在c盤哪個資料夾 c盤桌面檔案路徑
- Java中獲取JAR檔案中資源路徑的三種方法JavaJAR
- css檔案與資源路徑相關CSS
- Sqlserver移動檔案路徑move datafile的三種方法SQLServer
- win10 怎麼修改我的桌面路徑_win10如何更改桌面檔案的路徑Win10
- Linux檔案的路徑定位-相對路徑和絕對路徑Linux
- 整理post提交資料的四種解析方式
- gcc g++ 新增標頭檔案路徑和庫檔案路徑的方法GC
- 修改桌面路徑
- 回顧Node檔案路徑
- 查詢檔案位置路徑
- 017-html檔案路徑HTML
- 檔案路徑是否正確