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 Diskgroup Utilization Script
--------------------------------
SET ECHO OFF
SET FEEDBACK 6
SET HEADING ON
SET LINESIZE 180
SET PAGESIZE 500
SET TERMOUT ON
SET TIMING OFF
SET TRIMOUT ON
SET TRIMSPOOL ON
SET VERIFY OFF
CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES
COLUMN disk_group_name FORMAT a12 HEAD 'Disk Group Name'
COLUMN disk_file_path FORMAT a45 HEAD 'Path'
COLUMN disk_file_name FORMAT a12 HEAD 'File Name'
COLUMN disk_file_fail_group FORMAT a12 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 "" OF total_mb used_mb ON disk_group_name
COMPUTE sum LABEL "Grand Total: " 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 ,v$asm_disk b
where a.group_number (+) =b.group_number
ORDER BY a.name
/
To find the free space in an ASM disk:
--------------------------------------
set pages 9999 lines 900
select group_number, disk_number, name, failgroup, create_date, path, total_mb,free_mb from v$asm_disk;
To find the free space in an ASM diskgroup:
-------------------------------------------
set pages 9999 lines 900
select name, group_number, name, type, state, total_mb, free_mb from v$asm_diskgroup;
To see the current ASM operations in Progress:
----------------------------------------------
set pages 9999 lines 900
select group_number, operation, state, power, actual, sofar, est_work, est_rate, est_minutes from v$asm_operation;
ASM Diskgroup Information
-----------------------------
set pages 9999 lines 900
set head off
select 'Diskgroup Information' from dual;
set head on
column name format a15
column DG# format 99
select group_number DG#, name, state, type, total_mb, free_mb, round(free_mb/total_mb*100,2) pct_free from
v$asm_diskgroup;
Monitor space used in ASM Disk Groups
-------------------------------------
SET LINESIZE 145
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'
break on report on disk_group_name skip 1
compute sum label "Grand Total: " of total_mb used_mb on report
SELECT
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
v$asm_diskgroup
ORDER BY
name
/
Disk Group Name File Name File Size (MB) Used Size (MB) Free Size (MB) Pct. Used
-------------------- -------------------- -------------------- -------------- -------------- -------
Space used by database files.No other files found for cleanup.
There are 2 undo tablespaces for this database. Reclaimed some freespace from non-default undo tablespace
(i.e:"UNDOTBS1".)
Current status:
---------------
ORADATA diskgroup having xxxgb of freespace out of yyyygb.
http://dbasanthosh.wordpress.com/2012/05/
-----------------------------------------
SET LINESIZE 145
SET PAGESIZE 9999
SET VERIFY off
COLUMN disk_group_name FORMAT a20 HEAD 'Disk Group Name'
COLUMN disk_file_path FORMAT a20 HEAD 'Path'
COLUMN disk_file_name FORMAT a20 HEAD 'File Name'
COLUMN total_mb FORMAT 999,999,999 HEAD 'File Size (MB)'
COLUMN used_mb FORMAT 999,999,999 HEAD 'Used Size (MB)'
COLUMN free_mb FORMAT 999,999,999 HEAD 'Free Size (MB)'
COLUMN pct_used FORMAT 999.99 HEAD 'Pct. Used'
break on report on disk_group_name skip 1
compute sum label “” of total_mb used_mb free_mb on disk_group_name
compute sum label “Grand Total: ” of total_mb used_mb free_mb on report
SELECT NVL(a.name, '[CANDIDATE]') disk_group_name,b.path disk_file_path,b.name disk_file_name,
b.total_mb total_mb,(b.total_mb – b.free_mb) used_mb,b.free_mb free_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) ORDER BY a.nameUSING (group_number) ORDER BY a.name
V$ASM_DISK Header_Status:
------------------------
UNKNOWN - Automatic Storage Management disk header has not been read
CANDIDATE - Disk is not part of a disk group and may be added to a disk group with the ALTER DISKGROUP statement
INCOMPATIBLE - Version number in the disk header is not compatible with the Automatic Storage Management software version.
PROVISIONED - Disk is not part of a disk group and may be added to a disk group with the ALTER DISKGROUP
statement. The PROVISIONED header status is different from the CANDIDATE header status in that PROVISIONED implies that an additional platform-specific action has been taken by an administrator to make the disk available for Automatic Storage Management.
MEMBER - Disk is a member of an existing disk group. No attempt should be made to add the disk to a different disk
group. The ALTER DISKGROUP statement will reject such an addition unless overridden with the FORCE option
FORMER - Disk was once part of a disk group but has been dropped cleanly from the group. It may be added to a new disk group with the ALTER DISKGROUP statement.
CONFLICT - Automatic Storage Management disk was not mounted due to a conflict
FOREIGN - Disk contains data created by an Oracle product other than ASM. This includes datafiles, logfiles, and OCR disks.
---------------------------------------------
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 Diskgroup Utilization Script
--------------------------------
SET ECHO OFF
SET FEEDBACK 6
SET HEADING ON
SET LINESIZE 180
SET PAGESIZE 500
SET TERMOUT ON
SET TIMING OFF
SET TRIMOUT ON
SET TRIMSPOOL ON
SET VERIFY OFF
CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES
COLUMN disk_group_name FORMAT a12 HEAD 'Disk Group Name'
COLUMN disk_file_path FORMAT a45 HEAD 'Path'
COLUMN disk_file_name FORMAT a12 HEAD 'File Name'
COLUMN disk_file_fail_group FORMAT a12 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 "" OF total_mb used_mb ON disk_group_name
COMPUTE sum LABEL "Grand Total: " 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 ,v$asm_disk b
where a.group_number (+) =b.group_number
ORDER BY a.name
/
To find the free space in an ASM disk:
--------------------------------------
set pages 9999 lines 900
select group_number, disk_number, name, failgroup, create_date, path, total_mb,free_mb from v$asm_disk;
To find the free space in an ASM diskgroup:
-------------------------------------------
set pages 9999 lines 900
select name, group_number, name, type, state, total_mb, free_mb from v$asm_diskgroup;
To see the current ASM operations in Progress:
----------------------------------------------
set pages 9999 lines 900
select group_number, operation, state, power, actual, sofar, est_work, est_rate, est_minutes from v$asm_operation;
ASM Diskgroup Information
-----------------------------
set pages 9999 lines 900
set head off
select 'Diskgroup Information' from dual;
set head on
column name format a15
column DG# format 99
select group_number DG#, name, state, type, total_mb, free_mb, round(free_mb/total_mb*100,2) pct_free from
v$asm_diskgroup;
Monitor space used in ASM Disk Groups
-------------------------------------
SET LINESIZE 145
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'
break on report on disk_group_name skip 1
compute sum label "Grand Total: " of total_mb used_mb on report
SELECT
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
v$asm_diskgroup
ORDER BY
name
/
Disk Group Name File Name File Size (MB) Used Size (MB) Free Size (MB) Pct. Used
-------------------- -------------------- -------------------- -------------- -------------- -------
Space used by database files.No other files found for cleanup.
There are 2 undo tablespaces for this database. Reclaimed some freespace from non-default undo tablespace
(i.e:"UNDOTBS1".)
Current status:
---------------
ORADATA diskgroup having xxxgb of freespace out of yyyygb.
http://dbasanthosh.wordpress.com/2012/05/
-----------------------------------------
SET LINESIZE 145
SET PAGESIZE 9999
SET VERIFY off
COLUMN disk_group_name FORMAT a20 HEAD 'Disk Group Name'
COLUMN disk_file_path FORMAT a20 HEAD 'Path'
COLUMN disk_file_name FORMAT a20 HEAD 'File Name'
COLUMN total_mb FORMAT 999,999,999 HEAD 'File Size (MB)'
COLUMN used_mb FORMAT 999,999,999 HEAD 'Used Size (MB)'
COLUMN free_mb FORMAT 999,999,999 HEAD 'Free Size (MB)'
COLUMN pct_used FORMAT 999.99 HEAD 'Pct. Used'
break on report on disk_group_name skip 1
compute sum label “” of total_mb used_mb free_mb on disk_group_name
compute sum label “Grand Total: ” of total_mb used_mb free_mb on report
SELECT NVL(a.name, '[CANDIDATE]') disk_group_name,b.path disk_file_path,b.name disk_file_name,
b.total_mb total_mb,(b.total_mb – b.free_mb) used_mb,b.free_mb free_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) ORDER BY a.nameUSING (group_number) ORDER BY a.name
V$ASM_DISK Header_Status:
------------------------
UNKNOWN - Automatic Storage Management disk header has not been read
CANDIDATE - Disk is not part of a disk group and may be added to a disk group with the ALTER DISKGROUP statement
INCOMPATIBLE - Version number in the disk header is not compatible with the Automatic Storage Management software version.
PROVISIONED - Disk is not part of a disk group and may be added to a disk group with the ALTER DISKGROUP
statement. The PROVISIONED header status is different from the CANDIDATE header status in that PROVISIONED implies that an additional platform-specific action has been taken by an administrator to make the disk available for Automatic Storage Management.
MEMBER - Disk is a member of an existing disk group. No attempt should be made to add the disk to a different disk
group. The ALTER DISKGROUP statement will reject such an addition unless overridden with the FORCE option
FORMER - Disk was once part of a disk group but has been dropped cleanly from the group. It may be added to a new disk group with the ALTER DISKGROUP statement.
CONFLICT - Automatic Storage Management disk was not mounted due to a conflict
FOREIGN - Disk contains data created by an Oracle product other than ASM. This includes datafiles, logfiles, and OCR disks.
No comments:
Post a Comment