Tag Archives: oracle

How to Solve DB2 uses Limit Error

preface

Prepare DB2 to use a simple limit query, and the result reports an error
Baidu has a wave. The previous articles are of no use at all, but they still report errors
later, we finally found a correct one and recorded a wave.

Text

SQL sample for DB2:

select * from sname.usertable fetch first 10 rows only

DB2 uses fetch first 10 rows only to return the first 10 rows of data
MySQL uses limit 10 to return the first 10 rows of data
Oracle uses rownum & lt= 10 returns the first 10 rows of data.

Sqoop Error: Can‘t parse input data: ‘\N‘ [How to Solve]

Sqoop reports an error can’t parse input data: ‘\ n’

Problem Description: use sqoop to push the data in hive to Oracle. The program reports an error: can’t parse input data: ‘\ n’
. Null value cannot be recognized when derivative.

The reason for this exception is that when the table in hive is a partitioned table, the added fields do not use cascade, such as:

alter table app.suntest_user add  columns(name string comment 'name') cascade

When does not use the cascade keyword, hive does not update the history partition definition, resulting in differences between the columns in the table and the target table when transmitting history data.

For example:

False table suntest_ User created partition M08 in August, added a name column in September, and created partition M09, and added a name column to the table definition in the target database (Oracle). There is no problem exporting M09 partition data through the sqoop script, but an error will be reported when exporting M08 data: can’t parse input data: ‘\ n’

Solution:

You can rebuild the partition in August , or create a table for derivatives. The following is a table creation statement.

----- Create a table corresponding to the partition and use the temporary derivative 
	create table app.test_suntest_user as 
	select id,name from app.suntest_user where partition_month='M08' 

----Rebuild partition statement 
	--Rename partition 
	alter table app.suntest_user partition (partition_month='M08') rename to partition (partition_month='M08bak');
	--New Partition 
	insert overwrite table app.suntest_user partition(partition_month='M08')
	select id ,name from app.suntest_user where partition_month='M08bak';

The sqoop statement is as follows:

sqoop export --connect "jdbc:oracle:thin:@**********" \
--username "abc" \
--password "123" \
--input-null-string '\\N'  --input-null-non-string '\\N' \
--table "SUNTEST_USER" \
--export-dir /user/hive/warehouse/app/test_suntest_user\
--input-fields-terminated-by '\001' \

How to Solve NCC2005 Error: get table metadata error

 

Problem description

After ncc2005 started using was to adapt oracle19c database, a large number of operations reported “get table metadata error”.

Analysis process

Check the NC log and find that it is related to orai18n.jar package, which is used for Chinese garbled code in Oracle database.

The test shows that the NC middleware works normally. Through the search, orai18n-11.2.0.4.jar is found under nchome/lib.

Solution

The orai18n-11.2.0.4.jar file under nchome/lib. Copy to the directory.

For example,/IBM/WebSphere/appserver/lib, restart the service.

Error report after installing Oracle GoldenGate monitor agent oggmon-20603

Problem phenomenon: after the Oracle Golden Gate monitor agent is installed normally, check that the jagent process already exists in info all, and the process can also be started normally, but the Ogg component can not be found in the automatic search of the OEM’s Ogg plug-in;

Troubleshooting: how to run jagentdebug.jar debug script to help with golden gate monitoring issues (OEM/Ogg monitor server)?Jagentdebug.jar debug in (DOC ID 2410209.1), the installation of the entire Ogg monitor client is also normal; After troubleshooting, the logs of Ogg Monitor reported errors such as oggmon-20603 and oggmon-20609. After further analysis and troubleshooting, patch 29684138 needs to be installed (when Ogg monitor monitors Ogg versions above 18C, Ogg 19.1.0.0.210720 is used this time)   Version), after installing this patch, you can search Ogg from oem13.2 and add monitoring;

Log information of Ogg monitor client:

