When doing database refresh for EBS databases, the following steps may save time on completing the clone faster…<\/p>\n
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.<\/p>\n
Saving Information Before Clone<\/strong><\/p>\n 1. Define a directory path in wbackupdir variable, where the backup scripts are saved.<\/p>\n 2. Save the $CONTEXT_FILE on database node<\/p>\n 3. Save the database parameter file<\/p>\n Following items are saved, logged into the database.<\/p>\n Each item will be saved to separate files, thus can be run separately if needed after database refresh.<\/p>\n 4. Save database read-only users, user accounts used in db links, etc whose password need to be preserved.<\/p>\n <\/p>\n 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].<\/p>\n 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.<\/p>\n <\/p>\n <\/p>\n <\/p>\n 7. Save Oracle Directories created in the DB<\/p>\n <\/p>\n 8. Export the APPS owned DB Links<\/p>\n <\/p>\n Perform the Database Refresh<\/strong><\/p>\n After the information is saved from the database, you may do the following steps to complete the database copy process.<\/p>\n <\/p>\n Post Database Refresh Configuration<\/strong><\/p>\n Clear source node information from FND_NODES and other configuration tables.<\/p>\n Using the $CONTEXT_FILE, run autoconfig on the database node.<\/p>\n Now, you should see the database node name in the FND_NODES table.<\/p>\n Restore Saved Configuration Information<\/p>\n Restore DB Links under APPS<\/p>\n <\/p>\n If you are not interested in the AWR and audit information in the clone instance, you may truncate those tables…<\/p>\n <\/p>\n Complete the Clone on the EBS Nodes<\/strong><\/p>\n Change APPS, ALLORACLE, any custom schema passwords using FNDCPASS to match the passwords before the database refresh.<\/p>\n Now execute autoconfig on all EBS nodes to complete the clone.<\/em><\/p>\n <\/p>\n Hope this helps… Have fun!<\/p>\n 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.<\/p>\n <\/p>\n","protected":false},"excerpt":{"rendered":" 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 … Read more<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[10],"tags":[35,38,43],"class_list":["post-73","post","type-post","status-publish","format-standard","hentry","category-ebs","tag-clone","tag-data-refresh","tag-ebs"],"acf":[],"jetpack_featured_media_url":"","jetpack-related-posts":[],"jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/posts\/73","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/comments?post=73"}],"version-history":[{"count":0,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/posts\/73\/revisions"}],"wp:attachment":[{"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/media?parent=73"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/categories?post=73"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/bijoos.com\/oraclenotes\/wp-json\/wp\/v2\/tags?post=73"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}wbackupdir=\/myhomedir\/clone\/save<\/pre>\n
echo \"Copying XML file to backup directory\"\ncp -p $CONTEXT_FILE $wbackupdir\/<\/pre>\n
echo \"Copying $ORACLE_HOME\/dbs\/ initialization parameter files\"\ncp -p $ORACLE_HOME\/dbs\/*${ORACLE_SID}.ora $wbackupdir\/<\/pre>\nsqlplus -s system\/$SYSTEMPWD <<EOF\nSET PAGES 0 FEEDBACK OFF lines 200 trims on<\/pre>\n
SPOOL $wbackupdir\/passwords.sql\n\nSELECT 'ALTER USER '||name||' IDENTIFIED BY VALUES '''||password||''';'\nFROM sys.user\\$\nWHERE name in ('SYS','SYSTEM','EBS_QUERY','NOETIX_SYS',\n'EUL10_NTX_US', 'HR_QUERY','AM_STAGE','AM_EDM_ADMIN','ZZOIM',\n'APEX_PUBLIC_USER','XXEASE_REMOTE', 'XXEASE')\nor name like '%DBLINK'\nor name like '%INT'\nORDER BY 1;\n\nSPOOL OFF<\/pre>\nDROP DIRECTORY CLONE_SAVE;\nCREATE DIRECTORY CLONE_SAVE as '\/tmp';\n\nspool $wbackupdir\/dropappslinks.sql\nselect 'drop database link ' ||db_link ||';'\nfrom dba_db_links\nwhere owner = 'APPS'\nand db_link not like 'APPS_TO_APPS%'\nand db_link not like 'EDW_APPS_TO_WH%'\norder by db_link\n\/\nspool off<\/pre>\n
spool $wbackupdir\/profile1.sql\nSELECT ('Update XDO.XDO_CONFIG_VALUES\nSet value = ' || DECODE(value, NULL, 'NULL', ''''||value||'''') || '\nWhere property_code = ' ||''''||'SYSTEM_TEMP_DIR'||''';' )\nFROM XDO.XDO_CONFIG_VALUES\nWHERE property_code = 'SYSTEM_TEMP_DIR' ;\n\nspool off<\/pre>\nspool $wbackupdir\/profile2.sql\n\nREM Site level Values\n\nSELECT ('Update APPLSYS.FND_PROFILE_OPTION_VALUES\nSET profile_option_value = ' || DECODE(fpov.profile_option_value, NULL, 'NULL', ''''||profile_option_val\nue||'''') || '\nWHERE profile_option_id = ' || fpov.profile_option_id ||';')\nFROM APPLSYS.FND_PROFILE_OPTION_VALUES fpov, APPLSYS.FND_PROFILE_OPTIONS fpo\nWHERE fpo.profile_option_id = fpov.profile_option_id AND\nfpov.level_id = 10001 AND\nfpo.profile_option_name IN\n('XXLPR_SPOOLER_URL',\n'XXLPR_OVERRIDE_POLLING_SERVER_URL',\n'XXPCK_WORK_DIRECTORY',\n'XXPCK_INBOUND_DIRECTORY',\n'XXPCK_OUTBOUND_DIRECTORY',\n'XXPCK_DISCOVERER_SERVER',\n'BNE_SERVER_LOG_PATH',\n'BNE_SERVLET_PATH',\n'BNE_UIX_BASE_PATH',\n'BNE_UIX_PHYSICAL_DIRECTORY',\n'BNE_UPLOAD_IMPORT_DIRECTORY',\n'BNE_UPLOAD_STAGING_DIRECTORY',\n'BNE_UPLOAD_TEXT_DIRECTORY',\n'POS_EXTERNAL_URL',\n'POS_INTERNAL_URL',\n'ASO_CONFIGURATOR_URL',\n'CZ_UIMGR_URL',\n'APPS_FRAMEWORK_AGENT',\n'APPS_JSP_AGENT',\n'APPS_PORTAL',\n'APPS_SERVLET_AGENT',\n'APPS_WEB_AGENT',\n'ASO_CONFIGURATOR_URL',\n'CZ_UIMGR_URL',\n'HELP_WEB_AGENT',\n'ICX_DISCOVERER_LAUNCHER',\n'ICX_DISCOVERER_VIEWER_LAUNCHER',\n'ICX_FORMS_LAUNCHER',\n'QP_PRICING_ENGINE_URL',\n'IBY_ECAPP_URL',\n'ICX_PAY_SERVER',\n'TCF:HOST'\n);\n\nspool off<\/pre>\nspool $wbackupdir\/profile3.sql\n\nREM Responsibility\/Server level Values\n\nSELECT 'Update applsys.fnd_profile_option_values\nSet profile_option_value = ' || DECODE(fpov.PROFILE_OPTION_VALUE, NULL, 'NULL',\n''''||profile_option_value||'''') ||' Where profile_option_id = ' ||\nfpov.PROFILE_OPTION_ID|| ' And level_id = '|| fpov.LEVEL_ID ||\n' And level_value2 = '|| '(select NODE_ID from applsys.fnd_nodes\nwhere NODE_NAME = '''||fn.NODE_NAME||''');'\nFROM applsys.fnd_profile_option_values fpov, applsys.fnd_nodes fn\nWHERE fn.NODE_ID = fpov.LEVEL_VALUE2 AND fpov.LEVEL_ID = 10007;\n\nselect 'update applsys.fnd_svc_comp_param_vals ',\n'set parameter_value = ''' || fscpv.PARAMETER_VALUE || '''',\n' where PARAMETER_ID= ' || fscpv.PARAMETER_ID || ';'\nfrom applsys.fnd_svc_comp_params_tl fscpt\n, applsys.fnd_svc_comp_param_vals fscpv\nwhere fscpt.display_name = 'Test Address'\nand fscpt.parameter_id = fscpv.parameter_id;\n\nselect 'commit;' from dual;\nspool off<\/pre>\n
spool $wbackupdir\/recreateoracledirs.sql\nset feedback off\nset pages 0 lines 300 trims on\nselect 'create or replace directory '|| DIRECTORY_NAME ||' as '''||DIRECTORY_PATH||''';'\nfrom dba_directories;\n\nselect 'grant '||PRIVILEGE||' on directory '||TABLE_NAME||' to '||GRANTEE||';'\nfrom dba_tab_privs\nwhere privilege in ('READ', 'WRITE');\n\nspool off<\/pre>\n\/bin\/rm \/tmp\/appsdblinks.dmp\nexpdp dumpfile=appsdblinks.dmp directory=clone_save full=y include=db_link userid=\\\"\/ as sysdba\\\"\ncp -p \/tmp\/appsdblinks.dmp $wbackupdir<\/pre>\n
\n
sqlplus apps\/prodpassword\nEXEC FND_CONC_CLONE.SETUP_CLEAN;\nCOMMIT;<\/pre>\n
cd $ORACLE_HOME\/appsutil\/clone\/bin\nperl .\/adcfgclone.pl dbconfig $CONTEXT_FILE<\/pre>\n
select NODE_NAME, SUPPORT_DB D, SUPPORT_ADMIN A, SUPPORT_CP C,\nSUPPORT_FORMS F, SUPPORT_WEB W\nfrom apps.FND_NODES\norder by 1;<\/pre>\n
sqlplus -s system\/$SYSTEMPWD <<EOF\n\nSET PAGES 0 FEEDBACK OFF lines 200 trims on\nCREATE OR REPLACE DIRECTORY CLONE_SAVE as '\/tmp';\n@passwords.sql\n@recreateoracledirs.sql\nconnect APPS\/$appspwd\n@profile1.sql\n@profile2.sql\n@profile3.sql\n@dropappslinks.sql<\/pre>\n
cp -p appsdblinks.dmp \/tmp\/appsdblinks.dmp\nimpdp directory=clone_save dumpfile=appsdblinks.dmp logfile=appsdblinks.imp.log schemas=apps userid=\\\"\/ as sysdba\\\"<\/pre>\n
sqlplus -s \"\/ as sysdba\" <<EOF\nSET PAGES 0 FEEDBACK OFF\n\nPrompt \"Cleaning up Audit and Statistics Tables\"\n\nTRUNCATE TABLE SYS.AUD\\$;\ntruncate table WRI\\$_OPTSTAT_TAB_HISTORY ;\ntruncate table WRI\\$_OPTSTAT_OPR ;\ntruncate table WRI\\$_OPTSTAT_IND_HISTORY ;\ntruncate table WRI\\$_OPTSTAT_HISTHEAD_HISTORY ;\ntruncate table WRI\\$_OPTSTAT_HISTGRM_HISTORY ;\ntruncate table WRI\\$_OPTSTAT_AUX_HISTORY ;\nexec dbms_stats.purge_stats(sysdate);\nselect dbms_stats.GET_STATS_HISTORY_AVAILABILITY from dual;\nselect dbms_stats.get_stats_history_retention from dual;\nexec dbms_stats.alter_stats_history_retention(8);\nEOF<\/pre>\n
FNDCPASS apps\/appspwd 0 Y system\/systempwd SYSTEM APPLSYS pwd_from_Dev\nFNDCPASS apps\/appspwd 0 Y system\/systempwd ALLORACLE pwd_from_Dev\nFNDCPASS apps\/appspwd 0 Y system\/systempwd ORACLE XX pwd_from_Dev \nFNDCPASS apps\/appspwd 0 Y system\/systempwd USER SYSADMIN pwd_from_Dev<\/em><\/em><\/em><\/pre>\n
cd $AD_TOP\/bin\n.\/adconfig.sh contextfile=$CONTEXT_FILE<\/pre>\n