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

Recently, when I was doing an experiment with Oracle RAC on a virtual machine, I saw that the background log kept reporting errors because the configured log_archive_dest_2 parameters were referred to node 2, and node 2 was not powered on, so there was always an error. So I thought that this parameter was modified when I was doing the experiment, but now I don't need it anymore. I just reset this parameter, so I executed the following statement:

Alter system reset log_archive_dest_2

Then use show parameter to check that the parameters have been cleared:

SQL > show parameter log_archive_dest_2 NAME TYPE VALUE-- log_archive_dest_2 string

Look at the alert log and there are no errors reported. I thought it would be over. But it's not over yet. After the next database restart, you can see that the previous error occurs again, and the setting of the log_archive_dest_2 parameter is restored to the value before the reset:

SQL > show parameter log_archive_dest_2 NAME TYPE VALUE-- log_archive_dest_2 string service=rac2

Is it possible that the last reset was not successful?

Judging from the results of the last check, the reset was successful, and the alert log also indicated that the modification was successful.

Then why did such a strange thing happen?

Try to reset again:

SQL > alter system reset log_archive_dest_2;alter system reset log_archive_dest_2*ERROR at line 1:ORA-32010: cannot find entry to delete in SPFILE

When you see the error above, it means that there is no corresponding entry in spfile to delete.

If there is no entry, where do I get my settings?

So generate pfile to see how log_archive_dest_2 is set in the parameter file:

Orcl2.log_archive_dest_1='location=/home/oracle/rac2'orcl1.log_archive_dest_1='location=/home/oracle/rac1'*.log_archive_dest_1='location=+data'orcl2.log_archive_dest_2='service=rac1'orcl1.log_archive_dest_2='service=rac2'

From the above, you can see that the log_archive_dest_2 for each instance has been set up. Is that the reason?

Try to reset again, this time with the following statement:

Alter system reset log_archive_dest_2 sid='orcl1'

Generate the pfile file again and see that the record of orcl1.log_archive_dest_2='service=rac2' is gone, which means that the configuration has been cleared. Restart the instance and check it again:

SQL > show parameter log_archive_dest_2 NAME TYPE VALUE-- log_archive_dest_2 string

There are no more errors in the alert log.

So here comes the question: what configuration did I clear for the first time?

Experienced students should have thought of the cause of this problem. Let's move on to the following analysis.

Let's go back to the spfile file generated for the first time and see the following configuration. Let's take the log_archive_dest_1 parameter as an example

Orcl2.log_archive_dest_1='location=/home/oracle/rac2'orcl1.log_archive_dest_1='location=/home/oracle/rac1'*.log_archive_dest_1='location=+data'

There are two types of log_archive_dest_1 parameter settings, one is'*.' It starts with a'.' At the beginning.' *.' The opening setting is valid for all instances related to the database and this parameter is not set, but with'.' The opening setting is valid only for the corresponding instance of the setting.

I previously set different values for the log_archive_dest_1 of the two instances orcl1 and orcl2, respectively, and set the log_archive_dest_1 parameter values of all previous instances to 'location=+data'.

Let's look at the syntax of alter system set | reset parameter:

Set parameter

Parameter_name = parameter_value [, parameter_value]... [COMMENT = string] [DEFERRED] [{SCOPE = {MEMORY | SPFILE | BOTH} | SID = {'sid' |' *'}}.]

Reset parameter

Parameter_name [{SCOPE = SPFILE | SID = {'sid' |' *'}}.]

As you can see from the syntax above, there is an option for setting and resetting parameters: SID= {'sid' |' *'}, and the default is SID='*'.

This should explain why I was successful the first time I reset log_archive_dest_ 2, but not the second time: I deleted the entry * .log _ archive_dest_2='xxxx' the first time, and the first time I deleted it again, there was nothing in the file, so I reported an error.

This also explains why the restart still takes effect after the sid='orcl1' entry is added to the reset.

Another problem is that if there is a'*. 'for the same parameter in the parameter file. It begins with'.' When setting the parameters at the beginning, which one will Oracle choose as the parameter for the instance? let's take a look at the description of SID in the official document to answer this question:

SID The SID clause lets you specify the SID of the instance where the value will take effect.

Specify SID ='*'if you want Oracle Database to change the value of the parameter for all instances that do not already have an explicit setting for this parameter.

Specify SID = 'sid' if you want Oracle Database to change the value of the parameter only for the instance sid. This setting takes precedence over previous and subsequent ALTER SYSTEM SET statements that specify SID ='*'.

If you do not specify this clause, then:

If the instance was started up with a pfile (traditional plain-text initialization parameter file), then Oracle Database assumes the SID of the current instance.

If the instance was started up with an spfile (server parameter file), then Oracle Database assumes SID ='*'.

Tip: you can use show spparameter to view the settings for parameters in spfile

Sys@ORA11G > show spparameter log_archive_dest_1SID NAME TYPE VALUE- ora11g log_archive_dest_1 String location=/home/oracle/dump* log_archive_dest_1 string location=/u01/

Reference: http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_2014.htm#i2061284

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.