[2021-10-15T10:29:53.650+08:00] [JAGENT] [ERROR] [OGGMON-20603] [com.goldengate.monitor.jagent.comm.ws.ManagerService] [tid: MessageCollector] [ecid: 0000Nm1gtXy0rm^_xTs1yW1XQE8n000002,0] RESTful Web Service with name messages/last has become unresponsive
[2021-10-15T10:29:58.607+08:00] [JAGENT] [ERROR] [OGGMON-20494] [com.goldengate.monitor.jagent.comm.ws.NotificationsCollector] [tid: StatusCollector] [ecid: 0000Nm1gtXx0rm^_xTs1yW1XQE8n000001,0] Error occurred while registering the OGG process. Exception: [[
 source parameter must not be null 
]]
[2021-10-15T10:29:58.651+08:00] [JAGENT] [ERROR] [OGGMON-20609] [com.goldengate.monitor.jagent.comm.ws.ManagerService] [tid: MessageCollector] [ecid: 0000Nm1gtXy0rm^_xTs1yW1XQE8n000002,0] Unsuccessful connection response from Message Web Service. Query String: messages/last ; Response Code: 404 ; Response Message: Not Found
[2021-10-15T10:29:58.652+08:00] [JAGENT] [ERROR] [OGGMON-20603] [com.goldengate.monitor.jagent.comm.ws.ManagerService] [tid: MessageCollector] [ecid: 0000Nm1gtXy0rm^_xTs1yW1XQE8n000002,0] RESTful Web Service with name messages/last has become unresponsive
[2021-10-15T10:29:58.652+08:00] [JAGENT] [ERROR] [OGGMON-20609] [com.goldengate.monitor.jagent.comm.ws.ManagerService] [tid: MessageCollector] [ecid: 0000Nm1gtXy0rm^_xTs1yW1XQE8n000002,0] Unsuccessful connection response from Message Web Service. Query String: messages/last ; Response Code: 404 ; Response Message: Not Found

Patching process:

GGSCI (oracle12c) 4> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
JAGENT      STOPPED                                           
PMSRVR      STOPPED                                           
EXTRACT     RUNNING     EXT1        00:00:00      00:00:00    
EXTRACT     RUNNING     PUMP1       00:00:00      00:00:04    


Close the JAGNET process first, so that there is no active process in the OGG MONITOR directory, you can ps -ef to confirm, and then you can patch it, the same reason as ORACLE database software patching.
$export ORACLE_HOME=/oracle/wls ===>> To set the OGG installation path

[oracle@oracle12c:/home/oracle/29684138]$/oracle/wls/OPatch/opatch lsinv
Oracle Interim Patch Installer version 13.9.1.0.0
Copyright (c) 2021, Oracle Corporation.  All rights reserved.


Oracle Home       : /oracle/wls
Central Inventory : /oracle/oraInventoryogg
   from           : /oracle/wls/oraInst.loc
OPatch version    : 13.9.1.0.0
OUI version       : 13.9.1.0.0
Log file location : /oracle/wls/cfgtoollogs/opatch/opatch2021-10-15_13-37-51PM_1.log


OPatch detects the Middleware Home as "/oracle/wls"

Lsinventory Output file location : /oracle/wls/cfgtoollogs/opatch/lsinv/lsinventory2021-10-15_13-37-51PM.txt

--------------------------------------------------------------------------------
Local Machine Information::
Hostname: oracle12c
ARU platform id: 226
ARU platform description:: Linux x86-64


Interim patches (6) :

Patch  22754279     : applied on Thu Oct 14 21:09:29 CST 2021
Unique Patch ID:  20383951
Patch description:  "One-off"
   Created on 9 Jul 2016, 00:36:58 hrs UTC
   Bugs fixed:
     22754279

Patch  21663638     : applied on Thu Oct 14 21:09:02 CST 2021
Unique Patch ID:  20477024
Patch description:  "One-off"
   Created on 31 Aug 2016, 21:01:13 hrs UTC
   Bugs fixed:
     21663638

