Skip to main content

Oracle Staging Environment

Introduction​

Efficient performance of the Oracle Database server is crucial for the optimal functioning of the enov8 Test Data Management (TDM) Tool. This document outlines the standard configurations required for an Oracle Database staging server and provides a comprehensive set of troubleshooting steps for Database Administrators (DBAs) to ensure that the database aligns with these standards and operates at peak efficiency.

Disclaimer

This documentation is written for Oracle Database versions 19c and 21c, specifically for use in staging environments. The configurations and SQL queries provided are recommendations based on typical staging setups and may not be suitable for all environments. It is essential to evaluate these recommendations in the context of your specific staging environment and requirements. Consult with your organization's database administrators and Oracle support for guidance tailored to your specific needs.

1. Standard Database Server Configuration​

1.1 Hardware Requirements​

The hardware requirements should scale based on the size of the database and the volume of data being masked. Below is a comprehensive guideline for CPU, memory, disk, and network:

CPU​

  • Cores: Start with 4-6 cores, add 1 core per 0.5 TB of data.
  • Example:
    • For a 1 TB database: 8 cores.
    • For a 10 TB database: 16 cores.

Memory (RAM)​

  • Base Requirement: Start with 16 GB, add 8 GB per 0.5 TB of data.
  • Example:
    • For a 1 TB database: 32 GB of RAM.
    • For a 10 TB database: 96 GB of RAM.

Disk​

  • Type: Use SSDs for optimal performance, especially for I/O-intensive operations.
  • Capacity: Ensure sufficient storage for data, logs, and backups.
  • IOPS: Minimum of 10,000 IOPS for SSDs; 300 IOPS for HDDs.
  • Example:
    • For a 1 TB database: 2 TB of SSD storage.

Network​

  • Bandwidth: At least 1 Gbps network interface card (NIC).
  • Latency: Low-latency network connections to minimize delays.

1.2 System-Level Database Optimizations​

Optimizing for Bulk Data Masking​

  • Disable Archive Logging During Masking:

    • Purpose: Reduces I/O overhead by not writing redo logs to archive logs.
    • Implementation: 
      -- As SYSDBA
      SHUTDOWN IMMEDIATE;
      STARTUP MOUNT;
      ALTER DATABASE NOARCHIVELOG;
      ALTER DATABASE OPEN;

  • Adjust Undo Management:

    • Purpose: Minimize the generation of undo data.
    • Implementation: 
      ALTER SYSTEM SET UNDO_RETENTION = 300 SCOPE=MEMORY SID='*'; -- Set to 5 minutes

  • Disable Supplemental Logging:

    • Purpose: Reduces overhead if supplemental logging is not required during the masking process.

    • Implementation:

      ALTER DATABASE DROP SUPPLEMENTAL LOG DATA;

  • Disable Triggers and Constraints (if applicable):

    • Purpose: Improves performance by preventing additional processing.
    • Implementation: 
      -- Disable all triggers
      BEGIN
        FOR t IN (SELECT OWNER, TRIGGER_NAME FROM DBA_TRIGGERS WHERE OWNER = 'YOUR_SCHEMA') LOOP
          EXECUTE IMMEDIATE 'ALTER TRIGGER ' || t.OWNER || '.' || t.TRIGGER_NAME || ' DISABLE';
        END LOOP;
      END;
      /

      -- Re-enable triggers after masking
      BEGIN
        FOR t IN (SELECT OWNER, TRIGGER_NAME FROM DBA_TRIGGERS WHERE OWNER = 'YOUR_SCHEMA') LOOP
          EXECUTE IMMEDIATE 'ALTER TRIGGER ' || t.OWNER || '.' || t.TRIGGER_NAME || ' ENABLE';
        END LOOP;
      END;
      /

Other Operating System Configurations​

  • Filesystem: Use Oracle ASM or high-performance filesystems like XFS (Linux) for database files.

  • Kernel Parameters (Linux):

    ParameterRecommended Setting
    kernel.shmmaxHalf of physical memory (e.g., 16 GB for a 32 GB system)
    kernel.sem250 32000 100 128

  • User Limits (Linux):

    LimitRecommended Setting
    Open Files (nofile)65536
    Max Processes (nproc)16384

