Compare 2 Servers RPM and Install RPM from Text File List

1. Generate RPM list on Old Server

rpm -qa –queryformat=’%{NAME}\n’ | sort > serverold.txt

2. Generate RPM list on New Server

rpm -qa –queryformat=’%{NAME}\n’ | sort > servernew.txt

3. Copy File to New Server and use diff comand to get differences

diff serverold.txt servernew.txt > diff.txt

4. Get list of packages missing in new Server

cat diff.txt | grep “<” > difference.txt

vi difference.txt

%s,< ,

## save and exit

5. Install missing packages on new server

yum -y install $(cat difference.txt)

Shell Script to Monitor Oracle Tablespace and Send Email Alert Only If Threshold Exceeds

[code language=”bash”]

export MMSG=/tmp/$$.mail
export ADDR="email address here"
export ORACLE_HOME=/u02/app/oracle/product/11.2.0/dbhome_1
export ORACLE_SID=appdev1

file=/tmp/${$}_`date +%Y%m%d`

sqlplus -s system/password << .eof > $file
set pages 0
select df.tablespace_name tspace,
round(sum(fs.bytes)/(df.bytes) * 100) "%_free",
round(sum(fs.bytes)/(1024*1024)) free_ts_size,
df.bytes/(1024*1024) tot_ts_size
from dba_free_space fs, (select tablespace_name, sum(bytes) bytes from dba_data_files
group by tablespace_name ) df
where fs.tablespace_name = df.tablespace_name
group by df.tablespace_name, df.bytes
having round(sum(fs.bytes)/(df.bytes) * 100) < 20;
exit;
.eof
#list all datafile below
egrep "SOE|APEX|SYSAUX|SYSTEM|TEMP|UNDOTBS1|USERS|AUDIT_TBS|APP_ENCRYPTED" /tmp/${$}_`date +%Y%m%d` >/tmp/table${ORACLE_SID}.txt
check_stat=`cat /tmp/table${ORACLE_SID}.txt|wc -l`;
oracle_num=`expr $check_stat`
if [ $oracle_num -ne 0 ]
then
echo "tablespace less than 20% for $ORACLE_SID : $oracle_num" > $MMSG
mail -s "TABLESPACE WITH LESS THAN 20% FREE SPACE" $ADDR < $MMSG
mail -s "TABLESPACE WITH LESS THAN 20% FREE SPACE IN DATABASE $ORACLE_SID" $ADDR < /tmp/table$ORACLE_SID.txt
fi

rm -f $MMSG > /dev/null 2>&1
rm $file

[/code]

Shell Script to Monitor AIX Filesystem and Send Email

The Below Shell Script checks the Filesystem mount points and using AWK outputs all filesystem exceeding 90% space to a file called diskspacepoll. Once that is done the sed command removes any special character like ‘%’ from the output file and cleans it to a file called output.log

The next important logic is in the AWK block. Here a variable called pattern is defined using the threshold of 90%. Another variable called var is defined. This is your baseline metric. So it value of pattern exceeds var then the mail is dispatched else the script does nothing. You can put this in crontab as a every 5 minute job to continuously poll the filesystems and incase the threshold is exceed it will dispatch an email immediately to the admin

[code language=”bash”]

#!/bin/ksh
df -P | grep -v Capacity | awk ‘{if ($5 >= 90) {print $5;}}’ > /home/root/diskspacepoll
sed ‘s/[!@#\$%^&*()]//g’ /home/root/diskspacepoll > /home/root/output.log
####### AWK LOGICAL BLOCK #########
pattern=$(awk ‘$1 > 90 {print $1}’ /home/root/output.log)
var=90
if [[ $pattern > $var ]]
then
echo "Please Check with System Administrator" | mailx -s "90% Threshold of DiskSpace exceeded on Server 1 (ESB1)" sysadmin@company.com
fi

[/code]

Notepad++ find and replace string with a new-line

Most of the times DBA’s need to edit blocks of PL/SQL or SQL scripts and perform some modifications. Notepad++ is a very powerful and lightweight tool to perform edits.

 

Lets say you have a document with text like below:

 

111

XYZ

DCD

999 ABC

Now you need  add a line break if you find pattern 999

In the lower left hand corner of the Replace box, you’ll see a section called “Search Mode”. Just select the “Extended (\n, \r, \t, , \x…)” choice and you can enter in \r as the replace variable and it will put the line break in.

notepad

 

 

The new file would look like below:

111

XYZ

DCD

999

ABC

 

Schema Replication using Oracle Goldengate 11g (Installation & Configuration)

GoldenGate is touted by Oracle as its future for data replication. It will slowly phase out Oracle Streams and will be more closely coupled with Oracle products. Golden Gate is relatively simpler to use and monitor than Oracle streams. Below I will demonstrate how to configure ASYNC Schema Replication one side using Oracle GoldenGate 11g on a Oracle 11gR2 database.

SID of Source : IVRPROD
SID of Target : IVRDR
Source Server name : ivrhodb
Target Server name : ivrdrdb
Source Schema name : IVR
Target Schema name : IVR
Tables : All tables

1. Tell database to log more (supplemental logging ~10% of more redo
writing) on source database only.

alter database add supplemental log data;

2. GoldenGate user creation on source and target side (if on Unix, on windows use same user in ora_dba group with which you installed Oracle)

useradd -d /data/home/gguser gguser

3. Copy the downloaded installation zip file ogg112101_ggs_Windows_x64_ora11g_64bit.zip) to a directory and unzip it.

