1、问题描述
oracle rac环境将datafile创建到本地文件系统，造成部分应用间歇性访问异常。
2、处理方法
问题处理思路
1、删除新添加的本地datafile,从新添加datafile到共享存储中
2、迁移问题datafile到共享存储datafile
删除数据文件的限制:
以下是删除数据文件和临时文件的限制：
数据库必须打开。
如果数据文件不为空，则不能删除。
如果必须删除不为空且不能通过删除模式对象而变空的数据文件，则必须删除包含数据文件的表空间。
您不能删除表空间中的第一个或唯一的数据文件。(这意味着DROP DATAFILE不能与bigfile表空间一起使用。)
您不能在只读表空间中删除数据文件。
您不能在SYSTEM表空间中删除数据文件。---特别注意
如果本地管理表空间中的数据文件脱机，则无法删除。
3、方法一 非system drop
SQL> col file_name for a40
set line 200
select file_id,tablespace_name,file_name,bytes/1024/1024 from dba_data_files;SQL> SQL>
FILE_ID TABLESPACE_NAME FILE_NAME BYTES/1024/1024
---------- ------------------------------ ---------------------------------------- ---------------
4 USERS /dev/rlvusers_1G 5
3 SYSAUX dev/rlvsysaux_512 360
2 UNDOTBS1 dev/rlvundotbs_512 35
1 SYSTEM dev/rlvsystem_512 490
5 TT_TEST /oracle/oradata/test/tt_test01.dbf 100
6 TT_TEST /oracle/oradata/test/tt_test02.dbf 100 --删除该数据文件
7 SYSTEM dev/rlvsystem_512_2 100
7 rows selected.
SQL> select owner,segment_name,tablespace_name from dba_extents where file_id=6;
no rows selected
SQL> alter tablespace TT_TEST drop datafile 6;
Tablespace altered.
注：
如果数据文件中有数据，需要先迁移，重建索引。
1、检查是否有数据：SQL> select owner,segment_name,tablespace_name from dba_extents where file_id=6;
2、ALTER TABLE table_name MOVE TABLESPACE tablespace_name;
3、index_rebuild
4、 alter tablespace TT_TEST drop datafile 6;
select file_id,tablespace_name,file_name,bytes/1024/1024 from dba_data_files;SQL> SQL>
FILE_ID TABLESPACE_NAME FILE_NAME BYTES/1024/1024
---------- ------------------------------ ---------------------------------------- ---------------
4 USERS /dev/rlvusers_1G 5
3 SYSAUX /dev/rlvsysaux_512 360
2 UNDOTBS1 /dev/rlvundotbs_512 35
1 SYSTEM /dev/rlvsystem_512 490
5 TT_TEST /oracle/oradata/test/tt_test01.dbf 100
7 SYSTEM /dev/rlvsystem_512_2 100
6 rows selected.
SQL>
4、 方法二 非system rename
说明:
SQL> select file_id,tablespace_name,file_name,bytes/1024/1024,ONLINE_STATUS from dba_data_files;
FILEID Tbname FILE_NAME BYTES/1024/1024 ONLINE_
---------- -------------------- ------------- --------------- -------
4 USERS /dev/rlvusers_1G 5 ONLINE
3 SYSAUX /dev/rlvsysaux_512 360 ONLINE
2 UNDOTBS1 /dev/rlvundotbs_512 35 ONLINE
1 SYSTEM /dev/rlvsystem_512 490 SYSTEM
5 TT_TEST /oracle/oradata/test2/tt_test01.dbf 100 ONLINE
7 SYSTEM /dev/rlvsystem_512_2 100 SYSTEM
6 rows selected.
SQL> alter database datafile 5 offline;
Database altered.
SQL> select file_id,tablespace_name,file_name,bytes/1024/1024,ONLINE_STATUS from dba_data_files;
FILEID Tbname FILE_NAME BYTES/1024/1024 ONLINE_
---------- -------------------- ------------- --------------- -------
4 USERS /dev/rlvusers_1G 5 ONLINE
3 SYSAUX /dev/rlvsysaux_512 360 ONLINE
2 UNDOTBS1 /dev/rlvundotbs_512 35 ONLINE
1 SYSTEM /dev/rlvsystem_512 490 SYSTEM
5 TT_TEST /oracle/oradata/test2/tt_test01.dbf RECOVER
7 SYSTEM /dev/rlvsystem_512_2 100 SYSTEM
6 rows selected.
SQL> !cp /oracle/oradata/test2/tt_test01.dbf /oracle/oradata/test/tt_test02.dbf
SQL> alter database rename file '/oracle/oradata/test2/tt_test01.dbf' to '/oracle/oradata/test/tt_test02.dbf';
Database altered.
SQL> select file_id,tablespace_name,file_name,bytes/1024/1024,ONLINE_STATUS from dba_data_files;
FILEID Tbname FILE_NAME BYTES/1024/1024 ONLINE_
---------- -------------------- ------------- --------------- -------
4 USERS /dev/rlvusers_1G 5 ONLINE
3 SYSAUX /dev/rlvsysaux_512 360 ONLINE
2 UNDOTBS1 /dev/rlvundotbs_512 35 ONLINE
1 SYSTEM /dev/rlvsystem_512 490 SYSTEM
5 TT_TEST /oracle/oradata/test/tt_test02.dbf RECOVER
7 SYSTEM /dev/rlvsystem_512_2 100 SYSTEM
6 rows selected.
SQL> recover datafile 5;
Media recovery complete.
SQL> alter database datafile 5 online;
Database altered.
SQL> select file_id,tablespace_name,file_name,bytes/1024/1024,ONLINE_STATUS from dba_data_files;
FILE_ID TABLESPACE_NAME FILE_NAME BYTES/1024/1024 ONLINE_
---------- -------------------- -------------------------------------
4 USERS /dev/rlvusers_1G 5 ONLINE
3 SYSAUX /dev/rlvsysaux_512 360 ONLINE
2 UNDOTBS1 /dev/rlvundotbs_512 35 ONLINE
1 SYSTEM /dev/rlvsystem_512 490 SYSTEM
5 TT_TEST /oracle/oradata/test/tt_test02.dbf 100 ONLINE
7 SYSTEM /dev/rlvsystem_512_2 100 SYSTEM
6 rows selected.
5、方法三 system rename
SQL> /
FILE_ID TABLESPACE_NAME FILE_NAME BYTES/1024/1024
---------- ------------------------------ ---------------------------
4 USERS /dev/rlvusers_1G 5
3 SYSAUX /dev/rlvsysaux_512 360
2 UNDOTBS1 /dev/rlvundotbs_512 35
1 SYSTEM /dev/rlvsystem_512 490
5 TT_TEST /oracle/oradata/test/tt_test01.dbf 100
6 TT_TEST /oracle/oradata/test/tt_test02.dbf 100
7 SYSTEM /oracle/oradata/test/system02.dbf 100--迁移该文件到裸设备
7 rows selected.
SQL> alter system checkpoint;
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1610612736 bytes
Fixed Size 2084400 bytes
Variable Size 419430864 bytes
Database Buffers 1174405120 bytes
Redo Buffers 14692352 bytes
Database mounted.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
$ export ORACLE_SID=test
$ rman target /
Recovery Manager: Release 10.2.0.4.0 - Production on Sun Sep 24 10:45:59 2017
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: TEST (DBID=2245579828, not open)
RMAN> copy datafile '/oracle/oradata/test/system02.dbf' to '/dev/rlvsystem_512_2';
Starting backup at 24-SEP-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00007 name=/oracle/oradata/test/system02.dbf
output filename=/dev/rlvsystem_512_2 tag=TAG20170924T104619 recid=1 stamp=955536380
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 24-SEP-17
$ export ORACLE_SID=test
$ sqlplus / as sysdba
SQL> alter database rename file '/oracle/oradata/test/system02.dbf' to '/dev/rlvsystem_512_2';
Databse altered.
SQL> alter database open;
Database altered.
SQL> select file_id,tablespace_name,file_name,bytes/1024/1024,ONLINE_STATUS from dba_data_files;
FILE_ID TABLESPACE_NAME FILE_NAME BYTES/1024/1024 ONLINE_
---------- -------------------- ---------------------------------------- --------------- -------
4 USERS /dev/rlvusers_1G 5 ONLINE
3 SYSAUX /dev/rlvsysaux_512 360 ONLINE
2 UNDOTBS1 /dev/rlvundotbs_512 35 ONLINE
1 SYSTEM /dev/rlvsystem_512 490 SYSTEM
5 TT_TEST /oracle/oradata/test/tt_test01.dbf 100 ONLINE
7 SYSTEM /dev/rlvsystem_512_2 100 SYSTEM
6 rows selected.
6、注意事项
使用裸设备创建oracle datafile，裸设备需要比oracle datafiles大。
参考：Configuring Raw Devices for Real Application Clusters on Linux (Doc ID 246205.1)
Note: The logical volumes should be bigger than the size of the oracle datafiles, in the following table, Datafile Size indicates the size used for the Oracle datafiles. In this article, we created the logical volumes or disk partitions 1Mb bigger (indicated by the sample filenames).The sizes indicated here are according to what dbca uses as defaults in V9.2+ except for the redo logfiles, the default sizes are mostly the same for V9.0.1. You are strongly encouraged to adjust the sizes to your needs. After the database creation, you can allow them to auto extent to the size you need.
