User Synchronization in Oracle Express

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