Thursday, August 16, 2018

New Features for PL/SQL in Oracle 18c

Following two new PL/SQL features came with Oracle 18c Database -

PL/SQL Hierarchical Profiler (DBMS_HPROF) Enhancements

PL/SQL Qualified Expressions
Posted by at No comments:

Oracle VirtualBox 5.2.18 is here!

Oracle VirtualBox 5.2.18 is here (released August 14 2018)

Just an update in case you missed it- 

For details-
https://www.virtualbox.org/wiki/Changelog#18

The following items were fixed and/or added:

    VMM: See user manual.
    VMM: fix loading with recent binutils and self-built versions of VirtualBox (bug #17851)
    NAT: fix --nataliasmode sameports which is a valid setting (bug #13000)
    VRDP: fixed VM process termination on RDP client disconnect if 3D is enabled for the virtual machine
Posted by at No comments:

Monday, August 13, 2018

Sending email from Oracle Database with PL/SQL.

Here is my recent PL/SQL package to send email directly from Oracle database with attachment or without attachment. I used other UTL packages before but this one UTL_TCP looks better. I am using it and found very efficient.

Note: please review it, test it before using any production environment. thanks.

If you face  ORA-24247: network access denied by access control list (ACL) . please find the solution in below link-
https://halimdba.blogspot.com/2015/02/ora-24247-network-access-denied-by.html

 SQL> select * from dba_network_acls;


1) Package specification 

 CREATE OR REPLACE PACKAGE SAY_HELLO_FROM_DB  
 IS  
   PROCEDURE pro_send_emails_with_attach (  
    sender_email_address     VARCHAR2,   
    recipient_email_addresses   VARCHAR2,  
    email_subject         VARCHAR2,  
    email_body_text        VARCHAR2,  
    email_attachment_text     CLOB,    
    email_attachment_file_name  VARCHAR2);  
   PROCEDURE pro_send_emails_without_attach (  
    sender_email_address     VARCHAR2,   
    recipient_email_addresses  VARCHAR2,  
    email_subject        VARCHAR2,  
    email_body_text       VARCHAR2);  
 END;  
 /  

2)  Package body


 CREATE OR REPLACE PACKAGE BODY SAY_HELLO_FROM_DB  
 IS  
   PROCEDURE pro_send_emails_with_attach (  
    sender_email_address     VARCHAR2,  
    recipient_email_addresses   VARCHAR2,  
    email_subject         VARCHAR2,  
    email_body_text        VARCHAR2,  
    email_attachment_text     CLOB,  
    email_attachment_file_name  VARCHAR2)  
   IS  
    c                UTL_TCP.connection;  
    rc               BINARY_INTEGER;  
    v_next_column          BINARY_INTEGER;  
    v_recipient_email_length    BINARY_INTEGER;  
    v_recipient_email_addresses   VARCHAR2 (500);  
    v_single_recipient_email_addr  VARCHAR2 (100);  
    j                BINARY_INTEGER;  
    current_position        BINARY_INTEGER := 1;  
    email_attachment_text_segment  CLOB;  
   --- v_date varchar2(100);  
   BEGIN  
    v_recipient_email_addresses := recipient_email_addresses;  
    v_recipient_email_length := LENGTH (v_recipient_email_addresses);  
    v_recipient_email_addresses := v_recipient_email_addresses || ',';  
    v_next_column := 1;  
    IF INSTR (v_recipient_email_addresses, ',') = 0  
    THEN  
      v_single_recipient_email_addr := v_recipient_email_addresses;  
      v_recipient_email_length := 1;  
    END IF;  
    -- -- CHANGE SMTP SERVER'S ip AND port  
    c := UTL_TCP.open_connection ('192.168.X.XXX', 25);  
    rc := UTL_TCP.write_line (c, 'HELO localhost');  
    DBMS_OUTPUT.put_line (UTL_TCP.get_line (c, TRUE));  
    WHILE v_next_column <= v_recipient_email_length  
    LOOP  
      v_single_recipient_email_addr :=  
       SUBSTR (  
         v_recipient_email_addresses,  
         v_next_column,  
          INSTR (v_recipient_email_addresses, ',', v_next_column)  
         - v_next_column);  
      v_next_column :=  
       INSTR (v_recipient_email_addresses, ',', v_next_column) + 1;  
      rc := UTL_TCP.write_line (c, 'MAIL FROM: ' || sender_email_address);  
      DBMS_OUTPUT.put_line (UTL_TCP.get_line (c, TRUE));  
      rc :=  
       UTL_TCP.write_line (c,  
                 'RCPT TO: ' || v_single_recipient_email_addr);  
      DBMS_OUTPUT.put_line (UTL_TCP.get_line (c, TRUE));  
      rc := UTL_TCP.write_line (c, 'DATA');  
      DBMS_OUTPUT.put_line (UTL_TCP.get_line (c, TRUE));  
      rc :=  
       UTL_TCP.write_line (  
         c,  
         'Date: ' || TO_CHAR (SYSDATE, 'dd Mon yy hh24:mi:ss'));  
      ----   v_date := 'Date: ' || TO_CHAR (SYSDATE, 'dd Mon yy hh24:mi:ss') ;  
      rc := UTL_TCP.write_line (c, 'From: ' || sender_email_address);  
      rc := UTL_TCP.write_line (c, 'MIME-Version: 1.0');  
      rc := UTL_TCP.write_line (c, 'To: ' || v_single_recipient_email_addr);  
      rc := UTL_TCP.write_line (c, 'Subject: ' || email_subject);  
      rc := UTL_TCP.write_line (c, 'Content-Type: multipart/mixed;');  
      rc := UTL_TCP.write_line (c, ' boundary="-----SECBOUND"');  
      rc := UTL_TCP.write_line (c, '');  
      rc := UTL_TCP.write_line (c, '-------SECBOUND');  
      rc := UTL_TCP.write_line (c, 'Content-Type: text/plain');  
      rc := UTL_TCP.write_line (c, 'Content-Transfer-Encoding: 7bit');  
      rc := UTL_TCP.write_line (c, '');  
      rc := UTL_TCP.write_line (c, email_body_text);  
      rc := UTL_TCP.write_line (c, '');  
      rc := UTL_TCP.write_line (c, '-------SECBOUND');  
      rc := UTL_TCP.write_line (c, 'Content-Type: text/plain;');  
      rc :=  
       UTL_TCP.write_line (  
         c,  
         ' name="' || email_attachment_file_name || '"');  
      rc := UTL_TCP.write_line (c, 'Content-Transfer_Encoding: 8bit');  
      rc := UTL_TCP.write_line (c, 'Content-Disposition: attachment;');  
      rc := UTL_TCP.write_line (c, '');  
      current_position := 1;  
      j := (ROUND (LENGTH (email_attachment_text)) / 32000) + 1;  
      DBMS_OUTPUT.put_line (  
       'j= ' || j || ' length= ' || LENGTH (email_attachment_text));  
      FOR i IN 1 .. j  
      LOOP  
       email_attachment_text_segment :=  
         SUBSTR (email_attachment_text, current_position, 32000);  
       rc := UTL_TCP.write_line (c, email_attachment_text_segment);  
       current_position := current_position + 32000;  
      END LOOP;  
      rc := UTL_TCP.write_line (c, '-------SECBOUND--');  
      rc := UTL_TCP.write_line (c, '');  
      rc := UTL_TCP.write_line (c, '.');  
      DBMS_OUTPUT.put_line (UTL_TCP.get_line (c, TRUE));  
      DBMS_OUTPUT.put_line (  
         '***** Single E-mail= '  
       || v_single_recipient_email_addr  
       || ' '  
       || v_next_column);  
    END LOOP;  
    rc := UTL_TCP.write_line (c, 'QUIT');  
    DBMS_OUTPUT.put_line (UTL_TCP.get_line (c, TRUE));  
    UTL_TCP.close_connection (c);  
   END;  
   PROCEDURE pro_send_emails_without_attach (  
    sender_email_address     VARCHAR2,  
    recipient_email_addresses  VARCHAR2,  
    email_subject        VARCHAR2,  
    email_body_text       VARCHAR2)  
   IS  
    c                UTL_TCP.connection;  
    rc               BINARY_INTEGER;  
    v_next_column          BINARY_INTEGER;  
    v_recipient_email_length    BINARY_INTEGER;  
    v_recipient_email_addresses   VARCHAR2 (500);  
    v_single_recipient_email_addr  VARCHAR2 (100);  
   BEGIN  
    v_recipient_email_addresses := recipient_email_addresses;  
    v_recipient_email_length := LENGTH (v_recipient_email_addresses);  
    v_recipient_email_addresses := v_recipient_email_addresses || ',';  
    v_next_column := 1;  
    IF INSTR (v_recipient_email_addresses, ',') = 0  
    THEN  
      v_single_recipient_email_addr := v_recipient_email_addresses;  
      v_recipient_email_length := 1;  
    END IF;  
    -- CHANGE SMTP SERVER'S ip AND port  
    c := UTL_TCP.open_connection ('192.168.XX.XX', 25);  
    DBMS_OUTPUT.put_line (UTL_TCP.get_line (c, TRUE));  
    rc := UTL_TCP.write_line (c, 'HELO localhost');  
    DBMS_OUTPUT.put_line (UTL_TCP.get_line (c, TRUE));  
    WHILE v_next_column <= v_recipient_email_length  
    LOOP  
      v_single_recipient_email_addr :=  
       SUBSTR (  
         v_recipient_email_addresses,  
         v_next_column,  
          INSTR (v_recipient_email_addresses, ',', v_next_column)  
         - v_next_column);  
      v_next_column :=  
       INSTR (v_recipient_email_addresses, ',', v_next_column) + 1;  
      rc := UTL_TCP.write_line (c, 'MAIL FROM: ' || sender_email_address);  
      DBMS_OUTPUT.put_line (UTL_TCP.get_line (c, TRUE));  
      rc :=  
       UTL_TCP.write_line (c,  
                 'RCPT TO: ' || v_single_recipient_email_addr);  
      DBMS_OUTPUT.put_line (UTL_TCP.get_line (c, TRUE));  
      rc := UTL_TCP.write_line (c, 'DATA');  
      DBMS_OUTPUT.put_line (UTL_TCP.get_line (c, TRUE));  
      rc :=  
       UTL_TCP.write_line (  
         c,  
         'Date: ' || TO_CHAR (SYSDATE, 'dd Mon yy hh24:mi:ss'));  
      rc := UTL_TCP.write_line (c, 'From: ' || sender_email_address);  
      rc := UTL_TCP.write_line (c, 'MIME-Version: 1.0');  
      rc := UTL_TCP.write_line (c, 'To: ' || v_single_recipient_email_addr);  
      rc := UTL_TCP.write_line (c, 'Subject: ' || email_subject);  
      rc := UTL_TCP.write_line (c, email_body_text);  
      rc := UTL_TCP.write_line (c, '.');  
      DBMS_OUTPUT.put_line (UTL_TCP.get_line (c, TRUE));  
      DBMS_OUTPUT.put_line (  
         '***** Single E-mail= '  
       || v_single_recipient_email_addr  
       || ' '  
       || v_next_column);  
    END LOOP;  
    rc := UTL_TCP.write_line (c, 'QUIT');  
    DBMS_OUTPUT.put_line (UTL_TCP.get_line (c, TRUE));  
    UTL_TCP.close_connection (c);  
   END;  
 END;  
 / 

