# 1. CPU配置
– 生产环境：至少8核CPU
– RAC环境：每个节点至少8核
– 高负载：根据业务需求增加

# 2. 内存配置
– 生产环境：至少32GB内存
– SGA：总内存的40%-60%
– PGA：总内存的10%-20%
– OS预留：至少4GB

# 3. 存储配置
– 数据文件：独立存储
– 日志文件：独立存储
– 归档日志：独立存储
– 备份文件：独立存储

# 4. 网络配置
– 公网：业务访问网络
– 私网：RAC心跳网络
– 备份网络：备份专用网络

# 5. 存储布局最佳实践
/oradata/
├── control01.ctl # 控制文件1
├── control02.ctl # 控制文件2
├── redo01.log # 日志组1
├── redo02.log # 日志组2
├── redo03.log # 日志组3
├── system01.dbf # 系统表空间
├── sysaux01.dbf # 辅助表空间
├── undotbs01.dbf # 回滚表空间
├── temp01.dbf # 临时表空间
├── users01.dbf # 用户表空间
└── fgedu_data01.dbf # 业务表空间

/fast_recovery/
├── archivelog/ # 归档日志
└── backupset/ # 备份文件

# 1. 内存参数
SQL> ALTER SYSTEM SET sga_target = 8G SCOPE = SPFILE;
SQL> ALTER SYSTEM SET pga_aggregate_target = 2G SCOPE = SPFILE;
SQL> ALTER SYSTEM SET memory_target = 0 SCOPE = SPFILE;

# 2. 日志参数
SQL> ALTER SYSTEM SET db_recovery_file_dest_size = 100G SCOPE = BOTH;
SQL> ALTER SYSTEM SET db_recovery_file_dest = ‘/fast_recovery’ SCOPE = BOTH;

# 3. 进程参数
SQL> ALTER SYSTEM SET processes = 500 SCOPE = SPFILE;
SQL> ALTER SYSTEM SET sessions = 800 SCOPE = SPFILE;

# 4. 优化参数
SQL> ALTER SYSTEM SET open_cursors = 1000 SCOPE = BOTH;
SQL> ALTER SYSTEM SET cursor_sharing = FORCE SCOPE = BOTH;
SQL> ALTER SYSTEM SET optimizer_mode = ALL_ROWS SCOPE = BOTH;

# 5. 安全参数
SQL> ALTER SYSTEM SET audit_trail = DB,EXTENDED SCOPE = SPFILE;
SQL> ALTER SYSTEM SET sec_case_sensitive_logon = TRUE SCOPE = SPFILE;

# 6. 诊断参数
SQL> ALTER SYSTEM SET diagnostic_dest = ‘/oracle/app/oracle’ SCOPE = SPFILE;
SQL> ALTER SYSTEM SET max_dump_file_size = ‘1G’ SCOPE = BOTH;

# 7. 关键参数检查
SQL> SELECT name, value, isdefault
FROM v$parameter
WHERE isdefault = ‘FALSE’
ORDER BY name;

# 1. 表空间管理
# 创建表空间
SQL> CREATE TABLESPACE fgedu_data
DATAFILE ‘/oradata/fgedudb/fgedu_data01.dbf’
SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE 10G
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;

# 创建临时表空间
SQL> CREATE TEMPORARY TABLESPACE fgedu_temp
TEMPFILE ‘/oradata/fgedudb/fgedu_temp01.dbf’
SIZE 500M AUTOEXTEND ON NEXT 50M MAXSIZE 2G;

# 创建回滚表空间
SQL> CREATE UNDO TABLESPACE fgedu_undo
DATAFILE ‘/oradata/fgedudb/fgedu_undo01.dbf’
SIZE 500M AUTOEXTEND ON NEXT 50M MAXSIZE 2G;

# 2. 表空间监控
SQL> SELECT tablespace_name,
ROUND((used/total)*100, 2) used_pct,
ROUND(free/1024/1024, 2) free_mb
FROM (
SELECT tablespace_name,
SUM(bytes) total,
SUM(bytes) – (SELECT SUM(bytes) FROM dba_free_space f
WHERE f.tablespace_name = d.tablespace_name) used,
(SELECT SUM(bytes) FROM dba_free_space f
WHERE f.tablespace_name = d.tablespace_name) free
FROM dba_data_files d
GROUP BY tablespace_name
)
WHERE (used/total)*100 > 80;