4. Set the Environment variables in .bash_profile of user gguser on
both source and target database servers.

— source —

export ORACLE_HOME=/u01/app/oracle/product/10.2.0
export ORACLE_SID=IVRPROD
export LD_LIBRARY_PATH=/u01/app/oracle/product/10.2.0/lib
export PATH=$PATH:$ORACLE_HOME/bin

— Detination —

export ORACLE_HOME=/u01/app/oracle/product/10.2.0
export ORACLE_SID=IVRDR
export LD_LIBRARY_PATH=/u01/app/oracle/product/10.2.0/lib
export PATH=$PATH:$ORACLE_HOME/bin

5. create the tablespace for both databases (source + target ) like:

Create tablespace ggate datafile ‘/u01/oradata/ggate01.dbf’ size 1000M autoextend on next 100M;

6. GoldenGate schema creation into source and target database

create user ggate identified by ggate default tablespace ggate
temporary tablespace TEMP profile DEFAULT;
alter user ggate QUOTA UNLIMITED ON ggate;
grant CONNECT to ggate;
grant CREATE SESSION to ggate;
grant RESOURCE to ggate;
grant SELECT ANY TABLE to ggate;
grant ALTER SESSION to ggate;
grant CREATE TABLE to ggate;
grant FLASHBACK ANY TABLE to ggate;
grant SELECT ANY DICTIONARY to ggate;
grant DBA to ggate;

logis as sysdba on both source and target run following steps…

SQL> grant execute on utl_file to ggate;

SQL> @C:\ggs_Windows_x64_ora11g_64bit/marker_setup.sql

SQL> @C:\ggs_Windows_x64_ora11g_64bit/ddl_setup.sql

SQL> @C:\ggs_Windows_x64_ora11g_64bit/role_setup.sql

SQL> grant GGS_GGSUSER_ROLE to ggate;

And more important grant on dictionay views otherwise extract will keep on abending on both source and target schema (in our case IVR and IVR on both source and target. Schema name is same on both databases)

— on source —

SQL> grant select any dictionary to ivr;

— on target —

SQL> grant select any dictionary to ivr;

7. Go to installation directory and execute ./ggsci to see if your able to run the golden gate console on both source and target

8.
// Name of schema to be replicated will be IVR residing on IVRPROD database, it will be replicated to same schema name in IVRDR database.Make sure the characterset is same on Source and Target databases. //

— Source Configuration —

GGSCI (ivrhodb) 1> create subdirs
GGSCI (ivrhodb) 1> status all
GGSCI (ivrhodb) 1> edit params mgr

port 7809
lagreportminutes 5
laginfominutes 1
lagcriticalminutes 2
purgeoldextracts ./dirdat/t*, minkeepdays 2, usecheckpoints

GGSCI (ivrhodb) 1> start mgr
GGSCI (ivrhodb) 1> info all

If manager is running then manager configuration is ok.

GGSCI (ivrhodb) 1> dblogin userid ggate, password ggate
GGSCI (ivrhodb) 1> list tables ivr.*

If you see the list of tables then your configuration is good and you can continue:

GGSCI (ivrhodb) 1> add trandata IVR.*
GGSCI (ivrhodb) 1> info trandata IVR.*

Lets create and configure now the extractor process, edit add these files and save:

GGSCI (ivrhodb) 1> edit params ext1

