Tag Archives: Oracle

[Solved] oracle Execute netca Error: UnsatisfiedLinkError exception loading native library: njni11

@Write at the beginning: after testing, this kind of problem occurs when centos8 installs Oracle 11g R2

@There will also be no response after lsnrctl start. After checking the lsnrctl file, it is found that the file is also 0kb

@It may be a compatibility problem. It is recommended to install Oracle help center with the system recommended by Oracle

1. Execute netca/silent/responsefile/data/Oracle/response/netca RSP error unsatisfiedlinkerror exception loading native library: njni11

[oracle@oracle ~]$ netca /silent /responsefile /datas/oracle/response/netca.rsp
UnsatisfiedLinkError exception loading native library: njni11
java.lang.UnsatisfiedLinkError: /datas/oracle/product/11.2.0/db_1/lib/libnjni11.so: /datas/oracle/product/11.2.0/db_1/lib/libclntsh.so.11.1: file too short
java.lang.UnsatisfiedLinkError: jniGetOracleHome
	at oracle.net.common.NetGetEnv.jniGetOracleHome(Native Method)
	at oracle.net.common.NetGetEnv.getOracleHome(Unknown Source)
	at oracle.net.ca.NetCALogger.getOracleHome(NetCALogger.java:230)
	at oracle.net.ca.NetCALogger.initOracleParameters(NetCALogger.java:215)
	at oracle.net.ca.NetCALogger.initLogger(NetCALogger.java:130)
	at oracle.net.ca.NetCA.main(NetCA.java:404)

Error: jniGetOracleHome
Oracle Net Services configuration failed.  The exit code is 1

2. View/data/Oracle/product/11.2.0/db_1/lib/libclntsh.so.11.1 documents found 0kb

3. Find libclntsh.so.11.1 file In the system (the installation information directory orainventory happens to exist)

find/-name libclntsh.so.11.1

4. Cover the damaged libclntsh.so.11.1 documents

cp /datas/oracle/product/11.2.0/db_1/inventory/backup/2021-04-27_02-37-24PM/Scripts/ext/lib/libclntsh.so.11.1 /datas/oracle/product/11.2.0/db_1/lib/libclntsh.so.11.1

5. Execute the netca command again and solve the problem successfully

[Solved] 64-bit Python calls 32-bit Oracle client error: Cx_Oracle.DatabaseError: DPI-1047

problem

The operating system is 64 bit Python and 32 bit Oracle client. For other reasons, the Oracle client cannot be changed to 64 bit, resulting in Cx_Oracle64 bit cannot be used, CX_Oracle32 bit cannot be installed.

reason

Under Windows environment, if 64 bit Python is installed, CX_Oracle is used, the 64 bit Oracle client is called by default. In this case, we can only install Cx_Oracle win_Amd 64-bit version.

Solution:

1. First install CX_Oracle matching the current Python version, my is Python 3.8. The installed version is: Cx_Oracle-8.3.0-cp38-cp38-win_amd64.whl

2. Download the relevant Oracle instantclient (instant client) and unzip it to a folder convenient for calling

Download address: https://www.oracle.com/database/technologies/instant-client/winx64-64-downloads.html

The version I downloaded is: instantclient-basiclite-windows-x64-19.13.0.0.0dbru.Zip, you can try several times, there is always one for you

3. Unzip the instantclient and change the environment variable in the Python code to adjust the Oracle driver location.

My unzipped address is:

E:\software\Python\instantclient-basiclite-windows.x64-19.13.0.0.0dbru

The code adjustment method is

import os

os.environ[‘path’] =  r’E:\software\Python\instantclient-basiclite-windows.x64-19.13.0.0.0dbru\instantclient_19_13′

import cx_Oracle

Now CX_Oracle can call normally

[Solved] python Connect Oracle Error: DPI-1047

Prompt when Python connects to Oracle:: databaseerror: dpi-1047: cannot locate a 64 bit Oracle Client Library: “the specified module could not be found” See https://oracle.github.io/odpi/doc/installation.html#windows for help

