ftp到ASM磁盘组路径错误的问题

最新推荐文章于 2022-08-19 10:20:54 发布

ckawt40802

最新推荐文章于 2022-08-19 10:20:54 发布

阅读量109

点赞数

文章标签：数据库操作系统

由于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的跟目录下，而是处于一个奇怪的位置中：

在当前目录下，进入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 "http://xmlns.oracle.com/xdb/XDBResource.xsd" 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 "http://xmlns.oracle.com/xdb/XDBResource.xsd" 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错误导致的问题全部解决。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/4227/viewspace-612407/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/4227/viewspace-612407/