To support all the greatest ASM cluster file system functionality in Oracle Database 11g Release 2, Oracle provides set of command-line tools available for all Unix and Windows platforms. Here’s a summary of all the available commands:

 
acfsdbg 			Debugs an Oracle ACFS file system. 
acfsutil info 	Displays various Oracle ACFS file system information. 
acfsutil registry 	Registers an Oracle ACFS file system with the Oracle ACFS mount registry. 
acfsutil rmfs 	Removes an Oracle ACFS file system. 
acfsutil size 	Resizes an Oracle ACFS file system. 
acfsutil snap create Creates a read-only snapshot of an Oracle ACFS file system. 
acfsutil snap delete Deletes a read-only snapshot of an Oracle ACFS file system. 
acfsutil tune 	Modifies or displays Oracle ACFS tuneables. 
advmutil tune 	Modifies or displays Oracle ADVM parameters. 
advmutil volinfo 	Displays information about Oracle ADVM volumes. 

Let’s start with the acfsutil command. If you are not logged in as root, acfsutil may not be in your path. acfs is located in the /sbin directory. If you are logged in as oracle, you will have to add /sbin to your environment $PATH variable or fully qualify the path of the executable.

You can get the complete list of all the option by displaying the help page with the -h or -help option as shown here:

 
[root@jin1 ~]# acfsutil -h

Usage: acfsutil [-h] command ...   # (Version 11.2.0.1.0.0)

             -h - help

 Command Subcmd Arguments
-------- ------ ----------------------------------------------------------------
    help                                 - display this message
 version                                 - display ACFS version information
    info fs                              - Display all ACFS file systems
    info fs     [<path> ...]             - Display specific ACFS file sys
    info fs     -o <item> <path>         - Display specific file sys item:
    info fs     -o                           freespace,    totalspace,
    info fs     -o                           mountpoints,  volumes,
    info fs     -o                           available,    ismountpoint,
    info fs     -o                           isavailable,  iscorrupt,
    info fs     -o                           isadvm,       diskgroup,
    info fs     -o                           redundancy,   resizeincrement,
    info fs     -o                           stripewidth,  stripecolumns,
    info fs     -o                           primaryvolume
    info file   <path> [<path> ...]      - Display specific file info
    info id     <file-identifier> <mountpoint> - 
    info id           Display the pathname associated with the given  
    info id           ACFS file identifier and mountpoint.
    rmfs        <device>                 - Remove unmounted ACFS file sys
    size        [-|+]nnn[K|M|G|T|P] [-d <device>] <path> - Resize file system
registry        [-l]                     - Display registry contents
registry        -l <mountpoint>          - Display a specific mount point
registry        -l <device>              - Display a specific device
registry        -m <device>              - Display a specific mount point associated with a device
registry        -a [-f] [-o <opts>] [-n <nodes>] <device> <mountpoint> - Add a mount point
registry        -d [<mountpoint> | <device>] - Delete a mount point or a device
    tune        AcfsMaxOpenFiles         - Maximum number of open files
    tune                                   (Windows only)
    snap create <snap_name> <mountpoint> - create a file system snapshot
    snap delete <snap_name> <mountpoint> - delete a file system snapshot
 
[root@jin1 ~]# acfsutil version
acfsutil version: 11.2.0.1.0.0
 
[root@jin1 ~]# acfsutil info fs
/apps/oracle/acfsmounts/data_vol_rpts
    ACFS Version: 11.2.0.1.0.0
    flags:        MountPoint,Available
    mount time:   Wed Sep  9 00:45:39 2009
    volumes:      1
    total size:   5368709120
    total free:   5317926912
    primary volume: /dev/asm/vol_rpts-340
        label:                 
        flags:                 Primary,Available,ADVM
        on-disk version:       39.0
        allocation unit:       4096
        major, minor:          252, 174081
        size:                  5368709120
        free:                  5317926912
        ADVM diskgroup         DATA
        ADVM resize increment: 268435456
        ADVM redundancy:       unprotected
        ADVM stripe columns:   4
        ADVM stripe width:     131072
    number of snapshots:  0
    snapshot space usage: 0
 
/apps/oracle/acfsmounts/data_vol_logfile
    ACFS Version: 11.2.0.1.0.0
    flags:        MountPoint,Available
    mount time:   Fri Sep 11 07:21:55 2009
    volumes:      1
    total size:   3221225472
    total free:   2990030848
    primary volume: /dev/asm/vol_logfile-340
        label:                 Log Volume
        flags:                 Primary,Available,ADVM
        on-disk version:       39.0
        allocation unit:       4096
        major, minor:          252, 174082
        size:                  3221225472
        free:                  2990030848
        ADVM diskgroup         DATA
        ADVM resize increment: 268435456
        ADVM redundancy:       unprotected
        ADVM stripe columns:   4
        ADVM stripe width:     131072
    number of snapshots:  3
    snapshot space usage: 106496
 
