Tag Archives: oracle

[Solved] sqoop Error: jSQLException in nextKeyValue Caused by: ORA-24920:column size too large for client

Question

When importing Oracle data with sqoop, the following errors are reported:

INFO mapreduce.Job: Task Id : attempt_1646802944907_15460_m_000000_1, Status : FAILED
Error: java.io.IOException: SQLException in nextKeyValue
        at org.apache.sqoop.mapreduce.db.DBRecordReader.nextKeyValue(DBRecordReader.java:275)
        at org.apache.hadoop.mapred.MapTask$NewTrackingRecordReader.nextKeyValue(MapTask.java:568)
        at org.apache.hadoop.mapreduce.task.MapContextImpl.nextKeyValue(MapContextImpl.java:80)
        at org.apache.hadoop.mapreduce.lib.map.WrappedMapper$Context.nextKeyValue(WrappedMapper.java:91)
        at org.apache.hadoop.mapreduce.Mapper.run(Mapper.java:145)
        at org.apache.sqoop.mapreduce.AutoProgressMapper.run(AutoProgressMapper.java:64)
        at org.apache.hadoop.mapred.MapTask.runNewMapper(MapTask.java:799)
        at org.apache.hadoop.mapred.MapTask.run(MapTask.java:347)
        at org.apache.hadoop.mapred.YarnChild$2.run(YarnChild.java:174)
        at java.security.AccessController.doPrivileged(Native Method)
        at javax.security.auth.Subject.doAs(Subject.java:422)
        at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1875)
        at org.apache.hadoop.mapred.YarnChild.main(YarnChild.java:168)
Caused by: java.sql.SQLException: ORA-24920: column size too large for client

reason

Before using sqoop import other database is normal, this time from the new database import data problems, first check what is the difference between the two databases, found an Oracle version is 11, the new Oracle database version is 19, which may be the cause of the problem.
Go online to check the ORA-24920 error, said to upgrade the oracle client, further speculation may be the problem of Oracle driver.
Under the lib file of sqoop tool, the Oracle JDBC driver found for sqoop is ojdbc6.jar, which does not match with Oracle version 19.
You can check the Oracle version and the corresponding Oracle JDBC driver version on this page:
https://www.oracle.com/database/technologies/faq-jdbc.html#02_03
The screenshot is as follows:

the link to the download page is as follows:
https://www.oracle.com/database/technologies/appdev/jdbc-downloads.html

Solution:

According to the version, ojdbc8.0.jar was downloaded. After uploading, delete the original version and re import the data.
the driver of the original version here needs to be deleted or moved, otherwise it will not succeed. Guess that if there are two versions, the old version may be read

[Solved] cx_Oracle.DatabaseError: Error while trying to retrieve text for error ORA-01804

Error: 

cx_Oracle connect oracle error:

cx_Oracle.DatabaseError: Error while trying to retrieve text for error ORA-01804
sample code:
import cx_Oracle
conn = cx_Oracle.connect(user,pwd, self.ois_tns)

 

Solution: Check the environment variable settings for oracle in the .bash_profile under the Linux user on the server executing the code, as follows.

export ORACLE_HOME=/test/home/oracle/product/11.2.0.4
export LD_LIBRARY_PATH=O R A C L E H O M E / l i b e x p o r t T N S A D M I N = ORACLE_HOME/lib export TNS_ADMIN=ORACLEHOME/libexportTNSADMIN=ORACLE_HOME/network/admin

 

[INS-06006] Passwordless SSH connectivity not set up [Solved]

After installing RAC, I encountered [INS-06006] Passwordless SSH connectivity not set up between the following node(s) when installing Oracle, rac1 and rac2 mutual trust. When the Setup was executed successfully, the passwordless login was already available on the OS, but Test did not work, so the following steps could not be performed.

Solution: I found that the problem lies in the virtual NIC virbr0 that comes with the virtual machine, remove the virtual NIC.
1. ifconfig virbr0 down
2. brctl delbr virbr0
3. systemctl disable libvirtd
4. Restart the virtual machine
5.Remove the original /home/oracle/.ssh file, and reset,test it.