# 3. 表空间维护
# 增加数据文件
SQL> ALTER TABLESPACE fgedu_data
ADD DATAFILE ‘/oradata/fgedudb/fgedu_data02.dbf’
SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE 10G;

# 扩展数据文件
SQL> ALTER DATABASE DATAFILE ‘/oradata/fgedudb/fgedu_data01.dbf’
RESIZE 2G;

# 1. 备份策略
– 全备：每周一次全备
– 增量：每日增量备份
– 归档：归档日志备份
– 控制：控制文件备份

# 2. RMAN备份配置
$ rman target /
RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
RMAN> CONFIGURE BACKUP OPTIMIZATION ON;
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 4;

# 3. 执行备份
# 全备
RMAN> BACKUP DATABASE PLUS ARCHIVELOG;

# 增量备份
RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE;

# 归档日志备份
RMAN> BACKUP ARCHIVELOG ALL DELETE INPUT;

# 4. 备份验证
RMAN> VALIDATE DATABASE;
RMAN> RESTORE DATABASE VALIDATE;

# 5. 恢复测试
# 恢复表空间
RMAN> RESTORE TABLESPACE fgedu_data;
RMAN> RECOVER TABLESPACE fgedu_data;

# 时间点恢复
RMAN> RUN {
SET UNTIL TIME “TO_DATE(‘2026-04-05 10:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)”;
RESTORE DATABASE;
RECOVER DATABASE;
}

# 1. SQL识别
# 查找消耗资源最多的SQL
SQL> SELECT sql_id, executions,
ROUND(elapsed_time/1000000, 2) elapsed_sec,
ROUND(buffer_gets/executions, 2) buffer_per_exec
FROM v$sql
WHERE executions > 0
ORDER BY elapsed_time DESC
WHERE ROWNUM <= 10;

# 2. SQL分析
# 查看执行计划
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(‘sql_id’));

# 查看SQL统计
SQL> SELECT * FROM v$sqlstats WHERE sql_id = ‘sql_id’;

# 3. SQL调优
# 使用SQL调优顾问
SQL> DECLARE
l_task_name VARCHAR2(30);
BEGIN
l_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_id => ‘sql_id’,
scope => DBMS_SQLTUNE.SCOPE_COMPREHENSIVE
);
DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => l_task_name);
END;
/

SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(‘task_name’) FROM dual;

# 4. SQL优化建议
– 使用绑定变量
– 避免全表扫描
– 使用合适的索引
– 避免隐式转换
– 优化JOIN操作

# 5. 索引优化
# 创建索引
SQL> CREATE INDEX idx_customers_name
ON customers(customer_name)
TABLESPACE fgedu_index;

# 监控索引使用
SQL> ALTER INDEX idx_customers_name MONITORING USAGE;
SQL> SELECT * FROM v$object_usage WHERE index_name = ‘IDX_CUSTOMERS_NAME’;

# 1. SGA优化
# Buffer Cache
SQL> ALTER SYSTEM SET db_cache_size = 4G SCOPE = SPFILE;

# Shared Pool
SQL> ALTER SYSTEM SET shared_pool_size = 2G SCOPE = SPFILE;

# Large Pool
SQL> ALTER SYSTEM SET large_pool_size = 512M SCOPE = SPFILE;

# Java Pool
SQL> ALTER SYSTEM SET java_pool_size = 256M SCOPE = SPFILE;

# 2. PGA优化
SQL> ALTER SYSTEM SET pga_aggregate_target = 2G SCOPE = SPFILE;

# 3. 内存监控
SQL> SELECT component, current_size/1024/1024 size_mb
FROM v$sga_dynamic_components;

SQL> SELECT name, value/1024/1024 size_mb
FROM v$pgastat
WHERE name IN (‘total PGA allocated’, ‘total PGA inuse’);

# 4. Buffer Cache命中率
SQL> SELECT ROUND((1 – (phy.value / (cur.value + con.value))) * 100, 2) hit_ratio
FROM v$sysstat cur, v$sysstat con, v$sysstat phy
WHERE cur.name = ‘db block gets’
AND con.name = ‘consistent gets’
AND phy.name = ‘physical reads’;

# 5. Shared Pool命中率
SQL> SELECT ROUND((1 – (reloads/pins)) * 100, 2) hit_ratio
FROM v$librarycache
WHERE namespace = ‘SQL AREA’;