Oracle Best Practices

Oracle Best Practices

Follow these proven best practices to ensure optimal performance, security, and reliability of your Oracle databases.

🏗️ Database Design

1. Naming Conventions

Establish consistent naming standards:

-- Tables: Plural, uppercase
CREATE TABLE EMPLOYEES (...);

-- Columns: Descriptive, use underscores
employee_id NUMBER,
first_name VARCHAR2(50),
hire_date DATE

-- Indexes: Prefix with IX_ or IDX_
CREATE INDEX IX_EMP_DEPT_ID ON EMPLOYEES(department_id);

-- Primary Keys: Prefix with PK_
ALTER TABLE EMPLOYEES ADD CONSTRAINT PK_EMPLOYEES
  PRIMARY KEY (employee_id);

-- Foreign Keys: Prefix with FK_
ALTER TABLE EMPLOYEES ADD CONSTRAINT FK_EMP_DEPT
  FOREIGN KEY (department_id) REFERENCES DEPARTMENTS(department_id);

2. Normalization

Follow normalization rules while considering performance:

• Normalize to 3NF for OLTP systems
• Consider denormalization for OLAP/reporting
• Use materialized views for complex aggregations

3. Partitioning Strategy

Implement partitioning for large tables:

-- Range partitioning for time-series data
CREATE TABLE SALES (
    sale_id NUMBER,
    sale_date DATE,
    amount NUMBER(10,2)
)
PARTITION BY RANGE (sale_date) (
    PARTITION p_2023 VALUES LESS THAN (DATE '2024-01-01'),
    PARTITION p_2024 VALUES LESS THAN (DATE '2025-01-01'),
    PARTITION p_future VALUES LESS THAN (MAXVALUE)
);

🚀 Performance Optimization

1. Indexing Best Practices

Create appropriate indexes:

-- Analyze query patterns first
SELECT sql_id, executions, buffer_gets, disk_reads
FROM v$sql
WHERE sql_text LIKE '%YOUR_TABLE%'
ORDER BY buffer_gets DESC;

-- Create covering indexes for frequent queries
CREATE INDEX IX_EMP_COVERING ON EMPLOYEES(
    department_id,
    job_id,
    salary
) COMPRESS 2;

-- Monitor index usage
SELECT index_name, table_name, monitoring, used
FROM v$object_usage;

2. Statistics Management

Keep optimizer statistics current:

-- Gather statistics regularly
BEGIN
    DBMS_STATS.gather_database_stats(
        estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
        method_opt => 'FOR ALL COLUMNS SIZE AUTO',
        cascade => TRUE
    );
END;
/

-- Lock statistics for static tables
EXEC DBMS_STATS.lock_table_stats('SCHEMA', 'STATIC_TABLE');

3. SQL Tuning

Write efficient SQL:

-- Use bind variables
DECLARE
    v_dept_id NUMBER := 10;
    v_salary NUMBER;
BEGIN
    SELECT AVG(salary) INTO v_salary
    FROM employees
    WHERE department_id = v_dept_id;  -- Bind variable
END;
/

-- Avoid SELECT *
-- Bad
SELECT * FROM employees;

-- Good
SELECT employee_id, first_name, last_name, salary
FROM employees;

-- Use EXISTS instead of IN for subqueries
-- Better performance
SELECT e.employee_id
FROM employees e
WHERE EXISTS (
    SELECT 1
    FROM departments d
    WHERE d.department_id = e.department_id
    AND d.location_id = 1700
);

🔒 Security Best Practices

1. User Management

Implement least privilege principle:

-- Create roles for job functions
CREATE ROLE app_read_only;
GRANT SELECT ON schema.table TO app_read_only;

CREATE ROLE app_developer;
GRANT SELECT, INSERT, UPDATE, DELETE ON schema.table TO app_developer;

-- Assign roles to users
GRANT app_read_only TO user1;
GRANT app_developer TO user2;

-- Implement password policies
ALTER PROFILE DEFAULT LIMIT
    FAILED_LOGIN_ATTEMPTS 5
    PASSWORD_LIFE_TIME 90
    PASSWORD_REUSE_TIME 365
    PASSWORD_REUSE_MAX 10
    PASSWORD_VERIFY_FUNCTION verify_function_11g;

2. Auditing

Enable comprehensive auditing:

-- Enable unified auditing
CREATE AUDIT POLICY sensitive_data_access
ACTIONS SELECT ON hr.employees,
        SELECT ON hr.salary_history
WHEN 'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') NOT IN (''HR_ADMIN'')';

AUDIT POLICY sensitive_data_access;

-- Monitor privileged users
AUDIT ALL STATEMENTS BY DBA;

3. Encryption

Protect sensitive data:

-- Transparent Data Encryption (TDE)
-- Encrypt tablespace
CREATE TABLESPACE secure_ts
DATAFILE '/u01/oradata/secure_ts01.dbf' SIZE 100M
ENCRYPTION USING 'AES256'
DEFAULT STORAGE(ENCRYPT);

-- Column-level encryption
ALTER TABLE employees MODIFY (salary ENCRYPT);

💾 Backup and Recovery

1. RMAN Best Practices

Configure comprehensive backup strategy:

-- Configure retention policy
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;