Enter according to the error prompt https://oracle.github.io/odpi/doc/installation.html#windows, download the corresponding instant client package, then unzip the configuration environment variables and run it again. Generally, it can run successfully to solve the problem.

If dpi-104 error is still prompted, you need to check whether VC + + is installed. The check method is to run genezi.com under the downloaded instant client directory Exe program. For example: C:\Oracle\instantclient_21_3\genezi.exe. If there is an error during execution, it means VC_redist is not installed correctly. reenter https://oracle.github.io/odpi/doc/installation.html#windows Page, download the corresponding VC according to the corresponding Oracle version_redist.exe version.

Download and install to solve the problem.

[Solved] MYSQL Error: SQLErrorCodes loaded: [DB2, Derby, H2, HSQL, Informix, MS-SQL, MySQL, Oracle, PostgreSQL, Sybase, Hana]

1. When calling an interface, an interface reports an error

o.s.b.f.xml. XmlBeanDefinitionReader: Loading XML bean definitions from class path resource [org/springframework/jdbc/support/sql-error-codes.xml]

o.s.jdbc. support. SQLErrorCodesFactory : SQLErrorCodes loaded: [DB2, Derby, H2, HSQL, Informix, MS-SQL, MySQL, Oracle, PostgreSQL, Sybase, Hana]

Screenshot information:

2. Solution ideas

(1) Look at the problem description, it is a database problem.

(2) Check the interface log, through the log, you can initially locate the problem in the “==> Preparing: REPLACE into” sql statement, while the log returned the sql statement’s input “==> Parameters”

(3) According to the parameters, the sql statement was executed in Navicat, and the result came out because of the problem of foreign keys in the database.

3.Summary

The problem how to solve is not the focus, the focus is to encounter such problems, first check the logs, locate where the problem, and then targeted to solve.

There are several common problems in the degree, such as field naming errors, type mismatch, data problems.

At the end of the day, according to the logs, execute the problem statement in the visual sql execution tool. The problem can be solved.

[Solved] oracle Install Error: Checking monitor: must be configured to display at least 256 colors

Solution:

Method 1:
Run the following command to install vnc
yum install tigervnc tigervnc-serverv
Method 2:
If you execute ./runInstaller, it still reports an error as

Could not execute auto check for display colors using command /usr/bin/xdpyinfo <<failed

Install xdpyinfo

yum install xdpyinfo

[Oracle Scheduled Backup Failed] Oracle Backup manually Error: ORA-31634ORA-31664

Automatic backup fails, manual execution of the script reports an error ORA-31634 ORA-31664

1. Problem discovery:
The daily timing data pump export job is not imported normally. Checking the import.log, it is actually empty.
2. Problem tracking:
Manually call the back.sh script to export, and an error will be reported after running for a few seconds:
ORA-31634: job already exists
ORA-31664: unable to construct unique job name when defaulted
3. Find and solve
1) Analyze the error and how to solve it:

ORA-31634: job already exists
Cause: Job creation or restart failed because a job having the selected name is currently executing. This also generally indicates that a Master Table with that job name exists in the user schema. Refer to any following error messages for clarification.
Action: Select a different job name, or stop the currently executing job and re-try the operation (may require a DROP on the Master Table).
ORA-31664: unable to construct unique job name when defaulted
Cause: The job name was defaulted, and the name creation algorithm was unable to find a unique job name for this schema where the table name (for the master table) didn”t already exist.
Action: Specify a job name to use or delete some of the existing tables causing the name conflicts.
The analysis shows that the unique name of the job called does not exist when expdp is running. Query dba_datapump_jobs. Under normal circumstances, the job_name field only has two rows SYS_IMPORT_SCHEMA_01 and SYS_IMPORT_SCHEMA_02, and this table has 135 rows. The solution is to delete the table whose field state is NOT RUNNING in the table.