3)  Calling the procedure for this package for sending email

If you want send to multiple person, please use comma separated  email address as below
In SQL plus -
exec SAY_HELLO_FROM_DB.pro_send_emails_with_attach('Halim@test.com','Halim1@test.com,Halim2@test.com','Test From Database','Check the alter log','have you checked alertlog today?','attention');

Posted by at No comments:

Friday, July 13, 2018

Finding first day and last day of the year in Oracle

I am thinking these are the easiest way to find out the first day and last day of a year in oracle.  If you have one please write in a comment. I will update it here. thanks.

 First Day of Previous Year

SELECT ADD_MONTHS (TRUNC (SYSDATE,’YEAR’), -12) FROM DUAL;

Last Day of Previous Year

SELECT ADD_MONTHS (TRUNC (SYSDATE, ‘YEAR’), -1 ) +30 FROM DUAL;

 First Day of Current Year

SELECT TRUNC (SYSDATE , ‘YEAR’) FROM DUAL;

Last Day of Current Year

SELECT ADD_MONTHS(TRUNC (SYSDATE ,’YEAR’),12)-1 FROM DUAL;
Posted by at 1 comment:

Wednesday, June 13, 2018

How to do Flashback Database in oracle 12c active data guard environment

Here are the easy steps-


There was a requirement to run stress testing (provably it may run 24 hours) in production active data guard database environment (DB version - Enterprise Edition Release 12.1.0.2.0 – 64bit). 

