HTTP 500: Internal Server Error when login to a new cloned environment

Environment

Oracle e-Business Suite 12.1.3 (Oracle EBS)

Oracle Linux 5.7

Symptoms

The following error occurs when trying to login to the new cloned environment.

500 Internal Server Error

File application.log in $LOG_HOME/ora/10.1.3/j2ee/oacore/oacore_default_group_1 shows

Caused by: oracle.apps.jtf.base.resources.FrameworkException:

ORA-01578: ORACLE data block corrupted (file # 12, block # 180784)

ORA-01110: data file 12: '/data/oradata/TEST12/APPS_TS_SUMMARY01.dbf'

ORA-26040: Data block was loaded using the NOLOGGING option

Solution

To identify the corrupted object use:

select s.*

from dba_extents s

where file_id = 12 -- change

and 180784 -- change

between block_id and (block_id + blocks -1);

It happened to be APPLSYS.WF_LOCAL_USER_ROLES

The solution is described in Oracle Support Doc ID 781413.1

sqlplus apps/password

set serveroutput on size 100000;

declare

begin

WF_LOCAL_SYNCH.BULKSYNCHRONIZATION(

P_ORIG_SYSTEM => 'ALL',

P_PARALLEL_PROCESSES => null,

P_LOGGING => null,

P_RAISEERRORS => TRUE);

exception

when others then

dbms_output.put_line('sqlerrm = ' || sqlerrm);

end;

/

Useful info

Partitions in Workflow Local Tables are Automatically Switched to NOLOGGING (Doc ID 433280.1)

Http 500 : Internal Server Error When Login To A New Cloned Environment (Doc ID 781413.1)

How to find whether an oracle database patch was applied

Using SQL:
select * from sys.registry$history;
It is useful for patch set levels, but not for a particular bug/patch.

OPatch, in Linux
$ORACLE_HOME/OPatch/opatch lsinventory
$ORACLE_HOME/OPatch/opatch lsinventory | grep something
some useful flags
-bugs_fixed Reports bugs fixed by installed patches with bug descriptions and extra info
-patch ..order based on installed time
-patch_id ..order based on patch numbers

In OEM 12c
Targets > All Targrets > 
Targer Type > Others > Oracle Home > click your Target
on Oracle Home page, see tab Patches Applied. In the table below, you can find Bugs Fixed, Files and Components.

For multiple targets, you can create an OEM job to run a SQL script on multiple databases or opatch command on multiple hosts.

In OEM 12c
Enterprise > Configuration > Inventory and Usage Details
Show "Database Installations"
In the table below, column "Patches Applied", click "Yes" (or No, but in this case there is nothing to see)

Compiling public synonyms

Environment: Oracle database 11.2, Toad for Oracle 12.6.0.53

There is an invalid public synonym F_GET_PARTY_NAME. The target object exists and valid.
select s.* , o.*
--'alter public synonym ' || synonym_name || ' compile;'
from dba_synonyms s, dba_objects o
where s.owner=o.owner and s.synonym_name=O.OBJECT_NAME
and o.status ='INVALID' 
and s.owner='PUBLIC'
;
When connected as SYSTEM or SYS in “TOAD Script runner” or in “TOAD for Oracle 12.6.0.53” (looks like a TOAD’s bug)
alter public synonym F_GET_PARTY_NAME compile;

alter public synonym
Error at line 1
ORA-00995: missing or invalid synonym identifier

F_GET_PARTY_NAME compile;
Error at line 1
ORA-00900: invalid SQL statement

When connected as SYSTEM in sqlplus
alter public synonym F_GET_PARTY_NAME compile;
alter public synonym F_GET_PARTY_NAME compile
*
ERROR at line 1:
ORA-01031: insufficient privileges

When connected as SYS in sqlplus
SQL> alter public synonym F_GET_PARTY_NAME compile;

Synonym altered.

Validating CSV file with regular expressions / Проверка CSV файла регулярным выражением

The CSV file does not contain double quote.
14 commas (field delimiter) are expected in each row.
A row delimiter is CRLF (\r\n).

Searching for a row with 15 commas in Notepad++ :
^([^,\r]*,){15}
[^,\r]* matches any char except comma and CR

ORACLE data block corrupted after restoring database

Environment: Oracle database 11.2, Oracle Linux 5.7.

Test database was restored from production backup. Some object have NOLOGGING, so recovery for such object will fail to roll changes forward.
When object is used, the following errors happen

ORA-01578: ORACLE data block corrupted (file # 42, block # 1029253)
ORA-01110: data file 42: '/data001/oradata/CDRSIT/cdrsit_STG_DATA_02.dbf'

Find affected segment
select segment_name, segment_type, owner
from dba_extents
where file_id = 42 -- change
and 1029253 -- change
between block_id and (block_id + blocks -1);

as in this case it is an index, we can (make unusable and rebuild) or (drop and recreate).
ALTER INDEX owner.name UNUSABLE;
ALTER INDEX owner.name REBUILD;

Modifying collection schedule for OEM metric Tablespace Allocation.

Environment:
Oracle DB 11.2, Standard Edition One with DB console.
Windows 2003 R2, 32bit

Action:
Default metric (Tablespace Allocation) collection interval is "Every 7 Days", which can be found on page "All Metrics" in DB console.

Go to %ORACLE_HOME%\sysman\admin\default_collection
and edit file database.xmlp

Find "Category: Tablespace Allocation" and few rows down:
      IntervalSchedule INTERVAL="7" TIME_UNIT="Day"

Change the interval number. Time unit can also be Hr or Min. Save the file.
Restart the oracle agent.
In this particular case by restarting windows service OracleDBConsoleSID, which bounces the agent as well.

"All Metrics" in DB console will still show 7 days interval. Check the real metric collection by the following SQL.
select * from  SYSMAN.MGMT$METRIC_DETAILS
where METRIC_LABEL in ('Tablespace Allocation') and key_value='SYSTEM'
order by collection_timestamp desc;

Configuring Oracle Database 11g Gateway for ODBC MS SQL Server

Environment

Oracle database 11.2
Oracle Linux Server release 5.7
Microsoft® ODBC Driver 11 for SQL Server - RedHat Linux
Microsoft SQL Server 2005,2008R2

Installing ODBC driver

Install the driver according to MS http://www.microsoft.com/en-us/download/details.aspx?id=36437

To verify that the ODBC Driver on Linux was registered successfully, execute the following command:
odbcinst -q -d -n "ODBC Driver 11 for SQL Server"

edit ~/.odbc.ini
and add

[infraUAT]
Driver=ODBC Driver 11 for SQL Server
Description=My Sample ODBC Database Connection
Trace=Yes
Server=gtpsql2
Port=1433
Database=InfraEnt_ITSD_UAT

"Driver" has to be same as output of odbcinst -q -d, which comes from /etc/odbcinst.ini

Test it
odbcinst -q -s
[infraUAT]

isql -v infraUAT user password
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> quit

Configuring Gateway

cd $ORACLE_HOME/hs/admin

If you use initdg4odbc.ora, then dg4odbc will be SID.

edit initdg4odbc.ora

S_FDS_CONNECT_INFO = infraUAT

HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so

# you need to match HS_LANGUAGE to SQLS

HS_LANGUAGE=AMERICAN_AMERICA.WE8MSWIN1252

# you might need the following HS_

#HS_KEEP_REMOTE_COLUMN_SIZE=LOCAL

#HS_NLS_LENGTH_SEMANTICS=CHAR

#nvarchars of a SQL Server are UCS2 character set

#HS_NLS_NCHAR=UCS2

# ODBC specific environment variables

set ODBCINI=/home/oracle/.odbc.ini

HS_LANGUAGE needs to match code page of SQL Server.

When Oracle use unicode AL32UTF8, it will fail to connect. See Doc ID 756186.1

ERROR at line 1:

ORA-28500: connection from ORACLE to a non-Oracle system returned this message:

[

edit listener.ora and add new SID_DESC, where SID matches initdg4odbc.ora

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

        (SID_NAME = dg4odbc)

        (ORACLE_HOME = /opt/oracle/product/se1/11.2.0.3)

        (PROGRAM = dg4odbc)

        (ENVS='LD_LIBRARY_PATH=/usr/lib64:/opt/microsoft/msodbcsql/lib64:/opt/oracle/product/se1/11.2.0.3/lib')

    )

  )

Restart listener

configure  tnsnames.ora

HS1 =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = tcp)(HOST = )(PORT = 1521))

    )

    (CONNECT_DATA =

      (SID = dg4odbc)

    )

    (HS=OK)

  )

