Oracle DBA Day to Day Scripts: January 2021

Friday, January 22, 2021

adding disk

Steps to add a raw disk to the asm diskgroup

/dev/oracleasm/disks > sudo oracleasm querydisk /dev/mapper/bolora1a_cooraprod06_datafile_4

/dev/oracleasm/disks > sudo oracleasm querydisk /dev/mapper/bolora1a_log_6
/dev/oracleasm/disks > sudo oracleasm querydisk /dev/mapper/bolora1a_log_5

sudo /etc/init.d/oracleasm querydisk /dev/mapper/bolora1a_cooraprod06_datafile_4

dmzora:+ASM@bolora1a /dev/mapper > sudo /etc/init.d/oracleasm querydisk /dev/mapper/bolora1a_cooraprod06_datafile_4
Device "/dev/mapper/bolora1a_cooraprod06_datafile_4" is not marked as an ASM disk
dmzora:+ASM@bolora1a /dev/mapper > sudo /etc/init.d/oracleasm querydisk /dev/mapper/bolora1a_cooraprod06_datafile_3
Device "/dev/mapper/bolora1a_cooraprod06_datafile_3" is not marked as an ASM disk
dmzora:+ASM@bolora1a /dev/mapper > date
Fri Jun 13 02:49:41 MDT 2014

sudo /etc/init.d/oracleasm createdisk LOG5 /dev/mapper/bolora1a_log_5

sudo /etc/init.d/oracleasm createdisk LOG6 /dev/mapper/bolora1a_log_6

sudo /etc/init.d/oracleasm createdisk LOG5 /dev/mapper/bolora1a_log_5

sudo /etc/init.d/oracleasm createdisk LOG5 /dev/mapper/bolora1b_log_5
sudo /etc/init.d/oracleasm createdisk LOG6 /dev/mapper/bolora1b_log_6

/dev/mapper/bolora1b_log_6

sudo /etc/init.d/oracleasm createdisk LOG5 /dev/mapper/bolora1a_log_5
sudo /etc/init.d/oracleasm createdisk LOG6 /dev/mapper/bolora1a_log_6

sudo chown dmzora:dba LOG5
sudo chown dmzora:dba LOG6

sudo chown dmzora:dba LOG5

sudo chmod 660 LOG5
sudo chmod 660 LOG6

sudo chmod 660 DATA_ORPROD06_02

CREATE DISKGROUP LOG5 EXTERNAL REDUNDANCY DISK '/dev/oracleasm/disks/LOG5' attribute 'compatible.asm' = '11.2','au_size' = '4M';

CREATE DISKGROUP LOG6 EXTERNAL REDUNDANCY DISK '/dev/oracleasm/disks/LOG6' attribute 'compatible.asm' = '11.2','au_size' = '4M';

/dev/oracleasm/disks/DATA_ORPROD06_01 0 PROVISIONED
0 /dev/oracleasm/disks/DATA_ORPROD06_02

CREATE DISKGROUP DATAFILE_COORAPROD06
EXTERNAL REDUNDANCY DISK
'/dev/oracleasm/disks/DATA_ORPROD06_01',
'/dev/oracleasm/disks/DATA_ORPROD06_02'
attribute 'compatible.asm' = '11.2','au_size' = '4M';

CREATE DISKGROUP DATAFILE_COORAPROD06
EXTERNAL REDUNDANCY DISK
'/dev/oracleasm/disks/DATA_ORPROD06_01',
'/dev/oracleasm/disks/DATA_ORPROD06_02'
attribute 'compatible.asm' = '11.2','au_size' = '4M';

DATA_ORPROD06_02

Sarada,

Here are the steps for adding of disks and rebalancing.

Check for the disk groups:

SQL>
set lines 250
SELECT NAME, TOTAL_MB, FREE_MB, (TOTAL_MB - FREE_MB) USED_MB,STATE, TYPE FROM V$ASM_DISKGROUP ;