1.3 Oracle Database Internal Configuration​

Memory Management​

  • Parameters:
    • MEMORY_TARGET: 60-80% of total RAM (e.g., 48 GB on a 64 GB system)
    • MEMORY_MAX_TARGET: Equal to or greater than MEMORY_TARGET
  • Implementation: 
    ALTER SYSTEM SET MEMORY_TARGET=48G SCOPE=SPFILE;
    ALTER SYSTEM SET MEMORY_MAX_TARGET=48G SCOPE=SPFILE;
  • Purpose: Ensures sufficient memory allocation for SGA and PGA to handle large queries efficiently.

Process and Session Parameters​

  • Parameters:
    • PROCESSES: Calculate based on expected concurrent sessions (e.g., 1000)
    • SESSIONS: PROCESSES * 1.1 + 5 (e.g., 1105)
  • Implementation: 
    ALTER SYSTEM SET PROCESSES=1000 SCOPE=SPFILE;
    ALTER SYSTEM SET SESSIONS=1105 SCOPE=SPFILE;
  • Purpose: Accommodates the expected number of concurrent connections during the masking process.

Optimizer Settings​

  • Parameter:
    • OPTIMIZER_MODE: ALL_ROWS
  • Implementation: 
    ALTER SYSTEM SET OPTIMIZER_MODE='ALL_ROWS' SCOPE=SPFILE;
  • Purpose: Optimizes execution plans for batch operations common in data masking.

Redo Logs​

  • Configuration:
    • Create Redo Log Groups: 
      ALTER DATABASE ADD LOGFILE GROUP 1 ('/u01/app/oracle/oradata/redo01.log') SIZE 2G;
      ALTER DATABASE ADD LOGFILE GROUP 2 ('/u01/app/oracle/oradata/redo02.log') SIZE 2G;
      ALTER DATABASE ADD LOGFILE GROUP 3 ('/u01/app/oracle/oradata/redo03.log') SIZE 2G;
    • Add Members to Redo Log Groups: 
      ALTER DATABASE ADD LOGFILE MEMBER '/u01/app/oracle/oradata/redo01b.log' TO GROUP 1;
      ALTER DATABASE ADD LOGFILE MEMBER '/u01/app/oracle/oradata/redo02b.log' TO GROUP 2;
      ALTER DATABASE ADD LOGFILE MEMBER '/u01/app/oracle/oradata/redo03b.log' TO GROUP 3;
  • Purpose: Ensures smooth log switching and reduces bottlenecks during heavy DML operations.

Tablespace Configuration​

  • Bigfile Tablespaces:

    • Purpose: Simplifies management and improves performance for large databases.
    • Implementation: 
      CREATE BIGFILE TABLESPACE big_tbs
      DATAFILE '/u01/app/oracle/oradata/big_tbs01.dbf'
      SIZE 10G
      AUTOEXTEND ON
      NEXT 1G
      MAXSIZE UNLIMITED;
    • Considerations: Ensure storage can handle large files efficiently.

  • Automatic Segment Space Management (ASSM):

    • Purpose: Automates free space management, reducing manual intervention.
    • Implementation: 
      CREATE TABLESPACE assm_tbs
      DATAFILE '/u01/app/oracle/oradata/assm_tbs01.dbf'
      SIZE 5G
      SEGMENT SPACE MANAGEMENT AUTO;
    • Benefits: Reduces contention for free space, beneficial in high DML environments.

  • Extent Management:

    • Locally Managed Tablespaces (LMT): Use uniform extent sizes to improve performance. 
      CREATE TABLESPACE lmt_tbs
      DATAFILE '/u01/app/oracle/oradata/lmt_tbs01.dbf'
      SIZE 5G
      EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
    • Purpose: Reduces fragmentation and improves space allocation efficiency.

  • Temporary Tablespace:

    • Purpose: Used for sorting operations and temporary data storage.
    • Configuration: 
      CREATE TEMPORARY TABLESPACE temp_tbs
      TEMPFILE '/u01/app/oracle/oradata/temp_tbs01.dbf'
      SIZE 20G
      AUTOEXTEND ON
      NEXT 1G
      MAXSIZE UNLIMITED;
    • Considerations: Ensure adequate sizing for large sort operations.

  • Monitoring and Maintenance:

    • Regular Monitoring: Use Oracle Enterprise Manager or scripts to monitor usage and growth.
    • Reclaiming Space: Use the SHRINK command to reclaim unused space. 
      ALTER TABLESPACE big_tbs SHRINK SPACE;
    • Alerts: Set up usage threshold alerts to manage space proactively.

