When doing database refresh for EBS databases, the following steps may save time on completing the clone faster…
Copy the code relevant to your environment to a file and run the script file prior to the clone to save the configuration information. Once database refresh completed, you may restore the values to the database and then run autoconfig to complete the clone.
Saving Information Before Clone
1. Define a directory path in wbackupdir variable, where the backup scripts are saved.
wbackupdir=/myhomedir/clone/save
2. Save the $CONTEXT_FILE on database node
echo "Copying XML file to backup directory"
cp -p $CONTEXT_FILE $wbackupdir/
3. Save the database parameter file
echo "Copying $ORACLE_HOME/dbs/ initialization parameter files"
cp -p $ORACLE_HOME/dbs/*${ORACLE_SID}.ora $wbackupdir/
Following items are saved, logged into the database.
sqlplus -s system/$SYSTEMPWD <<EOF
SET PAGES 0 FEEDBACK OFF lines 200 trims on
Each item will be saved to separate files, thus can be run separately if needed after database refresh.
4. Save database read-only users, user accounts used in db links, etc whose password need to be preserved.
SPOOL $wbackupdir/passwords.sql
SELECT 'ALTER USER '||name||' IDENTIFIED BY VALUES '''||password||''';'
FROM sys.user\$
WHERE name in ('SYS','SYSTEM','EBS_QUERY','NOETIX_SYS',
'EUL10_NTX_US', 'HR_QUERY','AM_STAGE','AM_EDM_ADMIN','ZZOIM',
'APEX_PUBLIC_USER','XXEASE_REMOTE', 'XXEASE')
or name like '%DBLINK'
or name like '%INT'
ORDER BY 1;
SPOOL OFF
5. To preserve the database links, we use expdp. But need to drop the database links before doing the import. Following code will generate database link drop statements [only drop private DB Links under APPS user].
DROP DIRECTORY CLONE_SAVE;
CREATE DIRECTORY CLONE_SAVE as '/tmp';
spool $wbackupdir/dropappslinks.sql
select 'drop database link ' ||db_link ||';'
from dba_db_links
where owner = 'APPS'
and db_link not like 'APPS_TO_APPS%'
and db_link not like 'EDW_APPS_TO_WH%'
order by db_link
/
spool off
6. Save the profile values that need to be preserved. Second SQL capture site level profile values of interest. Add/remove as appropriate for your environment.
spool $wbackupdir/profile1.sql
SELECT ('Update XDO.XDO_CONFIG_VALUES
Set value = ' || DECODE(value, NULL, 'NULL', ''''||value||'''') || '
Where property_code = ' ||''''||'SYSTEM_TEMP_DIR'||''';' )
FROM XDO.XDO_CONFIG_VALUES
WHERE property_code = 'SYSTEM_TEMP_DIR' ;
spool off
spool $wbackupdir/profile2.sql
REM Site level Values
SELECT ('Update APPLSYS.FND_PROFILE_OPTION_VALUES
SET profile_option_value = ' || DECODE(fpov.profile_option_value, NULL, 'NULL', ''''||profile_option_val
ue||'''') || '
WHERE profile_option_id = ' || fpov.profile_option_id ||';')
FROM APPLSYS.FND_PROFILE_OPTION_VALUES fpov, APPLSYS.FND_PROFILE_OPTIONS fpo
WHERE fpo.profile_option_id = fpov.profile_option_id AND
fpov.level_id = 10001 AND
fpo.profile_option_name IN
('XXLPR_SPOOLER_URL',
'XXLPR_OVERRIDE_POLLING_SERVER_URL',
'XXPCK_WORK_DIRECTORY',
'XXPCK_INBOUND_DIRECTORY',
'XXPCK_OUTBOUND_DIRECTORY',
'XXPCK_DISCOVERER_SERVER',
'BNE_SERVER_LOG_PATH',
'BNE_SERVLET_PATH',
'BNE_UIX_BASE_PATH',
'BNE_UIX_PHYSICAL_DIRECTORY',
'BNE_UPLOAD_IMPORT_DIRECTORY',
'BNE_UPLOAD_STAGING_DIRECTORY',
'BNE_UPLOAD_TEXT_DIRECTORY',
'POS_EXTERNAL_URL',
'POS_INTERNAL_URL',
'ASO_CONFIGURATOR_URL',
'CZ_UIMGR_URL',
'APPS_FRAMEWORK_AGENT',
'APPS_JSP_AGENT',
'APPS_PORTAL',
'APPS_SERVLET_AGENT',
'APPS_WEB_AGENT',
'ASO_CONFIGURATOR_URL',
'CZ_UIMGR_URL',
'HELP_WEB_AGENT',
'ICX_DISCOVERER_LAUNCHER',
'ICX_DISCOVERER_VIEWER_LAUNCHER',
'ICX_FORMS_LAUNCHER',
'QP_PRICING_ENGINE_URL',
'IBY_ECAPP_URL',
'ICX_PAY_SERVER',
'TCF:HOST'
);
spool off
spool $wbackupdir/profile3.sql
REM Responsibility/Server level Values
SELECT 'Update applsys.fnd_profile_option_values
Set profile_option_value = ' || DECODE(fpov.PROFILE_OPTION_VALUE, NULL, 'NULL',
''''||profile_option_value||'''') ||' Where profile_option_id = ' ||
fpov.PROFILE_OPTION_ID|| ' And level_id = '|| fpov.LEVEL_ID ||
' And level_value2 = '|| '(select NODE_ID from applsys.fnd_nodes
where NODE_NAME = '''||fn.NODE_NAME||''');'
FROM applsys.fnd_profile_option_values fpov, applsys.fnd_nodes fn
WHERE fn.NODE_ID = fpov.LEVEL_VALUE2 AND fpov.LEVEL_ID = 10007;
select 'update applsys.fnd_svc_comp_param_vals ',
'set parameter_value = ''' || fscpv.PARAMETER_VALUE || '''',
' where PARAMETER_ID= ' || fscpv.PARAMETER_ID || ';'
from applsys.fnd_svc_comp_params_tl fscpt
, applsys.fnd_svc_comp_param_vals fscpv
where fscpt.display_name = 'Test Address'
and fscpt.parameter_id = fscpv.parameter_id;
select 'commit;' from dual;
spool off
7. Save Oracle Directories created in the DB
spool $wbackupdir/recreateoracledirs.sql
set feedback off
set pages 0 lines 300 trims on
select 'create or replace directory '|| DIRECTORY_NAME ||' as '''||DIRECTORY_PATH||''';'
from dba_directories;
select 'grant '||PRIVILEGE||' on directory '||TABLE_NAME||' to '||GRANTEE||';'
from dba_tab_privs
where privilege in ('READ', 'WRITE');
spool off
8. Export the APPS owned DB Links
/bin/rm /tmp/appsdblinks.dmp
expdp dumpfile=appsdblinks.dmp directory=clone_save full=y include=db_link userid=\"/ as sysdba\"
cp -p /tmp/appsdblinks.dmp $wbackupdir
Perform the Database Refresh
After the information is saved from the database, you may do the following steps to complete the database copy process.
- Shutdown EBS and Database Sevices
- Startup database in mount status, restricted mode
- Drop database
- Restore the initialization parameter file saved in step 3
- Using RMAN or other methods, perform clone of the database [RMAN duplicate].
Post Database Refresh Configuration
Clear source node information from FND_NODES and other configuration tables.
sqlplus apps/prodpassword
EXEC FND_CONC_CLONE.SETUP_CLEAN;
COMMIT;
Using the $CONTEXT_FILE, run autoconfig on the database node.
cd $ORACLE_HOME/appsutil/clone/bin
perl ./adcfgclone.pl dbconfig $CONTEXT_FILE
Now, you should see the database node name in the FND_NODES table.
select NODE_NAME, SUPPORT_DB D, SUPPORT_ADMIN A, SUPPORT_CP C,
SUPPORT_FORMS F, SUPPORT_WEB W
from apps.FND_NODES
order by 1;
Restore Saved Configuration Information
sqlplus -s system/$SYSTEMPWD <<EOF
SET PAGES 0 FEEDBACK OFF lines 200 trims on
CREATE OR REPLACE DIRECTORY CLONE_SAVE as '/tmp';
@passwords.sql
@recreateoracledirs.sql
connect APPS/$appspwd
@profile1.sql
@profile2.sql
@profile3.sql
@dropappslinks.sql
Restore DB Links under APPS
cp -p appsdblinks.dmp /tmp/appsdblinks.dmp
impdp directory=clone_save dumpfile=appsdblinks.dmp logfile=appsdblinks.imp.log schemas=apps userid=\"/ as sysdba\"
If you are not interested in the AWR and audit information in the clone instance, you may truncate those tables…
sqlplus -s "/ as sysdba" <<EOF
SET PAGES 0 FEEDBACK OFF
Prompt "Cleaning up Audit and Statistics Tables"
TRUNCATE TABLE SYS.AUD\$;
truncate table WRI\$_OPTSTAT_TAB_HISTORY ;
truncate table WRI\$_OPTSTAT_OPR ;
truncate table WRI\$_OPTSTAT_IND_HISTORY ;
truncate table WRI\$_OPTSTAT_HISTHEAD_HISTORY ;
truncate table WRI\$_OPTSTAT_HISTGRM_HISTORY ;
truncate table WRI\$_OPTSTAT_AUX_HISTORY ;
exec dbms_stats.purge_stats(sysdate);
select dbms_stats.GET_STATS_HISTORY_AVAILABILITY from dual;
select dbms_stats.get_stats_history_retention from dual;
exec dbms_stats.alter_stats_history_retention(8);
EOF
Complete the Clone on the EBS Nodes
Change APPS, ALLORACLE, any custom schema passwords using FNDCPASS to match the passwords before the database refresh.
FNDCPASS apps/appspwd 0 Y system/systempwd SYSTEM APPLSYS pwd_from_Dev
FNDCPASS apps/appspwd 0 Y system/systempwd ALLORACLE pwd_from_Dev
FNDCPASS apps/appspwd 0 Y system/systempwd ORACLE XX pwd_from_Dev
FNDCPASS apps/appspwd 0 Y system/systempwd USER SYSADMIN pwd_from_Dev
Now execute autoconfig on all EBS nodes to complete the clone.
cd $AD_TOP/bin
./adconfig.sh contextfile=$CONTEXT_FILE
Hope this helps… Have fun!
PS: Please comment on the approach and method. If there is better, faster, easier method, would love to know. Here the assumption is all patch levels on the database side and EBS side are same, just need more recent data in the non-production instance for testing purposes.