Stress testing will create lots of testing data and after that they want to rollback all testing transactions.  Means they want to go back to the state where it was before stress testing start. 

So for that, I recommended to use flashback Database over point-in-time recovery. because it is very easy ,quick and with less impact on database availability. 

I suggested following two step by step Methods as per their environment.

you guys can use any of them. both worked fine for me.

Two methods are below-  

Method #1:   
May use if Data guard’s  protechtion mode is -  MAXIMUM PERFORMANCE.
else use Method #2 .

So in this case, we can plan to shutdown standby server before stress testing and start after flashback database on primary database.  Steps are below-

   1)      Confirming flashback enabled on both primary and standby databases.

SQL> select database_role,flashback_on from v$database;

   2)   Just before stress testing –  Cancel Managed recovery on standby server and shutdown    standby server.


     SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

          3)   Create a guaranteed restore point in primary.
           
              SQL> CREATE RESTORE POINT before_stress_test_pr GUARANTEE FLASHBACK DATABASE;
 


          4) After stress testing ,
 On primary –     
                      SQL>   SHUTDOWN IMMEDIATE  ;
                       SQL> STARTUP MOUNT ;
                         SQL>  FLASHBACK DATABASE TO RESTORE POINT before_stress_test_pr;
                         SQL> ALTER DATABASE OPEN RESETLOGS ;
 
         Checking SCN number in primary –(just for confirmation it is greater that standby by SCN number later) 
 
                 SQL>  SELECT CURRENT_SCN FROM V$DATABASE;
 
             5)    Start the standby server .
