Oracle 11g 数据库配置说明

🔧 配置已调整为Oracle 11g

当前数据库配置

"Database": {
  "Enabled": true,
  "Type": "Oracle",
  "ConnectionString": "Data Source=localhost:1521/XE;User Id=PRODUCTION_MES;Password=your_password;Pooling=true;Connection Timeout=60;",
  "AutoCreateTables": true,
  "BatchSize": 100,
  "DataRetentionDays": 90,
  "Tables": {
    "ModbusDataTable": "PRODUCTION_DATA",
    "CommunicationLogTable": "COMMUNICATION_LOG", 
    "ErrorLogTable": "ERROR_LOG",
    "StatisticsTable": "STATISTICS"
  }
}

📋 Oracle连接字符串配置

1. 基本连接格式

Data Source=服务器地址:端口/服务名;User Id=用户名;Password=密码;Pooling=true;Connection Timeout=60;

2. 常见配置示例

本地Oracle XE版本

"ConnectionString": "Data Source=localhost:1521/XE;User Id=PRODUCTION_MES;Password=your_password;Pooling=true;Connection Timeout=60;"

远程Oracle服务器

"ConnectionString": "Data Source=192.168.1.100:1521/ORCL;User Id=PRODUCTION_MES;Password=your_password;Pooling=true;Connection Timeout=60;"

使用TNS名称连接

"ConnectionString": "Data Source=PRODUCTION_DB;User Id=PRODUCTION_MES;Password=your_password;Pooling=true;Connection Timeout=60;"

使用SID方式连接

"ConnectionString": "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SID=ORCL)));User Id=PRODUCTION_MES;Password=your_password;Pooling=true;"

🗄️ Oracle数据库准备

1. 创建用户和表空间

-- 创建表空间
CREATE TABLESPACE PRODUCTION_MES_DATA
DATAFILE 'C:\\oracle\\oradata\\XE\\production_mes_data.dbf' 
SIZE 500M AUTOEXTEND ON NEXT 10M MAXSIZE 2G;

-- 创建用户
CREATE USER PRODUCTION_MES 
IDENTIFIED BY your_password
DEFAULT TABLESPACE PRODUCTION_MES_DATA
TEMPORARY TABLESPACE TEMP;

-- 授予权限
GRANT CONNECT, RESOURCE TO PRODUCTION_MES;
GRANT CREATE TABLE TO PRODUCTION_MES;
GRANT CREATE SEQUENCE TO PRODUCTION_MES;
GRANT CREATE VIEW TO PRODUCTION_MES;
GRANT UNLIMITED TABLESPACE TO PRODUCTION_MES;

2. 表结构（自动创建）

程序会自动创建以下表：

PRODUCTION_DATA - 生产数据表

CREATE TABLE PRODUCTION_DATA (
    ID NUMBER PRIMARY KEY,
    READ_TIME DATE NOT NULL,
    PROJECT_NAME NVARCHAR2(100) NOT NULL,
    PLC_IP_ADDRESS NVARCHAR2(50) NOT NULL,
    CONTROL_SIGNALS_JSON CLOB,
    PRODUCT_DATA_JSON CLOB,
    MEASUREMENT_DATA_JSON CLOB,
    RAW_REGISTERS_JSON CLOB,
    CREATED_AT DATE DEFAULT SYSDATE
);

COMMUNICATION_LOG - 通信日志表

CREATE TABLE COMMUNICATION_LOG (
    ID NUMBER PRIMARY KEY,
    LOG_TIME DATE NOT NULL,
    PROJECT_NAME NVARCHAR2(100) NOT NULL,
    PLC_IP_ADDRESS NVARCHAR2(50) NOT NULL,
    EVENT_TYPE NVARCHAR2(50) NOT NULL,
    EVENT_DESCRIPTION NVARCHAR2(500),
    IS_SUCCESS NUMBER(1) NOT NULL,
    DURATION_MS NUMBER,
    ADDITIONAL_DATA_JSON CLOB
);

ERROR_LOG - 错误日志表

CREATE TABLE ERROR_LOG (
    ID NUMBER PRIMARY KEY,
    ERROR_TIME DATE NOT NULL,
    PROJECT_NAME NVARCHAR2(100) NOT NULL,
    PLC_IP_ADDRESS NVARCHAR2(50) NOT NULL,
    ERROR_TYPE NVARCHAR2(100) NOT NULL,
    ERROR_MESSAGE NVARCHAR2(1000) NOT NULL,
    STACK_TRACE CLOB,
    SEVERITY NVARCHAR2(20) DEFAULT 'Medium',
    RETRY_COUNT NUMBER DEFAULT 0,
    IS_RESOLVED NUMBER(1) DEFAULT 0
);

STATISTICS - 统计数据表