extract ext1
userid ggate, password ggate
discardfile ./dirrpt/ext1.dsc,purge
reportcount every 15 minutes, rate
exttrail ./dirdat/t1
table IVR.*;

GGSCI (ivrhodb) 1> add extract ext1, tranlog, begin now
GGSCI (ivrhodb) 1> add exttrail ./dirdat/t1, extract ext1, megabytes 100
GGSCI (ivrhodb) 1> status all
GGSCI (ivrhodb) 1> start ext1

Extractor created, now we create the data pump process:

GGSCI (ivrhodb) 1> edit params dpe1

extract dpe1
passthru
rmthost ivrdrdb, mgrport 7809
rmttrail ./dirdat/t1
table IVR.*;

//rmthost is the target server name or ip address. Ensure port 7809 is open on target server

GGSCI (ivrhodb) 1> add extract dpe1, exttrailsource ./dirdat/t1
GGSCI (ivrhodb) 1> add rmttrail ./dirdat/t1, extract dpe1, megabytes 100
GGSCI (ivrhodb) 1> status all

We are not going to start pump process yet, because destination is not configured.

9.
— Destination Configuration —
Target side on your ggsci prompt:

GGSCI (ivrdrdb) 1> create subdirs
GGSCI (ivrdrdb) 1> edit params mgr

port 7809
dynamicportlist 7900-7950
lagreportminutes 5
laginfominutes 1
lagcriticalminutes 2
purgeoldextracts ./dirdat/t*, minkeepdays 2, usecheckpoints

GGSCI (ivrdrdb) 1> start mgr
GGSCI (ivrdrdb) 1> status all
GGSCI (ivrdrdb) 1> view report mgr

Manager created and verified, now we are going to create replicat processes. We’ll create the replicat with the 5 minutes lag, purposely for replication to be behind 5 minutes :

GGSCI (ivrdrdb) 1> edit params rep1

replicat rep1
userid ggate, password ggate
discardfile ./dirrpt/rep1.dsc, purge
assumetargetdefs
reportcount every 15 minutes, rate
batchsql
deferapplyinterval 5 mins
map IVR.*, target IVR.*;

GGSCI (ivrdrdb) 1> add replicat rep1, exttrail ./dirdat/t1
GGSCI (ivrdrdb) 1> ADD CHECKPOINTTABLE GGATE.CHKPTAB

That should be all about configuration , now last two steps, lets start data pump process on source side and replicat process on destination.

— on source —
GGSCI (ivrhodb) 1> start dpe1

— on target —
GGSCI (ivrdrdb) 1> start rep1

Now just check statuses on both sides with “status all” command. If everything is running and nothing abandoned you can check event logs on both sides and activity:

stats ext1, totalsonly *, reportrate sec
stats rep1, totalsonly *, reportrate sec
send rep1, status

And we have configured Oracle Golden Gate installation and setup forone asynchronous data replication.

Monitor User Processes and Send Email Shell Script Solaris10

Sometimes on a system it is important to know the maximum user processes does not increase so much that it consumes all shared memory. Recently we had an incident in our ORganization where a rogue application bug caused user processes to spike upto 20,000 and it consumed all memory and swap and the entire server crashed.  If we pro-actively monitored the system for increasing user processes we could have averted this disaster. Below is script to monitor user processes and send email.

swx – is name process name

500- threshold for permitted processes

#!/bin/ksh
VAL=`ps -ef | grep -c swx | awk ‘{if ( $1 > 500 ) print $1 }’`

if [ “$VAL” -ge “500” ]; then
echo “$VAL user processes found running with SWX user. Exceeded the Threshold,
Please take immediate action.” | /usr/bin/mailx -s “Alert !! $VAL SWX Applicati
on processes” email@test.com
fi

exit 0

You can add this to crontab like below:

* * * * * /u02/scripts/userprocmon.sh >/dev/null 2>&1

Configuring DNS on Solaris 10 (Joining Solaris10 server to Domain)

################ Configure Solaris 10 machine to be on DNS##############

1. Check for file resolv.conf in /etc directory, if it doenst exist create it

2. add below information to the resolv.conf file

domain mydomain.com
nameserver 172.20.4.10
nameserver 172.20.4.166
search mydomain.com

3. Check for file nsswitch.conf in /etc directory

hosts:      files
ipnodes:    files

## edit the above 2 lines and add ‘dns’ in front of file

hosts:      files dns
ipnodes:    files dns

save and exit

$ nslookup swxscan

Server:         172.20.4.10
Address:        172.20.4.10#53

