Home > Database > Mysql Tutorial > Oracle set and reset usage tutorial cases

Oracle set and reset usage tutorial cases

小云云
Release: 2017-12-11 15:03:37
Original
1495 people have browsed it

This article mainly introduces you to the relevant information about the practical cases of set and reset in the Oracle parameter setting tutorial. The article introduces it in great detail through sample code. Friends who need it can refer to it. I hope it can help everyone.

Preface

This article mainly introduces to you the relevant content about set and reset of Oracle parameter settings, and shares it for your reference and study. I won’t say much below, let’s take a look at the details. Let’s introduce it.

Environment: Oracle 10.2.0.5 RAC

Requirements: The aq_tm_processes of node 1 is required to be restored to the default, and the settings of node 2 are required to remain unchanged

Background introduction:

You can find the aq_tm_processes parameter from the 10.2 official file. You can see that the default value of this parameter is 0. Let’s call it the default 0.

Then, in practice, we found that the effect of this default 0 is completely different from that of manually setting its SET to 0.

Communicated with colleagues about this matter, and finally learned a technical detail:

In Oracle, using SET to set parameter values ​​actually requires a lot less work. If you If you want to restore Oracle's default value, the most effective way is to RESET the value, so that everything will follow Oracle's default design.

Looking specifically at the current scenario, if you set the parameter aq_tm_processes to 0, it is completely different from the original default 0.

If set to 0, all q00 worker processes will be shut down and will not be restarted. If it is 0 by default, the q00 worker process can be started.

1. Build the test environment

The currently produced parameter content is similar to this, with global settings and instance 1 settings, as follows:

*.aq_tm_processes=1
jy1.aq_tm_processes=0
Copy after login
Copy after login

We know that the setting priority for instances is high, which means that the effect of this setting is that the parameter for instance 1 is 0, and the parameter for instance 2 is 1.

That is: instance 1 cannot start the q00 worker process, but instance 2 can start the q00 worker process.

Build a test environment to simulate production:

First set aq_tm_processes to 1:

alter system set aq_tm_processes = 1 scope=both sid='*';
--create pfile='/tmp/pfile11.ora' from spfile;
Copy after login

At this time, the parameter file will There is such a setting:

*.aq_tm_processes=1
Copy after login

Then set aq_tm_processes of instance 1 to 0

alter system set aq_tm_processes = 0 scope=both sid='jy1';
--create pfile='/tmp/pfile12.ora' from spfile;
Copy after login

At this time, there will be such settings in the parameter file:

*.aq_tm_processes=1
jy1.aq_tm_processes=0
Copy after login
Copy after login

This simulates the current situation of the production environment. Let’s take a look at the actual operation process:

SQL> 
SQL> alter system set aq_tm_processes = 1 scope=both sid='*';

System altered.

SQL> create pfile='/tmp/pfile11.ora' from spfile;

File created.

SQL> show parameter aq

NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes      integer  1
SQL> !ps -ef|grep q00
oracle 1462 27385 0 15:27 pts/1 00:00:00 /bin/bash -c ps -ef|grep q00
oracle 1464 1462 0 15:27 pts/1 00:00:00 grep q00
oracle 26534  1 0 15:08 ?  00:00:00 ora_q002_jy1
oracle 31538  1 0 15:21 ?  00:00:00 ora_q000_jy1

SQL> 
SQL> alter system set aq_tm_processes = 0 scope=both sid='jy1';

System altered.

SQL> create pfile='/tmp/pfile12.ora' from spfile;

File created.

SQL> show parameter aq

NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes      integer  0
SQL> !ps -ef|grep q00
oracle 2044 27385 0 15:28 pts/1 00:00:00 /bin/bash -c ps -ef|grep q00
oracle 2046 2044 0 15:28 pts/1 00:00:00 grep q00

SQL>
Copy after login

As you can see, after setting it to 0, the q00 process disappears, and the same thing happens even if the instance is restarted. , the q00 process will no longer start.

2. Test plan

What we have to do now is to restore the aq_tm_processes of node 1 to default without changing the settings of node 2.

2.1 Try to directly reset the parameter to specify node 1, and then restart the verification?

alter system reset aq_tm_processes scope=spfile sid='jy1';
create pfile='/tmp/pfile13.ora' from spfile;
Copy after login

Restart node 1 verification? Confirm whether the requirements can be achieved?

