Rename or Move a datafile In Oracle 19c RAC-20220117
Rename or Move a datafile In Oracle 19c RAC-20220117
導讀：
1.Oracle data files的位置在Oracle資料庫中並不是固定不變，它是可以遷移。遷移的方法如2和3兩種
2.Oracle 12c之前data files遷移是需要offline，遷移時無法讀取和更新data files中的資料。
3.Oracle 12c之後data files遷移是online進行，遷移時data files是可以提供讀取和更新的。
Oracle 19c rac 如何遷移data files的位置？
1.版本 19.9 ru 和 歸檔模式
SQL> select * from v$version; BANNER -------------------------------------------------------------------------------- BANNER_FULL -------------------------------------------------------------------------------- BANNER_LEGACY -------------------------------------------------------------------------------- CON_ID ---------- Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.9.0.0.0 Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production 0 BANNER -------------------------------------------------------------------------------- BANNER_FULL -------------------------------------------------------------------------------- BANNER_LEGACY -------------------------------------------------------------------------------- CON_ID ----------
2.建立測試使用者，表空間，datafile
create tablespace doudou datafile '+data/racdb/datafile/doudou01.dbf' size 100m; create user doudou identified by oracle default tablespace doudou; grant connect, resource,dba to doudou; create table doudou.doudou_test as select * from dba_objects; conn doudou/oracle SQL> select count(*) from doudou_test; COUNT(*) ---------- 72563
2.1 透過dba_data_files檢視檢視doudou01.dbf資料檔案當前位置
set line 160 col file_name for a60 col tablespace_name for a40 select tablespace_name,file_name from dba_data_files order by 1; TABLESPACE_NAME FILE_NAME ---------------------------------------- ------------------------------------------------------------ DOUDOU +DATA/racdb/datafile/doudou01.dbf SYSAUX +DATA/RACDB/DATAFILE/sysaux.259.1092523797 SYSTEM +DATA/RACDB/DATAFILE/system.258.1092523721 UNDOTBS1 +DATA/RACDB/DATAFILE/undotbs1.260.1092523823 UNDOTBS2 +DATA/RACDB/DATAFILE/undotbs2.266.1092525073 UNDOTBS3 +DATA/RACDB/DATAFILE/undotbs3.267.1092525073 USERS +DATA/RACDB/DATAFILE/users.261.1092523823
2.2 透過asmcmd命令檢視當前doudou01.dbf資料檔案當前位置
ASMCMD> cd +data/racdb/datafile ASMCMD> ls -l Type Redund Striped Time Sys Name DATAFILE UNPROT COARSE MAR 17 06:00:00 Y DOUDOU.274.1099551153 DATAFILE UNPROT COARSE MAR 17 06:00:00 Y SYSAUX.259.1092523797 DATAFILE UNPROT COARSE MAR 17 05:00:00 Y SYSTEM.258.1092523721 DATAFILE UNPROT COARSE MAR 17 05:00:00 Y UNDOTBS1.260.1092523823 DATAFILE UNPROT COARSE MAR 17 05:00:00 Y UNDOTBS2.266.1092525073 DATAFILE UNPROT COARSE MAR 17 05:00:00 Y UNDOTBS3.267.1092525073 DATAFILE UNPROT COARSE MAR 17 05:00:00 Y USERS.261.1092523823 DATAFILE UNPROT COARSE MAR 17 06:00:00 N doudou01.dbf => +DATA/RACDB/DATAFILE/DOUDOU.274.1099551153 ASMCMD> ls -l DOUDOU.274.1099551153 Type Redund Striped Time Sys Name DATAFILE UNPROT COARSE MAR 17 06:00:00 Y DOUDOU.274.1099551153 # asmcmd命令檢視我們指定的doudou01.dbf僅是一個指標，doudou01.dbf會自動生成一個類似這樣的DOUDOU.274.1099551153檔案，這個檔案才是真正的資料檔案。
2.3 建立一個新目錄
ASMCMD> ls -l Type Redund Striped Time Sys Name Y DATAFILE/ N TESTFILE/ <=create new # 測試環境已具備
3.將doduou01.dbf從datafile目錄遷移testfile目錄的方法
3.1 12c以前的方法：rename and relocate offline data files
優缺點：datafile offline後，無法檢視datafile儲存的資料 具體操作如下： alter tablespace doudou offline normal; ASMCMD> cp '+DATA/RACDB/DATAFILE/doudou01.dbf' to '+DATA/RACDB/TESTFILE/doudou01.dbf' ; alter tablespace doudou rename datafile '+DATA/RACDB/DATAFILE/doudou01.dbf' to '+DATA/RACDB/TESTFILE/doudou01.dbf' ; alter tablespace doudou online; ASMCMD> rm +data/racdb/datafile/doudou01.dbf
3.1.1 檢視datafile online時，datafile狀態
set line 160 col file_name for a60 col tablespace_name for a40 select tablespace_name,file_name,online_status from dba_data_files order by 1; TABLESPACE_NAME FILE_NAME ---------------------------------------- ------------------------------------------------------------ DOUDOU +DATA/racdb/datafile/doudou01.dbf SYSAUX +DATA/RACDB/DATAFILE/sysaux.259.1092523797 SYSTEM +DATA/RACDB/DATAFILE/system.258.1092523721 UNDOTBS1 +DATA/RACDB/DATAFILE/undotbs1.260.1092523823 UNDOTBS2 +DATA/RACDB/DATAFILE/undotbs2.266.1092525073 UNDOTBS3 +DATA/RACDB/DATAFILE/undotbs3.267.1092525073 USERS +DATA/RACDB/DATAFILE/users.261.1092523823
3.1.2 datafile offline
SQL> alter database datafile '+DATA/racdb/datafile/doudou01.dbf' offline; Database altered.
3.1.3 檢視datafile offline時，datafile狀態
SQL> select file_name,tablespace_name,online_status from dba_data_files where tablespace_name='DOUDOU'; FILE_NAME TABLESPACE_NAME ONLINE_STATUS ------------------------------------------------------------ ---------------------------------------- -------------- +DATA/racdb/datafile/doudou01.dbf DOUDOU RECOVER # datafile需要recover
3.1.4 資料庫為了保證資料的一致性，故被offline表空間裡的資料是無法讀取或更新的。並物理檔案遷移
select count(*) from doudou_test * ERROR at line 1: ORA-00376: file 8 cannot be read at this time ORA-01110: data file 8: '+DATA/racdb/datafile/doudou01.dbf' # datafile offline時，物理檔案是可以被遷移的。把doudou01.dbf檔案cp到新的路徑下 ASMCMD> cp +data/racdb/datafile/doudou01.dbf +data/racdb/testfile/doudou01.dbf copying +data/racdb/datafile/doudou01.dbf -> +data/racdb/testfile/doudou01.dbf # 當doudou01.dbf當位置 ASMCMD> ls -l +data/racdb/testfile/doudou01.dbf Type Redund Striped Time Sys Name DATAFILE UNPROT COARSE MAR 17 07:00:00 N doudou01.dbf => +DATA/ASM/DATAFILE/doudou01.dbf.273.1099554317 ASMCMD> ls -l +data/racdb/testfile Type Redund Striped Time Sys Name DATAFILE UNPROT COARSE MAR 17 07:00:00 N doudou01.dbf => +DATA/ASM/DATAFILE/doudou01.dbf.273.1099554317 ASMCMD> ls -l +data/racdb/datafile/ Type Redund Striped Time Sys Name DATAFILE UNPROT COARSE MAR 17 07:00:00 Y DOUDOU.274.1099551153 DATAFILE UNPROT COARSE MAR 17 07:00:00 Y SYSAUX.259.1092523797 DATAFILE UNPROT COARSE MAR 17 05:00:00 Y SYSTEM.258.1092523721 DATAFILE UNPROT COARSE MAR 17 05:00:00 Y UNDOTBS1.260.1092523823 DATAFILE UNPROT COARSE MAR 17 05:00:00 Y UNDOTBS2.266.1092525073 DATAFILE UNPROT COARSE MAR 17 05:00:00 Y UNDOTBS3.267.1092525073 DATAFILE UNPROT COARSE MAR 17 05:00:00 Y USERS.261.1092523823 DATAFILE UNPROT COARSE MAR 17 07:00:00 N doudou01.dbf => +DATA/RACDB/DATAFILE/DOUDOU.274.1099551153 # testfile 目錄下的doudou01.dbf是doudou01.dbf.273.1099554317 # dataile 目錄下的doudou01.dbf是DOUDOU.274.1099551153
3.1.5 rename datafile for database open
SQL> alter database rename file '+data/racdb/datafile/doudou01.dbf' to '+data/racdb/testfile/doudou01.dbf' ; Database altered.
3.1.6 datafile recover and datafile online
SQL> select file_name,tablespace_name,online_status from dba_data_files where tablespace_name='DOUDOU'; FILE_NAME TABLESPACE_NAME ONLINE_STATUS ------------------------------------------------------------ ---------------------------------------- -------------- +DATA/racdb/testfile/doudou01.dbf DOUDOU RECOVER SQL> recover datafile 8; Media recovery complete. SQL> alter database datafile 8 online; Database altered. SQL> select file_name,tablespace_name,online_status from dba_data_files where tablespace_name='DOUDOU'; FILE_NAME TABLESPACE_NAME ONLINE_STATUS ------------------------------------------------------------ ---------------------------------------- -------------- +DATA/racdb/testfile/doudou01.dbf DOUDOU ONLINE SQL> select count(*) from doudou_test; COUNT(*) ---------- 72563
3.1.7 delete old datafile
ASMCMD> rm +data/racdb/datafile/doudou01.dbf ASMCMD> ls -l Type Redund Striped Time Sys Name DATAFILE UNPROT COARSE MAR 17 07:00:00 Y SYSAUX.259.1092523797 DATAFILE UNPROT COARSE MAR 17 05:00:00 Y SYSTEM.258.1092523721 DATAFILE UNPROT COARSE MAR 17 05:00:00 Y UNDOTBS1.260.1092523823 DATAFILE UNPROT COARSE MAR 17 05:00:00 Y UNDOTBS2.266.1092525073 DATAFILE UNPROT COARSE MAR 17 05:00:00 Y UNDOTBS3.267.1092525073 DATAFILE UNPROT COARSE MAR 17 05:00:00 Y USERS.261.1092523823
3.1.8 datafile遷移已完成,檢視datafile位置
doudou01.dbf檔案真實的位置是在在asm中 ASMCMD> ls -l +data/racdb/testfile Type Redund Striped Time Sys Name DATAFILE UNPROT COARSE MAR 17 08:00:00 N doudou01.dbf => +DATA/ASM/DATAFILE/doudou01.dbf.273.1099554317 ASMCMD> ls -l +DATA/ASM/DATAFILE/doudou01.dbf.273.1099554317 Type Redund Striped Time Sys Name DATAFILE UNPROT COARSE MAR 17 08:00:00 Y doudou01.dbf.273.1099554317
3.2 12c以後的方法：alter database move datafile
優缺點：datafile無需offline
3.2.1 檢視datafile當前位置與狀態
select tablespace_name,file_name,online_status from dba_data_files order by 1; TABLESPACE_NAME FILE_NAME ONLINE_STATUS ---------------------------------------- ------------------------------------------------------------ -------------- DOUDOU +DATA/racdb/testfile/doudou01.dbf ONLINE SYSAUX +DATA/RACDB/DATAFILE/sysaux.259.1092523797 ONLINE SYSTEM +DATA/RACDB/DATAFILE/system.258.1092523721 SYSTEM UNDOTBS1 +DATA/RACDB/DATAFILE/undotbs1.260.1092523823 ONLINE UNDOTBS2 +DATA/RACDB/DATAFILE/undotbs2.266.1092525073 ONLINE UNDOTBS3 +DATA/RACDB/DATAFILE/undotbs3.267.1092525073 ONLINE USERS +DATA/RACDB/DATAFILE/users.261.1092523823 ONLINE
3.2.2 直接move datafile
SQL> alter database move datafile '+DATA/racdb/testfile/doudou01.dbf' to '+DATA/RACDB/DATAFILE/doudou01.dbf'; Database altered.
3.2.3 datafile遷移已完成，檢視datafile位置
SQL> select tablespace_name,file_name,online_status from dba_data_files order by 1; TABLESPACE_NAME FILE_NAME ONLINE_STATUS ---------------------------------------- ------------------------------------------------------------ -------------- DOUDOU +DATA/RACDB/DATAFILE/doudou01.dbf ONLINE SYSAUX +DATA/RACDB/DATAFILE/sysaux.259.1092523797 ONLINE SYSTEM +DATA/RACDB/DATAFILE/system.258.1092523721 SYSTEM UNDOTBS1 +DATA/RACDB/DATAFILE/undotbs1.260.1092523823 ONLINE UNDOTBS2 +DATA/RACDB/DATAFILE/undotbs2.266.1092525073 ONLINE UNDOTBS3 +DATA/RACDB/DATAFILE/undotbs3.267.1092525073 ONLINE USERS +DATA/RACDB/DATAFILE/users.261.1092523823 ONLINE 7 rows selected. ASMCMD> ls -l doudou01.dbf Type Redund Striped Time Sys Name DATAFILE UNPROT COARSE JAN 16 22:00:00 N doudou01.dbf => +DATA/RACDB/DATAFILE/DOUDOU.273.1094164595 # datafile遷移已完成
總結：
12c之後datafile遷移更簡單，也會資料庫的影響更小了，提高了資料庫的強壯性，進而提升了業務的連續性。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26442936/viewspace-2879018/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- ocp 19c考題，科目082考試題(15)-move datafile to
- Sqlserver移動檔案路徑move datafile的三種方法SQLServer
- oracle 分割槽表move和包含分割槽表的lob moveOracle
- oracle 線上rename資料檔案Oracle
- Oracle move和shrink釋放高水位空間Oracle
- Oracle 19C EMOracle
- oracle 19c dataguard silent install (oracle 19c dataguard 靜默安裝)Oracle
- Oracle 12.2 新特性: Online PDB relocate (PDB hot move)Oracle
- Oracle 19c Concepts(01)：Introduction to Oracle DatabaseOracleDatabase
- Oracle 19c Concepts(13)：Oracle Database InstanceOracleDatabase
- Oracle 19c Broker配置Oracle
- Oracle資料泵(Oracle Data Pump) 19cOracle
- Oracle：Oracle RAC 11.2.0.4 升級為 19cOracle
- Oracle 19c Concepts(10)：TransactionsOracle
- Oracle 19c RPM安裝Oracle
- oracle 19c 初體驗Oracle
- Oracle 19c中的TomcatOracleTomcat
- Oracle 19c Concepts(07)：SQLOracleSQL
- Oracle 19c的安裝Oracle
- oracle 19c pdb遷移Oracle
- Oracle 19c Database Management ToolsOracleDatabase
- Oracle 19c Concepts(00)：Changes in This Release for Oracle Database ConceptsOracleDatabase
- Oracle 19c Concepts(16)：Application and Oracle Net Services ArchitectureOracleAPP
- Overview of Oracle Flex ASM In Oracle 19c RAC-20220111ViewOracleFlexASM
- Oracle 19c - 手動升級 Oracle 12.x, 18c CDB 到 Oracle 19c (19.x)Oracle
- Oracle 19c Concepts(14)：Memory ArchitectureOracle
- Oracle 19c Concepts(15)：Process ArchitectureOracle
- Oracle 19c Concepts(05)：Data IntegrityOracle
- Sqlcl 連線Oracle DataBase 19cSQLOracleDatabase
- Oracle Linux 7.1 silent install 19cOracleLinux
- oracle 19c CDB vs pdb 建立Oracle
- Oracle 19c RAC INS-40724Oracle
- oracle 19C 靜默安裝Oracle
- Docker中安裝Oracle 19cDockerOracle
- 1 Oracle Database 19c 新特性OracleDatabase
- Oracle 19c 安裝嚐鮮Oracle
- LGnn 程式介紹（oracle 19c）GNNOracle
- 【19c】Oracle 19c 和 20c 的新特性解密Oracle解密