[Solved] Oracle Delete the Archive Error: RMAN-08137

Rman-08137 reports an error when Oracle deletes the archive.

RMAN-08137: warning: archived log not deleted, needed for standby or upstream capture process
archived log file name=/u02/prod/archivelog/1_938000_1004292720.dbf thread=1 sequence=938000
RMAN-08137: warning: archived log not deleted, needed for standby or upstream capture process
archived log file name=/u02/prod/archivelog/1_938001_1004292720.dbf thread=1 sequence=938001
RMAN-08137: warning: archived log not deleted, needed for standby or upstream capture process
archived log file name=/u02/prod/archivelog/1_938002_1004292720.dbf thread=1 sequence=938002
RMAN-08137: warning: archived log not deleted, needed for standby or upstream capture process
archived log file name=/u02/prod/archivelog/1_938003_1004292720.dbf thread=1 sequence=938003
RMAN-08137: warning: archived log not deleted, needed for standby or upstream capture process
archived log file name=/u02/prod/archivelog/1_938004_1004292720.dbf thread=1 sequence=938004
RMAN-08137: warning: archived log not deleted, needed for standby or upstream capture process
archived log file name=/u02/prod/archivelog/1_938005_1004292720.dbf thread=1 sequence=938005
RMAN-08137: warning: archived log not deleted, needed for standby or upstream capture process
archived log file name=/u02/prod/archivelog/1_938006_1004292720.dbf thread=1 sequence=938006
RMAN-08137: warning: archived log not deleted, needed for standby or upstream capture process
archived log file name=/u02/prod/archivelog/1_938007_1004292720.dbf thread=1 sequence=938007

The error message shows that the archive to be deleted cannot be deleted because the backup database still needs to be deleted. Check the archive number applied to the backup database

SQL> select open_mode,database_role from v$database;

OPEN_MODE                           DATABASE_ROLE
----------------------------------- ------------------------------------------------
READ ONLY WITH APPLY                PHYSICAL STANDBY

SQL> select process,sequence# from v$managed_standby;

PROCESS                      SEQUENCE#
--------------------------- ----------
DGRD                                 0
ARCH                            939246
DGRD                                 0
ARCH                            939252
ARCH                            939248
ARCH                            939253
RFS                                  0
MRP0                            939254
DGRD                                 0
RFS                                  0
RFS                                  0

Deleting 938000 from the main database shows that the standby database still needs to be, and the standby database query has been applied to the 939254 archive
query the status of the standby database on the primary database:

SQL> select open_mode,database_role from v$database;

OPEN_MODE                                     DATABASE_ROLE
--------------------------------------------- ------------------------------------------------
READ WRITE                                    PRIMARY

SQL> select dest_name,PROTECTION_MODE,GAP_STATUS,APPLIED_THREAD#,APPLIED_SEQ# from gV$ARCHIVE_DEST_STATUS where type='PHYSICAL';

DEST_NAME                 PROTECTION_MODE                                              GAP_STATUS                APPLIED_THREAD# APPLIED_SEQ#
------------------------- ------------------------------------------------------------ ------------------------- --------------- ------------
LOG_ARCHIVE_DEST_2        MAXIMUM PERFORMANCE                                          RESOLVABLE GAP                          1       939258

The main library shows that the backup library has GAP and the status is RESOLVABLE_GAP
Through the related information query, the solution is:

  1. Mount the primary database.

    Issue the following SQL statement at the primary database:
    SQL> ALTER SYSTEM FLUSH REDO TO <target_db_name>;
    target_db_name is the db_unique_name of the backup database

    View the backup db_unique_name

SQL> select open_mode,database_role from v$database;

OPEN_MODE                           DATABASE_ROLE
----------------------------------- ------------------------------------------------
READ ONLY WITH APPLY                PHYSICAL STANDBY

SQL> show parameter db_unique_name

NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
db_unique_name                       string                            standby

Discuss the downtime with the business and restart the production database to mount status

SQL> select open_mode,database_role from v$database;

