Posts Tagged ‘Oracle’

APPS_TS_TX_DATA and APPS_TS_TX_IDX Size Keep On Increasing

May 31st, 2021, posted in Oracle Queries

APPS_TS_TX_DATA and APPS_TS_TX_IDX Size Keep On Increasing


You can truncate these tables for deleting entire data without any back up.The truncate is more faster compare to delete command because data in the table is not write to roll back segments.

SQL>truncate table xla.XLA_DIAG_SOURCES;
SQL>truncate table xla.XLA_DIAG_EVENTS;

Turn OFF theSLA: Diagnostics Enabled profile option at all levels.

Check the tables, tablespace and archive logs status


TSPITR Issue By Doing Mount Clone Database

November 15th, 2020, posted in Oracle EBS Application, Oracle Queries

alter database mount clone database is a statement that forces all datafiles to be put OFFLINE when mounting the target instance.ORA-19588: archived log RECID 1003 STAMP 2001986 is no longer valid,oracle error,imam dba,dba imam,immam dba,dba immam,rman error
You’ll see it’s being used in the background when you run RMAN commands like:

transport tablespace
recover tablespace .. auxiliary destination ..
“recover table”  (12c feature)

In my case, i was trying to recover an dropped table using “Tablespace Point In Time Recovery” by following the MOS note “How to Recover From a DROP / TRUNCATE / DELETE TABLE with RMAN (Doc ID 223543.1)“. The basic steps are:

– create pfile
– restore controlfile
– mount with “alter database mount clone database;”
– restore and recover a subset of the database with an RMAN script similar to:

RMAN> connect target /
        run {
        allocate channel c1 device type DISK;
        set until time "to_date( '13-01-2016 13:50', 'DD-MM-YYYY HH24:MI')";
        set newname for datafile 1 to "+RECO";
        set newname for datafile 3 to "+RECO";
        set newname for datafile 4 to "+RECO";
        set newname for datafile 23 to "+RECO";
        restore tablespace SYSTEM, UNDOTBS01, UNDOTBS02, TOOLS;
        switch datafile all;
        sql "alter database datafile 1,3,4,23 online";
        recover database skip forever tablespace TEMP,INDX,USERS,etc.;
        sql "alter database rename file ''+RECO/xxx/onlinelog/group_1.281.739547347'' to ''+RECO''";
        sql "alter database rename file ''+RECO/xxx/onlinelog/group_2.282.739547353'' to ''+RECO''";
        sql "alter database rename file ''+RECO/xxx/onlinelog/group_3.283.739547359'' to ''+RECO''";
        sql "alter database rename file ''+RECO/xxx/onlinelog/group_4.284.739547365'' to ''+RECO''";
release channel c1;

After completing the required steps, recovery raised the following error:

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup

Of course i got the error when tried to open with resetlogs. After struggling with possible causes, i found an internal support note similar with my issue: “ORA-1547 After Recovery When Database Was Mounted As Clone (Doc ID 252181.1)“. The interesting part was this note was published at Oct 23, 2003 for versions to and the fix was:

Use the following workaround until the bug gets resolved :
Do not mount the database as CLONE as in :
SQL> alter database mount clone database;
but mount is as normal database with :
SQL> alter database mount;

But there’s no bug number stated in the note. Even i thought that mysterious bug must have been fixed many years ago, desperation made me try the workaround with mounting the instance with “alter database mount” and manually taking the required datafiles offline. Suprisingly it worked and i was able to open the database with resetlogs.

As a result, it’s fine for me that RMAN uses “mount clone database” statement internally, however when I’ll do TSPITR again you can be sure that I’ll mount the instance with the way I used to !!


Enable Export Feature in Custom Forms

April 26th, 2020, posted in Oracle Queries

Occurred In Oracle Reports,Oracle Reports,Oracle,Reports,Enable Export Feature in Custom Forms,Enable Export Feature, in Custom Forms,Oracle Custom Forms,Oracle Forms,In Order to Enable Export option, available in Oracle Application forms to export the data into Excel file.
In the custom form at form level trigger write the below code in When-New-Form-Instance trigger.


Difference Between Bitmap and Character Mode Report

April 12th, 2020, posted in Oracle Queries

Here is an example to help explain how Oracle Reports are designed and printed in both the bitmap and character-mode environments.Difference Between Bitmap and Character Mode Report, Occurred In Oracle Reports,Oracle Reports,Oracle,Reports,Difference Between Bitmap and Character Mode Assume you wish to print “Cc” where “C” is a different font and a larger point size than “c” and is in boldface type (where “c” is not). In Oracle Reports Designer, bitmap mode, you can make “C” bold and in a different font and point size than “c”.

This is because you are generating postscript output. Postscript is a universal printer language and any postscript printer is able to interpret your different design instructions. In Oracle Reports Designer, character mode, the APPLICATIONS STANDARDS REQUIRE the report to be designed in ONE FONT/ ONE CHARACTER SIZE. Character mode reports generate ASCII output.

In ASCII you cannot dynamically change the font and character size. The standard is in effect so a report prints as identically as possible from both conventional and postscript printers.


REP-0300: ORACLE Error Occurred In Oracle Reports

March 29th, 2020, posted in Oracle Queries

Imam Dba, Immam Dba, Oracle DBA,

If the below error occurs

REP-0300: ORACLE error occurred.
REP-0069: Internal error
REP-57054: In-process job terminated:Terminated with error: 
REP-300: ORACLE error occurred.

Answer :
Please check the report parameter data types.
It should be correct to overcome the issue.