1.4 Network Configuration​

  • Oracle Net Services:

    • Optimize sqlnet.ora and listener.ora for performance.
    • Adjust Session Data Unit (SDU) size if necessary.

  • Connection Protocols:

    • Use Dedicated Server Mode for heavy processing.

1.5 Security and Patch Management​

  • Patching:

    • Regularly apply the latest Critical Patch Updates (CPUs) and Patch Set Updates (PSUs).

  • Security Parameters:

    • REMOTE_LOGIN_PASSWORDFILE: EXCLUSIVE
    • SQL92_SECURITY: TRUE

  • Purpose: Ensure security and compliance with organizational policies.

2. Troubleshooting Steps for DBAs​

When experiencing performance issues with the TDM masking tool, the following steps can help identify and resolve common problems.

2.1 Verify Hardware and OS Configuration​

Check CPU and Memory​

  • Purpose: Ensure hardware meets specifications.
  • Commands:
    • Linux: 
      lscpu
    • Memory: 
      free -h
  • Verification: Confirm CPU and memory align with requirements.

Disk I/O Performance​

  • Purpose: Ensure disk I/O supports data masking operations.

  • Commands:

    iostat -x 1 5

  • Expected Output:

    Device:         rrqm/s wrqm/s   r/s   w/s  rkB/s  wkB/s avgrq-sz avgqu-sz await svctm  %util
    sda               0.00   0.00  10.00 20.00  5120   10240   512.00    0.05   2.00  0.50   1.50

  • Verification: The %util column should ideally be below 80%. High utilization or consistent high values in the %util column indicate potential disk I/O bottlenecks. Ensure your storage can meet the required IOPS to handle the demands of masking operations efficiently.

Kernel Parameters (Linux)​

  • Purpose: Confirm that the system kernel parameters are set as per the recommendations.

  • Commands:

    sysctl -a | grep -E 'shmmax|sem'

  • Expected Output:

    kernel.shmmax = 17179869184
    kernel.sem = 250 32000 100 128

  • Verification: Ensure kernel.shmmax is set to at least half of the physical memory and kernel.sem matches the recommended settings.

User Limits (Linux)​

  • Purpose: Ensure that the Oracle user has appropriate resource limits.

  • Commands:

    ulimit -a

  • Expected Output:

    open files                      (-n) 65536
    max user processes              (-u) 16384

  • Verification: Confirm that nofile and nproc values meet or exceed the recommended settings.

2.2 Validate Oracle Initialization Parameters​

Memory Settings​

  • Purpose: Verify that the database memory settings are configured correctly to handle large operations.

  • Commands:

    SHOW PARAMETER MEMORY_TARGET;
    SHOW PARAMETER MEMORY_MAX_TARGET;

  • Expected Output:

    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- -------
    memory_max_target                    big integer 48G
    memory_target                        big integer 48G

  • Verification: Ensure that MEMORY_TARGET and MEMORY_MAX_TARGET are set to appropriate values (e.g., 48 GB on a system with 64 GB RAM).

