Oracle日常运维 #

-- 检查实例状态
SELECT instance_name, status, startup_time
FROM v$instance;

-- 检查数据库状态
SELECT name, open_mode, database_role
FROM v$database;

-- 检查后台进程
SELECT name, description, status
FROM v$bgprocess
WHERE paddr != '00';

-- 检查监听状态
-- 命令行执行
-- lsnrctl status

-- 查看当前会话
SELECT 
    sid,
    serial#,
    username,
    status,
    osuser,
    machine,
    program
FROM v$session
WHERE username IS NOT NULL;

-- 查看活跃会话
SELECT 
    sid,
    serial#,
    username,
    status,
    sql_id,
    event
FROM v$session
WHERE status = 'ACTIVE' AND username IS NOT NULL;

-- 查看阻塞会话
SELECT 
    blocking_session,
    sid,
    serial#,
    wait_class,
    seconds_in_wait
FROM v$session
WHERE blocking_session IS NOT NULL;

-- 终止会话
ALTER SYSTEM KILL SESSION 'sid,serial#';

-- 查看系统统计
SELECT * FROM v$sysstat
WHERE name IN ('physical reads', 'physical writes', 'redo writes', 'parse count (hard)');

-- 查看等待事件
SELECT event, total_waits, time_waited, average_wait
FROM v$system_event
ORDER BY time_waited DESC
FETCH FIRST 10 ROWS ONLY;

-- 查看IO统计
SELECT name, phyrds, phywrts, readtim, writetim
FROM v$filestat f, v$datafile d
WHERE f.file# = d.file#
ORDER BY phyrds + phywrts DESC;

-- 查看锁等待
SELECT 
    o.object_name,
    l.session_id,
    l.locked_mode,
    s.username,
    s.status
FROM v$locked_object l
JOIN dba_objects o ON l.object_id = o.object_id
JOIN v$session s ON l.session_id = s.sid;

-- 查看告警日志位置
SELECT value FROM v$parameter WHERE name = 'background_dump_dest';

-- 查看告警日志内容（需要目录权限）
-- 使用ADRCI工具
-- adrci> show alert -tail 100

-- 查看最近错误
SELECT * FROM (
    SELECT 
        originating_timestamp,
        message_text
    FROM v$diag_alert_ext
    WHERE component_id = 'rdbms'
    ORDER BY originating_timestamp DESC
)
WHERE ROWNUM <= 20;

-- 查看归档日志状态
ARCHIVE LOG LIST;

-- 查看归档日志信息
SELECT 
    name,
    sequence#,
    first_time,
    next_time,
    blocks * block_size / 1024 / 1024 AS size_mb
FROM v$archived_log
ORDER BY sequence# DESC
FETCH FIRST 10 ROWS ONLY;

-- 查看归档日志空间
SELECT 
    name,
    space_limit / 1024 / 1024 AS space_limit_mb,
    space_used / 1024 / 1024 AS space_used_mb,
    space_reclaimable / 1024 / 1024 AS space_reclaimable_mb
FROM v$recovery_file_dest;

-- 删除过期归档日志（RMAN）
-- RMAN> DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-7';

-- 查看审计日志
SELECT 
    timestamp,
    username,
    userhost,
    action_name,
    returncode
FROM dba_audit_trail
ORDER BY timestamp DESC
FETCH FIRST 20 ROWS ONLY;

-- 清理审计日志
-- TRUNCATE TABLE aud$;

-- 查看表空间使用情况
SELECT 
    t.tablespace_name,
    ROUND(t.total_space_mb, 2) AS total_mb,
    ROUND(f.free_space_mb, 2) AS free_mb,
    ROUND(t.total_space_mb - f.free_space_mb, 2) AS used_mb,
    ROUND((t.total_space_mb - f.free_space_mb) / t.total_space_mb * 100, 2) AS used_pct
FROM (
    SELECT 
        tablespace_name,
        SUM(bytes) / 1024 / 1024 AS total_space_mb
    FROM dba_data_files
    GROUP BY tablespace_name
) t
JOIN (
    SELECT 
        tablespace_name,
        SUM(bytes) / 1024 / 1024 AS free_space_mb
    FROM dba_free_space
    GROUP BY tablespace_name
) f ON t.tablespace_name = f.tablespace_name
ORDER BY used_pct DESC;

-- 查看表空间自动扩展
SELECT 
    tablespace_name,
    file_name,
    bytes / 1024 / 1024 AS size_mb,
    autoextensible,
    maxbytes / 1024 / 1024 AS max_size_mb
FROM dba_data_files
ORDER BY tablespace_name;

-- 添加数据文件
ALTER TABLESPACE users
ADD DATAFILE '/u01/app/oracle/oradata/orcl/users02.dbf' SIZE 1G;

-- 调整数据文件大小
ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/orcl/users01.dbf'
RESIZE 2G;

