######################################### 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;