NAME TOTAL_MB FREE_MB USED_MB STATE TYPE
------------------------------ ---------- ---------- ---------- ----------- ------
ARCHLOG 307260 296404 10856 MOUNTED EXTERN
DATAFILE_COORAPROD46 716940 32724 684216 MOUNTED EXTERN
DATAFILE_COORAPROD68 102420 29896 72524 MOUNTED EXTERN
DATAFILE_COORAPROD75 204840 200788 4052 MOUNTED EXTERN
DATAFILE_CTORAPROD36 2048400 221516 1826884 MOUNTED EXTERN
LOG1 20484 11148 9336 MOUNTED EXTERN
LOG2 20484 11180 9304 MOUNTED EXTERN

7 rows selected.

SQL> select NAME,GROUP_NUMBER from v$asm_diskgroup;

NAME GROUP_NUMBER
------------------------------ ------------
ARCHLOG 1
DATAFILE_COORAPROD46 2
DATAFILE_COORAPROD68 3
DATAFILE_COORAPROD75 4
DATAFILE_CTORAPROD36 5
LOG1 6
LOG2 7

7 rows selected.

SQL>
set linesize 150
set pagesize 9999
column path format a70
select GROUP_NUMBER,NAME,PATH,TOTAL_MB/1024,HEADER_STATUS from v$asm_disk order by total_mb;

