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