6)      Start Managed recovery on the standby –
 
                 SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

             7)     Drop guaranteed restore point from primary.

             SQL> DROP RESTORE POINT before_stress_test_pr;

               
Method #2
This one can be used any situation.

1)      Confirming flashback enabled on both primary and standby databases.

SQL> select database_role,flashback_on from v$database;

2)      Create a guaranteed restore point in standby  ( just before stress testing start)

CREATE RESTORE POINT before_stress_test_st GUARANTEE FLASHBACK DATABASE;

3)      After few minutes, create a guaranteed restore point in primary (just before stress testing start)

CREATE RESTORE POINT before_stress_test_pr GUARANTEE FLASHBACK DATABASE;

 After stress testing ,
4)       On primary –     
           SQL>   SHUTDOWN IMMEDIATE  ;
           SQL> STARTUP MOUNT ;
                         SQL>  FLASHBACK DATABASE TO RESTORE POINT before_stress_test_pr;
                         SQL> ALTER DATABASE OPEN RESETLOGS ;
 
  Checking SCN number in primary –(just for confirmation it is greater that standby by SCN number later) 
 
             SQL>  SELECT CURRENT_SCN FROM V$DATABASE;
 
5)      On Standby – 
     SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; 
 
Checking SCN number in standby – for confirmation it is lesser than primary SCN number.
 
     SQL> SELECT CURRENT_SCN FROM V$DATABASE;
 
     SQL> FLASHBACK STANDBY DATABASE TO RESTORE POINT before_stress_test_st  ;
 
6)       Start Managed recovery on the standby –
 
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

7)      Drop both guaranteed restore point.




More Usability of Flashback Database-

Typically, in following situations you can use Flashback Database. 

  • A user error or corruption removes needed data or introduces corrupted data. For example, a user or DBA might erroneously delete or update the contents of one or more tables, drop database objects that are still needed during an update to an application, or run a large batch update that fails midway.
  • A database upgrade fails or an upgrade script goes awry.
  • A complete database recovery after a media failure cannot succeed because you do not have all of the needed redo logs or incremental backups.


Posted by at No comments:
Subscribe to: Posts (Atom)