Database Administration: January 2016

All steps and information below derived from Oracle Doc ID (1054431.1) with great details. I just wanted to reflect my own experience with DBFS configuration.

Create database group to facilitate your tasks

[root@ttdbadm01 ~]# cat /home/oracle/dbs_group

ttdbadm01

ttdbadm02

Prerequisites steps:

[root@ttdbadm01 ~]# id oracle

uid=1001(oracle) gid=1001(oinstall) groups=1001(oinstall),1004(asmdba),1002(dba),1003(racoper)

[root@ttdbadm01 ~]# dcli -g ~/dbs_group -l root usermod -a -G fuse oracle

[root@ttdbadm01 ~]# id oracle

uid=1001(oracle) gid=1001(oinstall) groups=499(fuse),1001(oinstall),1004(asmdba),1002(dba),1003(racoper)

[root@ttdbadm01 ~]# dcli -g ~/dbs_group -l root "echo user_allow_other > /etc/fuse.conf"

[root@ttdbadm01 ~]# dcli -g ~/dbs_group -l root chmod 644 /etc/fuse.conf

[root@ttdbadm01 ~]# dcli -g ~/dbs_group -l root mkdir /dbfs_direct

[root@ttdbadm01 ~]# dcli -g ~/dbs_group -l root chown oracle:dba /dbfs_direct

Create new repository database as instructed in Oracle Doc ID (1191144.1):

1. Invoke DBCA

2. Choose Real Application Clusters database.

3. Choose Create a Database

4. Choose the General Purpose or Transaction Processing template (include datafiles)

5. Choose Admin-Managed and select all nodes

6. Name your database (e.g. fsdb)

7. Optionally configure Enterprise Manager and automatic maintenance tasks as per site requirements

8. Choose Automatic Storage Management and Oracle-Managed Files

9. Choose the desired Disk Group. In general, DBFS_DG is adequate for DBFS purposes

10. De-select Flash Recovery Area

11. Do not select Enable Archiving. Archivelog mode is not necessary for this use case.

12. In the Memory Tab:

12.1 Choose Custom and then Automatic Shared Memory Management

12.2 Set SGA size by entering 1536 (and choose M Bytes for units) in the SGA box and enter 6656 (and choose M Bytes for units) in the PGA Size box

Note: Required SGA size changes depending on versions and if too low, you will see an ora-0431 (SGA is set to 1536m for 12.1.0.2.) If this message is seen increase sga to 2048m then the db will start without issues.

13. In the Character Sets tab choose AL32UTF8 as the Database Character Set

14. Open the All Initialization Parameters dialogue. Select "Show Advanced Parameters". Scroll down to parallel_max_servers and enter "2" under the Value column.

Create new tablespace on the newly created repository database.

SQL> create bigfile tablespace dbfsts datafile '+DBFS_DG' size 75g autoextend on next 8g maxsize 300g NOLOGGING EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO ;

Tablespace created.

Create new user for DBFS and grant appropriate privileges.

SQL> create user dbfs_user identified by dbfs_passwd default tablespace dbfsts quota unlimited on dbfsts;

User created.

SQL> grant create session, create table, create view, create procedure, dbfs_role to dbfs_user;

Grant succeeded.

Create dbfs database objects:

[oracle@ttdbadm01 ~]$ cd $ORACLE_HOME/rdbms/admin

[oracle@ttdbadm01 admin]$ sqlplus dbfs_user

SQL*Plus: Release 11.2.0.4.0 Production on Sun Jan 3 14:32:11 2016

Enter password:

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> start dbfs_create_filesystem dbfsts FS1

No errors.

--------

CREATE STORE:

begin dbms_dbfs_sfs.createFilesystem(store_name => 'FS_FS1', tbl_name =>

'T_FS1', tbl_tbs => 'dbfsts', lob_tbs => 'dbfsts', do_partition => false,

partition_key => 1, do_compress => false, compression => '', do_dedup => false,

do_encrypt => false); end;

--------

begin dbms_dbfs_content.registerStore(store_name=> 'FS_FS1', provider_name =>

'sample1', provider_package => 'dbms_dbfs_sfs'); end;

--------

MOUNT STORE:

begin dbms_dbfs_content.mountStore(store_name=>'FS_FS1', store_mount=>'FS1');

end;

--------

CHMOD STORE:

declare m integer; begin m := dbms_fuse.fs_chmod('/FS1', 16895); end;

No errors.

Download mount-dbfs-20151013.zip from Oracle Doc ID (1054431.1) and place it to the server, then do the following:

[oracle@ttdbadm01 tmp]$ unzip mount-dbfs-20151013.zip

Archive: mount-dbfs-20151013.zip

inflating: mount-dbfs.conf

inflating: mount-dbfs.sh

Ensure from the file transfer

[oracle@ttdbadm01 tmp]$ dos2unix mount-dbfs.conf

dos2unix: converting file mount-dbfs.conf to UNIX format ...

[oracle@ttdbadm01 tmp]$ dos2unix mount-dbfs.sh

