11g dbfs配置

参考文档

Configuring DBFS on Oracle Database Machine (Doc ID 1054431.1)
Oracle? Database SecureFiles and Large Objects Developer’s Guide 11g Release 2 (11.2) – 6 DBFS File System Client
https://docs.oracle.com/cd/E11882_01/appdev.112/e18294/adlob_client.htm#ADLOB0006
https://oracle-base.com/articles/11g/dbfs-11gr2#mounting_a_file_system
https://www.hhutzler.de/blog/debugging-prolbems-when-mounting-a-dbfs/

在DBFS中，Server为Oracle数据库，文件存储在表的一个SecureFiles LOBs 字段里面。
DBFS目录库允许每个数据库用户创建一个或多个文件系统，并且可以被clients使用。每个文件系统拥有专有的数据表，保证文件系统的内容。

一、On dbserver

1、创建用户

create bigfile tablespace dbfsts datafile '+DBFS_DG' size 32g autoextend on next 8g maxsize 300g NOLOGGING EXTENT MANAGEMENT LOCAL AUTOALLOCATE  SEGMENT SPACE MANAGEMENT AUTO ;
create user dbfs_user identified by dbfs_passwd default tablespace dbfsts quota unlimited on dbfsts;
grant create session, create table, create view, create procedure, dbfs_role to dbfs_user;

2、创建dbfs文件系统

$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus dbfs_user/dbfs_passwd

SQL> @?/rdbms/admin/dbfs_create_filesystem dbfsts queue
  --dbfsts:    <tablespace> for the DBFS database objects
  --queue:     <filesystem name>, this can be any string and will appear as a directory under the mount point

No errors.
--------
CREATE STORE:
begin dbms_dbfs_sfs.createFilesystem(store_name => 'FS_QUEUE', tbl_name => 'T_QUEUE', tbl_tbs => 'dbfsts', lob_tbs => 'dbfsts', 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_QUEUE', provider_name => 'sample1', provider_package => 'dbms_dbfs_sfs'); end;
--------
MOUNT STORE:
begin dbms_dbfs_content.mountStore(store_name=>'FS_QUEUE', store_mount=>'queue'); end;
--------
CHMOD STORE:
declare m integer; begin m := dbms_fuse.fs_chmod('/queue', 16895); end;
No errors.

dbfs_create_filesystem.sql 实际上是调用了一些 package，创建了 DBFS 文件系统。
而这种默认方式对于 Securefile LOB 的一些特性都是没有启用的，比如压缩，去重，分区，加密等，如果要启用这些特性，可以使用 dbfs_create_filesystem_advanced.sql

3、如何Dropping a File System

客户端先umount

# umount /queue

然后在数据库中删除

$ sqlplus dbfs_user/dbfs_passwd
SQL> @$ORACLE_HOME/rdbms/admin/dbfs_drop_filesystem.sql  <filesystem name>

！！注意有个bug：Drop DBFS with dbfs_drop_filesystem gives error PLS-103: Encountered the symbol "CREATE" (Doc ID 1935011.1)

4、dbfs相关的视图

SQL> conn dbfs_user/dbfs_passwd
SQL> select * from DBFS_CONTENT_PROPERTIES;
SQL> select * from sys.dbfs_content;
SQL> select * from table(dbms_dbfs_content.listallcontent);
SQL> select * from table(dbms_dbfs_content.listallproperties);
SQL> select * from table(dbms_dbfs_content.listStores);
SQL> select * from table(dbms_dbfs_sfs.listFilesystems);
SQL> select * from table(dbms_dbfs_sfs.listTables);

二、On Client

1、创建目录

# usermod -a -G fuse oracle
# echo "user_allow_other" > /etc/fuse.conf"
# chmod 644 /etc/fuse.conf

# mkdir /dbfs_direct
# chown oracle:dba /dbfs_direct

$ cat /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/tnsnames.ora
tns_orcl =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ex01-scan)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

2、安装fuse软件包

On platforms other than Oracle Database Machine, additional setup steps may be required to install the required fuse RPM packages which are installed by default on Oracle Database Machine database servers.

我直接拿系统的ISO盘来装

# rpm -ivh kernel-devel-3.10.0-862.el7.x86_64.rpm
# rpm -ivh fuse-libs-2.9.2-10.el7.x86_64.rpm
# rpm -qa|grep kernel-devel
kernel-devel-3.10.0-862.el7.x86_64
# rpm -qa|grep fuse
fuse-libs-2.9.2-10.el7.x86_64
fuse-2.9.2-10.el7.x86_64

--Login as root user. Add a new library path.
# echo "/usr/local/lib" >> /etc/ld.so.conf.d/usr_local_lib.conf

--Change directory to lib, and create the following symbolic links to the libclntsh.so.11.1 and libnnz11.so libraries.
# cd /usr/local/lib
# export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
# ln -s $ORACLE_HOME/lib/libclntsh.so.11.1
# ln -s $ORACLE_HOME/lib/libnnz11.so

--Locate libfuse.so, and create a symbolic link this library.
# locate libfuse.so
  determined_path/libfuse.so
OR
# ldconfig -p|grep fuse
        libfuse.so.2 (libc6,x86-64) => /lib64/libfuse.so.2
OR
# find ./ -name libfuse.so*
./usr/openv/pdde/vpfs/lib/libfuse.so.2
./usr/openv/pdde/vpfs/lib/libfuse.so.2.9.7
./usr/openv/pdde/vpfs/lib/libfuse.so
./usr/lib64/libfuse.so.2
./usr/lib64/libfuse.so.2.9.2

# cd /usr/local/lib
ln -s /lib64/libfuse.so.2 libfuse.so.2
ln -s /lib64/libfuse.so.2 libfuse.so
# ls -l
total 0
lrwxrwxrwx 1 root root 54 Dec 16 15:22 libclntsh.so.11.1 -> /u01/app/oracle/product/11.2.0.4/lib/libclntsh.so.11.1
lrwxrwxrwx 1 root root 23 Dec 16 15:26 libfuse.so -> /lib64/libfuse.so.2
lrwxrwxrwx 1 root root 23 Dec 16 15:26 libfuse.so.2 -> /lib64/libfuse.so.2
lrwxrwxrwx 1 root root 48 Dec 16 15:22 libnnz11.so -> /u01/app/oracle/product/11.2.0.4/lib/libnnz11.so

--Run ldconfig to create the links and cache for the new symbolic links.
# ldconfig

3、挂载目录

# mkdir /queue
# chown oracle:dba /queue
# su - oracle
$ echo "dbfs_passwd" > dbfs_user.pw
$ nohup dbfs_client dbfs_user@tns_orcl -o allow_other /dbfs_direct < /home/oracle/dbfs_user.pw &

//dbfs_client db_user@db_server [-o option_1 -o option_2 ...] mount_point
//allow_other: To allow other users to access the filesystem. You must set the user_allow_other parameter in the /etc/fuse.conf configuration file.

$ df -h
Filesystem                 Size  Used Avail Use% Mounted on
dbfs-dbfs_user@tns_dbfs:/  300G  160K  300G   1% /queue