Oracle parameter set and reset settings

Actual cases of set and reset in Oracle parameter setting

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

• 1. Build test environment
• 2.Test plan
• 3. Summary

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, we actually found that the effect of this default 0 is completely different from that of manually setting its SET to 0.
Communicating this matter with colleagues, I finally learned a technical detail:
In Oracle, using SET to set parameter values ​​actually saves you a lot of work. 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 it is 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 a 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, there will be such settings in the parameter file:

*.aq_tm_processes=1

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

At this point, the current situation of the production environment is simulated. 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, the q00 process disappears after it is indeed set to 0. Even if the instance is restarted, it is the same, and 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 operations are 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 was a global parameter *�� 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 Experiment the right way

With the above foundation, we have the idea of ​​​​implementing the requirements:
Let's think about it. If we reset the global parameters, it will affect the previous settings of node 2. In this way, we can only set the value of node 2 separately, and then reset the global parameters.

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;

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

The specific actual operations are as follows:

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>

As you can see, the answer to the real operation test verification is consistent with expectations: 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 taken to complete the requirements:

--Ensure that the settings of node 2 remain unchanged
alter system set aq_tm_processes=1 scope=spfile sid='jy2';
--reset node 1 settings
alter system reset aq_tm_processes scope=spfile sid='jy1';
--reset global settings
alter system reset aq_tm_processes scope=spfile sid='*';

Summary of knowledge points in this article: In fact, you only need to understand the following three knowledge points:

The reset operation actually just removes this value from the spfile;

Settings for a certain instance level have higher priority than overall settings;

Note that Oracle actually does a lot less for SET parameter values. A simple understanding is that the default 0 is different from the setting 0.

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