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

Introduction

DBASK is an online browsing database of the latest information and technical articles of WeChat Mini Programs, you can also refer to the knowledge base to submit questions. Recently, we have newly associated the PingCAP official account in DBASK Mini Program, displaying the latest information module in the first column of the home page, while adjusting the display style of articles and optimizing the display content of the knowledge base. Welcome to Mini Program to experience.

Collection of questions and answers

Next, we share the problems and diagnosis summary sorted out in this issue for your reference and study. The detailed diagnosis and analysis process can be redirected to Mini Program through the title link.

Problem 1. Oracle 12.2 rac SCM0 process occupies a high cpu

The 1-node scm0 process occupies a high cpu. What is the problem that causes the scm0 process to be shut down? Does shutting down the process have any impact?

Diagnostic conclusion: the DLM Statistics Collection and Management Slave (SCM0) is responsible for collecting and managing statistics related to the Global queuing Service (GES) and the Global Cache Service (GCS). This secondary server exists only when DLM statistics collection is enabled, and can be directly kill, with no impact after shutting down the process. DLM statistics are not used at all in 12.2 (18c and 19c just started).

Question 2. ORA-19706: invalid SCN

Dblink in the normal use of Times error: ORA-19706: invalid SCN ORA-02063: followed by line, original library: oracle10.2.0.4 AIX Version 5.3, target library: 11.2.0.4 AIX Version 7.1, found the online saying, need to upgrade the original library, and then modify the implied parameter _ external_scn_rejection_threshold_hours; excuse me to ask the Enmo Master, is there a solution that can be solved without upgrading?

Diagnosis conclusion: SCN will also be automatically upgraded in June this year. If you want to continue to use DBLINK, it is officially recommended that you upgrade and patch 10g at least to 10205. For more information, please see the article modb.pro/db/topic/0/564 below this topic.

Question 3. MySQL ERROR 1205 (HY000): Lock wait timeout exceeded

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction, what is the cause of the 1205 lock waiting problem in the database of the senior Mysql Innodb engine?

Diagnostic conclusion: in general, this error indicates that the row lock wait timed out. InnoDB is a transaction engine. You need to get a row lock before each row operation (usually update,delete). (in some specific scenarios, a gap lock is also required) before you can actually update the data. If the lock corresponding to the data to be modified by your transaction is already held by another transaction, your statement will wait until that transaction is committed or rolled back. After this wait exceeds the specified time (innodb_lock_wait_timeout), it will cause your session to end the wait and return this error. One solution is to see if there is a long session or SQL.

Question 4. OGG replication process error ORA-01403: no data found

Previously, my colleague had an ogg process, and there was no problem with data synchronization. Suddenly one day a similar error was reported: ORA-01403, and then the copy process dsc broke up and couldn't be written in, and the process was cut off. Then the colleague reinitialized it again and reported a similar mistake a few days later. What is the reason for this?

Diagnosis conclusion: the replication process with discard parameters will output the error record to the discard file, but although the file is configured with a size of nearly 50m, the error message has not been fully written down, so you should take the following steps to deal with it.

1. Use view params reppd to check the settings of discard. 2. Analyze DISCARDFILE to see what causes so many error messages in replication.

Question 5. Time synchronization of Oracle setting NTP

There is such a problem, that is, my oracle cluster time synchronization is synchronized with my own ctss service, but the system time is slowly getting slower and slower. Now I want to switch to ntp service to synchronize time. What is the general procedure? Do you need to restart the database cluster? is there any risk? I didn't do this. I don't have much experience. I want guidance.

Diagnostic conclusions: 1. Refer to the ntp configuration document of the corresponding platform, 2 nodes point to the same available ntp server, 2, turn off the cluster service, 3 or 2 nodes manually synchronize with the ntp server once, 4, start the cluster service

Question 6. Will changing the sys and system passwords affect DATAGUARD?

I would like to ask, will changing the password of oracle database sys and system affect the data synchronization between DATAGUARD master and slave libraries?

Diagnostic conclusion: sys will have an impact. 12.2 can automatically synchronize password files. If it is 11g, you can set the redo_transport_user parameter to specify a specific user as redo synchronization.

Question 7. Split partition ora-01652

Create a new partition table LGY_201905. In the t _ gnlk table, the LGY_201905 table space was created successfully, and an error was reported while creating the partition. Ora-01652: cannot extend the temp segment through 8192 (LGY_201905 in the table space).

Diagnostic conclusion: split needs at least twice as much space when splitting partitions.

Problem 8. Unable to connect after vip transfer

There are two rac nodes. After node 1 down is dropped, the vip of node 1 is transferred to node 2, but the database cannot be connected through the vip of node 1. Is it possible to add the vip of node 1 to the local_listener and remote_listener parameters of node 2 for configuration?

Diagnosis conclusion: the vip of node 1 floats to node 2, and the VIP of node 1 can not connect to the database, either through scanip, or configure the VIP of both nodes in the client connection string and set failover to on.

Question 9. How to allocate Oracle sga memory

Server memory 251g, currently sga is 70g, want to modify sga size, how to take a reasonable value, db_cache_size and shared_pool_size how to value, the current parameter cdb-sga is 70g want to modify the size of sga, how much is appropriate?

Expert answer: 1, refer to Lao Xiong's article on how to allocate memory modb.pro/db/article/0/126,2 to Oracle, if SGA gives 70G, you can set db_cache_size=35G,shared_pool_size=15G first, and then adjust it later according to the operation of the database. 3, the memory is adjusted step by step according to your own needs, suitable for your own system.

Question 10. The difference between sqlplus connection and dblink connection

What is the difference between a sqlplus connection and a dblink connection? For example, I can connect 12c with ora8/9 's client,sqlplus, but I can't connect 12c with dblink in 8's library. Thank you very much

Diagnostic conclusion: sqlplus is a client-side tool, while dblink is an oracle internal connection, that is, two completely unrelated things. There is a compatibility list for DBLINK cross-version connections, which can be viewed in details.

Question 11. Oracle12c rac service TAF problem

The service is added through srvcrl add service, but the scv service does not automatically switch to instance 2 after shutdown instance 1. What is the reason?

Diagnostic conclusion: starting at 12c, the cluster does not transfer service by shutting down the database through artificial commands such as sqlplus and srvctl, because CRS feels that this is a man-made routine. Test the service transfer through-failover or the direct kill pmon process.

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.