dos2unix: converting file mount-dbfs.sh to UNIX format ...

Open and Edit the file mount-dbfs.conf to reference all appropriate values such as (dbname, mountpoint, dbfsuser, ORACLE_HOME, GI_HOME..etc), ignoring any value related to WALLET or PDB since I’m not going to use them.

[oracle@ttdbadm01 tmp]$ vi mount-dbfs.conf

Copy mount-dbfs.sh and mount-dbfs.conf GI_HOME/crs/script and /etc/oracle respectively and then set the proper permissions.

[root@ttdbadm01 ~]# dcli -g ~/dbs_group -l root -d /u01/app/11.2.0.4/grid/crs/script/ -f /tmp/mount-dbfs.sh

[root@ttdbadm01 ~]# dcli -g ~/dbs_group -l root chown oracle:dba /u01/app/11.2.0.4/grid/crs/script/mount-dbfs.sh

[root@ttdbadm01 ~]# dcli -g ~/dbs_group -l root chmod 750 /u01/app/11.2.0.4/grid/crs/script/mount-dbfs.sh

[root@ttdbadm01 ~]# dcli -g ~/dbs_group -l root -d /etc/oracle -f /tmp/mount-dbfs.conf

[root@ttdbadm01 ~]# dcli -g ~/dbs_group -l root chown oracle:dba /etc/oracle/mount-dbfs.conf

[root@ttdbadm01 ~]# dcli -g ~/dbs_group -l root chmod 640 /etc/oracle/mount-dbfs.conf

Create and execute add-dbfs-resource.sh file to register clusterware resource.

[oracle@ttdbadm01 ~]$ vi add-dbfs-resource.sh

##### start script add-dbfs-resource.sh
#!/bin/bash
ACTION_SCRIPT=/u01/app/11.2.0/grid/crs/script/mount-dbfs.sh
RESNAME=dbfs_mount
DBNAME=fsdb
DBNAMEL=`echo $DBNAME | tr A-Z a-z`
ORACLE_HOME=/u01/app/11.2.0/grid
PATH=$ORACLE_HOME/bin:$PATH
export PATH ORACLE_HOME
crsctl add resource $RESNAME \
-type local_resource \
-attr "ACTION_SCRIPT=$ACTION_SCRIPT, \
         CHECK_INTERVAL=30,RESTART_ATTEMPTS=10, \
         START_DEPENDENCIES='hard(ora.$DBNAMEL.db)pullup(ora.$DBNAMEL.db)',\
         STOP_DEPENDENCIES='hard(ora.$DBNAMEL.db)',\
         SCRIPT_TIMEOUT=300"
##### end script add-dbfs-resource.sh

[oracle@ttdbadm01 ~]$ sh ./add-dbfs-resource.sh

Check status of dbfs_mount

[grid@ttdbadm01 bin]$ ./crsctl stat res dbfs_mount -t

--------------------------------------------------------------------------------

NAME TARGET STATE SERVER STATE_DETAILS

--------------------------------------------------------------------------------

Local Resources

--------------------------------------------------------------------------------

dbfs_mount

OFFLINE OFFLINE ttdbadm01

OFFLINE OFFLINE ttdbadm02

Please note that you must grant oracle execution privilege to Oracle user in order to start dbfs_mount resource, otherwise you will experience issues like:

CRS-4000: Command Start failed, or completed with errors.

Execute the following before attempting to star dbfs_mount

dcli -g ~/dbs_group -l root chmod +x /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/dbfs_client

As Oracle user start dbfs_mount resource

[oracle@ttdbadm01 bin]$ ./crsctl start resource dbfs_mount

CRS-2672: Attempting to start 'dbfs_mount' on 'ttdbadm01'

CRS-2672: Attempting to start 'dbfs_mount' on 'ttdbadm02'

CRS-2676: Start of 'dbfs_mount' on 'ttdbadm01' succeeded

CRS-2676: Start of 'dbfs_mount' on 'ttdbadm02' succeeded

[oracle@ttdbadm01 bin]$ ./crsctl stat res dbfs_mount -t

--------------------------------------------------------------------------------

NAME TARGET STATE SERVER STATE_DETAILS

--------------------------------------------------------------------------------

Local Resources

--------------------------------------------------------------------------------

dbfs_mount

ONLINE ONLINE ttdbadm01

ONLINE ONLINE ttdbadm02

[oracle@ttdbadm01 bin]$ df -h

Filesystem Size Used Avail Use% Mounted on

/dev/mapper/VGExaDb-LVDbSys1

30G 24G 4.4G 85% /

tmpfs 252G 8.2G 244G 4% /dev/shm

/dev/sda1 504M 38M 441M 8% /boot

/dev/mapper/VGExaDb-LVDbOra1

99G 90G 3.8G 96% /u01

dbfs-dbfs_user@:/ 100G 120K 100G 1% /dbfs_direct

Database Administration

Monday, January 4, 2016

Configuring DBFS on Oracle Exadata Database Machine

About Me