Database Notes

Wednesday, 9 February 2022

ORA-00600: internal error code, arguments: [HO define: Long fetch], [], [], [], [], [], [], [], [], [], [], []

Hi,

While fetching the data over dblink between Oracle & MS SQL database, got below error:

SQL> select * from "dbo"."ACC_TAB"@mssql;

select * from "dbo"."ACC_TAB"@mssql

ORA-00600: internal error code, arguments: [HO define: Long fetch], [], [], [], [], [], [], [], [], [], [], []

SQL>

Solution:

You can follow below MOS Doc to resolve this.

ORA-600 [HO define: Long fetch] Error Message When Selecting Data Via Oracle Database Gateways (Doc ID 1224783.1)

In my case, found that base table in MS SQL database "ACC_TAB" having columns datatype "varchar(max)", hence got the error.

In order to resolve this, I have modified the column and defined the column length and created index on the same column to fetch the data faster. Bingo! issue has been resolved. I can able to fetch data over dblink.

alter table [dbo].[ACC_TAB] ALTER COLUMN [Account] varchar(30);

create index acc on [dbname].[d]bo.[ACC_TAB] ([Account]);

SQL> select * from "dbo"."ACC_TAB"@mssql;

--------

901212

Cheers !

Wednesday, 10 March 2021

OPatch failed with error code 1 -- Incompatible usage of java with OUI (32/64 bit).

OPatch failed with error code 1 -- Incompatible usage of java with OUI (32/64 bit).

$ ./opatch lsinventory
OPatch cannot continue because it would not be able to load OUI platform dependent library from the directory "/u02/app/oracle/product/19.3.0.0/db_1/oui/lib/linux". The directory does not exist in the Oracle home.
This could be due to the following reasons.
(1) Incompatible usage of java with OUI (32/64 bit).
(2) Wrong 32-bit Oracle Home installation in 64-bit environment (or) vice-versa.
Please contact Oracle support for more details.
OPatch failed with error code 1

Solution: Mistakenly downloaded 32 bit OPatch installer and post this error, we have downloded 64 bit and issue has been resolved.

CATPROC Oracle Database Packages and Types INVALID after upgrade from 12.1.0.2.0 to 19.3.0.0

CATPROC Oracle Database Packages and Types INVALID after upgrade from 12.1.0.2.0 to 19.3.0.0

Scenario:

We have upgraded our db from 12.1.0.2 to 19.3 and SYS objects got INVALID.

Comp ID Component Status Version Org_Version Prv_Version
------- ---------------------------------- --------- -------------- -------------- --------------
CATPROC Oracle Database Packages and Types INVALID 19.0.0.0.0 12.1.0.2.0 12.1.0.2.0

Solution :

1) Below script will tell us which PLSQL code needs to be validated/compiled first for the CATPROC to get validated too. Please apply its recommendation and then repeat it until no more recommendations are there.

sqlplus / as sysdba
set serveroutput on;
declare
start_time date;
end_time date;
object_name varchar(100);
object_id char(10);
begin
SELECT date_loading, date_loaded into start_time, end_time FROM registry$ WHERE cid = 'CATPROC';
SELECT obj#,name into object_id,object_name FROM obj$ WHERE status > 1 AND (ctime BETWEEN start_time AND end_time OR mtime BETWEEN start_time AND end_time OR stime BETWEEN start_time AND end_time) AND ROWNUM <=1;
dbms_output.put_line('Please compile Invalid object '||object_name||' Object_id '||object_id );
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('CATPROC can be validated now' );
end;
/

2) If the above query returns 'CATPROC can be validated now', then please execute the following command to validate the catproc registry component:
$ sqlplus / as sysdba
exec dbms_registry_sys.validate_catproc;

I have followed 1st step,

SQL> declare
start_time date;
end_time date;
object_name varchar(100);
object_id char(10);
begin
SELECT date_loading, date_loaded into start_time, end_time FROM registry$ WHERE cid = 'CATPROC';
SELECT obj#,name into object_id,object_name FROM obj$ WHERE status > 1 AND 2 (ctime BETWEEN start_time AND end_time OR mtime BETWEEN start_time AND end_time OR stime BETWEEN start_time AND end_time) AND ROWNUM <=1;
dbms_output.put_line('Please compile Invalid object '||object_name||' Object_id '||object_id );
EXCEPTION
WHEN NO_D 3 ATA_FOUND THEN
dbms_output.put_line('CATPROC can be validated now' );
end;
/ 4 5 6 7 8 9 10 11 12 13 14
Please compile Invalid object DBMS_AUTO_INDEX_INTERNAL Object_id 904209
PL/SQL procedure successfully completed.

