[grid@sbdbdr01 ~]$ sqlplus "/as sysasm"
SQL*Plus: Release 11.2.0.4.0 Production on Sun Oct 23 12:47:18 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Automatic Storage Management option
SQL> SET ECHO OFF
SET FEEDBACK 6
SET HEADING ON
SET LINESIZE 180
SET PAGESIZE 50000
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 a25 HEAD 'Disk Group Name'
COLUMN disk_file_path FORMAT a30 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 "" 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.name disk_file_name
, b.failgroup disk_file_fail_group
, b.total_mb total_mb
, (b.total_mb - b.free_mb) used_mb
FROM
v$asm_diskgroup a RIGHT OUTER JOIN v$asm_disk b USING (group_number)
ORDER BY
a.name;
/SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> columns cleared
SQL> breaks cleared
SQL> computes cleared
SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> 2 3 4 5 6 7 8 9 10
Disk Group Name File Name Fail Group File Size (MB) Used Size (MB)
------------------------- -------------------- -------------------- -------------- --------------
DATA DB_ORCL_DATA_ASM_05 DB_ORCL_DATA_ASM_05 49,151 23,281
DB_ORCL_DATA_ASM_04 DB_ORCL_DATA_ASM_04 49,151 23,280
DB_ORCL_DATA_ASM_03 DB_ORCL_DATA_ASM_03 49,151 23,283
DB_ORCL_DATA_ASM_02 DB_ORCL_DATA_ASM_02 49,151 23,284
DB_ORCL_DATA_ASM_01 DB_ORCL_DATA_ASM_01 49,151 23,282
************************* -------------- --------------
245,755 116,410
FRA DB_ORCL_FRA_ASM_04 DB_ORCL_FRA_ASM_04 23,551 7,071
DB_ORCL_FRA_ASM_03 DB_ORCL_FRA_ASM_03 23,551 7,073
DB_ORCL_FRA_ASM_02 DB_ORCL_FRA_ASM_02 23,551 7,074
DB_ORCL_FRA_ASM_01 DB_ORCL_FRA_ASM_01 23,551 7,077
************************* -------------- --------------
94,204 28,295
RMAN_BKP DB_ORCL_FRA_ASM_05 DB_ORCL_FRA_ASM_05 23,551 59
************************* -------------- --------------
23,551 59
[CANDIDATE] 0 0
************************* -------------- --------------
0 0
-------------- --------------
Grand Total: 363,510 144,764
11 rows selected.
SQL> select * from v$asm_operation ;
no rows selected
SQL> ALTER DISKGROUP DATA DROP DISK DB_ORCL_DATA_ASM_05;
Diskgroup altered.
SQL> select * from v$asm_operation ;
GROUP_NUMBER OPERA STAT POWER ACTUAL SOFAR EST_WORK EST_RATE EST_MINUTES ERROR_CODE
------------ ----- ---- ---------- ---------- ---------- ---------- ---------- ----------- --------------------------------------------
1 REBAL RUN 1 1 340 33291 5431 6
SQL>
SQL*Plus: Release 11.2.0.4.0 Production on Sun Oct 23 12:47:18 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Automatic Storage Management option
SQL> SET ECHO OFF
SET FEEDBACK 6
SET HEADING ON
SET LINESIZE 180
SET PAGESIZE 50000
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 a25 HEAD 'Disk Group Name'
COLUMN disk_file_path FORMAT a30 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 "" 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.name disk_file_name
, b.failgroup disk_file_fail_group
, b.total_mb total_mb
, (b.total_mb - b.free_mb) used_mb
FROM
v$asm_diskgroup a RIGHT OUTER JOIN v$asm_disk b USING (group_number)
ORDER BY
a.name;
/SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> columns cleared
SQL> breaks cleared
SQL> computes cleared
SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> 2 3 4 5 6 7 8 9 10
Disk Group Name File Name Fail Group File Size (MB) Used Size (MB)
------------------------- -------------------- -------------------- -------------- --------------
DATA DB_ORCL_DATA_ASM_05 DB_ORCL_DATA_ASM_05 49,151 23,281
DB_ORCL_DATA_ASM_04 DB_ORCL_DATA_ASM_04 49,151 23,280
DB_ORCL_DATA_ASM_03 DB_ORCL_DATA_ASM_03 49,151 23,283
DB_ORCL_DATA_ASM_02 DB_ORCL_DATA_ASM_02 49,151 23,284
DB_ORCL_DATA_ASM_01 DB_ORCL_DATA_ASM_01 49,151 23,282
************************* -------------- --------------
245,755 116,410
FRA DB_ORCL_FRA_ASM_04 DB_ORCL_FRA_ASM_04 23,551 7,071
DB_ORCL_FRA_ASM_03 DB_ORCL_FRA_ASM_03 23,551 7,073
DB_ORCL_FRA_ASM_02 DB_ORCL_FRA_ASM_02 23,551 7,074
DB_ORCL_FRA_ASM_01 DB_ORCL_FRA_ASM_01 23,551 7,077
************************* -------------- --------------
94,204 28,295
RMAN_BKP DB_ORCL_FRA_ASM_05 DB_ORCL_FRA_ASM_05 23,551 59
************************* -------------- --------------
23,551 59
[CANDIDATE] 0 0
************************* -------------- --------------
0 0
-------------- --------------
Grand Total: 363,510 144,764
11 rows selected.
SQL> select * from v$asm_operation ;
no rows selected
SQL> ALTER DISKGROUP DATA DROP DISK DB_ORCL_DATA_ASM_05;
Diskgroup altered.
SQL> select * from v$asm_operation ;
GROUP_NUMBER OPERA STAT POWER ACTUAL SOFAR EST_WORK EST_RATE EST_MINUTES ERROR_CODE
------------ ----- ---- ---------- ---------- ---------- ---------- ---------- ----------- --------------------------------------------
1 REBAL RUN 1 1 340 33291 5431 6
SQL>
No comments:
Post a Comment