2) Solution
The SQL statement generated to clear the master table is:
select’drop table ‘|| owner_name ||’.’ || job_name ||’;’ from dba_datapump_jobs where state =’NOT RUNNING’
After manually executing the generated SQL statement, query dba_datapump_jobs again to confirm whether there is a master table whose state is NOT RUNNING, and repeat the generated SQL statement for execution.
Manually call the import script again and import normally.

Error attempting to get column ‘xxx’ from result set. Cause: java.sql.sqlexception: unable to convert to internal representation. Error resolution during Oracle database query

Overall error log details:

Org.springframework.jdbc.uncategorized sqlexception: error attempting to get column ‘xxx’ from result set. Cause: java.sql.sqlexception: unable to convert to internal representation
; uncategorized SQLException; SQL state [99999]; error code [17059]; Cannot convert to internal representation; Nested exception is java.sql.sqlexception: cannot convert to internal representation

There are many errors that can lead to this problem. Let me say that I made the error because I used the in the Lombok plug-in  @ Builder   This annotation

The first solution is to kill this annotation. When assigning an object, you can directly xxx.setxxx;

The second solution is to add these two annotations @ allargsconstructor on Lombok    @ NoArgsConstructor   Add a construction method that does not participate in all parameters,

Simply put, @ builder is added to mybatis to query without parameters   This annotation results in the absence of a parameterless structure, resulting in an error.

 

Summary, problems

Solution 1: directly kill the @ builder annotation on the entity class

Solution 2: add @ allargsconstructor @ noargsconstructor to the entity class   annotation



Oracle: How to use errorstack to track the ora error of the client

Oracle uses errorstack to track the ora error of the client

preface

Recently, a business module reported an error ora-00942: table or view doors not exist. I want to help find out which SQL is causing it.

Generally, if the ora error of the client is simply caused by the client, such ora error will not be recorded in the database server.

For example, if an error is reported when the table or view does not exist, if the client deliberately checks a non-existent table and performs a large number of operations, considering concurrency, if the server records logs, a large number of logs will be generated.

Or when the client executes an SQL with syntax errors, such as ora-00936: missing expression, the same is true.

However, you can use the errorstack switch to turn on the monitoring of an ora error number and turn it off after obtaining enough information.

experiment

Take the ora-00942 error report as an example, use the sys user to turn on the monitoring switch with error number 942.

15:23:27 SYS@test(1218)> alter system set events '942 trace name errorstack forever, level 3';

System altered.

Elapsed: 00:00:00.00

Warning log at this time:

Thu Sep 30 15:25:47 2021
OS Pid: 21259 executed alter system set events '942 trace name errorstack forever, level 3'

Then ordinary users log in and execute a query to query a non-existent table.

15:23:17 ZKM@test(432)> select * from no;
select * from no
              *
ERROR at line 1:
ORA-00942: table or view does not exist


Elapsed: 00:00:04.16

Warning log at this time:

Thu Sep 30 15:27:47 2021
Errors in file /u01/app/oracle/diag/rdbms/test/test/trace/test_ora_21173.trc:
Errors in file /u01/app/oracle/diag/rdbms/test/test/trace/test_ora_21173.trc:
ORA-00942: table or view does not exist
Thu Sep 30 15:27:49 2021
Dumping diagnostic data in directory=[cdmp_20210930152749], requested by (instance=1, osid=21173), summary=[abnormal process termination].
Dumping diagnostic data in directory=[cdmp_20210930152752], requested by (instance=1, osid=21173), summary=[abnormal process termination].

Turn off the switch in time:

15:30:45 SYS@test(1218)> alter system set events '942 trace name errorstack off';

System altered.

Elapsed: 00:00:00.00

To view the trace file:

Trace file /u01/app/oracle/diag/rdbms/test/test/trace/test_ora_21173.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1
System name:    Linux
Node name:      testdb
Release:        5.4.17-2011.6.2.el7uek.x86_64
Version:        #2 SMP Thu Sep 3 14:09:14 PDT 2020
Machine:        x86_64
VM name:        VMWare Version: 6
Instance name: test
Redo thread mounted by this instance: 1
Oracle process number: 129
Unix process pid: 21173, image: oracle@testdb (TNS V1-V3)


