ASM diskgroup usage

To monitor ASM Diskgroup Usage

SET LINESIZE  145
SET PAGESIZE  9999
SET VERIFY    off
COLUMN group_name             FORMAT a20           HEAD 'Disk Group|Name'
COLUMN sector_size            FORMAT 99,999        HEAD 'Sector|Size'
COLUMN block_size             FORMAT 99,999        HEAD 'Block|Size'
COLUMN allocation_unit_size   FORMAT 999,999,999   HEAD 'Allocation|Unit Size'
COLUMN state                  FORMAT a11           HEAD 'State'
COLUMN type                   FORMAT a6            HEAD 'Type'
COLUMN total_mb               FORMAT 999,999,999   HEAD 'Total Size (MB)'
COLUMN used_mb                FORMAT 999,999,999   HEAD 'Used Size (MB)'
COLUMN pct_used               FORMAT 999.99        HEAD 'Pct. Used'



break on report on disk_group_name skip 1
compute sum label "Grand Total: " of total_mb used_mb on report


SELECT
    name                                     group_name
  , sector_size                              sector_size
  , block_size                               block_size
  , allocation_unit_size                     allocation_unit_size
  , state                                    state
  , type                                     type
  , total_mb                                 total_mb
  , (total_mb - free_mb)                     used_mb
  , ROUND((1- (free_mb / total_mb))*100, 2)  pct_used
FROM
    v$asm_diskgroup
ORDER BY
    name
/

Credits: Gavin Soorma



Monitoring using OEM

select collection_timestamp ,key_value DISK_GROUP,round(value/1024/1024,2) "Free TB",round((round(value/1024/1024,2)/7.57*100),2) "Used Space"
from
(select distinct target_name,target_type,target_guid from mgmt_targets where target_type='osm_cluster' and target_name='+ASM_xxxipcva-clus')
join (
 select distinct
  target_type,metric_name,metric_label,metric_column,column_label,short_name,metric_guid
 from mgmt_metrics
) using(target_type)
join mgmt_metrics_raw using(target_guid,metric_guid)
where key_value = 'RECO_DG' and collection_timestamp >= sysdate-0.5/24 and metric_label like '%Usage' and column_label like '%Usable Free%' --and ROWNUM <= 5
order by collection_timestamp desc ;

Disable logging on Oracle objects and database

ALTER TABLE tablename MODIFY PARTITION 2016dec NOLOGGING;

Alter table table_name nologging;

select table_name, logging  from dba_tables where table_name like 'test%';

select partition_name,logging from DBA_TAB_PARTITIONS where table_name='tablename';


ALTER TABLE tablename MODIFY LOB (USER_DATA) (NOCACHE NOLOGGING);


To enable force logging on db

ALTER DATABASE force logging;

ALTER TABLESPACE users FORCE LOGGING;

To disable:

ALTER DATABASE no force logging;

ALTER TABLESPACE users NO FORCE LOGGING;

Configuring DBFS on Exadata

Requirements:

Fuse, Fuse libs, Kernel Devel package, Repository database with big file tablespace

Install Fuse, Fuse libs and Kernel Devel package

Run all the steps below until directory creation on both the database nodes.

# cd /etc/yum.repos.d

Verify if you have public repository is present. If not wget it. Or else when ever you try to run install command it says nothing to do

# /usr/openv/pdde/pdopensource/bin/wget http://public-yum.oracle.com/public-yum-ol6.repo

After the above is added run the below to install fuse and kernel-devel

# yum install fuse fuse-libs kernel-devel

Once installed you will get

Setting up Install Process
Package fuse-2.9.4-1.0.1.el6.x86_64 already installed and latest version
Package fuse-libs-2.9.4-1.0.1.el6.x86_64 already installed and latest version
Package kernel-devel-2.6.32-642.11.1.el6.x86_64 already installed and latest version
Nothing to do


Optional - Dos2Unix

# sudo yum install dos2unix

Create the mount directory

# mkdir /dbfs_mnt
# chown oracle:dba /dbfs_mnt

*****************************************************************

Create Repository Database testdbfs

Create tablespace and dbfs_user

CREATE BIGFILE TABLESPACE DBFS_DATA DATAFILE '+DATA_DG' SIZE 1T AUTOEXTEND OFF NOLOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

CREATE USER DBFS_USER IDENTIFIED BY "Password" DEFAULT TABLESPACE DBFS_DATA TEMPORARY TABLESPACE TEMP;
 
grant create session, resource, create view, dbfs_role to dbfs;

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

-- Here dbfs_Data is tablespace name and oracle is the folder name that will be created inside dbfs_mnt directory

@$ORACLE_HOME/rdbms/admin/dbfs_create_filesystem dbfs_data oracle


*****************************************************************
Run on Both nodes

