ASM Troubleshooting
# /proc/partitions - shows the major minor numbers for the device at OS level
# asmcmd lsdsk -p
# /etc/init.d/oracleasm status
# /etc/init.d/oracleasm listdisks
# ls -l /dev/oracleasm
# ls -l /dev/oracleasm/disks
# oracleasm query disk /dev/mapper/<ASMLABEL>
# fdisk -l /dev/mapper/<ASMLABEL>
# fdisk -l /dev/sd-10 ----- OS level
Set the environment for the asm
-------------------------------
uname
cat /etc/oratab OR cat /var/opt/oracle/oratab
export ORACLE_SID=<DBNAME>
export ORACLE_HOME=<>
export PATH=$PATH:$ORACLE_HOME/bin
ps -ef| grep asm
. oraenv
+ASM
ASM Space Report
----------------
SET LINESIZE 150
SET PAGESIZE 9999
SET VERIFY off
COLUMN group_name FORMAT a20 HEAD 'Disk Group|Name'
COLUMN sector_size FORMAT 99,999 HEAD 'Sector|Size'
COLUMN block_size FORMAT 99,999 HEAD 'Block|Size'
COLUMN allocation_unit_size FORMAT 999,999,999 HEAD 'Allocation|Unit Size'
COLUMN state FORMAT a11 HEAD 'State'
COLUMN type FORMAT a6 HEAD 'Type'
COLUMN total_mb FORMAT 999,999,999 HEAD 'Total Size (MB)'
COLUMN used_mb FORMAT 999,999,999 HEAD 'Used Size (MB)'
COLUMN pct_used FORMAT 999.99 HEAD 'Pct. Used'
SELECT
distinct name group_name
, sector_size sector_size
, block_size block_size
, allocation_unit_size allocation_unit_size
, state state
, type type
, total_mb total_mb
, (total_mb - free_mb) used_mb
, ROUND((1- (free_mb / total_mb))*100, 2) pct_used
FROM gv$asm_diskgroup ORDER BY name
/
ASM Disk Space Usage Report In Detail
-------------------------------------
SET LINESIZE 150
SET PAGESIZE 9999
SET VERIFY off
COLUMN disk_group_name FORMAT a15 HEAD 'Disk Group Name'
COLUMN disk_file_path FORMAT a17 HEAD 'Path'
COLUMN disk_file_name FORMAT a20 HEAD 'File Name'
COLUMN disk_file_fail_group FORMAT a20 HEAD 'Fail Group'
COLUMN total_mb FORMAT 999,999,999 HEAD 'File Size (MB)'
COLUMN used_mb FORMAT 999,999,999 HEAD 'Used Size (MB)'
COLUMN pct_used FORMAT 999.99 HEAD 'Pct. Used'
break on report on disk_group_name skip 1
compute sum label 'Disk Group' of total_mb used_mb on disk_group_name
compute sum label 'ASM Disk Group Total Size in MB' of total_mb used_mb on report
SELECT
NVL(a.name, '[CANDIDATE]') disk_group_name
, b.path disk_file_path
, b.name disk_file_name
, b.failgroup disk_file_fail_group
, b.total_mb total_mb
, (b.total_mb - b.free_mb) used_mb
, ROUND((1- (b.free_mb / b.total_mb))*100, 2) pct_used
FROM v$asm_diskgroup a RIGHT OUTER JOIN v$asm_disk b USING (group_number) where b.header_status <> 'PROVISIONED'
ORDER BY a.name
/
OTHER ASM QUERIES
-----------------
set pages 9999 lines 900
SELECT GROUP_NUMBER, NAME, TYPE, STATE, ceil (total_mb/1024) TOTAL_GB,ceil (free_mb/1024) FREE_GB,
ceil ((required_mirror_free_mb)/1024),ceil ((usable_file_mb)/1024), SYSDATE
FROM V$ASM_DISKGROUP order by NAME;
SELECT GROUP_NUMBER, NAME, TYPE, STATE, ceil (total_mb/1024) TOTAL_GB,ceil (free_mb/1024) FREE_GB,
ceil ((required_mirror_free_mb)/1024),ceil ((usable_file_mb)/1024), SYSDATE
FROM GV$ASM_DISKGROUP order by NAME;
select GROUP_NUMBER,DISK_NUMBER,TOTAL_MB,FREE_MB,NAME,MOUNT_STATUS from v$asm_disk;
select INST_ID,GROUP_NUMBER,DISK_NUMBER,TOTAL_MB,FREE_MB,NAME,MOUNT_STATUS from gv$asm_disk;
select table_name from dict where table_name like '%ASM%';
=====================================================================================================================================
ASM TABLESPACE Scripts
=========================
When a tablespace is getting filled up, action need to be taken (Preference-wise)
1. Resize the datafile.
2. If AUTOEXTEND ON,then resize the maxsize of the datafile.
3. Add a new datafile to the tablespace.
Note:-
Check the availability of the free space on the disk at OS level.
df -h (Linux,AIX)
df -gt
oracle instance process running on the server
---------------------------------------------
ps -ef | grep pmon
Set the environment for the database
------------------------------------
uname
cat /etc/oratab (Linux/AIX)
(OR)
cat /var/opt/oracle/oratab
export ORACLE_SID=<INSTANCE_NAME>
export ORACLE_HOME=/usr/app/oracle/product/10.2.0/
export PATH=$PATH:$ORACLE_HOME/bin
Database Details
----------------
sqlplus "/as sysdba"
set pages 9999 lines 300
col OPEN_MODE for a10
col HOST_NAME for a20
select name DB_NAME,INSTANCE_NAME,HOST_NAME,DATABASE_ROLE,OPEN_MODE,version DB_VERSION,LOGINS,
to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME" from v$database,gv$instance;
ASM Tablespace Utilization Script
----------------------------------
SET LINESIZE 300
SET PAGESIZE 9999
SET VERIFY off
COLUMN group_name FORMAT a20 HEAD 'Disk Group|Name'
COLUMN sector_size FORMAT 99,999 HEAD 'Sector|Size'
COLUMN block_size FORMAT 99,999 HEAD 'Block|Size'
COLUMN allocation_unit_size FORMAT 999,999,999 HEAD 'Allocation|Unit Size'
COLUMN state FORMAT a11 HEAD 'State'
COLUMN type FORMAT a6 HEAD 'Type'
COLUMN total_mb FORMAT 999,999,999 HEAD 'Total Size (GB)'
COLUMN free_mb FORMAT 999,999,999 HEAD 'Free Size (GB)'
COLUMN used_mb FORMAT 999,999,999 HEAD 'Used Size (GB)'
COLUMN pct_used FORMAT 999.99 HEAD 'Pct. Used'
SELECT
distinct name group_name
, sector_size sector_size
, block_size block_size
, allocation_unit_size allocation_unit_size
, state state
, type type
, round(total_mb/1024) "total_gb"
, round(free_mb/1024) "free_gb"
, round((total_mb - free_mb) / 1024) "used_gb"
, round((1- (free_mb / total_mb))*100, 2) "pct_used"
from v$asm_diskgroup ORDER BY name
/
ASM Disk Space Usage Script (In Detail)
---------------------------------------
SET LINESIZE 150
SET PAGESIZE 9999
SET VERIFY off
COLUMN disk_group_name FORMAT a15 HEAD 'Disk Group Name'
COLUMN disk_file_path FORMAT a17 HEAD 'Path'
COLUMN disk_file_name FORMAT a20 HEAD 'File Name'
COLUMN disk_file_fail_group FORMAT a20 HEAD 'Fail Group'
COLUMN total_mb FORMAT 999,999,999 HEAD 'File Size (MB)'
COLUMN used_mb FORMAT 999,999,999 HEAD 'Used Size (MB)'
COLUMN pct_used FORMAT 999.99 HEAD 'Pct. Used'
break on report on disk_group_name skip 1
compute sum label 'Disk Group' of total_mb used_mb on disk_group_name
compute sum label 'ASM Disk Group Total Size in MB' of total_mb used_mb on report
SELECT
NVL(a.name, '[CANDIDATE]') disk_group_name
, b.path disk_file_path
, b.name disk_file_name
, b.failgroup disk_file_fail_group
, b.total_mb total_mb
, (b.total_mb - b.free_mb) used_mb
, ROUND((1- (b.free_mb / b.total_mb))*100, 2) pct_used
FROM v$asm_diskgroup a RIGHT OUTER JOIN v$asm_disk b USING (group_number) where b.header_status = 'MEMBER'
ORDER BY a.name
/
Datafiles of a particular TableSpace
-------------------------------------
set pages 9999 lines 300
col tablespace_name for a30
col file_name for a80
select tablespace_name,file_name,bytes/1024/1024 Size_MB,autoextensible,maxbytes/1024/1024 MAXSIZE_MB
from dba_data_files where tablespace_name='&tablespace_name' order by 1,2;
ASM Disk Database Files Script
------------------------------
set pages 9999 lines 300
col full_alias_path for a70
col file_type for a15
select concat('+'||gname, sys_connect_by_path(aname, '/')) full_alias_path,
system_created, alias_directory, file_type
from ( select b.name gname, a.parent_index pindex, a.name aname,
a.reference_index rindex , a.system_created, a.alias_directory,
c.type file_type
from v$asm_alias a, v$asm_diskgroup b, v$asm_file c
where a.group_number = b.group_number
and a.group_number = c.group_number(+)
and a.file_number = c.file_number(+)
and a.file_incarnation = c.incarnation(+)
)
start with (mod(pindex, power(2, 24))) = 0
and rindex in
( select a.reference_index
from v$asm_alias a, v$asm_diskgroup b
where a.group_number = b.group_number
and (mod(a.parent_index, power(2, 24))) = 0
and a.name = '&DATABASE_NAME'
)
connect by prior rindex = pindex;
TABLESPACE DDL
--------------
set pagesize 0
SET LONG 9999999
select dbms_metadata.get_ddl('TABLESPACE','&TABLESPACE_NAME') FROM DUAL;
To resize a datafile (ASM)
--------------------------
alter database datafile '&datafile_name' resize 4096M;
If AUTOEXTEND ON
----------------
alter database datafile '&datafile_name' autoextend on maxsize 8G;
To add a new datafile in a tablespace (ASM)
-------------------------------------------
BEFORE ADDING A DATAFILE WE SHOULD CHECK FOR DUPLICATE DATAFILE For datafile
select tablespace_name,file_name from dba_data_files where file_name like '%&datafile_name%';
ALTER TABLESPACE <TABLESPACE_NAME> ADD DATAFILE '+<ASM_DISKGROUP_NAME>' SIZE 4G;
If AUTOEXTEND ON
----------------
ALTER TABLESPACE <TABLESPACE_NAME> ADD DATAFILE '+<ASM_DISKGROUP_NAME>' SIZE 1G AUTOEXTEND ON MAXSIZE 8G;
To Create a new tablespace (ASM)
---------------------------------
CREATE TABLESPACE <TABLESPACE_NAME> DATAFILE '+<ASM_DISKGROUP_NAME>' SIZE 4G;
If AUTOEXTEND ON
----------------
CREATE TABLESPACE <TABLESPACE_NAME> DATAFILE '+<ASM_DISKGROUP_NAME>' SIZE 1G AUTOEXTEND ON MAXSIZE UNLIMITED;
Schemas in a tablespace
-----------------------
set pages 9999 lines 300
col "size MB" format 999,999,999
col "Objects" format 999,999,999
select obj.owner "Owner"
, obj_cnt "Objects"
, decode(seg_size, NULL, 0, seg_size) "size MB"
from (select owner, count(*) obj_cnt from dba_objects group by owner) obj
, (select owner, ceil(sum(bytes)/1024/1024) seg_size
from dba_segments group by owner) seg
where obj.owner = seg.owner(+)
order by 3 desc ,2 desc, 1
/
All schema object details in a tablespace
-----------------------------------------
set pages 9999 lines 300
col tablespace_name format a15
col segment_name format a40
col segment_type format a20
col PARTITION_NAME format a20
col mb format 999,999,999
select owner
, tablespace_name
, segment_name
, segment_type
, PARTITION_NAME
, ceil(sum(bytes) / 1024 / 1024) "Size in MB"
from dba_segments
where tablespace_name like '&tablespace_name'
group by segment_name
order by ceil(sum(bytes) / 1024 / 1024) desc
/
license: GPL
srcversion: 6D09F6DEC4890E127C660DD
depends:
vermagic: 2.6.18-194.el5 SMP mod_unload gcc-4.1
5. Make sure the disks which are using, kernel should knows by the device in place of /dev or /etc/partitions. All the asmlib disks
should be partition before to create disk.
6. as the configuration part of ASMLib,run /etc/init.d/oracleasm configure as below,
[root@test1a ~]# /etc/init.d/oracleasm configure
Configuring the Oracle ASM library driver.
This will configure the on-boot properties of the Oracle ASM library
driver. The following questions will determine whether the driver is
loaded on boot and what permissions it will have. The current values
will be shown in brackets ('[]'). Hitting <ENTER> without typing an
answer will keep that current value. Ctrl-C will abort.
Default user to own the driver interface [oracle]:
Default group to own the driver interface [dba]:
Start Oracle ASM library driver on boot (y/n) [y]: y
Fix permissions of Oracle ASM disks on boot (y/n) [y]: y
Writing Oracle ASM library driver configuration: [ OK ]
Scanning system for ASM disks: [ OK ]
7. after oracleasm configured the status of the output should be like below,
[root@test1a ~]# /etc/init.d/oracleasm status
Checking if ASM is loaded: [ OK ]
Checking if /dev/oracleasm is mounted: [ OK ]
8. all the configured ASM disks should exitsts the default mount point location.
[root@test1a ~]# ls -l /dev/oracleasm
total 0
drwxr-xr-x 1 root root 0 Jul 18 16:52 disks
drwxrwx--- 1 oracle dba 0 Jul 18 16:52 iid
[root@test1a ~]# ls -l /dev/oracleasm/disks
total 0
brw-rw---- 1 oracle dba 120, 112 Jul 18 16:52 ARCHLOG
brw-rw---- 1 oracle dba 120, 224 Jul 18 16:52 DATA1
brw-rw---- 1 oracle dba 120, 208 Jul 18 16:52 DATA2
9. create a disk using by createdisk parameters:
#/etc/init.d/oracleasm createdisk VOL1 /dev/mapper/mapth1
10. For scanning the createdisk or ASM disks as below,
[root@test1a ~]# /etc/init.d/oracleasm scandisks
Scanning system for ASM disks: [ OK ]
11. For lists out the ASM disk which is under in ASM, use the below command,
[root@test1a ~]# /etc/init.d/oracleasm listdisks
ARCHLOG
DATA1
DATA2
DATA3
LOG1
LOG2
LOG3
LOG4
12. To query the asm disks, whether it is in VALID or notVALID state.
[root@test1a ~]# /etc/init.d/oracleasm querydisk LOG1
Disk "LOG1" is a valid ASM disk on device [120, 48]
13. To remove or delete the ASM disks, use below command,
[root@test1a ~]# /etc/init.d/oracleasm deletedisk LOG1
14. /usr/sbin/oracleasm-discover -- it will discover the asm configured disks with that associated name.
[root@test1a ~]# /usr/sbin/oracleasm-discover
15. how to identify the configured ASM disk to physical Disk name as tricky step use the blkid command.
[root@test1a ~]# /sbin/blkid
# /proc/partitions - shows the major minor numbers for the device at OS level
# asmcmd lsdsk -p
# /etc/init.d/oracleasm status
# /etc/init.d/oracleasm listdisks
# ls -l /dev/oracleasm
# ls -l /dev/oracleasm/disks
# oracleasm query disk /dev/mapper/<ASMLABEL>
# fdisk -l /dev/mapper/<ASMLABEL>
# fdisk -l /dev/sd-10 ----- OS level
Set the environment for the asm
-------------------------------
uname
cat /etc/oratab OR cat /var/opt/oracle/oratab
export ORACLE_SID=<DBNAME>
export ORACLE_HOME=<>
export PATH=$PATH:$ORACLE_HOME/bin
ps -ef| grep asm
. oraenv
+ASM
ASM Space Report
----------------
SET LINESIZE 150
SET PAGESIZE 9999
SET VERIFY off
COLUMN group_name FORMAT a20 HEAD 'Disk Group|Name'
COLUMN sector_size FORMAT 99,999 HEAD 'Sector|Size'
COLUMN block_size FORMAT 99,999 HEAD 'Block|Size'
COLUMN allocation_unit_size FORMAT 999,999,999 HEAD 'Allocation|Unit Size'
COLUMN state FORMAT a11 HEAD 'State'
COLUMN type FORMAT a6 HEAD 'Type'
COLUMN total_mb FORMAT 999,999,999 HEAD 'Total Size (MB)'
COLUMN used_mb FORMAT 999,999,999 HEAD 'Used Size (MB)'
COLUMN pct_used FORMAT 999.99 HEAD 'Pct. Used'
SELECT
distinct name group_name
, sector_size sector_size
, block_size block_size
, allocation_unit_size allocation_unit_size
, state state
, type type
, total_mb total_mb
, (total_mb - free_mb) used_mb
, ROUND((1- (free_mb / total_mb))*100, 2) pct_used
FROM gv$asm_diskgroup ORDER BY name
/
ASM Disk Space Usage Report In Detail
-------------------------------------
SET LINESIZE 150
SET PAGESIZE 9999
SET VERIFY off
COLUMN disk_group_name FORMAT a15 HEAD 'Disk Group Name'
COLUMN disk_file_path FORMAT a17 HEAD 'Path'
COLUMN disk_file_name FORMAT a20 HEAD 'File Name'
COLUMN disk_file_fail_group FORMAT a20 HEAD 'Fail Group'
COLUMN total_mb FORMAT 999,999,999 HEAD 'File Size (MB)'
COLUMN used_mb FORMAT 999,999,999 HEAD 'Used Size (MB)'
COLUMN pct_used FORMAT 999.99 HEAD 'Pct. Used'
break on report on disk_group_name skip 1
compute sum label 'Disk Group' of total_mb used_mb on disk_group_name
compute sum label 'ASM Disk Group Total Size in MB' of total_mb used_mb on report
SELECT
NVL(a.name, '[CANDIDATE]') disk_group_name
, b.path disk_file_path
, b.name disk_file_name
, b.failgroup disk_file_fail_group
, b.total_mb total_mb
, (b.total_mb - b.free_mb) used_mb
, ROUND((1- (b.free_mb / b.total_mb))*100, 2) pct_used
FROM v$asm_diskgroup a RIGHT OUTER JOIN v$asm_disk b USING (group_number) where b.header_status <> 'PROVISIONED'
ORDER BY a.name
/
OTHER ASM QUERIES
-----------------
set pages 9999 lines 900
SELECT GROUP_NUMBER, NAME, TYPE, STATE, ceil (total_mb/1024) TOTAL_GB,ceil (free_mb/1024) FREE_GB,
ceil ((required_mirror_free_mb)/1024),ceil ((usable_file_mb)/1024), SYSDATE
FROM V$ASM_DISKGROUP order by NAME;
SELECT GROUP_NUMBER, NAME, TYPE, STATE, ceil (total_mb/1024) TOTAL_GB,ceil (free_mb/1024) FREE_GB,
ceil ((required_mirror_free_mb)/1024),ceil ((usable_file_mb)/1024), SYSDATE
FROM GV$ASM_DISKGROUP order by NAME;
select GROUP_NUMBER,DISK_NUMBER,TOTAL_MB,FREE_MB,NAME,MOUNT_STATUS from v$asm_disk;
select INST_ID,GROUP_NUMBER,DISK_NUMBER,TOTAL_MB,FREE_MB,NAME,MOUNT_STATUS from gv$asm_disk;
select table_name from dict where table_name like '%ASM%';
=====================================================================================================================================
ASM TABLESPACE Scripts
=========================
When a tablespace is getting filled up, action need to be taken (Preference-wise)
1. Resize the datafile.
2. If AUTOEXTEND ON,then resize the maxsize of the datafile.
3. Add a new datafile to the tablespace.
Note:-
Check the availability of the free space on the disk at OS level.
df -h (Linux,AIX)
df -gt
oracle instance process running on the server
---------------------------------------------
ps -ef | grep pmon
Set the environment for the database
------------------------------------
uname
cat /etc/oratab (Linux/AIX)
(OR)
cat /var/opt/oracle/oratab
export ORACLE_SID=<INSTANCE_NAME>
export ORACLE_HOME=/usr/app/oracle/product/10.2.0/
export PATH=$PATH:$ORACLE_HOME/bin
Database Details
----------------
sqlplus "/as sysdba"
set pages 9999 lines 300
col OPEN_MODE for a10
col HOST_NAME for a20
select name DB_NAME,INSTANCE_NAME,HOST_NAME,DATABASE_ROLE,OPEN_MODE,version DB_VERSION,LOGINS,
to_char(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB UP TIME" from v$database,gv$instance;
ASM Tablespace Utilization Script
----------------------------------
SET LINESIZE 300
SET PAGESIZE 9999
SET VERIFY off
COLUMN group_name FORMAT a20 HEAD 'Disk Group|Name'
COLUMN sector_size FORMAT 99,999 HEAD 'Sector|Size'
COLUMN block_size FORMAT 99,999 HEAD 'Block|Size'
COLUMN allocation_unit_size FORMAT 999,999,999 HEAD 'Allocation|Unit Size'
COLUMN state FORMAT a11 HEAD 'State'
COLUMN type FORMAT a6 HEAD 'Type'
COLUMN total_mb FORMAT 999,999,999 HEAD 'Total Size (GB)'
COLUMN free_mb FORMAT 999,999,999 HEAD 'Free Size (GB)'
COLUMN used_mb FORMAT 999,999,999 HEAD 'Used Size (GB)'
COLUMN pct_used FORMAT 999.99 HEAD 'Pct. Used'
SELECT
distinct name group_name
, sector_size sector_size
, block_size block_size
, allocation_unit_size allocation_unit_size
, state state
, type type
, round(total_mb/1024) "total_gb"
, round(free_mb/1024) "free_gb"
, round((total_mb - free_mb) / 1024) "used_gb"
, round((1- (free_mb / total_mb))*100, 2) "pct_used"
from v$asm_diskgroup ORDER BY name
/
ASM Disk Space Usage Script (In Detail)
---------------------------------------
SET LINESIZE 150
SET PAGESIZE 9999
SET VERIFY off
COLUMN disk_group_name FORMAT a15 HEAD 'Disk Group Name'
COLUMN disk_file_path FORMAT a17 HEAD 'Path'
COLUMN disk_file_name FORMAT a20 HEAD 'File Name'
COLUMN disk_file_fail_group FORMAT a20 HEAD 'Fail Group'
COLUMN total_mb FORMAT 999,999,999 HEAD 'File Size (MB)'
COLUMN used_mb FORMAT 999,999,999 HEAD 'Used Size (MB)'
COLUMN pct_used FORMAT 999.99 HEAD 'Pct. Used'
break on report on disk_group_name skip 1
compute sum label 'Disk Group' of total_mb used_mb on disk_group_name
compute sum label 'ASM Disk Group Total Size in MB' of total_mb used_mb on report
SELECT
NVL(a.name, '[CANDIDATE]') disk_group_name
, b.path disk_file_path
, b.name disk_file_name
, b.failgroup disk_file_fail_group
, b.total_mb total_mb
, (b.total_mb - b.free_mb) used_mb
, ROUND((1- (b.free_mb / b.total_mb))*100, 2) pct_used
FROM v$asm_diskgroup a RIGHT OUTER JOIN v$asm_disk b USING (group_number) where b.header_status = 'MEMBER'
ORDER BY a.name
/
Datafiles of a particular TableSpace
-------------------------------------
set pages 9999 lines 300
col tablespace_name for a30
col file_name for a80
select tablespace_name,file_name,bytes/1024/1024 Size_MB,autoextensible,maxbytes/1024/1024 MAXSIZE_MB
from dba_data_files where tablespace_name='&tablespace_name' order by 1,2;
ASM Disk Database Files Script
------------------------------
set pages 9999 lines 300
col full_alias_path for a70
col file_type for a15
select concat('+'||gname, sys_connect_by_path(aname, '/')) full_alias_path,
system_created, alias_directory, file_type
from ( select b.name gname, a.parent_index pindex, a.name aname,
a.reference_index rindex , a.system_created, a.alias_directory,
c.type file_type
from v$asm_alias a, v$asm_diskgroup b, v$asm_file c
where a.group_number = b.group_number
and a.group_number = c.group_number(+)
and a.file_number = c.file_number(+)
and a.file_incarnation = c.incarnation(+)
)
start with (mod(pindex, power(2, 24))) = 0
and rindex in
( select a.reference_index
from v$asm_alias a, v$asm_diskgroup b
where a.group_number = b.group_number
and (mod(a.parent_index, power(2, 24))) = 0
and a.name = '&DATABASE_NAME'
)
connect by prior rindex = pindex;
TABLESPACE DDL
--------------
set pagesize 0
SET LONG 9999999
select dbms_metadata.get_ddl('TABLESPACE','&TABLESPACE_NAME') FROM DUAL;
To resize a datafile (ASM)
--------------------------
alter database datafile '&datafile_name' resize 4096M;
If AUTOEXTEND ON
----------------
alter database datafile '&datafile_name' autoextend on maxsize 8G;
To add a new datafile in a tablespace (ASM)
-------------------------------------------
BEFORE ADDING A DATAFILE WE SHOULD CHECK FOR DUPLICATE DATAFILE For datafile
select tablespace_name,file_name from dba_data_files where file_name like '%&datafile_name%';
ALTER TABLESPACE <TABLESPACE_NAME> ADD DATAFILE '+<ASM_DISKGROUP_NAME>' SIZE 4G;
If AUTOEXTEND ON
----------------
ALTER TABLESPACE <TABLESPACE_NAME> ADD DATAFILE '+<ASM_DISKGROUP_NAME>' SIZE 1G AUTOEXTEND ON MAXSIZE 8G;
To Create a new tablespace (ASM)
---------------------------------
CREATE TABLESPACE <TABLESPACE_NAME> DATAFILE '+<ASM_DISKGROUP_NAME>' SIZE 4G;
If AUTOEXTEND ON
----------------
CREATE TABLESPACE <TABLESPACE_NAME> DATAFILE '+<ASM_DISKGROUP_NAME>' SIZE 1G AUTOEXTEND ON MAXSIZE UNLIMITED;
Schemas in a tablespace
-----------------------
set pages 9999 lines 300
col "size MB" format 999,999,999
col "Objects" format 999,999,999
select obj.owner "Owner"
, obj_cnt "Objects"
, decode(seg_size, NULL, 0, seg_size) "size MB"
from (select owner, count(*) obj_cnt from dba_objects group by owner) obj
, (select owner, ceil(sum(bytes)/1024/1024) seg_size
from dba_segments group by owner) seg
where obj.owner = seg.owner(+)
order by 3 desc ,2 desc, 1
/
All schema object details in a tablespace
-----------------------------------------
set pages 9999 lines 300
col tablespace_name format a15
col segment_name format a40
col segment_type format a20
col PARTITION_NAME format a20
col mb format 999,999,999
select owner
, tablespace_name
, segment_name
, segment_type
, PARTITION_NAME
, ceil(sum(bytes) / 1024 / 1024) "Size in MB"
from dba_segments
where tablespace_name like '&tablespace_name'
group by segment_name
order by ceil(sum(bytes) / 1024 / 1024) desc
/
license: GPL
srcversion: 6D09F6DEC4890E127C660DD
depends:
vermagic: 2.6.18-194.el5 SMP mod_unload gcc-4.1
5. Make sure the disks which are using, kernel should knows by the device in place of /dev or /etc/partitions. All the asmlib disks
should be partition before to create disk.
6. as the configuration part of ASMLib,run /etc/init.d/oracleasm configure as below,
[root@test1a ~]# /etc/init.d/oracleasm configure
Configuring the Oracle ASM library driver.
This will configure the on-boot properties of the Oracle ASM library
driver. The following questions will determine whether the driver is
loaded on boot and what permissions it will have. The current values
will be shown in brackets ('[]'). Hitting <ENTER> without typing an
answer will keep that current value. Ctrl-C will abort.
Default user to own the driver interface [oracle]:
Default group to own the driver interface [dba]:
Start Oracle ASM library driver on boot (y/n) [y]: y
Fix permissions of Oracle ASM disks on boot (y/n) [y]: y
Writing Oracle ASM library driver configuration: [ OK ]
Scanning system for ASM disks: [ OK ]
7. after oracleasm configured the status of the output should be like below,
[root@test1a ~]# /etc/init.d/oracleasm status
Checking if ASM is loaded: [ OK ]
Checking if /dev/oracleasm is mounted: [ OK ]
8. all the configured ASM disks should exitsts the default mount point location.
[root@test1a ~]# ls -l /dev/oracleasm
total 0
drwxr-xr-x 1 root root 0 Jul 18 16:52 disks
drwxrwx--- 1 oracle dba 0 Jul 18 16:52 iid
[root@test1a ~]# ls -l /dev/oracleasm/disks
total 0
brw-rw---- 1 oracle dba 120, 112 Jul 18 16:52 ARCHLOG
brw-rw---- 1 oracle dba 120, 224 Jul 18 16:52 DATA1
brw-rw---- 1 oracle dba 120, 208 Jul 18 16:52 DATA2
9. create a disk using by createdisk parameters:
#/etc/init.d/oracleasm createdisk VOL1 /dev/mapper/mapth1
10. For scanning the createdisk or ASM disks as below,
[root@test1a ~]# /etc/init.d/oracleasm scandisks
Scanning system for ASM disks: [ OK ]
11. For lists out the ASM disk which is under in ASM, use the below command,
[root@test1a ~]# /etc/init.d/oracleasm listdisks
ARCHLOG
DATA1
DATA2
DATA3
LOG1
LOG2
LOG3
LOG4
12. To query the asm disks, whether it is in VALID or notVALID state.
[root@test1a ~]# /etc/init.d/oracleasm querydisk LOG1
Disk "LOG1" is a valid ASM disk on device [120, 48]
13. To remove or delete the ASM disks, use below command,
[root@test1a ~]# /etc/init.d/oracleasm deletedisk LOG1
14. /usr/sbin/oracleasm-discover -- it will discover the asm configured disks with that associated name.
[root@test1a ~]# /usr/sbin/oracleasm-discover
15. how to identify the configured ASM disk to physical Disk name as tricky step use the blkid command.
[root@test1a ~]# /sbin/blkid
No comments:
Post a Comment