Oracle Asm: differenze tra le versioni
Jump to navigation
Jump to search
Nessun oggetto della modifica |
|||
(14 versioni intermedie di uno stesso utente non sono mostrate) | |||
Riga 6: | Riga 6: | ||
http://www.oracle.com/technetwork/server-storage/linux/asmlib/ol6-1709075.html |
http://www.oracle.com/technetwork/server-storage/linux/asmlib/ol6-1709075.html |
||
==report utilizzo== |
|||
select GROUP_NUMBER,TYPE,sum(bytes)/(1024*1024) MB from v$asm_file group by GROUP_NUMBER,TYPE order by 1,2; |
|||
set pagesize 200 |
|||
set linesize 200 |
|||
set feedback off |
|||
col PATH for a30 |
|||
col GROUPNAME for a10 |
|||
col NAME for a15 |
|||
col FAILGROUP_TYPE for a14 |
|||
col FAILGROUP for a15 |
|||
col REDUNDANCY for a10 |
|||
select g.NAME GROUPNAME |
|||
,d.FAILGROUP |
|||
,d.NAME NAME |
|||
,d.PATH PATH |
|||
,d.STATE STATE |
|||
,d.TOTAL_MB TOTAL_MB |
|||
,d.FREE_MB |
|||
,d.FREE_MB-(avg(d.FREE_MB) over (partition by g.NAME)) SCO |
|||
,d.SECTOR_SIZE |
|||
,d.READS |
|||
,d.WRITES |
|||
from v$asm_disk d, v$asm_diskgroup g |
|||
where d.GROUP_NUMBER=g.GROUP_NUMBER(+) order by 1,2; |
|||
select GROUP_NUMBER,NAME,STATE,TYPE,TOTAL_MB,FREE_MB, |
|||
round(((TOTAL_MB-FREE_MB)/nullif(TOTAL_MB,0))*100,2) "USED%", |
|||
ALLOCATION_UNIT_SIZE,OFFLINE_DISKS |
|||
from v$asm_diskgroup order by 1; |
|||
==ricerca file orfani== |
|||
<pre> |
|||
DEFINE ASMGROUP="DGDATA" |
|||
set linesize 200 |
|||
set pagesize 50000 |
|||
col reference_index noprint |
|||
col type format a15 |
|||
col files format a80 |
|||
WITH v_asmgroup AS (SELECT group_number FROM v$asm_diskgroup WHERE name='&ASMGROUP'), |
|||
v_parentindex AS (SELECT parent_index |
|||
FROM v$asm_alias |
|||
WHERE group_number = (SELECT group_number FROM v_asmgroup) |
|||
AND alias_index=0), |
|||
v_asmfiles AS (SELECT file_number, type |
|||
FROM v$asm_file |
|||
WHERE group_number = (SELECT group_number FROM v_asmgroup)), |
|||
v_dbname AS (SELECT '/'||upper(db_unique_name)||'/' dbname from v$database) |
|||
SELECT 'rm '|| files files FROM -- this line show the delete command |
|||
( |
|||
SELECT '+&ASMGROUP'||files files, type |
|||
FROM (SELECT upper(sys_connect_by_path(aa.name,'/')) files, aa.reference_index, b.type |
|||
FROM (SELECT file_number,alias_directory,name, reference_index, parent_index |
|||
FROM v$asm_alias) aa, |
|||
(SELECT parent_index FROM v_parentindex) a, |
|||
(SELECT file_number, type FROM v_asmfiles) b |
|||
WHERE aa.file_number=b.file_number(+) |
|||
AND aa.alias_directory='N' |
|||
-- missing PARAMETERFILE, DATAGUARDCONFIG |
|||
AND b.type in ('DATAFILE','ONLINELOG','CONTROLFILE','TEMPFILE') |
|||
START WITH aa.PARENT_INDEX=a.parent_index |
|||
CONNECT BY PRIOR aa.reference_index=aa.parent_index) |
|||
WHERE substr(files,instr(files,'/',1,1),instr(files,'/',1,2)-instr(files,'/',1,1)+1) = (select dbname FROM v_dbname) |
|||
MINUS ( |
|||
SELECT upper(name) files, 'DATAFILE' type FROM v$datafile |
|||
UNION ALL |
|||
SELECT upper(name) files, 'TEMPFILE' type FROM v$tempfile |
|||
UNION ALL |
|||
SELECT upper(name) files, 'CONTROLFILE' type FROM v$controlfile WHERE name like '+&ASMGROUP%' |
|||
UNION ALL |
|||
SELECT upper(member) files, 'ONLINELOG' type FROM v$logfile WHERE member like '+&ASMGROUP%' |
|||
) |
|||
); |
|||
</pre> |
|||
In caso di database in stato "mounted": |
|||
<pre> |
|||
DEFINE ASMGROUP="DGDATA" |
|||
set linesize 200 |
|||
set pagesize 50000 |
|||
col reference_index noprint |
|||
col type format a15 |
|||
col files format a80 |
|||
SELECT 'rm '|| files files FROM -- this line show the delete command |
|||
( |
|||
SELECT '+&ASMGROUP'||files files, type |
|||
FROM (SELECT upper(sys_connect_by_path(aa.name,'/')) files, aa.reference_index, b.type |
|||
FROM (SELECT file_number,alias_directory,name, reference_index, parent_index |
|||
FROM v$asm_alias) aa, |
|||
(SELECT parent_index FROM (SELECT parent_index |
|||
FROM v$asm_alias |
|||
WHERE group_number = (SELECT group_number FROM v$asm_diskgroup WHERE name='&ASMGROUP') |
|||
AND alias_index=0)) a, |
|||
(SELECT file_number, type FROM (SELECT file_number, type |
|||
FROM v$asm_file |
|||
WHERE group_number = (SELECT group_number FROM v$asm_diskgroup WHERE name='&ASMGROUP'))) b |
|||
WHERE aa.file_number=b.file_number(+) |
|||
AND aa.alias_directory='N' |
|||
-- missing PARAMETERFILE, DATAGUARDCONFIG |
|||
AND b.type in ('DATAFILE','ONLINELOG','CONTROLFILE','TEMPFILE') |
|||
START WITH aa.PARENT_INDEX=a.parent_index |
|||
CONNECT BY PRIOR aa.reference_index=aa.parent_index) |
|||
WHERE substr(files,instr(files,'/',1,1),instr(files,'/',1,2)-instr(files,'/',1,1)+1) = (select dbname FROM (SELECT '/'||upper(db_unique_name)||'/' dbname from v$database)) |
|||
MINUS ( |
|||
SELECT upper(name) files, 'DATAFILE' type FROM v$datafile |
|||
UNION ALL |
|||
SELECT upper(name) files, 'TEMPFILE' type FROM v$tempfile |
|||
UNION ALL |
|||
SELECT upper(name) files, 'CONTROLFILE' type FROM v$controlfile WHERE name like '+&ASMGROUP%' |
|||
UNION ALL |
|||
SELECT upper(member) files, 'ONLINELOG' type FROM v$logfile WHERE member like '+&ASMGROUP%' |
|||
) |
|||
); |
|||
</pre> |
|||
http://anjo.pt/wp/keyword-oracle/2013/02/26/find-orphan-asm-files/ |
|||
=ASM Senza oracleasm= |
|||
* udev per permission a grid:oracleasm |
|||
cat /etc/udev/rules.d/99-oracle-asmdevices.rules |
|||
#xen |
|||
KERNEL=="xvd?", SUBSYSTEM=="block", ENV{ID_FS_TYPE}=="oracleasm", OWNER="grid", GROUP="asmadmin", MODE="0660" |
|||
#scsi |
|||
KERNEL=="sd?", SUBSYSTEM=="block", ENV{ID_FS_TYPE}=="oracleasm", OWNER="grid", GROUP="asmadmin", MODE="0660" |
|||
* udev per /dev/asm |
|||
[[Categoria:Database]] |
[[Categoria:Database]] |
||
[[Categoria:Oracle]] |
Versione attuale delle 10:17, 17 set 2020
http://www.oracle.com/technetwork/server-storage/linux/asmlib/rhel6-1940776.html
http://www.oracle.com/technetwork/server-storage/linux/asmlib/ol6-1709075.html
report utilizzo
select GROUP_NUMBER,TYPE,sum(bytes)/(1024*1024) MB from v$asm_file group by GROUP_NUMBER,TYPE order by 1,2;
set pagesize 200 set linesize 200 set feedback off col PATH for a30 col GROUPNAME for a10 col NAME for a15 col FAILGROUP_TYPE for a14 col FAILGROUP for a15 col REDUNDANCY for a10 select g.NAME GROUPNAME ,d.FAILGROUP ,d.NAME NAME ,d.PATH PATH ,d.STATE STATE ,d.TOTAL_MB TOTAL_MB ,d.FREE_MB ,d.FREE_MB-(avg(d.FREE_MB) over (partition by g.NAME)) SCO ,d.SECTOR_SIZE ,d.READS ,d.WRITES from v$asm_disk d, v$asm_diskgroup g where d.GROUP_NUMBER=g.GROUP_NUMBER(+) order by 1,2;
select GROUP_NUMBER,NAME,STATE,TYPE,TOTAL_MB,FREE_MB, round(((TOTAL_MB-FREE_MB)/nullif(TOTAL_MB,0))*100,2) "USED%", ALLOCATION_UNIT_SIZE,OFFLINE_DISKS from v$asm_diskgroup order by 1;
ricerca file orfani
DEFINE ASMGROUP="DGDATA" set linesize 200 set pagesize 50000 col reference_index noprint col type format a15 col files format a80 WITH v_asmgroup AS (SELECT group_number FROM v$asm_diskgroup WHERE name='&ASMGROUP'), v_parentindex AS (SELECT parent_index FROM v$asm_alias WHERE group_number = (SELECT group_number FROM v_asmgroup) AND alias_index=0), v_asmfiles AS (SELECT file_number, type FROM v$asm_file WHERE group_number = (SELECT group_number FROM v_asmgroup)), v_dbname AS (SELECT '/'||upper(db_unique_name)||'/' dbname from v$database) SELECT 'rm '|| files files FROM -- this line show the delete command ( SELECT '+&ASMGROUP'||files files, type FROM (SELECT upper(sys_connect_by_path(aa.name,'/')) files, aa.reference_index, b.type FROM (SELECT file_number,alias_directory,name, reference_index, parent_index FROM v$asm_alias) aa, (SELECT parent_index FROM v_parentindex) a, (SELECT file_number, type FROM v_asmfiles) b WHERE aa.file_number=b.file_number(+) AND aa.alias_directory='N' -- missing PARAMETERFILE, DATAGUARDCONFIG AND b.type in ('DATAFILE','ONLINELOG','CONTROLFILE','TEMPFILE') START WITH aa.PARENT_INDEX=a.parent_index CONNECT BY PRIOR aa.reference_index=aa.parent_index) WHERE substr(files,instr(files,'/',1,1),instr(files,'/',1,2)-instr(files,'/',1,1)+1) = (select dbname FROM v_dbname) MINUS ( SELECT upper(name) files, 'DATAFILE' type FROM v$datafile UNION ALL SELECT upper(name) files, 'TEMPFILE' type FROM v$tempfile UNION ALL SELECT upper(name) files, 'CONTROLFILE' type FROM v$controlfile WHERE name like '+&ASMGROUP%' UNION ALL SELECT upper(member) files, 'ONLINELOG' type FROM v$logfile WHERE member like '+&ASMGROUP%' ) );
In caso di database in stato "mounted":
DEFINE ASMGROUP="DGDATA" set linesize 200 set pagesize 50000 col reference_index noprint col type format a15 col files format a80 SELECT 'rm '|| files files FROM -- this line show the delete command ( SELECT '+&ASMGROUP'||files files, type FROM (SELECT upper(sys_connect_by_path(aa.name,'/')) files, aa.reference_index, b.type FROM (SELECT file_number,alias_directory,name, reference_index, parent_index FROM v$asm_alias) aa, (SELECT parent_index FROM (SELECT parent_index FROM v$asm_alias WHERE group_number = (SELECT group_number FROM v$asm_diskgroup WHERE name='&ASMGROUP') AND alias_index=0)) a, (SELECT file_number, type FROM (SELECT file_number, type FROM v$asm_file WHERE group_number = (SELECT group_number FROM v$asm_diskgroup WHERE name='&ASMGROUP'))) b WHERE aa.file_number=b.file_number(+) AND aa.alias_directory='N' -- missing PARAMETERFILE, DATAGUARDCONFIG AND b.type in ('DATAFILE','ONLINELOG','CONTROLFILE','TEMPFILE') START WITH aa.PARENT_INDEX=a.parent_index CONNECT BY PRIOR aa.reference_index=aa.parent_index) WHERE substr(files,instr(files,'/',1,1),instr(files,'/',1,2)-instr(files,'/',1,1)+1) = (select dbname FROM (SELECT '/'||upper(db_unique_name)||'/' dbname from v$database)) MINUS ( SELECT upper(name) files, 'DATAFILE' type FROM v$datafile UNION ALL SELECT upper(name) files, 'TEMPFILE' type FROM v$tempfile UNION ALL SELECT upper(name) files, 'CONTROLFILE' type FROM v$controlfile WHERE name like '+&ASMGROUP%' UNION ALL SELECT upper(member) files, 'ONLINELOG' type FROM v$logfile WHERE member like '+&ASMGROUP%' ) );
http://anjo.pt/wp/keyword-oracle/2013/02/26/find-orphan-asm-files/
ASM Senza oracleasm
- udev per permission a grid:oracleasm
cat /etc/udev/rules.d/99-oracle-asmdevices.rules #xen KERNEL=="xvd?", SUBSYSTEM=="block", ENV{ID_FS_TYPE}=="oracleasm", OWNER="grid", GROUP="asmadmin", MODE="0660" #scsi KERNEL=="sd?", SUBSYSTEM=="block", ENV{ID_FS_TYPE}=="oracleasm", OWNER="grid", GROUP="asmadmin", MODE="0660"
- udev per /dev/asm