OPEN_MODE                                     DATABASE_ROLE
--------------------------------------------- ------------------------------------------------
MOUNTED                                          PRIMARY

SQL> ALTER SYSTEM FLUSH REDO TO standby;

System alted.

SQL> alter database open;
 
 Database alted.

Query gap status of Zhu Bei database on the main database

SQL> select open_mode,database_role from v$database;

OPEN_MODE                                     DATABASE_ROLE
--------------------------------------------- ------------------------------------------------
READ WRITE                                    PRIMARY

SQL> select dest_name,PROTECTION_MODE,GAP_STATUS,APPLIED_THREAD#,APPLIED_SEQ# from gV$ARCHIVE_DEST_STATUS where type='PHYSICAL';

DEST_NAME                 PROTECTION_MODE                                              GAP_STATUS                APPLIED_THREAD# APPLIED_SEQ#
------------------------- ------------------------------------------------------------ ------------------------- --------------- ------------
LOG_ARCHIVE_DEST_2        MAXIMUM PERFORMANCE                  NO GAP                          1       939258

GAP_STATUS is now NO GAP, continue to execute the archive delete command, the deletion is normal

Oracle Database Cannot Open mount Mode Error: ORA-01102

Error in opening mount mode of database: ora-01102: cannot mount database in exclusive mode

SQL> startup nomount;
 
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size		    2932632 bytes
Variable Size		  427819112 bytes
Database Buffers	  629145600 bytes
Redo Buffers		   13844480 bytes
SQL> SQL> alter database mount;
 
alter database mount
*
ERROR at line 1:
ORA-01102: cannot mount database in EXCLUSIVE mode

Reason: There is a “sgadef.dbf” file in the “ORACLE_HOME/dbs” directory and Oracle processes (pmon, smon, lgwr, and dbwr) still exist – even if the database is closed, the shared memory segments and semaphores still exist – there is an “ORACLE_HOME/dbs/lk” file “lk” and “sgadef. There is an “ORACLE_HOME/dbs/lk” file “lk” and “sgadef. dbf ” files for locking shared memory. It seems that even if no memory is allocated, Oracle thinks the memory is still locked.
To view the startup log:


Solution.
1. Go to /d01/oracle/PROD/db/tech_st/12.1.0/dbs/ directory
2. Delete the lkPOD file

rm -rf  lkPROD

3. Make sure Oracle has no background processes: ps -ef |grep ora_ |grep PROD|grep ora_dbw0_PROD

if there is a background process, please use the command “kill” to delete it.

[[email protected] ~]$  kill -9 1912

Log in again using mount mode

[[email protected] ~]$ sqlplus/as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Mar 2 12:52:42 2022

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size		    2932632 bytes
Variable Size		  427819112 bytes
Database Buffers	  629145600 bytes
Redo Buffers		   13844480 bytes
Database mounted.

Successfully resolved.

Oracle18c Error: ORA-12012: error on auto execute of job

For the newly installed Oracle 18C database, the alert log keeps making errors:

ORA-12012: error on auto execute of job

ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_222"
ORA-20001: Statistics Advisor: Invalid task name for the current user
ORA-06512: at "SYS.DBMS_STATS", line 49538
ORA-06512: at "SYS.DBMS_STATS_ADVISOR", line 881
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 21631
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 23763
ORA-06512: at "SYS.DBMS_STATS", line 49526
2022-02-28 01:27:20.762000 +08:00
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_j000_104148.trc:
ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_224"
ORA-20001: Statistics Advisor: Invalid task name for the current user
ORA-06512: at "SYS.DBMS_STATS", line 49538
ORA-06512: at "SYS.DBMS_STATS_ADVISOR", line 881
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 21631
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 23763
ORA-06512: at "SYS.DBMS_STATS", line 49526
2022-02-28 01:37:21.758000 +08:00
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_j000_104738.trc:
ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_226"
ORA-20001: Statistics Advisor: Invalid task name for the current user
ORA-06512: at "SYS.DBMS_STATS", line 49538
ORA-06512: at "SYS.DBMS_STATS_ADVISOR", line 881
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 21631
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 23763
ORA-06512: at "SYS.DBMS_STATS", line 49526

 