Name:   swxscan.mydomain.com
Address: 172.21.51.18
Name:   swxscan.mydomain.com
Address: 172.21.51.17
Name:   swxscan.mydomain.com
Address: 172.21.51.19

Voila

Setting up user profiles for Grid and Oracle user : Oracle RAC 11gR2 on Solaris 10

Below is the Solaris user profiles for “grid” and “oracle” user. If you have followed Oracles official documentation for installation ORacle Rac 11gR2. It is best practice to install Oracle clusterware under grid user and oracle database with Oracle user. It takes a little time to get used to this setup specially for RAC users coming from 10g. But once you get a hang of it. It will work like a charm.

FYI the below user profiles have been set on Solaris 10\

___________________________________________

GRID USER PROFILE
___________________________________________

grid:x:100:100::/export/home/grid:/usr/bin/bash
oracle:x:101:100::/export/home/oracle:/usr/bin/bash

cd /export/home/grid

root@swxracnode1 # more .bash_profile

## If doesnt exist create it and give permission as 775 and owner as grid:oinstall

ORACLE_HOME=/u02/11.2.0/grid
GRID_HOME=/u02/11.2.0/grid
ORACLE_SID=+ASM1
export ORACLE_HOME ORACLE_SID GRID_HOME
PATH=/u02/11.2.0/grid/bin:$PATH

. .bash_profile

___________________________________________

ORACLE USER PROFILE
___________________________________________

grid:x:100:100::/export/home/grid:/usr/bin/bash
oracle:x:101:100::/export/home/oracle:/usr/bin/bash

cd /export/home/oracle

root@swxracnode1 # more .bash_profile

## If doesnt exist create it and give permission as 775 and owner as oracle:oinstall

ORACLE_HOME=’/u01/app/oracle/product/11.2.0/db_1′
ORACLE_SID=’swx1′
export ORACLE_HOME ORACLE_SID
PATH=$PATH:$ORACLE_HOME/bin
export PATH
ORACLE_UNQNAME=’swx’
export ORACLE_UNQNAME

Oracle Database 11g installation on Solaris 10

I had installed Oracle Database 11g on Solaris 10 few days back. I thought of documenting the steps for easy reference. Please find below steps for the same

Installation Pre-Requisite

Refer to Oracle Database Installation Guide 11g Release 1 (11.1) for Solaris Operating System for checking Hardware and Software Requirements.

User Creation and Environment Settings

1)Create groups for Oracle account

#groupadd oinstall
#groupadd dba
#groupadd oper

2)Create Oracle Default Home directory

# mkdir /export/home
# mkdir /export/home/oracle

3)Create Oracle user

# useradd -g oinstall -G dba -d /export/home/oracle -s /usr/bin/bash oracle
# chown oracle:oinstall /export/home/oracle

4)Create Project for Oracle for setting the kernel parameters

In case of Solaris 10, you can use projects to configure the kernel parameters instead of /etc/system file. This can be done as following

# projadd -U oracle -K "project.max-shm-memory=(priv,4g,deny)" oracle
# projmod -sK "project.max-sem-nsems=(priv,256,deny)" oracle
# projmod -sK "project.max-sem-ids=(priv,100,deny)" oracle
# projmod -sK "project.max-shm-ids=(priv,100,deny)" oracle

There are many more ways of creating project entries such as group.group-name or user.user-name. For more details refer to Solaris Administration documents.

Update

Last three settings made by projmod command are not required as these values are lower than the default. This was pointed by Mike Madland and he also gave a Sun documentation link

http://docs.sun.com/app/docs/doc/819-2724/6n50b0795?l=en&a=view#chapter1-33
You can check the values for max-sem-ids and max-shm-ids with this command:

prctl -n project.max-sem-ids -i task `ps -o taskid= -p $$`

5)Create .bash_profile for Oracle user

#Oracle Environment Settings
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR
ORACLE_BASE=/u03/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.1.0/db_1; export ORACLE_HOME
ORACLE_SID=TESTDB11G; export ORACLE_SID
PATH=$PATH:/usr/local/bin:/usr/ccs/bin:/usr/sfw/bin:$ORACLE_HOME/bin

Now Set the Display to a X-windowing enabled system.

$ export DISPLAY=192.168.4.47:0.0

Also allow the host to accept the connection by

$xhost +

Oracle Software Installation

Go to the Oracle dump location and run runInstaller as Oracle user

$./runInstaller

