ORACLE 12C RAC 之03-ASM管理

1.連接ASM

[grid@xag01 ~]$ /u01/app/oracle/product/12.2.0/db_1/bin/sql / as sysasm

SQL> set sqlformat ansiconsole

SQL> select instance_name,status from v$instance;
INSTANCE_NAME  STATUS   
+ASM2          STARTED 

2. ASM磁盘空间

#操作系统中查看的方法
[grid@xag01 ~]$ asmcmd -p

ASMCMD [+] > lsdg
State    Type    Rebal  Sector  Logical_Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  NORMAL  N         512             512   4096  4194304   7065600  7058464          2355200         2351632              0             N  DATA/
MOUNTED  NORMAL  N         512             512   4096  4194304   6291456  6287376          2097152         2095112              0             N  FRA/
MOUNTED  NORMAL  N         512             512   4096  4194304    460800   392824           153600          119612              0             N  MGMT/
MOUNTED  NORMAL  N         512             512   4096  4194304     30720    29852            10240            9806              0             Y  OCR/

#通过SQL语句查看(v$asm_diskgroup)
SQL> select group_number,name,total_mb,free_mb from v$asm_diskgroup;
GROUP_NUMBER  NAME  TOTAL_MB  FREE_MB  
1             DATA  7065600   7058464  
2             FRA   6291456   6287376  
3             MGMT  460800    392824   
4             OCR   30720     29852

#通过SQL语句查看(v$asm_disk)
SQL> select name,total_mb,free_mb from v$asm_disk order by 1;
NAME       TOTAL_MB  FREE_MB  
DATA_0000  2355200   2352832  
DATA_0001  2355200   2352824  
DATA_0002  2355200   2352808  
FRA_0000   2097152   2095800  
FRA_0001   2097152   2095792  
FRA_0002   2097152   2095784  
MGMT_0000  153600    130936   
MGMT_0001  153600    130948   
MGMT_0002  153600    130940   
OCR_0000   10240     9956     
OCR_0001   10240     9948     
OCR_0002   10240     9948

#查看是否有数据库实例连接上ASM实例
SQL> select instance_name,db_name,status from v$asm_client;
INSTANCE_NAME  DB_NAME  STATUS     
+ASM2          +ASM     CONNECTED  
MYRAC12        MYRAC1   CONNECTED  
MYRAC12        MYRAC1   CONNECTED  
+ASM2          +ASM     CONNECTED  
+ASM2          +ASM     CONNECTED  
xag01.mp.com   _OCR     CONNECTED 

#记录BALANCE操作
SQL> select operation,state,power,actual,sofar from v$asm_operation;
      no rows selected

3. 表空間管理

[oracle@xag02 ~]$ sql / as sysdba
#單個數據文件
SQL> create temporary tablespace temp1 tempfile '+DATA/MYRAC1/DATAFILE/TEMP1.dbf' size 256M autoextend on next 256M maxsize unlimited extent management local;

#兩個數據文件（本次未測試，生產環境要遠大於本次預設空間）
SQL> create temporary tablespace temp2 tempfile
    '+DATA/MYRAC1/DATAFILE/TEMP2_1.dbf' size 256M reuse autoextend on next 128M  maxsize 1G,
    '+DATA/MYRAC1/DATAFILE/TEMP2_2.dbf' size 256M reuse autoextend on next 128M  maxsize 1G
    EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;


SQL> ALTER TABLESPACE temp2 TABLESPACE GROUP TEMP_GP;

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP_GP;

SQL> drop tablespace TEMP including contents and datafiles;

#創建用戶表空間
create tablespace XAG_UD datafile 
'+DATA/MYRAC1/DATAFILE/XAG_UD1.dbf' size 128M AUTOEXTEND ON NEXT 64M MAXSIZE UNLIMITED,                  
'+DATA/MYRAC1/DATAFILE/XAG_UD2.dbf' size 128M AUTOEXTEND ON NEXT 64M MAXSIZE UNLIMITED 
extent management local;

4.創建用戶

#創建用戶
create user c##xag identified by 123456 default tablespace XAG_UD temporary tablespace TEMP_GP;

#赋权限
grant dba to c##xag;

[oracle@xag01 ~]$ sql c##xag/123456

SQL> create table t2 as select sysdate as a from dual;