Archive for the ‘Oracle Queries’ Category

« Older Entries
Newer Entries »

ADAUTOCFG.SH AND ADCFGLONCE.PL DBCONFIG – FAILS WITH ORA-01157 LOCK DATAFILE

October 13th, 2019, posted in Oracle Queries
Share
When executed Auto config  and Clone Config after RMAN Duplicate, following error occured.

 

ERROR: InDbCtxFile.uploadCtx() : Exception : Error executng BEGIN fnd_gsm_util.append_ctx_fragment(:1,:2,:3); END;: 1;
Oracle error -1157: ORA-01157: cannot identify/lock data file 515 – see DBWR trace file
ORA-01110: data file 515: ‘+USUAT_DATA’ has been detected in FND_GSM_UTIL.APPEND_CTX_FRAGMENT.oracle.apps.ad.autoconfig.oam.InDbCtxFileException:
Error executng BEGIN fnd_gsm_util.append_ctx_fragment(:1,:2,:3); END;: 1; Oracle error -1157: ORA-01157: cannot identify/lock data file 515 –
see DBWR trace file
ORA-01110: data file 515: ‘+USUAT_DATA’ has been detected in FND_GSM_UTIL.APPEND_CTX_FRAGMENT.

 

[orajzuat@usuatsrvdbd1bin]$ export PATH=/usr/bin:$PATH
[orajzuat@usuatsrvdbd1bin]$ perl adcfgclone.pl dbconfig /us9001/oracle/11.2.0/appsutil/bpijzuat_usuatsrvdbd1.xml

Beginning dbconfig Apply – Fri Jan 29 14:53:12 2016

…….

  |     30% completed       ERROR: InDbCtxFile.uploadCtx() : Exception : Error executng BEGIN fnd_gsm_util.append_ctx_fragment(:1,:2,:3); END;: 1; Oracle error -1157: ORA-01157: cannot identify/lock data file 515 – see DBWR trace file

ORA-01110: data file 515: ‘+USUAT_DATA’ has been detected in FND_GSM_UTIL.APPEND_CTX_FRAGMENT.

oracle.apps.ad.autoconfig.oam.InDbCtxFileException: Error executng BEGIN fnd_gsm_util.append_ctx_fragment(:1,:2,:3); END;: 1; Oracle error -1157: ORA-01157: cannot identify/lock data file 515 – see DBWR trace file

ORA-01110: data file 515: ‘+USUAT_DATA’ has been detected in FND_GSM_UTIL.APPEND_CTX_FRAGMENT.

        at oracle.apps.ad.autoconfig.oam.InDbCtxFile.uploadCtx(InDbCtxFile.java:249)
at oracle.apps.ad.autoconfig.oam.CtxSynchronizer.uploadToDb(CtxSynchronizer.java:328)
at oracle.apps.ad.tools.configuration.FileSysDBCtxMerge.updateDBCtx(FileSysDBCtxMerge.java:721)
at oracle.apps.ad.tools.configuration.FileSysDBCtxMerge.updateDBFiles(FileSysDBCtxMerge.java:226)
at oracle.apps.ad.context.CtxValueMgt.processCtxFile(CtxValueMgt.java:1690)
at oracle.apps.ad.clone.ApplyDatabase.runCVM(ApplyDatabase.java:3058)
at oracle.apps.ad.clone.ApplyDatabase.runCVMAndAutoConfig(ApplyDatabase.java:3003)
at oracle.apps.ad.clone.ApplyDatabase.doConf(ApplyDatabase.java:649)
at oracle.apps.ad.clone.ApplyDatabase.doApply(ApplyDatabase.java:473)
at oracle.apps.ad.clone.ApplyDatabase.<init>(ApplyDatabase.java:366)
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
at java.lang.reflect.Constructor.newInstance(Constructor.java:501)
at oracle.apps.ad.clone.util.CloneProcessor.run(CloneProcessor.java:67)
at java.lang.Thread.run(Thread.java:637)
–     30% completed

Completed Apply…

Fri Jan 29 14:54:23 2016
Starting database listener for bpijzuat:
Running:

/us9001/oracle/11.2.0/appsutil/scripts/bpijzuat_usuatsrvdbd1/addlnctl.sh start bpijzuat
Logfile: /us9001/oracle/11.2.0/appsutil/log/bpijzuat_usuatsrvdbd1/addlnctl.txt

You are running addlnctl.sh version 120.1.12010000.4
Starting listener process bpijzuat …
Listener bpijzuat has already been started.
addlnctl.sh: exiting with status 0

addlnctl.sh: check the logfile /us9001/oracle/11.2.0/appsutil/log/bpijzuat_usuatsrvdbd1/addlnctl.txt for more information …

[orajzuat@usuatsrvdbd1bin]$

Cause: This error occurs when there is missing or corrupted TEMP tablespace tempfile

Oracle dba,immam dba,dba immam,oracle application,oracle clone



Solution:

Drop and Re-create Tempfile.

Current Temp Tablespace:

select file#,name,ts# from v$tempfile;

FILE# NAME  TS#
1     +USUAT_TMP/usuat/tempfile/temp3.257.902419859   292
2     +USUAT_TMP/usuat/tempfile/temp4.256.902419677   393

select name from v$tablespace where ts# in (292,393)

NAME
Temp1
Temp2

We can see the same error when trying to select the size of the tempfile

select file_name,bytes/1024/1024/1024 from dba_temp_files where tablespace_name=’TEMP1′;

select file_name,bytes/1024/1024/1024 from dba_temp_files where tablespace_name=’TEMP2′;

–ORA-01157: cannot identify/lock data file 513 – see DBWR trace file
–ORA-01110: data file 513: ‘+USUAT_DATA’

Note: Same error as the adconfig

Create temporary tablespace TEMP3 tempfile ‘+USUAT_TMP’ size 20G;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp3;
drop tablespace TEMP1;
drop tablespace TEMP2;

 

Now run adautocfg.sh…
SUCCESS !!

Share
read more no comments

ORA-25153: Temporary Tablespace is Empty has been detected in FND_GSM_UTIL.APPEND_CTX_FRAGMENT

October 9th, 2019, posted in Oracle Queries
Share

While Cloning

 

– 50% completed ERROR: InDbCtxFile.uploadCtx() : Exception : Error executng BEGIN fnd_gsm_util.append_ctx_fragment(:1,:2,:3); END;: 1; Oracle error -25153: ORA-25153: Temporary Tablespace is Empty has been detected in FND_GSM_UTIL.APPEND_CTX_FRAGMENT.
oracle.apps.ad.autoconfig.oam.InDbCtxFileException: Error executng BEGIN fnd_gsm_util.append_ctx_fragment(:1,:2,:3); END;: 1; Oracle error -25153: ORA-25153: Temporary Tablespace is Empty has been detected in FND_GSM_UTIL.APPEND_CTX_FRAGMENT.
at oracle.apps.ad.autoconfig.oam.InDbCtxFile.uploadCtx(InDbCtxFile.java:220)
at oracle.apps.ad.autoconfig.oam.CtxSynchronizer.uploadToDb(CtxSynchronizer.java:328)
at oracle.apps.ad.tools.configuration.FileSysDBCtxMerge.updateDBCtx(FileSysDBCtxMerge.java:721)
at oracle.apps.ad.tools.configuration.FileSysDBCtxMerge.updateDBFiles(FileSysDBCtxMerge.java:226)
at oracle.apps.ad.context.CtxValueMgt.processCtxFile(CtxValueMgt.java:1690)
at oracle.apps.ad.clone.ApplyApplTop.runCVM(ApplyApplTop.java:510)
at oracle.apps.ad.clone.ApplyApplTop.runAutoConfig(ApplyApplTop.java:552)
at oracle.apps.ad.clone.ApplyApplTop.doConf(ApplyApplTop.java:339)
at oracle.apps.ad.clone.ApplyApplTop.doApply(ApplyApplTop.java:382)
at oracle.apps.ad.clone.ApplyApplTop.<init>(ApplyApplTop.java:267)
at oracle.apps.ad.clone.ApplyAppsTier.<init>(ApplyAppsTier.java:105)
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
at oracle.apps.ad.clone.util.CloneProcessor.run(CloneProcessor.java:67)
at java.lang.Thread.run(Thread.java:662)
/ 90% completed