This will open Oracle Universal Installer(OUI) screen. If Oracle Universal Installer is not displayed, then ensure DISPLAY variable is set correctly. Select “Software only” option and install the software. If any of the pre-requisite’s are not met , then installation will fail. You would be required to make necessary changes to proceed.

Database Creation

We will be using ASM for the Database files. For this we need to perform some configuration

1)Prepare the Raw device for using as ASM Disks

# ls -l
total 0
crw------- 1 root root 125, 1 Jun 20 10:39 1

Disk should be owned by Oracle user and should have permission set to 660

# chown oracle:dba 1
# chmod 660 1

- # ls -ltr
total 0
crw-rw---- 1 oracle dba 125, 1 Jun 20 10:39 1

2)Configure CSS Service

In case of Solaris 10, we need to use Service Management Facility (SMF) for configuring CSS service else it will not start. Refer to my earlier post for this step i.e 11.1.0.6 ASM installation on Solaris fails -II

3) Configure ASM Instance

a)Go to $ORACLE_HOME/bin

b)Execute dbca from this directory (ensure dbca is properly set)

$./dbca

c) Select Configure ASM Instance option. This will create ASM instance for you. After this you can create Diskgroups using GUI or else use sqlplus to do the same.

4)Now continue creating database normally and enter Diskgroup Name after selecting Oracle Managed files as database file location.

While you navigate through GUI screens, it will prompt you to Specifying Security Settings

– Keep the enhanced 11g security settings(recommended)
– Revert to pre 11g settings

Select the 11g settings which will enable Auditing by default and also enable Case sensitive passwords with Stronger password hashing algorithm.

I have not discussed GUI screens for DBCA and OUI in this article. These are pretty much standard screens. In case you need more information about it, then you can refer to : Oracle 11g Install guide for Solaris

Automatic Storage Management (ASM) in Oracle Database 10g

Automatic Storage Management (ASM) in Oracle Database 10g

Automatic Storage Management (ASM) is a new feature was introduced in Oracle 10g to simplify the storage of Oracle datafiles, controlfiles and logfiles.

Overview of Automatic Storage Management (ASM)

Automatic Storage Management (ASM) simplifies administration of Oracle related files by allowing the administrator to reference disk groups rather than individual disks and files, which are managed by ASM. The ASM functionality is an extention of the Oracle Managed Files (OMF) functionality that also includes striping and mirroring to provide balanced and secure storage. The new ASM functionality can be used in combination with existing raw and cooked file systems, along with OMF and manually managed files.

The ASM functionality is controlled by an ASM instance. This is not a full database instance, just the memory structures and as such is very small and lightweight.

The main components of ASM are disk groups, each of which comprise of several physical disks that are controlled as a single unit. The physical disks are known as ASM disks, while the files that reside on the disks are know as ASM files. The locations and names for the files are controlled by ASM, but user-friendly aliases and directory structures can be defined for ease of reference.

The level of redundancy and the granularity of the striping can be controlled using templates. Default templates are provided for each file type stored by ASM, but additional templates can be defined as needed.

Failure groups are defined within a disk group to support the required level of redundancy. For two-way mirroring you would expect a disk group to contain two failure groups so individual files are written to two locations.

In summary ASM provides the following functionality:

  • Manages groups of disks, called disk groups.
  • Manages disk redundancy within a disk group.
  • Provides near-optimal I/O balancing without any manual tuning.
  • Enables management of database objects without specifying mount points and filenames.
  • Supports large files.

Initialization Parameters and ASM Instance Creation

The initialization parameters that are of specific interest for an ASM instance are:

  • INSTANCE_TYPE – Set to ASM or RDBMS depending on the instance type. The default is RDBMS.
  • DB_UNIQUE_NAME – Specifies a globally unique name for the database. This defaults to +ASM but must be altered if you intend to run multiple ASM instances.
  • ASM_POWER_LIMIT -The maximum power for a rebalancing operation on an ASM instance. The valid values range from 1 to 11, with 1 being the default. The higher the limit the more resources are allocated resulting in faster rebalancing operations. This value is also used as the default when the POWER clause is omitted from a rebalance operation.
  • ASM_DISKGROUPS – The list of disk groups that should be mounted by an ASM instance during instance startup, or by the ALTER DISKGROUP ALL MOUNT statement. ASM configuration changes are automatically reflected in this parameter.
  • ASM_DISKSTRING – Specifies a value that can be used to limit the disks considered for discovery. Altering the default value may improve the speed of disk group mount time and the speed of adding a disk to a disk group. Changing the parameter to a value which prevents the discovery of already mounted disks results in an error. The default value is NULL allowing all suitable disks to be considered.

