6 Replies Latest reply: Apr 24, 2013 5:58 AM by Artem S. Tsygankov RSS

    exec "alter system" and setup UTL_MAIL on Primary and replicate to Standby

    Artem S. Tsygankov
      Hi!
      I have the next configuration:

      DGMGRL> show configuration verbose;
      Configuration - work_DataGuardConfig
      Protection Mode: MaxAvailability
      Databases:
      primary - Primary database
      physical1 - Physical standby database
      physical2 - Physical standby database
      Properties:
      FastStartFailoverThreshold = '30'
      OperationTimeout = '30'
      FastStartFailoverLagLimit = '30'
      CommunicationTimeout = '180'
      FastStartFailoverAutoReinstate = 'TRUE'
      FastStartFailoverPmyShutdown = 'TRUE'
      BystandersFollowRoleChange = 'ALL'
      Fast-Start Failover: DISABLED
      Configuration Status:
      SUCCESS

      How to properly setup UTL_MAIL package in my configuration (Primary and Standby Servers), e.g.:
      1. If I execute " @$ORACLE_HOME/rdbms/admin/utlmail.sql " on Primary, it will replicate to Standby?
      2. If I execute " alter system set smtp_out_server='smtp.server.local' scope=both " on Primary, it will replicate to Standby?
      3. If I execute
      begin
      dbms_network_acl_admin.create_acl (
      acl => 'utl_mail.xml',
      description => 'Allow mail to be send',
      principal => 'SCOTT',
      is_grant => TRUE,
      privilege => 'connect'
      );
      commit;
      end;
      on Primary, this changes will replicate to Standby?
        • 1. Re: exec "alter system" and setup UTL_MAIL on Primary and replicate to Standby
          Mahir M. Quluzade
          user12024575 wrote:
          Hi!
          I have the next configuration:

          DGMGRL> show configuration verbose;
          Configuration - work_DataGuardConfig
          Protection Mode: MaxAvailability
          Databases:
          primary - Primary database
          physical1 - Physical standby database
          physical2 - Physical standby database
          Properties:
          FastStartFailoverThreshold = '30'
          OperationTimeout = '30'
          FastStartFailoverLagLimit = '30'
          CommunicationTimeout = '180'
          FastStartFailoverAutoReinstate = 'TRUE'
          FastStartFailoverPmyShutdown = 'TRUE'
          BystandersFollowRoleChange = 'ALL'
          Fast-Start Failover: DISABLED
          Configuration Status:
          SUCCESS

          How to properly setup UTL_MAIL package in my configuration (Primary and Standby Servers), e.g.:
          1. If I execute " @$ORACLE_HOME/rdbms/admin/utlmail.sql " on Primary, it will replicate to Standby?
          2. If I execute " alter system set smtp_out_server='smtp.server.local' scope=both " on Primary, it will replicate to Standby?
          3. If I execute
          begin
          dbms_network_acl_admin.create_acl (
          acl => 'utl_mail.xml',
          description => 'Allow mail to be send',
          principal => 'SCOTT',
          is_grant => TRUE,
          privilege => 'connect'
          );
          commit;
          end;
          on Primary, this changes will replicate to Standby?
          Hi,

          In all cases (1,2,3) you are doing only system changes. System changes is not effective on standby database.

          For example in your case : alter system set smtp_out_server='smtp.server.local' scope=both;
          this script only set a parameter of database, how to you think this parameter must be chaged on standby?

          Is it logically?
          Then when we change log_archive_dest_2 for transport redo to standby, then this parameter will change on standby. is it true?

          Regards
          Mahir M. Quluzade
          http://www.mahir-quluzade.com
          • 2. Re: exec "alter system" and setup UTL_MAIL on Primary and replicate to Standby
            JohnWatson
            1. If I execute " @$ORACLE_HOME/rdbms/admin/utlmail.sql " on Primary, it will replicate to Standby?
            2. If I execute " alter system set smtp_out_server='smtp.server.local' scope=both " on Primary, it will replicate to Standby?
            3. If I execute
            begin
            dbms_network_acl_admin.create_acl (
            acl => 'utl_mail.xml',
            description => 'Allow mail to be send',
            principal => 'SCOTT',
            is_grant => TRUE,
            privilege => 'connect'
            );
            commit;
            end;
            on Primary, this changes will replicate to Standby?
            1. yes
            2. no
            3. yes
            If you think about whether the operation generates redo, then all becomes clear.
            • 3. Re: exec "alter system" and setup UTL_MAIL on Primary and replicate to Standby
              Artem S. Tsygankov
              It's not true, I agree.
              So, I must change these parameters on each database?
              Should I do some actions in Data Guard for setup UTL_MAIL?
              • 5. Re: exec "alter system" and setup UTL_MAIL on Primary and replicate to Standby
                Artem S. Tsygankov
                JohnWatson wrote:
                2. no
                3. yes
                If you think about whether the operation generates redo, then all becomes clear.
                Thanks JohnWatson!
                I execute on Standby (after install UTL_MAIL on Primary):

                select a.OWNER,a.OBJECT_NAME,a.OBJECT_TYPE,a.status,a.CREATED
                from all_objects a
                where a.OBJECT_NAME like '%UTL_MAIL%'
                and a.OBJECT_TYPE='PACKAGE';

                and I see UTL_MAIL on Standby.

                And (on Standby):

                select d.ACL, d.host
                from DBA_NETWORK_ACLS d
                where d.ACL like '%mail%';

                I see ACL /sys/acls/utl_mail.xml and host.
                • 6. Re: exec "alter system" and setup UTL_MAIL on Primary and replicate to Standby
                  Artem S. Tsygankov
                  Mahir M. Quluzade wrote:
                  Yes, you must change on all databases.
                  Thanks Mahir M. Quluzade!
                  But it is just enough to execute "alter system" on all DB.