TAF (scan+dataguard) JDBC Data source Connection Debug

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:
Customers to move the engine room, after the move, test everything is normal, left the scene after a few days said connection database is not normal, often reported that the database is read-only or is in the open state;

[Default] [2018-01-07 10:28:57,246] [ERROR] [Com.primeton.das.entity.impl.hibernate.util.jdbcexceptionreporter:77] ORA-16000: Opening a database for read-only access
Test scan address ok,ping OK; Network engineer said, network no problem;
Investigation:
Depending on the error, it is obvious that you are attached to the repository, and it is strange that you should be connected to the repository (the main library is only connected to the library, and the main library is not connected to it, but checking the primary repository is available).
1 The non-reproducibility of the problem determines
A) You have to do this job, all the while guarding that, and so finally caught the moment;
b) write a program to keep it all day, to catch a log; take a look, perhaps the program sincerity does not deceive you;
Recently bad luck, a succession of various fry, choose b Bar, began to build the program:
Language: JAVA JDK1.6 can
Editor: Notepad + + (very low, do this with Eclipse dare not);
Code: The most basic Java class library, add Ojdbc6.jar (remember with 11g later)
Idea: To have a program to keep the database, and then even to write down the time, even which instance, even do not have to write down the log, then the time, preferably with the network Ping, suspected network, but they said no problem, you can not Die Lai is it;
Coding:
The package to use:

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, to have IP, to have the time-out time limit, to have the log file output address;
Tbname.properties:

//定义导出的csv文件，注意要手动建立目录pname=test.csv//定义SCAN连接地址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("写入路径"+pathname+"\t"+"连接SCAN地址:"+ipaddress); }catch (IOException e) { System.out.println(e.getLocalizedMessage());

}

To define a database connection profile: Oracle.properties, to have a connection string, you must have a 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 statements, I'm more disgusting here. 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));
Defining icons
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 the line wrap feature
Textareaoutput.setwrapstyleword (TRUE); Activate break word function
Jf.setextendedstate (jframe.iconified);
Select database type
Buttongroup bg=new Buttongroup ();
Jradiobutton sqlbutton=new Jradiobutton ("SQL Server");
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 (600,200);
Jf.setlocation (400,200);
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 the ping function, using the simplest:

public static Boolean ping (String ipaddress,int timeOut) throws Exception {
When the return value is true, it indicates that host is available and false is not.
Boolean status = Inetaddress.getbyname (ipAddress). isreachable (TimeOut);
return status;
}

This is simple, but easy to have bugs, known as the following:
Jdk-5061568:java.net.inetaddress.isreachable () kills Windows networking article says this bug will always reappear ( This bug can is 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 a thread safe when using ICMP echo.
http://bugs.java.com/view_bug.do?bug_id=6595834
But I use it very well here, just skip it;
Print error log, this is the date you need to be aware of, In Java.sql,java.util, if not specified, an error will be found:

SimpleDateFormat df = new SimpleDateFormat ("Yyyy-mm-dd HH:mm:ss");
OutputStreamWriter err_log=new OutputStreamWriter (New FileOutputStream ("Err.log", True), "GBK");
Java.util.Date s_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, can be used; Point a test began to connect, the normal log to a file, the wrong hit Err.log;

Let the program run a day, take out the log a look, really have not even the time, and even to the standby library, and even when the first node is connected;
And the program Ping host really pass, it seems that maintenance personnel did not flicker, a variety of inspection database, did not find anything valuable, continue to open the program run, let colleagues see; A will find indeed sometimes not linked, hurriedly manual test, ping Host, with VIP address, Telnet scan host 1521, does not pass, Telnet VIP 1521 pass, Ghost, contact network personnel, said very uninteresting, said there is no limit.

Take a look at the idea:
1 database Scan address when the connection is not connected, the log proves that the database is good, the problem is still on the IP;
2 Network is in the same network, did not make any restrictions, repeatedly proved that no digging pits;
3 ping results indicate that the IP connection did not break;
In general, and a network phenomenon is very much like, ARP spoofing.
To continue testing:
When not connected, Ping Pass, Telnet scan port found incredibly not connected, and even when the Telnet pass,
Arp-d
Check the ARP, found that two times the MAC address is not the same; Is it really arp cheating, very excited ah, a mail poke into the network room, the problem seems to solve;
However, the administrator checked the next MAC address, this MAC address is the second node of the-_-, that is, two hosts have the same MAC address, Oracle put us all pit;
Check two host IP, do have a scan address, the spot directly without words, on the MoS bar, this problem ....
Keyword: Duplicate scan VIP on both node search, there are really:
Duplicate SCAN VIP After recovering public LAN problem on Windows R2 (document ID 2030432.1)
Temporary solution:
netsh interface ip delete address name= "" addr=xx.xx.xx.xx
Conclusion: There are many special flowers every year.

TAF (scan+dataguard) JDBC Data source Connection Debug