Incorrect usage of parameters in ASM or RDBMS instances result in ORA-15021 errors.

To create an ASM instance first create a file called init+ASM.ora in the /tmp directory containing the following information.

INSTANCE_TYPE=ASM

Next, using SQL*Plus connect to the ide instance.

export ORACLE_SID=+ASM
sqlplus / as sysdba

Create an spfile using the contents of the init+ASM.ora file.

SQL> CREATE SPFILE FROM PFILE='/tmp/init+ASM.ora';

File created.

Finally, start the instance with the NOMOUNT option.

SQL> startup nomount
ASM instance started

Total System Global Area  125829120 bytes
Fixed Size                  1301456 bytes
Variable Size             124527664 bytes
Database Buffers                  0 bytes
Redo Buffers                      0 bytes
SQL>

The ASM instance is now ready to use for creating and mounting disk groups. To shutdown the ASM instance issue the following command.

SQL> shutdown
ASM instance shutdown
SQL>

Once an ASM instance is present disk groups can be used for the following parameters in database instances (INSTANCE_TYPE=RDBMS) to allow ASM file creation:

  • DB_CREATE_FILE_DEST
  • DB_CREATE_ONLINE_LOG_DEST_n
  • DB_RECOVERY_FILE_DEST
  • CONTROL_FILES
  • LOG_ARCHIVE_DEST_n
  • LOG_ARCHIVE_DEST
  • STANDBY_ARCHIVE_DEST

Startup and Shutdown of ASM Instances

ASM instance are started and stopped in a similar way to normal database instances. The options for the STARTUP command are:

  • FORCE – Performs a SHUTDOWN ABORT before restarting the ASM instance.
  • MOUNT – Starts the ASM instance and mounts the disk groups specified by the ASM_DISKGROUPS parameter.
  • NOMOUNT – Starts the ASM instance without mounting any disk groups.
  • OPEN – This is not a valid option for an ASM instance.

The options for the SHUTDOWN command are:

  • NORMAL – The ASM instance waits for all connected ASM instances and SQL sessions to exit then shuts down.
  • IMMEDIATE – The ASM instance waits for any SQL transactions to complete then shuts down. It doesn’t wait for sessions to exit.
  • TRANSACTIONAL – Same as IMMEDIATE.
  • ABORT – The ASM instance shuts down instantly.

Administering ASM Disk Groups

Disks

Disk groups are created using the CREATE DISKGROUP statement. This statement allows you to specify the level of redundancy:

  • NORMAL REDUNDANCY – Two-way mirroring, requiring two failure groups.
  • HIGH REDUNDANCY – Three-way mirroring, requiring three failure groups.
  • EXTERNAL REDUNDANCY – No mirroring for disks that are already protected using hardware mirroring or RAID.

In addition failure groups and preferred names for disks can be defined. If the NAME clause is omitted the disks are given a system generated name like “disk_group_1_0001”. The FORCE option can be used to move a disk from another disk group into this one.

CREATE DISKGROUP disk_group_1 NORMAL REDUNDANCY
  FAILGROUP failure_group_1 DISK
    '/devices/diska1' NAME diska1,
    '/devices/diska2' NAME diska2
  FAILGROUP failure_group_2 DISK
    '/devices/diskb1' NAME diskb1,
    '/devices/diskb2' NAME diskb2;

Disk groups can be deleted using the DROP DISKGROUP statement.

DROP DISKGROUP disk_group_1 INCLUDING CONTENTS;

Disks can be added or removed from disk groups using the ALTER DISKGROUP statement. Remember that the wildcard “*” can be used to reference disks so long as the resulting string does not match a disk already used by an existing disk group.

-- Add disks.
ALTER DISKGROUP disk_group_1 ADD DISK
  '/devices/disk*3',
  '/devices/disk*4';

-- Drop a disk.
ALTER DISKGROUP disk_group_1 DROP DISK diska2;

Disks can be resized using the RESIZE clause of the ALTER DISKGROUP statement. The statement can be used to resize individual disks, all disks in a failure group or all disks in the disk group. If the SIZE clause is omitted the disks are resized to the size of the disk returned by the OS.

-- Resize a specific disk.
ALTER DISKGROUP disk_group_1
  RESIZE DISK diska1 SIZE 100G;