GROUP_NUMBER NAME PATH TOTAL_MB/1024 HEADER_STATU
------------ ------------------------------ ---------------------------------------------------------------------- ------------- ------------
0 /dev/oracleasm/disks/VMX_DATA38 0 FORMER
0 /dev/oracleasm/disks/VMX_DATA25 0 FORMER
0 /dev/oracleasm/disks/VMX_R10_LOG4 0 FORMER
0 /dev/oracleasm/disks/VMX_DATA26 0 FORMER
0 /dev/oracleasm/disks/VMX_R10_LOG3 0 FORMER
0 /dev/oracleasm/disks/VMX_DATA40 0 FORMER
0 /dev/oracleasm/disks/VMX_DATA34 0 FORMER
0 /dev/oracleasm/disks/VMX_DATA39 0 FORMER
0 /dev/oracleasm/disks/VMX_DATA35 0 FORMER
6 LOG1_0000 /dev/oracleasm/disks/VMX_R10_LOG1 20.0039063 MEMBER
7 LOG2_0000 /dev/oracleasm/disks/VMX_R10_LOG2 20.0039063 MEMBER
5 DATAFILE_CTORAPROD36_0008 /dev/oracleasm/disks/VMX_DATA31 100.019531 MEMBER
5 DATAFILE_CTORAPROD36_0007 /dev/oracleasm/disks/VMX_DATA32 100.019531 MEMBER
5 DATAFILE_CTORAPROD36_0006 /dev/oracleasm/disks/VMX_DATA33 100.019531 MEMBER
4 DATAFILE_COORAPROD75_0001 /dev/oracleasm/disks/VMX_DATA2 100.019531 MEMBER
4 DATAFILE_COORAPROD75_0000 /dev/oracleasm/disks/VMX_DATA1 100.019531 MEMBER
5 DATAFILE_CTORAPROD36_0010 /dev/oracleasm/disks/VMX_DATA3 100.019531 MEMBER
5 DATAFILE_CTORAPROD36_0005 /dev/oracleasm/disks/VMX_DATA4 100.019531 MEMBER
5 DATAFILE_CTORAPROD36_0004 /dev/oracleasm/disks/VMX_DATA5 100.019531 MEMBER
5 DATAFILE_CTORAPROD36_0003 /dev/oracleasm/disks/VMX_DATA6 100.019531 MEMBER
5 DATAFILE_CTORAPROD36_0002 /dev/oracleasm/disks/VMX_DATA7 100.019531 MEMBER
5 DATAFILE_CTORAPROD36_0001 /dev/oracleasm/disks/VMX_DATA8 100.019531 MEMBER
5 DATAFILE_CTORAPROD36_0000 /dev/oracleasm/disks/VMX_DATA9 100.019531 MEMBER
3 DATAFILE_COORAPROD68_0000 /dev/oracleasm/disks/VMX_DATA10 100.019531 MEMBER
2 DATAFILE_COORAPROD46_0000 /dev/oracleasm/disks/VMX_DATA11 100.019531 MEMBER
2 DATAFILE_COORAPROD46_0001 /dev/oracleasm/disks/VMX_DATA12 100.019531 MEMBER
2 DATAFILE_COORAPROD46_0002 /dev/oracleasm/disks/VMX_DATA13 100.019531 MEMBER
2 DATAFILE_COORAPROD46_0004 /dev/oracleasm/disks/VMX_DATA15 100.019531 MEMBER
2 DATAFILE_COORAPROD46_0003 /dev/oracleasm/disks/VMX_DATA14 100.019531 MEMBER
2 DATAFILE_COORAPROD46_0005 /dev/oracleasm/disks/VMX_DATA16 100.019531 MEMBER
1 ARCHLOG_0001 /dev/oracleasm/disks/VMX_DATA17 100.019531 MEMBER
1 ARCHLOG_0002 /dev/oracleasm/disks/VMX_DATA18 100.019531 MEMBER
2 DATAFILE_COORAPROD46_0006 /dev/oracleasm/disks/VMX_DATA19 100.019531 MEMBER
5 DATAFILE_CTORAPROD36_0011 /dev/oracleasm/disks/VMX_DATA29 100.019531 MEMBER
5 DATAFILE_CTORAPROD36_0012 /dev/oracleasm/disks/VMX_DATA28 100.019531 MEMBER
5 DATAFILE_CTORAPROD36_0013 /dev/oracleasm/disks/VMX_DATA27 100.019531 MEMBER
5 DATAFILE_CTORAPROD36_0017 /dev/oracleasm/disks/VMX_DATA20 100.019531 MEMBER
5 DATAFILE_CTORAPROD36_0019 /dev/oracleasm/disks/VMX_DATA24 100.019531 MEMBER
5 DATAFILE_CTORAPROD36_0018 /dev/oracleasm/disks/VMX_DATA23 100.019531 MEMBER
5 DATAFILE_CTORAPROD36_0014 /dev/oracleasm/disks/VMX_DATA22 100.019531 MEMBER
1 ARCHLOG_0000 /dev/oracleasm/disks/VMX_DATA21 100.019531 MEMBER
5 DATAFILE_CTORAPROD36_0016 /dev/oracleasm/disks/VMX_DATA37 100.019531 MEMBER
5 DATAFILE_CTORAPROD36_0015 /dev/oracleasm/disks/VMX_DATA36 100.019531 MEMBER
5 DATAFILE_CTORAPROD36_0009 /dev/oracleasm/disks/VMX_DATA30 100.019531 MEMBER

44 rows selected.

Given devices:

oracle:+ASM@lnxora14 /oracle/admin/scripts > sudo /etc/init.d/oracleasm querydisk /dev/emcpowerbf
Device "/dev/emcpowerbf" is not marked as an ASM disk
oracle:+ASM@lnxora14 /oracle/admin/scripts > sudo /etc/init.d/oracleasm querydisk /dev/emcpowerbg
Device "/dev/emcpowerbg" is not marked as an ASM disk
oracle:+ASM@lnxora14 /oracle/admin/scripts > sudo /etc/init.d/oracleasm querydisk /dev/emcpowerbh
Device "/dev/emcpowerbh" is not marked as an ASM disk

or

