Oracle set and reset usage tutorial cases

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

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;

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

*.aq_tm_processes=1

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;

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

*.aq_tm_processes=1 jy1.aq_tm_processes=0

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>

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;

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>

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>

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='*';

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!