How to figure out space used by all databases under ASM Disk Group.

Below script will show space usage used by all databases under ASM Diskgroup.

echo “Database Used_MB Mirror_used_MB”

for i in `asmcmd ls +vg1/*|grep +vg1|grep “:”|awk -F: ‘{ print $1}’`
do
printf $i
asmcmd du $i|grep -v Used
done;

Convert Non-ASM to ASM or ASM to Non-ASM

1. Convert from Non-ASM to ASM
1.1 Startup the database nomount
SQL> startup nomount

1.2 Restore controlfile
You may need to change parameter file before you start the instance in this case.
RMAN> RESTORE CONTROLFILE FROM ‘/u01/TST/control01.ctl’;

1.3 Using RMAN copy the datafiles from NON-ASN to ASM
RMAN> BACKUP AS COPY DATABASE FORMAT ‘+DG1′;

1.4 Using RMAN rename datafiles
RMAN> SWITCH DATABASE TO COPY;

1.5 Switch tempfile and open database
RMAN> run {
set newname for tempfile 1 to ‘+ASM’;
set newname for tempfile 2 to ‘+ASM’;
switch tempfile all;
}
1.6 Open the database
You need to drop and recreate redo log files by yourself.

2. Convert ASM to Non-ASM database
2.1 Startup the database nomount
SQL> startup nomount

2.2 Restore controlfile
You may need to change parameter file before you start the instance in this case.
RMAN> RESTORE CONTROLFILE FROM ‘+DG1/TST/control01.ctl’;

2.3 Using RMAN copy the datafiles from NON-ASN to ASM
RMAN> BACKUP AS COPY DATABASE FORMAT ‘/oradata/DB/datafile/%U’;

2.4 Using RMAN rename datafiles
RMAN> SWITCH DATABASE TO COPY;

2.5 Recreate the tempfile and redo logfiles as before.