Category Archives: How to Fix

Oracle stops a job

1. Related tables and views

　dba_ jobs 　　all_ jobs 　　user_ Jobs contains all the job information DBA of the login user_ jobs_ Running contains information about running jobs. Note that you must use the sys user of Oracle to log in to the database to view the DBA_ jobs_ Running, V $process, V $session table. At the same time, when logging in to the operating system, the Oracle user should be used.

2. Problem description

Solve a problem for colleagues, when the network connection is poor, execute a very long time SQL insert operation.

Since the network condition is not good, we choose to use disposable job to complete the insertion operation. After the job was executed for a period of time, I found that there was something wrong with the inserted table (I’m sorry, I didn’t check it first at that time). I’m going to stop job, because when job is running, all my modifications will report the error that system resources are busy.

It is not feasible to forcibly kill session, because the job will be restarted later. If the executed SQL is also killed, the restarted job will be executed again.

3. Solutions

The better way to do it is to do it;

1. First determine the job number to stop

In 10g, it can pass DBA_ Jobs_ Running to confirm.

Find running job:

    select sid from dba_ jobs_ running;

 

Find the SPID of the running job:

    select a.spid from v$process a ,v$session b where a.addr=b.paddr  and b.sid in (select sid from dba_ jobs_ running);

 

2. Break your confirmed job

Pay attention to DBMS_ Job package to identify your job as broken.

    SQL> EXEC DBMS_ JOB.BROKEN (job#,TRUE);

Note: after executing the command, the job you selected is still running.

 

3. Oracle session corresponding to kill

If the job is required to stop immediately, you need to find the corresponding session (SID, serial #) of the job, and then execute the following command:

    ALTER SYSTEM KILL SESSION ‘sid,serial#’;

Or directly kill the session of the corresponding operating system. If you use alter system kill session to execute for a long time, you can use the OS command to quickly kill the session

     For Windows, at the DOS Prompt: orakill sid spid

     For UNIX at the command line> kill –9 spid

 

4. Check if your job is still running

Check whether the job you want to stop is still running. In most cases, it should have stopped. In particular, the execution of the third step of the “killer” command. If it really doesn’t stop, we have to do it again from the first step and the third step.

 

5. Change the number of job queue processes to 0

First, confirm the current number of job queue processes

    SQL> col value for a10

    SQL> select name,value from v$parameter where name =’job_ queue_ processes’;     

Then change the number of job queue processes to 0

    SQL> ALTER SYSTEM SET job_ queue_ processes = 0;

Make sure all jobs stop.

 

6. Modify anything you want to modify, even the content in the job.

 

7. After the modification is completed, stop the broken state of the job.

SQL>EXEC DBMS_ JOB.BROKEN (job#,FALSE):

 

8. Resume job_ queue_ The original value of processes

     ALTER SYSTEM SET job_ queue_ processes = original_ value;

At this point, the entire stop and modify job completed

    

However, it should be noted that when mark is in a broken state, it may take a while for job to execute SQL internally. Therefore, you must consider it carefully when creating a job. At the same time, if possible, you can add some parts to judge the “stop signal” in your PL/SQL code. To perform the above steps.

After all, Oracle is very stubborn when executing jobs

 

4. Annex: usage of orakill

Oracle’s lock table solution “ora-00031: session marked for kill” often encounters a session that is always active, making the CPU always in use. Although it kills, it can’t make the thread end. Kill session is just to kill the process, but the thread is always active. It needs a real kill thread to solve the problem of high CPU utilization. OS: Windows 2003 orakill uses orakill Sid SPID, where sid is the instance name and SPID is the thread number. How to get this SPID?The following is a statement to find SPID. Select SPID, osuser, s.program from V $process P, V $session s where p.addr = s.paddr and s.sid = XXX; — Note: XXX is the SID of session (not database SID). You can input it yourself.
orakill instance_ Name SPID can successfully solve the problem of high CPU utilization in this way

MySQL forgot the root password

MySQL forgot the root password

1. Start the task manager and end the MySQL process

2. Enter the command line, enter the bin directory of MySQL (& lt; environment change & gt; path, you can find the bin directory) and start the MySQL service

mysqld -nt –skip-grant-tables  

(start without checking permissions);

(it may appear: 120705 17:41:14 [warning] option ‘new’: Boolean value’t ‘wasn’t recognized. Set to off. )

3. Reopen a command prompt window

 

  

Input: MySQL – uroot

4. Change the root password:

 

  

update mysql.user set password=PASSWORD(‘newpass’) where User=’root’;

5, flush privileges;

6, quit

Reconnecting MySQL

Reprint address:

http://blog.sina.com.cn/s/blog_ a3695da601010 mrs.html

MySQL modify character set

First of all, there are two main concepts about MySQL character set, one is character sets, the other is collations, the former is character content
and encoding, and the latter is some rules for comparing the former. These two parameter sets can be specified at four levels: database instance, single database, table and column.

For users, utf8 encoding is generally recommended to store data. To solve the problem of garbled code, it is not only the storage of MySQL data, but also related to the coding mode of user program files and the connection mode between user program and MySQL database.

first of all, MySQL has a default character set, which is determined during installation. When compiling mysql, you can use default_ Charset =
utf8 and default_ COLLATION=utf8_ general_ CI (MySQL version 5.5, version 5.1 uses — with charset =
utf8 — with collation = utf8)_ general_ The default character set is utf8, which is also the most once and for all method. After this is specified,
the coding mode of client connecting to database is utf8 by default, and the application does not need any processing.

unfortunately, many people don’t specify these two parameters when compiling and installing mysql, and most people install it through binary programs.
at this time, the default character set of MySQL is Latin1. At this time, we can still specify the default character set of MySQL by my.cnf Add two parameters to the file:
1. Add
default character set = utf8 under [mysqld]
2. Add
default character set = utf8 under [Client]
2 In this way, we don’t need to specify utf8 character set when we build database and table. This writing method in the configuration file solves the problem of data storage and comparison
, but it has no effect on the connection of the client. At this time, the client usually needs to specify utf8 connection to avoid garbled code. This is the general set
Names command. In fact, the set names utf8 command corresponds to the following server-side commands:
set character_ set_ client = utf8;
SET character_ set_ results = utf8;
SET character_ set_ Connection = xutf8;
but these three parameters cannot be written in the configuration file my.cnf It’s in the library. It can only be modified dynamically by the set command. What we need is to write a way to
Yongyi in the configuration file. At this time, is there a way to solve the problem on the server?The feasible idea is in init_ Set in connect. This command will be triggered every time an ordinary user connects. You can add the following line in the [mysqld] section to set the connection character set:
Add under [mysqld]:
init_ Connect =’set names utf8 ‘
summary:
1. It is preferred to specify two parameters to use utf8 encoding when compiling and installing mysql.
2. Select in the configuration file my.cnf or my.ini Set two parameters and init at the same time_ Connect parameter.
3. The third is in the configuration file my.cnf or my.ini Set two parameters, and specify the set names command for the client connection.
4. In the configuration file my.cnf The default character set parameter is added to the client and server in to facilitate management.

Design of MQ asynchronous collection report data

1. MQ asynchronous data acquisition

1) Using MQ asynchronization to break away from the main service and shorten the response time of the main service.

2) Using MQ asynchronous data storage operation to reduce the pressure of the main business server.

3) Storage uses es to support big data query and improve query efficiency.

