由於ASM磁碟組訪問比較麻煩，因此透過FTP的方式，將資料檔案複製到ASM磁碟組中，結果不小心導致資料檔案複製到錯誤的目錄下。

透過FTP訪問ASM磁碟組：http://yangtingkun.itpub.net/post/468/470778

當時FTP的操作型別下面的例子：

bash-3.00$ ftp 172.0.2.62 7788
Connected to 172.0.2.62.
220- ser1
Unauthorised use of this FTP server is prohibited and may be subject to civil and criminal prosecution.
220 ser1 FTP Server (Oracle XML DB/Oracle Database) ready.
Name (172.0.2.62:oracle): system
331 pass required for SYSTEM
Password:
230 SYSTEM logged in
Remote system type is Unix.
ftp> bin
200 Type set to I.
ftp> prompt
Interactive mode off.
ftp> mput test01.dbf
200 PORT Command successful
150 BIN Data Connection
226 BIN Transfer Complete
local: test01.dbf remote: test01.dbf
104890368 bytes sent in 9.3 seconds (11014.97 Kbytes/s)

由於忘記了指定目標路徑，因此資料檔案放到了錯誤的路徑中。需要注意當前的路徑可不是ASM磁碟組DATA的跟目錄下，而是處於一個奇怪的位置中：

ftp> ls
200 PORT Command successful
150 ASCII Data Connection
OLAP_XDS
images
olap_data_security
public
sys
sysaux01.dbf
temp01.dbf
test01.dbf
undotbs01.dbf
users01.dbf
xdbconfig.xml
yangtk01.dbf
226 ASCII Transfer Complete
146 bytes received in 0.00061 seconds (233.12 Kbytes/s)
ftp> cd sys
250 CWD Command successful
ftp> ls
200 PORT Command successful
150 ASCII Data Connection
acloids
acls
apps
asm
log
oid
principals
schemas
version
workspaces
xs
226 ASCII Transfer Complete
82 bytes received in 0.0005 seconds (159.52 Kbytes/s)
ftp> cd asm
250 CWD Command successful
ftp> ls
200 PORT Command successful
150 ASCII Data Connection
DATA
226 ASCII Transfer Complete
6 bytes received in 0.16 seconds (0.04 Kbytes/s)

在當前目錄下，進入sys目錄，然後進入asm目錄，這時出現的是DATA磁碟組目錄，在進入到DATA目錄後，訪問的才是ASM磁碟組。

那麼現在資料檔案被放到哪裡了。顯然不是作業系統上，在作業系統上進行搜尋找不到這些資料檔案：

root@ser1 # find / -name 'test01.dbf'
find: stat() error /proc/8117: No such file or directory
root@ser1 #

根據ASM的FTP訪問配置，透過FTP訪問ASM要藉助XDB的功能，莫非資料檔案被傳送到資料庫中：

bash-3.00$ export ORACLE_SID=ractest1
bash-3.00$ sqlplus / as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on 星期日 8月 9 20:57:44 2009

連線到:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> set pages 100 lines 120
SQL> select owner, sum(bytes)/1024/1024/1024
2 from dba_segments
3 group by owner
4 having sum(bytes)/1024/1024/1024 > 1
5 order by 2 desc;

OWNER                          SUM(BYTES)/1024/1024/1024
------------------------------ -------------------------
XDB                                           9.32232666
SYS                                           1.17749023

SQL> select segment_name, sum(bytes)/1024/1024/1024
2 from dba_segments
3 where wner = 'XDB'
4 group by segment_name
5 having sum(bytes)/1024/1024 > 500;

SEGMENT_NAME SUM(BYTES)/1024/1024/1024
----------------------------------------- -------------------------
SYS_LOB0000054787C00025$$ 9.25

SQL> select object_name, object_type
2 from dba_objects
3 where wner = 'XDB'
4 and object_name = 'SYS_LOB0000054787C00025$$';

OBJECT_NAME OBJECT_TYPE
------------------------------ -------------------
SYS_LOB0000054787C00025$$ LOB

