Shulou(Shulou.com)06/01 Report--

1 problem

A batch processing in the production environment was not completed.

2 Analysis

Batch processing logic:

Java- > shell- > sqlldr

Check the database session and find that the corresponding session wait event is SQL*Net message from client and the corresponding v$session.program is sqlldr@xxx,v$session.seq#, which lasts more than 5 hours (6:45-12:20).

Query the table loaded in the database and find that part of the data has been loaded, but the corresponding sqlldr log is empty.

The corresponding sqlldr command is as follows:

Sqlldr user/pwd data=a.txt control=a.ctl log=a.log bad=a.bad discard=a.dis errors=9999999 rows=1000

It is verified in the test environment that when sqlldr runs directly, all data can be loaded smoothly; when running in java- > shell- > sqlldr mode, the problem can be reproduced if it stops after a specific number of rows are loaded.

At the time of the above test, when sqlldr runs directly, the current window will output that the xx lines have been loaded. In fact, this is where the problem lies. When running in java- > shell- > sqlldr mode, the standard output is not read by the program, and when the amount of data to be loaded reaches a certain level (exceeding the default buffer size), the loading process will stop.

The verification process is referred to the appendix.

3 solution

Handle the standard output and error output of sqlldr, optional method:

Method one: sqlldr... Silent= (ALL)

Method 2: sqlldr. 1 > std.log 2 > err.log

Attached, test script

-- RunShell.java

Import java.util.Date

Import java.text.SimpleDateFormat

Import java.io.BufferedReader

Import java.io.InputStreamReader

Public class RunShell {

Public static void main (String [] args) {

Try {

SimpleDateFormat df = new SimpleDateFormat ("yyyy-MM-dd HH:mm:ss")

String shpath= "/ home/oracle/java/test.sh"

System.out.println ("- 1muri -" + df.format (new Date ()

Process ps = Runtime.getRuntime () .exec (shpath)

System.out.println ("--2muri -" + df.format (new Date ()

/ / ps.waitFor ()

BufferedReader br = new BufferedReader (new InputStreamReader (ps.getInputStream ()

String loop = args [0]

System.out.println (loop)

String line

While ("0" .equals (loop) & & (line = br.readLine ())! = null) {

System.out.println (line)

}

System.out.println ("--3muri -" + df.format (new Date ()

Ps.waitFor ()

System.out.println ("--4mura -" + df.format (new Date ()

}

Catch (Exception e) {

E.printStackTrace ()

}

}

}

-- test.sh

Seq-w 1 100000 | awk'{print $0 "xxxxxxxxxxxxxx"}'

Test 1 Mel-pass in parameter 0, and the main thread actively reads the standard output of shell

Java RunShell 0

-1 Murray 2018-04-20 13:23:25

-2murmur2018-04-20 13:23:25

0

...

...

-3murmur2018-04-20 13:23:34

-4murmur2018-04-20 13:23:34

= = > can be completed successfully

Test 2 Mel-pass in parameter 1, and the main thread does not read the standard output of shell

Java RunShell 1

-1 Murray 2018-04-20 13:28:30

-2murmur2018-04-20 13:28:30

one

-3murmur2018-04-20 13:28:30

= = > suspended for a long time

Ctrl-C

^ Cmurmur4 Muffin 2018-04-20 16:37:08

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.