Solution:
Call the initialization package manually: go to sqlplus as administrator
1. sqlplus / as sysdba

2. check the current auto task belongs to the user already exists, if not then initialize the package

3. EXEC dbms_stats.init_package();

4. Confirm again

SQL> column name format A35
SQL> set linesize 120
SQL> select name, ctime, how_created from sys.wri$_adv_tasks where owner_name = 'SYS' and name in ('AUTO_STATS_ADVISOR_TASK','INDIVIDUAL_STATS_ADVISOR_TASK');

no rows selected

SQL> EXEC dbms_stats.init_package();

PL/SQL procedure successfully completed.

SQL> select name, ctime, how_created from sys.wri$_adv_tasks where owner_name = 'SYS' and name in ('AUTO_STATS_ADVISOR_TASK','INDIVIDUAL_STATS_ADVISOR_TASK');

NAME                                CTIME     HOW_CREATED
----------------------------------- --------- ------------------------------
AUTO_STATS_ADVISOR_TASK             28-FEB-22 CMD
INDIVIDUAL_STATS_ADVISOR_TASK       28-FEB-22 CMD

Error in invoking target [How to Solve]

When Oracle 11g is installed on Linux 7, an error in invoking target is reported when the installation process reaches 86%. The screenshot is as follows

solution:

[[email protected] ~]$ cd $ORACLE_HOME/sysman/lib/
[[email protected] lib]$ vi ins_emagent.mk

Find:
$(SYSMANBIN)emdctl:
        $(MK_EMAGENT_NMECTL)

Modify to:
$(SYSMANBIN)emdctl:
        $(MK_EMAGENT_NMECTL) -lnnz11

Click Retry after the change

Oracle Start as DBA Error [How to Solve]

Problem Description:

In the morning, I created Oracle11g on the new machine and reported an error when starting with the startup command:

This error message means:
ora-00119: the initialization parameters of the system are invalid

Ora-00132: unresolved network listener name

My understanding: there is a problem with the listening configuration in the initialization file. It cannot be initialized and listening cannot be started.

Troubleshooting direction:

View the initialization file. Everyone has different installation paths.

The initialization file must exist in the Oracle installation directory orcl. My path:

Open the initialization file in Notepad and find the local listening configuration: local_Listener: the original local listening configuration parameter is listener_ORCL

Now, change it to:

local_listener=(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)

verification:

Load initialization file synchronously at startup:

The database started successfully.

[Solved] Navicat Connect Error: Oracle library is not loaded.

Navicat connection error – Oracle library is not loaded

Error reason: OCI. Of Navicat installation package There is a problem with the DLL

Solution: Download instantclient basic windows on the official website of Oracle x64-11.2.0.4.0.zip

Note that the version should be the same as Navicat –> Tools –> Options –> Environment –> OCI environment –> oci. The version information in the address of DLL is consistent

Finally, restart Navicat

[Solved] Error in invoking target ‘agent nmhs‘ of makefile

Error in invoking target ‘agent nmhs’ of makefile when installing Oracle 11g in Linux 7

Solution
add parameters linking libnnz11 Library in makefile
Modify $Oracle_HOME/sysman/lib/ins_ emagent. MK, modify
$(mk_event_nmectl) to $(mk_event_nmectl) – lnnz11
it is recommended to back up the original file before modification
[ [email protected] ~]$ cd $ORACLE_ HOME/sysman/lib
[ [email protected] lib]$ cp ins_emagent.mk ins_emagent.mk.bak
[ [email protected] lib]$ vi ins_emagent. mk

After entering the VI editor, enter/nmectl in the command mode to quickly locate the line to be modified
append parameters -lnnz11 þ the first is the letter L þ the last two are the numbers 1

Save and exit retry

Error when accessing Oracle: connected to an idle instance

When accessing Oracle with SYSDBA, the following information is prompted:

[[email protected] ~]$ sqlplus/as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Dec 2 20:21:40 2021
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> select * from dual;
select * from dual
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0

solve:

First, make sure to start listening:

[[email protected] ~]$ lsnrctl start

