Configuring DBFS on Oracle Exadata Database Machine

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

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

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;

--------

REGISTER STORE:

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

While scheduling backup job  through OEM, I've encountered "ora-20446 the owner of the job is not registered". as workaround from metalink simply execute the following command using sysman user:


sqlplus sysman SQL*Plus: Release 11.2.0.1.0 Production on Thu Nov 13 12:14:41 2014 Copyright (c) 1982, 2009, Oracle. All rights reserved. Enter password: Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SYSMAN@ feco1>execute MGMT_USER_MAKE_EM_USER('DB_USER');


while DB_USER is the user that you use to connect to OEM.



Reference:
Doc ID 463874.1

Change SYS password

It's being long time writing a post in my blog ..

I've inherited newly installed RAC four nodes without OEM configuration and without SYS password which required to configure OEM.

Generally this post for how to change the sys password in RAC environment which seems very simple at the first instance but it has a trick. The trick is when you change the sys password in one instance, it doesn't reflect on other instances unlike any other user so you need to change it in all instances as follows:

SYS@ feco1> alter user sys identified by newPwd;

User altered.

Then login to node2 and do the same

SYS@ feco2> alter user sys identified by newPwd;

User altered.

and do so in all your RAC nodes.

the other solution which is the only one for 10g is to change the password in one instance and then copy your password file (orapw<instance_name>) located in '$ORACLE_HOME/dbs' to other nodes and amend the name accordingly,

Change Flash Recovery Area Location

Due to insufficient space on some drives/mount point, you need to change the location of fast/flash recovery area.

Old Location: D:\flash_recovery_area

New Location: E:\flash_recovery_area

ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='E:\flash_recovery_area' SCOPE=BOTH SID='*';

After change this parametr, all new archive log will create on the new location and then database going to remove old archive log files once aged out.

In the case that you need to your old files (archive log, backupset, datafile image copy) do the following:

for archivelog transmission:

RMAN> BACKUP AS COPY ARCHIVELOG ALL DELETE INPUT;

 for backupset transmission:

RMAN> BACKUP DEVICE TYPE DISK BACKUPSET ALL DELETE INPUT;

for datafile image copy transmission:

RMAN> BACKUP AS COPY DATAFILECOPY ALL DELETE INPUT;

Physical Standby vs Logical Standby

Physical Standby	Logical Standby
Complete (Block-based) copy of the primary database.	Can be a complete or partial copy of primary DB.
Use Redo Apply method to apply changes.	Redo data is first converted into SQL statements and then applied to the standby database.
Redo Apply uses Managed Recovery Process (MRP) in order to manage application of the change in information on redo.	The Logical Standby Process (LSP) process manages the application of changes to a logical standby database.
In 11g, a physical standby database can be accessible in read-only mode while Redo Apply is working (Active Data Guard).	This method makes it possible to access the standby database permanently and allows read/write while the replication of data is active.
Unable to change anything on DB structure.	Read-Write mode enables you to create as many object as you need.
Supports all the data types.	One discouraging aspect of the logical standby database is the unsupported data types, objects, and DDLs.
Active Data Guard comes with extra cost.	Free of cost
Easy to configure, manage and less or no performance issues.	More steps to configure, complex to manage and more performance issues.

OCR Backups Filling Up CRS_HOME Space

While checking my OCR backup I wondered why Oracle keep creating OCR backup without deleting the old ones. Should I delete them by my own?.. it sounds unkind!!.

I start searching on Google and metaling.. Guess what??? It’s a BUG.

That simply means, if you have dozens of OCR backups you may facing the same bug. Since the OCR backups should consist of 7 files as follow:

#ls -ltr u02/app/11.2.0/grid/cdata/feco-cluster/day* week* backup0*

-rw-------    1 root     system      7028736 May 22 2011  backup02.ocr

-rw-------    1 root     system      7028736 May 22 2011  backup01.ocr

-rw-------    1 root     system      7028736 May 22 2011  backup00.ocr

-rw-------    1 root     system      6979584 May 22 2011  week.ocr

-rw-------    1 root     system      7028736 May 22 2011  day_.ocr

-rw-------    1 root     system      7028736 May 22 2011  day.ocr

presenting the result of :

# ./ocrconfig -showbackup auto

feco04     2014/05/24 12:31:09     /u02/app/11.2.0/grid/cdata/feco-cluster/backup00.ocr

feco04     2014/05/24 08:31:07     /u02/app/11.2.0/grid/cdata/feco-cluster/backup01.ocr

feco04     2014/05/24 04:31:06     /u02/app/11.2.0/grid/cdata/feco-cluster/backup02.ocr

feco04     2014/05/23 04:31:00     /u02/app/11.2.0/grid/cdata/feco-cluster/day.ocr

feco04     2014/05/14 04:29:51     /u02/app/11.2.0/grid/cdata/feco-cluster/week.ocr

My current ocr backup look like:

-rw-------    1 root     system      7221248 Jun 24 2013  13458777.ocr

-rw-------    1 root     system      7221248 Jun 24 2013  14872007.ocr

-rw-------    1 root     system      7221248 Jun 24 2013  28713517.ocr

-rw-------    1 root     system      7221248 Jun 25 2013  39937824.ocr

-rw-------    1 root     system      7221248 Jun 25 2013  56169871.ocr

-rw-------    1 root     system      7221248 Jun 25 2013  58569011.ocr

:

:

-rw-------    1 root     system      7221248 Jun 27 2013  21069301.ocr

-rw-------    1 root     system      7221248 Jun 27 2013  11741111.ocr

-rw-------    1 root     system      7221248 Jun 27 2013  29616835.ocr

-rw-------    1 root     system      7221248 Jun 27 2013  19887106.ocr

-rw-------    1 root     system      7221248 Jun 28 2013  27246017.ocr

And crsd.log showing this error:

OCRSRV][9521]Failure in renaming file [/u02/app/11.2.0/grid/cdata/feco-cluster/25677603.ocr] to [/u02/app/11.2.0/grid/cdata/feco-cluster/backup00.ocr]

How to solve that???

According to (Doc ID 1191067.1) "change all 7 automatic backup files to be owned  by root:root with permission "-rw-------""

The current privilege of the 7 files are:

-rw-r--r--    1 grid     oinstall      7028736 May 22 2011  backup02.ocr

-rw-r--r--    1 grid     oinstall      7028736 May 22 2011  backup01.ocr

-rw-r--r--    1 grid     oinstall      7028736 May 22 2011  backup00.ocr

-rw-r--r--    1 grid     oinstall      6979584 May 22 2011  week.ocr

-rw-r--r--    1 grid     oinstall      7028736 May 22 2011  day_.ocr

-rw-r--r--    1 grid     oinstall      7028736 May 22 2011  day.ocr 

Just change them to:

chown root:system week*

chown root:system day*

chown root:system backup0*

chmod 600 week*

chmod 600 day*

chmod 600 backup0*

:-)

Note:

My database version is : 11.2.0.2.0

Reference:

Automatic OCR Backup Filling Up <CRS_HOME>/cdata/<clustername> Directory (Doc ID 1191067.1)