Wednesday, 11 July 2018

Broken DG configuration

Few days ago I 've run into the following situation on a DG configuration:
  • the primary shipped logs to the standby properly, but reported errors if asked for status in dgmgrl
    DGMGRL> show configuration
    
    Configuration - dbd
    
      Protection Mode: MaxPerformance
      Databases:
        db1d - Primary database
        db2d - Physical standby database
    
    Fast-Start Failover: DISABLED
    
    Configuration Status:
    ORA-16610: command "Broker operation" in progress
    DGM-17017: unable to determine configuration status
    
  • the standby initially did not process archivelogs, but after configuration reset (disable and enable operations) it started to process them, but also reported errors in dgmgrl
  • it was possible to log in from the primary to the standby
  • it was possible to log in from the standby to the primary
  • I copied password file from the primary to the standby to ensure this is the same file
  • when calling show database on the primary the errors were reported for the standby
  • when calling show database on the standby the errors were reported for the primary
What was the cause?
It showed that the problem was generated by the TNS entries on the primary - apparently the TNS alias definitions for the primary and the standby there included some blank characters, which caused the alias to be read improperly in case of dgmgrl - at the same time simple call to those aliases through sqlplus went ok.
Once I removed all the formatting in the tnsnames.ora, whole thing started to work properly. What is a bit interesting is that the errors in the TNS were present only on the primary, while the dgmgrl calls malfunctioned on both ends.

Role order

Lately a colleague of mine run into the problem with too many roles - some account have them 150, while maximally allowed are 148. He wanted to establish the order in which the roles are assigned to the user as obviously only the excess over 148 was thrown away.
He has found that the roles are loaded with the following query:
select /*+ connect_by_filtering */ privilege#,level from sysauth$ connect by grantee#=prior privilege# and privilege#>0 start with grantee#=:1 and privilege#>0; 
Thus it seems as the the order may be random, though it is possible that usually the data is returned in the order of insertion.