- Whenever we clone Production env to Test env, we may often be required to change all its passwords and credentials before giving it to developers to perform setup and testing.
- You dont need to know the Production password to perform these action items.
- Here are the Steps to clear Production credentials in the cloned EBS instance
Invalidating all the passwords in the database
(1) Start the database clone for the first time
startup restrict
(2) Clear all production credentials from the cloned database( This step is not mandatory, we can avoid it also)
update SYS.user$ set
password = translate(password,'0123456789ABCDEF','0000000000000000')
where type#=1 and length(password) = 16
/
update APPLSYS.FND_ORACLE_USERID set
ENCRYPTED_ORACLE_PASSWORD='INVALID'
/
update APPLSYS.FND_USER set
ENCRYPTED_FOUNDATION_PASSWORD='INVALID',
ENCRYPTED_USER_PASSWORD='INVALID'
/
commit;
(3)Shutdown the database
shutdown exit
Steps to bootstrap the cloned instance
(1) sqlplus / as sysdba
alter user SYS identified by CLONE;
alter user SYSTEM identified by CLONE;
alter user APPLSYSPUB identified by CLONE;
alter user APPLSYS identified by CLONE;
alter user APPS identified by CLONE;
(2) Provide boot-strap info for FNDCPASS…
update APPLSYS.FND_ORACLE_USERID set
ENCRYPTED_ORACLE_PASSWORD='CLONE'
where ORACLE_USERNAME = 'APPLSYSPUB'
/
update APPLSYS.FND_ORACLE_USERID set
ENCRYPTED_ORACLE_PASSWORD='ZG' ||
'B27F16B88242CE980EF07605EF528F9391899B09552FD89FD' ||
'FF43E4DDFCE3972322A41FBB4DDC26DDA46A446582307D412'
where ORACLE_USERNAME = 'APPLSYS'
/
update APPLSYS.FND_ORACLE_USERID set
ENCRYPTED_ORACLE_PASSWORD='ZG' ||
'6CC0BB082FF7E0078859960E852F8D123C487C024C825C0F9' ||
'B1D0863422026EA41A6B2B5702E2299B4AC19E6C1C23333F0'
where ORACLE_USERNAME = 'APPS'
/
commit;
(3) We run as SYS, now connect as APPS to run some plsql
connect APPS/CLONE
(a) Every EBS database needs a GUEST user
select APPS.fnd_web_sec.change_guest_password( 'CLONE', 'CLONE' ) "RES"
from dual;
commit;
(b) Set GUEST credential in site level profile option
set serveroutput on
declare
dummy boolean;
begin
dummy := APPS.FND_PROFILE.SAVE('GUEST_USER_PWD', 'GUEST/CLONE', 'SITE');
if not dummy then
dbms_output.put_line( 'Error setting GUEST_USER_PWD profile' );
end if;
end;
/
commit;
(4) One more time for luck (avoid session caching of profiles)
connect APPS/CLONE
REM Set SYSADMIN password
select APPS.fnd_web_sec.change_password('SYSADMIN','CLONE') "RES"
from dual;
commit;
exit
Steps to set other passwords in EBS instance
(1) Generate a sql script to set passwords for db users not managed with EBS
select 'alter user "'|| USERNAME ||'" identified by CLONE; '
from SYS.DBA_USERS
where USERNAME not in (select ORACLE_USERNAME from APPLSYS.FND_ORACLE_USERID)
and USERNAME not in ('SYS','SYSTEM');
It will generate the alter statement, Run those statements on sqlplus / as sysdba
(2) Use the below command to reset the password for the Oracle EBS schema’s
FNDCPASS apps/clone 0 Y system/clone ALLORACLE clone
3) Generate a shell script to set passwords for non-EBS db users managed with EBS
select 'FNDCPASS apps/clone 0 Y system/clone ORACLE "' ||
replace(ORACLE_USERNAME,'$','\$') || '" clone'
from APPLSYS.FND_ORACLE_USERID
where READ_ONLY_FLAG = 'X'
and ORACLE_USERNAME in (select USERNAME from SYS.DBA_USERS);
Finally, reset the APPS, and APPLSYS passwords again.
FNDCPASS apps/clone 0 Y system/clone SYSTEM APPLSYS clone
