ORA-48913: Writing into trace file failed, file size limit

Error detailed from alert log.

Error message: ORA-48913: Writing into trace file failed, file size limit [10485760] reached
ORA-609 : opiodr aborting process unknown ospid (4397_47562687353296)
ORA-609 : opiodr aborting process unknown ospid (28545_47989084123600)
ORA-609 : opiodr aborting process unknown ospid (4703_47579510747600)
Non critical error ORA-48913 caught while writing to trace file "/u01/prod/db/tech_st/11.1.0/admin/PROD1_prod1/
diag/rdbms/prod1/PROD1/trace/PROD1_dbrm_13082.trc"
Error message: ORA-48913: Writing into trace file failed, file size limit [10485760] reached


Cause :-

We can increase the setting for the parameter MAX_DUMP_FILE_SIZE or set it to unlimited

MAX_DUMP_FILE_SIZE specifies the maximum size of trace files (excluding the alert file). Change this limit if you are concerned that trace files may use too much space.
A numerical value for MAX_DUMP_FILE_SIZE specifies the maximum size in operating system blocks.
A number followed by a K or M suffix specifies the file size in kilobytes or megabytes.
The special value string UNLIMITED means that there is no upper limit on trace file size. Thus, dump files can be as large as the operating system permits.




Solution :-


SQL> show parameter MAX_DUMP_FILE_SIZE;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
max_dump_file_size                   string      20480
SQL>

You would need to reset value of MAX_DUM_FILE_SIZE in order to have your trace files size extend beyond 20480 MB now. You can use “alert system” 
command to change the value. You may also set it to UNLIMITED.


SQL> ALTER SYSTEM SET max_dump_file_size=unlimited;

Pluggable Database (PDB) useful SQL statement