Configure Shared Libraries
# 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
# ln -s /lib64/libfuse.so.2 libfuse.so
# ldconfig
# ldconfig -p | grep fuse
# ln -s $ORACLE_HOME/bin/dbfs_client /sbin/mount.dbfs

# echo user_allow_other > /etc/fuse.conf
# chmod 644 /etc/fuse.conf

*****************************************************************

Download and modify mount-dbfs.zip from oracle website (Configuring DBFS on Oracle Exadata Database Machine (Doc ID 1054431.1)

Copy this zip to tmp folder on one of the server

# unzip mount-dbfs-20160215.zip
Archive:  mount-dbfs-20160215.zip
  inflating: mount-dbfs.conf
  inflating: mount-dbfs.sh
# dos2unix mount-dbfs.conf
dos2unix: converting file mount-dbfs.conf to UNIX format ...
# dos2unix mount-dbfs.sh
dos2unix: converting file mount-dbfs.sh to UNIX format ...

You will have two files

1)mount-dbfs.sh
2)mount-dbfs.conf

We need to modify following values in conf file and leave the others as-is

DBNAME=testdbfs

MOUNT_POINT=/dbfs_mnt

DBFS_USER=dbfs_user

ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/dbhome_1

GRID_HOME=/u01/app/11.2.0.4/grid

DBFS_PASSWD=Password

Once modified we need to copy these files to their respective locations on both nodes

using of dcli command here will copy to both nodes. So you dont need  to repeat the same

Create a group called dbs_group in root home directory and include the 2 nodes
# cat dbs_group
10.111.11.111
10.111.11.222

# dcli -g ~/dbs_group -l root -d /u01/app/11.2.0.4/grid/crs/script/ -f /tmp/mount-dbfs.sh
# dcli -g ~/dbs_group -l root chown oracle:dba /u01/app/11.2.0.4/grid/crs/script/mount-dbfs.sh
# dcli -g ~/dbs_group -l root chmod 750 /u01/app/11.2.0.4/grid/crs/script/mount-dbfs.sh
# dcli -g ~/dbs_group -l root -d /etc/oracle -f /tmp/mount-dbfs.conf
# dcli -g ~/dbs_group -l root chmod 640 /etc/oracle/mount-dbfs.conf

Once copied verify all the locations and check permissions.

Now test the functionality to see if mounts are starting properly
$ cd $GRID_HOME/crs/scripts/
$ ./mount-dbfs.sh start
$ ./mount-dbfs.sh status
$ ./mount-dbfs.sh stop

When you start you can verify if the mount is up by checking df -kh . This should list the new mount
Once verified now its time to add to cluster
*****************************************************************
Create a shell script on one of the nodes and run it
$ vi add-dbfs-resource.sh
##### start script add-dbfs-resource.sh
#!/bin/bash
ACTION_SCRIPT=/u01/app/11.2.0.4/grid/crs/script/mount-dbfs.sh
RESNAME=dbfs_mnt
DBNAME=testdbfs
DBNAMEL=`echo $DBNAME | tr A-Z a-z`
ORACLE_HOME=/u01/app/11.2.0.4/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

Run it as Oracle user

$ add-dbfs-resource.sh

Now verify using to see if the mount shows up in resources

crsctl status res -t

To start the mounts

crsctl start resource dbfs_mnt

To stop the mounts

crsctl stop resource dbfs_mnt -f

*****************************************************************

Note: When stopping make sure no session is open and pointing to the folder. If you are inside the folder and try to unmount you get error saying Busy.

If you try to stop the database when mounts are online you will get error as there is dependencies. To shut down we need to specify force option

After eveything is mounted the filesystem permissions will be changed back to root:root

srvctl start database -d testdbfs -f

You can also unmount using following

fusermount -u /dbfs_mnt

To force

umount -l  /dbfs_mnt
fusermount -uz /dbfs_mnt

*****************************************************************
To drop filesystem

@dbfs_drop_filesystem.sql oracle

*****************************************************************
Testing mounts if working properly

$ # Connection prompts for password and holds session.
$ dbfs_client dbfs_user@testdbfs /dbfs_mnt

$ # Connection retrieves password from file and releases session.
echo Password > pw.f
$ nohup dbfs_client dbfs_user@testdbfs /dbfs_mnt  < pw.f &
*****************************************************************

For debugging

Please refer to
http://www.hhutzler.de/blog/debugging-prolbems-when-mounting-a-dbfs/

$ means oracle user
# means root user

Run SQL statements in background UNIX

To run sql statements in background

Copy all the sql statements to a file name test.sql on the unix server

Then run the following using nohup

nohup sqlplus / as sysdba @test.sql &

or

nohup sqlplus USERNAME/password@DBNAME @test.sql &

view output in nohup.out