4) In process asynchronous MQ message sending operation can be realized by using Google thread bus or spring @ async

2. The design is as follows

 

Could not create connection to database server. Attempted reconnect 3 times. Giving

1. Questions

The program connection to the database is abnormal, as follows:

Could not create connection to database server. Attempted reconnect 3 times. Giving 

2. Solutions

I tried a variety of solutions, and finally realized: MySQL connector- java.jar Inconsistent with the latest database version. The database is the latest version of 8

0.19，mysql-connector- java.jar It’s 5.0.6.

Solution: MySQL connector- java.jar Keep the same version number as the database.

The POM file is as follows, please check and modify the version by yourself; some versions are compatible, but it is better to keep them unified.

<dependency>
   <groupId>mysql</groupId>
   <artifactId>mysql-connector-java</artifactId>
   <version>8.0.19</version>
</dependency>

 

Optimization and upgrade solution for one click deployment without Jenkins under Linux

1. Scene

Jenkins one click deployment has not been installed under Linux.

2. Operation process

① Manual upload.

② Delete history code.

③ Unzip the war package.

④ Delete the war package.

⑤ Restart Tomcat.

⑥ Show logs.

3. Process disassembly

1) Use the RZ command to upload the war package.

2) Use the following command to complete the rest of the work in one go. Note: the last line is reserved for carriage return and line feed to ensure that the command will be executed.

cd   /opt/codes/portal-system
rm -rf  META-INF
rm -rf  WEB-INF
rm -rf importDataModel.xlsx
unzip portal-system-1.0.0. RELEASE.war
rm -rf portal-system-1.0.0. RELEASE.war
/opt/server/tomcat_ portal_ system/bin/ restart.sh
 

3） restart.sh Please refer to the previous blog post for the script.

4. Incremental design scheme

cd /opt/codes/portal-system/WEB-INF/classes
rz
rm -r com
unzip com.zip  
rm -rf com.zip  
/opt/server/tomcat_ portal_ system/bin/ restart.sh
#tail -f /opt/server/tomcat_ portal_ system/logs/ info.log

Why must microservices have gateways?

1、 What is a service gateway

