OceanBase数据库（Oracle模式）从零开始：架构解析、迁移实战与性能调优指南

# 1. 关闭防火墙和SELinux
systemctl stop firewalld && systemctl disable firewalld
setenforce 0

# 2. 修改内核参数（OceanBase对内存和文件句柄有较高要求）
echo 'vm.max_map_count=655360' >> /etc/sysctl.conf
echo 'fs.file-max=655360' >> /etc/sysctl.conf
sysctl -p

# 3. 创建OceanBase用户
useradd -U -d /home/admin admin
echo 'admin:password' | chpasswd

# 1. 下载并安装OBD
yum install -y yum-utils
yum-config-manager --add-repo https://mirrors.aliyun.com/oceanbase/OceanBase.repo
yum install -y ob-deploy

# 2. 创建部署目录
mkdir -p /data/obdata
chown -R admin:admin /data/obdata

# 3. 使用obd demo一键部署Oracle模式（适用于学习环境）
# 此命令会自动部署1个Observer + 1个Proxy，并创建Oracle模式的租户
obd demo --tenant_mode oracle

# 4. 查看集群状态
obd cluster list
obd cluster display demo

# 使用SQLcl连接（需提前安装）
sqlplus -L ob_user/ob_password@127.0.0.1:2883/oracle_tenant

-- 创建一张测试表
CREATE TABLE employees (
    emp_id NUMBER(10) PRIMARY KEY,
    emp_name VARCHAR2(100),
    salary NUMBER(10,2),
    hire_date DATE
);

-- 创建一个存储过程，用于插入员工并自动生成序列
CREATE OR REPLACE PROCEDURE add_employee(
    p_name IN VARCHAR2,
    p_salary IN NUMBER
) IS
    v_id NUMBER;
BEGIN
    -- 使用Oracle风格的序列
    SELECT emp_seq.NEXTVAL INTO v_id FROM DUAL;
    INSERT INTO employees(emp_id, emp_name, salary, hire_date)
    VALUES (v_id, p_name, p_salary, SYSDATE);
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;
/
-- 调用存储过程
CALL add_employee('张三', 15000);

-- 包声明
CREATE OR REPLACE PACKAGE emp_pkg IS
    FUNCTION get_annual_salary(p_emp_id NUMBER) RETURN NUMBER;
    PROCEDURE update_salary(p_emp_id NUMBER, p_new_salary NUMBER);
END emp_pkg;
/

-- 包主体
CREATE OR REPLACE PACKAGE BODY emp_pkg IS
    FUNCTION get_annual_salary(p_emp_id NUMBER) RETURN NUMBER IS
        v_salary NUMBER;
    BEGIN
        SELECT salary * 12 INTO v_salary FROM employees WHERE emp_id = p_emp_id;
        RETURN v_salary;
    END;
    PROCEDURE update_salary(p_emp_id NUMBER, p_new_salary NUMBER) IS
    BEGIN
        UPDATE employees SET salary = p_new_salary WHERE emp_id = p_emp_id;
        COMMIT;
    END;
END emp_pkg;
/
-- 调用包内函数
SELECT emp_pkg.get_annual_salary(1) FROM DUAL;

CREATE OR REPLACE PROCEDURE log_error(p_message VARCHAR2) IS
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    INSERT INTO error_log(log_time, message) VALUES (SYSDATE, p_message);
    COMMIT;  -- 自治事务独立提交，不受主事务影响
END;
/

<!-- pom.xml -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
    <version>1.2.20</version>
</dependency>
<dependency>
    <groupId>com.oceanbase</groupId>
    <artifactId>oceanbase-client</artifactId> <!-- OceanBase JDBC驱动，基于MySQL协议改造 -->
    <version>2.4.6</version>
</dependency>

# application.yml
spring:
  datasource:
    url: jdbc:oceanbase://127.0.0.1:2883/oracle_tenant?useUnicode=true&characterEncoding=UTF-8
    username: ob_user
    password: ob_password
    driver-class-name: com.oceanbase.jdbc.Driver
    druid:
      initial-size: 10
      max-active: 100
      max-wait: 60000
      validation-query: SELECT 1 FROM DUAL  # Oracle模式使用DUAL

@TableName("employees")
public class Employee {
    @TableId(type = IdType.INPUT)  // 手动赋值
    private Long empId;
    private String empName;
    private BigDecimal salary;
    private Date hireDate;
}

// Mapper中自定义插入，使用序列
@Insert("INSERT INTO employees(emp_id, emp_name, salary, hire_date) VALUES(emp_seq.NEXTVAL, #{empName}, #{salary}, SYSDATE)")
@Options(useGeneratedKeys = true, keyProperty = "empId")
int insertWithSequence(Employee employee);

EXPLAIN SELECT * FROM orders WHERE order_date >= DATE '2026-01-01';

-- 调整租户内存大小为20GB
ALTER SYSTEM SET memory_limit = '20G';