/apps/oracle/acfsmounts/lobdata
    ACFS Version: 11.2.0.1.0.0
    flags:        MountPoint,Available
    mount time:   Sat Sep 12 13:47:06 2009
    volumes:      1
    total size:   4294967296
    total free:   4248444928
    primary volume: /dev/asm/vol_lob-484
        label:                 
        flags:                 Primary,Available,ADVM
        on-disk version:       39.0
        allocation unit:       4096
        major, minor:          252, 247809
        size:                  4294967296
        free:                  4248444928
        ADVM diskgroup         LOBDATA
        ADVM resize increment: 268435456
        ADVM redundancy:       unprotected
        ADVM stripe columns:   4
        ADVM stripe width:     131072
    number of snapshots:  0
    snapshot space usage: 0
 
[root@jin1 ~]# acfsutil info fs /apps/oracle/acfsmounts/data_vol_rpts
/apps/oracle/acfsmounts/data_vol_rpts
    ACFS Version: 11.2.0.1.0.0
    flags:        MountPoint,Available
    mount time:   Wed Sep  9 00:45:39 2009
    volumes:      1
    total size:   5368709120
    total free:   5317926912
    primary volume: /dev/asm/vol_rpts-340
        label:                 
        flags:                 Primary,Available,ADVM
        on-disk version:       39.0
        allocation unit:       4096
        major, minor:          252, 174081
        size:                  5368709120
        free:                  5317926912
        ADVM diskgroup         DATA
        ADVM resize increment: 268435456
        ADVM redundancy:       unprotected
        ADVM stripe columns:   4
        ADVM stripe width:     131072
    number of snapshots:  0
    snapshot space usage: 0
 
[oracle@jin1 ~]$ /sbin/acfsutil info fs -o freespace -o totalspace /apps/oracle/acfsmounts/data_vol_rpts
5368709120
[oracle@jin1 ~]$ /sbin/acfsutil info fs -o freespace -o totalspace -o volumes /apps/oracle/acfsmounts/data_vol_rpts
1
[oracle@jin1 ~]$ /sbin/acfsutil info fs -o volumes /apps/oracle/acfsmounts/data_vol_rpts1
[oracle@jin1 ~]$ /sbin/acfsutil info fs -o redundancy  /apps/oracle/acfsmounts/data_vol_rpts
unprotected
[oracle@jin1 ~]$ /sbin/acfsutil info fs -o diskgroup  /apps/oracle/acfsmounts/data_vol_rpts
DATA
[oracle@jin1 ~]$ /sbin/acfsutil info fs -o mountpoints  /apps/oracle/acfsmounts/data_vol_rpts
/apps/oracle/acfsmounts/data_vol_rpts
[oracle@jin1 ~]$ /sbin/acfsutil info fs -o stripewidth  /apps/oracle/acfsmounts/data_vol_rpts
131072
[oracle@jin1 ~]$ /sbin/acfsutil info fs -o stripecolumns  /apps/oracle/acfsmounts/data_vol_rpts
4
[oracle@jin1 ~]$ /sbin/acfsutil info fs -o resizeincrement  /apps/oracle/acfsmounts/data_vol_rpts
268435456
[oracle@jin1 ~]$ /sbin/acfsutil info fs -o freespace  /apps/oracle/acfsmounts/data_vol_rpts
5317926912
 
[oracle@jin1 ~]$ /sbin/acfsutil info fs -o primaryvolume  /apps/oracle/acfsmounts/data_vol_rpts
/dev/asm/vol_rpts-340
[oracle@jin1 ~]$ /sbin/acfsutil info fs -o totalspace  /apps/oracle/acfsmounts/data_vol_rpts
5368709120

Shrink the file system by 2G

In this example, we will shrink the file system by 2G down to 3G:

 
[oracle@jin1 ~]$ /sbin/acfsutil size -2G -d /dev/asm/vol_rpts-340 /apps/oracle/acfsmounts/data_vol_rpts
acfsutil size: new file system size: 3221225472 (3072MB)

[oracle@jin1 ~]$ df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/VolGroup00-LogVol00
                      122G   11G  106G   9% /
/dev/sda1              99M   12M   82M  13% /boot
tmpfs                 1.1G  154M  874M  15% /dev/shm
.host:/               163G  149G   15G  92% /mnt/hgfs
/dev/asm/vol_rpts-340
                      3.0G   49M  3.0G   2% /apps/oracle/acfsmounts/data_vol_rpts
/dev/asm/vol_logfile-340
                      3.0G  221M  2.8G   8% /apps/oracle/acfsmounts/data_vol_logfile
/dev/asm/vol_lob-484  4.0G   45M  4.0G   2% /apps/oracle/acfsmounts/lobdata