Then start instance:

SQL> startup;

This is the open status when you view the database instance again:

SQL> select status from v$instance;

STATUS
------------------------
OPEN

 

Linux First Install oracle Start Error [How to Solve]

The following error occurs when installing Oracle for the first time:

[[email protected] db_1]$ ./runInstaller
ERROR: Unable to verify the graphical display setup. This application requires X display. Make sure that xdpyi                                               nfo exist under PATH variable.

No X11 DISPLAY variable was set, but this program performed an operation which requires it.

Solution:

[[email protected] db_1]$ export DISPLAY=:0
[[email protected] db_1]$ echo $DISPLAY
:0

After execution, the following problems occur:

[[email protected] db_1]$ ./runInstaller
ERROR: Unable to verify the graphical display setup. This application requires X display. Make sure that xdpyi                                               nfo exist under PATH variable.
Exception in thread "main" java.lang.UnsatisfiedLinkError: /data/oracle/product/19.3/db_1/jdk/jre/lib/amd64/li                                               bawt_xawt.so: libXrender.so.1: cannot open shared object file: No such file or directory
        at java.lang.ClassLoader$NativeLibrary.load(Native Method)
        at java.lang.ClassLoader.loadLibrary0(ClassLoader.java:1941)
        at java.lang.ClassLoader.loadLibrary(ClassLoader.java:1824)
        at java.lang.Runtime.load0(Runtime.java:809)
        at java.lang.System.load(System.java:1086)
        at java.lang.ClassLoader$NativeLibrary.load(Native Method)
        at java.lang.ClassLoader.loadLibrary0(ClassLoader.java:1941)
        at java.lang.ClassLoader.loadLibrary(ClassLoader.java:1845)
        at java.lang.Runtime.loadLibrary0(Runtime.java:870)
        at java.lang.System.loadLibrary(System.java:1122)
        at java.awt.Toolkit$3.run(Toolkit.java:1636)
        at java.awt.Toolkit$3.run(Toolkit.java:1634)
        at java.security.AccessController.doPrivileged(Native Method)
        at java.awt.Toolkit.loadLibraries(Toolkit.java:1633)
        at java.awt.Toolkit.<clinit>(Toolkit.java:1670)
        at com.jgoodies.looks.LookUtils.isLowResolution(LookUtils.java:484)
        at com.jgoodies.looks.LookUtils.<clinit>(LookUtils.java:249)
        at com.jgoodies.looks.plastic.PlasticLookAndFeel.<clinit>(PlasticLookAndFeel.java:135)
        at java.lang.Class.forName0(Native Method)
        at java.lang.Class.forName(Class.java:348)
        at javax.swing.SwingUtilities.loadSystemClass(SwingUtilities.java:1879)
        at javax.swing.UIManager.setLookAndFeel(UIManager.java:582)
        at oracle.install.commons.util.Application.startup(Application.java:976)
        at oracle.install.commons.flow.FlowApplication.startup(FlowApplication.java:181)
        at oracle.install.commons.flow.FlowApplication.startup(FlowApplication.java:198)
        at oracle.install.commons.base.driver.common.Installer.startup(Installer.java:1106)
        at oracle.install.ivw.db.driver.DBConfigWizard.startup(DBConfigWizard.java:71)
        at oracle.install.ivw.db.driver.DBConfigWizard.main(DBConfigWizard.java:81)

Install libxrender.So.1 in the prompt as root:

[[email protected] ~]# yum install libXrender.so.1

New problems after installation:

[[email protected] db_1]$ ./runInstaller
ERROR: Unable to verify the graphical display setup. This application requires X display. Make sure that xdpyinfo exist under PATH variable.
Exception in thread "main" java.lang.UnsatisfiedLinkError: /data/oracle/product/19.3/db_1/jdk/jre/lib/amd64/libawt_xawt.so: libXrender.so.1: wrong ELF class: ELFCLASS32

Install libxrender:

[[email protected] ~]# yum install -y libXrender

New issues:

Can't connect to X11 window server using ':0' as the value of the DISPLAY variable.

After installation, restart oracle