This discussion is archived
6 Replies Latest reply: Apr 24, 2013 3:58 AM by Artem S. Tsygankov RSS

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

Artem S. Tsygankov Newbie
Currently Being Moderated
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
    MahirM.Quluzade Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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?
  • 4. Re: exec "alter system" and setup UTL_MAIL on Primary and replicate to Standby
    MahirM.Quluzade Guru
    Currently Being Moderated
    Yes, you must change on all databases.
  • 5. Re: exec "alter system" and setup UTL_MAIL on Primary and replicate to Standby
    Artem S. Tsygankov Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points