Increase the file system by 2G

In this example, we will increase the file system by 2G and put the file system back to 5G.

 
[oracle@jin1 ~]$ /sbin/acfsutil size +2G -d /dev/asm/vol_rpts-340 /apps/oracle/acfsmounts/data_vol_rpts
acfsutil size: new file system size: 5368709120 (5120MB)

[oracle@jin1 ~]$ df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/VolGroup00-LogVol00
                      122G   11G  106G   9% /
/dev/sda1              99M   12M   82M  13% /boot
tmpfs                 1.1G  154M  874M  15% /dev/shm
.host:/               163G  149G   15G  92% /mnt/hgfs
/dev/asm/vol_rpts-340
                      5.0G   49M  5.0G   1% /apps/oracle/acfsmounts/data_vol_rpts
/dev/asm/vol_logfile-340
                      3.0G  221M  2.8G   8% /apps/oracle/acfsmounts/data_vol_logfile
/dev/asm/vol_lob-484  4.0G   45M  4.0G   2% /apps/oracle/acfsmounts/lobdata
 
[oracle@jin1 ~]$ /sbin/acfsutil registry -h
Usage: acfsutil [-h] registry [-l]                     - Display registry contents
                -l <mountpoint>          - Display a specific mount point
                -l <device>              - Display a specific device
                -m <device>              - Display a specific mount point associated with a device
                -a [-f] [-o <opts>] [-n <nodes>] <device> <mountpoint> - Add a mount point
                -d [<mountpoint> | <device>] - Delete a mount point or a device

[oracle@jin1 ~]$ /sbin/acfsutil registry -l
Device : /dev/asm/vol_rpts-340 : Mount Point : /apps/oracle/acfsmounts/data_vol_rpts : Options : none : Nodes : all : Disk Group : DATA : Volume : VOL_RPTS
Device : /dev/asm/vol_logfile-340 : Mount Point : /apps/oracle/acfsmounts/data_vol_logfile : Options : none : Nodes : all : Disk Group : DATA : Volume : VOL_LOGFILE

[oracle@jin1 ~]$ /sbin/acfsutil registry -l /apps/oracle/acfsmounts/data_vol_rpts
Device : /dev/asm/vol_rpts-340 : Mount Point : /apps/oracle/acfsmounts/data_vol_rpts : Options : none : Nodes : all : Disk Group : DATA : Volume : VOL_RPTS

[oracle@jin1 ~]$ /sbin/acfsutil registry -m /dev/asm/vol_rpts-340
/apps/oracle/acfsmounts/data_vol_rpts

[oracle@jin1 ~]$ /sbin/acfsutil registry -l /dev/asm/vol_rpts-340
Device : /dev/asm/vol_rpts-340 : Mount Point : /apps/oracle/acfsmounts/data_vol_rpts : Options : none : Nodes : all : Disk Group : DATA : Volume : VOL_RPTS
 
[oracle@jin1 ~]$ /sbin/acfsutil snap create -h
Usage: acfsutil [-h] snap create <snap_name> <mountpoint> - create a file system snapshot
[oracle@jin1 ~]$ /sbin/acfsutil snap create 13Sep2009_rpts /apps/oracle/acfsmounts/data_vol_rpts  
acfsutil snap create: Snapshot operation is complete.
Posted in ASM


Here’s a new version of the database growth report. The new version determines the growth trend for the database for the past one year. Check out this script:

set pages 255 lines 2000 trims on
compute sum of growth_in_gb on report
col week_number for a35
break on report
select to_char(creation_time, 'ww mon yyyy') week_number, round(sum(bytes)/1024/1024/1024,1) growth_in_gb
from sys.v_$datafile
where creation_time &gt; SYSDATE-365
group by to_char(creation_time, 'ww mon yyyy')
order by 1
/

Posted by Charles Kim, Oracle ACE Director


 

def S='&1'
def T='&2'
set lines 2000 pages 0 ver off echo off head off feed off
set newpage none
set trimspool on
set long 5000000
col output for a1000 word_wrapped
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SQLTERMINATOR',true);
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'STORAGE',false);

spool &T..sql
select dbms_metadata.get_ddl('TABLE','&T','&S') output from dual;
select dbms_metadata.get_dependent_ddl('INDEX','&T','&S') output from dual;
select dbms_metadata.get_dependent_ddl('CONSTRAINT','&T','&S') output from dual;
select dbms_metadata.get_dependent_ddl('REF_CONSTRAINT','&T','&S') output from dual;
select dbms_metadata.get_dependent_ddl('TRIGGER','&T','&S') output from dual;

-- Uncomment to generate object level grants
-- select dbms_metadata.get_dependent_ddl('OBJECT_GRANT','&T','&S') output from dual;

spool off;


