Sujeet APPS DBA: 2024

ORA-00059: Maximum Number Of DB_FILES Exceeded in 19C database

When I am adding datafile to my 19C database facing the below error.

SQL> alter tablespace DATA add datafile '/u01/data/data15.dbf' size 20G;
alter tablespace DATA add datafile '/u01/data/data15.dbf' size 20G
*
ERROR at line 1:
ORA-00059: maximum number of DB_FILES exceeded

SOLUTION-

Check for DB_FILES value

SQL> sho parameter db_files;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------
db_files integer 500

Check Value for Total number of datafiles in Database

SQL> select count(*) from dba_data_files;

COUNT(*)
----------
496

Set the db_files parameter to a new high-value

SQL> alter system set db_files = 1000 scope = spfile;

System altered.

Restart your database

SQL>shutdown immediate;

SQL>startup

SQL> sho parameter db_files;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------
db_files integer 1000

SQL> alter tablespace DATA add datafile '/u01/data/data15.dbf' size 20G;

issue resolved.

Active Session History, (ASH) reports.

Active Session History, (ASH) reports are one of my favorite when investigating a performance issue.

The biggest challenge I think that faces many administrators is to know when to turn to an ASH report and how to use the report to diagnose an issue.

# Generating an ASH Report on PDB database.

[oracle@ora-19c]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jun 6 17:49:49 2024

Version 19.21.0.0.0

Connected to:

Oracle Database 19c EE High Perf Release 19.0.0.0.0 - Production

Version 19.21.0.0.0

SQL> show pdbs;

CON_ID CON_NAME OPEN MODE RESTRICTED

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

2 PDB$SEED READ ONLY NO

3 PDB READ WRITE NO

SQL> alter session set container=PDB;

Session altered.

SQL> @$ORACLE_HOME/rdbms/admin/ashrpt.sql

Enter value for awr_location: AWR_PDB

Specify the begin time in minutes before the system date:

Enter value for begin_time: -30

In this example, 30 minutes before the current time is selected.

Enter value for begin_time: -30

Enter value for duration: <ENTER>

Enter value for report_name: ASH_report.txt

SQL> !

find same file(ASH_report.txt) in same location.

#Report Options.

You’ll notice that the SQL Details page offers you two reports that you can run- AWR SQL Report and the ASH Report.

These reports can both be found in the $ORACLE_HOME/rdbms/admin directory.

AWR SQL_ID specific report is named awrsqrpt.sql and the ASH report is named ashrpt.sql if you’re interested in running them from the command line via SQLPlus.

We are going to choose to use the ASH report instead of the SQL_ID specific AWR report OR a full AWR report for a couple reasons:

1. We are only interested in this specific SQL_ID, so the full AWR report would be overkill.

2. The specific AWR SQL_ID report wouldn’t include the session, blocked session and other wait info we are interested in.

3. We aren’t interested in execution plans, which the SQL_ID specific AWR Report includes.

4. The ASH report allows us to use samples and drill down to by time vs. snapshots, which comes in handy when we are inspecting particular set of transactions. We aren’t concerned with number of executions, which is offered in AWR, but not in samples with ASH.

SHRD0014: GLLEZL - process exiting with failure

SYMPTOMS

Journal Import completes with the following error:

Error

------

ORA-01653 : unable to extend table GL.GL_IMPORT_REFERENCES

ORA-01653 : unable to extend table GL.GL_JE_LINES

shrd0014: gllezl - process exiting with failure

gllcje- for the consolidation transfer program.

CAUSE

The tablespace must be increased at the database level.

SOLUTION

To implement the solution, please execute the following steps:

The DBA needs to increase or extend the tablespace for the tables referenced in the error at the database level.

Use this specific SQL to find the tablespace name:

SQL > select OWNER, TABLE_NAME , TABLESPACE_NAME

from dba_tables

where owner = 'GL'

and table_name like '%GL_IMPORT_REFERENCES%';

There are other columns on dba_tables that the DBA may want to query to get the extents etc.

Add a Datafile:

ALTER TABLESPACE ADD DATAFILE '' SIZE ;

If the tablespace is being used as a temporary tablespace, temporary segments may be still holding the space.

You can query this for how much free, used, total, and percentage filled space is available for each tablespace.