Process and Session Parameters​

  • Purpose: Ensure that the database can handle the expected number of sessions and processes.

  • Commands:

    SHOW PARAMETER PROCESSES;
    SHOW PARAMETER SESSIONS;

  • Expected Output:

    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- -------
    processes                            integer     1000
    sessions                             integer     1105

  • Verification: Confirm that PROCESSES and SESSIONS are set as per the calculations.

Optimizer Mode​

  • Purpose: Ensure that the optimizer is configured for efficient execution of bulk operations.

  • Commands:

    SHOW PARAMETER OPTIMIZER_MODE;

  • Expected Output:

    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- -------
    optimizer_mode                       string      ALL_ROWS

  • Verification: The VALUE should be ALL_ROWS.

Redo Log Configuration​

  • Purpose: Confirm that redo logs are configured to prevent performance issues during heavy DML operations.

  • Commands:

    SELECT GROUP#, BYTES/1024/1024 AS SIZE_MB, STATUS FROM V$LOG;

  • Expected Output:

     GROUP#   SIZE_MB   STATUS
    -------   -------   ------------
         1     2048     ACTIVE
         2     2048     INACTIVE
         3     2048     INACTIVE

  • Verification: Ensure that there are multiple groups and that each log file is appropriately sized (e.g., 2 GB).

Undo Tablespace​

  • Purpose: Ensure that the undo tablespace is properly configured to handle transactions without unnecessary overhead.

  • Commands:

    SHOW PARAMETER UNDO_MANAGEMENT;
    SHOW PARAMETER UNDO_RETENTION;

  • Expected Output:

    NAME                  TYPE        VALUE
    --------------------- ----------- -------
    undo_management       string      AUTO
    undo_retention        integer     300

  • Verification: UNDO_MANAGEMENT should be AUTO, and UNDO_RETENTION should be set as per the performance considerations (e.g., 300 seconds).

2.3 Assess Database Performance​

Generate AWR Reports​

  • Purpose: Identify performance bottlenecks.

  • Commands:

    @$ORACLE_HOME/rdbms/admin/awrrpt.sql

  • Verification: Review the report for high wait events, top SQL queries consuming resources, and recommendations.

Review ADDM Findings​

  • Purpose: Utilize Oracle's Automatic Database Diagnostic Monitor for performance insights.

  • Commands:

    • Access ADDM reports via Oracle Enterprise Manager or use the following:

      SELECT DBMS_ADVISOR.get_task_report('ADDM_TASK', 'TEXT', 'ALL') FROM DUAL;

  • Verification: Look for identified issues and recommended actions.

2.4 Check Session and Query Performance​

Identify Active Sessions​

  • Purpose: Find sessions currently executing to determine if any are causing performance issues.

  • Commands:

    SELECT SID, SERIAL#, STATUS, USERNAME, OSUSER, MACHINE
    FROM V$SESSION
    WHERE STATUS = 'ACTIVE';

  • Expected Output:

     SID   SERIAL#   STATUS   USERNAME   OSUSER   MACHINE
    ----   -------   ------   --------   ------   -------
     123     4567    ACTIVE   APP_USER   oracle   dbserver1
     124     8910    ACTIVE   APP_USER   oracle   dbserver1

  • Verification: Check for an unusually high number of active sessions or any sessions that have been active for an extended period.

Monitor Long-Running Queries​

  • Purpose: Identify queries consuming excessive resources.

  • Commands:

    SELECT SID, SQL_ID, ELAPSED_TIME/1000000 AS ELAPSED_SECONDS
    FROM V$SQL_MONITOR
    ORDER BY ELAPSED_TIME DESC;


  • Expected Output:

     SID   SQL_ID        ELAPSED_SECONDS
    ----   ------------- ---------------
     123   abcdefghijklm   3600
     124   nopqrstuvwxyz   1800

  • Verification: Investigate queries with high ELAPSED_SECONDS values.

Review Execution Plans​

  • Purpose: Determine if inefficient execution plans are causing performance issues.

  • Commands:

    SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('<SQL_ID>'));

  • Verification: Look for full table scans or other costly operations that could be optimized.

2.5 Evaluate Storage and Space Usage​

