oracle rac将datafile创建到本地文件系统导致orastar-20170924错误怎么办

2024-04-02 19:04:59 375人浏览安东尼

摘要

这篇文章主要介绍oracle rac将datafile创建到本地文件系统导致orastar-20170924错误怎么办，文中介绍的非常详细，具有一定的参考价值，感兴趣的小伙伴们一定要看完！1、问题描述将o

这篇文章主要介绍oracle rac将datafile创建到本地文件系统导致orastar-20170924错误怎么办，文中介绍的非常详细，具有一定的参考价值，感兴趣的小伙伴们一定要看完！

1、问题描述

将oracle rac环境的数据库创建到本地文件系统，造成部分应用间歇性访问异常。

2、处理方法

问题处理思路
1、删除新添加的本地datafile,从新添加datafile到共享存储中
2、迁移问题datafile，到共享存储datafile

删除数据文件的限制:
以下是删除数据文件和临时文件的限制：

点击(此处)折叠或打开

数据库必须打开。
如果数据文件不为空，则不能删除。
如果必须删除不为空且不能通过删除模式对象而变空的数据文件，则必须删除包含数据文件的表空间。
您不能删除表空间中的第一个或唯一的数据文件。(这意味着DROP DATAFILE不能与bigfile表空间一起使用。)
您不能在只读表空间中删除数据文件。
您不能在SYSTEM表空间中删除数据文件。---特别注意
如果本地管理表空间中的数据文件脱机，则无法删除。

|方法一 (非system drop)、move dba_extents中数据，删除datafile
|非system表空间-|
| |方法二 (非system rename)、datafile offline,cp datafile ,rename
datafile---|
|
|system表空间-|方法三 (system rename)、stop instance,startup mount,cp datafile to new location,rename,recover datafile,instance open

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 drop rename

说明:
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/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;

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/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;

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 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';

Database 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.