#########################################    User Synchronization notification in Oracle Express ########################################################
-- Description: This process will notify by mail for following condition:
                               --  If user password mismatch  in Primary and Secondary Database
                               -- If a user in primary database does not exist in Secondary Database
                               -- If a user in secondary database does not exist in Primary Database

-- If you get mail regarding new user/password miss-match you need to fix that manually
-- I Needed to synchronize user in Oracle Express database and since Oracle Express don't have many features for replication therefore I have done this below mentioned way



#################################################
-- On Secondary database
#################################################
SQL> create user user_sync identified by <PWD>;

SQL> grant create session to user_sync;


SQL> grant select on sys.user$ to user_sync;


SQL> grant select on dba_users to user_sync;


#################################################
-- On primary database
#################################################
-- As sys user
SQL> create user user_sync identified by <PWD>;

SQL> grant create session to user_sync;


SQL> grant create procedure to user_sync;


SQL> grant select on sys.user$ to user_sync;


SQL> grant select on dba_users to user_sync;


SQL> grant create database link to user_sync;

SQL> grant all on UTL_SMTP to user_sync;


SQL>  grant all on UTL_TCP to user_sync;

SQL>
BEGIN
  DBMS_NETWORK_ACL_ADMIN.create_acl (
    acl          => 'user_sync.xml',
    description  => 'Purpose of the acl is user synchronization between XE database',
    principal    => 'USER_SYNC',
    is_grant     => TRUE,
    privilege    => 'connect',
    start_date   => SYSTIMESTAMP,
    end_date     => NULL);

  DBMS_NETWORK_ACL_ADMIN.assign_acl (
    acl         => 'user_sync.xml',
    host        => '127.0.0.1',
    lower_port  => 1,
    upper_port  => 9999);

  COMMIT;
END;
/


-- As user_sync User
 SQL> CREATE DATABASE LINK XE_USER_SYN CONNECT TO user_sync IDENTIFIED BY <PWD> USING 'STEGE_XE2';      -- Make sure you have enty in tnsnames.ora

SQL>
CREATE OR REPLACE PROCEDURE send_mail (p_to        IN VARCHAR2,
                                       p_from      IN VARCHAR2,
                                       p_subject   IN VARCHAR2,
                                       p_message   IN VARCHAR2,
                                       p_smtp_host IN VARCHAR2,
                                       p_smtp_port IN NUMBER DEFAULT 25)
AS
  l_mail_conn   UTL_SMTP.connection;
BEGIN
  l_mail_conn := UTL_SMTP.open_connection(p_smtp_host, p_smtp_port);
  UTL_SMTP.helo(l_mail_conn, p_smtp_host);
  UTL_SMTP.mail(l_mail_conn, p_from);
  UTL_SMTP.rcpt(l_mail_conn, p_to);

  UTL_SMTP.open_data(l_mail_conn);

  UTL_SMTP.write_data(l_mail_conn, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_mail_conn, 'To: ' || p_to || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_mail_conn, 'From: ' || p_from || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_mail_conn, 'Subject: ' || p_subject || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_mail_conn, 'Reply-To: ' || p_from || UTL_TCP.crlf || UTL_TCP.crlf);

  UTL_SMTP.write_data(l_mail_conn, p_message || UTL_TCP.crlf || UTL_TCP.crlf);
  UTL_SMTP.close_data(l_mail_conn);

  UTL_SMTP.quit(l_mail_conn);
END;
/


SQL>
CREATE OR REPLACE PROCEDURE USER_SYNC IS
v_password VARCHAR2(30);
v_missing_user_primary VARCHAR2(30);
v_missing_user_secondery VARCHAR2(30);
BEGIN

select
listagg (s1.name, ',')
WITHIN GROUP
(ORDER BY s2.name) "Password"
into v_password
from sys.user$ s1
join dba_users d1 on(D1.USERNAME=S1.NAME)
join sys.user$@XE_USER_SYN s2 on(s2.name=S1.name)
join dba_users@XE_USER_SYN d2 on(D2.USERNAME=S2.NAME)
where D2.ACCOUNT_STATUS='OPEN'
and D2.ACCOUNT_STATUS='OPEN'
and S1.password!=S2.password
order by s1.name;

select
listagg (s1.name, ',')
WITHIN GROUP
(ORDER BY s1.name) "User Name"
into v_missing_user_primary
from sys.user$ s1
join dba_users d1 on(D1.USERNAME=S1.NAME)
where s1.name not in (select s2.name from sys.user$@XE_USER_SYN s2 join dba_users@XE_USER_SYN d2 on(D2.USERNAME=S2.NAME) and D2.ACCOUNT_STATUS='OPEN')
and D1.ACCOUNT_STATUS='OPEN';

select
listagg (s2.name, ',')
WITHIN GROUP
(ORDER BY s2.name) "User Name"
into v_missing_user_secondery
from sys.user$@XE_USER_SYN s2
join dba_users@XE_USER_SYN d2 on(D2.USERNAME=S2.NAME)
where s2.name not in (select s1.name from sys.user$ s1 join dba_users d1 on(D1.USERNAME=S1.NAME) and D1.ACCOUNT_STATUS='OPEN')
and D2.ACCOUNT_STATUS='OPEN' ;

if (v_password IS not null) THEN send_mail(p_to=> 'mohammad.nazmulhuda@netsize.com',p_from=> 'oracle@ipx.com',p_subject   => 'Password Need to Synchoronized XE',p_message   => v_password,p_smtp_host => '127.0.0.1'); end if;
if (v_missing_user_primary IS not null) THEN send_mail(p_to=> 'mohammad.nazmulhuda@netsize.com',p_from=> 'oracle@ipx.com',p_subject   => 'User need to create in Secondary Database XE',p_message   => v_missing_user_primary,p_smtp_host => '127.0.0.1'); end if;
if (v_missing_user_secondery IS not null) THEN send_mail(p_to=> 'mohammad.nazmulhuda@netsize.com',p_from=> 'oracle@ipx.com',p_subject   => 'User Need to Create in Primary Database XE',p_message   => v_missing_user_secondery,p_smtp_host => '127.0.0.1'); end if;
END;
/



-- Execute this procedure daily/hourly from script
SQL> EXECUTE USER_SYNC;