Top 50 Critical ORA-/RMAN- Errors

 Oracle 19c Database

Top 50 Critical ORA-/RMAN- Errors

Step-by-step diagnosis and resolution with command-line examples and output

12 October 2026

Outputs are representative samples (hostnames, SIDs, sizes illustrative). Test in non-production first; take a backup before any recovery or parameter change.

Index of 50 Errors

#	Error	Description	Section
1	ORA-12541	TNS:no listener	Connectivity
2	ORA-12154	TNS:could not resolve the connect identifier specified	Connectivity
3	ORA-12514	TNS:listener does not currently know of service requested	Connectivity
4	ORA-12170	TNS:Connect timeout occurred	Connectivity
5	ORA-01017 / ORA-28000	Invalid username/password; account is locked	Authentication
6	ORA-01031	insufficient privileges	Authentication
7	ORA-28001	the password has expired	Authentication
8	ORA-01950 / ORA-01536	No privileges on tablespace / space quota exceeded	Authentication
9	ORA-00942	table or view does not exist	Authentication
10	ORA-04031	Unable to allocate bytes of shared memory	Instance
11	ORA-01034	ORACLE not available	Instance
12	ORA-27101	shared memory realm does not exist	Instance
13	ORA-01033	ORACLE initialization or shutdown in progress	Instance
14	ORA-01109	database not open	Instance
15	ORA-00205	error in identifying control file	Instance
16	ORA-00313 / ORA-00312	open failed for members of log group	Instance
17	ORA-00845	MEMORY_TARGET not supported on this system	Instance
18	ORA-00020	maximum number of processes exceeded	Instance
19	ORA-00018	maximum number of sessions exceeded	Instance
20	ORA-04030	out of process memory when trying to allocate N bytes	Instance
21	ORA-01653	Unable to extend table by N in tablespace	Storage
22	ORA-00257	Archiver error. Connect internal only, until freed	Storage
23	ORA-01652	unable to extend temp segment by N in tablespace TEMP	Storage
24	ORA-01654	unable to extend index by N in tablespace	Storage
25	ORA-01691	unable to extend lob segment by N in tablespace	Storage
26	ORA-30036	unable to extend segment by N in undo tablespace	Storage
27	ORA-01157 / ORA-01110	cannot identify/lock data file	Storage
28	ORA-01113	file N needs media recovery	Storage
29	ORA-00376	file N cannot be read at this time	Storage
30	ORA-00600	internal error code, arguments: [...]	Internal Errors
31	ORA-07445	exception encountered: core dump [function][signal]	Internal Errors
32	ORA-00604	error occurred at recursive SQL level N	Internal Errors
33	ORA-01578	ORACLE data block corrupted	Internal Errors
34	ORA-01555	Snapshot too old: rollback segment too small	Locking
35	ORA-00054 / ORA-00060	Resource busy (NOWAIT) / Deadlock detected	Locking
36	ORA-01013	user requested cancel of current operation	Locking
37	ORA-01000	maximum open cursors exceeded	Locking
38	ORA-04021	timeout occurred while waiting to lock object	Locking
39	ORA-04068	existing state of packages has been discarded	Locking
40	ORA-04063	view/package/procedure has errors	Locking
41	ORA-00001	unique constraint violated	Locking
42	ORA-02291	integrity constraint violated - parent key not found	Locking
43	ORA-19505	failed to identify file (RMAN backup piece missing)	Backup
44	ORA-00308	cannot open archived log	Backup
45	RMAN-06059	expected archived log not found, loss of archived log compromises recoverability	Backup
46	ORA-01152	file N was not restored from a sufficiently old backup	Backup
47	ORA-39002 / ORA-39070	invalid operation / unable to open the log file (Data Pump directory)	Backup
48	ORA-39126	worker unexpected fatal error in KUPW$WORKER	Backup
49	ORA-31684 / ORA-39151	object already exists / table exists, skipped	Backup
50	ORA-39142	incompatible version number in dump file	Backup

Universal first-response checklist

•      Read the alert log: $ORACLE_BASE/diag/rdbms/<db>/<SID>/trace/alert_<SID>.log

•      adrci> show alert -tail 50 ; show incident

•      SELECT status, database_status FROM v$instance;

•      OS: df -h, free -m, top, ulimit -a

•      Capture the exact error text/time before changing anything; read the whole error stack, not only the first line

$ sqlplus / as sysdba

SQL> SELECT instance_name, status, database_status FROM v$instance;

INSTANCE_NAME    STATUS   DATABASE_STATUS

---------------- -------- ---------------

ORCL19C          OPEN     ACTIVE

Section 1: Connectivity & Listener

1. ORA-12541: TNS:no listener

Symptom

Client/application cannot connect. sqlplus returns ORA-12541; DB itself may be up.

Step 1 – Confirm the error