-- Enable control file autobackup
CONFIGURE CONTROLFILE AUTOBACKUP ON;

-- Set parallelism for performance
CONFIGURE DEVICE TYPE DISK PARALLELISM 4;

-- Implement incremental backup strategy
RUN {
    BACKUP INCREMENTAL LEVEL 0 DATABASE PLUS ARCHIVELOG;
    DELETE NOPROMPT OBSOLETE;
}

2. Backup Validation

Regularly validate backups:

-- Validate database backup
RMAN> VALIDATE DATABASE;

-- Check backup integrity
RMAN> RESTORE DATABASE VALIDATE;

-- Test recovery scenarios
RMAN> RESTORE DATABASE UNTIL TIME
      "TO_DATE('2024-01-15 12:00:00','YYYY-MM-DD HH24:MI:SS')"
      VALIDATE;

🔧 Maintenance Tasks

1. Regular Health Checks

Schedule automated health monitoring:

BEGIN
    DBMS_SCHEDULER.create_job(
        job_name => 'DAILY_HEALTH_CHECK',
        job_type => 'PLSQL_BLOCK',
        job_action => 'BEGIN health_check_pkg.run_full_check; END;',
        start_date => SYSTIMESTAMP,
        repeat_interval => 'FREQ=DAILY; BYHOUR=6',
        enabled => TRUE
    );
END;
/

2. Space Management

Proactive space monitoring:

-- Monitor tablespace growth
CREATE OR REPLACE PROCEDURE check_tablespace_usage
IS
    CURSOR c_tablespaces IS
        SELECT tablespace_name,
               ROUND((used_space/tablespace_size)*100, 2) as pct_used
        FROM dba_tablespace_usage_metrics
        WHERE (used_space/tablespace_size)*100 > 80;
BEGIN
    FOR ts IN c_tablespaces LOOP
        DBMS_OUTPUT.PUT_LINE('Warning: ' || ts.tablespace_name ||
                           ' is ' || ts.pct_used || '% full');
        -- Add alerting logic here
    END LOOP;
END;
/

3. Archive Log Management

Prevent archive destination issues:

-- Monitor archive destination usage
SELECT dest_name,
       status,
       ROUND(percent_full, 2) as pct_full
FROM v$archive_dest_status
WHERE status = 'VALID';

-- Automated archive deletion
RMAN> DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-7';

📋 Development Standards

1. PL/SQL Coding Standards

Write maintainable code:

CREATE OR REPLACE PACKAGE employee_mgmt
IS
    -- Constants
    c_max_salary CONSTANT NUMBER := 100000;

    -- Exceptions
    e_salary_too_high EXCEPTION;
    PRAGMA EXCEPTION_INIT(e_salary_too_high, -20001);

    -- Procedures
    PROCEDURE update_salary(
        p_employee_id IN employees.employee_id%TYPE,
        p_new_salary  IN employees.salary%TYPE
    );
END employee_mgmt;
/

2. Error Handling

Implement comprehensive error handling:

BEGIN
    -- Main logic
    UPDATE employees SET salary = p_new_salary
    WHERE employee_id = p_employee_id;

    IF SQL%ROWCOUNT = 0 THEN
        RAISE_APPLICATION_ERROR(-20002, 'Employee not found');
    END IF;

EXCEPTION
    WHEN e_salary_too_high THEN
        log_error('Salary exceeds maximum: ' || p_new_salary);
        RAISE;
    WHEN OTHERS THEN
        log_error('Unexpected error: ' || SQLERRM);
        RAISE;
END;

🌐 High Availability

1. Data Guard Configuration

Implement disaster recovery:

-- Monitor Data Guard status
SELECT database_role, open_mode, protection_mode
FROM v$database;

-- Check standby lag
SELECT name, value, unit
FROM v$dataguard_stats
WHERE name IN ('transport lag', 'apply lag');

2. RAC Best Practices

Optimize cluster performance:

-- Monitor interconnect performance
SELECT instance_number, name, value
FROM gv$sysstat
WHERE name LIKE '%interconnect%'
ORDER BY instance_number, name;

-- Check cluster load balance
SELECT inst_id, COUNT(*) session_count
FROM gv$session
WHERE type = 'USER'
GROUP BY inst_id;

📚 Documentation

1. Database Documentation

Maintain comprehensive documentation:

• Data dictionary descriptions
• Business rule documentation
• Schema change history
• Performance baselines

2. Code Comments

Document code thoroughly:

/******************************************************************************
 * Package: EMPLOYEE_MGMT
 * Purpose: Manages employee data operations
 * Author: DBA Team
 * Created: 2024-01-15
 *
 * Modification History:
 * Date        Author      Description
 * ----------  ----------  ---------------------------------------------------
 * 2024-01-15  DBA Team    Initial creation
 ******************************************************************************/

🎯 Summary

Key takeaways:

1. Plan before implementing - Design with performance in mind
2. Monitor continuously - Proactive monitoring prevents issues
3. Automate routine tasks - Reduce human error
4. Document everything - Future you will thank you
5. Test thoroughly - Especially backup and recovery procedures
6. Stay current - Keep up with Oracle updates and patches

Remember: These are guidelines. Always adapt them to your specific environment and requirements.