SQL> select table_name, column_name, segment_name, tablespace_name
2 from dba_lobs
3 where wner = 'XDB'
4 and segment_name = 'SYS_LOB0000054787C00025$$';

TABLE_NAME COLUMN_NAME SEGMENT_NAME TABLESPACE_NAME
--------------- -------------------- ------------------------------ ---------------
XDB$RESOURCE "XMLDATA"."XMLLOB" SYS_LOB0000054787C00025$$ SYSAUX

果然，檔案被放到了XDB使用者下XDB$RESOURCE表的LOB列中，檢查這個表的結構：

SQL> DESC XDB.XDB$RESOURCE
名稱是否為空? 型別
----------------------------------------------------------------- -------- -----------
TABLE of XMLTYPE(XMLSchema "" Element "Resource") STORAGE Object-relational TYPE "XDB$RESOURCE_T"

SQL> DESC XDB.XDB$RESOURCE_T
名稱                                 是否為空? 型別
------------------------------------ -------- ----------------
VERSIONID                                     NUMBER(38)
CREATIONDATE                                  TIMESTAMP(6)
MODIFICATIONDATE                              TIMESTAMP(6)
AUTHOR                                        VARCHAR2(128)
DISPNAME                                      VARCHAR2(128)
RESCOMMENT                                    VARCHAR2(128)
LANGUAGE                                      VARCHAR2(128)
CHARSET                                       VARCHAR2(128)
CONTYPE                                       VARCHAR2(128)
REFCOUNT                                      RAW(4)
LOCKS                                         RAW(2000)
ACLOID                                        RAW(16)
OWNERID                                       RAW(16)
CREATORID                                     RAW(16)
LASTMODIFIERID                                RAW(16)
ELNUM                                         NUMBER(38)
SCHOID                                        RAW(16)
XMLREF                                        REF OF XMLTYPE
XMLLOB                                        BLOB
FLAGS                                         RAW(4)
RESEXTRA                                      CLOB
ACTIVITYID                                    NUMBER(38)
VCRUID                                        RAW(16)
PARENTS                                       XDB.XDB$PREDECESSOR_LIST_T
SBRESEXTRA                                    XDB.XDB$XMLTYPE_REF_LIST_T
SNAPSHOT                                      RAW(6)
ATTRCOPY                                      BLOB
CTSCOPY                                       BLOB
NODENUM                                       RAW(6)
SIZEONDISK                                    NUMBER(38)
RCLIST                                        XDB.XDB$RCLIST_T
CHECKEDOUTBYID                                RAW(16)
BASEVERSION                                   RAW(16)

SQL> SET LONG 100000
SQL> SELECT DBMS_METADATA.GET_DDL('TABLE', 'XDB$RESOURCE', 'XDB') FROM DUAL;

DBMS_METADATA.GET_DDL('TABLE','XDB$RESOURCE','XDB')
--------------------------------------------------------------------------------

CREATE TABLE "XDB"."XDB$RESOURCE" OF "SYS"."XMLTYPE"
   (    REF ("XMLDATA"."XMLREF") WITH ROWID,
        REF ("XMLDATA"."XMLREF") ALLOW PRIMARY KEY
   )
XMLSCHEMA "" ELEMENT "Resource" ID 734 PCTFRE
E 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "SYSAUX"
LOB ("XMLDATA"."XMLLOB") STORE AS BASICFILE (
TABLESPACE "SYSAUX" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10
NOCACHE LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT))
LOB ("XMLDATA"."RESEXTRA") STORE AS BASICFILE (
TABLESPACE "SYSAUX" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10
NOCACHE LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT))
LOB ("XMLDATA"."ATTRCOPY") STORE AS BASICFILE (
TABLESPACE "SYSAUX" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10
NOCACHE LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT))
LOB ("XMLDATA"."CTSCOPY") STORE AS BASICFILE (
TABLESPACE "SYSAUX" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10
NOCACHE LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT))

顯然這又是一個複雜的XML物件表，由於對XML物件不熟悉，因此還是透過FTP命令來解決這個問題：

