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迁移更简单，也会数据库的影响更小了，提高了数据库的强壮性，进而提升了业务的连续性。