SQL> alter package DBMS_AUTO_INDEX_INTERNAL compile body;
Warning: Package Body altered with compilation errors.
SQL> show error
Errors for PACKAGE BODY DBMS_AUTO_INDEX_INTERNAL:
LINE/COL ERROR
-------- -----------------------------------------------------------------
7256/5 PL/SQL: SQL Statement ignored
7319/14 PLS-00231: function 'DBMS_AUTO_INDEX_INTERNAL.AUTO_INDEX_ALLOW'
may not be used in SQL
7319/39 PL/SQL: ORA-00904: : invalid identifier
7403/9 PL/SQL: Statement ignored
7403/13 PLS-00364: loop index variable 'CUR' use is invalid
7407/9 PL/SQL: Statement ignored
7407/43 PLS-00364: loop index variable 'CUR' use is invalid
7410/9 PL/SQL: Statement ignored
7410/44 PLS-00364: loop index variable 'CUR' use is invalid

Still this package is INVALID, so followed below steps :

1. connect to the database as sysdba:
sqlplus "/ as sysdba"
2. shutdown immediate
3. startup upgrade
4. spool catalog.log
@$ORACLE_HOME/rdbms/admin/catalog.sql
spool off
5. spool catproc.log
@$ORACLE_HOME/rdbms/admin/catproc.sql
spool off
6. spool utlrp.log
@?/rdbms/admin/utlrp
spool off
7. shutdown immediate
8. startup
9.
Comp ID Component Status Version Org_Version Prv_Version
------- ---------------------------------- --------- -------------- -------------- --------------
CATPROC Oracle Database Packages and Types VALID 19.0.0.0.0 12.1.0.2.0 12.1.0.2.0

Hope this will help you :)

Sunday, 8 March 2020

joxcsys: release mismatch 12.1.0.2.0 1.6 in database (classes.bin) vs 12.1.0.2.191015 1.6 in executable

joxcsys: release mismatch 12.1.0.2.0 1.6 in database (classes.bin) vs 12.1.0.2.191015 1.6 in executable

Observed multiple errors in one of the development database.

Solution :

