Posts Tagged ‘Oracle’

TSPITR Issue By Doing Mount Clone Database

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

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 11.2.0.4 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 8.1.7.4 to 9.2.0.4 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 !!

Share

Enable Export Feature in Custom Forms

April 26th, 2020, posted in Oracle Queries
Share

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.

APP_SPECIAL.ENABLE('FILE.EXPORT', PROPERTY_ON);
Share

Difference Between Bitmap and Character Mode Report

April 12th, 2020, posted in Oracle Queries
Share

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.

Share

REP-0300: ORACLE Error Occurred In Oracle Reports

March 29th, 2020, posted in Oracle Queries
Share

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.

Share

Query To Find Legal Entity Organization Company Code

February 3rd, 2020, posted in Oracle Queries
Share
SELECT
xep.legal_entity_id "Legal Entity ID",
xep.name "Legal Entity",
hr_outl.name "Organization Name",
hr_outl.organization_id "Organization ID",
hr_loc.location_id "Location ID",
hr_loc.country "Country Code",
hr_loc.location_code "Location Code",
glev.flex_segment_value "Company Code"
FROM
xle_entity_profiles xep,
xle_registrations reg,
--
hr_operating_units hou,
-- hr_all_organization_units hr_ou,
hr_all_organization_units_tl hr_outl,
hr_locations_all hr_loc,
--
gl_legal_entities_bsvs glev
WHERE
1=1
AND xep.transacting_entity_flag = 'Y'
AND xep.legal_entity_id = reg.source_id
AND reg.source_table = 'XLE_ENTITY_PROFILES'
AND reg.identifying_flag = 'Y'
AND xep.legal_entity_id = hou.default_legal_context_id
AND reg.location_id = hr_loc.location_id
AND xep.legal_entity_id = glev.legal_entity_id
--
-- AND hr_ou.organization_id = hou.business_group_id
AND hr_outl.organization_id = hou.organization_id
ORDER BY hr_outl.name


Share