Giám sát cơ sở dữ liệu Oracle với PL/SQL

Monitoring the entire Oracle database involves collecting information on various aspects of the database, including performance metrics, resource usage, health checks, and more.

Here's a basic outline of the monitoring tasks:

1. Database Health Checks:

• Check database availability.
• Check tablespace status and space usage.
• Check database uptime and last startup time.
• Check for any critical database errors.

2. Performance Monitoring:

• Monitor CPU usage.
• Monitor memory usage.
• Monitor disk I/O performance.
• Monitor long-running queries.

3. Session Monitoring:

• Monitor active sessions and their activities.
• Monitor locks and wait events.

4. Backup and Recovery Monitoring:

• Check the status of the last database backup.
• Monitor the redo log switch frequency.

5. Resource Usage:

• Monitor the size and growth of database objects (tables, indexes, etc.).
• Monitor the size and growth of specific schemas.

CREATE OR REPLACE PROCEDURE monitor_database AS
  v_timestamp TIMESTAMP := SYSTIMESTAMP;
  v_username VARCHAR2(30);
  v_tablespace_name VARCHAR2(30);
  v_table_count NUMBER;
  v_table_size_mb NUMBER;
  v_used_space_mb NUMBER;
  v_free_space_mb NUMBER;
  v_cpu_usage NUMBER;
  v_memory_usage NUMBER;
BEGIN
  -- Get current username
  SELECT USER INTO v_username FROM DUAL;

  -- Print timestamp and username
  DBMS_OUTPUT.PUT_LINE('Timestamp: ' || v_timestamp);
  DBMS_OUTPUT.PUT_LINE('Username: ' || v_username);

  -- Check database availability
  IF NOT DBMS_DB_VERSION.VERIFIER_AVAILABLE THEN
    DBMS_OUTPUT.PUT_LINE('WARNING: Database is not available.');
  END IF;

  -- Check database uptime and last startup time
  DBMS_OUTPUT.PUT_LINE('Database Uptime: ' || TRUNC(UPPER(SYSDATE) - LOWER(STARTUP_TIME)) || ' days');
  DBMS_OUTPUT.PUT_LINE('Last Startup Time: ' || TO_CHAR(STARTUP_TIME, 'DD-MON-YYYY HH24:MI:SS'));

  -- Monitor tablespace usage
  FOR ts IN (SELECT tablespace_name FROM dba_tablespaces) LOOP
    v_tablespace_name := ts.tablespace_name;

    -- Get table count and size
    SELECT COUNT(*) INTO v_table_count FROM dba_tables WHERE tablespace_name = v_tablespace_name;
    SELECT SUM(bytes) / 1024 / 1024 INTO v_table_size_mb FROM dba_segments WHERE tablespace_name = v_tablespace_name;

    -- Get used and free space
    v_used_space_mb := (v_table_size_mb - (SELECT SUM(bytes) / 1024 / 1024 FROM dba_free_space WHERE tablespace_name = v_tablespace_name));
    v_free_space_mb := (SELECT SUM(bytes) / 1024 / 1024 FROM dba_free_space WHERE tablespace_name = v_tablespace_name);

    -- Print tablespace information
    DBMS_OUTPUT.PUT_LINE('Tablespace: ' || v_tablespace_name);
    DBMS_OUTPUT.PUT_LINE('Table Count: ' || v_table_count);
    DBMS_OUTPUT.PUT_LINE('Tablespace Size (MB): ' || v_table_size_mb);
    DBMS_OUTPUT.PUT_LINE('Used Space (MB): ' || v_used_space_mb);
    DBMS_OUTPUT.PUT_LINE('Free Space (MB): ' || v_free_space_mb);
    DBMS_OUTPUT.PUT_LINE('-----------------------');
  END LOOP;

  -- Monitor CPU and Memory usage
  SELECT ROUND(VALUE/100, 2) INTO v_cpu_usage FROM v$osstat WHERE NAME = 'IDLE_TIME';
  SELECT ROUND(SUM(BYTES)/1024/1024, 2) INTO v_memory_usage FROM v$sgastat WHERE POOL = 'shared pool';

  DBMS_OUTPUT.PUT_LINE('CPU Usage (%): ' || v_cpu_usage);
  DBMS_OUTPUT.PUT_LINE('Memory Usage (MB): ' || v_memory_usage);

EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;

view raw Giám sát cơ sở dữ liệu Oracle với PLSQL.md hosted with ❤ by GitHub