*** 2021-09-30 15:27:47.079
*** SESSION ID:(432.4017) 2021-09-30 15:27:47.079
*** CLIENT ID:() 2021-09-30 15:27:47.079
*** SERVICE NAME:(SYS$USERS) 2021-09-30 15:27:47.079
*** MODULE NAME:(SQL*Plus) 2021-09-30 15:27:47.079
*** ACTION NAME:() 2021-09-30 15:27:47.079

dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=3, mask=0x0)
----- Error Stack Dump -----
----- Current SQL Statement for this session (sql_id=2611xga5f8w95) -----
select * from no

----- Call Stack Trace -----
calling              call     entry                argument values in hex
location             type     point                (?means dubious value)
-------------------- -------- -------------------- ----------------------------

*** 2021-09-30 15:27:47.306
skdstdst()+41        call     kgdsdst()            000000000 ?000000000 ?
                                                   7FFDE0D2CA80 ?7FFDE0D2CB58 ?
                                                   7FFDE0D31600 ?000000002 ?
ksedst1()+103        call     skdstdst()           000000000 ?000000000 ?
                                                   7FFDE0D2CA80 ?7FFDE0D2CB58 ?
                                                   7FFDE0D31600 ?000000002 ?
ksedst()+39          call     ksedst1()            000000000 ?000000001 ?
                                                   7FFDE0D2CA80 ?7FFDE0D2CB58 ?
                                                   7FFDE0D31600 ?000000002 ?
dbkedDefDump()+2746  call     ksedst()             000000000 ?000000001 ?
                                                   7FFDE0D2CA80 ?7FFDE0D2CB58 ?
                                                   7FFDE0D31600 ?000000002 ?
ksedmp()+41          call     dbkedDefDump()       000000003 ?000000000 ?
                                                   7FFDE0D2CA80 ?7FFDE0D2CB58 ?
                                                   7FFDE0D31600 ?000000002 ?
dbkdaKsdActDriver()  call     ksedmp()             000000003 ?000000000 ?
+1960                                              7FFDE0D2CA80 ?7FFDE0D2CB58 ?
                                                   7FFDE0D31600 ?000000002 ?
dbgdaExecuteAction(  call     dbkdaKsdActDriver()  7F0DE7A1F730 ?7FFDE0D33D00 ?
)+1065                                             7FFDE0D2CA80 ?7FFDE0D2CB58 ?
                                                   7FFDE0D31600 ?000000002 ?
...
Starting SQL statement dump
SQL Information
user_id=703 user_name=ZKM module=SQL*Plus action=
sql_id=2611xga5f8w95 plan_hash_value=0 problem_type=0
----- Current SQL Statement for this session (sql_id=2611xga5f8w95) -----
select * from no
sql_text_length=17
sql=select * from no
Optimizer state dump:
Compilation Environment Dump
optimizer_mode_hinted               = false
optimizer_features_hinted           = 0.0.0
parallel_execution_enabled          = true 
parallel_query_forced_dop           = 0
...

You can see that there is enough information.

Oracle executes SQL script file with command

When there are too many SQL commands (the SQL file is too large), the execution of PLSQL is slow and easy to timeout. At this time, you can directly execute the SQL script file with sqlplus command, as follows:

1. Sqlplus login

> sqlplus username/ password@dbname

2. Execute SQL file

If the SQL file is in the current directory, you can directly execute:

>@ demo.sql

If the SQL file is not in the current directory, you can use the absolute path:

>@ D:/demo.sql

Oracle trigger error: ORA-04098 [How to Solve]

When Oracle writes a trigger, an error is reported during execution, and the error prompt information is shown in the figure above. Similar trigger statements generally have syntax errors. Re audit the statement and execute it again

If you are using PL/SQL developer, you can check the triggers in the object bar under the current user to find the newly written trigger. If there is an error, there is a small red X in the icon in front of the corresponding trigger name