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.

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.

oracle rac將datafile建立到本地檔案系統問題處理總結-orastar-20170924

1、問題描述

2、處理方法

3、方法一非system drop實驗

4、方法二非system drop rename

5、方法三 system rename

6、注意事項

相關文章

oracle rac將datafile建立到本地檔案系統問題處理總結-orastar-20170924

1、問題描述

2、處理方法

3、方法一 非system drop實驗

4、 方法二 非system drop rename

5、方法三 system rename

6、注意事項

相關文章

3、方法一非system drop實驗

4、方法二非system drop rename