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