oracle rac將datafile創(chuàng)建到本地文件系統(tǒng)導致orastar-20170924錯誤怎么辦

發(fā)布時間：2021-11-09 14:11:09 來源：億速云閱讀：103 作者：小新欄目：關(guān)系型數(shù)據(jù)庫

這篇文章主要介紹oracle rac將datafile創(chuàng)建到本地文件系統(tǒng)導致orastar-20170924錯誤怎么辦，文中介紹的非常詳細，具有一定的參考價值，感興趣的小伙伴們一定要看完！

1、問題描述

將oracle rac環(huán)境的數(shù)據(jù)庫創(chuàng)建到本地文件系統(tǒng)，造成部分應用間歇性訪問異常。

2、處理方法

問題處理思路
1、刪除新添加的本地datafile,從新添加datafile到共享存儲中
2、遷移問題datafile，到共享存儲datafile

刪除數(shù)據(jù)文件的限制:
以下是刪除數(shù)據(jù)文件和臨時文件的限制：

點擊(此處)折疊或打開

數(shù)據(jù)庫必須打開。
如果數(shù)據(jù)文件不為空，則不能刪除。
如果必須刪除不為空且不能通過刪除模式對象而變空的數(shù)據(jù)文件，則必須刪除包含數(shù)據(jù)文件的表空間。
您不能刪除表空間中的第一個或唯一的數(shù)據(jù)文件。(這意味著DROP DATAFILE不能與bigfile表空間一起使用。)
您不能在只讀表空間中刪除數(shù)據(jù)文件。
您不能在SYSTEM表空間中刪除數(shù)據(jù)文件。---特別注意
如果本地管理表空間中的數(shù)據(jù)文件脫機，則無法刪除。

|方法一 (非system drop)、move dba_extents中數(shù)據(jù)，刪除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 --刪除該數(shù)據(jù)文件
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.

注：
如果數(shù)據(jù)文件中有數(shù)據(jù)，需要先遷移，重建索引。
1、檢查是否有數(shù)據(jù)：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.

6、注意事項

使用裸設備創(chuàng)建oracle datafile，裸設備需要比oracle datafiles大。

以上是“oracle rac將datafile創(chuàng)建到本地文件系統(tǒng)導致orastar-20170924錯誤怎么辦”這篇文章的所有內(nèi)容，感謝各位的閱讀！希望分享的內(nèi)容對大家有幫助，更多相關(guān)知識，歡迎關(guān)注億速云行業(yè)資訊頻道！

向AI問一下細節(jié)

oracle rac將datafile創(chuàng)建到本地文件系統(tǒng)導致orastar-20170924錯誤怎么辦

1、問題描述

2、處理方法

3、方法一 非system drop實驗

4、 方法二 非system drop rename

5、方法三 system rename

6、注意事項

猜你喜歡

最新資訊

相關(guān)推薦

相關(guān)標簽