Monday 16 February 2015

DATA GUARD BROKER CONFIGURATION

In this article we are going to learn how to set up Data Guard broker for managing data guard.
Primary and standby database server details:
Primary Server: ora1-1.mydomain Database: TESTDB
Standby Server: ora1-2.mydomain Database: STANDBY
Primary Database Details:
 
[oracle@NVMBD1BZY150D00 ~]$ sqlplus / as sysdba
 
SQL*Plus: Release 11.2.0.3.0 Production on Thu Feb 12 16:06:52 2015
 
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
 
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
SQL> select status,instance_name,database_role,protection_mode from v$database,v$instance;
 
STATUS       INSTANCE_NAME    DATABASE_ROLE    PROTECTION_MODE
------------ ---------------- ---------------- --------------------
OPEN         TESTDB           PRIMARY          MAXIMUM PERFORMANCE
 
SQL> select max(sequence#) from v$archived_log;
 
MAX(SEQUENCE#)
--------------
           803
 
Standby Database Details:
SQL> select status,instance_name,database_role,protection_mode from v$database,v$instance;
 
STATUS       INSTANCE_NAME    DATABASE_ROLE    PROTECTION_MODE
------------ ---------------- ---------------- --------------------
MOUNTED      STANDBY          PHYSICAL STANDBY MAXIMUM PERFORMANCE
 
SQL> select max(sequence#) from v$archived_log;
 
MAX(SEQUENCE#)
--------------
           803
 
On both primary and standby databases, enable the dataguard broker process by setting the value of the parmater “DG_BROKER_START” to TRUE.
SQL> alter system set dg_broker_start=true;
 
System altered.
 
Add a static entry for the DGMGRL in the listener.ora file on both the primary and standby servers.
On Primary:
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = TESTDB_DGMGRL)
      (ORACLE_HOME = /data/oracle/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = TESTDB)
    )
On Standby:
   (SID_DESC =
      (GLOBAL_DBNAME = STANDBY_DGMGRL)
      (ORACLE_HOME = /data/oracle/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = STANDBY)
    )
Listener.ora file contents on primary server:
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.11.9.2)(PORT = 1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = TESTDB_DGMGRL )
      (ORACLE_HOME = /data/oracle/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = TESTDB)
    )
  )
Listener.ora file contents on standby server:
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.13.9.3)(PORT = 1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )
 
 
SID_LIST_LISTENER =
   (SID_DESC =
      (GLOBAL_DBNAME = STANDBY_DGMGRL )
      (ORACLE_HOME = /data/oracle/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = STANDBY)
    )
 
  )
On the primary server, create the Dataguard Broker configuration.
 
[oracle@NVMBD1BZY150D00 ~]$ dgmgrl
 
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production
 
Copyright (c) 2000, 2009, Oracle. All rights reserved.
 
Welcome to DGMGRL, type "help" for information.
 
DGMGRL> connect sys/sys@TESTDB
 
Connected.
 
DGMGRL> create configuration 'dgtest' as primary database is 'TESTDB' connect identifier is TESTDB;
 
Configuration "dgtest" created with primary database "TESTDB"
 
DGMGRL> show configuration;
 
Configuration - dgtest
 
  Protection Mode: MaxPerformance
  Databases:
    TESTDB - Primary database
 
Fast-Start Failover: DISABLED
 
Configuration Status:
DISABLED
 
Add the standby database “SRPSTB” to the configuration created above.
DGMGRL> add database 'STANDBY' as connect identifier is STANDBY maintained as physical;
 
Database "STANDBY" added
 
DGMGRL> show configuration;
 
Configuration - dgtest
 
  Protection Mode: MaxPerformance
  Databases:
    TESTDB  - Primary database
    STANDBY - Physical standby database
 
Fast-Start Failover: DISABLED
 
Configuration Status:
DISABLED
 
The configuration added, needs to be enabled.
 
DGMGRL> enable configuration;
 
Enabled.
 
DGMGRL> show configuration
 
Configuration - dgtest
 
  Protection Mode: MaxPerformance
  Databases:
    TESTDB  - Primary database
    STANDBY - Physical standby database
 
Fast-Start Failover: DISABLED
 
Configuration Status:
SUCCESS
 
 
Setup is complete, you are ready to use DG Broker.
 
Few Monitorable properties to troubleshoot

DGMGRL> SHOW DATABASE 'TESTPRI' 'StatusReport';
DGMGRL> SHOW DATABASE 'TESTPRI' 'LogXptStatus';
DGMGRL> SHOW DATABASE 'TESTPRI' 'InconsistentProperties';
DGMGRL> SHOW DATABASE 'TESTPRI' 'InconsistentLogXptProps';
DGMGRL> SHOW DATABASE 'TESTDG' 'StatusReport';
DGMGRL> SHOW DATABASE 'TESTDG' 'LogXptStatus';
DGMGRL> SHOW DATABASE 'TESTDG' 'InconsistentProperties';
DGMGRL> SHOW DATABASE 'TESTDG' 'InconsistentLogXptProps';

Equivalent Broker Commands to 'ALTER SYSTEM'

SQL> alter database recover managed standby database cancel;
DGMGRL> edit database 'stby_dbname' set state='LOG-APPLY-OFF';

SQL> alter database recover managed standby database disconnect;
DGMGRL> edit database 'stby_dbname' set state='ONLINE';

SQL> alter system set log_archive_max_processes=4;
DGMGRL> edit database 'dbname' set property 'LogArchiveMaxProcesses'=4;

