Common Issues and Solutions

Common Issues and Solutions

This guide provides solutions to frequently encountered Oracle database issues, along with diagnostic steps and preventive measures.

Connection Issues

ORA-12541: TNS no listener

Problem: The database listener is not running or not reachable.

Solution:

# Check listener status
lsnrctl status

# Start the listener
lsnrctl start

# Check listener configuration
cat $ORACLE_HOME/network/admin/listener.ora

# Verify TNS configuration
tnsping <database_name>

Prevention:

• Configure listener to start automatically
• Monitor listener status with alerting
• Keep backup of network configuration files

ORA-12154: TNS could not resolve the connect identifier

Problem: The connection string cannot be resolved.

Solution:

# Check tnsnames.ora file
cat $ORACLE_HOME/network/admin/tnsnames.ora

# Verify ORACLE_HOME and TNS_ADMIN
echo $ORACLE_HOME
echo $TNS_ADMIN

# Test connection
sqlplus username/password@tns_alias

# Use Easy Connect if tnsnames.ora is problematic
sqlplus username/password@//hostname:port/service_name

ORA-01017: Invalid username/password

Problem: Authentication failure.

Solution:

-- Check if account is locked
SELECT username, account_status, lock_date
FROM dba_users
WHERE username = 'YOUR_USER';

-- Unlock account
ALTER USER your_user ACCOUNT UNLOCK;

-- Reset password
ALTER USER your_user IDENTIFIED BY new_password;

-- Check password case sensitivity
SHOW PARAMETER sec_case_sensitive_logon;

Performance Issues

Slow Query Performance

Problem: Queries running slower than expected.

Diagnostic Steps:

-- Get execution plan
EXPLAIN PLAN FOR
SELECT * FROM your_table WHERE conditions;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- Check for missing indexes
SELECT
    'CREATE INDEX idx_' || table_name || '_' || column_name ||
    ' ON ' || table_name || '(' || column_name || ');' as index_ddl
FROM (
    SELECT DISTINCT
        s.sql_id,
        p.object_owner as table_owner,
        p.object_name as table_name,
        p.filter_predicates
    FROM v$sql s
    JOIN v$sql_plan p ON s.sql_id = p.sql_id
    WHERE p.operation = 'TABLE ACCESS'
        AND p.options = 'FULL'
        AND s.executions > 100
);

-- Analyze table statistics
BEGIN
    DBMS_STATS.GATHER_TABLE_STATS(
        ownname => 'SCHEMA_NAME',
        tabname => 'TABLE_NAME',
        cascade => TRUE
    );
END;
/

Solutions:

1. Add appropriate indexes
2. Update table and index statistics
3. Rewrite query to use existing indexes
4. Consider partitioning for large tables
5. Use hints if necessary

High CPU Usage

Problem: Database consuming excessive CPU resources.

Diagnostic Query:

-- Find top CPU consuming sessions
SELECT
    s.sid,
    s.serial#,
    s.username,
    s.program,
    ss.value/100 as cpu_seconds,
    s.sql_id,
    sq.sql_text
FROM v$session s
JOIN v$sesstat ss ON s.sid = ss.sid
JOIN v$statname sn ON ss.statistic# = sn.statistic#
LEFT JOIN v$sql sq ON s.sql_id = sq.sql_id
WHERE sn.name = 'CPU used by this session'
    AND s.status = 'ACTIVE'
    AND s.username IS NOT NULL
ORDER BY ss.value DESC
FETCH FIRST 10 ROWS ONLY;

Solutions:

• Optimize high CPU queries
• Add resource manager rules
• Consider parallel query settings
• Review and tune PGA/SGA settings

Space Issues

ORA-01653: Unable to extend table

Problem: Tablespace is full.

Solution:

-- Check tablespace usage
SELECT
    tablespace_name,
    ROUND(used_space * 8192 / 1024 / 1024, 2) AS used_mb,
    ROUND(tablespace_size * 8192 / 1024 / 1024, 2) AS total_mb,
    ROUND(used_percent, 2) AS used_percent
FROM dba_tablespace_usage_metrics
ORDER BY used_percent DESC;

-- Add datafile
ALTER TABLESPACE users
ADD DATAFILE '/path/to/users02.dbf'
SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE 10G;

-- Resize existing datafile
ALTER DATABASE DATAFILE '/path/to/users01.dbf'
RESIZE 5G;

-- Enable autoextend
ALTER DATABASE DATAFILE '/path/to/users01.dbf'
AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;

Archive Log Space Full

Problem: Archive destination is full.

Solution:

-- Check archive usage
ARCHIVE LOG LIST;

-- Check flash recovery area usage
SELECT * FROM v$recovery_file_dest;
SELECT * FROM v$flash_recovery_area_usage;

-- Delete old archives (after backup!)
RMAN> DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-7';

-- Increase recovery area size
ALTER SYSTEM SET db_recovery_file_dest_size = 50G SCOPE=BOTH;

Lock and Blocking Issues

Session Blocking

Problem: Sessions waiting on locks.

Diagnostic Query:

-- Find blocking sessions
SELECT
    s1.sid || ',' || s1.serial# AS blocker,
    s1.username AS blocker_user,
    s2.sid || ',' || s2.serial# AS blocked,
    s2.username AS blocked_user,
    s2.seconds_in_wait,
    s2.event,
    sq1.sql_text AS blocker_sql,
    sq2.sql_text AS blocked_sql
FROM v$session s1
JOIN v$session s2 ON s1.sid = s2.blocking_session
LEFT JOIN v$sql sq1 ON s1.sql_id = sq1.sql_id
LEFT JOIN v$sql sq2 ON s2.sql_id = sq2.sql_id
WHERE s2.blocking_session IS NOT NULL;

