An instance of RAC cannot be mounted due to the configuration of the DG Broker.
Today, I encountered a fault in my own lab. It seemed very simple at first, but it was actually interesting and easy to ignore if I was not careful. We believe that there will also be customers in the production environment.
Environment: Oracle 11.2.0.4 RAC (2 nodes Primary + 2 nodes Standby)
Background: There was no problem when the experiment environment was set up. Various tests have been conducted later, including adding a new storage directory to the master database. Therefore, you need to modify the db_file_name_convert parameter of the slave database and add corresponding relationships.
I didn't care too much about modifying a parameter. At that time, the database was restarted successfully. As a result, after the standby database restarted again, two standby nodes started normally, if another node fails to start, the following error is returned:
SQL> shutdown abortORACLE instance shut down.SQL> startupORACLE instance started.Total System Global Area 534462464 bytesFixed Size 2254952 bytesVariable Size 444598168 bytesDatabase Buffers 83886080 bytesRedo Buffers 3723264 bytesORA-01105: mount is incompatible with mounts by other instancesORA-01677: standby file name convert parameters differ from other instance
The error message is obvious, that is, the convert parameter configuration is inconsistent with that of other instances. The check is true:
The convert parameters of an instance that cannot be started are as follows:
SQL> show parameter convertNAME TYPE VALUE------------------------------------ ----------- ------------------------------db_file_name_convert string +data1/jyzhao, +data/mynaslog_file_name_convert string +data1/jyzhao, +data/mynas, +f ra1/jyzhao, +fra/mynas
The convert parameters of a properly started instance are as follows:
SQL> show parameter convertNAME TYPE VALUE------------------------------------ ----------- ------------------------------db_file_name_convert string +fra1/jyzhao, +fra/mynas, +dat a1/jyzhao, +data/mynaslog_file_name_convert string +data1/jyzhao, +data/mynas, +f ra1/jyzhao, +fra/mynas
Obviously, the values of the two instances of db_file_name_convert are inconsistent.
Here, the first response is to see if the parameter files on both sides are consistent?
The answer is that the content of the parameter file is completely consistent, and the value of db_file_name_convert corresponding to the parameter file is.
*.db_file_name_convert='+data1/jyzhao','+data/mynas'
Background: the value of the Standby RAC parameter is*.db_file_name_convert='+data1/jyzhao','+data/mynas'
Later, the parameter values of the standby database were modified:
SQL> alter system set db_file_name_convert = '+fra1/jyzhao', '+fra/mynas', '+data1/jyzhao', '+data/mynas' scope=spfile;
But it does not seem to take effect now. Is there any negligence in the last modification?
Test again with the correct value:
SQL> alter system set db_file_name_convert = '+fra1/jyzhao', '+fra/mynas', '+data1/jyzhao', '+data/mynas' scope=spfile;System altered.SQL> shutdown abortORACLE instance shut down.SQL> startupORACLE instance started.Total System Global Area 534462464 bytesFixed Size 2254952 bytesVariable Size 444598168 bytesDatabase Buffers 83886080 bytesRedo Buffers 3723264 bytesDatabase mounted.Database opened.SQL> SQL> show parameter convertNAME TYPE VALUE------------------------------------ ----------- ------------------------------db_file_name_convert string +fra1/jyzhao, +fra/mynas, +dat a1/jyzhao, +data/mynaslog_file_name_convert string +data1/jyzhao, +data/mynas, +f ra1/jyzhao, +fra/mynas
You can see that after the modification, the instance can be started normally, and the parameter value is displayed correctly.
What happened last time? Shut down the instance again and restart the instance. The problem is found again:
SQL> SQL> SQL> shutdown abortORACLE instance shut down.SQL> startupORACLE instance started.Total System Global Area 534462464 bytesFixed Size 2254952 bytesVariable Size 444598168 bytesDatabase Buffers 83886080 bytesRedo Buffers 3723264 bytesORA-01105: mount is incompatible with mounts by other instancesORA-01677: standby file name convert parameters differ from other instanceSQL> show parameter convertNAME TYPE VALUE------------------------------------ ----------- ------------------------------db_file_name_convert string +data1/jyzhao, +data/mynaslog_file_name_convert string +data1/jyzhao, +data/mynas, +f ra1/jyzhao, +fra/mynasSQL> select status from v$instance;STATUS------------STARTED
Finally, the Database alert finds clues, that is, after manually modifying the parameters, after the database is started successfully, it is automatically changed to the original value.
Wed Jan 03 05:47:06 2018Starting Data Guard Broker (DMON)Wed Jan 03 05:47:06 2018INSV started with pid=41, OS id=26314Physical standby database opened for read only access.Completed: ALTER DATABASE OPENWed Jan 03 05:47:10 2018NSV0 started with pid=42, OS id=26321Wed Jan 03 05:47:14 2018RSM0 started with pid=43, OS id=26331 Using STANDBY_ARCHIVE_DEST parameter default value as USE_DB_RECOVERY_FILE_DESTALTER SYSTEM SET log_archive_trace=0 SCOPE=BOTH SID='jyzhao1';ALTER SYSTEM SET log_archive_format='%t_%s_%r.dbf' SCOPE=SPFILE SID='jyzhao1';ALTER SYSTEM SET standby_file_management='AUTO' SCOPE=BOTH SID='*';ALTER SYSTEM SET archive_lag_target=0 SCOPE=BOTH SID='*';ALTER SYSTEM SET log_archive_max_processes=4 SCOPE=BOTH SID='*';ALTER SYSTEM SET log_archive_min_succeed_dest=1 SCOPE=BOTH SID='*';ALTER SYSTEM SET db_file_name_convert='+data1/jyzhao','+data/mynas' SCOPE=SPFILE;ALTER SYSTEM SET log_file_name_convert='+data1/jyzhao','+data/mynas','+fra1/jyzhao','+fra/mynas' SCOPE=SPFILE;ALTER SYSTEM SET fal_server='jyzhao' SCOPE=BOTH;Wed Jan 03 05:47:53 2018Decreasing number of real time LMS from 1 to 0
Now you know the answer to the question.
You can also think about what alert described in this section?
The truth is:
The DG Environment Once configured the DG Broker when simulating a customer's real scenario for the DG test. The configuration information of the DG Broker is not updated normally after various changes in the subsequent environment.
- Configure and test Oracle RAC 11g DG Broker
There are two solutions:
First, delete the configuration of the DG Broker and stop using it. The simplest thing is to set the dgbroker not to start.
The second is to continue using the DG Broker, but you need to reconfigure it correctly.
Next, reconfigure the DG Broker and modify the parameters in the master database:
You can refer to the above article to reconfigure the DG Broker.
Original Value:
db_file_name_convert string +data/mynas, +data1/jyzhao
Modify parameters of the master database:
SQL> alter system set db_file_name_convert = '+data/mynas', '+data1/jyzhao', '+fra/mynas', '+fra1/jyzhao' scope=spfile;
Modify the parameters of the slave database:
SQL> alter system set db_file_name_convert = '+fra1/jyzhao', '+fra/mynas', '+data1/jyzhao', '+data/mynas' scope=spfile;
Or not:
Broker command:
show database verbose jyzhao;show database verbose mynas;
Update the configuration in the broker (root cause ):
edit database 'jyzhao' set property 'DbFileNameConvert'='+data/mynas, +data1/jyzhao, +fra/mynas, +fra1/jyzhao';edit database 'mynas' set property 'DbFileNameConvert'='+fra1/jyzhao, +fra/mynas, +data1/jyzhao, +data/mynas';
It seems that the configuration of the DG Broker must be consistent with that of the database.
Summary: it is very important to be a qualified DBA, and you should be familiar with all kinds of common tools, such as the DG Broker here.