Check Tablespace Utilization​

  • Purpose: Ensure that tablespaces have sufficient free space.

  • Commands:

    SELECT
      TABLESPACE_NAME,
      ROUND(SUM(BYTES)/1024/1024,2) AS TOTAL_MB,
      ROUND(SUM(FREE_SPACE)/1024/1024,2) AS FREE_MB
    FROM
      DBA_TABLESPACE_USAGE_METRICS
    GROUP BY
      TABLESPACE_NAME;

  • Expected Output:

    TABLESPACE_NAME   TOTAL_MB   FREE_MB
    ---------------   --------   -------
    USERS              20480      10240
    TEMP               10240       5120

  • Verification: Confirm that FREE_MB is sufficient for ongoing operations.

2.6 Review Statistics and Indexes​

Ensure Statistics are Up-to-Date​

  • Purpose: Outdated statistics can lead to suboptimal execution plans.

  • Commands:

    EXEC DBMS_STATS.GATHER_DATABASE_STATS(ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE);

  • Verification: Confirm that statistics have been recently gathered.

Analyze Index Usage​

  • Purpose: Identify unused or inefficient indexes.

  • Commands:

    SELECT
      OBJECT_NAME,
      INDEX_NAME,
      MONITORING,
      USED
    FROM
      V$OBJECT_USAGE
    WHERE
      USED = 'NO';

  • Expected Output:

    OBJECT_NAME   INDEX_NAME    MONITORING   USED
    -----------   ----------    ----------   ----
    EMPLOYEES     EMP_IDX       YES          NO

  • Verification: Consider dropping or rebuilding unused indexes.

2.7 Network Diagnostics​

Check Listener Status​

  • Purpose: Ensure that the database listener is operational.

  • Commands:

    lsnrctl status

  • Expected Output:

    STATUS of the LISTENER
    ------------------------
    Alias                     LISTENER
    Version                   TNSLSNR for Linux: Version 19.0.0.0.0
    Start Date                01-JAN-2024 08:00:00
    Uptime                    0 days 4 hr. 30 min. 0 sec
    ...

    Listening Endpoints Summary...
      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbserver1)(PORT=1521)))

  • Verification: Confirm that the listener is running and listening on the correct ports.

Test Connectivity​

  • Purpose: Verify that clients can connect to the database.

  • Commands:

    tnsping <service_name>

  • Expected Output:

    TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 01-JAN-2024 12:30:00

    Used parameter files:
    /oracle/product/19.0.0/dbhome_1/network/admin/sqlnet.ora

    Attempting to contact (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbserver1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL)))
    OK (20 msec)

  • Verification: Ensure that the connection attempt is successful (OK).

2.8 Verify Security and Patch Levels​

Check Applied Patches​

  • Purpose: Ensure that the database is up-to-date with patches.

  • Commands:

    $ORACLE_HOME/OPatch/opatch lsinventory

  • Expected Output:

    Oracle Interim Patch Installer version 19.0.0.0.0
    ...

    List of Patch IDs:

    12345678
    87654321
    ...

    OPatch succeeded.

  • Verification: Confirm that the latest patches are applied as per Oracle's recommendations.

2.9 Application Interaction​

Confirm Connection Management​

  • Purpose: Ensure the application uses connection pooling to optimize resource usage.

  • Verification:

    • Review application configuration settings for connection pooling parameters.

  • Monitor Application Sessions

    SELECT SID, SERIAL#, USERNAME, STATUS
    FROM V$SESSION
    WHERE USERNAME = 'APP_USER';
    • Expected Output: A reasonable number of sessions corresponding to the connection pool size.

2.10 System Resource Monitoring​

Monitor CPU and Memory Usage​

  • Purpose: Detect system-level resource bottlenecks.

  • Commands:

    • Linux:

      top

    • Verification: Check for high CPU or memory usage by Oracle processes.

Check Swap Usage​

  • Purpose: Ensure minimal swap usage.
  • Commands: 
    swapon -s
  • Verification: Confirm minimal swap usage.