---------------------------by acdante-------------------------------非系统表空间数据文件
前提
数据库版本：Oracle 11.2.0.4.0
非系统表空间-à适用方法1、方法2、方法3
This is not a System tablespace datafile.
系统表空间à适用方法4
重要事项：做好数据备份，并确认数据备份可用，再进行操作
另：在Oracle 12c版本中，移动数据文件已经可以在线移动任何一个正在使用的数据文件，而无需将其OFFLINE。包括SYSEM表空间中的数据文件也可以在线移动。具体信息可参见：
Document ID 2194252.1
实验环境
系统版本
|
[root@acdante-1 ~]# lsb_release -a LSB Version: :base-4.0-amd64:base-4.0-noarch:core-4.0-amd64:core-4.0-noarch:graphics-4.0-amd64:graphics-4.0-noarch:printing-4.0-amd64:printing-4.0-noarch Distributor ID: RedHatEnterpriseServer Description: Red Hat Enterprise Linux Server release 6.5 (Santiago) Release: 6.5 Codename: Santiago |
数据库版本：
|
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
|
已开启归档
|
SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 86 Next log sequence to archive 87 Current log sequence 87 |
模拟情景
模拟将数据文件误创建在本地磁盘上：
|
目前数据文件，系统表空间和用户表空间数据文件都在ASM磁盘组ORADATA1上。 SQL> select file_name, file_id from dba_data_files;
FILE_NAME FILE_ID -------------------------------------------------- ---------- +ORADATA1/racdb/datafile/system.259.952265189 1 +ORADATA1/racdb/datafile/sysaux.260.952265199 2 +ORADATA1/racdb/datafile/undotbs1.261.952265207 3 +ORADATA1/racdb/datafile/undotbs2.263.952265225 4 +ORADATA1/racdb/datafile/undotbs3.264.952265229 5 +ORADATA1/racdb/datafile/users.265.952265231 6
6 rows selected.
创建实验表空间，将数据文件存放在节点1本地 SQL> create tablespace test01 datafile '/u01/app/oracle/test01.dbf' size 100m extent management local uniform segment space management auto;
Tablespace created.
SQL> select file_name, file_id from dba_data_files;
FILE_NAME FILE_ID -------------------------------------------------- ---------- +ORADATA1/racdb/datafile/system.259.952265189 1 +ORADATA1/racdb/datafile/sysaux.260.952265199 2 +ORADATA1/racdb/datafile/undotbs1.261.952265207 3 +ORADATA1/racdb/datafile/undotbs2.263.952265225 4 +ORADATA1/racdb/datafile/undotbs3.264.952265229 5 +ORADATA1/racdb/datafile/users.265.952265231 6 /u01/app/oracle/test01.dbf 7
7 rows selected.
|
方法一、ASMCMD CP
使用ASMCMD CP命令进行非系统表空间数据文件的在线迁移，无需停掉整个数据库。但是相应的表空间会被OFFLINE，再进行操作。故在实际操作中，需要谨慎评估相关表空间所承载的业务，以及相应的业务量，表空间OFFLINE后，将无法访问相应数据，需要评估业务影响范围，确认好，需要暂停哪些业务，或者是否有必要进行停库操作。
1.将相应数据文件OFFLINE
|
可看到目前datafile7数据文件位于本地磁盘，那么就会出现其他节点无法访问的情况。 SQL> select file_name, file_id from dba_data_files;
FILE_NAME FILE_ID -------------------------------------------------- ---------- +ORADATA1/racdb/datafile/system.259.952265189 1 +ORADATA1/racdb/datafile/sysaux.260.952265199 2 +ORADATA1/racdb/datafile/undotbs1.261.952265207 3 +ORADATA1/racdb/datafile/undotbs2.263.952265225 4 +ORADATA1/racdb/datafile/undotbs3.264.952265229 5 +ORADATA1/racdb/datafile/users.265.952265231 6 /u01/app/oracle/test01.dbf 7
7 rows selected. SQL> select tablespace_name,file_name,file_id,online_status from dba_data_files where file_id=7;
TABLESPACE_NAME FILE_NAME FILE_ID ONLINE_ ------------------------------ -------------------------------------------------- -------- TEST01 /u01/app/oracle/test01.dbf 7 ONLINE
OFFLINE datafie7
SQL> alter system switch logfile; System altered. SQL> alter database datafile 7 offline; Database altered. SQL> select tablespace_name,file_name,file_id,online_status from dba_data_files where file_id=7; TABLESPACE_NAME FILE_NAME FILE_ID ONLINE_ ------------------------------ ---------------------------------------------- TEST01 /u01/app/oracle/test01.dbf 7 RECOVER
|
2.ASMCMD CP move datafile
|
[grid@acdante-1:/home/grid]$asmcmd ASMCMD> cp /u01/app/oracle/test01.dbf +ORADATA1/RACDB/DATAFILE/test01.dbf copying /u01/app/oracle/test01.dbf -> +ORADATA1/RACDB/DATAFILE/test01.dbf ASMCMD>cd +ORADATA1/RACDB/DATAFILE ASMCMD> ls -lt Type Redund Striped Time Sys Name N test01.dbf => +ORADATA1/ASM/DATAFILE/test01.dbf.271.957783867 DATAFILE UNPROT COARSE OCT 19 10:00:00 Y USERS.265.952265231 DATAFILE UNPROT COARSE OCT 19 10:00:00 Y UNDOTBS3.264.952265229 DATAFILE UNPROT COARSE OCT 19 10:00:00 Y UNDOTBS2.263.952265225 DATAFILE UNPROT COARSE OCT 19 10:00:00 Y UNDOTBS1.261.952265207 DATAFILE UNPROT COARSE OCT 19 10:00:00 Y SYSTEM.259.952265189 DATAFILE UNPROT COARSE OCT 19 10:00:00 Y SYSAUX.260.952265199 |
3.RENAME DATAFILE
|
SQL> alter database rename file '/u01/app/oracle/test01.dbf' to '+ORADATA1/RACDB/DATAFILE/test01.dbf'; Database altered. SQL> |
4.RECOVER DATAFILE
|
SQL> alter database recover datafile 7;
Database altered. SQL> select tablespace_name,file_name,file_id,online_status from dba_data_files where file_id=7;
TABLESPACE_NAME FILE_NAME FILE_ID ONLINE_ ------------------------------ -------------------------------------------------- ------- TEST01 +ORADATA1/racdb/datafile/test01.dbf 7 OFFLINE |
5.ONLINE DATAFILE
|
SQL> alter database datafile 7 online;
Database altered.
SQL> select tablespace_name,file_name,file_id,online_status from dba_data_files where file_id=7;
TABLESPACE_NAME FILE_NAME FILE_ID ONLINE_ ------------------------------ ---------------------------------------------- TEST01 +ORADATA1/racdb/datafile/test01.dbf 7 ONLINE
目前TEST01表空间对应的数据文件已经成功移动到ASM磁盘组，已经可以正常访问。 |
方法二、DBMS_FILE_TRANSFER
Oracle PL/SQL中包dbms_file_transfer也可以实现传输。其中的copy_file过程，可以实现将文件传输跨越ASM和文件系统，甚至可以跨版本进行表空间的传输和移动。
要求数据库版本：10.1.0.2 and later
使用dbms_file_transfer包前提需要指定文件目录，在Oracle中是通过directory对象实现的
由于之前将datafile7 移动到ASM磁盘组中，现在将datafile7移动回文件系统
1.创建源目录
source directory
|
SQL> create or replace directory SOURCE_DIR as '+ORADATA1/RACDB/DATAFILE/';
Directory created. |
2.创建目标目录
destination directory
|
SQL> create or replace directory DEST_DIR as '/u01/app/oracle';
Directory created. |
3.将相应数据文件OFFLINE
|
OFFLINE datafie7
SQL> alter system switch logfile; System altered. SQL> alter database datafile 7 offline; Database altered. SQL> select tablespace_name,file_name,file_id,online_status from dba_data_files where file_id=7;
TABLESPACE_NAME FILE_NAME FILE_ID ONLINE_ ------------------------------ -------------------------------------------------- -------- TEST01 +ORADATA1/racdb/datafile/test01.dbf 7 RECOVER |
4.DBMS_FILE_TRANSFER
|
SQL> begin 2 dbms_file_transfer.copy_file(source_directory_object => 'SOURCE_DIR',source_file_name => 'test01.dbf', 3 destination_directory_object => 'DEST_DIR',destination_file_name => 'test01.dbf'); 4 END; 5 /
PL/SQL procedure successfully completed.
SQL> |
文件系统中可看到创建了test01.dbf文件
|
[root@acdante-1 oracle]# ls -ltr total 102428 drwxr-xr-x 3 oracle oinstall 4096 Apr 9 2017 11.2 drwxrwxr-x 11 oracle oinstall 4096 Apr 9 2017 diag drwxr-xr-x 2 oracle oinstall 4096 Apr 9 2017 checkpoints drwxr-x--- 5 oracle oinstall 4096 Apr 9 2017 cfgtoollogs drwxr-x--- 3 oracle oinstall 4096 Aug 17 14:05 admin -rw-r----- 1 oracle asmadmin 104865792 Oct 19 13:17 test01.dbf |
5.RENAME DATAFILE
|
SQL> alter database rename file '+ORADATA1/RACDB/DATAFILE/test01.dbf' to '/u01/app/oracle/test01.dbf'; Database altered. SQL> |
6.RECOVER DATAFILE
|
SQL> alter database recover datafile '/u01/app/oracle/test01.dbf'; Database altered. SQL> select tablespace_name,file_name,file_id,online_status from dba_data_files where file_id=7;
TABLESPACE_NAME FILE_NAME FILE_ID ONLINE_ ------------------------------ -------------------------------------------------- -----TEST01 /u01/app/oracle/test01.dbf 7 OFFLINE SQL> |
7.ONLINE DATAFILE
|
SQL> alter database datafile 7 online;
Database altered.
SQL> select tablespace_name,file_name,file_id,online_status from dba_data_files where file_id=7;
TABLESPACE_NAME FILE_NAME FILE_ID ONLINE_ ------------------------------ -------------------------------------------------- -------- TEST01 /u01/app/oracle/test01.dbf 7 ONLINE 目前TEST01表空间对应的数据文件已经成功从ASM磁盘组移动回文件系统。 |
方法三、RMAN COPY
接下来使用RMAN再次将TEST01表空间对应的数据文件从File System移动至ASM磁盘组中。
1.将相应数据文件OFFLINE
|
SQL> select file_name, file_id from dba_data_files;
FILE_NAME FILE_ID -------------------------------------------------- ---------- +ORADATA1/racdb/datafile/system.259.952265189 1 +ORADATA1/racdb/datafile/sysaux.260.952265199 2 +ORADATA1/racdb/datafile/undotbs1.261.952265207 3 +ORADATA1/racdb/datafile/undotbs2.263.952265225 4 +ORADATA1/racdb/datafile/undotbs3.264.952265229 5 +ORADATA1/racdb/datafile/users.265.952265231 6 /u01/app/oracle/test01.dbf 7
7 rows selected. SQL> select tablespace_name,file_name,file_id,online_status from dba_data_files where file_id=7;
TABLESPACE_NAME FILE_NAME FILE_ID ONLINE_ ------------------------------ -------------------------------------------------- -------- TEST01 /u01/app/oracle/test01.dbf 7 ONLINE
OFFLINE datafie7
SQL> alter system switch logfile; System altered. SQL> alter database datafile 7 offline; Database altered. SQL> select tablespace_name,file_name,file_id,online_status from dba_data_files where file_id=7; TABLESPACE_NAME FILE_NAME FILE_ID ONLINE_ ------------------------------ ---------------------------------------------- TEST01 /u01/app/oracle/test01.dbf 7 RECOVER
|
2.RMAN COPY
|
[oracle@acdante-1:/home/oracle]$rman target / Recovery Manager: Release 11.2.0.4.0 - Production on Thu Oct 19 13:25:45 2017 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: RACDB (DBID=949881948) RMAN> copy datafile '/u01/app/oracle/test01.dbf' to '+ORADATA1';
Starting backup at 2017-10-19 13:28:33 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile copy input datafile file number=00007 name=/u01/app/oracle/test01.dbf output file name=+ORADATA1/racdb/datafile/test01.272.957792515 tag=TAG20171019T132833 RECID=2 STAMP=957792516 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03 Finished backup at 2017-10-19 13:28:37 RMAN> |
3.RENAME DATAFILE
|
SQL> alter database rename file '/u01/app/oracle/test01.dbf' to '+ORADATA1/racdb/datafile/test01.272.957792515'; Database altered. |
4.RECOVER DATAFILE
|
SQL> alter database recover datafile '+ORADATA1/racdb/datafile/test01.272.957792515'; Database altered. SQL> select tablespace_name,file_name,file_id,online_status from dba_data_files where file_id=7;
TABLESPACE_NAME FILE_NAME FILE_ID ONLINE_ ------------------------------ -------------------------------------------------- -------- TEST01 +ORADATA1/racdb/datafile/test01.272.957792515 7 OFFLINE |
5.ONLINE DATAFILE
|
SQL> alter database datafile '+ORADATA1/racdb/datafile/test01.272.957792515' online;
Database altered. SQL> select tablespace_name,file_name,file_id,online_status from dba_data_files where file_id=7;
TABLESPACE_NAME FILE_NAME FILE_ID ONLINE_ ------------------------------ -------------------------------------------------- -------- TEST01 +ORADATA1/racdb/datafile/test01.272.957792515 7 ONLINE目前TEST01表空间对应的数据文件已经成功从文件系统磁盘组移动回ASM磁盘组。 |
方法四、操作系统层面Move
注：本操作方式适用于SYSTEM表空间数据文件移动。
STOP DB
停止数据库实例
|
[root@acdante-1 ~]# srvctl stop database -d racdb -o immediate |
ASMCMD CP move datafile
在RAC环境下，使用ASMCMD cp命令进行数据文件的移动，在单实例未使用ASM时，可直接通过cp/mv等命令移动数据文件到指定位置。这里需要注意相应目录的权限，ASMCMD使用GRID用户连接，需要相关目录对GRID用户有读写权限。若无权限，则可能会出现如下报错：
|
ASMCMD-8016: copy source '+ORADATA1/racdb/datafile/test01.272.957792515' and target '/u01/test01.dbf;' failed ORA-19505: failed to identify file "/u01/test01.dbf;" ORA-27040: file create error, unable to create file Linux-x86_64 Error: 13: Permission denied Additional information: 1 ORA-15120: ASM file name '/u01/test01.dbf;' does not begin with the ASM prefix character ORA-06512: at "SYS.X$DBMS_DISKGROUP", line 415 ORA-06512: at line 3 (DBD ERROR: OCIStmtExecute) |
|
ASMCMD> cp /u01/app/grid/test01.dbf +ORADATA1/racdb/datafile/test01.dbf copying /u01/app/grid/test01.dbf -> +ORADATA1/racdb/datafile/test01.dbf ASMCMD> ASMCMD> ls -l Type Redund Striped Time Sys Name DATAFILE UNPROT COARSE OCT 19 14:00:00 Y SYSAUX.260.952265199 DATAFILE UNPROT COARSE OCT 19 14:00:00 Y SYSTEM.259.952265189 DATAFILE UNPROT COARSE OCT 19 14:00:00 Y UNDOTBS1.261.952265207 DATAFILE UNPROT COARSE OCT 19 14:00:00 Y UNDOTBS2.263.952265225 DATAFILE UNPROT COARSE OCT 19 14:00:00 Y UNDOTBS3.264.952265229 DATAFILE UNPROT COARSE OCT 19 14:00:00 Y USERS.265.952265231 N test01.dbf => +ORADATA1/ASM/DATAFILE/test01.dbf.271.957783867 |
MOUNT Database
|
[root@acdante-1 ~]# srvctl start database -d racdb -o mount |
RENAME DATAFILE
|
SQL> alter database rename file '/u01/app/grid/test01.dbf' to '+ORADATA1/racdb/datafile/test01.dbf’;
Database altered. |
Open DB
|
SQL> alter database open; Database altered. |
验证
|
SQL> select tablespace_name,file_name,file_id,online_status from dba_data_files where file_id=7;
TABLESPACE_NAME FILE_NAME FILE_ID ONLINE_ ------------------------------ ---------------------------------------------- TEST01 +ORADATA1/racdb/datafile/test01.db 7 ONLINE
|
SYSTEM系统表空间数据文件
SYSTEM 表空间移动参考非系统表空间数据文件移动的第4种方法，一定要将数据库实例 停止，再进行数据文件的移动，启动至MOUNT状态后，将系统表空间数据文件rename，再正常OPEN数据库即可。