Focus On Oracle

Installing, Backup & Recovery, Performance Tuning,
Troubleshooting, Upgrading, Patching

Oracle Engineered System


当前位置: 首页 » 技术文章 » Oracle

Oracle ACFS and DBFS(二)

如何配置DBFS
首 先我们需要安装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

关键词:asm 

相关文章

Oracle ASM from 10g to 18c
在18c中通过ASM Flex DiskGroup克隆PDB
Exadata and ASM
Oracle ASM Storage Limits
12c 如何将Standard ASM转化为Flex ASM
How to use amdu(ASM Metadata Dump Utility)
How to use kfed(Kernel Files metadata Editor)
How to use kfod(Kernel Files OSM Disk)
如何计算ASM磁盘头自动备份的位置
What is disk_repair_time?
Find block in ASM
12c新特性ASMFD
Top