ftp> ls
200 PORT Command successful
150 ASCII Data Connection
OLAP_XDS
images
olap_data_security
public
sys
sysaux01.dbf
temp01.dbf
test01.dbf
undotbs01.dbf
users01.dbf
xdbconfig.xml
yangtk01.dbf
226 ASCII Transfer Complete
146 bytes received in 0.00048 seconds (298.97 Kbytes/s)
ftp> delete sysaux01.dbf
250 DELE Command successful
ftp> ls
200 PORT Command successful
150 ASCII Data Connection
OLAP_XDS
images
olap_data_security
public
sys
temp01.dbf
test01.dbf
undotbs01.dbf
users01.dbf
xdbconfig.xml
yangtk01.dbf
226 ASCII Transfer Complete
132 bytes received in 0.00038 seconds (339.76 Kbytes/s)
ftp> delete temp01.dbf
250 DELE Command successful
ftp> delete test01.dbf
250 DELE Command successful
ftp> del undotbs01.dbf
250 DELE Command successful
ftp> del users01.dbf
250 DELE Command successful
ftp> del yangtk01.dbf
250 DELE Command successful
ftp> ls
200 PORT Command successful
150 ASCII Data Connection
OLAP_XDS
images
olap_data_security
public
sys
xdbconfig.xml
226 ASCII Transfer Complete
66 bytes received in 0.00027 seconds (243.13 Kbytes/s)

現在錯誤的資料檔案已經被清除掉了，不過資料庫中的LOB欄位的大小還沒有恢復：

SQL> SELECT SEGMENT_NAME, BYTES/1024/1024/1024
2 FROM DBA_SEGMENTS
3 WHERE WNER = 'XDB'
4 AND SEGMENT_NAME = 'SYS_LOB0000054787C00025$$';

SEGMENT_NAME BYTES/1024/1024/1024
------------------------------ --------------------
SYS_LOB0000054787C00025$$ 9.37988281

SQL> ALTER TABLE XDB.XDB$RESOURCE MOVE;

表已更改。

SQL> SELECT SEGMENT_NAME, BYTES/1024/1024/1024
2 FROM DBA_SEGMENTS
3 WHERE WNER = 'XDB'
4 AND SEGMENT_NAME = 'SYS_LOB0000054787C00025$$';

SEGMENT_NAME BYTES/1024/1024/1024
------------------------------ --------------------
SYS_LOB0000054787C00025$$ .03125

顯然恢復了XDB$RESOURCE表的大小，但是XDB表空間的大小還沒有恢復：

SQL> SELECT TABLESPACE_NAME, FILE_NAME, BYTES/1024/1024/1024
2 FROM DBA_DATA_FILES
3 WHERE TABLESPACE_NAME = 'SYSAUX';

TABLESPACE_NAME FILE_NAME BYTES/1024/1024/1024
--------------- --------------------------------------------- --------------------
SYSAUX +DATA/ractest/datafile/sysaux.260.664395357 10.7851563

SQL> ALTER DATABASE DATAFILE '+DATA/ractest/datafile/sysaux.260.664395357' RESIZE 4G;
ALTER DATABASE DATAFILE '+DATA/ractest/datafile/sysaux.260.664395357' RESIZE 4G
*
第 1 行出現錯誤:
ORA-03297: 檔案包含在請求的 RESIZE 值以外使用的資料

導致這個錯誤的原因是由於雖然XDB$RESOURCE表所佔用的空間已經被釋放，但是釋放的空間在資料檔案的中部，資料檔案的最後部分是MOVE得到的新的XDB$RESOURCE表，因此無法RESIZE空間。

SQL> ALTER TABLE XDB.XDB$RESOURCE MOVE;

表已更改。

SQL> ALTER DATABASE DATAFILE '+DATA/ractest/datafile/sysaux.260.664395357' RESIZE 4G;

資料庫已更改。

再次執行MOVE操作後，XDB$RESOURCE表被移動到資料檔案靠前的位置，使得資料檔案的空間可以釋放，至此由於FTP錯誤導致的問題全部解決。

ftp到ASM磁碟組路徑錯誤的問題

相關文章