Service gateway = route forwarding + filter

1. Route forwarding: receive all external requests and forward them to the back-end micro service.

2. Filter: in the service gateway, a series of crosscutting functions can be completed, such as permission verification, current limiting and monitoring. All these can be completed through the filter (in fact, routing and forwarding are also realized through the filter).

2、 Why a service gateway is needed
the above crosscutting function (taking permission verification as an example) can be written in three places:

1) each service is implemented by itself [not recommended]

2) write to a public service, and then all other services depend on this service [not recommended]

3) write it to the prefilter of the service gateway, and all requests come to check the permission [recommended]

First, the disadvantages are too obvious to use;
Second, compared with the first point, the code development is not redundant, but there are two disadvantages:

(1) because each service introduces this public service, it is equivalent to introducing the same permission verification code in each service, which increases the jar package size of each service for no reason. Especially for the deployment scenario using docker image, the smaller the jar, the better;

       ② Since this public service is introduced into every service, it may be difficult for us to upgrade this service in the future. Moreover, the more functions the public service has, the more difficult it will be to upgrade. Suppose we change the way of permission verification in public service. If we want all services to use the new way of permission verification, we need to re package all previous services , compile the deployment.

The service gateway can solve this problem

    write the logic of permission verification in the filter of the gateway, the back-end service does not need to pay attention to the code of permission verification, so the logic of permission verification will not be introduced into the jar package of the service, and the size of the jar package will not be increased; if you want to modify the logic of permission verification, you only need to modify the filter of permission verification in the gateway, without upgrading all existing micro services.

So, need service gateway!!!

 

3、 Service gateway technology selection

 

After the introduction of service gateway, the microservice architecture is as above, including three parts: service gateway, open service and service.

1. Overall process:

The service gateway, open service and service are registered in the registry when they are started; the user requests the gateway directly, and the gateway performs intelligent routing and forwarding (including service discovery and load balancing) to the open service, which includes permission verification, monitoring, current limiting and other operations. The open service aggregates the internal service response, returns it to the gateway, and the gateway returns it to the user

2. Points for attention in introducing gateway

With the addition of gateway and one more layer of forwarding (originally, the user requested to directly access the open service), the performance will decline (but the decline is not big. Generally, the performance of gateway machine will be very good, and the access between gateway and open service is usually intranet access, which is very fast); single point problem of gateway: there must be a single point in the whole network call process, which may be Gateway, nginx, DNS server, etc. To prevent gateway single point, you can hang another nginx in front of the gateway layer. The performance of nginx is very high, and it will not hang basically. After that, the gateway service can continuously add machines. However, such a request is forwarded twice, so the best way is to deploy the gateway single point service on a powerful machine (estimate the configuration of the machine through pressure test). Moreover, the performance comparison between nginx and zuul is similar according to the experiment done by a foreign friend. Zuul is an open source framework for gateway of Netflix, and the gateway should be fully implemented Light weight.

3. Basic functions of service gateway

Intelligent routing: receive all external requests and forward them to the external service open service of the back end;

Note: we only forward external requests, and requests between services do not go through the gateway. This means that full link tracking, internal service API monitoring, fault tolerance of calls between internal services, and intelligent routing cannot be completed in the gateway. Of course, all service calls can go through the gateway, and almost all functions can be integrated into the gateway, but in this case, the gateway’s pressure can be reduced It’s going to be very heavy. Permission verification: only the user’s request to the open service is verified, and the internal request of the service is not verified. Is it necessary to verify the request inside the service?API monitoring: only monitor the requests passing through the gateway and some performance indicators of the gateway itself (for example, GC, etc.); current limiting: cooperate with the monitoring to carry out current limiting operation; API log unified collection: similar to an aspect aspect aspect, record the relevant log when the interface enters and goes out… Follow up supplement

The above functions are the basic functions of the gateway, and the gateway can also realize the following functions:

A | B test: a | B test is a relatively large thing, including background experiment configuration, data burial point (see conversion rate) and streaming engine. In the service gateway, the streaming engine can be realized, but in fact the streaming engine will call internal services, so if it is in accordance with the architecture in the figure above, the streaming engine should be in the open service rather than in the service gateway…. Follow up supplement

 

4. Technology selection

The author is going to build a lightweight service gateway

Development language: java + groovy, the advantage of groovy is that the gateway service can dynamically add filter to achieve some functions without restart; microservice basic framework: springboot; gateway basic component: Netflix zuul; service registry: consult; permission verification: JWT; API monitoring: Prometheus + grafana; API unified log collection: logback+ Elk; stress test: JMeter;… Follow up supplement

In the follow-up introduction, will gradually introduce each knowledge point, and complete a lightweight service gateway!!!

Java uses lock to realize multithread sequential alternate execution mode 2

1. Principle