The specific actual operation is as follows:

SQL> alter system reset aq_tm_processes scope=spfile sid='jy1';

System altered.

SQL> create pfile='/tmp/pfile13.ora' from spfile;

File created.

SQL> show parameter aq

NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes      integer  0
SQL> !ps -ef|grep q00
oracle 3801 27385 0 15:32 pts/1 00:00:00 /bin/bash -c ps -ef|grep q00
oracle 3803 3801 0 15:32 pts/1 00:00:00 grep q00

SQL> startup force
ORACLE instance started.

Total System Global Area 599785472 bytes
Fixed Size     2098112 bytes
Variable Size    301993024 bytes
Database Buffers   289406976 bytes
Redo Buffers    6287360 bytes
Database mounted.
Database opened.
SQL> show parameter aq

NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes      integer  1
SQL> !ps -ef|grep q00
oracle 4228  1 0 15:33 ?  00:00:00 ora_q000_jy1
oracle 4232  1 0 15:33 ?  00:00:00 ora_q002_jy1
oracle 5021 27385 0 15:35 pts/1 00:00:00 /bin/bash -c ps -ef|grep q00
oracle 5023 5021 0 15:35 pts/1 00:00:00 grep q00

SQL>
Copy after login

As you can see, the answer is obviously: no.

Because this will only reset the parameters of instance 1, but because there were global parameters * before, you will find that the aq_tm_processes parameter will be 1 after restarting instance 1.
In other words, if the settings for instance 1 are removed, the overall settings will naturally be followed.

2.2 The correct way to experiment

With the above foundation, we have the idea of ​​​​implementing the requirements:

Let’s think about it, if we consider the overall situation The parameters are also reset, which will affect the previous settings of node 2. In this case, the value of node 2 can only be set separately, and then the global parameters are reset.

alter system set aq_tm_processes = 1 scope=both sid='jy2';
--create pfile='/tmp/pfile14.ora' from spfile;

alter system reset aq_tm_processes scope=spfile sid='*';
--create pfile='/tmp/pfile15.ora' from spfile;

重启节点1验证??确认是否可以实现需求?

具体实际操作如下:

SQL> alter system set aq_tm_processes = 1 scope=both sid='jy2';

System altered.

SQL> create pfile='/tmp/pfile14.ora' from spfile;

File created.

SQL> alter system reset aq_tm_processes scope=spfile sid='*';

System altered.

SQL> create pfile='/tmp/pfile15.ora' from spfile;

File created.

SQL> startup force
ORACLE instance started.

Total System Global Area 599785472 bytes
Fixed Size     2098112 bytes
Variable Size    301993024 bytes
Database Buffers   289406976 bytes
Redo Buffers    6287360 bytes
Database mounted.
Database opened.
SQL> show parameter aq

NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes      integer  0
SQL> !ps -ef|grep q00
oracle 7446  1 1 15:40 ?  00:00:00 ora_q000_jy1
oracle 7448  1 0 15:40 ?  00:00:00 ora_q001_jy1
oracle 7450  1 0 15:40 ?  00:00:00 ora_q002_jy1
oracle 7452  1 0 15:40 ?  00:00:00 ora_q003_jy1
oracle 7480 27385 0 15:41 pts/1 00:00:00 /bin/bash -c ps -ef|grep q00
oracle 7482 7480 0 15:41 pts/1 00:00:00 grep q00

SQL>
Copy after login

It can be seen that the answer of the real operation test verification is consistent with the expectation: yes.

Note: All steps to create pfile can be removed. At that time, I added this operation after each step because I wanted to confirm whether the theory was correct.

3. Summary

So to summarize, in actual customer environment, the following three steps should be done to complete the requirements:

--保障节点2的设置不变
alter system set aq_tm_processes=1 scope=spfile sid='jy2';
--reset节点1的设置
alter system reset aq_tm_processes scope=spfile sid='jy1';
--reset全局的设置
alter system reset aq_tm_processes scope=spfile sid='*';
Copy after login

Related recommendations:

Detailed explanation of the translate function and replace function in Oracle

Summary on the usage of the Contains function in Oracle

A brief analysis of the application of table functions in Oracle

The above is the detailed content of Oracle set and reset usage tutorial cases. For more information, please follow other related articles on the PHP Chinese website!

Related labels:
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template