線上移動資料檔案
在ORACLE12C之前，如果需要將資料檔案（不包括臨時檔案）移動到其他位置，需要在offline下操作；
在12C中則可以進行ONLINE資料檔案移動。
SQL> select file#,name from v$datafile order by con_id;
FILE# NAME
---------- ------------------------------------------------------------
1 /u02/app/oracle/oradata/ORCL12C/system01.dbf
3 /u02/app/oracle/oradata/ORCL12C/sysaux01.dbf
4 /u02/app/oracle/oradata/ORCL12C/undotbs01.dbf
6 /u02/app/oracle/oradata/ORCL12C/users01.dbf
5 /u02/app/oracle/oradata/ORCL12C/pdbseed/system01.dbf
7 /u02/app/oracle/oradata/ORCL12C/pdbseed/sysaux01.dbf
8 /u02/app/oracle/oradata/ORCL12C/PDBCN/system01.dbf
10 /u02/app/oracle/oradata/ORCL12C/PDBCN/PDBCN_users01.dbf
9 /u02/app/oracle/oradata/ORCL12C/PDBCN/sysaux01.dbf
11 /u02/app/oracle/oradata/ORCL12C/PDBUS/system01.dbf
12 /u02/app/oracle/oradata/ORCL12C/PDBUS/sysaux01.dbf
14 /u02/app/oracle/admin/ORCL12C/dpdump/test.dbf
13 /u02/app/oracle/admin/ORCL12C/dpdump/example01.dbf
15 /u02/app/oracle/admin/ORCL12C/dpdump/users01.dbf
13，14，15這三個資料檔案是從11G資料庫上impdp進來的，需要改變位置。
alter database move datafile 14 to '/u02/app/oracle/oradata/ORCL12C/PDBUS/pdbustest.dbf';
alter database move datafile 15 to '/u02/app/oracle/oradata/ORCL12C/PDBUS/pdbususers01.dbf';
alter database move datafile 13 to '/u02/app/oracle/oradata/ORCL12C/PDBUS/pdbusexample01.dbf'
*
ERROR at line 1:
ORA-01516: nonexistent log file, data file, or temporary file "13"
SQL> alter database move datafile 14 to '/u02/app/oracle/oradata/ORCL12C/PDBUS/pdbustest.dbf'
*
ERROR at line 1:
ORA-01516: nonexistent log file, data file, or temporary file "14"
SQL>
alter database move datafile 15 to '/u02/app/oracle/oradata/ORCL12C/PDBUS/pdbususers01.dbf'
*
ERROR at line 1:
ORA-01516: nonexistent log file, data file, or temporary file "15"
出現上述的錯誤是因為當前container=root$cdb;
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> alter session set container=pdbus;
Session altered.
SQL> alter database move datafile 13 to '/u02/app/oracle/oradata/ORCL12C/PDBUS/pdbusexample01.dbf';
alter database move datafile 14 to '/u02/app/oracle/oradata/ORCL12C/PDBUS/pdbustest.dbf';
alter database move datafile 15 to '/u02/app/oracle/oradata/ORCL12C/PDBUS/pdbususers01.dbf';
Database altered.
SQL>
Database altered.
SQL>
Database altered.
SQL> select file#,name from v$datafile order by con_id; SQL> select file#,name from v$datafile order by con_id;
FILE# NAME
---------- ------------------------------------------------------------
1 /u02/app/oracle/oradata/ORCL12C/system01.dbf
3 /u02/app/oracle/oradata/ORCL12C/sysaux01.dbf
4 /u02/app/oracle/oradata/ORCL12C/undotbs01.dbf
6 /u02/app/oracle/oradata/ORCL12C/users01.dbf
5 /u02/app/oracle/oradata/ORCL12C/pdbseed/system01.dbf
7 /u02/app/oracle/oradata/ORCL12C/pdbseed/sysaux01.dbf
8 /u02/app/oracle/oradata/ORCL12C/PDBCN/system01.dbf
10 /u02/app/oracle/oradata/ORCL12C/PDBCN/PDBCN_users01.dbf
9 /u02/app/oracle/oradata/ORCL12C/PDBCN/sysaux01.dbf
11 /u02/app/oracle/oradata/ORCL12C/PDBUS/system01.dbf
12 /u02/app/oracle/oradata/ORCL12C/PDBUS/sysaux01.dbf
14 /u02/app/oracle/oradata/ORCL12C/PDBUS/pdbustest.dbf
13 /u02/app/oracle/oradata/ORCL12C/PDBUS/pdbusexample01.dbf
15 /u02/app/oracle/oradata/ORCL12C/PDBUS/pdbususers01.dbf
14 rows selected. 12 /u02/app/oracle/oradata/ORCL12C/PDBUS/sysaux01.dbf
------參考文章
Prior to Oracle 12c, moving datafiles has always been an offline
task. There were certain techniques you could employ to minimize that
downtime, but you couldn't remove it completely. Oracle 12c includes an
enhancement to the ALTER DATABASE command to allow datafiles to be moved online.
Related articles.
Basic Syntax
The text description of the syntax is shown below, but the syntax diagrams and a full description of the ALTER DATABASE command is available in the documentation .
ALTER DATABASE MOVE DATAFILE ( 'filename' | 'ASM_filename' | file_number ) [ TO ( 'filename' | 'ASM_filename' ) ] [ REUSE ] [ KEEP ]
The source file can be specified using the file number or name, while the destination file must be specified by the file name. The REUSE keyword indicates the new file should be created even if it already exists. The KEEP keyword indicates the original copy of the datafile should be retained.
When the source file is an OMF file the KEEP option can not be used. If the destination file is an OMF file, the TO clause can be omitted and the file will be created with an OMF name in the DB_CREATE_FILE_DEST location.
The file number can be queried from the V$DATAFILE and DBA_DATA_FILES views.
SQL> CONN / AS SYSDBA
SQL> SET LINESIZE 100
SQL> COLUMN name FORMAT A70
SQL> SELECT file#, name FROM v$datafile WHERE con_id = 1 ORDER BY file#;
FILE# NAME
---------- ----------------------------------------------------------------------
1 /u01/app/oracle/oradata/cdb1/system01.dbf
3 /u01/app/oracle/oradata/cdb1/sysaux01.dbf
4 /u01/app/oracle/oradata/cdb1/undotbs01.dbf
6 /u01/app/oracle/oradata/cdb1/users01.dbf
SQL>
SQL> COLUMN file_name FORMAT A70
SELECT file_id, file_name FROM dba_data_files ORDER BY file_id;
FILE_ID FILE_NAME
---------- ----------------------------------------------------------------------
1 /u01/app/oracle/oradata/cdb1/system01.dbf
3 /u01/app/oracle/oradata/cdb1/sysaux01.dbf
4 /u01/app/oracle/oradata/cdb1/undotbs01.dbf
6 /u01/app/oracle/oradata/cdb1/users01.dbf
SQL>
Examples
The following example shows a basic file move, specifying both source and destination by name. Notice the original file is no longer present.
SQL> ALTER DATABASE MOVE DATAFILE '/u01/app/oracle/oradata/cdb1/system01.dbf' TO '/tmp/system01.dbf'; Database altered. SQL> SQL> SELECT file_id, file_name FROM dba_data_files WHERE file_id = 1; FILE_ID FILE_NAME ---------- ---------------------------------------------------------------------- 1 /tmp/system01.dbf SQL> SQL> HOST ls -al /u01/app/oracle/oradata/cdb1/system01.dbf ls: cannot access /u01/app/oracle/oradata/cdb1/system01.dbf: No such file or directory SQL> HOST ls -al /tmp/system01.dbf -rw-r-----. 1 oracle oinstall 838868992 Oct 8 22:48 /tmp/system01.dbf SQL>
The next example uses the file number for the source file and keeps the original file.
SQL> ALTER DATABASE MOVE DATAFILE 1 TO '/u01/app/oracle/oradata/cdb1/system01.dbf' KEEP; Database altered. SQL> SQL> SELECT file_id, file_name FROM dba_data_files WHERE file_id = 1; FILE_ID FILE_NAME ---------- ---------------------------------------------------------------------- 1 /u01/app/oracle/oradata/cdb1/system01.dbf SQL> SQL> HOST ls -al /u01/app/oracle/oradata/cdb1/system01.dbf -rw-r-----. 1 oracle oinstall 838868992 Oct 8 22:48 /u01/app/oracle/oradata/cdb1/system01.dbf SQL> HOST ls -al /tmp/system01.dbf -rw-r-----. 1 oracle oinstall 838868992 Oct 8 22:49 /tmp/system01.dbf SQL>
The next example shows the use of OMF.
SQL> ALTER SYSTEM SET db_create_file_dest='/u01/app/oracle/oradata/cdb1'; System altered. SQL> ALTER DATABASE MOVE DATAFILE '/u01/app/oracle/oradata/cdb1/system01.dbf'; Database altered. SQL> SQL> SELECT file_id, file_name FROM dba_data_files WHERE file_id = 1; FILE_ID FILE_NAME ---------- ---------------------------------------------------------------------- 1 /u01/app/oracle/oradata/cdb1/CDB1/datafile/o1_mf_system_958zo3ll_.dbf SQL>
The final example attempts to use the KEEP option, where the source file in an OMF file. Notice how the KEEP option is ignored.
SQL> ALTER DATABASE MOVE DATAFILE 1 To '/u01/app/oracle/oradata/cdb1/system01.dbf' KEEP; Database altered. SQL> SQL> SELECT file_id, file_name FROM dba_data_files WHERE file_id = 1; FILE_ID FILE_NAME ---------- ---------------------------------------------------------------------- 1 /u01/app/oracle/oradata/cdb1/system01.dbf SQL> SQL> host ls -al /u01/app/oracle/oradata/cdb1/CDB1/datafile/o1_mf_system_958zo3ll_.dbf ls: cannot access /u01/app/oracle/oradata/cdb1/CDB1/datafile/o1_mf_system_958zo3ll_.dbf: No such file or directory SQL>
Pluggable Database (PDB)
The container database (CDB) can not move files that belong to a pluggable database. The following query displays all the datafiles for the CDB and the PDBs.
SQL> SELECT file#, name FROM v$datafile ORDER BY file#;
FILE# NAME
---------- ----------------------------------------------------------------------
1 /u01/app/oracle/oradata/cdb1/system01.dbf
3 /u01/app/oracle/oradata/cdb1/sysaux01.dbf
4 /u01/app/oracle/oradata/cdb1/undotbs01.dbf
5 /u01/app/oracle/oradata/cdb1/pdbseed/system01.dbf
6 /u01/app/oracle/oradata/cdb1/users01.dbf
7 /u01/app/oracle/oradata/cdb1/pdbseed/sysaux01.dbf
8 /u01/app/oracle/oradata/cdb1/pdb1/system01.dbf
9 /u01/app/oracle/oradata/cdb1/pdb1/sysaux01.dbf
10 /u01/app/oracle/oradata/cdb1/pdb1/pdb1_users01.dbf
29 /u01/app/oracle/oradata/pdb2/system01.dbf
30 /u01/app/oracle/oradata/pdb2/sysaux01.dbf
31 /u01/app/oracle/oradata/pdb2/pdb2_users01.dbf
SQL>
If we try to move a datafile belonging to a PDB an error is returned.
SQL> ALTER DATABASE MOVE DATAFILE '/u01/app/oracle/oradata/pdb2/system01.dbf' TO '/tmp/system01.dbf' REUSE; ALTER DATABASE MOVE DATAFILE '/u01/app/oracle/oradata/pdb2/system01.dbf' TO '/tmp/system01.dbf' REUSE * ERROR at line 1: ORA-01516: nonexistent log file, data file, or temporary file "29" SQL>
If we switch to the PDB container, the datafile can be moved as normal.
SQL> ALTER SESSION SET container=pdb2; Session altered. SQL> ALTER DATABASE MOVE DATAFILE '/u01/app/oracle/oradata/pdb2/system01.dbf' TO '/tmp/system01.dbf' REUSE; Database altered. SQL> SQL> SELECT file_id, file_name FROM dba_data_files WHERE file_id = 29; FILE_ID FILE_NAME ---------- ---------------------------------------------------------------------- 29 /tmp/system01.dbf SQL> SQL> ALTER DATABASE MOVE DATAFILE 29 TO '/u01/app/oracle/oradata/pdb2/system01.dbf' REUSE; Database altered. SQL> SQL> SELECT file_id, file_name FROM dba_data_files WHERE file_id = 29; FILE_ID FILE_NAME ---------- ---------------------------------------------------------------------- 29 /u01/app/oracle/oradata/pdb2/system01.dbf SQL>ALTER SESSION SET container=cdb1; SQL> ALTER SESSION SET container=CDB$ROOT; Session altered. SQL>
Tempfiles
Not surprisingly, the ALTER DATABASE MOVE DATAFILE syntax does not work for temporary files.
SQL> SELECT file_id, file_name FROM dba_temp_files; FILE_ID FILE_NAME ---------- ---------------------------------------------------------------------- 1 /u01/app/oracle/oradata/cdb1/temp01.dbf SQL> SQL> ALTER DATABASE MOVE DATAFILE '/u01/app/oracle/oradata/cdb1/temp01.dbf' TO '/tmp/temp01.dbf' REUSE; ALTER DATABASE MOVE DATAFILE '/u01/app/oracle/oradata/cdb1/temp01.dbf' TO '/tmp/temp01.dbf' REUSE * ERROR at line 1: ORA-01516: nonexistent log file, data file, or temporary file "/u01/app/oracle/oradata/cdb1/temp01.dbf" SQL>
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/21754115/viewspace-1063920/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20181031]12c 線上移動資料檔案.txt
- 12c pdb線上移動資料檔案或者重新命名資料檔案
- 12C關於CDB、PDB線上移動資料檔案、線上重新命名資料檔案的操作說明
- SQLServer移動資料檔案SQLServer
- oracle 線上rename資料檔案Oracle
- oracle 修改資料檔案位置（路徑）（移動）Oracle
- Oracle12C新特性-線上重新命名遷移資料檔案(一)Oracle
- 移動檔案
- 12c新特性，線上move資料檔案
- AJAX資料互動及檔案上傳功能
- SpringMVC(四)上傳檔案、json資料互動SpringMVCJSON
- PostgreSQL在不同的表空間移動資料檔案SQL
- python——批次移動檔案Python
- 達夢資料庫資料檔案遷移過程資料庫
- 利用offline datafile檔案方式遷移資料
- 織夢CMS(dedecms)的資料庫連線檔案_織夢連線資料庫檔案資料庫
- 在Linux中，如何建立、移動和刪除檔案和資料夾？Linux
- 批處理指令碼：遞迴移動資料夾內所有檔案指令碼遞迴
- python之批次移動檔案Python
- 【ASM】ASM資料檔案和OS檔案（FILESYSTEM）轉移方法總結ASM
- kkFileView檔案線上預覽View
- ZBlogPHP如何線上管理檔案？PHP
- mv 命令 – 移動或改名檔案
- 在 Linux 中如何移動檔案Linux
- python移動檔案指標seekPython指標
- 網站連線資料庫配置檔案網站資料庫
- 使用dbeaver 用csv 檔案進行資料遷移
- FastDFS檔案系統遷移和資料恢復AST資料恢復
- http不使用Form表單傳送檔案資料和非檔案資料(上傳篇)HTTPORM
- ORACLE RAC ASM資料檔案遷移OMF檔案報錯ORA-01276解決OracleASM
- 遷移資料庫的檔案到不同路徑(轉)資料庫
- Linux 中複製和移動檔案Linux
- 函式：file.move 移動檔案函式
- 橫向無檔案移動--SCshell使用
- Java讀取properties檔案連線資料庫Java資料庫
- Python將表格檔案中某些列的資料整體向上移動一行Python
- 線上資料遷移，數字化時代的必修課 —— 京東雲資料遷移實踐
- [20230224]改動資料檔案小技巧.txt
- 資料庫課程作業筆記 - 編寫資料庫遷移檔案資料庫筆記