Patch  19795066     : applied on Thu Oct 14 21:08:34 CST 2021
Unique Patch ID:  19149348
Patch description:  "One-off"
   Created on 16 Jul 2015, 15:51:43 hrs UTC
   Bugs fixed:
     19795066

Patch  19632480     : applied on Thu Oct 14 21:08:08 CST 2021
Unique Patch ID:  19278519
Patch description:  "One-off"
   Created on 25 Aug 2015, 07:19:43 hrs UTC
   Bugs fixed:
     19632480

Patch  19154304     : applied on Thu Oct 14 21:07:41 CST 2021
Unique Patch ID:  19278518
Patch description:  "One-off"
   Created on 25 Aug 2015, 07:10:13 hrs UTC
   Bugs fixed:
     19154304

Patch  19030178     : applied on Thu Oct 14 21:07:14 CST 2021
Unique Patch ID:  19234068
Patch description:  "One-off"
   Created on 4 Aug 2015, 05:40:22 hrs UTC
   Bugs fixed:
     19030178



--------------------------------------------------------------------------------

OPatch succeeded.


[oracle@oracle12c:/home/oracle/29684138]$/oracle/wls/OPatch/opatch apply
Oracle Interim Patch Installer version 13.9.1.0.0
Copyright (c) 2021, Oracle Corporation.  All rights reserved.


Oracle Home       : /oracle/wls
Central Inventory : /oracle/oraInventoryogg
   from           : /oracle/wls/oraInst.loc
OPatch version    : 13.9.1.0.0
OUI version       : 13.9.1.0.0
Log file location : /oracle/wls/cfgtoollogs/opatch/opatch2021-10-15_13-38-00PM_1.log


OPatch detects the Middleware Home as "/oracle/wls"

Verifying environment and performing prerequisite checks...
OPatch continues with these patches:   29684138  

Do you want to proceed?[y|n]
y
User Responded with: Y
All checks passed.

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/oracle/wls')


Is the local system ready for patching?[y|n]
y
User Responded with: Y
Backing up files...
Applying interim patch '29684138' to OH '/oracle/wls'

Patching component oracle.ogg.monitor.agent, 12.2.1.2.0...

Patching component oracle.ogg.monitor.agent, 12.2.1.2.0...
Patch 29684138 successfully applied.
Log file location: /oracle/wls/cfgtoollogs/opatch/opatch2021-10-15_13-38-00PM_1.log

OPatch succeeded.

Oracle reports an error and lsnrctl listening cannot be started

