11g dbfs配置

最新推荐文章于 2021-04-08 08:41:58 发布

刚烈的小马

最新推荐文章于 2021-04-08 08:41:58 发布

阅读量433

点赞数

分类专栏： oracle # 安装配置

本文链接：https://blog.csdn.net/myy10146/article/details/112051136

版权

oracle 同时被 2 个专栏收录

7 篇文章

订阅专栏

安装配置

3 篇文章

订阅专栏

本文档详细介绍了在Oracle数据库中配置和管理DBFS（数据库文件系统）的步骤，包括创建用户、建立DBFS文件系统、客户端的设置、挂载与卸载文件系统，以及相关视图的查询。DBFS将文件存储在SecureFilesLOBs字段，允许每个用户创建文件系统并供客户端使用。此外，还提到了启用文件系统特性的高级配置和解决遇到的问题。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

参考文档

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