首 先我们需要安装kernel-devel和FUSE包,kernel-devel这个可从镜像中找到,FUSE(file system in userspace)文件可以从https://github.com/libfuse下载,本文按照官方文档中的版本做的测试。
FUSE (Filesystem in Userspace) is a simple interface for userspace programs to export a virtual filesystem to the Linux kernel. FUSE also aims to provide a secure method for non privileged users to create and mount their own filesystem implementations.
You can download the source code releases from
https://github.com/libfuse/libfuse/releases
在Linux安装必须满足以下条件
A.The dbfs_client host must have the kernel-devel package installed to configure and build FUSE.
B.The dbfs_client host must have the FUSE Linux package installed.
C.A group named fuse must be created and the user name that is running the dbfs_client must be a member of the fuse group.
安装fuse[oracle@db1 ~]$$ tar -xzvf fuse-2.7.4.tar.gz [oracle@db1 ~]$$ cd fuse-2.7.4 [oracle@db1 fuse-2.7.4]$ ./configure --prefix=/usr [oracle@db1 fuse-2.7.4]$ make [oracle@db1 fuse-2.7.4]$ su [root@db1 fuse-2.7.4]# make install [root@db1 fuse-2.7.4]# /sbin/depmod [root@db1 fuse-2.7.4]# /sbin/modprobe fuse [root@db1 fuse-2.7.4]# chmod 666 /dev/fuse [oracle@db1 admin]$ ls -lrt /dev | grep fuse crw-rw-rw- 1 root root 10, 229 May 11 14:56 fuse [oracle@db1 admin]$ [root@db1 fuse-2.7.4]# echo "/sbin/modprobe fuse" >> /etc/rc.modules [root@db1 fuse-2.7.4]# lsmod |grep fuse fuse 73530 2 [root@db1 fuse-2.7.4]#
查看fuse版本
[root@db1 ~]# rpm -qa|grep kernel-devel kernel-devel-2.6.32-431.el6.x86_64 [root@db1 ~]# fusermount -V fusermount version: 2.7.4 [root@db1 ~]#
查看dbfs_create_filesystem.sql
dbfs_create_filesystem.sql会调用dbfs_create_filesystem_advanced.sql,不过dbfs_create_filesystem.sql后面的参数默认为nocompress nodeduplicate noencrypt non-partition
[oracle@db1 fuse-2.9.4]$ cd $ORACLE_HOME [oracle@db1 112]$ cd rdbms/admin/ [oracle@db1 admin]$ ls -l|grep dbfs_create_filesystem -rw-r--r-- 1 oracle oinstall 6588 Oct 29 2012 dbfs_create_filesystem_advanced.sql -rw-r--r-- 1 oracle oinstall 974 Jun 1 2010 dbfs_create_filesystem.sql [oracle@db1 admin]$ cat dbfs_create_filesystem.sql Rem Rem $Header: rdbms/admin/dbfs_create_filesystem.sql /main/4 2010/06/01 11:01:01 nmukherj Exp $ Rem Rem dbfs_create_filesystem.sql Rem Rem Copyright (c) 2009, 2010, Oracle and/or its affiliates. Rem All rights reserved. Rem Rem NAME Rem dbfs_create_filesystem.sql - DBFS create filesystem Rem Rem DESCRIPTION Rem DBFS create filesystem script Rem Usage: sqlplus <dbfs_user> @dbfs_create_filesystem.sql Rem <tablespace_name> <filesystem_name> Rem Rem NOTES Rem Rem MODIFIED (MM/DD/YY) Rem nmukherj 05/30/10 - changing default to non-partitioned SF segment Rem weizhang 03/11/10 - bug 9220947: tidy up Rem weizhang 04/06/09 - Created Rem SET ECHO OFF SET VERIFY OFF SET FEEDBACK OFF SET TAB OFF SET SERVEROUTPUT ON define ts_name = &1 define fs_name = &2 @@dbfs_create_filesystem_advanced.sql &ts_name &fs_name nocompress nodeduplicate noencrypt non-partition undefine ts_name undefine fs_name [oracle@db1 admin]$ cat dbfs_create_filesystem_advanced.sql Rem Rem $Header: rdbms/admin/dbfs_create_filesystem_advanced.sql /st_rdbms_11.2.0/1 2012/10/29 10:22:03 weizhang Exp $ Rem Rem dbfs_create_filesystem.sql Rem Rem Copyright (c) 2009, 2012, Oracle and/or its affiliates. Rem All rights reserved. Rem Rem NAME Rem dbfs_create_filesystem_advanced.sql - DBFS create filesystem Rem Rem DESCRIPTION Rem DBFS create filesystem script Rem Usage: sqlplus @dbfs_create_filesystem_advanced.sql Rem <tablespace_name> <filesystem_name> Rem <compress-high | compress-medium | nocompress> Rem <deduplicate | nodeduplicate> <encrypt | noencrypt> Rem <non-partition | partition | partition-by-itemname | Rem partition-by-guid, partition-by-path> Rem Rem NOTES Rem Rem MODIFIED (MM/DD/YY) Rem apfwkr 10/21/12 - Backport weizhang_bug-14666696 from main Rem weizhang 03/11/10 - bug 9220947: tidy up Rem weizhang 06/12/09 - Package name change Rem weizhang 04/06/09 - Created Rem SET ECHO OFF SET VERIFY OFF SET FEEDBACK OFF SET TAB OFF SET SERVEROUTPUT ON define ts_name = &1 define fs_name = &2 define fs_compress = &3 define fs_dedup = &4 define fs_encrypt = &5 define fs_partition = &6 -------------------------------------------------- -- Create DBFS file system help procedure --------------------------------------------------
创建用户及DBFS文件系统
SQL> create tablespace ohs_tbs datafile '/oradata/upgr/ohs01.dbf' size 10M; Tablespace created. SQL> create user ohsdba identified by ohsdba default tablespace ohs_tbs; User created. SQL> grant connect,resource,create session,dbfs_role to ohsdba; Grant succeeded.
SQL> conn ohsdba/ohsdba
Connected.
SQL>
SQL> @$ORACLE_HOME/rdbms/admin/dbfs_create_filesystem.sql ohs_tbs dbfs
No errors. -------- CREATE STORE: begin dbms_dbfs_sfs.createFilesystem(store_name => 'FS_DBFS', tbl_name => 'T_DBFS', tbl_tbs => 'ohs_tbs', lob_tbs => 'ohs_tbs', do_partition => false, partition_key => 1, do_compress => false, compression => '', do_dedup => false, do_encrypt => false); end; -------- REGISTER STORE: begin dbms_dbfs_content.registerStore(store_name=> 'FS_DBFS', provider_name => 'sample1', provider_package => 'dbms_dbfs_sfs'); end; -------- MOUNT STORE: begin dbms_dbfs_content.mountStore(store_name=>'FS_DBFS', store_mount=>'dbfs'); end; -------- CHMOD STORE: declare m integer; begin m := dbms_fuse.fs_chmod('/dbfs', 16895); end; No errors. SQL>
挂载文件系统
在挂载之前,必须先先做以下步骤
# echo "/usr/local/lib" >> /etc/ld.so.conf.d/usr_local_lib.conf
# cd /usr/local/lib
# ln -s $ORACLE_HOME/lib/libclntsh.so.11.1
# ln -s $ORACLE_HOME/lib/libnnz11.so
# locate libfuse.so
determined_path/libfuse.so
# ln –s determined_path/libfuse.so
# ldconfig
[root@db1 ~]# export ORACLE_HOME=/u01/oracle/product/112 [root@db1 ~]# echo "/usr/local/lib" >> /etc/ld.so.conf.d/usr_local_lib.conf [root@db1 ~]# cd /usr/local/lib [root@db1 lib]# ln -s $ORACLE_HOME/lib/libclntsh.so.11.1 [root@db1 lib]# ln -s $ORACLE_HOME/lib/libnnz11.so [root@db1 lib]# ln -s /usr/lib/libfuse.so [root@db1 lib]# ldconfig [root@db1 lib]#
在前台挂载
[oracle@db1 ~]$ dbfs_client ohsdba@db1:/upgr -o rw,user,direct_io /dbfs Password:
open another session to check
[oracle@db1 ~]$ df -h Filesystem Size Used Avail Use% Mounted on /dev/mapper/vg_db1-lv_root 36G 15G 19G 45% / tmpfs 931M 228K 931M 1% /dev/shm /dev/sda1 485M 63M 397M 14% /boot /dev/sda3 40G 9.0G 29G 24% /oradata dbfs-ohsdba@db1:/upgr:/ 9.0M 120K 8.9M 2% /dbfs [oracle@db1 ~]$
在后台挂载
[oracle@db1 ~]$ cat passwd ohsdba [oracle@db1 ~]$ nohup dbfs_client ohsdba@db1:/upgr -o rw,user,direct_io /dbfs <passwd & [1] 18563 [oracle@db1 ~]$ nohup: appending output to `nohup.out' [oracle@db1 ~]$ jobs [1]+ Running nohup dbfs_client ohsdba@db1:/upgr -o rw,user,direct_io /dbfs < passwd & [oracle@db1 ~]$ df -h Filesystem Size Used Avail Use% Mounted on /dev/mapper/vg_db1-lv_root 36G 15G 19G 45% / tmpfs 931M 228K 931M 1% /dev/shm /dev/sda1 485M 63M 397M 14% /boot /dev/sda3 40G 9.0G 29G 24% /oradata dbfs-ohsdba@db1:/upgr:/ 9.0M 120K 8.9M 2% /dbfs [oracle@db1 ~]$
dbfs_client命令
[oracle@db1 ~]$ dbfs_client --------MOUNT mode: usage: dbfs_client <db_user>@<db_server> [options] <mountpoint> db_user: Name of Database user that owns DBFS content repository filesystem(s) db_server: A valid connect string for Oracle database server (for example, hrdb_host:1521/hrservice) mountpoint: Path to mount Database File System(s) All the file systems owned by the database user will be seen at the mountpoint. DBFS options: -o direct_io Bypass the Linux page cache. Gives much better performance for large files. Programs in the file system cannot be executed with this option. This option is recommended when DBFS is used as an ETL staging area. -o wallet Run dbfs_client in background. Wallet must be configured to get credentials. -o failover dbfs_client fails over to surviving database instance with no data loss. Some performance cost on writes, especially for small files. -o allow_root Allows root access to the filesystem. This option requires setting 'user_allow_other' parameter in '/etc/fuse.conf'. -o allow_other Allows other users access to the file system. This option requires setting 'user_allow_other' parameter in '/etc/fuse.conf'. -o rw Mount the filesystem read-write. [Default] -o ro Mount the filesystem read-only. Files cannot be modified. -o trace_file=STR Tracing <filename> | 'syslog' -o trace_level=N Trace Level: 1->DEBUG, 2->INFO, 3->WARNING, 4->ERROR, 5->CRITICAL [Default: 4] -h help -V version --------COMMAND mode: Usage: dbfs_client <db_user>@<db_server> --command command [switches] [arguments] command: Command to be executed, e.g., ls, cp, mkdir, rm switches: Switches are described below for each command. arguments: File names or directory names NOTE: All database pathnames must be absolute and preceded by dbfs:/ Commands ls dbfs_client <db_user>@<db_server> --command ls [switches] target Switches: -a Show all files including those starting with '.' -l Use a long listing format. In addition to the name of each file print the file type, permissions, size, user and group information -R List subdirectories recursively cp dbfs_client <db_user>@<db_server> --command cp [switches] source destination Switches: -r, -R Copy a directory and its contents recursively into the destination directory rm dbfs_client <db_user>@<db_server> --command rm [switches] target Switches: -r, -R Removes a directory and its contents recursively mkdir dbfs_client <db_user>@<db_server> --command mkdir directory_name Examples dbfs_client ETLUser@DBConnectString --command ls -l -a dbfs:/staging_area/directory1 dbfs_client ETLUser@DBConnectString --command cp -R /tmp/1-Jan-2009-dump dbfs:/staging_area dbfs_client ETLUser@DBConnectString --command rm dbfs:/staging_area/hello.txt dbfs_client ETLUser@DBConnectString --command mkdir dbfs:/staging_area/directory2 [oracle@db1 ~]$
测试列出dbfs目录
[oracle@db1 ~]$ dbfs_client ohsdba@db1:/upgr --command ls -l -a -R dbfs:/dbfs
Password:
drwxr-xr-x root root 0 May 11 00:02 dbfs:/dbfs/.sfs drwxr-xr-x root root 0 May 11 00:02 dbfs:/dbfs/.sfs/attributes drwxr-xr-x root root 0 May 11 00:02 dbfs:/dbfs/.sfs/tools drwxr-xr-x root root 0 May 11 00:02 dbfs:/dbfs/.sfs/snapshots drwxr-xr-x root root 0 May 11 00:02 dbfs:/dbfs/.sfs/RECYCLE drwxr-xr-x root root 0 May 11 00:02 dbfs:/dbfs/.sfs/content [oracle@db1 ~]$
测试创建目录
[oracle@db1 ~]$ dbfs_client ohsdba@db1:/upgr --command mkdir dbfs:/dbfs/ohsdba
Password:
[oracle@db1 ~]$ dbfs_client ohsdba@db1:/upgr --command ls -a -R dbfs:/dbfs Password: dbfs:/dbfs/.sfs dbfs:/dbfs/.sfs/attributes dbfs:/dbfs/.sfs/tools dbfs:/dbfs/.sfs/snapshots dbfs:/dbfs/.sfs/RECYCLE dbfs:/dbfs/.sfs/content dbfs:/dbfs/ohsdba [oracle@db1 ~]$
测试复制文件
[oracle@db1 ~]$ dbfs_client ohsdba@db1:/upgr --command cp /etc/hosts dbfs:/dbfs/ohsdba
Password: /etc/hosts -> dbfs:/dbfs/ohsdba/hosts [oracle@db1 ~]$ dbfs_client ohsdba@db1:/upgr --command ls -a -R dbfs:/dbfs/ohsdba Password: dbfs:/dbfs/ohsdba/hosts [oracle@db1 ~]$
测试删除文件
[oracle@db1 ~]$ dbfs_client ohsdba@db1:/upgr --command rm dbfs:/dbfs/ohsdba/hosts Password: unlinking file dbfs:/dbfs/ohsdba/hosts [oracle@db1 ~]$ dbfs_client ohsdba@db1:/upgr --command ls -a -R dbfs:/dbfs/ohsdba Password: [oracle@db1 ~
操作系统层面操作
[oracle@db1 ohsdba]$ pwd /dbfs/dbfs/ohsdba [oracle@db1 ohsdba]$ ls [oracle@db1 ohsdba]$ cp /etc/hosts . [oracle@db1 ohsdba]$ ls -l total 1 -rw-r--r-- 1 oracle oinstall 190 May 11 00:34 hosts [oracle@db1 ohsdba]$ pwd /dbfs/dbfs/ohsdba [oracle@db1 ohsdba]$ [oracle@db1 ohsdba]$ cp /etc/passwd . [oracle@db1 ohsdba]$ pwd /dbfs/dbfs/ohsdba [oracle@db1 ohsdba]$ ls -l total 3 -rw-r--r-- 1 oracle oinstall 190 May 11 00:34 hosts -rw-r--r-- 1 oracle oinstall 2120 May 11 00:35 passwd [oracle@db1 ohsdba]$
在数据库查看
我们可以发现表Oracle自动创建了2个表,一个为T_DBFS和SFS$_FSTP_1。PATHNAME字段表示文件系统路径,ITEM字段表示文件名字,是以BLOB的形式存放在数据库中。
SQL> select * from tab; TNAME TABTYPE CLUSTERID ------------------------------ ------- ---------- SFS$_FSTP_1 TABLE T_DBFS TABLE SQL> desc t_dbfs Name Null? Type ----------------------------------------- -------- ---------------------------- VOLID NOT NULL NUMBER CSNAP# NOT NULL NUMBER LSNAP# NUMBER PATHNAME NOT NULL VARCHAR2(1024) ITEM NOT NULL VARCHAR2(256) PATHTYPE NOT NULL NUMBER(38) FILEDATA BLOB POSIX_NLINK NUMBER(38) POSIX_MODE NUMBER(38) POSIX_UID NUMBER(38) POSIX_GID NUMBER(38) STD_ACCESS_TIME NOT NULL TIMESTAMP(6) STD_ACL VARCHAR2(1024) STD_CHANGE_TIME NOT NULL TIMESTAMP(6) STD_CONTENT_TYPE VARCHAR2(1024) STD_CREATION_TIME NOT NULL TIMESTAMP(6) STD_DELETED NOT NULL NUMBER(38) STD_GUID NOT NULL NUMBER(38) STD_MODIFICATION_TIME NOT NULL TIMESTAMP(6) STD_OWNER VARCHAR2(32) STD_PARENT_GUID NOT NULL NUMBER(38) STD_REFERENT VARCHAR2(1024) OPT_HASH_TYPE VARCHAR2(32) OPT_HASH_VALUE VARCHAR2(128) OPT_LOCK_COUNT NUMBER(38) OPT_LOCK_DATA VARCHAR2(128) OPT_LOCK_STATUS NUMBER(38) SQL>
SQL> col pathname for a20
SQL> col item for a40
SQL> select volid,pathname,item from t_dbfs;
VOLID PATHNAME ITEM ---------- -------------------- ---------------------------------------- 0 / ROOT 0 /.sfs .sfs 0 /.sfs/attributes attributes 0 /.sfs/tools tools 0 /.sfs/snapshots snapshots 0 /.sfs/RECYCLE RECYCLE 0 /.sfs/content content 0 /ohsdba ohsdba 0 /ohsdba/hosts hosts 0 /ohsdba/passwd passwd 10 rows selected. SQL> desc SFS$_FSTP_1 Name Null? Type ----------------------------------------- -------- ---------------------------- VOLID NOT NULL NUMBER CSNAP# NOT NULL NUMBER LSNAP# NUMBER STD_GUID NOT NULL NUMBER(38) PROPNAME NOT NULL VARCHAR2(32) PROPVALUE NOT NULL VARCHAR2(1024) TYPECODE NOT NULL NUMBER(38) SQL> SQL> col PROPVALUE for a20 SQL> select * from SFS$_FSTP_1; no rows selected SQL>
卸载文件系统
[oracle@db1 dbfs]$ ps -ef|grep dbfs_client
oracle 18563 18372 0 00:25 pts/1 00:00:00 dbfs_client ohsdba@db1:/upgr -o rw,user,direct_io /dbfs
oracle 19016 18372 0 00:45 pts/1 00:00:00 grep dbfs_client
[oracle@db1 dbfs]$ fusermount -u /dbfs
umount: /dbfs: device is busy.
(In some cases useful info about processes that use
the device is found by lsof(8) or fuser(1))
[oracle@db1 dbfs]$ kill -9 18563 [oracle@db1 dbfs]$ kill -9 18563 -bash: kill: (18563) - No such process [1]+ Killed nohup dbfs_client ohsdba@db1:/upgr -o rw,user,direct_io /dbfs < passwd (wd: ~) (wd now: /dbfs/dbfs) [oracle@db1 dbfs]$ [oracle@db1 dbfs]$ [oracle@db1 dbfs]$ cd /dbfs -bash: cd: /dbfs: Transport endpoint is not connected [oracle@db1 dbfs]$ [oracle@db1 ~]$ ls -l / ls: cannot access /dbfs: Transport endpoint is not connected total 118 dr-xr-xr-x. 2 root root 4096 Mar 10 11:35 bin dr-xr-xr-x. 5 root root 1024 Mar 10 05:15 boot drwxr-xr-x. 2 root root 4096 Nov 22 2013 cgroup d?????????? ? ? ? ? ? dbfs [oracle@db1 ~]$ fusermount -u /dbfs [oracle@db1 ~]$ ls -l / total 122 dr-xr-xr-x. 2 root root 4096 Mar 10 11:35 bin dr-xr-xr-x. 5 root root 1024 Mar 10 05:15 boot drwxr-xr-x. 2 root root 4096 Nov 22 2013 cgroup drwxr-xr-x 2 oracle oinstall 4096 May 11 00:17 dbfs [oracle@db1 ~]$ cd /dbfs/ [oracle@db1 dbfs]$ ls -l total 0 [oracle@db1 dbfs]$
DBFS reorg
DBFS文件系统中的文件删除之后,空间不会释放,我们可以通过以下方法去reorg,原理是新创建个临时的DBFS文件系统,最后再删除这个临时的文件系统。
详见http://docs.oracle.com/database/121/ADLOB/adlob_client.htm#ADLOB46219
SQL> select * from table(dbms_dbfs_sfs.listfilesystems);STORE_NAME SCHEMA_NAME
-------------------------------- --------------------------------
TABLE_NAME
--------------------------------
VOLUME_NAME
--------------------------------------------------------------------------------
SNAPSHOT_NAME
--------------------------------------------------------------------------------
CREATED
---------------------------------------------------------------------------
FS_DBFS OHSDBA
T_DBFS
main
10-MAY-16 04.02.00.082920 PM
SQL>
PROCEDURE REORGANIZEFS
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SRCSTORE-->源dbfs名字 VARCHAR2 IN
DSTSTORE-->目标dbfs名字 VARCHAR2 IN
新创建一DBFS文件系统
SQL> @$ORACLE_HOME/rdbms/admin/dbfs_create_filesystem.sql ohs_tbs dbfs_new
No errors. -------- CREATE STORE: begin dbms_dbfs_sfs.createFilesystem(store_name => 'FS_DBFS_NEW', tbl_name => 'T_DBFS_NEW', tbl_tbs => 'ohs_tbs', lob_tbs => 'ohs_tbs', do_partition => false, partition_key => 1, do_compress => false, compression => '', do_dedup => false, do_encrypt => false); end; -------- REGISTER STORE: begin dbms_dbfs_content.registerStore(store_name=> 'FS_DBFS_NEW', provider_name => 'sample1', provider_package => 'dbms_dbfs_sfs'); end; -------- MOUNT STORE: begin dbms_dbfs_content.mountStore(store_name=>'FS_DBFS_NEW', store_mount=>'dbfs_new'); end; -------- CHMOD STORE: declare m integer; begin m := dbms_fuse.fs_chmod('/dbfs_new', 16895); end; No errors. SQL>
[root@db1 ~]# cd /dbfs/
-bash: cd: /dbfs/: Permission denied
[root@db1 ~]# su - oracle
[oracle@db1 ~]$ cd /dbfs/
[oracle@db1 dbfs]$ ls -l
total 0
drwxrwxrwx 4 root root 0 May 11 00:30 dbfs
drwxrwxrwx 3 root root 0 May 11 15:27 dbfs_new
[oracle@db1 dbfs]$ cd dbfs_new
[oracle@db1 dbfs_new]$ ls
[oracle@db1 dbfs_new]$
通过DBFS$_STORES查看
SQL> desc DBFS$_STORES
ERROR:
ORA-04043: object DBFS$_STORES does not exist
SQL> show user
USER is "OHSDBA"
SQL> conn / as sysdba
Connected.
SQL> desc DBFS$_STORES
Name Null? Type
----------------------------------------- -------- ----------------------------
S_OWNER NOT NULL VARCHAR2(32)
S_NAME NOT NULL VARCHAR2(32)
P_NAME NOT NULL VARCHAR2(32)
P_PKG NOT NULL VARCHAR2(32)
CREATED NOT NULL TIMESTAMP(6)
SQL> set linesize 156
SQL> select s_owner,s_name,p_name from dbfs$_Stores;
S_OWNER S_NAME P_NAME
-------------------------------- -------------------------------- -------------
OHSDBA FS_DBFS sample1
OHSDBA FS_DBFS_NEW sample1
SQL>
执行dbms_dbfs_sfs.reorganizefs(需要dba权限,完成操作后可以回收权利)
exec dbms_dbfs_sfs.reorganizefs('FS_DBFS','FS_DBFS_NEW'); SQL> exec dbms_dbfs_sfs.reorganizefs('FS_DBFS','FS_DBFS_NEW'); BEGIN dbms_dbfs_sfs.reorganizefs('FS_DBFS','FS_DBFS_NEW'); END; * ERROR at line 1: ORA-01031: insufficient privileges ORA-06512: at "SYS.DBMS_DBFS_SFS", line 8953 ORA-06512: at line 1 SQL> SQL> show user USER is "OHSDBA"
SQL> conn / as sysdba
Connected.
SQL> grant dba to ohsdba;
SQL> conn ohsdba
Enter password:
Connected.
SQL> exec dbms_dbfs_sfs.reorganizefs('FS_DBFS','FS_DBFS_NEW');
SQL>
SQL> @$ORACLE_HOME/rdbms/admin/dbfs_drop_filesystem.sql dbfs_new
SQL> revoke dba from ohsdba;
SQL>
删除dbfs
[oracle@db1 ~]$ cat $ORACLE_HOME/rdbms/admin/dbfs_drop_filesystem.sql
Rem Rem $Header: rdbms/admin/dbfs_drop_filesystem.sql /st_rdbms_11.2.0/1 2012/10/29 10:22:03 weizhang Exp $ Rem Rem dbfs_drop_filesystem.sql Rem Rem Copyright (c) 2009, 2012, Oracle and/or its affiliates. Rem All rights reserved. Rem Rem NAME Rem dbfs_drop_filesystem.sql - DBFS drop filesystem Rem Rem DESCRIPTION Rem DBFS drop filesystem script Rem Usage: sqlplus <dbfs_user> @dbfs_drop_filesystem.sql <fs_name> Rem Rem NOTES Rem Rem MODIFIED (MM/DD/YY) Rem apfwkr 10/21/12 - Backport weizhang_bug-14666696 from main Rem weizhang 03/11/10 - bug 9220947: tidy up Rem weizhang 11/19/09 - Support default fsDrop FORCE Rem weizhang 06/12/09 - Package name change Rem weizhang 04/06/09 - Created Rem
SQL> @$ORACLE_HOME/rdbms/admin/dbfs_drop_filesystem.sql dbfs_new
SQL>
用PL/SQL查看DBFS上文件内容
[oracle@db1 ~]$ cd /dbfs/dbfs/ohsdba/
[oracle@db1 ohsdba]$ ls -l
total 6
-rw-r--r-- 1 oracle oinstall 71 May 11 14:50 cvs
-rw-r--r-- 1 oracle oinstall 190 May 11 00:34 hosts
-rw-r--r-- 1 oracle oinstall 1770 May 11 13:47 ntp.conf
-rw-r--r-- 1 oracle oinstall 2120 May 11 00:35 passwd
[oracle@db1 ohsdba]$
path=>'/dbfs/ohsdba/hosts',格式为:/DBFS文件系统名字/文件夹或文件名字
set serveroutput on declare buf raw(32767); props dbms_dbfs_content.properties_t; data blob; itype integer; len number; off number; amt number; BEGIN dbms_dbfs_content.getpath(path=>'/dbfs/ohsdba/hosts', properties=> props, content=>data, item_type=> itype, prop_flags=>dbms_dbfs_content.prop_data); len := dbms_lob.getlength(data); dbms_output.put_line('Length: ' || len); off := 1; while off < len loop amt := 32767; dbms_lob.read(data, amt, off, buf); if amt > 0 then dbms_output.put_line(utl_raw.cast_to_varchar2(buf)); end if; off := off + amt; end loop; dbms_output.put_line(''); END; /
Length: 190
127.0.0.1 localhost localhost.localdomain localhost4
localhost4.localdomain4
::1 localhost localhost.localdomain localhost6
localhost6.localdomain6
PL/SQL procedure successfully completed.
SQL>
在Linux上通过fstab挂载DBFS
File systems are commonly configured using the fstab utility in Linux. To mount DBFS through /etc/fstab, You must use Oracle Wallet for authentication. Run the following operations as root user. To mount DBFS through fstab in Linux: Login as root user. Change the user and group of dbfs_client to be user root and group fuse. # chown root.fuse $ORACLE_HOME/bin/dbfs_client Set the setuid bit on dbfs_client, and restrict execute privileges to the user and group only. # chmod u+rwxs,g+rx-w,o-rwx dbfs_client Create a symbolic link to dbfs_client in /sbin as mount.dbfs. $ ln -s $ORACLE_HOME/bin/dbfs_client /sbin/mount.dbfs Create a new Linux group called fuse. Add the Linux user that is running the DBFS Client to the fuse group. Add the following line to /etc/fstab: /sbin/mount.dbfs#db_user@db_server mount_point fuse rw,user,noauto 0 0 For example: /sbin/mount.dbfs#/@DBConnectString /mnt/dbfs fuse rw,user,noauto 0 0 The Linux user can mount the DBFS file system using the standard Linux mount command. For example: $ mount /mnt/dbfs Note that FUSE does not currently support automount.
DBFS Client使用Oracle Wallet的方法
An Oracle Wallet allows the DBFS client to mount a DBFS store without the user having to enter a password. Please refer to Oracle Database Advanced Security Administrator's Guide for more information about creation and management of wallets. The "/@" syntax means to use the wallet.
To create an Oracle Wallet:
Create a directory for the wallet. For example:
mkdir $ORACLE_HOME/oracle/wallet
Create an auto-login wallet.
mkstore -wrl $ORACLE_HOME/oracle/wallet -create
Add the wallet location in the client's sqlnet.ora file:
vi $TNS_ADMIN/sqlnet.ora
WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY =
$ORACLE_HOME/oracle/wallet) ) )
Add the following parameter in the client's sqlnet.ora file:
vi $TNS_ADMIN/sqlnet.ora
SQLNET.WALLET_OVERRIDE = TRUE
Create credentials:
mkstore -wrl wallet_location -createCredential db_connect_string username password
For example:
mkstore -wrl $ORACLE_HOME/oracle/wallet -createCredential DBConnectString scott tiger
Add the connection alias to your tnsnames.ora file.
Use you can use dbfs_client with Oracle Wallet.
For example:
$ dbfs_client -o wallet /@DBConnectString /mnt/dbfs
常用视图
SQL> select pathname from dbfs_content;
PATHNAME
--------------------------------------------------------------------------------
/dbfs
/dbfs/ohsdba/passwd
/dbfs/ohsdba/hosts
/dbfs/ohsdba
/dbfs/.sfs/tools
/dbfs/.sfs/snapshots
/dbfs/.sfs/content
/dbfs/.sfs/attributes
/dbfs/.sfs/RECYCLE
/dbfs/.sfs
SQL>
DBFS$_MOUNTS
DBFS$_STATS
DBFS$_STORES
DBFS_CONTENT
DBFS_CONTENT_PROPERTIES
DBFS_CONTEXT
DBFS_SFS$_FS
DBFS_SFS$_FSSEQ
DBFS_SFS$_FST
DBFS_SFS$_FSTO
DBFS_SFS$_FSTP
DBFS_SFS$_SNAP
DBFS_SFS$_TAB
DBFS_SFS$_VOL
DBFS_CONTENT
desc dbms_dbfs_sfs
FUNCTION LISTFILESYSTEMS RETURNS FILESYSTEMS_T
FUNCTION LISTSNAPSHOTS RETURNS SNAPSHOTS_T
FUNCTION LISTTABLES RETURNS TABLES_T
FUNCTION LISTVOLUMES RETURNS VOLUMES_T
select * from table(dbms_dbfs_sfs.listTables);
select * from table(dbms_dbfs_sfs.listfilesystems);
desc dbms_dbfs_content
FUNCTION LISTMOUNTS RETURNS MOUNTS_T
FUNCTION LISTSTORES RETURNS STORES_T
FUNCTION LISTALLCONTENT RETURNS PATH_ITEMS_T
FUNCTION LISTALLPROPERTIES RETURNS PROP_ITEMS_T
select * from table(dbms_dbfs_content.listStores);
select * from table(dbms_dbfs_content.listMounts);
Reference
ACFS Guie
http://docs.oracle.com/cd/E11882_01/server.112/e18951/asmfilesystem.htm#OSTMG31000
DBFS Guide
Database SecureFiles and Large Objects Developer's Guide
http://docs.oracle.com/cd/E11882_01/appdev.112/e18294/toc.htm
http://docs.oracle.com/cd/E11882_01/appdev.112/e18294/adlob_client.htm#ADLOB0006