[oracle@devoracledb ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sun Mar 8 11:18:02 2020

Copyright (c) 1982, 2014, Oracle. 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

SYS on 08-MAR-20 @ db_UAT2 > select dbms_java.get_jdk_version() from dual;

select dbms_java.get_jdk_version() from dual
*
ERROR at line 1:
ORA-29548: Java system class reported: release of Java system classes in the
database (12.1.0.2.0 1.6) does not match that of the oracle executable
(12.1.0.2.191015 1.6)

SYS on 08-MAR-20 @ db_UAT2 > @?/javavm/install/update_javavm_db.sql
SYS on 08-MAR-20 @ db_UAT2 > SET FEEDBACK 1
SYS on 08-MAR-20 @ db_UAT2 > SET NUMWIDTH 10
SYS on 08-MAR-20 @ db_UAT2 > SET LINESIZE 80
SYS on 08-MAR-20 @ db_UAT2 > SET TRIMSPOOL ON
SYS on 08-MAR-20 @ db_UAT2 > SET TAB OFF
SYS on 08-MAR-20 @ db_UAT2 > SET PAGESIZE 100
SYS on 08-MAR-20 @ db_UAT2 >
SYS on 08-MAR-20 @ db_UAT2 > alter session set "_ORACLE_SCRIPT"=true;

Session altered.

SYS on 08-MAR-20 @ db_UAT2 >
SYS on 08-MAR-20 @ db_UAT2 > -- If Java is installed, do CJS.
SYS on 08-MAR-20 @ db_UAT2 >
SYS on 08-MAR-20 @ db_UAT2 > -- If CJS can deal with the SROs inconsistent with the new JDK,
SYS on 08-MAR-20 @ db_UAT2 > -- the drop_sros() call here can be removed.
SYS on 08-MAR-20 @ db_UAT2 > call initjvmaux.drop_sros();

Call completed.

SYS on 08-MAR-20 @ db_UAT2 >
SYS on 08-MAR-20 @ db_UAT2 > create or replace java system;
2 /

Java created.

SYS on 08-MAR-20 @ db_UAT2 >
SYS on 08-MAR-20 @ db_UAT2 > update dependency$
2 set p_timestamp=(select stime from obj$ where obj#=p_obj#)
3 where (select stime from obj$ where obj#=p_obj#)!=p_timestamp and
4 (select type# from obj$ where obj#=p_obj#)=29 and
5 (select owner# from obj$ where obj#=p_obj#)=0;

0 rows updated.

SYS on 08-MAR-20 @ db_UAT2 >
SYS on 08-MAR-20 @ db_UAT2 > commit;

Commit complete.

SYS on 08-MAR-20 @ db_UAT2 >
SYS on 08-MAR-20 @ db_UAT2 > alter session set "_ORACLE_SCRIPT"=false;

Session altered.

SYS on 08-MAR-20 @ db_UAT2 >
SYS on 08-MAR-20 @ db_UAT2 > SYS on 08-MAR-20 @ db_UAT2 > SYS on 08-MAR-20 @ db_UAT2 >
SYS on 08-MAR-20 @ db_UAT2 >
SYS on 08-MAR-20 @ db_UAT2 > ---- VERIFIED

SYS on 08-MAR-20 @ db_UAT2 > select dbms_java.get_jdk_version() from dual;

DBMS_JAVA.GET_JDK_VERSION()
-------------------------------------------------------------
1.6.0_211

1 row selected.

SYS on 08-MAR-20 @ db_UAT2 >

Monday, 17 February 2020

ORA-12012: error on auto execute of job ORA-12008: error in materialized view refresh path ORA-01722: invalid number

Errors in file /u01/app/oracle/diag/rdbms/db/db/trace/db_j000_31458.trc:
ORA-12012: error on auto execute of job 111816
ORA-12008: error in materialized view refresh path
ORA-01722: invalid number
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2821
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 3058
ORA-06512: at "SYS.DBMS_IREFRESH", line 687
ORA-06512: at "SYS.DBMS_REFRESH", line 195
ORA-06512: at line 1

Approach :

select job, schema_user, what from dba_jobs where job='111816';

JOB SCHEMA_USER WHAT
111816 DB_PROD dbms_refresh.refresh('"DB_PROD"."MV_CUST"');

Open the above mentioned materialized view "MV_CUST", in select query, one of the condition getting failed with "INVALID NUMBER". By digging down, found that, in one column having (its datatype VARCHAR) instead of number there was CHARACTER and this row getting compared with number.

Monday, 10 February 2020

Offline installation of SQL Server 2017 on Linux

Offline installation of SQL Server 2017 on Linux::

1) Meet the pre-requisites

2) Download the rpm package for the SQL Server database engine. You can download the required rpm packages from the release note (https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-release-notes?view=sql-server-ver15) or from Microsoft Repositories.

3) Create a directory for SQLINSTALL & Copy the downloaded rpm to the Linux machine using WinSCP.

4) # systemctl status mssql-server

5) Go ahead with rpm installation

6) Complete installation by executing below command & selected Evaluation pack & enter admin password.

# sudo /opt/mssql/bin/mssql-conf setup

7) Check the services :

8) Now you can connect to DB through the SSMS tool.

Sunday, 2 February 2020

MySQL to Oracle Characterset Problem -- Solved

In our environment, one of the Oracle production DB fetches MySQL DB data for reporting purposes. Recently application team reported that they are getting data WITH SPACE in between.

DOC	REF	REFNO	REMARKS
R C	R A	1 0 1 2 0	R E C E I P T
R C	R A	1 0 1 2 0	D E P O S I T

Solution:

1. Check Character set of MySQL database.

mysql> show variables like "character_set_database";

+------------------------+--------+

| Variable_name | Value |

+------------------------+--------+

| character_set_database | latin1 |

+------------------------+--------+

1 row in set (0.00 sec)

2. Insert below line in odbc.ini file

set LD_LIBRARY_PATH=/usr/lib64/lib

Description = MySQL Production database

Trace = ON

TraceFile = /tmp/stderr

SERVER = 10.xx.XX.XX

USER = root

PASSWORD = XXXX

PORT = 3306

DATABASE = MYSQLPROD

Driver=/home/oracle/oracleTOmysql/lib/libmyodbc8w.so

CHARSET = latin1

3. Fetch the data again from oracle DB. Now getting proper data from MySQL.

DOC	REF	REFNO	REMARKS
RC	RA	10120	RECEIPT
RC	RA	10120	DEPOSIT

Hope this will help 😊