ERROR while running Apply…
Mon Dec 10 09:21:51 2018

ERROR: Failed to execute /u01/EBSUPGR/apps/apps_st/comn/clone/bin/adclone.pl

Please check logfile.Oracle Database,DBA immam,DBA imam,oracle issues,oracle database,oracle clone issues,oracle clone,oracle autoconfig,oracle autoconfig issues

 

Solution :

I checked my temporary tablespace (v$tempfile), Files are there but still I am getting the issue

 

This issue occurred due to one of temporary tablespace group temp file not there.

SQL> select tablespace_name, group_name from DBA_TABLESPACE_GROUPS; 

TABLESPACE_NAME GROUP_NAME
—————————— ——————————
TEMP1 TEMP
TEMP2 TEMP

select file_name from dba_temp_files where TABLESPACE_NAME=’TEMP2′;

no rows selected

 

Add tempfile to temp2 also

SQL> alter tablespace temp2 add tempfile ‘/u01/EBSUPGR/db/apps_st/data/temp02.dbf’ size 5G autoextend on;

Tablespace altered.

 

Run autoconfig db tier and start to do perl adcfgclone.pl appstier again

Share
read more 2 comments

AutoConfig Failing With NegativeArraySizeException 11g Database

October 6th, 2019, posted in Oracle Queries
Share

I encountered Some issues. I encounter this issue while running autoconfig as post upgrade step.

Error stack while running autoconfig :

 

Updating Context file…COMPLETED

Attempting upload of Context file and templates to database...ERROR: 

InDbCtxFile.uploadCtx() : Exception : Error executng BEGIN fnd_gsm_util.upload_context_file(:1,:2,:3,:4,:5); END;: 1; Oracle error -29532: ORA-29532: Java call terminated by uncaught Java exception: java.lang.NegativeArraySizeException has been detected in FND_GSM_UTIL.upload_context_file.
oracle.apps.ad.autoconfig.oam.InDbCtxFileException: Error executng BEGIN fnd_gsm_util.upload_context_file(:1,:2,:3,:4,:5); END;: 1; Oracle error -29532: ORA-29532: Java call terminated by uncaught Java exception: java.lang.NegativeArraySizeException has been detected in FND_GSM_UTIL.upload_context_file.
at oracle.apps.ad.autoconfig.oam.InDbCtxFile.uploadCtx(InDbCtxFile.java:281)
at oracle.apps.ad.autoconfig.oam.CtxSynchronizer.uploadToDb(CtxSynchronizer.java:328)
at oracle.apps.ad.tools.configuration.FileSysDBCtxMerge.updateDBInf(FileSysDBCtxMerge.java:735)
at oracle.apps.ad.tools.configuration.FileSysDBCtxMerge.updateDBFiles(FileSysDBCtxMerge.java:224)
at oracle.apps.ad.context.CtxValueMgt.processCtxFile(CtxValueMgt.java:1663)
at oracle.apps.ad.context.CtxValueMgt.main(CtxValueMgt.java:709)
FAILED

Solution:

Run below query from apps user>

select is_compiled from user_java_methods where name = 'oracle/xml/parser/v2/SAXAttrList' and method_name='addAttr'
and arguments=7;

It should return “YES”, if it return YES then proceed further.

Execute following sql statement from RDBMS oracle home as apps user:

call dbms_java.set_native_compiler_option('optimizerThrowConversion', 'false');

Execute the following sql statement from RDBMS oracle home as sys user:

call dbms_java.set_native_compiler_option('optimizerThrowConversion', 'false');

 

Flush all JIT-compiled code from the database by using following statement as SYS:

truncate table java$mc$;
commit;

After this step restarted database and completed autoconfig without any issues.

Share
read more no comments

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

October 1st, 2019, posted in Oracle EBS Application, Oracle Queries
Share

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
Share
read more no comments

Renaming Data Files in 11g database

September 22nd, 2019, posted in Oracle Queries
Share

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:

Step: 1

[ora@oracle ~]$ sqlplus / as sysdba

SQL> ALTER TABLESPACE SYSAUX OFFLINE NORMAL;
Tablespace altered.

SQL> !



Step: 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


Step: 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.



Step: 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.

Share
read more no comments
« Older Entries
Newer Entries »