Steps to clear Production credential in cloned EBS instance

Whenever we clone Production env to Test env, we may often require to change all its password and credential before giving it developers to perform setup and testing.

You dont need to know Production password to perform these action items.

Here are the Steps to clear  Production credential in cloned EBS instance

Invalidating all the password in database

1) Start the database clone for the first time
startup restrict

2) Clear all production credentials from the cloned database

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 password 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 statement on sqlplus / as sysdba
2) Use below command to reset password for the Oracle EBS schema’s

FNDCPASS apps/clone 0 Y system/clone ALLORACLE clone

3) Generate a shell script to set password 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,APPLSYS password again.
FNDCPASS apps/clone 0 Y system/clone SYSTEM APPLSYS clone