Lock synchronization lock

Signal() and await() of condition

2. Code examples

package com.thread;

import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.concurrent.locks.Condition;
import java.util.concurrent.locks.Lock;
import java.util.concurrent.locks.ReentrantLock;

public class TasksTestLock extends Thread {
    private static Lock lock = new ReentrantLock();
    private static Condition condition = lock.newCondition();
    private static int num = 1;
    private int id;

    public TasksTestLock(int id) {
        this.id = id;
    }


    @Override
    public void run() {
        while (num <= 12) {

            lock.lock();

            System.out.println("Thread" + id + " num:" + num);
            num++;

            condition.signal();
            try {
                condition.await();
            } catch (InterruptedException e) {
                e.printStackTrace();
            }

            lock.unlock();

        }
    }


    public static void main(String[] args) {
        Thread thread0 = new TasksTestLock(0);
        Thread thread1 = new TasksTestLock(1);

        ExecutorService exec = Executors.newFixedThreadPool(3);

        exec.submit(thread0);
        exec.submit(thread1);

        exec.shutdown();

    }
}

3. Output results

 

Implementation of multithread sequential alternate execution by using lock in Java

1. Principle

Synchronized thread synchronization

Notify() calls up the thread

The wait() thread waits

2. Code examples

package com.thread;

import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;

public class TasksTestSync extends Thread {
    private static Integer num = 0;
    private int id;

    public TasksTestSync(int id) {
        this.id = id;
    }

    @Override
    public void run() {
        while (num < 12) {
            synchronized (TasksTestSync.class) {
                num = num + 1;
                System.out.println("thread_" + id + " num:" + num);

                TasksTestSync.class.notify();
                try {
                    TasksTestSync.class.wait();
                } catch (InterruptedException e) {
                    e.printStackTrace();
                }

            }
        }
    }

    public static void main(String[] args) {
        Thread thread0 = new TasksTestSync(0);
        Thread thread1 = new TasksTestSync(1);

        ExecutorService exec = Executors.newFixedThreadPool(3);

        exec.submit(thread0);
        exec.submit(thread1);

        exec.shutdown();

    }
}

3. Implementation results

Why use thread pools? Remember and understand

1. Reduce cost and improve efficiency

Reduce the time of thread creation and destruction and the overhead of system resources;

At the same time, it improves the response speed of the system. When a new task arrives, it can be executed immediately by reusing the existing thread without waiting for the creation of a new thread.

2. Improve the manageability of threads

It is convenient to control the number of concurrent threads. Unlimited thread creation may lead to excessive memory consumption, resulting in oom, and will cause excessive CPU switching. There is a time cost for CPU switching threads: it is necessary to keep the scene of the current thread and restore the scene of the thread to be executed.

It can allocate, tune and monitor threads in a unified way, so as to improve the response speed, and provide more powerful functions, such as delaying and timing thread pool.

 

Nginx front end and back end separation + service cluster reverse proxy

1. Scene

Nginx implements the separation of front and back end, and the reverse proxy of service cluster.

2. Nginx configuration instance

upstream portal-system {
       server 127.0.0.1:8061 max_fails=3 fail_timeout=30s;
       server 172.31.88.30:8061 max_fails=3 fail_timeout=30s;
}

server {
        listen       80;
        server_name  47.102.168.177;
        root /opt/pages/dispatch-portal-system/;

       location/{
         proxy_set_header Host $host:$server_port;
         proxy_pass   http://portal-system;
       }

       location /images/ {
         alias  /opt/images/dispatch-portal-system/;
       }
       
       location /favicon.ico {
         root /opt/images/dispatch-portal-system/;
       }
       
       location /api/user/updateImage/ {
          proxy_set_header Host $host:$server_port;
          proxy_pass   http://127.0.0.1:8061/;
       }

       location   =/{
          root /opt/pages/dispatch-portal-system/;
          add_header Cache-Control "no-cache, no-store";
       }
	   
        location /index.html {
          root  /opt/pages/dispatch-portal-system/;
          add_header Cache-Control "no-cache, no-store";
        }

        location /static/ {
          root  /opt/pages/dispatch-portal-system/;
        }

}

 

NxL job cluster nginx routing forwarding and reverse proxy

1. Scene

Two servers are deployed with XXL job respectively to build a high availability cluster

Provide easy request URL

2. Nginx configuration

 upstream xxl-jobs {
        server 192.168.30.01:9500 max_fails=3 fail_timeout=30s;
        server 192.168.30.02:9500 max_fails=3 fail_timeout=30s;
    }

     server {
        listen    8888;
        server_name  localhost;
        location/{
            proxy_pass http://xxl-jobs;
            index  index.html index.htm;
        }
        error_page   500 502 503 504  /50x.html;
        location = /50x.html {
            root   html;
        }
    }