SQL> select tablespace_name, ROUND(bytes_free,2) free,ROUND(bytes_total-bytes_free,2) used, ROUND(bytes_total,2) total,perused

from (select a.tablespace_name, a.bytes_free,b.bytes_total,

ROUND((100-(100*a.bytes_free/b.bytes_total)),2) perused from

(Select tablespace_name,sum(bytes)/1024/1024 bytes_free From dba_free_space Group by tablespace_name) a,

(Select tablespace_name,sum(bytes)/1024/1024 bytes_total From dba_data_files Group by tablespace_name) b

where a.tablespace_name=b.tablespace_name) where perused >= 0 ORDER BY tablespace_name;

select tablespace_name,sum(bytes)/(1024*1024) from dba_free_space

group by tablespace_name;

## ADD TABLESPACE

SQL > alter tablespace DATA add datafile '/u01/data/data13.dbf' size 10G;

## Check Temp tablespace.

SQL> select TABLESPACE_NAME, BYTES_USED/1024/1024, BYTES_FREE/1024/1024 from V$TEMP_SPACE_HEADER;

TABLESPACE_NAME BYTES_USED/1024/1024 BYTES_FREE/1024/1024

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

TEMP1 10240 10240

TEMP2 10240 10240

##Resize/add temp datafile.

SQL> alter tablespace TEMP2 add tempfile '/u01/data/temp04.dbf' size 30G;

SQL> alter tablespace TEMP1 add tempfile '/u01/data/temp05.dbf' size 30G;

SQL> ALTER DATABASE TEMPFILE '/u01/data/temp02.dbf' RESIZE 30G;

ORA-01552: cannot use system rollback segment for non-system tablespace 'TEMP'

ORA-01552: cannot use system rollback segment for non-system tablespace "string"

Cause: Used the system rollback segment for non-system tablespace operations.

If this is a clone database, this will happen when attempting any data modification

outside the UNDOTBS tablespace.

Solution:

1) Switch to Automatic Undo Management

$ sqlplus / as sysdba

alter system set undo_management=auto scope=spfile;

2) Restart the database.

SQL> Shutdown immediate;

SQL> startup

ORA-08004: sequence IEX_DEL_BUFFERS_S.NEXTVAL exceeds MAXVALUE

Error:-

IEX: Scoring Engine Harness Error - ORA-08004: sequence IEX_DEL_BUFFERS_S.NEXTVAL exceeds MAXVALUE (Doc ID 2056754.1)

SYMPTOMS:-

You may have noticed that IEX_DELINQUENCIES_ALL is not getting updated with closed delinquencies or new delinquencies on past due invoices when the IEX:

Scoring Engine Harness or IEX: Delinquency Management processes run. You have confirmed that you are not putting in a value for the Maximum Number of Records parameter on the IEX: Scoring Engine Harness, however no delinquencies are being created.

On further investigation, even though IEX: Scoring Engine Harness shows successful the following message can be seen in the log -

ERROR IN CONCUR: IEX_SCORE: storeDelBuffest: UnExpected Error ORA-08004: sequence IEX_DEL_BUFFERS_S.NEXTVAL exceeds MAXVALUE and cannot be instatiated

CAUSE:-

The IEX_DELINQUENCIES_ALL table has run out of sequence numbers and you can no longer create new records.

Bug 21812472

SOLUTION:-

To resolve the issue, the following SQL will recreate the sequence IEX_DEL_BUFFERS_S with NO Max Value:

set serveroutput on;

declare

BEGIN

execute immediate ('drop sequence IEX.iex_del_buffers_s');

execute immediate ('create sequence IEX.IEX_DEL_BUFFERS_S START WITH 10000 '

|| 'MINVALUE 1 INCREMENT BY 1 CACHE 100 NOORDER ');

DBMS_OUTPUT.put_line('New Sequence for IEX_DEL_BUFFERS_S ' || sqlerrm);

EXCEPTION

WHEN OTHERS then

DBMS_OUTPUT.put_line('Sequence creation failed IEX_DEL_BUFFERS_S ' ||

sqlerrm);

end;

commit;

Output like below:-

New Sequence for IEX_DEL_BUFFERS_S ORA-0000: normal, successful completion

PL/SQL procedure successfully completed.

Commit complete.