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

Environment: RAC+dataguard weblogic 10.3.6

JDBC connection database configuration:

Jdbc:oracle:thin:@ (DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=XX.XX.XX.xx) (PORT=1521)) (ADDRESS= (PROTOCOL=TCP) (HOST=XX.XX.xx.xx) (PORT=1521)) (LOAD_BALANCE=no) (CONNECT_DATA= (SERVER=DEDICATED) (SERVICE_NAME=TCIS) (FAILOVER_MODE= (TYPE=SELECT) (METHOD=BASIC) (RETRIES=180) (DELAY=5)

Cause:

After the customer moved the computer room, everything was normal after the test. A few days after leaving the site, he said that the connection to the database was not normal, and often reported that the database was read-only or was open.

[com.primeton.das.entity.impl.hibernate.util.JDBCExceptionReporter:77] ORA-16000: open the database for read-only access

Test scan address OK,ping ok; the network engineer said the network is fine.

Troubleshooting:

According to the error, it is obvious that it is connected to the standby library; it is strange why it is connected to the standby library (according to the string, the primary library will only be connected if it is not connected, while checking that the primary library is available, the various check will no longer be explained).

The non-reproducibility of the problem determines

A) you can either hire someone to do this job and watch it all the time, waiting for the moment when you finally catch it

B) write a program to watch it all day long and catch a log; take a look, maybe the program doesn't deceive you.

Recently, I have had bad luck. I have been bumping into all kinds of bomb libraries one after another. Choose B and start building the program:

Language: JAVA JDK1.6 is fine.

Editor: notepad + + (very low, dare not do this with eclipse)

Code: the most basic JAVA class library, plus ojdbc6.jar (remember to use 11g later)

Idea: if you want to have a program that constantly connects to the database, and then can write down the time, and even which instance it is, you have to write down a log. At that time, you had better take the Ping of the network and suspect the network, but people say it's okay, and you can't rely on it, can you?

Coding:

The bag used:

Import javax.swing.

Import java.awt.

Import java.awt.event.

Import java.sql.

Import java.util.

Import java.io.

Import java.net.InetAddress

Import java.text.SimpleDateFormat

Define a parameter file with IP, timeout, and log file output address

Tbname.properties:

/ / define the exported csv file. Note that you need to manually establish the directory pname=test.csv// definition SCAN connection address ipaddr=XX.XX.XX.XXtimeout=3000

Reading method:

Try {Properties ps=new Properties (); FileInputStream fis= new FileInputStream ("tbname.properties"); ps.load (fis); fis.close (); pathname=ps.getProperty ("pname"); ipaddress=ps.getProperty ("ipaddr"); timeOut= Integer.parseInt (ps.getProperty ("timeout")); System.out.println ("write path" + pathname+ "\ t" + "connection SCAN address:" + ipaddress);} catch (IOException e) {System.out.println (e.getLocalizedMessage ())

}

Define database connection profile: oracle.properties, connection string, username / password