This script will generate the DDL to re-create a specific table. The script accepts two parameters:
1. Schema name
2. Table name

def S='&1'
def T='&2'
-- def I='&3'
set lines 2000 pages 0 ver off echo off head off feed off
set newpage none
set trimspool on
set long 5000000
col output for a1000 word_wrapped
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SQLTERMINATOR',true);
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'STORAGE',false);

spool &T..sql
select dbms_metadata.get_ddl('TABLE','&T','&S') output from dual;

spool off;

Created by Charles Kim, Oracle ACE Director


If you notice in Oracle Linux 5.6, Oracle starts to disable NUMA at boot time as shown in the following grub.conf file:

 
[root@rac561 ~]# cat /etc/grub.conf
# grub.conf generated by anaconda
#
# Note that you do not have to rerun grub after making changes to this file
# NOTICE:  You do not have a /boot partition.  This means that
#          all kernel and initrd paths are relative to /, eg.
#          root (hd0,0)
#          kernel /boot/vmlinuz-version ro root=/dev/sda1
#          initrd /boot/initrd-version.img
#boot=/dev/sda
default=0
timeout=5
splashimage=(hd0,0)/boot/grub/splash.xpm.gz
hiddenmenu
title Oracle Linux Server (2.6.32-100.26.2.el5uek)
        root (hd0,0)
        kernel /boot/vmlinuz-2.6.32-100.26.2.el5 ro root=/dev/sda1 rhgb quiet numa=off
        initrd /boot/initrd-2.6.32-100.26.2.el5.img

Looking at /proc/cmdline, you will notice that the numa=off argument was passed at boot:

 
[root@rac561 ~]# cat /proc/cmdline
ro root=/dev/sda1 rhgb quiet numa=off

Posted by Charles Kim, Oracle ACE Director



You can use the following script to purge your Automatic Diagnostic Repository. The script provided will delete everything older than 1 day (1440 minutes). If you would like to maintain a week worth of information, set the -age option to be 10080.

 $ cat adr.ksh export CTL=adrci.ctl echo "show homes" |adrci |grep ^diag> $CTL 

The algorithm is simple. You determine what all your ADR homes are and loop through each one of them and purge log and trace files.


cat $CTL |while read HP do echo “Purging for HOMEPATH: $HP” adrci < [/code] Posted by Charles Kim, Oracle ACE Director


Starting from Oracle Database 11g, the DBA_USERS.PASSWORD column no longer contains the encrypted password. Now, you must query the SYS.USER$ table to access the information that you are looking for:

  1* select name, password from sys.user$ where name='RODBA'
SQL> /

NAME                           PASSWORD
------------------------------ ------------------------------
RODBA                          10AD051DA4653404

Once you find the encrypted password, everything is same as it was before. On a separate window, change the password:

SQL> alter user RODBA identified by RODBA123;
User altered.

On another window, login:

SQL> conn RODBA/RODBA123
Connected.

Reset the password back ASAP:
SQL> alter user RODBA identified by values '10AD051EC4653404';

User altered.

Posted by Charles Kim, Oracle ACE Director


In the following example, we will create a 6 node RAC database on the clustered nodes viscosityoradb1 to viscosityoradb6. We will create a general purpose database with 500MB redo log files and the database will be created in the DATA disk group. We will also allocate 2.4GB for the SGA and create the database with AL32UTF8 character set.

dbca -silent \
-createDatabase \
-templateName General_Purpose.dbc \
-gdbName DBATOOLS \
-sid DBATOOLS \
-SysPassword dba123 \
-SystemPassword dba123 \
-emConfiguration NONE \
-redoLogFileSize 500 \
-recoveryAreaDestination FRA \
-storageType ASM \
-asmSysPassword dba123 \
-diskGroupName DATA \
-characterSet AL32UTF8 \
-nationalCharacterSet AL16UTF16 \
-totalMemory 2400 \
-databaseType MULTIPURPOSE \
-nodelist viscosityoradb1,viscosityoradb2,viscosityoradb3,viscosityoradb4,viscosityoradb5,viscosityoradb6

Executing the dbca script yields the following output:

Copying database files
1% complete
3% complete
30% complete
Creating and starting Oracle instance
32% complete
36% complete
40% complete
44% complete
45% complete
48% complete
50% complete
Creating cluster database views
52% complete
70% complete
Completing Database Creation
73% complete
76% complete
79% complete
82% complete
91% complete
100% complete
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/DBATOOLS/DBATOOLS.log" for further details.

As a follow-up step, you should shutdown the database, mount the database and enable archive logging.

Within the Database Cloud offerings, we can provide database as a service quickly and efficiently by leveraging dbca in silent mode. Provisioning databases to customers can be done in within 1/2 hour rather than 1/2 day.

Created by Charles Kim, Oracle ACE Director

Posted in RAC