-- Resize all disks in a failure group.
ALTER DISKGROUP disk_group_1
  RESIZE DISKS IN FAILGROUP failure_group_1 SIZE 100G;

-- Resize all disks in a disk group.
ALTER DISKGROUP disk_group_1
  RESIZE ALL SIZE 100G;

The UNDROP DISKS clause of the ALTER DISKGROUP statement allows pending disk drops to be undone. It will not revert drops that have completed, or disk drops associated with the dropping of a disk group.

ALTER DISKGROUP disk_group_1 UNDROP DISKS;

Disk groups can be rebalanced manually using the REBALANCE clause of the ALTER DISKGROUP statement. If the POWER clause is omitted the ASM_POWER_LIMIT parameter value is used. Rebalancing is only needed when the speed of the automatic rebalancing is not appropriate.

ALTER DISKGROUP disk_group_1 REBALANCE POWER 5;

Disk groups are mounted at ASM instance startup and unmounted at ASM instance shutdown. Manual mounting and dismounting can be accomplished using the ALTER DISKGROUP statement as seen below.

ALTER DISKGROUP ALL DISMOUNT;
ALTER DISKGROUP ALL MOUNT;
ALTER DISKGROUP disk_group_1 DISMOUNT;
ALTER DISKGROUP disk_group_1 MOUNT;

Templates

Templates are named groups of attributes that can be applied to the files within a disk group. The following example show how templates can be created, altered and dropped.

-- Create a new template.
ALTER DISKGROUP disk_group_1 ADD TEMPLATE my_template ATTRIBUTES (MIRROR FINE);

-- Modify template.
ALTER DISKGROUP disk_group_1 ALTER TEMPLATE my_template ATTRIBUTES (COARSE);

-- Drop template.
ALTER DISKGROUP disk_group_1 DROP TEMPLATE my_template;

Available attributes include:

  • UNPROTECTED – No mirroring or striping regardless of the redundancy setting.
  • MIRROR – Two-way mirroring for normal redundancy and three-way mirroring for high redundancy. This attribute cannot be set for external redundancy.
  • COARSE – Specifies lower granuality for striping. This attribute cannot be set for external redundancy.
  • FINE – Specifies higher granularity for striping. This attribute cannot be set for external redundancy.

Directories

A directory heirarchy can be defined using the ALTER DISKGROUP statement to support ASM file aliasing. The following examples show how ASM directories can be created, modified and deleted.

-- Create a directory.
ALTER DISKGROUP disk_group_1 ADD DIRECTORY '+disk_group_1/my_dir';

-- Rename a directory.
ALTER DISKGROUP disk_group_1 RENAME DIRECTORY '+disk_group_1/my_dir' TO '+disk_group_1/my_dir_2';

-- Delete a directory and all its contents.
ALTER DISKGROUP disk_group_1 DROP DIRECTORY '+disk_group_1/my_dir_2' FORCE;

Aliases

Aliases allow you to reference ASM files using user-friendly names, rather than the fully qualified ASM filenames.

-- Create an alias using the fully qualified filename.
ALTER DISKGROUP disk_group_1 ADD ALIAS '+disk_group_1/my_dir/my_file.dbf'
  FOR '+disk_group_1/mydb/datafile/my_ts.342.3';

-- Create an alias using the numeric form filename.
ALTER DISKGROUP disk_group_1 ADD ALIAS '+disk_group_1/my_dir/my_file.dbf'
  FOR '+disk_group_1.342.3';

-- Rename an alias.
ALTER DISKGROUP disk_group_1 RENAME ALIAS '+disk_group_1/my_dir/my_file.dbf'
  TO '+disk_group_1/my_dir/my_file2.dbf';

-- Delete an alias.
ALTER DISKGROUP disk_group_1 DELETE ALIAS '+disk_group_1/my_dir/my_file.dbf';

Attempting to drop a system alias results in an error.

Files

Files are not deleted automatically if they are created using aliases, as they are not Oracle Managed Files (OMF), or if a recovery is done to a point-in-time before the file was created. For these circumstances it is necessary to manually delete the files, as shown below.

-- Drop file using an alias.
ALTER DISKGROUP disk_group_1 DROP FILE '+disk_group_1/my_dir/my_file.dbf';

-- Drop file using a numeric form filename.
ALTER DISKGROUP disk_group_1 DROP FILE '+disk_group_1.342.3';

