转载

Using FTP Transferring Non-ASM Datafiles to ASM diskgroup

可以使用XML DB中的虚拟目录功能来将Non-ASM数据文件传输到ASM磁盘组,可以通过XML DB协议比如 FTP,HTTP与API来维护ASM文件与目录。使用这种方法，ASM虚拟目录作为/sys/asm被mount。目录是虚拟的，使用XML DB ASM目录与文件不能物理存储。然而对ASM虚拟目录ASM组件可以执行任何操作。为了使用了这种方法来传输文件，最重要的就是安装与配置XML DB。

ASM虚拟目录缺省情况下是在安装XML DB时创建。如果使用ASM的数据库没有配置，那么这个目录将是空间并且不允许操作。如果ASM被配置，ASM虚拟目录,/sys/asm,会被mounted。ASM目录对于每个被 mount的磁盘组都有一个子目录。每个磁盘组目录对于每个数据库名包含一个子目录。另外，还可能包含管理员所创建的其它文件以及与目标相关的别名。

下面的例子使用ftp方式来传输文件
1.以root用户来检查ftp服务是否启用

[root@jyrac1 ~]# netstat -a | grep ftp
tcp        0      0 *:ftp                       *:*                         LISTEN

2.对XML DB配置FTP与HTTP端口

[oracle@jyrac1 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Dec 13 11:14:48 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> execute dbms_xdb.sethttpport(8080);

PL/SQL procedure successfully completed.

SQL> execute dbms_xdb.setftpport(2100);

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.


SQL> select dbms_xdb.getftpport() from dual;

DBMS_XDB.GETFTPPORT()
---------------------
                 2100

SQL> select dbms_xdb.gethttpport() from dual;

DBMS_XDB.GETHTTPPORT()
----------------------
                  8080

3.检查XML DB的dispatchers的配置如果没有设置就需要设置
对于单实例设置如下:

alter system set dispatchers = (PROTOCOL=TCP) (SERVICE=XDB)" scope=both

对于RAC实例，执行以下命令:

SQL> alter system set dispatchers ='(PROTOCOL=TCP) (SERVICE=jyrac1XDB)' scope=both  sid='jyrac1';

System altered.

SQL> alter system set dispatchers ='(PROTOCOL=TCP) (SERVICE=jyrac2XDB)' scope=both  sid='jyrac2';

System altered.

4.如果缺省监听没有使用的话，需要设置local_listener参数，例如可能需要将端口设置为1521。如需要重启监听

[grid@jyrac1 ~]$ srvctl stop listener -n jyrac1
[grid@jyrac1 ~]$ srvctl stop listener -n jyrac2
[grid@jyrac1 ~]$ srvctl start listener -n jyrac1
[grid@jyrac1 ~]$ srvctl start listener -n jyrac2

5.验证监听是否已经注册了FTP和HTTP

[grid@jyrac1 ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 13-DEC-2016 11:39:25

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                13-DEC-2016 11:39:15
Uptime                    0 days 0 hr. 0 min. 9 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/product/11.2.0/crs/network/admin/listener.ora
Listener Log File         /u01/app/grid/diag/tnslsnr/jyrac1/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.138.130.153)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=jyrac1)(PORT=2100))(Presentation=FTP) (Session=RAW))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=jyrac1)(PORT=8080))(Presentation=HTTP) (Session=RAW))
Services Summary...
Service "jyrac" has 1 instance(s).
  Instance "jyrac1", status READY, has 4 handler(s) for this service...
Service "jyrac1XDB" has 1 instance(s).
  Instance "jyrac1", status READY, has 1 handler(s) for this service...
Service "jyracXDB" has 1 instance(s).
  Instance "jyrac1", status READY, has 0 handler(s) for this service...
The command completed successfully

