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

Today, the editor will bring you an article on how to use Oracle set and reset. The editor thinks it's pretty good, so I'll share it for you as a reference. Let's follow the editor and have a look.

Environment: Oracle 10.2.0.5 RAC

Requirements: the aq_tm_processes requirements of node 1 are restored to the default, and the settings of node 2 remain unchanged

Background introduction:

The aq_tm_processes parameter can be found from the 10. 2 official file. We can see that the default value of this parameter is 0, so let's call it the default 0. 0.

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

Communicate the matter with colleagues and finally get a technical detail:

In Oracle, setting parameter values using SET actually does a lot less. If you want to restore the default value of Oracle, the most effective way is to RESET this value, so that everything will follow the default design of Oracle.

Specifically, in the current scenario, if you set the parameter aq_tm_processes to 0. That is completely different from the original default of 0.

If you set 0, all Q00 worker processes will shut down and will not start again. If it is the default 0Query Q00 small labor process can be started.

1. Build a test environment

The current production parameters are similar to this, with global settings and instance 1 settings, as follows:

* .aq_tm_processes=1jy1.aq_tm_processes=0

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

That is, instance 1 cannot start the Q00 worker process, and 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 point, there are such settings in the parameter file:

* .aq_tm_processes=1

Then set the 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 point, there are such settings in the parameter file:

* .aq_tm_processes=1jy1.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:

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 aqNAME TYPE VALUE-- aq_tm_processes integer 1SQL >! ps-ef | grep q00oracle 1462 27385 0 15:27 pts/1 00:00:00 / bin/bash-c ps-ef | grep q00oracle 1464 1462 0 15:27 pts/1 00:00:00 grep q00oracle 26534 10 15:08? 00:00:00 ora_q002_jy1oracle 31538 10 15:21? 00:00:00 ora_q000_jy1SQL > 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 aqNAME TYPE VALUE-- aq_tm_processes integer 0SQL >! ps-ef | grep q00oracle 2044 27385 0 15:28 pts/1 00:00:00 / bin/bash-c ps-ef | grep q00oracle 2046 2044 0 15:28 pts/1 00:00:00 grep q00SQL >

As you can see, the Q00 process disappears after it is really set to 0, even if the instance is restarted, and the Q00 process will no longer start.

two。 Test scheme

What we need to do now is to make sure that the aq_tm_processes of node 1 is restored to the default without changing the settings of node 2.

2.1 try to specify Node 1 directly with the reset parameter, 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 met?

The 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 aqNAME TYPE VALUE-- aq_tm_processes integer 0SQL >! ps-ef | grep q00oracle 3801 27385 0 15:32 pts/1 00:00:00 / bin/bash-c ps-ef | grep q00oracle 3803 3801 0 15:32 pts/1 00:00:00 grep q00SQL > startup forceORACLE instance started.Total System Global Area 599785472 bytesFixed Size 2098112 bytesVariable Size 301993024 bytesDatabase Buffers 289406976 bytesRedo Buffers 6287360 bytesDatabase mounted.Database opened.SQL > show parameter aqNAME TYPE VALUE -- aq_tm_processes integer 1SQL >! ps-ef | grep q00oracle 4228 10 15:33? 00:00:00 ora_q000_jy1oracle 4232 10 15:33? 00:00:00 ora_q002_jy1oracle 5021 27385 0 15:35 pts/1 00:00:00 / bin/bash-c ps-ef | grep q00oracle 5023 5021 0 15:35 pts/1 00:00:00 grep q00SQL >

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

Because this will only reset the parameter of instance 1, but since there is a global parameter * before, after restarting instance 1, you will find that the aq_tm_processes parameter will be 1.

That is, if the setting for instance 1 is removed, it will naturally follow the overall setting.

2.2 the right way to experiment

With the above foundation, there are ideas to achieve the requirements:

We think that if the global parameter is also reset, it will affect the previous setting of node 2, so that the value of node 2 can only be set separately, and then reset the global parameter.

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 met? The actual operation is 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 forceORACLE instance started.Total System Global Area 599785472 bytesFixed Size 2098112 bytesVariable Size 301993024 bytesDatabase Buffers 289406976 bytesRedo Buffers 6287360 bytesDatabase mounted.Database opened.SQL > show parameter aqNAME TYPE VALUE -aq_tm_processes integer 0SQL >! ps-ef | grep q00oracle 7446 11 15:40? 00:00:00 ora_q000_jy1oracle 7448 10 15:40? 00:00:00 ora_q001_jy1oracle 7450 10 15:40? 00:00:00 ora_q002_jy1oracle 7452 10 15:40? 00:00:00 ora_q003_jy1oracle 7480 27385 0 15:41 pts/1 00:00:00 / bin/bash-c ps-ef | grep q00oracle 7482 7480 0 15:41 pts/1 00:00:00 grep q00SQL >

As you can see, the answer verified by the real operation test is the same as expected: yes.

Note: all the steps for creating a pfile can be removed. At that time, I added this operation after each step because I wanted to make sure that the theory was correct.

3. Summary

To sum up, in fact, in the customer environment, we should do the following three steps 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='*'

After reading the appeal, have you mastered the use of Oracle set and reset? If you want to know more related content, welcome to follow the industry information channel, thank you for reading!

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.