Oracle Database 11g Release 2 adds intelligent data placement (IDP) support for JBOD (just a bunch of disks) configurations for ASM disk groups. IDP leverages geometry of disks and will place hot files on the outer most edge of the spindles and cold files in the inner rings of the drives. In addition, files that are accessed with similar patterns are placed close to each other to reduce latency.

IDP is supported by both ASM Configuration Assistant (ASMCA) and Enterprise Manager.

The outermost tracks provide greater speed and higher bandwidth. IDP works at disk group attribute level or at the file level and can be modified after the disk group is created. IDP is suited for situations where:
1. Disk groups that are > 25% full
2. Database datafiles are accessed at different rates
3. JBOD configurations – LUNs carved out of SANs are not suited for IDP

You can set IDP for only new files. Existing files must go through a rebalance operation to leverage IDP. In order to leverage IDP, both COMPATIBLE.ASM and COMPATIBLE.RDBMS disk group attributes must be set to a value of 11.2.0 or higher. IDP can be set with the following SQL syntax:

 
1.  ALTER DISKGROUP DATA ADD TEMPLATE
2.  ALTER DISKGROUP DATA MODIFY TEMPLATE
3.  ALTER DISKGROUP DATA MOFIFY FILE

For example:

alter diskgroup data add template hotfiles attributes (hot mirrorhot);

IDP information can be retrieved by querying V$ASM_DISK, V$ASM_DISK_IOSTAT, V$ASM_FILE and V$ASM_TEMPLATE views.

Columns from V$ASM_DISK that are of particular interest are:

 
 HOT_READS					    NUMBER
 HOT_WRITES					    NUMBER
 HOT_BYTES_READ 				    NUMBER
 HOT_BYTES_WRITTEN				    NUMBER
 COLD_READS					    NUMBER
 COLD_WRITES					    NUMBER
 COLD_BYTES_READ				    NUMBER
 COLD_BYTES_WRITTEN			    NUMBER
 HOT_USED_MB					    NUMBER
 COLD_USED_MB					    NUMBER

New columns in v$asm_template that apply to IDP are:

 
 PRIMARY_REGION 				    VARCHAR2(4)
 MIRROR_REGION				    VARCHAR2(4)

Posted by Charles Kim, Oracle ACE

Posted in ASM

As a general rule, configure the FRA and define your local archiving parameters to be:

LOG_ARCHIVE_DEST_1=’LOCATION=USE_DB_RECOVERY_FILE_DEST’ 

As part of best practices, you should enable Flashback Database on primary and standby databases for easy re-instantiation after a failover. With Flashback Database enabled, you do not have to rebuild the primary database after a database failover. You can re-instate the failed primary database. Also Flashback Database provides the mechanism to expeditiously rewind the database after an erroneous batch update, a bad data load, user error(s), or a malicious set of activities on the database.

Oracle MAA recommends that DB_FLASHBACK_RETENTION_TARGET should be set to a minimum of 60 minutes if all you are trying to achieve re-instantiation of the primary database after a failover; however, if you require the additional protection from user errors and corruptions, then you will need to extend that time. Oracle MAA best practices recommend a minimum of 6 hours retention period. You need to determine what that retention period is based on your business requirements. The longer you set the flashback retention time, more disk space you will need.

You should also enable Flashback Database on the standby database to minimize downtime resulting from logical corruptions.

It looks like as of Oracle Database 11g Release 2, we can enable flashback database while the database is online.  For some DBAs who do not want to turn on flashback database, this can provide them a little more flexibility for enabling or disabling flashback for critical database operations or in a controlled scenario.  You can now turn on Flashback Logging (alter database flashback on;) prior to a critical operation and turn it off (alter database flashback off;) afterwards without a database outage.  For DBAs who have to deal with limited space for the FRA and have to disable flashback logging from time to time due to limited space and uncontrolled batch jobs, we can now re-enable it when crisis situations clear up.

Posted by Charles Kim, Oracle ACE Director


If “root.sh” is already executed, then follow the step below.
Please wait until each step completes before advancing to the next node. As root, execute “$GRID_HOME/crs/install/rootcrs.pl -verbose -deconfig -force” on all nodes, except the last node:

# $GRID_HOME/crs/install/rootcrs.pl -verbose -deconfig -force

On the last node:
As root, execute
“$GRID_HOME/crs/install/rootcrs.pl -verbose -deconfig -force -lastnode”.

This command will zero out OCR and vote disk disk also.

# $GRID_HOME/crs/install/rootcrs.pl -verbose -deconfig -force -lastnode

Created by Charles Kim, Oracle ACE Director

Posted in RAC

Setting up HugePages requires one kernel parameter change and modifications to two entries to the /etc/securities/limits.conf file. The kernel parameter that needs to be modified is vm.nr_hugepages. The default hugepage size is 2M. The kernel parameters needs to be set in granules of 2M. For example, if we are allocating 48GB as our hugepage size, we will need to set our kernel parameter to 24576 (48*1024/2) in the /etc/sysctl.conf file.

Respectively, the /etc/security/limits.conf file needs to be adjusted to set soft and hard limits for oracle:

oracle          soft    memlock        50331648
oracle          hard    memlock        50331648

After the kernel parameters are set, it is highly recommended that you reboot the server.

Created by Charles Kim, Oracle ACE Director


screen Cheat Sheet
launch screen with your individual unix id:  $ screen

Detach from Screen:

control+d, release, then type d

Find the list of screen sessions:

screen -ls
There is a screen on:
        21586.pts-3.rac02 (Detached)
1 Socket in /var/run/screen/S-ckim

If you are already attached then your screen “-ls” will show as (Attached); however, it you are already sudo’d to oracle, you will not seen a valid result set because oracle did not start the screen session.

 > screen -ls
There is a screen on:
        21586.pts-3.rac02 (Attached)
1 Socket in /var/run/screen/S-ckim.

Re-attach to previous screen session

rac02:/home/ckim
 > screen -r 21586.pts-3.rac02

If you running putty, your putty terminal title will change.

Tip:
Do not exit your screen session, always detach from it !!

Rename your screen session:

Control+a (lower a) followed by Shift+A (capital A)

Then rename “bash” to whatever you want
Again your putty terminal title will change

There is no need to start multiple sessions of screen.  Within screen, you can create sub-screen sessions:

control-a followed by c (for create)

The trick is to put a Title for each screen session:

control-a followed by Capital A

To toggle between each screen session:

control-a n --> next screen
control-a p --> previous screen

The title of your screen and window will change

List all your windows:
control-a ” 

How do you scrollback on the screen to see past output
First, create a file called .screenrc in your $HOME directory:  
Add the line below to set buffer to 32000 lines:

defscrollback 32000

To view previous buffer, go into Copy Mode

Next:
Control-a [
Control-u -  Scrolls a half page up.
Control-d -  Scrolls a half page down
Control-b -  Scrolls a full page up.
Control-f -  Scrolls the full page down.

Posted by Charles Kim, Oracle ACE Director


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