-- Kill blocking session (use carefully!)
ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

Deadlock Detection

Problem: ORA-00060: Deadlock detected.

Solution:

-- Check alert log for deadlock trace file
SELECT value FROM v$diag_info WHERE name = 'Diag Trace';

-- Review trace file for deadlock graph
-- Look for "Deadlock graph" section

-- Prevent deadlocks:
-- 1. Always access tables in the same order
-- 2. Keep transactions short
-- 3. Use SELECT FOR UPDATE NOWAIT
SELECT * FROM table_name
WHERE condition
FOR UPDATE NOWAIT;

Memory Issues

ORA-04031: Unable to allocate shared memory

Problem: Shared pool fragmentation or sizing issue.

Solution:

-- Check shared pool usage
SELECT
    pool,
    name,
    ROUND(bytes/1024/1024, 2) as size_mb
FROM v$sgastat
WHERE pool = 'shared pool'
ORDER BY bytes DESC
FETCH FIRST 20 ROWS ONLY;

-- Flush shared pool (temporary fix)
ALTER SYSTEM FLUSH SHARED_POOL;

-- Increase shared pool size
ALTER SYSTEM SET shared_pool_size = 500M SCOPE=BOTH;

-- Pin frequently used objects
BEGIN
    DBMS_SHARED_POOL.KEEP('SCHEMA.PACKAGE_NAME', 'P');
END;
/

PGA Memory Issues

Problem: ORA-04030: Out of process memory.

Solution:

-- Check PGA usage
SELECT
    name,
    ROUND(value/1024/1024, 2) as value_mb
FROM v$pgastat
WHERE name IN (
    'aggregate PGA target parameter',
    'total PGA allocated',
    'total PGA used'
);

-- Increase PGA target
ALTER SYSTEM SET pga_aggregate_target = 2G SCOPE=BOTH;

-- Monitor PGA usage by session
SELECT
    sid,
    username,
    program,
    ROUND(pga_used_mem/1024/1024, 2) as pga_used_mb,
    ROUND(pga_alloc_mem/1024/1024, 2) as pga_alloc_mb
FROM v$session s
JOIN v$process p ON s.paddr = p.addr
WHERE s.username IS NOT NULL
ORDER BY pga_alloc_mem DESC;

Corruption Issues

ORA-01578: Oracle data block corrupted

Problem: Data block corruption detected.

Solution:

-- Identify corrupted blocks
SELECT * FROM v$database_block_corruption;

-- Run RMAN validation
RMAN> VALIDATE DATABASE;
RMAN> VALIDATE DATAFILE 5;

-- Use RMAN to recover blocks
RMAN> RECOVER CORRUPTION LIST;

-- Alternative: Use DBMS_REPAIR
BEGIN
    DBMS_REPAIR.CHECK_OBJECT(
        schema_name => 'SCHEMA',
        object_name => 'TABLE_NAME',
        corrupt_count => :cc
    );
END;
/

Startup/Shutdown Issues

Database Won’t Start

Common Issues and Solutions:

-- Check alert log
tail -f $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/trace/alert_$ORACLE_SID.log

-- Common startup issues:

-- 1. Control file issues
STARTUP NOMOUNT;
-- Recreate control file from trace

-- 2. Missing datafile
STARTUP MOUNT;
ALTER DATABASE DATAFILE '/path/to/file.dbf' OFFLINE DROP;
ALTER DATABASE OPEN;

-- 3. Redo log issues
STARTUP MOUNT;
ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 1;

-- 4. Parameter file issues
CREATE PFILE='/tmp/init.ora' FROM SPFILE;
-- Edit the pfile to fix issues
STARTUP PFILE='/tmp/init.ora';
CREATE SPFILE FROM PFILE='/tmp/init.ora';

Best Practices for Issue Prevention

Proactive Monitoring

-- Create monitoring job
BEGIN
    DBMS_SCHEDULER.CREATE_JOB(
        job_name => 'DAILY_HEALTH_CHECK',
        job_type => 'PLSQL_BLOCK',
        job_action => 'BEGIN check_database_health; END;',
        start_date => SYSTIMESTAMP,
        repeat_interval => 'FREQ=DAILY; BYHOUR=6',
        enabled => TRUE
    );
END;
/

Regular Maintenance Tasks

1. Statistics Gathering

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

2. Index Maintenance

   -- Rebuild fragmented indexes
   ALTER INDEX index_name REBUILD ONLINE;

3. Backup Validation

   RMAN> VALIDATE BACKUPSET ALL;

4. Space Monitoring

   -- Set up tablespace alerts
   EXEC DBMS_SERVER_ALERT.SET_THRESHOLD(
       DBMS_SERVER_ALERT.TABLESPACE_PCT_FULL,
       DBMS_SERVER_ALERT.OPERATOR_GE, 85, 95, 1, 1, NULL,
       DBMS_SERVER_ALERT.OBJECT_TYPE_TABLESPACE, 'USERS');

Emergency Response Procedures

Critical Issue Checklist

1. Assess Impact

   • Number of users affected
   • Business processes impacted
   • Data integrity concerns

2. Gather Information

   • Alert log entries
   • Recent changes
   • Error messages
   • System resource usage

3. Immediate Actions

   • Notify stakeholders
   • Document findings
   • Implement temporary workarounds

4. Resolution Steps

   • Apply fix in test environment first
   • Get approval for production changes
   • Implement with rollback plan
   • Verify resolution

5. Post-Incident

   • Root cause analysis
   • Update documentation
   • Implement preventive measures
   • Share lessons learned