ORA-15041: diskgroup “FRA” space exhausted

The following errors occur in Oracle database alert log :

...
..
.
Unable to create archive log file '+FRA'
Errors in file /u01/app/oracle/diag/rdbms/testdb/TESTDB1/trace/TESTDB1_arc1_12300.trc:
ORA-19816: WARNING: Files may exist in db_recovery_file_dest that are not known to database.
ORA-17502: ksfdcre:4 Failed to create file +FRA
ORA-15041: diskgroup "FRA" space exhausted

CAUSE

Diskgroup FRA had been overallocated, the sum of db_recovery_file_dest_size of all the databases is more than the size of diskgroup FRA.

SQL> select NAME,TOTAL_MB,FREE_MB from v$asm_diskgroup
;

NAME                             TOTAL_MB    FREE_MB
------------------------------ ---------- ----------
...
..
.
FRA                               1024142       120

RESOLUTION

Decrease ‘db_recovery_file_dest_size” for some or all databases, and make sure the diskgroup FRA is not overallocated.

ORA-03206: maximum file size of (4194304) blocks in AUTOEXTEND clause is out of range (DBD ERROR: OCIStmtExecute) from Corrective Action of Add Space to Tablespace

OEM add space to tablespace corrective action failed with the following ORA-03206 error:

...
..
.
OMF directory: +DATAC1
OMF disk group: DATAC1
ASM projected safely usable free space (MB) is: 28548641.667
ASM projected Space Used (%) is: 59.11
Add datafile SQL is: ALTER TABLESPACE "TEST" ADD DATAFILE SIZE 7291M AUTOEXTEND ON NEXT 100M MAXSIZE 32768M
Execute(ALTER TABLESPACE "TEST" ADD DATAFILE SIZE 7291M AUTOEXTEND ON NEXT 100M MAXSIZE 32768M) failed:ORA-03206: maximum file size of (4194304) blocks in AUTOEXTEND clause is out of range (DBD ERROR: OCIStmtExecute)
Disconnected from database

CAUSE

For small datafile tablespace, the maximum datafile size is 32GB ( 32768M ) in theory. But in actual situation, the given size should be little less than 32GB.

SOLUTION

Explicitly specify the maximum datafile size to 31GB ( 31744 M ), for example, instead of the default 32GB ( 32768M ) in corrective action parameters configuration.

Before

Maximum Datafile Size (GB)

After

Maximum Datafile Size (GB)31
...
..
.
OMF directory: +DATAC1
OMF disk group: DATAC1
ASM projected safely usable free space (MB) is:  28548641.667
ASM projected Space Used (%) is: 59.11
Add datafile SQL is: ALTER TABLESPACE "TEST" ADD DATAFILE SIZE 7291M AUTOEXTEND ON NEXT 100M MAXSIZE 31744M
Successfully added new datafile

“ORA-01031: insufficient privileges” from SYS User Creating Materialized Views

A client try to create a materialized view by SYS user with the following errors:

SQL> show user
USER is "SYS"

SQL> create materialized view USER1.MV_TABLE2
BUILD IMMEDIATE
REFRESH FAST ON commit
with primary key
as select * from USER2.TABLE2;


ERROR at line 6:
ORA-01031: insufficient privileges

SOLUTION

Subscribe to get access

Read more of this content when you subscribe today.

“ORA-06533: Subscript beyond count” from DBMS_STATS

DBMS_STATS threw the following errors:

SQL> EXEC DBMS_STATS.gather_table_stats('USERNAME','TABLENAME');
BEGIN DBMS_STATS.gather_table_stats('USERNAME','TABLENAME'); END;

*
ERROR at line 1:
ORA-06533: Subscript beyond count
ORA-06512: at "SYS.DBMS_STATS", line 34634
ORA-06512: at line 1

WORKAROUND

Use a numeric value for estimate_percent instead of default dbms_stats.auto_sample_size:

SQL> exec dbms_stats.gather_table_stats( ownname=>'USERNAME',tabname=>'EVENT',estimate_percent=>100);

PL/SQL procedure successfully completed.

ORA-01450: maximum key length (3215) exceeded

A client is trying to rebuild an index online with the following errors:

SQL> alter index indx_cdr rebuild online;
alter index IDX_CDR rebuild online
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01450: maximum key length (3215) exceeded



SQL> select max(length( call_message)) from  cdr;

MAX(LENGTH(CALL_MESSAGE))
-------------------------
               3586

WORKAROUND

Build the index without ONLINE option:

SQL> alter index indx_cdr rebuild;

Index altered.