[grid@jyrac2 ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 13-DEC-2016 11:39:43

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                13-DEC-2016 11:39:22
Uptime                    0 days 0 hr. 0 min. 21 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/product/11.2.0/crs/network/admin/listener.ora
Listener Log File         /u01/app/grid/diag/tnslsnr/jyrac2/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.138.130.154)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=jyrac2)(PORT=2100))(Presentation=FTP) (Session=RAW))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=jyrac2)(PORT=8080))(Presentation=HTTP) (Session=RAW))
Services Summary...
Service "jyrac" has 1 instance(s).
  Instance "jyrac2", status READY, has 4 handler(s) for this service...
Service "jyrac2XDB" has 1 instance(s).
  Instance "jyrac2", status READY, has 1 handler(s) for this service...
Service "jyracXDB" has 1 instance(s).
  Instance "jyrac2", status READY, has 0 handler(s) for this service...
The command completed successfully

在两个节点输出的监听信息可以看到以下信息，说明监听已经注册了FTP与HTTP

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=jyrac2)(PORT=8080))(Presentation=HTTP) (Session=RAW))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=jyrac2)(PORT=2100))(Presentation=FTP) (Session=RAW))

6.验证数据库中是否存在无效的XML DB相关的软件包

SQL> select count(*)from dba_objects where owner='XDB' and status='INVALID';

  COUNT(*)
----------
         0

7.验证dba_registry中的XML DB状态

SQL> col comp_id for a15
SQL> col version for a15
SQL> col comp_name for a30
SQL> col status for a15
SQL> select comp_name, status, version from dba_registry where comp_name = 'Oracle XML  Database';

COMP_NAME                      STATUS          VERSION
------------------------------ --------------- ---------------
Oracle XML Database            VALID           11.2.0.4.0

8.登录XML DB ftp

[oracle@jyrac1 ~]$ ftp jyrac1 2100
Connected to jyrac1.
220- jyrac1
Unauthorised use of this FTP server is prohibited and may be subject to civil and  criminal prosecution.
220 jyrac1 FTP Server (Oracle XML DB/Oracle Database) ready.
530  Please login with USER and PASS.
530  Please login with USER and PASS.
KERBEROS_V4 rejected as an authentication type
Name (jyrac1:oracle): system
331 pass required for SYSTEM
Password:
230 SYSTEM logged in
Remote system type is Unix.

9.使用XML DB FTP来传输文件，该命令与标准的FTP命令相同

ftp> cd /sys/asm
250 CWD Command successful

ftp> cd datadg/jyrac/datafile
250 CWD Command successful
ftp> ls -lrt
227 Entering Passive Mode (127,0,0,1,84,36)
150 ASCII Data Connection
-rw-r--r--   1 SYS      oracle  1121984512 DEC 13 03:44 SYSAUX.258.930413055
-rw-r--r--   1 SYS      oracle  796925952 DEC 13 03:44 SYSTEM.259.930413057
-rw-r--r--   1 SYS      oracle  363077632 DEC 13 03:44 EXAMPLE.260.930413057
-rw-r--r--   1 SYS      oracle  157294592 DEC 13 03:44 UNDOTBS2.261.930413057
-rw-r--r--   1 SYS      oracle  104865792 DEC 13 03:44 UNDOTBS1.262.930413057
-rw-r--r--   1 SYS      oracle   5251072 DEC 13 03:44 USERS.263.930413057
226 ASCII Transfer Complete

ftp> bin
200  Type set to I.
ftp> get SYSTEM.259.930413057
local: SYSTEM.259.930413057 remote: SYSTEM.259.930413057
227 Entering Passive Mode (127,0,0,1,71,155)
150 BIN Data Connection
226 BIN Transfer Complete
796925952 bytes received in 46 seconds (1.7e+04 Kbytes/s)

验证传输的文件

[root@jyrac1 sys]# find / -name SYSTEM.259.930413057
/home/oracle/SYSTEM.259.930413057
[root@jyrac1 sys]# cd /home/oracle/
[root@jyrac1 oracle]# ls -lrt
total 779012
-rw-r--r-- 1 oracle oinstall 796925952 Dec 13 11:46 SYSTEM.259.930413057

正文到此结束