-----------------------------------------------------------------------------------------------------------
[{{{{{ sudo oracleasm querydisk /dev/mapper/test23b_asm_data_3_new

oracle:+ASM@lnxora17 /dev/oracleasm/disks > sudo oracleasm querydisk /dev/mapper/test23b_asm_data_3_new
[sudo] password for oracle:
Device "/dev/mapper/test23b_asm_data_3_new" is not marked as an ASM disk

cd /dev/oracleasm/disks

DATA_ORTEST23_03

sudo /usr/sbin/asmtool -C -l /dev/oracleasm/disks -n DATA_ORTEST23_03 -s /dev/mapper/test23b_asm_data_3_new -a force=yes

sudo /etc/init.d/oracleasm createdisk DATA_ORTEST23_03 /dev/mapper/test23b_asm_data_3_new

sudo /etc/init.d/oracleasm listdisks ]}}}}}}}}}

---------------------------------------------------------------------------------------------------------------

sudo /usr/sbin/asmtool -C -l /dev/oracleasm -n VMX_DATA41 -s /dev/emcpowerbf -a force=yes
sudo /usr/sbin/asmtool -C -l /dev/oracleasm -n VMX_DATA42 -s /dev/emcpowerbg -a force=yes
sudo /usr/sbin/asmtool -C -l /dev/oracleasm -n VMX_DATA43 -s /dev/emcpowerbh -a force=yes

oracle:+ASM@lnxora14 /dev/oracleasm/disks > sudo /usr/sbin/asmtool -C -l /dev/oracleasm -n VMX_DATA41 -s /dev/emcpowerbf -a force=yes
[sudo] password for oracle:
asmtool: Device "/dev/emcpowerbf" is not a partition
asmtool: Continuing anyway

oracle:+ASM@lnxora14 /dev/oracleasm/disks > sudo /usr/sbin/asmtool -C -l /dev/oracleasm -n VMX_DATA42 -s /dev/emcpowerbg -a force=yes
asmtool: Device "/dev/emcpowerbg" is not a partition
asmtool: Continuing anyway

oracle:+ASM@lnxora14 /dev/oracleasm/disks > sudo /usr/sbin/asmtool -C -l /dev/oracleasm -n VMX_DATA43 -s /dev/emcpowerbh -a force=yes
asmtool: Device "/dev/emcpowerbh" is not a partition
asmtool: Continuing anyway

sudo /etc/init.d/oracleasm listdisks

oracle:+ASM@lnxora14 /dev/oracleasm/disks > ls -la

brw-r----- 1 root root 120, 912 Jun 6 17:36 VMX_DATA41
brw-r----- 1 root root 120, 928 Jun 6 17:37 VMX_DATA42
brw-r----- 1 root root 120, 944 Jun 6 17:37 VMX_DATA43

oracle:+ASM@lnxora14 /dev/oracleasm/disks > sudo chown oracle:dba VMX_DATA41
oracle:+ASM@lnxora14 /dev/oracleasm/disks > sudo chown oracle:dba VMX_DATA42
oracle:+ASM@lnxora14 /dev/oracleasm/disks > sudo chown oracle:dba VMX_DATA43

oracle:+ASM@lnxora14 /dev/oracleasm/disks > ls -la
brw-r----- 1 oracle dba 120, 912 Jun 6 17:36 VMX_DATA41
brw-r----- 1 oracle dba 120, 928 Jun 6 17:37 VMX_DATA42
brw-r----- 1 oracle dba 120, 944 Jun 6 17:37 VMX_DATA43

r - 4
w - 2
x - 1

oracle:+ASM@lnxora14 /dev/oracleasm/disks > sudo chmod 660 VMX_DATA41
oracle:+ASM@lnxora14 /dev/oracleasm/disks > sudo chmod 660 VMX_DATA42
oracle:+ASM@lnxora14 /dev/oracleasm/disks > sudo chmod 660 VMX_DATA43
oracle:+ASM@lnxora14 /dev/oracleasm/disks > ls -la

brw-rw---- 1 oracle dba 120, 912 Jun 6 17:36 VMX_DATA41
brw-rw---- 1 oracle dba 120, 928 Jun 6 17:37 VMX_DATA42
brw-rw---- 1 oracle dba 120, 944 Jun 6 17:37 VMX_DATA43