Db_url=jdbc:oracle:thin:@ (description= (address_list= (address= (host=xx.xx.xx.xx) (protocol=tcp) (port=1521)) (load_balance=NO) (failover=yes)) (connect_data= (service_name=orcl)

# db_url=jdbc:jtds:sqlserver://127.0.0.1:1433/ccxe

Username=test

Password=test

Related methods:

Public Connection getConnection () throws ClassNotFoundException,IOException

SQLException {

Properties ps=new Properties ()

FileInputStream fis= new FileInputStream ("oracle.properties")

Ps.load (fis)

Fis.close ()

String url=ps.getProperty ("db_url")

String user=ps.getProperty ("username")

String pwd=ps.getProperty ("password")

String driver = "oracle.jdbc.driver.OracleDriver"

Class.forName (driver)

Return DriverManager.getConnection (url, user, pwd)

}

Custom SQL statement, I am rather disgusting here to do a graphical, text box:

Public void createUI () {

JFrame jf=new JFrame ("data Export tool csv")

Container c=jf.getContentPane ()

C.setLayout (new FlowLayout (FlowLayout.LEFT,30,30))

/ / define icon

ImageIcon ii=new ImageIcon ("main.gif")

Image image=ii.getImage ()

JPanel panel = new JPanel ()

Jf.setIconImage (image)

TextAreaOutput = new JTextArea ("select instance_name from v$instance", 6,10)

TextAreaOutput.setSelectedTextColor (Color.RED)

TextAreaOutput.setLineWrap (true); / / activate automatic line wrapping

TextAreaOutput.setWrapStyleWord (true); / / activate the line breaking continuous word function

Jf.setExtendedState (JFrame.ICONIFIED)

/ / Select a database type

ButtonGroup bg=new ButtonGroup ()

JRadioButton sqlbutton=new JRadioButton ("Sqlserver")

JRadioButton orabutton=new JRadioButton ("oracle")

Bg.add (sqlbutton)

Bg.add (orabutton)

Button=new JButton ("Test")

JButton prebutton=new JButton (Preview)

Button.addActionListener (this)

Sqlbutton.addActionListener (this)

Orabutton.addActionListener (this)

Prebutton.addActionListener (this)

/ / Control window initialization size

Jf.setSize (600200)

/ / jf.setLocation (400200)

C.add (textAreaOutput)

C.add (sqlbutton)

C.add (orabutton)

C.add (button)

C.add (prebutton)

Jf.setDefaultCloseOperation (JFrame.EXIT_ON_CLOSE)

Jf.setVisible (true)

}

Define ping functionality with the simplest:

Public static boolean ping (String ipAddress,int timeOut) throws Exception {

/ / when the returned value is true, host is available, but false is not.

Boolean status = InetAddress.getByName (ipAddress) .isReachable (timeOut)

Return status

}

Although this is simple, it is easy to have bug. The following are known:

JDK-5061568: java.net.InetAddress.isReachable () kills Windows networking article says that this bug will always reappear (This bug can be reproduced always.)

Http://bugs.java.com/view_bug.do?bug_id=5061568

JDK-5061571: InetAddress#isReachable does not send PINGs but only TCP echos

Http://bugs.java.com/bugdatabase/view_bug.do?bug_id=5061571

JDK-6595834: InetAddress.isReachable is not thread safe when using ICMP ECHO.

Http://bugs.java.com/view_bug.do?bug_id=6595834

But it's good for me to use it here. Just skip it.

Print the error log. What you need to note here is this date, which is available in java.sql,java.util. If it is not specified, an error will be reported:

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

OutputStreamWriter err_log=new OutputStreamWriter (new FileOutputStream ("err.log", true), "GBK")

Java.util.Date date = new java.util.Date ()

Err_log.write (df.format (s_date) + "\ t" + e.getMessage ())

Err_log.flush ()

Err_log.close ()

The rest is javac xxxx.java.

Set classpath=%classpath%;.;.\ ojdbc6.jar;.\ sqljdbc4.jar

Java CsvExportTable

And then

The interface is ugly and can be used; click on the test to start the connection, type the normal log into a file, and type the wrong err.log.

Let the program run for a day, take out the log and take a look, there is really a time when it can not be connected; it is connected to the standby database, and when it can be connected, only the first node is connected.

And the program ping host is really through, it seems that the maintenance personnel really did not deceive, all kinds of check the database, did not find anything valuable, continue to open the program to run, let colleagues see; after a while found that sometimes can not connect, quickly manual test, ping host, with vip address, telnet scan host 1521, not connected, telnet vip 1521, ghost, contact network personnel, that is very boring, said there are no restrictions.

Let's get this straight:

1 the SCAN address of the database can be connected, but sometimes not; the log proves that the database is good; the problem still lies in the IP

2 the network is located in the same network disconnection, it is true that there are no restrictions, and it has been proved again and again that there are no holes dug.

The result of 3 ping indicates that the IP connection has not been broken.

On the whole, it is very similar to a network phenomenon, ARP deception.

Continue testing:

When you can't connect, ping can get through, and telnet scan port finds that you can't get through, but when you can, telnet can.

Arp-d

Check arp and find that the two MAC addresses are not the same; is it really ARP deception, very excited, an email poked into the network room, the problem seems to be solved

However, the administrator checked the MAC address, this MAC address is the second node-_ -, that is to say, the two hosts have the same MAC address, oracle cheated us all;

Check the IP of the two hosts, do have SCAN addresses, directly speechless on the spot, on the MOS bar, this kind of problem.

Keyword: duplicate scan vip on two node search, there are:

Duplicate SCAN VIP after recovering public LAN problem on Windows 2012 R2 (document ID 2030432.1)

Temporary solution:

Netsh interface ip delete address name= "" addr=xx.xx.xx.xx

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.