-- 启用自动扩展
ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/orcl/users01.dbf'
AUTOEXTEND ON NEXT 100M MAXSIZE 10G;

-- 查看大段
SELECT 
    segment_name,
    segment_type,
    tablespace_name,
    bytes / 1024 / 1024 AS size_mb
FROM dba_segments
ORDER BY bytes DESC
FETCH FIRST 20 ROWS ONLY;

-- 查看表碎片
SELECT 
    table_name,
    num_rows,
    blocks,
    empty_blocks,
    avg_row_len
FROM dba_tables
WHERE blocks > 1000
ORDER BY empty_blocks DESC;

-- 整理表碎片
ALTER TABLE employees MOVE;
ALTER INDEX idx_emp_name REBUILD;

-- 1. 会话阻塞
-- 查找阻塞会话
SELECT 
    s1.sid AS blocking_sid,
    s2.sid AS blocked_sid,
    s1.username AS blocking_user,
    s2.username AS blocked_user,
    s2.event
FROM v$session s1, v$session s2
WHERE s1.sid = s2.blocking_session;

-- 终止阻塞会话
ALTER SYSTEM KILL SESSION 'sid,serial#';

-- 2. 归档日志空间不足
-- 查看空间
SELECT * FROM v$recovery_file_dest;

-- 删除过期归档
-- RMAN> DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-3';

-- 扩展空间
ALTER SYSTEM SET db_recovery_file_dest_size = 20G;

-- 3. 表空间空间不足
-- 查看表空间使用
SELECT * FROM dba_tablespace_usage_metrics;

-- 扩展表空间
ALTER TABLESPACE users ADD DATAFILE '...' SIZE 1G;

-- 正常启动
STARTUP;

-- 启动到NOMOUNT
STARTUP NOMOUNT;

-- 启动到MOUNT
STARTUP MOUNT;

-- 正常关闭
SHUTDOWN IMMEDIATE;

-- 事务关闭
SHUTDOWN TRANSACTIONAL;

-- 中止关闭（不推荐）
SHUTDOWN ABORT;

-- 启动到只读模式
STARTUP MOUNT;
ALTER DATABASE OPEN READ ONLY;

-- 启动监听
-- lsnrctl start

-- 停止监听
-- lsnrctl stop

-- 查看监听状态
-- lsnrctl status

-- 重载监听配置
-- lsnrctl reload

-- 查看监听服务
-- lsnrctl services

每日检查清单
├── 实例状态
│   └── 数据库是否正常运行
├── 监听状态
│   └── 监听是否正常
├── 表空间使用
│   └── 空间是否充足
├── 归档日志
│   └── 归档是否正常
├── 备份状态
│   └── 备份是否成功
└── 告警日志
    └── 是否有错误

每周检查清单
├── 空间增长趋势
│   └── 分析空间增长
├── 性能趋势
│   └── 分析AWR报告
├── 无效对象
│   └── 重新编译无效对象
├── 统计信息
│   └── 收集统计信息
└── 备份验证
    └── 验证备份有效性

每月检查清单
├── 安全审计
│   └── 检查用户权限
├── 参数检查
│   └── 检查参数配置
├── 补丁更新
│   └── 检查是否需要补丁
├── 容量规划
│   └── 评估容量需求
└── 文档更新
    └── 更新运维文档

-- 数据库健康检查
DECLARE
    v_status VARCHAR2(20);
    v_tablespace_warning BOOLEAN := FALSE;
BEGIN
    -- 检查实例状态
    SELECT status INTO v_status FROM v$instance;
    DBMS_OUTPUT.PUT_LINE('Instance Status: ' || v_status);
    
    -- 检查表空间
    FOR t IN (
        SELECT tablespace_name, 
               ROUND((used_mb / total_mb) * 100, 2) AS used_pct
        FROM (
            SELECT 
                t.tablespace_name,
                SUM(d.bytes) / 1024 / 1024 AS total_mb,
                SUM(NVL(f.bytes, 0)) / 1024 / 1024 AS free_mb,
                SUM(d.bytes - NVL(f.bytes, 0)) / 1024 / 1024 AS used_mb
            FROM dba_tablespaces t
            JOIN dba_data_files d ON t.tablespace_name = d.tablespace_name
            LEFT JOIN dba_free_space f ON d.file_id = f.file_id
            GROUP BY t.tablespace_name
        )
    ) LOOP
        IF t.used_pct > 80 THEN
            DBMS_OUTPUT.PUT_LINE('WARNING: ' || t.tablespace_name || ' is ' || t.used_pct || '% used');
            v_tablespace_warning := TRUE;
        END IF;
    END LOOP;
    
    IF NOT v_tablespace_warning THEN
        DBMS_OUTPUT.PUT_LINE('All tablespaces are within limits');
    END IF;
END;
/