oracle:+ASM@lnxora14 /dev/oracleasm/disks > sqlasm

SQL*Plus: Release 11.2.0.3.0 Production on Thu Jun 6 17:40:31 2013

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Automatic Storage Management option

SQL>
set lines 250
col path format a70
select GROUP_NUMBER,NAME,PATH,TOTAL_MB/1024,HEADER_STATUS from v$asm_disk where HEADER_STATUS='PROVISIONED';

GROUP_NUMBER NAME PATH TOTAL_MB/1024 HEADER_STATU
------------ ------------------------------ ---------------------------------------------------------------------- ------------- ------------
0 /dev/oracleasm/disks/VMX_DATA43 0 PROVISIONED
0 /dev/oracleasm/disks/VMX_DATA41 0 PROVISIONED
0 /dev/oracleasm/disks/VMX_DATA42 0 PROVISIONED

Open alert log to check any errors found

Always give complete path(shown in above query) of disk and should be in provisioned header status while creating diskgroup :
==============================================================================================================================

CREATE DISKGROUP MYTEST EXTERNAL REDUNDANCY DISK '/dev/oracleasm/disks/VMX_DATA41','/dev/oracleasm/disks/VMX_DATA42','/dev/oracleasm/disks/VMX_DATA43';

Open alert log to check any errors found
---------------------------------------

SQL> ALTER DISKGROUP MYTEST CHECK ALL;

Diskgroup altered.

SQL> ALTER DISKGROUP MYTEST MOUNT ;

Diskgroup altered.

SQL> DROP DISKGROUP MYTEST ;

Diskgroup dropped.

SQL>
set lines 250
col path format a70
select GROUP_NUMBER,NAME,PATH,TOTAL_MB/1024,HEADER_STATUS from v$asm_disk where HEADER_STATUS='PROVISIONED';

it shOULD SHOW NO ROWS

SQL>ALTER DISKGROUP DATAFILE_COORAPROD46 ADD DISK '/dev/oracleasm/disks/VMX_DATA41' NAME DATAFILE_COORAPROD46_0007 ,'/dev/oracleasm/disks/VMX_DATA42' NAME DATAFILE_COORAPROD46_0008 REBALANCE POWER 4;

Diskgroup altered.

SQL> set lines 250
col path format a70
select GROUP_NUMBER,NAME,PATH,TOTAL_MB/1024,HEADER_STATUS from v$asm_disk;

SQL> Select operation, state, power, est_rate, est_minutes, round((sofar/EST_WORK)*100,2) pct_comp From v$asm_operation;

OPERA STAT POWER EST_RATE EST_MINUTES PCT_COMP
----- ---- ---------- ---------- ----------- ----------
REBAL RUN 4 2443 14 5.4

SQL>
set lines 250
SELECT NAME, TOTAL_MB, FREE_MB, (TOTAL_MB - FREE_MB) USED_MB,STATE, TYPE FROM V$ASM_DISKGROUP ;

Drop old disks if any to recliam the space( note: after adding new disks only) [mostly while storage migration] :
----------------------------------------------------------------------------------------------------------------
ALTER DISKGROUP DATAFILE_COORAPROD46 DROP DISK 'DATAFILE_COORAPROD46_0007' REBALANCE POWER 5;

How to tar a file in linux

Let say u have a directory under /rajesh/logs/ and logs has multiple logs and trace files, and you would like to create or tar the logs files means execute the command below as

tar -zcvf tar_filename.tar.gz /rajesh/logs

In your current directory the file will be created

-c : Create a tar ball.
-v : Verbose output (show progress).
-f : Output tar ball archive file name.
-x : Extract all files from archive.tar.
-t : Display the contents (file list) of an archive.

Create a tar ball called /tmp/data.tar for /home/vivek/data directory, enter:

tar -cvf /tmp/data.tar /home/vivek/data