CREATE TABLE STATISTICS (
    ID NUMBER PRIMARY KEY,
    STATISTICS_TIME DATE NOT NULL,
    PROJECT_NAME NVARCHAR2(100) NOT NULL,
    PLC_IP_ADDRESS NVARCHAR2(50) NOT NULL,
    TOTAL_CONNECTION_ATTEMPTS NUMBER NOT NULL,
    SUCCESSFUL_CONNECTIONS NUMBER NOT NULL,
    TOTAL_DATA_READS NUMBER NOT NULL,
    SUCCESSFUL_DATA_READS NUMBER NOT NULL,
    TOTAL_ERRORS NUMBER NOT NULL,
    AVERAGE_RESPONSE_TIME_MS NUMBER(10,3),
    MAX_RESPONSE_TIME_MS NUMBER,
    MIN_RESPONSE_TIME_MS NUMBER,
    CONNECTION_SUCCESS_RATE NUMBER(5,2),
    DATA_READ_SUCCESS_RATE NUMBER(5,2),
    PERIOD_START DATE NOT NULL,
    PERIOD_END DATE NOT NULL
);

🔍 常见问题解决

1. 连接失败

• 检查Oracle服务是否启动
• 确认端口1521是否开放
• 验证用户名密码是否正确

2. 权限不足

-- 授予额外权限
GRANT CREATE ANY TABLE TO PRODUCTION_MES;
GRANT DROP ANY TABLE TO PRODUCTION_MES;

3. 字符编码问题

在连接字符串中添加：
json "ConnectionString": "Data Source=localhost:1521/XE;User Id=PRODUCTION_MES;Password=your_password;Pooling=true;Connection Timeout=60;Unicode=True;"

📊 数据查询示例

查看最新生产数据

SELECT 
    READ_TIME,
    PROJECT_NAME,
    JSON_VALUE(PRODUCT_DATA_JSON, '$.ProductModel') AS 产品型号,
    JSON_VALUE(PRODUCT_DATA_JSON, '$.TestResult') AS 测试结果,
    JSON_VALUE(MEASUREMENT_DATA_JSON, '$.WorkingVoltage') AS 工作电压
FROM PRODUCTION_DATA 
WHERE READ_TIME >= SYSDATE - 1
ORDER BY READ_TIME DESC;

查看通信统计

SELECT 
    LOG_TIME,
    EVENT_TYPE,
    EVENT_DESCRIPTION,
    IS_SUCCESS
FROM COMMUNICATION_LOG 
WHERE LOG_TIME >= SYSDATE - 1
ORDER BY LOG_TIME DESC;

查看错误日志

SELECT 
    ERROR_TIME,
    ERROR_TYPE,
    ERROR_MESSAGE,
    SEVERITY
FROM ERROR_LOG 
WHERE ERROR_TIME >= SYSDATE - 1
AND IS_RESOLVED = 0
ORDER BY ERROR_TIME DESC;

⚡ Oracle 11g 性能优化建议

1. 索引创建

-- 为常用查询字段创建索引
CREATE INDEX IDX_PRODUCTION_DATA_TIME ON PRODUCTION_DATA(READ_TIME);
CREATE INDEX IDX_COMMUNICATION_LOG_TIME ON COMMUNICATION_LOG(LOG_TIME);
CREATE INDEX IDX_ERROR_LOG_TIME ON ERROR_LOG(ERROR_TIME);

2. 分区表（大数据量时）

-- 按月分区生产数据表
CREATE TABLE PRODUCTION_DATA (
    -- 字段定义...
)
PARTITION BY RANGE (READ_TIME) (
    PARTITION P202501 VALUES LESS THAN (DATE '2025-02-01'),
    PARTITION P202502 VALUES LESS THAN (DATE '2025-03-01'),
    -- 继续添加分区...
);

3. 数据清理作业

-- 创建定期清理过期数据的作业
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name        => 'CLEANUP_OLD_DATA',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN 
                          DELETE FROM PRODUCTION_DATA WHERE READ_TIME < SYSDATE - 90;
                          DELETE FROM COMMUNICATION_LOG WHERE LOG_TIME < SYSDATE - 90;
                          DELETE FROM ERROR_LOG WHERE ERROR_TIME < SYSDATE - 90 AND IS_RESOLVED = 1;
                          COMMIT;
                        END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=DAILY; BYHOUR=2; BYMINUTE=0; BYSECOND=0',
    enabled         => TRUE
  );
END;

🚀 使用步骤

1. 准备Oracle数据库：创建用户PRODUCTION_MES并授权
2. 修改连接字符串：更新实际的服务器地址、用户名和密码
3. 启动程序：选择production_config.json配置文件
4. 连接测试：程序会自动创建表结构并开始数据采集

配置已完成，现在您的系统完全支持Oracle 11g数据库！🎉