Connecting to (description = (address = (protocol = IPC) (key = extproc))
tns-12557: TNS: protocol adapter is not loadable
  Tns-12560: protocol adapter error
  Tns-00527: the protocol adapter cannot be loaded
2. Check whether/TMP /. Oracle and/var/TMP /. Oracle directories exist
3. Check the permissions of the current user trying to start the listener on these directories
MKDIR/var/TMP /. Oracle
MKDIR/TMP /. Oracle
chown – R Oracle: oiinstall/var/TMP /. Oracle/TMP /. Oracle
Chmod – R 01777/var/TMP /. Oracle/TMP /. Oracle
4. Run the listener to solve the problem:
lsnrctl start
5. If the listener still cannot be started, grant Oracle 777 permission on/tmp directory
chmod -R 777/tmp/var/tmp

[Solved] Golden Gate ggsci start manager: ERROR: Parameter file mgr.prm does not exist.

1. An error is thrown when starting Ogg. Error: parameter file/Ogg/dirprm/mgr.prm does not exist

[oracle@node01:/ogg]$ ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 19.1.0.0.4 OGGCORE_19.1.0.0.0_PLATFORMS_191017.1054_FBO
Linux, x64, 64bit (optimized), Oracle 19c on Oct 17 2019 21:16:29
Operating system character set identified as UTF-8.

Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved.



GGSCI (node01) 1> start mgr
ERROR: Parameter file /ogg/dirprm/mgr.prm does not exist.

GGSCI (node01) 3> info all                                

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     STOPPED

2. Execute commands using Oracle users

[oracle@node01:/ogg]$ cd /ogg
[oracle@node01:/ogg]$ mkdir dirprm
[oracle@node01:/ogg]$ mkdir dirrpt
[oracle@node01:/ogg]$ mkdir dirpcs

3. Execute the command to add a port

GGSCI (node01) 5> edit param mgr
add
PORT 7809

GGSCI (node01) 6> start manager
Manager started.

GGSCI (node01) 7> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING

How to Solve Oracle startup monitoring error

Solve the error of Oracle startup monitoring

On the Linux virtual machine, start the Oracle listening service:

[oracle@localhost ~]$ lsnrctl start

As a result, a listening error message appears, as follows:

TNS-12537: TNS:connection closed
 TNS-12560: TNS:protocol adapter error
  TNS-00507: Connection closed
   Linux Error: 29: Illegal seek

After many attempts and data access, it is found that the error is caused by the default hostname. At this time, the following steps can be taken to solve the above error reporting problem:

    1. modify hostname
[root@localhost oracle]# hostname oracle

Add “host IP oracle” in the/etc/hosts file

[root@oracle oracle]# vim /etc/hosts
...
localhost ip oracle

Add “hostname = oracle” in the etc/sysconfig/network file

[root@oracle oracle]# vim /etc/sysconfig/network
...
hostname=oracle

Restart listening

[root@oracle oracle]# lsnrctl start

After a wave of configuration, monitoring is successfully enabled:

How to Solve Ogg start error message ogg-00014

preface

recently, when configuring Ogg two-way replication, due to improper parameter settings, an error was reported at startup. The processing methods are summarized as follows

1. Startup error

[oracle@target ogg]$ ggsci
Explanation: According to the error message, the parameter is not set properly

2. Treatment method

[oracle@target ogg]$ more ./GLOBALS
CHEMA ogg
checkpointtable ogg.rep_demo_ckpt

Switch to OGG root directory, here CHEMA ogg error, should be GGSCHEMA ogg, modify can

[oracle@target ogg]$ ggsci
Login again, everything is OK

Oracle Database AWR error: ORA-06502 [How to Solve]

Oracle database AWR report is an important tool for DBA to analyze database performance!

Recently encountered a problem and reported an error:

ERROR:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 919
ORA-06512: at line 1

So, how to solve this problem?

Solution:

update WRH$_SQLTEXT set sql_text = SUBSTR(sql_text, 1, 1000);
commit;

After execution, re execute sqlplus/as SYSDBA @/ RDMBS/admin/awrrpt. SQL script successfully generates AWR report!

This sharing is over~

Mybatis integrates Oracle query and reports an error in the datetime type field

Question:

The same SQL statement can be executed normally in Oracle, but an error will be reported in the mybatis framework: ora-01722: invalid number or string does not match the data type

solve

Convert variable to string type:

g.UPDATETIME >= TO_CHAR(TRUNC (SYSDATE))，
g.CHECKDATE >= TO_CHAR('2021-01-01 00:00:00'))

-bash: sqlplus: command not found [How to Solve]

Error description

$ sqlplus/as sysdba
-bash: sqlplus: command not found

Treatment method

View environment.bash_ Profile configuration

$ find -name .bash_profile
./.bash_profile
$ pwd
/home/oracle
$ vi .bash_profile
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/.local/bin:$HOME/bin

export PATH

#add by rui on 20210715
export ORACLE_BASE=/oracle/app
export ORACLE_HOME=$ORACLE_BASE/oracle/product/12.1.0/dbhome_1 --Configuration error, change to [1]
export ORACLE_SID=rui1
export PATH=$HOME/bin:$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib

[1] export ORACLE_ HOME=$ORACLE_ BASE/product/12.1.0/dbhome_ one

Open a new window and enter the command again

$ sqlplus/as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Jul 15 04:42:11 2021

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

SQL>

It can be seen that the problem has been successfully solved
the reason for this error is $oracle_ Home configuration error.