# Oracle 11g 数据库配置说明

## 🔧 配置已调整为Oracle 11g

### 当前数据库配置
```json
"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版本
```json
"ConnectionString": "Data Source=localhost:1521/XE;User Id=PRODUCTION_MES;Password=your_password;Pooling=true;Connection Timeout=60;"
```

#### 远程Oracle服务器
```json
"ConnectionString": "Data Source=192.168.1.100:1521/ORCL;User Id=PRODUCTION_MES;Password=your_password;Pooling=true;Connection Timeout=60;"
```

#### 使用TNS名称连接
```json
"ConnectionString": "Data Source=PRODUCTION_DB;User Id=PRODUCTION_MES;Password=your_password;Pooling=true;Connection Timeout=60;"
```

#### 使用SID方式连接
```json
"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. 创建用户和表空间
```sql
-- 创建表空间
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 - 生产数据表
```sql
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 - 通信日志表
```sql
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 - 错误日志表
```sql
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 - 统计数据表
```sql
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. 权限不足
```sql
-- 授予额外权限
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;"
```

## 📊 数据查询示例

### 查看最新生产数据
```sql
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;
```

### 查看通信统计
```sql
SELECT 
    LOG_TIME,
    EVENT_TYPE,
    EVENT_DESCRIPTION,
    IS_SUCCESS
FROM COMMUNICATION_LOG 
WHERE LOG_TIME >= SYSDATE - 1
ORDER BY LOG_TIME DESC;
```

### 查看错误日志
```sql
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. 索引创建
```sql
-- 为常用查询字段创建索引
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. 分区表（大数据量时）
```sql
-- 按月分区生产数据表
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. 数据清理作业
```sql
-- 创建定期清理过期数据的作业
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数据库！🎉