To View a Tar Ball Contains (list file inside a tar ball)

Type the following command:

tar -tvf /tmp/data.ta

To Extract a Tar Ball

Type the following command to extract /tmp/data.tar in a current directory, enter:

tar -xvf /tmp/data.ta

Type the following command to extract resume.pdf file from an archive called data-backup.tar in a current directory, enter:

tar -xvf data-backup.tar resume.pdf

Type the following command to extract pic1.jpg, file1.doc, and foo.mov files from an archive called data-backup.tar in a current directory, enter:

tar -xvf data-backup.tar pic1.jpg file1.doc foo.movf

Type the following command to extract /tmp/data.tar in a directory called /home/sales/data, enter:

tar -xvf /tmp/data.tar -C /home/sales/data

Backup hangs

Some times , rman sessions can hang due to so many reasons, or some time we may have to stop

the backup jobs, during those times we can use the below query to grab oracle rman session details to

kill from the database level

COLUMN CLIENT_INFO FORMAT a30

COLUMN SID FORMAT 999

COLUMN SPID FORMAT 9999

SELECT s.SID, p.SPID, s.CLIENT_INFO
FROM   V$PROCESS p, V$SESSION s
WHERE  p.ADDR = s.PADDR
AND    CLIENT_INFO LIKE 'rman%'
/

Oracle Database size

To Find an oracle database size

SELECT SUM (bytes) / 1024 / 1024 / 1024 AS GB FROM dba_data_files;

The above dba_data_files view will calculate size of the (actual) all datafiles created with the

current size.

ASM_Administration

  REM ASM views:

REM VIEW            |ASM INSTANCE                                     |DB INSTANCE

REM ----------------------------------------------------------------------------------------------------------

REM V$ASM_DISKGROUP |Describes a disk group (number, name, size       |Contains one row for every open ASM

REM                 |related info, state, and redundancy type)        |disk in the DB instance.

REM V$ASM_CLIENT    |Identifies databases using disk groups           |Contains no rows.

REM                 |managed by the ASM instance.                     |

REM V$ASM_DISK      |Contains one row for every disk discovered       |Contains rows only for disks in the

REM                 |by the ASM instance, including disks that        |disk groups in use by that DB instance.

REM                 |are not part of any disk group.                  |

REM V$ASM_FILE      |Contains one row for every ASM file in every     |Contains rows only for files that are

REM                 |disk group mounted by the ASM instance.          |currently open in the DB instance.

REM V$ASM_TEMPLATE  |Contains one row for every template present in   |Contains no rows.

REM                 |every disk group mounted by the ASM instance.    |

REM V$ASM_ALIAS     |Contains one row for every alias present in      |Contains no rows.

REM                 |every disk group mounted by the ASM instance.    |

REM v$ASM_OPERATION |Contains one row for every active ASM long       |Contains no rows.

REM                 |running operation executing in the ASM instance. |

set wrap off

set lines 155 pages 9999

col "Group Name" for a6    Head "Group|Name"

col "Disk Name"  for a10

col "State"      for a10

col "Type"       for a10   Head "Diskgroup|Redundancy"

col "Total GB"   for 9,990 Head "Total|GB"

col "Free GB"    for 9,990 Head "Free|GB"

col "Imbalance"  for 99.9  Head "Percent|Imbalance"

col "Variance"   for 99.9  Head "Percent|Disk Size|Variance"

col "MinFree"    for 99.9  Head "Minimum|Percent|Free"

col "MaxFree"    for 99.9  Head "Maximum|Percent|Free"

col "DiskCnt"    for 9999  Head "Disk|Count"

prompt

prompt ASM Disk Groups

prompt ===============

SELECT g.group_number  "Group"

,      g.name          "Group Name"

,      g.state         "State"

,      g.type          "Type"

,      g.total_mb/1024 "Total GB"

,      g.free_mb/1024  "Free GB"