Configure DB Link

CREATE PUBLIC DATABASE LINK INFRA

 CONNECT TO DSUSER

 IDENTIFIED BY password

 USING 'hs1';

Problem 1 - code page conversion

Some symbols are not converted correctly, e.g. Microsoft double quote or single quote. I have not tested non-latin alphabets.

SELECT short_problem  FROM CL_CALL_LOGGING@infra where call_number=4234;

output

The printer on Level 5 is showing ‘perform printer maintenance’. 

Solution 1

as Oracle NLS_CHARACTERSET = AL32UTF8 (check view NLS_DATABASE_PARAMETERS), create a view in SQLS to CAST this column to unicode, then you can select from this view from Oracle.

CREATE VIEW TEST_VIEW1 AS

SELECT CALL_NUMBER, CAST(SHORT_PROBLEM AS NVARCHAR(200)) AS SHORT_PROBLEM

FROM DBO.CL_CALL_LOGGING;

in Oracle

SELECT CALL_NUMBER, SHORT_PROBLEM FROM TEST_VIEW1@INFRA WHERE CALL_NUMBER=4234;

output

The printer on Level 5 is showing ‘perform printer maintenance’.

Problem 2 - multibyte characters

ORA-28500: connection from ORACLE to a non-Oracle system returned this message:

