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.
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 2 core per 1 TB of data.
- Example:
- For a 1 TB database: 4 cores.
- For a 5 TB database: 12 cores.
Memory (RAM)
- Base Requirement: Start with 16 GB, add 16 GB per 1 TB of data.
- Example:
- For a 1 TB database: 16 GB of RAM.
- For a 5 TB database: 80 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):
Parameter Recommended Setting kernel.shmmax
Half of physical memory (e.g., 16 GB
for a32 GB
system)kernel.sem
250 32000 100 128
-
User Limits (Linux):
Limit Recommended 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 a64 GB
system)MEMORY_MAX_TARGET
: Equal to or greater thanMEMORY_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;
- Create Redo Log Groups:
- 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.
- Locally Managed Tablespaces (LMT): Use uniform extent sizes to improve performance.
-
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
andlistener.ora
for performance. - Adjust Session Data Unit (SDU) size if necessary.
- Optimize
-
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
- Linux:
- 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 andkernel.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
andnproc
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
andMEMORY_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
andSESSIONS
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 beALL_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 beAUTO
, andUNDO_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.