OceanBase常用命令

数据运维 2024-05-07 竹子爱熊猫 手机阅读

OceanBase常用命令
整理一些OB中常用的查询语句，解决一些OB常用的运维操作，后期不断补充吧。

租户类

  • OB支持多租户，默认是sys租户，通常我们都需要自己创建一个租户供业务使用，创建一个完成租户顺序是 unit->resource pool->tenant ，当然在最开始创建租户时，可能会遇到报错就是资源不足的问题，所以首先要确认下资源可用情况:
SELECT a.zone,concat(a.svr_ip,':',a.svr_port) observer, cpu_total, cpu_assigned, (cpu_total-cpu_assigned) cpu_free,

mem_total/1024/1024/1024 mem_total_gb,

mem_assigned/1024/1024/1024 mem_assign_gb,

(mem_total-mem_assigned)/1024/1024/1024 mem_free_gb

FROM __all_virtual_server_stat a

JOIN __all_server b ON (a.svr_ip=b.svr_ip

AND a.svr_port=b.svr_port)

ORDER BY a.zone,

a.svr_ip ;

// 4.0之后:

SELECT SVR_IP ,

SVR_PORT ,

ZONE ,

SQL_PORT ,

CPU_CAPACITY ,

CPU_CAPACITY_MAX ,

CPU_ASSIGNED ,

CPU_ASSIGNED_MAX ,

MEM_CAPACITY/1024/1024/1024 as MEM_CAPACITY_GB ,

MEM_ASSIGNED/1024/1024/1024 as MEM_ASSIGNED_GB,

LOG_DISK_CAPACITY/1024/1024/1024 as LOG_DISK_CAPACITY_GB ,

LOG_DISK_ASSIGNED/1024/1024/1024 as LOG_DISK_ASSIGNED_GB ,

LOG_DISK_IN_USE/1024/1024/1024 as LOG_DISK_IN_USE_GB ,

DATA_DISK_CAPACITY/1024/1024/1024 as DATA_DISK_CAPACITY_GB ,

DATA_DISK_IN_USE/1024/1024/1024 as DATA_DISK_IN_USE_GB,

DATA_DISK_HEALTH_STATUS ,

MEMORY_LIMIT/1024/1024/1024 as MEMORY_LIMIT_GB

FROM GV$OB_SERVERS;

//结果如下面看到 cpu、mem、disk 可使用最大资源和已使用情况，后面创建租户时就知道最大能使用的资源了。:

+---------------+----------+-------+----------+--------------+------------------+--------------+------------------+-----------------+-----------------+----------------------+----------------------+--------------------+-----------------------+---------------------+-------------------------+-----------------+

| SVR_IP | SVR_PORT | ZONE | SQL_PORT | CPU_CAPACITY | CPU_CAPACITY_MAX | CPU_ASSIGNED | CPU_ASSIGNED_MAX | MEM_CAPACITY_GB | MEM_ASSIGNED_GB | LOG_DISK_CAPACITY_GB | LOG_DISK_ASSIGNED_GB | LOG_DISK_IN_USE_GB | DATA_DISK_CAPACITY_GB | DATA_DISK_IN_USE_GB | DATA_DISK_HEALTH_STATUS | MEMORY_LIMIT_GB |

+---------------+----------+-------+----------+--------------+------------------+--------------+------------------+-----------------+-----------------+----------------------+----------------------+--------------------+-----------------------+---------------------+-------------------------+-----------------+

| 10.140.118.7 | 2882 | zone3 | 2881 | 16 | 16 | 1 | 1 | 8.000000000000 | 2.000000000000 | 30.000000000000 | 2.000000000000 | 1.625000000000 | 60.000000000000 | 1.257812500000 | NORMAL | 10.000000000000 |

| 10.140.114.12 | 2882 | zone1 | 2881 | 16 | 16 | 1 | 1 | 8.000000000000 | 2.000000000000 | 30.000000000000 | 2.000000000000 | 1.625000000000 | 19.990234375000 | 1.271484375000 | NORMAL | 10.000000000000 |

| 10.140.60.14 | 2882 | zone2 | 2881 | 16 | 16 | 1 | 1 | 8.000000000000 | 2.000000000000 | 30.000000000000 | 2.000000000000 | 1.625000000000 | 60.000000000000 | 1.257812500000 | NORMAL | 10.000000000000 |

+---------------+----------+-------+----------+--------------+------------------+--------------+------------------+-----------------+-----------------+----------------------+----------------------+--------------------+-----------------------+---------------------+-------------------------+-----------------+

3 rows in set (0.005 sec)

```