[Microsoft][ODBC Driver 11 for SQL Server]String data, right truncation {01004}

ORA-02063: preceding 2 lines from INFRA

If in SQLS a column is described as VARCHAR(200) and a value length is 200 and it contains some symbols which become multi-byte, then in Oracle it becomes longer than 200 bytes.

Solution 2

The same as Solution 1, CAST it to unicode on SQLS.

If you create similar or the same table in Oracle, define length in chars, e.g. VARCHAR(200 char)

It does not have to be NVARCHAR as in my case oracle DB is already UTF8.

Problem 3 - varchar(max)

When selecting varchar(max) from SQLS:

ORA-28500: connection from ORACLE to a non-Oracle system returned this message:

[Microsoft][ODBC Driver 11 for SQL Server]String data, right truncation {01004}

ORA-02063: preceding 2 lines from INFRA

Solution 3.1

I haven't found a good solution for this. You can CAST(COLNAME AS TEXT) or as NTEXT in SQLS, in thin case you receive LONG type in Oracle (can be saved and converted to CLOB). Which is ok for most values (Latin characters), but it still has wrong conversion for multi-byte/non-latin characters.

Solution 3.2 - other ODBC drivers

try other drivers, e.g. Easysoft or Datadirect. I haven't tested it.

Solution 3.3 - non Oracle ETL

IBM Datastage works ok, when selecting as unicode CAST(col as NVARCHAR(max)) and keeping as LongNVarchar in DataStage to avoid Datastage conversion. It inserts into Oracle CLOB and looks ok.

Microsoft SSIS converts correctly, if destination table in Oracle is defined as NCLOB.

Documentation

Error Ora-28500 and Sqlstate I Issuing Selects From a Unicode Oracle RDBMS With Dg4odbc To Non-Oracle Databases Using the UnixODBC Driver Manager (Doc ID 756186.1)

How to Configure DG4ODBC on 64bit Unix OS (Linux, Solaris, AIX, HP-UX Itanium) to Connect to Non-Oracle Databases Post Install (Doc ID 561033.1)