,      100*(max((d.total_mb-d.free_mb)/d.total_mb)-min((d.total_mb-d.free_mb)/d.total_mb))/max((d.total_mb-d.free_mb)/d.total_mb) "Imbalance"

,      100*(max(d.total_mb)-min(d.total_mb))/max(d.total_mb) "Variance"

,      100*(min(d.free_mb/d.total_mb)) "MinFree"

,      100*(max(d.free_mb/d.total_mb)) "MaxFree"

,      count(*)        "DiskCnt"

FROM v$asm_disk d, v$asm_diskgroup g

WHERE d.group_number = g.group_number and

d.group_number <> 0 and

d.state = 'NORMAL' and

d.mount_status = 'CACHED'

GROUP BY g.group_number, g.name, g.state, g.type, g.total_mb, g.free_mb

ORDER BY 1;

prompt ASM Disks In Use

prompt ================

col "Group"          for 999

col "Disk"           for 999

col "Header"         for a9

col "Mode"           for a8

col "State"          for a8

col "Created"        for a10          Head "Added To|Diskgroup"

--col "Redundancy"     for a10

--col "Failure Group"  for a10  Head "Failure|Group"

col "Path"           for a19

--col "ReadTime"       for 999999990    Head "Read Time|seconds"

--col "WriteTime"      for 999999990    Head "Write Time|seconds"

--col "BytesRead"      for 999990.00    Head "GigaBytes|Read"

--col "BytesWrite"     for 999990.00    Head "GigaBytes|Written"

col "SecsPerRead"    for 9.000        Head "Seconds|PerRead"

col "SecsPerWrite"   for 9.000        Head "Seconds|PerWrite"

select group_number  "Group"

,      disk_number   "Disk"

,      header_status "Header"

,      mode_status   "Mode"

,      state         "State"

,      create_date   "Created"

--,      redundancy    "Redundancy"

,      total_mb/1024 "Total GB"

,      free_mb/1024  "Free GB"

,      name          "Disk Name"

--,      failgroup     "Failure Group"

,      path          "Path"

--,      read_time     "ReadTime"

--,      write_time    "WriteTime"

--,      bytes_read/1073741824    "BytesRead"

--,      bytes_written/1073741824 "BytesWrite"

,      read_time/reads "SecsPerRead"

,      write_time/writes "SecsPerWrite"

from   v$asm_disk_stat

where header_status not in ('FORMER','CANDIDATE')

order by group_number

,        disk_number

/

Prompt File Types in Diskgroups

Prompt ========================

col "File Type"      for a16

col "Block Size"     for a5    Head "Block|Size"

col "Gb"             for 9990.00

col "Files"          for 99990

break on "Group Name" skip 1 nodup

select g.name                                   "Group Name"

,      f.TYPE                                   "File Type"

,      f.BLOCK_SIZE/1024||'k'                   "Block Size"

,      f.STRIPED

,        count(*)                               "Files"

,      round(sum(f.BYTES)/(1024*1024*1024),2)   "Gb"

from   v$asm_file f,v$asm_diskgroup g

where  f.group_number=g.group_number

group by g.name,f.TYPE,f.BLOCK_SIZE,f.STRIPED

order by 1,2;

clear break

prompt Instances currently accessing these diskgroups

prompt ==============================================

col "Instance" form a8

select c.group_number  "Group"

,      g.name          "Group Name"

,      c.instance_name "Instance"

from   v$asm_client c

,      v$asm_diskgroup g

where  g.group_number=c.group_number

/

prompt Free ASM disks and their paths

prompt ==============================

col "Disk Size"    form a9

select header_status                   "Header"

, mode_status                     "Mode"

, path                            "Path"

, lpad(round(os_mb/1024),7)||'Gb' "Disk Size"

from   v$asm_disk

where header_status in ('FORMER','CANDIDATE')

order by path

/

prompt Current ASM disk operations

prompt ===========================

select *

from   v$asm_operation

/

Oracle DBA Day to Day Scripts