SQL> alter system set log_archive_dest_state_2='enable' scope=both;
DGMGRL> edit database 'stby_dbname' set property 'LogShipping'='ON';

SQL> alter system set log_archive_dest_state_2='defer' scope=both;
DGMGRL> edit database 'stby_dbname' set property 'LogShipping'='OFF';

DGMGRL> edit database 'pri_dbname' set state='LOG-TRANSPORT-OFF';
This will defer all standby databases
 
 
I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!
 
 
Posted by at No comments:
Label

Thursday 5 February 2015

Enable Oracle database to send emails via SMTP server



Sending e-mails from within the Oracle database using the UTL_MAIL PL/SQL package used to be quite easy in Oracle 10g. However, in Oracle 11gR2, things have changed.To enable SMTP package to send mail we need to perform following steps.

1.       First, you need to actually install the UTL_MAIL package. It’s not installed by default on 11g

2.       Next, you need to add the address and port of the e-mail server to the “smtp_out_server” initialization parameter.

3.       Next to create an Access Control List (ACL) for your e-mail server and grant the necessary users access to this ACL.

4.        Create the wrapper procedure in PL/SQL.

Let’s see above steps in detail.
STEP1: Install the UTL_MAIL package. It’s not installed by default on 11g
$ sqlplus /nolog
 
SQL*Plus: Release 11.2.0.3.0 Production on Fri Feb 6 10:21:07 2015
 
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
 
SQL> connect / as sysdba
 
Connected.
 
SQL> @?/rdbms/admin/utlmail.sql
 
Package created.
 
Synonym created.
 
SQL> @?/rdbms/admin/prvtmail.plb
 
Package created.
 
Package body created.
 
Grant succeeded.
 
Package body created.
 
No errors.
 
SQL> grant execute on utl_mail to public;
 
Grant succeeded.
 
STEP2: add the address and port of the e-mail server to the “smtp_out_server” initialization parameter.


You need to add the address and port of the e-mail server to the “smtp_out_server” initialization parameter. If you do not do this, you will receive “ORA-06502: PL/SQL: numeric or value error” error when you try to use the UTL_MAIL package.

Execute the following with user SYS as SYSDBA:

SQL> alter system set smtp_out_server = 'mymailserver@mydomain.com:25' scope=both;
 
3. Next to create an Access Control List (ACL) for your e-mail server and grant the necessary user’s access to this ACL.
The network utility family of PL/SQL packages, such as UTL_TCP, UTL_INADDR, UTL_HTTP, UTL_SMTP, and UTL_MAIL, allow Oracle users to make network callouts from the database using raw TCP or using higher-level protocols built on raw TCP. As of Oracle 11g a new package DBMS_NETWORK_ACL_ADMIN allows fine-grained control over network hosts access using access control lists (ACL) implemented by XML DB. Below I show major steps that are required to enable ACL for user SCOTT. You have to run them as SYS or SYSDBA user. Read Oracle documentation for more details.

Without an ACL, you will receive the following error:

ORA-24247: network access denied by access control list (ACL)

Create an ACL with one database user in Oracle database
BEGIN
   DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
    acl          => 'mail_access.xml',
    description  => 'Permissions to access e-mail server.',
    principal    => 'PUBLIC',
    is_grant     => TRUE,
    privilege    => 'connect');
   COMMIT;
END;
 
 Assign an ACL to one or more network hosts
BEGIN
   DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
    acl          => 'mail_access.xml',
    host         => 'mymailserver@mydomain.com',
    lower_port   => 25,
    upper_port   => 25
    );
   COMMIT;
END;
 
Confirm the created ACL is correct
SELECT host, lower_port, upper_port, acl
FROM   dba_network_acls
/
 
SELECT acl,
principal,
privilege,
is_grant,
TO_CHAR(start_date, 'DD-MON-YYYY HH24:MI') AS start_date,
TO_CHAR(end_date, 'DD-MON-YYYY') AS end_date
FROM   dba_network_acl_privileges
/
 
4. Create the wrapper procedure in PL/SQL
 
CREATE OR REPLACE PROCEDURE SEND_MAIL (
   p_sender       IN   VARCHAR2,
   p_recipients   IN   VARCHAR2,
   p_cc           IN   VARCHAR2 DEFAULT NULL,
   p_bcc          IN   VARCHAR2 DEFAULT NULL,
   p_subject      IN   VARCHAR2,
   p_message      IN   VARCHAR2,
   p_mime_type    IN   VARCHAR2 DEFAULT 'text/plain; charset=us-ascii'
)
IS
 BEGIN
   UTL_MAIL.SEND (sender          => p_sender,
                  recipients      => p_recipients,
                  cc              => p_cc,
                  bcc             => p_bcc,
                  subject         => p_subject,
                  message         => p_message,
                  mime_type       => p_mime_type
                 );
EXCEPTION
   WHEN OTHERS
   THEN
      RAISE;
END send_mail;
/
 
5. After these steps, you should be able to successfully send e-mails from within the database:
 
Begin
send_mail(
p_sender => ‘ora11gtest@mydomain.com’,
p_recipients => ‘Amit@mydomain.com’,
p_subject => ‘This is the subject line!’,
p_message => ‘Hello World!’);
end;

*Action:
anonymous block completed
 
 
 
I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!
Posted by at 5 comments:
Label
Subscribe to: Posts (Atom)