oracle rac將datafile建立到本地檔案系統問題處理總結-orastar-20170924
1、問題描述
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實驗
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
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.
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.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31442014/viewspace-2145383/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 本地無法複製檔案到windows server 系統的處理辦法WindowsServer
- ocfs2檔案系統問題總結(zt)
- oracle rac在加錯asm盤時，變成了檔案系統處理OracleASM
- 近期處理的Oracle資料庫問題總結Oracle資料庫
- 【故障處理】DBCA建庫詭異問題處理--rac環境不能建立rac庫
- Javascript如何訪問和處理系統檔案JavaScript
- oracle bdump 下.trc檔案過大問題處理Oracle
- RAC資料庫將資料檔案建立在本地磁碟資料庫
- 11G Oracle RAC新增新表空間時資料檔案誤放置到本地檔案系統的修正Oracle
- oracle系統預設臨時表空間以及redo日誌檔案問題處理Oracle
- 將本地檔案傳輸到GitHubGithub
- 解決從linux本地檔案系統上傳檔案到HDFS時的許可權問題Linux
- RAC中誤將資料檔案建立在本地盤時的修正
- oracle系統表空間過大問題處理Oracle
- 從ASM磁碟中複製檔案到本地檔案系統ASM
- Oracle RAC NFS掛載檔案系統OracleNFS
- 瀏覽器相容問題處理總結瀏覽器
- HIVEMapJoin異常問題處理總結Hive
- 將ASM裡面的檔案copy到檔案系統ASM
- 小測某Q友其應用人員不小心把RAC表空間tablespace資料檔案datafile建在本地檔案系統
- oracle em節點啟動不成功問題處理總結Oracle
- 記一次：歸檔檔案系統問題導致資料庫hang處理資料庫
- 處理Maven本地倉庫.lastUpdated檔案MavenAST
- XML檔案處理中增加xmlns問題XML
- oracle 11gR2 rac 安裝問題總結Oracle
- Oracle_dg歸檔丟失問題處理Oracle
- windows系統DLL檔案全恢復到初始化狀態批處理檔案。Windows
- ORACLE RAC 裸裝置資料庫一節點表空間擴容錯誤新增資料檔案到本地的處理Oracle資料庫
- RAC磁碟頭損壞問題處理
- 大體積XML檔案處理效能問題XML
- oracle之 RAC本地資料檔案遷移至ASMOracleASM
- Oracle資料庫聯機日誌檔案丟失處理方法（總結）！Oracle資料庫
- 總結logminer使用及各種問題處理
- redhat7 搭建oracle 11g RAC 問題與處理RedhatOracle
- ORACLE 9I RAC IPC Send timeout detected問題處理Oracle
- Oracle 11g RAC之HAIP相關問題總結OracleAI
- oracle RAC中表空間資料檔案錯誤建立到了節點本地位置Oracle
- 銀河麒麟系統安裝ORACLE資料庫問題處理Oracle資料庫