$ sqlplus system/***@ORCL19C

 

ERROR:

ORA-12541: TNS:no listener

Step 2 – Check listener status

$ lsnrctl status

 

TNS-12541: TNS:no listener

 TNS-12560: TNS:protocol adapter error

  TNS-00511: No listener

Step 3 – Verify DB instance is up and the listener process

$ ps -ef | grep -E "pmon|tnslsnr" | grep -v grep

oracle  2291     1  0 09:12 ?  00:00:03 ora_pmon_ORCL19C

 

(no tnslsnr process -> listener is down)

Step 4 – Check listener.ora host/port

$ cat $ORACLE_HOME/network/admin/listener.ora

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = dbsrv01)(PORT = 1521))))

Step 5 – Fix: start the listener

$ lsnrctl start

 

Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbsrv01)(PORT=1521)))

Services Summary...

Service "ORCL19C" has 1 instance(s).

The command completed successfully

Step 6 – Force dynamic registration if the service is missing

SQL> ALTER SYSTEM REGISTER;

System altered.

 

$ lsnrctl status | grep -i service

Service "ORCL19C" has 1 instance(s).

  Instance "ORCL19C", status READY, has 1 handler(s) for this service...

Step 7 – Verify

$ tnsping ORCL19C

OK (10 msec)

 

$ sqlplus system/***@ORCL19C

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0

Root cause

Listener stopped or crashed, wrong host/port in tnsnames.ora/listener.ora, or firewall blocking 1521.

2. ORA-12154: TNS:could not resolve the connect identifier specified

Symptom

Client cannot find the alias in tnsnames.ora (wrong TNS_ADMIN, typo, missing entry).

Step 1 – Diagnose

$ sqlplus scott/***@PROD

ORA-12154: TNS:could not resolve the connect identifier specified

 

$ tnsping PROD

TNS-03505: Failed to resolve name

 

$ echo $TNS_ADMIN

/u01/app/oracle/product/19c/dbhome_1/network/admin

$ grep -i "^PROD" $TNS_ADMIN/tnsnames.ora

(no output -> alias missing)

Step 2 – Fix

$ cat >> $TNS_ADMIN/tnsnames.ora <<'X'

PROD =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = dbsrv01)(PORT = 1521))

    (CONNECT_DATA = (SERVICE_NAME = ORCL19C)))

X

 

-- or bypass tnsnames with EZConnect:

$ sqlplus scott@//dbsrv01:1521/ORCL19C

Step 3 – Verify

$ tnsping PROD

OK (10 msec)

$ sqlplus scott/***@PROD

Connected.

Root cause

Alias missing/typo in tnsnames.ora, wrong TNS_ADMIN, or invalid syntax (unbalanced parentheses).

3. ORA-12514: TNS:listener does not currently know of service requested

Symptom

Listener is up but the service is not registered with it.

Step 1 – Diagnose

$ sqlplus scott/***@dbsrv01:1521/ORCL19C

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

 

$ lsnrctl services

The listener supports no services

 

SQL> SHOW PARAMETER service_names

service_names   string   ORCL19C

SQL> SHOW PARAMETER local_listener

local_listener  string   (ADDRESS=(PROTOCOL=TCP)(HOST=dbsrv02)(PORT=1521))   <-- wrong host

Step 2 – Fix

SQL> ALTER SYSTEM SET local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=dbsrv01)(PORT=1521))' SCOPE=BOTH;

System altered.

SQL> ALTER SYSTEM REGISTER;

System altered.

Step 3 – Verify

$ lsnrctl status | grep -A1 ORCL19C

Service "ORCL19C" has 1 instance(s).

  Instance "ORCL19C", status READY, has 1 handler(s) for this service...

Root cause

local_listener/service_names mismatch or the instance started before the listener; dynamic registration did not occur.

4. ORA-12170: TNS:Connect timeout occurred

Symptom

Client hangs, then times out. Usually network/firewall.

Step 1 – Diagnose

$ tnsping ORCL19C

TNS-12535: TNS:operation timed out

 

$ nc -zv dbsrv01 1521

nc: connect to dbsrv01 port 1521 (tcp) failed: Connection timed out

 

$ sudo firewall-cmd --list-ports

(1521/tcp not listed)

Step 2 – Fix

$ sudo firewall-cmd --permanent --add-port=1521/tcp

success

$ sudo firewall-cmd --reload

success

 

-- optional, protect listener from hung clients (sqlnet.ora on server)

SQLNET.INBOUND_CONNECT_TIMEOUT = 60

Step 3 – Verify

$ nc -zv dbsrv01 1521

Connection to dbsrv01 1521 port [tcp/*] succeeded!

$ tnsping ORCL19C

OK (10 msec)

Root cause

Firewall/security list blocking port 1521, wrong host/IP, or listener bound to wrong interface.

Section 2: Authentication, Privileges & Access

5. ORA-01017 / ORA-28000: Invalid username/password; account is locked

Symptom

Login fails after password change, or after repeated failed attempts (FAILED_LOGIN_ATTEMPTS limit).

Step 1 – Reproduce

SQL> conn scott/tiger

ERROR:

ORA-28000: the account is locked

Step 2 – Check account status

SQL> SELECT username, account_status, lock_date, expiry_date, profile

  2  FROM dba_users WHERE username='SCOTT';

 

USERNAME  ACCOUNT_STATUS  LOCK_DATE   EXPIRY_DATE  PROFILE

--------- --------------- ----------- ------------ -------

SCOTT     LOCKED(TIMED)   12-OCT-26   10-JAN-27    DEFAULT

Step 3 – Check the profile limits

SQL> SELECT resource_name, limit FROM dba_profiles

  2  WHERE profile='DEFAULT' AND resource_name IN

  3  ('FAILED_LOGIN_ATTEMPTS','PASSWORD_LOCK_TIME','PASSWORD_LIFE_TIME');

 

RESOURCE_NAME          LIMIT

---------------------- ---------

FAILED_LOGIN_ATTEMPTS  10

PASSWORD_LIFE_TIME     180

PASSWORD_LOCK_TIME     1

Step 4 – Find the source of failed logins (audit)

SQL> SELECT os_username, userhost, returncode, COUNT(*)

  2  FROM dba_audit_session

  3  WHERE username='SCOTT' AND returncode<>0

  4  GROUP BY os_username,userhost,returncode;

 

OS_USERNAME USERHOST   RETURNCODE COUNT(*)

----------- ---------- ---------- --------

batchuser   appsrv02   1017       10

Step 5 – Fix: unlock and reset password

SQL> ALTER USER scott ACCOUNT UNLOCK;

User altered.

 

SQL> ALTER USER scott IDENTIFIED BY "NewStr0ng#Pwd";

User altered.

Step 6 – Verify

SQL> conn scott/"NewStr0ng#Pwd"

Connected.

 

SQL> SELECT account_status FROM dba_users WHERE username='SCOTT';

OPEN

Root cause

A stale password stored in an application/cron job kept hitting the DB until FAILED_LOGIN_ATTEMPTS was reached. Update the credential at the source (appsrv02) or the account re-locks.

6. ORA-01031: insufficient privileges

Symptom

User lacks a system/object privilege, or OS group is wrong for "/ as sysdba".

Step 1 – Diagnose

SQL> CREATE TABLE t1 (id NUMBER);

ORA-01031: insufficient privileges

 

SQL> SELECT * FROM session_privs WHERE privilege LIKE 'CREATE TABLE';

no rows selected

 

$ sqlplus / as sysdba

ORA-01031: insufficient privileges

$ id

uid=1001(oracle) gid=1001(oinstall) groups=1001(oinstall)    <-- dba group missing

Step 2 – Fix

SQL> GRANT CREATE TABLE TO scott;

Grant succeeded.

 

-- for / as sysdba failure (as root):

# usermod -a -G dba oracle

# su - oracle -c id

uid=1001(oracle) groups=1001(oinstall),1002(dba)

Step 3 – Verify

SQL> conn scott/***

SQL> CREATE TABLE t1 (id NUMBER);

Table created.

Root cause

Missing grant (note: privileges via roles do not work inside definer-rights PL/SQL) or oracle OS user not in the dba group.

7. ORA-28001: the password has expired

Symptom

Application login fails after PASSWORD_LIFE_TIME elapses.

Step 1 – Diagnose

SQL> SELECT username, account_status, expiry_date, profile FROM dba_users WHERE username='APPUSER';

APPUSER   EXPIRED   01-OCT-26   DEFAULT

Step 2 – Fix

SQL> ALTER USER appuser IDENTIFIED BY "N3wPwd#2026";

User altered.

-- for service accounts, stop expiry via a dedicated profile:

SQL> CREATE PROFILE app_prof LIMIT PASSWORD_LIFE_TIME UNLIMITED FAILED_LOGIN_ATTEMPTS 10;

SQL> ALTER USER appuser PROFILE app_prof;

Step 3 – Verify

SQL> SELECT account_status FROM dba_users WHERE username='APPUSER';

OPEN

Root cause

DEFAULT profile PASSWORD_LIFE_TIME reached for a service account.

8. ORA-01950 / ORA-01536: No privileges on tablespace / space quota exceeded

Symptom

INSERT/CREATE fails although the tablespace has free space.

Step 1 – Diagnose

SQL> INSERT INTO t1 SELECT * FROM t1_stage;

ORA-01536: space quota exceeded for tablespace 'USERS'

 

SQL> SELECT tablespace_name, bytes/1024/1024 used_mb, max_bytes FROM dba_ts_quotas WHERE username='SCOTT';

USERS   100   104857600

Step 2 – Fix

SQL> ALTER USER scott QUOTA UNLIMITED ON users;

User altered.

Step 3 – Verify

SQL> SELECT tablespace_name, max_bytes FROM dba_ts_quotas WHERE username='SCOTT';

USERS   -1

SQL> INSERT INTO t1 SELECT * FROM t1_stage;

5000 rows created.

Root cause

User quota on tablespace exhausted (or no quota granted -> ORA-01950).

9. ORA-00942: table or view does not exist

Symptom

Object missing, in another schema, or no SELECT grant.

Step 1 – Diagnose

SQL> SELECT * FROM hr.employees;

ORA-00942: table or view does not exist

 

SQL> SELECT owner, object_name, object_type FROM dba_objects WHERE object_name='EMPLOYEES';

HR   EMPLOYEES   TABLE

 

SQL> SELECT grantee, privilege FROM dba_tab_privs WHERE table_name='EMPLOYEES' AND owner='HR';

no rows selected

Step 2 – Fix

SQL> GRANT SELECT ON hr.employees TO scott;

Grant succeeded.

SQL> CREATE SYNONYM scott.employees FOR hr.employees;

Step 3 – Verify

SQL> conn scott/***

SQL> SELECT COUNT(*) FROM employees;

107

Root cause

Missing object privilege/synonym, wrong schema, or case-sensitive quoted name.

Section 3: Instance, Startup & Memory

10. ORA-04031: Unable to allocate bytes of shared memory

Symptom

Random failures, slow parsing; "ORA-04031: unable to allocate 4160 bytes of shared memory ("shared pool","unknown object","sga heap(1,0)","KGLH0")".

Step 1 – Check current memory configuration

SQL> SHOW PARAMETER sga

sga_max_size            big integer  4G

sga_target              big integer  4G

 

SQL> SHOW PARAMETER shared_pool_size

shared_pool_size        big integer  0

Step 2 – Check shared pool free memory and resize activity

SQL> SELECT pool, name, ROUND(bytes/1024/1024) mb FROM v$sgastat

  2  WHERE pool='shared pool' AND name='free memory';

shared pool  free memory   12

 

SQL> SELECT component, current_size/1024/1024 mb, last_oper_type

  2  FROM v$sga_dynamic_components WHERE component='shared pool';

shared pool   1024   GROW

Step 3 – Find literal-SQL (no bind variables) flooding the pool

SQL> SELECT force_matching_signature, COUNT(*) cnt

  2  FROM v$sql WHERE force_matching_signature<>0

  3  GROUP BY force_matching_signature HAVING COUNT(*)>100

  4  ORDER BY cnt DESC FETCH FIRST 3 ROWS ONLY;

 

FORCE_MATCHING_SIGNATURE        CNT

------------------------- ---------

     9238471123984711          8421

Step 4 – Immediate relief (flush) and sizing

SQL> ALTER SYSTEM FLUSH SHARED_POOL;

System altered.

 

SQL> ALTER SYSTEM SET sga_target=8G SCOPE=BOTH;       -- needs sga_max_size >= 8G

SQL> ALTER SYSTEM SET sga_max_size=8G SCOPE=SPFILE;   -- then restart

SQL> ALTER SYSTEM SET shared_pool_size=2G SCOPE=BOTH; -- floor value

Step 5 – Permanent fix: stop literal SQL

-- Short-term workaround (app cannot be changed immediately)

SQL> ALTER SYSTEM SET cursor_sharing=FORCE SCOPE=BOTH;

System altered.

-- Long-term: fix the application to use bind variables.

Step 6 – Verify

SQL> SELECT name, ROUND(bytes/1024/1024) mb FROM v$sgastat

  2  WHERE pool='shared pool' AND name='free memory';

free memory   410

Root cause

Shared pool fragmentation from unshared literal SQL plus undersized SGA.

11. ORA-01034: ORACLE not available

Symptom

Instance is down (or ORACLE_SID points to a non-running instance).

Step 1 – Diagnose

$ sqlplus / as sysdba

Connected to an idle instance.

 

$ ps -ef | grep pmon | grep -v grep

(none)

$ tail -5 $ORACLE_BASE/diag/rdbms/orcl19c/ORCL19C/trace/alert_ORCL19C.log

Step 2 – Fix

SQL> STARTUP

ORACLE instance started.

Total System Global Area 4294964000 bytes

Database mounted.

Database opened.

Step 3 – Verify

SQL> SELECT instance_name, status, database_status FROM v$instance;

ORCL19C   OPEN   ACTIVE

Root cause

Instance crashed/shut down or ORACLE_SID mismatch. Always read the alert log for the crash reason before restarting.

12. ORA-27101: shared memory realm does not exist

Symptom

sqlplus / as sysdba fails; usually wrong ORACLE_SID/ORACLE_HOME or instance down.

Step 1 – Diagnose

$ sqlplus / as sysdba

ORA-01034: ORACLE not available

ORA-27101: shared memory realm does not exist

Linux-x86_64 Error: 2: No such file or directory

 

$ echo $ORACLE_SID

orcl

$ ps -ef | grep pmon | grep -v grep

oracle 2291 1 0 09:12 ? 00:00:03 ora_pmon_ORCL19C    <-- SID is upper case

Step 2 – Fix

$ export ORACLE_SID=ORCL19C

$ export ORACLE_HOME=/u01/app/oracle/product/19c/dbhome_1

$ export PATH=$ORACLE_HOME/bin:$PATH

Step 3 – Verify

$ sqlplus / as sysdba

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0

Root cause

ORACLE_SID is case-sensitive on Linux; or instance is not running.

13. ORA-01033: ORACLE initialization or shutdown in progress

Symptom

Logins rejected while the DB is mounting/opening/closing.

Step 1 – Diagnose

SQL> SELECT status FROM v$instance;

MOUNTED

 

$ tail -f alert_ORCL19C.log

Beginning crash instance recovery ...

Step 2 – Fix

-- let crash recovery finish, then:

SQL> ALTER DATABASE OPEN;

Database altered.

Step 3 – Verify

SQL> SELECT status, open_mode FROM v$instance, v$database;

OPEN   READ WRITE

Root cause

Instance is in transition (startup/shutdown/instance recovery).

14. ORA-01109: database not open

Symptom

Statements fail because DB is only mounted/nomount.

Step 1 – Diagnose

SQL> SELECT status FROM v$instance;

MOUNTED

SQL> SELECT open_mode FROM v$database;

MOUNTED

Step 2 – Fix

SQL> ALTER DATABASE OPEN;

Database altered.

-- if it errors (ORA-01113/01157) fix the datafile first (see Storage section).

Step 3 – Verify

SQL> SELECT open_mode FROM v$database;

READ WRITE

Root cause

Database was started with STARTUP MOUNT or open failed.

15. ORA-00205: error in identifying control file

Symptom

Instance cannot mount; control file missing/corrupt.

Step 1 – Diagnose

SQL> STARTUP

ORA-00205: error in identifying control file, check alert log for more info

SQL> SHOW PARAMETER control_files

control_files  string  /u01/oradata/ORCL19C/control01.ctl, /u02/fra/ORCL19C/control02.ctl

 

$ ls -l /u01/oradata/ORCL19C/control01.ctl

ls: cannot access ...: No such file or directory

$ ls -l /u02/fra/ORCL19C/control02.ctl

-rw-r----- 1 oracle oinstall 18436096 Oct 12 08:00 control02.ctl

Step 2 – Fix

$ cp /u02/fra/ORCL19C/control02.ctl /u01/oradata/ORCL19C/control01.ctl

SQL> STARTUP

-- no good copy? restore from autobackup:

RMAN> STARTUP NOMOUNT;

RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP;

RMAN> ALTER DATABASE MOUNT;

RMAN> RECOVER DATABASE;

RMAN> ALTER DATABASE OPEN RESETLOGS;

Step 3 – Verify

SQL> SELECT name FROM v$controlfile;

/u01/oradata/ORCL19C/control01.ctl

/u02/fra/ORCL19C/control02.ctl

Root cause

Control file deleted/corrupted. Always multiplex control files and enable CONFIGURE CONTROLFILE AUTOBACKUP ON.

16. ORA-00313 / ORA-00312: open failed for members of log group

Symptom

Open/startup fails because an online redo log member is missing.

Step 1 – Diagnose

ORA-00313: open failed for members of log group 2 of thread 1

ORA-00312: online log 2 thread 1: '/u01/oradata/ORCL19C/redo02.log'

 

SQL> SELECT group#, status, member FROM v$logfile JOIN v$log USING (group#);

2  INACTIVE  /u01/oradata/ORCL19C/redo02.log

Step 2 – Fix

-- INACTIVE group: recreate it

SQL> ALTER DATABASE CLEAR LOGFILE GROUP 2;

Database altered.

-- CURRENT/ACTIVE group needs recovery instead (RECOVER DATABASE UNTIL CANCEL; OPEN RESETLOGS)

Step 3 – Verify

SQL> ALTER DATABASE OPEN;

SQL> SELECT group#, status FROM v$log;

1 CURRENT

2 INACTIVE

Root cause

Redo member deleted or on a lost mount. Multiplex redo groups on separate disks.

17. ORA-00845: MEMORY_TARGET not supported on this system

Symptom

Startup fails with AMM when /dev/shm is smaller than MEMORY_TARGET.

Step 1 – Diagnose

SQL> STARTUP

ORA-00845: MEMORY_TARGET not supported on this system

$ df -h /dev/shm

tmpfs  2.0G  0  2.0G  0% /dev/shm

SQL> SHOW PARAMETER memory_target

memory_target  big integer  6G

Step 2 – Fix

# mount -o remount,size=8G /dev/shm

-- make persistent in /etc/fstab:

tmpfs /dev/shm tmpfs size=8G 0 0

-- or switch to ASMM: memory_target=0, sga_target=4G, pga_aggregate_target=2G

Step 3 – Verify

SQL> STARTUP

Database opened.

Root cause

/dev/shm smaller than MEMORY_TARGET/MEMORY_MAX_TARGET.

18. ORA-00020: maximum number of processes exceeded

Symptom

New connections rejected; DB fine for existing users.

Step 1 – Diagnose

SQL> SELECT resource_name, current_utilization cur, max_utilization max, limit_value FROM v$resource_limit WHERE resource_name IN ('processes','sessions');

processes   300   300   300

sessions    330   335   472

 

SQL> SELECT machine, COUNT(*) FROM v$session GROUP BY machine ORDER BY 2 DESC FETCH FIRST 3 ROWS ONLY;

appsrv02   240

Step 2 – Fix

SQL> ALTER SYSTEM SET processes=800 SCOPE=SPFILE;

-- restart required

SQL> SHUTDOWN IMMEDIATE

SQL> STARTUP

-- also fix the connection leak on appsrv02 / app pool size.

Step 3 – Verify

SQL> SHOW PARAMETER processes

processes  integer  800

Root cause

PROCESSES limit hit, typically by an application connection leak.

19. ORA-00018: maximum number of sessions exceeded

Symptom

Similar to ORA-00020 but SESSIONS limit (default 1.5 x processes + 22).

Step 1 – Diagnose

SQL> SELECT COUNT(*) FROM v$session;

472

SQL> SHOW PARAMETER sessions

sessions  integer  472

Step 2 – Fix

SQL> ALTER SYSTEM SET sessions=1222 SCOPE=SPFILE;  -- 1.5*800+22

-- clear idle sessions now:

SQL> SELECT 'ALTER SYSTEM KILL SESSION '''||sid||','||serial#||''' IMMEDIATE;'

  2  FROM v$session WHERE status='INACTIVE' AND username='APPUSER' AND last_call_et>7200;

Step 3 – Verify

SQL> SELECT COUNT(*) FROM v$session;

188

Root cause

Sessions exhausted by idle app connections.

20. ORA-04030: out of process memory when trying to allocate N bytes

Symptom

Server process hits PGA limits; session aborts.

Step 1 – Diagnose

SQL> SHOW PARAMETER pga

pga_aggregate_target  big integer  1G

pga_aggregate_limit   big integer  2G

 

SQL> SELECT s.sid, s.username, ROUND(p.pga_used_mem/1024/1024) pga_mb

  2  FROM v$process p JOIN v$session s ON s.paddr=p.addr ORDER BY 3 DESC FETCH FIRST 3 ROWS ONLY;

312  BATCH  1850

 

$ free -m ; ulimit -a | grep -i "max memory"

Step 2 – Fix

SQL> ALTER SYSTEM SET pga_aggregate_target=4G SCOPE=BOTH;

SQL> ALTER SYSTEM SET pga_aggregate_limit=8G SCOPE=BOTH;

-- tune the offending SQL (huge sorts/PL/SQL collections: use BULK COLLECT ... LIMIT)

Step 3 – Verify

SQL> SELECT name, ROUND(value/1024/1024) mb FROM v$pgastat WHERE name IN ('total PGA allocated','maximum PGA allocated');

Root cause

PGA undersized or a runaway PL/SQL collection / hash join consuming memory.

Section 4: Storage, Tablespace & Datafiles

21. ORA-01653: Unable to extend table by N in tablespace

Symptom

DML/DDL fails: "ORA-01653: unable to extend table HR.EMP_HIST by 1024 in tablespace USERS".

Step 1 – Check tablespace usage

SQL> SELECT tablespace_name,

  2  ROUND(used_space*8192/1024/1024) used_mb,

  3  ROUND(tablespace_size*8192/1024/1024) total_mb,

  4  ROUND(used_percent,2) pct

  5  FROM dba_tablespace_usage_metrics WHERE tablespace_name='USERS';

 

TABLESPACE_NAME   USED_MB   TOTAL_MB   PCT

--------------- --------- ---------- ------

USERS               4999       5000  99.98

Step 2 – Check datafiles and autoextend

SQL> SELECT file_name, bytes/1024/1024 mb, autoextensible, maxbytes/1024/1024 max_mb

  2  FROM dba_data_files WHERE tablespace_name='USERS';

 

FILE_NAME                          MB    AUTOEXTENSIBLE  MAX_MB

---------------------------------- ----- --------------- ------

/u01/oradata/ORCL19C/users01.dbf   5000  NO              0

Step 3 – Check OS free space before adding

$ df -h /u01/oradata

Filesystem      Size  Used Avail Use% Mounted on

/dev/sdb1       500G  310G  190G  62% /u01

Step 4 – Fix (option A): enable autoextend

SQL> ALTER DATABASE DATAFILE '/u01/oradata/ORCL19C/users01.dbf'

  2  AUTOEXTEND ON NEXT 256M MAXSIZE 20G;

Database altered.

Step 4 – Fix (option B): add a datafile

SQL> ALTER TABLESPACE users ADD DATAFILE '/u01/oradata/ORCL19C/users02.dbf'

  2  SIZE 5G AUTOEXTEND ON NEXT 256M MAXSIZE 20G;

Tablespace altered.

Step 5 – Verify and retry the failed statement

SQL> SELECT tablespace_name, ROUND(used_percent,2) pct

  2  FROM dba_tablespace_usage_metrics WHERE tablespace_name='USERS';

USERS    49.99

 

SQL> INSERT INTO hr.emp_hist SELECT * FROM hr.emp_hist_stage;

1048576 rows created.

Root cause

Fixed-size datafile at 100% with AUTOEXTEND OFF. Add monitoring at 85% threshold.

22. ORA-00257: Archiver error. Connect internal only, until freed

Symptom

Database hangs for all users; alert log shows archiver cannot write (FRA / log_archive_dest full).

Step 1 – Confirm in alert log

$ tail -20 $ORACLE_BASE/diag/rdbms/orcl19c/ORCL19C/trace/alert_ORCL19C.log

ORA-19809: limit exceeded for recovery files

ORA-19804: cannot reclaim 52428800 bytes disk space from 107374182400 limit

ARCH: Error 19809 Creating archive log file to '+FRA'

Step 2 – Check FRA usage

SQL> SELECT name, ROUND(space_limit/1024/1024/1024,1) limit_gb,

  2  ROUND(space_used/1024/1024/1024,1) used_gb,

  3  ROUND(space_reclaimable/1024/1024/1024,1) reclaim_gb

  4  FROM v$recovery_file_dest;

 

NAME                          LIMIT_GB  USED_GB  RECLAIM_GB

----------------------------- --------- -------- ----------

/u01/app/oracle/fast_recovery 100       99.9     0

 

SQL> SELECT file_type, percent_space_used FROM v$recovery_area_usage;

ARCHIVED LOG   96.4

Step 3 – Back up and delete archivelogs via RMAN (do NOT rm from OS)

$ rman target /

 

RMAN> BACKUP ARCHIVELOG ALL NOT BACKED UP 1 TIMES DELETE INPUT;

...

channel ORA_DISK_1: deleting archived log(s)

archived log file name=/u01/app/oracle/fra/ORCL19C/archivelog/2026_10_10/o1_mf_1_8841_.arc

Finished backup at 12-OCT-26

 

RMAN> DELETE NOPROMPT ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-2';

deleted archived log ... Deleted 214 objects

Step 4 – If space is still short: raise the FRA limit

SQL> ALTER SYSTEM SET db_recovery_file_dest_size=200G SCOPE=BOTH;

System altered.

Step 5 – Verify

SQL> SELECT ROUND(space_used/space_limit*100,1) pct_used FROM v$recovery_file_dest;

 PCT_USED

 --------

     38.2

 

SQL> ALTER SYSTEM SWITCH LOGFILE;

System altered.   (database responsive again)

Root cause

FRA full; archived logs not being backed up/purged. Schedule RMAN archivelog backup with DELETE INPUT every 30-60 min.

23. ORA-01652: unable to extend temp segment by N in tablespace TEMP

Symptom

Big sort/hash/index build fails.

Step 1 – Diagnose

SQL> SELECT tablespace_name, ROUND(tablespace_size*8192/1048576) total_mb, ROUND(free_space*8192/1048576) free_mb FROM dba_temp_free_space;

TEMP   8192   0

 

SQL> SELECT s.sid, s.username, u.tablespace, ROUND(u.blocks*8192/1048576) mb, u.sql_id

  2  FROM v$tempseg_usage u JOIN v$session s ON s.saddr=u.session_addr ORDER BY mb DESC FETCH FIRST 3 ROWS ONLY;

401 APPS TEMP 7900 9fd2k3x1abc12

Step 2 – Fix

SQL> ALTER TABLESPACE temp ADD TEMPFILE '/u01/oradata/ORCL19C/temp02.dbf' SIZE 8G AUTOEXTEND ON NEXT 512M MAXSIZE 32G;

Tablespace altered.

-- then tune SQL 9fd2k3x1abc12 (missing join, huge sort)

Step 3 – Verify

SQL> SELECT ROUND(free_space*8192/1048576) free_mb FROM dba_temp_free_space;

8192

Root cause

TEMP too small or one SQL consuming all of it.

24. ORA-01654: unable to extend index by N in tablespace

Symptom

Index maintenance (INSERT/rebuild) fails.

Step 1 – Diagnose

ORA-01654: unable to extend index HR.EMP_IDX1 by 128 in tablespace IDX_TS

SQL> SELECT tablespace_name, ROUND(used_percent,2) pct FROM dba_tablespace_usage_metrics WHERE tablespace_name='IDX_TS';

IDX_TS   99.97

Step 2 – Fix

SQL> ALTER TABLESPACE idx_ts ADD DATAFILE '/u01/oradata/ORCL19C/idx02.dbf' SIZE 4G AUTOEXTEND ON NEXT 256M MAXSIZE 16G;

Tablespace altered.

Step 3 – Verify

SQL> SELECT ROUND(used_percent,2) FROM dba_tablespace_usage_metrics WHERE tablespace_name='IDX_TS';

52.41

Root cause

Index tablespace full (same method as ORA-01653).

25. ORA-01691: unable to extend lob segment by N in tablespace

Symptom

LOB insert fails.

Step 1 – Diagnose

ORA-01691: unable to extend lob segment HR.SYS_LOB0000091234C00005$$ by 1024 in tablespace USERS

SQL> SELECT table_name, column_name, segment_name, tablespace_name FROM dba_lobs WHERE segment_name='SYS_LOB0000091234C00005$$';

DOCS  BODY  SYS_LOB0000091234C00005$$  USERS

Step 2 – Fix

SQL> ALTER DATABASE DATAFILE '/u01/oradata/ORCL19C/users01.dbf' RESIZE 20G;

-- or add datafile; or move LOB:

SQL> ALTER TABLE hr.docs MOVE LOB(body) STORE AS (TABLESPACE lob_ts) ONLINE;

Step 3 – Verify

SQL> INSERT INTO hr.docs ...;

1 row created.

Root cause

Tablespace holding the LOB segment is full.

26. ORA-30036: unable to extend segment by N in undo tablespace

Symptom

DML fails with undo exhausted (large transaction).

Step 1 – Diagnose

SQL> SELECT tablespace_name, status, ROUND(SUM(bytes)/1048576) mb FROM dba_undo_extents GROUP BY tablespace_name, status;

UNDOTBS1 ACTIVE 30000

UNDOTBS1 UNEXPIRED 0

SQL> SELECT s.sid, t.used_ublk FROM v$transaction t JOIN v$session s ON s.taddr=t.addr ORDER BY used_ublk DESC;

512  3800000

Step 2 – Fix

SQL> ALTER TABLESPACE undotbs1 ADD DATAFILE '/u01/oradata/ORCL19C/undotbs02.dbf' SIZE 10G AUTOEXTEND ON NEXT 512M MAXSIZE 40G;

-- commit in batches in the job (do not run 50M-row DML in one transaction)

Step 3 – Verify

SQL> SELECT nospaceerrcnt FROM v$undostat ORDER BY begin_time DESC FETCH FIRST 1 ROW ONLY;

0

Root cause

Single huge uncommitted transaction consumed all undo.

27. ORA-01157 / ORA-01110: cannot identify/lock data file

Symptom

Open fails: a datafile is missing or inaccessible.

Step 1 – Diagnose

SQL> ALTER DATABASE OPEN;

ORA-01157: cannot identify/lock data file 5 - see DBWR trace file

ORA-01110: data file 5: '/u01/oradata/ORCL19C/app01.dbf'

$ ls -l /u01/oradata/ORCL19C/app01.dbf

ls: cannot access: No such file or directory

Step 2 – Fix

RMAN> RESTORE DATAFILE 5;

Starting restore at 12-OCT-26

channel ORA_DISK_1: restored datafile 00005

RMAN> RECOVER DATAFILE 5;

media recovery complete

RMAN> ALTER DATABASE DATAFILE 5 ONLINE;

RMAN> ALTER DATABASE OPEN;

Step 3 – Verify

SQL> SELECT file#, status, name FROM v$datafile WHERE file#=5;

5  ONLINE  /u01/oradata/ORCL19C/app01.dbf

Root cause

Datafile deleted, wrong permissions, or mount point missing.

28. ORA-01113: file N needs media recovery

Symptom

Datafile older than control file/redo; open fails.

Step 1 – Diagnose

ORA-01113: file 4 needs media recovery

ORA-01110: data file 4: '/u01/oradata/ORCL19C/users01.dbf'

SQL> SELECT file#, error, change# FROM v$recover_file;

4   FILE NOT FOUND/ NEEDS RECOVERY  1234567

Step 2 – Fix

RMAN> RECOVER DATAFILE 4;

media recovery complete, elapsed time: 00:00:12

RMAN> ALTER DATABASE OPEN;

Step 3 – Verify

SQL> SELECT COUNT(*) FROM v$recover_file;

0

Root cause

Datafile was restored from an older backup or the instance crashed with the file offline.

29. ORA-00376: file N cannot be read at this time

Symptom

Queries on objects in that file fail.

Step 1 – Diagnose

ORA-00376: file 7 cannot be read at this time

ORA-01110: data file 7: '/u01/oradata/ORCL19C/hist01.dbf'

SQL> SELECT file#, status FROM v$datafile WHERE file#=7;

7  RECOVER

Step 2 – Fix

RMAN> RECOVER DATAFILE 7;

SQL> ALTER DATABASE DATAFILE 7 ONLINE;

Database altered.

Step 3 – Verify

SQL> SELECT file#, status FROM v$datafile WHERE file#=7;

7  ONLINE

Root cause

Datafile OFFLINE/needs recovery after an I/O error.

Section 5: Internal Errors & Corruption

30. ORA-00600: internal error code, arguments: [...]

Symptom

Oracle internal error (bug/corruption). Alert log lists arguments and an incident trace.

Step 1 – Diagnose

$ adrci

adrci> show incident -mode detail -p "incident_id=1234"

adrci> show alert -p "message_text like '%ORA-00600%'"

ORA-00600: internal error code, arguments: [kdsgrp1], [], [], [], [], [], [], []

Step 2 – Fix

-- 1. Package the incident for Oracle Support

adrci> ips create package incident 1234

adrci> ips generate package 1 in /tmp

-- 2. Look up the first argument in My Oracle Support (ORA-600/ORA-7445 lookup tool)

-- 3. Apply latest Release Update / one-off patch

$ $ORACLE_HOME/OPatch/opatch lsinventory | head

$ $ORACLE_HOME/OPatch/datapatch -verbose

Step 3 – Verify

adrci> show incident

0 rows after patch/clean; no new ORA-00600 in alert log for 24h

Root cause

Software bug or block/memory corruption. Check the failing SQL in the trace and DBV/RMAN VALIDATE for corruption.

31. ORA-07445: exception encountered: core dump [function][signal]

Symptom

Server process crashed with OS signal (SIGSEGV).

Step 1 – Diagnose

ORA-07445: exception encountered: core dump [qerixAllocate()+144] [SIGSEGV] [ADDR:0x0] [PC:0x1234] [Address not mapped to object] []

adrci> show incident -mode detail -p "incident_id=5678"

Step 2 – Fix

adrci> ips create package incident 5678

adrci> ips generate package 2 in /tmp

-- check known bug via MOS using the function name; apply RU:

$ $ORACLE_HOME/OPatch/opatch apply

$ $ORACLE_HOME/OPatch/datapatch -verbose

-- workaround often: alter session set "_optimizer_..." only under Oracle Support guidance

Step 3 – Verify

$ opatch lspatches

$ grep -c ORA-07445 alert_ORCL19C.log

0

Root cause

Oracle code bug triggered by specific SQL/plan; resolved by patching.

32. ORA-00604: error occurred at recursive SQL level N

Symptom

Wrapper error; the real cause is the next ORA- on the stack.

Step 1 – Diagnose

ORA-00604: error occurred at recursive SQL level 1

ORA-01653: unable to extend table SYS.AUD$ by 8192 in tablespace SYSTEM

SQL> SELECT ROUND(used_percent,2) FROM dba_tablespace_usage_metrics WHERE tablespace_name='SYSTEM';

99.9

Step 2 – Fix

-- fix the underlying error (here SYSTEM full because AUD$ grew)

SQL> BEGIN DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(audit_trail_type=>DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, use_last_arch_timestamp=>FALSE); END;

/

SQL> ALTER DATABASE DATAFILE '/u01/oradata/ORCL19C/system01.dbf' RESIZE 4G;

Step 3 – Verify

SQL> SELECT ROUND(used_percent,2) FROM dba_tablespace_usage_metrics WHERE tablespace_name='SYSTEM';

41.2

Root cause

Always read the second/third error in the stack: 00604 alone is never the cause.

33. ORA-01578: ORACLE data block corrupted

Symptom

Query reads a physically corrupt block.

Step 1 – Diagnose

ORA-01578: ORACLE data block corrupted (file # 6, block # 1234)

ORA-01110: data file 6: '/u01/oradata/ORCL19C/app01.dbf'

RMAN> VALIDATE DATABASE;

SQL> SELECT * FROM v$database_block_corruption;

FILE# BLOCK# BLOCKS CORRUPTION_TYPE

6     1234   1      CORRUPT

SQL> SELECT owner, segment_name FROM dba_extents WHERE file_id=6 AND 1234 BETWEEN block_id AND block_id+blocks-1;

HR  ORDERS

Step 2 – Fix

RMAN> RECOVER DATAFILE 6 BLOCK 1234;

Starting recover at 12-OCT-26

finished block recovery

-- or all at once:

RMAN> RECOVER CORRUPTION LIST;

Step 3 – Verify

SQL> SELECT COUNT(*) FROM v$database_block_corruption;

0

SQL> SELECT COUNT(*) FROM hr.orders;

1500000

Root cause

Storage/OS fault. Investigate hardware; set DB_BLOCK_CHECKSUM=TYPICAL and take regular RMAN backups.

Section 6: Locking, SQL & Application

34. ORA-01555: Snapshot too old: rollback segment too small

Symptom

Long-running query/report/export fails after hours with ORA-01555.

Step 1 – Check UNDO configuration

SQL> SHOW PARAMETER undo

undo_management         string   AUTO

undo_retention          integer  900

undo_tablespace         string   UNDOTBS1

Step 2 – Check longest query and undo errors

SQL> SELECT TO_CHAR(begin_time,'HH24:MI') bt, maxquerylen, ssolderrcnt, nospaceerrcnt

  2  FROM v$undostat ORDER BY begin_time DESC FETCH FIRST 3 ROWS ONLY;

 

BT      MAXQUERYLEN  SSOLDERRCNT  NOSPACEERRCNT

------  -----------  -----------  -------------

10:40         3820            4              0

Step 3 – Fix: undo_retention above longest query, and guarantee if needed

SQL> ALTER SYSTEM SET undo_retention=4200 SCOPE=BOTH;

System altered.

 

SQL> ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;   -- only if critical

Tablespace altered.

Step 4 – Ensure undo tablespace can hold it

SQL> ALTER DATABASE DATAFILE '/u01/oradata/ORCL19C/undotbs01.dbf'

  2  AUTOEXTEND ON NEXT 256M MAXSIZE 30G;

Database altered.

Step 5 – Verify

SQL> SELECT tablespace_name, retention FROM dba_tablespaces WHERE tablespace_name='UNDOTBS1';

UNDOTBS1   GUARANTEE

 

-- Re-run the report; SSOLDERRCNT stays 0 in V$UNDOSTAT.

Root cause

Query ran 3820 s but undo_retention was 900 s; undo was overwritten.

35. ORA-00054 / ORA-00060: Resource busy (NOWAIT) / Deadlock detected

Symptom

DDL or DML blocked; sessions hang on enq: TX - row lock contention.

Step 1 – Identify blockers and waiters

SQL> SELECT blocking_session, sid, serial#, username, event, seconds_in_wait

  2  FROM v$session WHERE blocking_session IS NOT NULL;

 

BLOCKING_SESSION  SID  SERIAL#  USERNAME  EVENT                      SECONDS_IN_WAIT

----------------- ---- -------- --------- -------------------------- ---------------

              112  245     3311 APPS      enq: TX - row lock content  1840

Step 2 – Inspect the blocker

SQL> SELECT s.sid, s.serial#, s.username, s.status, s.machine, s.sql_id, s.last_call_et

  2  FROM v$session s WHERE s.sid=112;

 

SID  SERIAL#  USERNAME  STATUS  MACHINE   SQL_ID         LAST_CALL_ET

112  2087     APPS      INACTIVE appsrv02 8g3k2xq7m1a9z  3600

 

SQL> SELECT sql_text FROM v$sql WHERE sql_id='8g3k2xq7m1a9z';

UPDATE gl_je_lines SET status='P' WHERE je_header_id=:1

Step 3 – Locked objects

SQL> SELECT o.object_name, l.locked_mode FROM v$locked_object l

  2  JOIN dba_objects o ON o.object_id=l.object_id WHERE l.session_id=112;

GL_JE_LINES   3

Step 4 – Fix: confirm with app owner, then kill the blocker

SQL> ALTER SYSTEM KILL SESSION '112,2087,@1' IMMEDIATE;

System altered.

 

-- if it stays KILLED, kill the OS process:

SQL> SELECT spid FROM v$process WHERE addr=(SELECT paddr FROM v$session WHERE sid=112);

$ kill -9 <spid>

Step 5 – Verify

SQL> SELECT COUNT(*) FROM v$session WHERE blocking_session IS NOT NULL;

0

Step 6 – ORA-00060 deadlock: read the trace

$ grep -l "Deadlock graph" $ORACLE_BASE/diag/rdbms/orcl19c/ORCL19C/trace/*.trc

.../ORCL19C_ora_30221.trc

-- Trace shows both sessions and the SQL; fix is application lock ordering / commit frequency.

Root cause

Uncommitted transaction held by an idle session.

36. ORA-01013: user requested cancel of current operation

Symptom

Query cancelled (client timeout, Ctrl-C, tool timeout).

Step 1 – Diagnose

SQL> SELECT sid, opname, sofar, totalwork, ROUND(sofar/totalwork*100) pct, time_remaining

  2  FROM v$session_longops WHERE totalwork>0 AND sofar<>totalwork;

233 Table Scan 400000 1000000 40 5400

Step 2 – Fix

-- tune SQL / raise tool or app timeout; get plan:

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('9fd2k3x1abc12',NULL,'ALLSTATS LAST'));

-- if app timeout (JDBC/OEM), increase it; if resource manager: check plan limits

SQL> SELECT plan, name, max_idle_time FROM dba_rsrc_plan_directives WHERE plan IS NOT NULL;

Step 3 – Verify

SQL> -- rerun after index/stats fix

Elapsed: 00:00:12.40

Root cause

Operation cancelled by client/tool timeout or user; real issue is usually slow SQL.

37. ORA-01000: maximum open cursors exceeded

Symptom

App errors under load; cursor leak in application.

Step 1 – Diagnose

SQL> SHOW PARAMETER open_cursors

open_cursors  integer  300

SQL> SELECT s.sid, s.username, s.machine, COUNT(*) open_cur FROM v$open_cursor o JOIN v$session s ON s.sid=o.sid GROUP BY s.sid,s.username,s.machine ORDER BY 4 DESC FETCH FIRST 3 ROWS ONLY;

455 APPUSER appsrv02 299

Step 2 – Fix

SQL> ALTER SYSTEM SET open_cursors=1000 SCOPE=BOTH;

System altered.

-- real fix: close ResultSet/Statement in app (try-with-resources) / CLOSE cursors in PL/SQL

Step 3 – Verify

SQL> SELECT MAX(a.value) FROM v$sesstat a, v$statname b WHERE a.statistic#=b.statistic# AND b.name='opened cursors current';

312   (stable, no longer climbing)

Root cause

Cursor leak in application code.

38. ORA-04021: timeout occurred while waiting to lock object

Symptom

DDL/compile hangs on library cache lock.

Step 1 – Diagnose

ORA-04021: timeout occurred while waiting to lock object HR.PKG_PAY

SQL> SELECT s.sid, s.serial#, s.username, s.event FROM v$session s WHERE s.event LIKE 'library cache%';

SQL> SELECT session_id, owner, name, mode_held FROM dba_ddl_locks WHERE name='PKG_PAY';

312  HR  PKG_PAY  Null/Share

Step 2 – Fix

-- find who is executing the package, wait or kill the long runner

SQL> SELECT sid, serial#, status, sql_id FROM v$session WHERE sid=312;

SQL> ALTER SYSTEM KILL SESSION '312,4021' IMMEDIATE;

SQL> ALTER PACKAGE hr.pkg_pay COMPILE;

Step 3 – Verify

SQL> SELECT status FROM dba_objects WHERE object_name='PKG_PAY';

VALID

Root cause

Compiling an object that sessions are actively executing.

39. ORA-04068: existing state of packages has been discarded

Symptom

App error after package recompile/patch; session state invalidated.

Step 1 – Diagnose

ORA-04068: existing state of packages has been discarded

ORA-04061: existing state of package body "HR.PKG_PAY" has been invalidated

ORA-06508: PL/SQL: could not find program unit being called

Step 2 – Fix

-- application should simply retry the call (state re-initialised)

-- avoid compiling packages with global variables during peak hours.

SQL> EXEC DBMS_SESSION.RESET_PACKAGE;

-- consider SERIALLY_REUSABLE pragma or move state to tables

Step 3 – Verify

SQL> EXEC hr.pkg_pay.run_payroll;

PL/SQL procedure successfully completed.

Root cause

Package recompiled while sessions held its state.

40. ORA-04063: view/package/procedure has errors

Symptom

Object is INVALID after DDL change or migration.

Step 1 – Diagnose

ORA-04063: package body "HR.PKG_PAY" has errors

SQL> SELECT owner, object_name, object_type FROM dba_objects WHERE status='INVALID' AND owner='HR';

HR PKG_PAY PACKAGE BODY

SQL> SELECT line, text FROM dba_errors WHERE name='PKG_PAY' AND owner='HR';

42 PLS-00201: identifier 'GL_UTIL.POST' must be declared

Step 2 – Fix

SQL> GRANT EXECUTE ON gl.gl_util TO hr;     -- fix cause shown in DBA_ERRORS

SQL> @?/rdbms/admin/utlrp.sql               -- recompile all invalid objects

SQL> ALTER PACKAGE hr.pkg_pay COMPILE BODY;

Step 3 – Verify

SQL> SELECT COUNT(*) FROM dba_objects WHERE status='INVALID';

0

Root cause

Dependent object changed or grant revoked; compile errors in DBA_ERRORS.

41. ORA-00001: unique constraint violated

Symptom

Duplicate key on INSERT/UPDATE.

Step 1 – Diagnose

ORA-00001: unique constraint (HR.EMP_PK) violated

SQL> SELECT column_name FROM dba_ind_columns WHERE index_name='EMP_PK';

EMPLOYEE_ID

SQL> SELECT employee_id, COUNT(*) FROM hr.emp_stage GROUP BY employee_id HAVING COUNT(*)>1;

100  2

SQL> SELECT MAX(employee_id) FROM hr.employees;   -- compare with sequence

5000

Step 2 – Fix

-- dedupe staging data, or fix the sequence that is behind

SQL> ALTER SEQUENCE hr.emp_seq RESTART START WITH 5001;

-- bulk loads: use MERGE or LOG ERRORS

SQL> INSERT INTO hr.employees SELECT * FROM hr.emp_stage LOG ERRORS INTO err$_employees REJECT LIMIT UNLIMITED;

Step 3 – Verify

SQL> SELECT COUNT(*) FROM err$_employees;

1

Root cause

Duplicate data or sequence lagging behind existing max key.

42. ORA-02291: integrity constraint violated - parent key not found

Symptom

Child row references a missing parent.

Step 1 – Diagnose

ORA-02291: integrity constraint (HR.EMP_DEPT_FK) violated - parent key not found

SQL> SELECT table_name, r_constraint_name FROM dba_constraints WHERE constraint_name='EMP_DEPT_FK';

EMPLOYEES  DEPT_PK

SQL> SELECT department_id FROM hr.departments WHERE department_id=999;

no rows selected

Step 2 – Fix

SQL> INSERT INTO hr.departments(department_id, department_name) VALUES (999,'NEW');

SQL> COMMIT;

-- or correct the child value

Step 3 – Verify

SQL> INSERT INTO hr.employees(...) VALUES (...,999);

1 row created.

Root cause

Insert order wrong or parent row missing/deleted.

Section 7: Backup, Recovery & Data Pump

43. ORA-19505: failed to identify file (RMAN backup piece missing)

Symptom

RMAN restore/crosscheck fails; backup pieces deleted at OS level.

Step 1 – Diagnose

RMAN> RESTORE DATABASE;

ORA-19505: failed to identify file "/backup/ORCL19C/full_0abc.bkp"

ORA-27037: unable to obtain file status

RMAN> LIST BACKUP SUMMARY;

Step 2 – Fix

RMAN> CROSSCHECK BACKUP;

backup piece handle=/backup/ORCL19C/full_0abc.bkp  ... EXPIRED

RMAN> DELETE NOPROMPT EXPIRED BACKUP;

RMAN> BACKUP DATABASE PLUS ARCHIVELOG;    -- take a fresh good backup

Step 3 – Verify

RMAN> LIST BACKUP SUMMARY;

... all AVAILABLE

RMAN> RESTORE DATABASE VALIDATE;

Root cause

Backup pieces removed outside RMAN, or storage not mounted.

44. ORA-00308: cannot open archived log

Symptom

Media recovery needs an archivelog that is not on disk.

Step 1 – Diagnose

ORA-00279: change 1234567 generated at 10/12/2026 01:00:00 needed for thread 1

ORA-00289: suggestion : /u01/arch/1_8841_1100000000.arc

ORA-00308: cannot open archived log '/u01/arch/1_8841_1100000000.arc'

RMAN> LIST BACKUP OF ARCHIVELOG SEQUENCE 8841;

Step 2 – Fix

RMAN> RESTORE ARCHIVELOG SEQUENCE 8841 THREAD 1;

-- if copied manually:

RMAN> CATALOG START WITH '/backup/arch/' NOPROMPT;

RMAN> RECOVER DATABASE;

Step 3 – Verify

RMAN> ALTER DATABASE OPEN;

Statement processed

Root cause

Archivelogs deleted before they were backed up, or on another host.

45. RMAN-06059: expected archived log not found, loss of archived log compromises recoverability

Symptom

RMAN backup of archivelogs fails.

Step 1 – Diagnose

RMAN-03002: failure of backup command

RMAN-06059: expected archived log not found, loss of archived log compromises recoverability

RMAN> LIST ARCHIVELOG ALL;   -- sequence 8801 listed but file missing

Step 2 – Fix

RMAN> CROSSCHECK ARCHIVELOG ALL;

validation failed for archived log ... 8801 ...

RMAN> DELETE NOPROMPT EXPIRED ARCHIVELOG ALL;

RMAN> BACKUP DATABASE PLUS ARCHIVELOG DELETE INPUT;   -- new full backup: older chain no longer recoverable past gap

Step 3 – Verify

RMAN> BACKUP ARCHIVELOG ALL;

Finished backup  (no RMAN-06059)

Root cause

Archivelogs removed with OS rm instead of RMAN. Never delete archivelogs from the OS.

46. ORA-01152: file N was not restored from a sufficiently old backup

Symptom

Recovery cannot proceed; datafile backup is newer than recovery point.

Step 1 – Diagnose

ORA-01152: file 3 was not restored from a sufficiently old backup

ORA-01110: data file 3: '/u01/oradata/ORCL19C/undotbs01.dbf'

SQL> SELECT file#, checkpoint_change# FROM v$datafile_header;

3  2345678

Step 2 – Fix

-- restore datafile 3 from an older backup, then recover forward

RMAN> RESTORE DATAFILE 3 UNTIL SCN 2345000;

RMAN> RECOVER DATAFILE 3;

-- or use RMAN to pick the right backup automatically

RMAN> RESTORE DATABASE;  RECOVER DATABASE;

Step 3 – Verify

SQL> ALTER DATABASE OPEN;

Database altered.

Root cause

Mix of restored files with different SCN points; restore a consistent set.

47. ORA-39002 / ORA-39070: invalid operation / unable to open the log file (Data Pump directory)

Symptom

expdp/impdp fails immediately.

Step 1 – Diagnose

$ expdp system/*** schemas=HR directory=DP_DIR dumpfile=hr.dmp logfile=hr.log

ORA-39002: invalid operation

ORA-39070: Unable to open the log file.

ORA-29283: invalid file operation: cannot open file

SQL> SELECT directory_name, directory_path FROM dba_directories WHERE directory_name='DP_DIR';

DP_DIR  /u01/dpump

$ ls -ld /u01/dpump

ls: cannot access /u01/dpump

Step 2 – Fix

$ mkdir -p /u01/dpump && chown oracle:oinstall /u01/dpump && chmod 750 /u01/dpump

SQL> CREATE OR REPLACE DIRECTORY dp_dir AS '/u01/dpump';

SQL> GRANT READ, WRITE ON DIRECTORY dp_dir TO hr;

Step 3 – Verify

$ expdp system/*** schemas=HR directory=DP_DIR dumpfile=hr.dmp logfile=hr.log

Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed

Root cause

DIRECTORY object points to a missing path, or oracle OS user has no permission.

48. ORA-39126: worker unexpected fatal error in KUPW$WORKER

Symptom

Data Pump job aborts on a specific object.

Step 1 – Diagnose

$ impdp system/*** directory=DP_DIR dumpfile=app.dmp schemas=APP logfile=imp.log

ORA-39126: Worker unexpected fatal error in KUPW$WORKER.PUT_DDLS [TABLE_STATISTICS]

ORA-01403: no data found

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105

ORA-06512: at "SYS.KUPW$WORKER", line 12345

----- PL/SQL Call Stack ----- ... Job aborted

-- note the object type in [brackets] and the object name in the log

Step 2 – Fix

-- 1. Re-run excluding the failing object type / object

$ impdp system/*** directory=DP_DIR dumpfile=app.dmp schemas=APP exclude=STATISTICS

-- 2. Drop leftover master tables from the failed job

SQL> SELECT owner_name, job_name, state FROM dba_datapump_jobs;

SQL> DROP TABLE system.SYS_IMPORT_SCHEMA_01 PURGE;

-- 3. Repair Data Pump/metadata packages and patches

SQL> @?/rdbms/admin/utlrp.sql

SQL> EXEC DBMS_METADATA_UTIL.LOAD_STYLESHEETS;

$ $ORACLE_HOME/OPatch/datapatch -verbose

-- 4. Check dump/DB version compatibility and tablespace/quota on target

Step 3 – Verify

$ grep -E "completed|ORA-" imp.log

Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully completed at 12-OCT-26

Root cause

Source dump object not importable (corrupt metadata, missing prerequisite, bug fixed in Data Pump bundle patch, invalid SYS packages). The bracketed object type in the message identifies what to exclude or fix.

49. ORA-31684 / ORA-39151: object already exists / table exists, skipped

Symptom

Import skips or errors on existing objects.

Step 1 – Diagnose

ORA-31684: Object type USER:"APP" already exists

ORA-39151: Table "APP"."ORDERS" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

Step 2 – Fix

$ impdp system/*** directory=DP_DIR dumpfile=app.dmp schemas=APP table_exists_action=REPLACE

-- or import to a different schema

$ impdp ... remap_schema=APP:APP_TEST

Step 3 – Verify

$ grep "completed" imp.log

Job ... completed with 1 error(s)   (ORA-31684 for USER is benign)

Root cause

Target already contains the objects. ORA-31684 on USER is informational.

50. ORA-39142: incompatible version number in dump file

Symptom

Import into a lower DB version than the export source.

Step 1 – Diagnose

ORA-39142: incompatible version number 5.1 in dump file "/u01/dpump/app.dmp"

Step 2 – Fix

-- re-export from the higher version for the lower target

$ expdp system/*** schemas=APP directory=DP_DIR dumpfile=app.dmp VERSION=12.2

-- or import into an equal/higher version DB

Step 3 – Verify

$ impdp system/*** directory=DP_DIR dumpfile=app.dmp schemas=APP

Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully completed

Root cause

Dump file created by a newer Oracle version than the importing database.

Prevention & Monitoring

•      Tablespace alerts at 85%/95% (dba_tablespace_usage_metrics)

•      RMAN archivelog backup with DELETE INPUT every 30-60 min; never rm archivelogs

•      Multiplex control files and redo logs; CONFIGURE CONTROLFILE AUTOBACKUP ON

•      Monitor FRA, TEMP, UNDO, processes/sessions via v$resource_limit

•      Weekly AWR/ADDM review; use bind variables

•      Quarterly Release Update + datapatch; test Data Pump imports

•      RMAN VALIDATE DATABASE weekly to catch block corruption early

•      Keep listener.ora/tnsnames.ora/sqlnet.ora under version control

SQL> SELECT tablespace_name, ROUND(used_percent,2) pct

  2  FROM dba_tablespace_usage_metrics WHERE used_percent > 85;