[ora@oracle]$ sqlplus system/****@PROD_CDB

SQL> show con_name;

CON_NAME
------------------------------
CDB$ROOT

SQL> select name from v$database;

NAME
---------
PROD

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB                         MOUNTED


SQL> ALTER SESSION SET CONTAINER= PDB;

Session altered.

SQL> ALTER PLUGGABLE DATABASE OPEN;

Warning: PDB altered with errors.

SQL> ALTER DATABASE OPEN;

Database altered.

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         3 PDB                         READ WRITE YES

SQL> shut immediate

SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;

System altered.

SQL> select value from v$parameter where name='PDB';

SQL> select cause, message from pdb_plug_in_violations where name = 'PDB';

SQL> select * from v$services;

SQL> select SERVICE_ID,NAME,NETWORK_NAME from v$services;

SQL> select NAME from v$services;

[ora@oracle]$ sqlplus apps/****@PDB


SQL> select username from dba_users where USERNAME= 'APPS';

no rows selected

ORA-03113: end-of-file on communication channel

SQL> startup
ORACLE instance started.

Total System Global Area 8589934592 bytes
Fixed Size                  3731384 bytes
Variable Size            1140850760 bytes
Database Buffers         7432306688 bytes
Redo Buffers               13045760 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 1264
Session ID: 814 Serial number: 23355


SQL>

[ora@sujeet ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Aug 22 21:07:49 2019

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 8589934592 bytes
Fixed Size                  3731384 bytes
Variable Size            1140850760 bytes
Database Buffers         7432306688 bytes
Redo Buffers               13045760 bytes
SQL>
SQL> alter database mount;

Database altered.

SQL>
SQL>
SQL> alter database clear unarchived logfile group 1;

Database altered.

SQL> alter database clear unarchived logfile group 2;

Database altered.

SQL> alter database clear unarchived logfile group 3;

Database altered.

SQL> shutdown immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL> startup
ORACLE instance started.

Total System Global Area 8589934592 bytes
Fixed Size                  3731384 bytes
Variable Size            1140850760 bytes
Database Buffers         7432306688 bytes
Redo Buffers               13045760 bytes
Database mounted.
Database opened.
SQL>

Renaming Data Files in 11g database

Renaming datafile (.dbf)  rename in 11g

Rename Datafiles in Single Tablespace  (Database Open Mode) :-

Caution : Backup your tablespace Before you will do some change in your tablespace.

Please find below steps to rename datafile.

We can use the alter tablespace rename datafile command,
but the tablespace most be offline and you must re-name at OS level command the data file while the tablespace is offline:


Steps:- 1

[ora@oracle ~]$ sqlplus / as sysdba

SQL> ALTER TABLESPACE SYSAUX OFFLINE NORMAL;


Tablespace altered.
SQL> !

Steps:- 2

Rename undo07.dbf to sysaux08.dbf


[ora@oracle ~]$ cd /u01/db/apps_st/data/

[ora@oracle data]$ ls -lrt|grep undo07.dbf
-rw-r----- 1 oraqa dba  3221233664 Aug  9 11:32 undo07.dbf

[ora@oracle data]$ chmod 755 undo07.dbf

[ora@oracle data]$ mv undo07.dbf sysaux08.dbf

[ora@oracle data]$ ls -lrt|grep undo07.dbf

[ora@oracle data]$ ls -lrt|grep sysaux08.dbf
-rwxr-xr-x 1 oraqa dba  3221233664 Aug  9 11:32 sysaux08.dbf


Steps:- 3

[ora@oracle data]$ sqlplus / as sysdba

SQL> ALTER TABLESPACE SYSAUX RENAME DATAFILE '/u01/db/apps_st/data/undo07.dbf' to '/u01/db/apps_st/data/sysaux08.dbf';

Tablespace altered.

Steps:- 4

SQL> ALTER TABLESPACE SYSAUX ONLINE;

Tablespace altered.

Steps:- 5

Verify tablespace status.

SQL> SELECT status
FROM dba_tablespaces
WHERE tablespace_name = 'SYSAUX';

STATUS
---------
ONLINE

Verify datafile name.

SQL> select TABLESPACE_NAME,FILE_NAME from dba_data_files where TABLESPACE_NAME = 'SYSAUX';

TABLESPACE_NAME-------------FILE_NAME
--------------------------------------------------------------------------------
SYSAUX                    /u01/db/apps_st/data/sysaux08.dbf





Rename datafile undo07.dbf to sysaux08.dbf completed.

ORA-48913: Writing into trace file failed, file size limit [10485760] reached

ERROR:-
Non critical error ORA-48913 caught while writing to trace file
 "/apps/PROD/db/diag/rdbms/prod/PROD/trace/PROD_dbrm_6874.trc"
Error message: ORA-48913: Writing into trace file failed, file size limit [10485760] reached.


In some environments DBAs limit the size of trace files generated by the database. This included all trace files that could get generated under USER_DUMP_DEST/DIAGNOSTIC_DEST). The parameter to set the limit for trace files is MAX_DUMP_FILE_SIZE and its value is in OS number of blocks. After setting this value, if any trace file size would increase form the size specified in this parameter, ORA-48913 would be recorded in alert log file.

Cause
The reason was Parameter MAX_DUMP_FILE_SIZE  is set too low.

Solution
We can increase the setting for the parameter MAX_DUMP_FILE_SIZE or set it to unlimited
MAX_DUMP_FILE_SIZE specifies the maximum size of trace files (excluding the alert file). Change this limit if you are concerned that trace files may use too much space.
A numerical value for MAX_DUMP_FILE_SIZE specifies the maximum size in operating system blocks.
A number followed by a K or M suffix specifies the file size in kilobytes or megabytes.
The special value string UNLIMITED means that there is no upper limit on trace file size. Thus, dump files can be as large as the operating system permits.


SQL> show parameter max_dump_file

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
max_dump_file_size string 20000


SQL> alter system set max_dump_file_size=UNLIMITED scope=both;

System altered.


SQL> show parameter max_dump_file

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
max_dump_file_size string UNLIMITED

OR

SQL> show parameter dump_file

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
max_dump_file_size                   string      20480


SQL> select max(lebsz) from x$kccle;

MAX(LEBSZ)
----------
       512

SQL> alter session set max_dump_file_size=’1024M’;

Session altered.

SQL> show parameter max_dump_file_size

NAME TYPE VALUE
———————————— ———– ———–
max_dump_file_size string 1024M

UnsatisfiedLinkError exception loading native library: njni11

UnsatisfiedLinkError exception loading native library: njni11 while running Autoconfig on DB Node


Context Value Management will now update the Context file
UnsatisfiedLinkError exception loading native library: njni11


FiX
---

To fix this issue, Edit the database node xml as below and re-run the autoconfig.

database xml file path :-/u01/db/tech_st/11.1.0/appsutil/VISPRD_hostname.xml

From
----
<          <JDK_TOP oa_var="s_jdktop">/u01/db/tech_st/11.1.0/appsutil/jre</JDK_TOP>
<          <JRE_TOP oa_var="s_jretop">/u01/db/tech_st/11.1.0/appsutil/jre</JRE_TOP>

To
---
>          <JDK_TOP oa_var="s_jdktop">/u01/db/tech_st/11.1.0/jdk/jre</JDK_TOP>
>          <JRE_TOP oa_var="s_jretop">/u01/db/tech_st/11.1.0/jdk/jre</JRE_TOP>

rerun auto-config on the DB node.

I hope issue resolved.

DBMS_AW_EXP: SYS.AW$EXPRESS: OLAP not enabled

Customer RecommendedDatapump Export (expdp) Raises Warnings Like "DBMS_AW_EXP: SYS.AW$EXPRESS: OLAP not enabled" (Doc ID 1638799.1)


APPLIES TO:
Oracle Database - Enterprise Edition - Version 11.2.0.4 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Information in this document applies to any platform.

SYMPTOMS
Expdp on Oracle Standard Edition / Standard Edition 1 produces below warnings

DBMS_AW_EXP: SYS.AW$EXPRESS: OLAP not enabled
DBMS_AW_EXP: SYS.AW$AWMD: OLAP not enabled
DBMS_AW_EXP: SYS.AW$AWCREATE: OLAP not enabled
DBMS_AW_EXP: SYS.AW$AWCREATE10G: OLAP not enabled
DBMS_AW_EXP: SYS.AW$AWXML: OLAP not enabled
DBMS_AW_EXP: SYS.AW$AWREPORT: OLAP not enabled

CAUSE
The issue was explained in
Bug 17718899 - EXPDP ON STANDARD EDITION ONE SHOWS MESSAGES "OLAP NOT ENABLED"
closed with status 'Not a Bug' with below comment:

OLAP is not available in Standard Edition One (SE1) or Standard Edition (SE) as per the Database Licensing Information document,
it is only available in Enterprise Edition (EE).

This message is intentionally output for DATAPUMP and coded as such in 11.2.0.4 and higher Standard Edition One (SE1) or Standard Edition (SE).

From the Datapump perspective these are warnings and it is safe to ignore them.



NOTE:
The same symptoms can be encountered with the Oracle Enterprise Edition when OLAP is not installed.


SOLUTION
The OLAP related warnings can be safely ignored for DataPump usage.


REFERENCES
BUG:17718899 - EXPDP ON STANDARD EDITION ONE SHOWS MESSAGES "OLAP NOT ENABLED"

ORA-39142: incompatible version number 5.1 in dump file

ORA-39142: incompatible version number 5.1 in dump file


PROBLEM:

Trying to export schema/tables from higher Database version 12.2.0.1.0 and import to version 12.1.0.1.0, causing following error message.

ERROR:

Import: Release 12.1.0.2.0 - Production on Thu Jun 13 17:03:18 2019

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-39142: incompatible version number 5.1 in dump file "/u01/expimp/schemas_name.dmp"


SOLUTION:

Please set parameter Version=12.1 in export command and import the data without issue.

Source Database(12.2.0.1):

expdp system/****@prod schemas=schemas_name Version=12.1 directory=EXPDP dumpfile=schemas_name.dmp log=schemas_name.log

expdp system/****@PROD directory=EXPDP tables=table_name Version=12.1dumpfile=schemas_name.dmp log=schemas_name.log

Target Database(12.1.0.1):

impdp system/****@prod  directory=EXPDP schemas=schemas_name dumpfile=schemas_name.dmp logfile=schemas_name.log

impdp system/****@PROD directory=EXPDP tables=table_name dumpfile=schemas_name.dmp log=schemas_name.log