-- Drop file using a fully qualified filename.
ALTER DISKGROUP disk_group_1 DROP FILE '+disk_group_1/mydb/datafile/my_ts.342.3';

Checking Metadata

The internal consistency of disk group metadata can be checked in a number of ways using the CHECK clause of the ALTER DISKGROUP statement.

-- Check metadata for a specific file.
ALTER DISKGROUP disk_group_1 CHECK FILE '+disk_group_1/my_dir/my_file.dbf'

-- Check metadata for a specific failure group in the disk group.
ALTER DISKGROUP disk_group_1 CHECK FAILGROUP failure_group_1;

— Check metadata for a specific disk in the disk group. ALTER DISKGROUP disk_group_1 CHECK DISK diska1; — Check metadata for all disks in the disk group. ALTER DISKGROUP disk_group_1 CHECK ALL;

ASM Views

The ASM configuration can be viewed using the V$ASM_% views, which often contain different information depending on whether they are queried from the ASM instance, or a dependant database instance.

View ASM Instance DB Instance
V$ASM_ALIAS Displays a row for each alias present in every disk group mounted by the ASM instance. Returns no rows
V$ASM_CLIENT Displays a row for each database instance using a disk group managed by the ASM instance. Displays a row for the ASM instance if the database has open ASM files.
V$ASM_DISK Displays a row for each disk discovered by the ASM instance, including disks which are not part of any disk group. Displays a row for each disk in disk groups in use by the database instance.
V$ASM_DISKGROUP Displays a row for each disk group discovered by the ASM instance. Displays a row for each disk group mounted by the local ASM instance.
V$ASM_FILE Displays a row for each file for each disk group mounted by the ASM instance. Displays no rows.
V$ASM_OPERATION Displays a row for each file for each long running operation executing in the ASM instance. Displays no rows.
V$ASM_TEMPLATE Displays a row for each template present in each disk group mounted by the ASM instance. Displays a row for each template present in each disk group mounted by the ASM instance with which the database instance communicates.

ASM Filenames

There are several ways to reference ASM file. Some forms are used during creation and some for referencing ASM files. The forms for file creation are incomplete, relying on ASM to create the fully qualified name, which can be retrieved from the supporting views. The forms of the ASM filenames are summarised below.

Filename Type Format
Fully Qualified ASM Filename +dgroup/dbname/file_type/file_type_tag.file.incarnation
Numeric ASM Filename +dgroup.file.incarnation
Alias ASM Filenames +dgroup/directory/filename
Alias ASM Filename with Template +dgroup(template)/alias
Incomplete ASM Filename +dgroup
Incomplete ASM Filename with Template +dgroup(template)

SQL and ASM

ASM filenames can be used in place of conventional filenames for most Oracle file types, including controlfiles, datafiles, logfiles etc. For example, the following command creates a new tablespace with a datafile in the disk_group_1 disk group.

CREATE TABLESPACE my_ts DATAFILE '+disk_group_1' SIZE 100M AUTOEXTEND ON;

Migrating to ASM Using RMAN

The following method shows how a primary database can be migrated to ASM from a disk based backup:

  • Disable change tracking (only available in Enterprise Edition) if it is currently being used.
    SQL> ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;
  • Shutdown the database.
    SQL> SHUTDOWN IMMEDIATE
  • Modify the parameter file of the target database as follows:
    • Set the DB_CREATE_FILE_DEST and DB_CREATE_ONLINE_LOG_DEST_n parameters to the relevant ASM disk groups.
    • Remove the CONTROL_FILES parameter from the spfile so the control files will be moved to the DB_CREATE_* destination and the spfile gets updated automatically. If you are using a pfile the CONTROL_FILES parameter must be set to the appropriate ASM files or aliases.
  • Start the database in nomount mode.
    RMAN> STARTUP NOMOUNT
  • Restore the controlfile into the new location from the old location.
    RMAN> RESTORE CONTROLFILE FROM 'old_control_file_name';
  • Mount the database.
    RMAN> ALTER DATABASE MOUNT;
  • Copy the database into the ASM disk group.
    RMAN> BACKUP AS COPY DATABASE FORMAT '+disk_group';
  • Switch all datafile to the new ASM location.
    RMAN> SWITCH DATABASE TO COPY;
  • Open the database.
    RMAN> ALTER DATABASE OPEN;
  • Create new redo logs in ASM and delete the old ones.
  • Enable change tracking if it was being used.
    SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;

Source : http://www.oracle-base.com/articles/10g/AutomaticStorageManagement10g.php