Asm表空间管理

---

 --表空间数据文件情况

set lin 200 pagesize 1000

select d.tablespace_name,
       t.status TBS_STATUS,
       d.file_name,
       d.status FILE_STATUS,
       d.AUTOEXTENSIBLE,
       d.BYTES / 1024 / 1024 MB
  from dba_data_files d, dba_tablespaces t
 where t.tablespace_name = d.tablespace_name;



 TABLESPACE_NAME                                              TBS_STATUS
------------------------------------------------------------ ------------------
FILE_NAME
--------------------------------------------------------------------------------
FILE_STATUS        AUTOEX         MB
------------------ ------ ----------
USERS                                                        ONLINE
+DATA/orcl/datafile/users.259.864477567
AVAILABLE          YES             5

UNDOTBS1                                                     ONLINE
+DATA/orcl/datafile/undotbs1.258.864477567
AVAILABLE          YES            75

SYSAUX                                                       ONLINE
+DATA/orcl/datafile/sysaux.257.864477565
AVAILABLE          YES           790

SYSTEM                                                       ONLINE
+DATA/orcl/datafile/system.256.864477565
AVAILABLE          YES           750

UNDOTBS2                                                     ONLINE
+DATA/orcl/datafile/undotbs2.264.864477889
AVAILABLE          YES            25



--表空间使用率

select b.tablespace_name,
       round(sum(b.bytes) / 1024 / 1024, 0) sum_MB,
       round(sum(nvl(a.bytes, 0)) / 1024 / 1024, 0) free_MB,
       round((sum(b.bytes) - sum(nvl(a.bytes, 0))) / sum(b.bytes), 4) * 100 use_precent
  from (select tablespace_name, file_id, sum(bytes) bytes
          from dba_free_space
         group by tablespace_name, file_id) a,
       dba_data_files b
 where a.file_id(+) = b.file_id
   and a.tablespace_name(+) = b.tablespace_name
-- and b.tablespace_name like '%%'
 group by b.tablespace_name
 order by 4;

TABLESPACE_NAME                                                  SUM_MB    FREE_MB USE_PRECENT
------------------------------------------------------------ ---------- ---------- -----------
UNDOTBS1                                                             75         61       18.67
USERS                                                                 5          4       26.25
SYSTEM                                                              750          7       99.13
UNDOTBS2                                                             25          0        99.5
SYSAUX                                                              790          0         100



--ASM磁盘空间情况

col name for a15
col COMPATIBILITY for a10
select GROUP_NUMBER,
       NAME,
       STATE,
       TYPE,
       TOTAL_MB / 1024,
       FREE_MB / 1024,
       COMPATIBILITY,
       OFFLINE_DISKS
  from v$asm_diskgroup;


GROUP_NUMBER NAME            STATE                  TYPE         TOTAL_MB/1024 FREE_MB/1024 COMPATIBIL OFFLINE_DISKS
------------ --------------- ---------------------- ------------ ------------- ------------ ---------- -------------
           1 BACK            CONNECTED              EXTERN          1.99902344   .260742188 11.2.0.0.0             0
           2 DATA            CONNECTED              EXTERN          1.99902344            0 11.2.0.0.0             0
           3 OCR             MOUNTED                NORMAL          5.99707031   5.09277344 11.2.0.0.0             0

 


--添加和创建表空间
CREATE TABLESPACE ICD_MAILSERVICE_IND
    DATAFILE '+DATA/orcl/datafile/system.dbf' SIZE 10M REUSE AUTOEXTEND OFF;


alter tablespace users add datafile '+DATA/orcl/datafile/system.dbf' size 5M autoextend off;  



--查寻磁盘组
col path for a18
SQL> select name,path from v$asm_disk order by 2;
NAME                           PATH
------------------------------ ------------------
DG1_0000                       /dev/raw/raw1
DG1_0001                       /dev/raw/raw2
RECOVERYDEST_0000              /dev/raw/raw3
DG1_0002                       /dev/raw/raw4
DG1_0003                       /dev/raw/raw5
                               /dev/raw/raw6
                               ORCL:VOL1
                               ORCL:VOL2
                               ORCL:VOL3
                               ORCL:VOL4
                               ORCL:VOL5
                               ORCL:VOL6
--添加磁盘组
ALTER DISKGROUP DG1 ADD DISK
      '/dev/raw/raw4',
      '/dev/raw/raw5'
      REBALANCE POWER 5 WAIT;

--创建磁盘组
CREATE DISKGROUP data external REDUNDANCY DISK '/dev/raw/raw6';
CREATE DISKGROUP dgroup1 NORMAL REDUNDANCY
FAILGROUP controller1 DISK
'/devices/diska1',
'/devices/diska2',
'/devices/diska3',
'/devices/diska4'
FAILGROUP controller2 DISK
'/devices/diskb1',
'/devices/diskb2',
'/devices/diskb3'3,
'/devices/diskb4';