declare
v_resp_id number;
v_resp_app_id number;
x boolean;
begin
for c1rec in (select * from fnd_user where creation_date > '13-JUL-09'
and sysdate between start_date and nvl(end_date,sysdate+1)
)
LOOP
for c2rec in (
SELECT responsibility_id,responsibility_application_id
--into v_resp_id,v_resp_app_id
FROM FND_USER_RESP_GROUPS_DIRECT where user_id=c1rec.user_id)
LOOP
fnd_user_resp_groups_api.update_assignment(
c1rec.user_id,
c2rec.responsibility_id,
c2rec.responsibility_application_id,
0,
sysdate-1,
null,
null);
end loop;
end loop;
end;
/
Backup reponsibilities of users and restore them back
cd /home/applvis/REFRESH_FILES_
exec dbms_output.enable(1000000);
set serverout on;
spool restore_resp_assignment.sql
DECLARE
v_resp_id number;
v_resp_app_id number;
x boolean;
BEGIN
FOR c1rec IN (SELECT user_id
FROM fnd_user
WHERE creation_date > sysdate-360
AND sysdate BETWEEN start_date
AND nvl(end_date,sysdate+1)
)
LOOP
FOR c2rec IN (SELECT responsibility_id,responsibility_application_id
FROM fnd_user_resp_groups_direct WHERE user_id=c1rec.user_id)
LOOP
dbms_output.put_line('fnd_user_resp_groups_api.insert_assignment('||c1rec.user_id||','
||c2rec.responsibility_id||','
||c2rec.responsibility_application_id||','
||0||','
||'sysdate-1,'
||'null,'
||'null);');
END LOOP;
END LOOP;
END;
/
Restore responsibilities with below script
cd /home/applvis/REFRESH_FILES_
@restore_resp_assignment.sql
Place Pending/(Normal/Standby) to On Hold
sqlplus apps
create table fnd_concurrent_requests_backup as select * from fnd_concurrent_requests;
UPDATE fnd_concurrent_requests
SET hold_flag = 'Y'
WHERE phase_code = 'P'
AND status_code in ('Q','I');
Commit;
SQL> update applsys.fnd_concurrent_requests
set hold_flag='Y' where phase_code='P' and hold_flag='N';
How to Correct Session Cookie Name.
a)select session_cookie_name from icx_parameters;
b)update icx_parameters set session_cookie_name = ‘
c)select session_cookie_name from icx_parameters;
Set the databases global name
The new database will still have the source databases global name. Run the following to reset it:
alter database rename global_name to
/
Alter database rename global_name to VIS.ABC.COM;
Controlfile backup
alter database backup controlfile to trace as '/home/oracle/cr_
backup users and their privileges on target database
$ sqlplus “/ as sysdba”
SQL> spool users.lst
SQL> select 'create user '||username||
' identified by values '''||password||
''' default tablespace '||default_tablespace||
' temporary tablespace '|| temporary_tablespace||';'
from dba_users
where username not in ('SYS','SYSTEM','OUTLN','DBSNMP');
SQL> select 'grant '||GRANTED_ROLE||' to '||GRANTEE||';'
from dba_role_privs
where grantee not in ('SYS','SYSTEM','OUTLN','DBSNMP');
SQL> select 'grant '||PRIVILEGE||' to '||GRANTEE||';'
from dba_sys_privs
where grantee not in ('SYS','SYSTEM','OUTLN','DBSNMP');
SQL> spool off
SQL> exit;
select 'drop user '||username||' cascade;' from dba_users where username not in ('SYS','SYSTEM','OUTLN','DBSNMP');
Verify No Profiles Pointing to PROD servers by running SQL below:set linesize 200
col profile_option_name format a40
col profile_option_value format a120
select pov.level_id,po.profile_option_name, pov.profile_option_ID
from applsys.FND_PROFILE_OPTION_VALUES pov, applsys.fnd_user fusr,
and level_id in ('10001', '10002', '10003','10007')
and pov.profile_option_id = po.profile_option_id;
Make sure nothing is pointing to SOURCE instance used for SNAP taking (sid, machine name and port).
select distinct V.PROFILE_OPTION_value
from fnd_profile_options o,
fnd_profile_option_values v
where o.application_id = v.application_id
and o.profile_option_id = v.profile_option_id
and lower (v.profile_option_value) like '%prod%';
Checking profile option values if they are pointing to source
(During cloning only site level profile options are updated to target instance).
SQL> select PO.PROFILE_OPTION_NAME,PV.PROFILE_OPTION_VALUE, PV.LEVEL_ID
from fnd_profile_option_values pv, fnd_profile_options po
where upper(PROFILE_OPTION_VALUE) like ‘%&enter_source_in_UPPERCASE%’
and pv.PROFILE_OPTION_ID=po.PROFILE_OPTION_ID;
In above query level_id represents following level
10001 - SITE
10002 - APP
10003 - RESP
10004 - USER
10005 - SERVER
10006 - ORG
10007 - ServerResponsibility
update profile options if any pointing to Source Instance
sqlplus apps/
column PROFILE_OPTION_VALUE format a80
select PROFILE_OPTION_ID,profile_option_value from fnd_profile_option_values
where profile_option_value like '%&VALUE%'
order by profile_option_value
Check for below values
source server names
source sid
source web port
Update profile options using below dml if any pointing to Source Instance.
update fnd_profile_option_values
set PROFILE_OPTION_value= '&NEW'
where PROFILE_OPTION_value = '&OLD';"
other way:
column start_value format a75
column target_value format a85
set lines 200
select profile_option_value Source_Value,
replace(replace(replace(replace(profile_option_value,'src_server','trg_server'),'PROD','PATCH'),
'/d01/applmgr/prodcomn','/d01/applptch/ptchcomn'),'/d02/oracle/proddb','/d02/oraptch/ptchdb') target_value
from fnd_profile_option_values
where profile_option_value like '%src_server%';
if you feel that the target values are correct then run the below update statement.
update fnd_profile_option_values
set profile_option_value=
replace(replace(replace(replace(profile_option_value,'src_server','trg_server'),'PROD','PATCH'),'/d01/applmgr/prodcomn','/d01/applptch/ptchcomn'),'/d02/oracle/proddb','/d02/oraptch/ptchdb')
where profile_option_value like '%src_server%';
commit;
select profile_option_value Source_Value,
replace(replace(replace(profile_option_value,'src_server','trg_server'),'PROD','PATCH'),'8000','8003') target_value
from fnd_profile_option_values
where profile_option_value like '%src_server%';
If you feel that the target values are correct then run the below update statement.
update fnd_profile_option_values
set profile_option_value=
replace(replace(replace(profile_option_value,'src_server','trg_server'),'PROD','PATCH'),'8000','8003')
where profile_option_value like '%src_server%';
update fnd_profile_option_values
set profile_option_value= null
where profile_option_value like '%ebusiness%';
Changing value for Workflow Administrator
Click on Workflow Administrator from the Navigator.
Administrator Workflow -> Administration ->
Change the value for Workflow System Administrator from “Sysadministrator” to “ Workflow Administrator”
After executing autoconfig make sure Workflow System Administrator is NOT set to SYSADMIN . Please set this to ""Workflow Administrator Web (New)""
Following script will take care of this ."
"sqlplus apps/""pwd""
SQL> update wf_resources set text = 'FND_RESP|FND|FNDWF_ADMIN_WEB_NEW|STANDARD' where name = 'WF_ADMIN_ROLE';
1 row updated.
SQL> commit;
Commit complete.
Update alerts and notifications after clone
update alr_alerts set end_date_active = sysdate -1;
update alr_alerts set enabled_flag ='N';
update wf_notifications set mail_status ='SENT' where mail_status ='MAIL';
update wf_notifications set status ='CLOSED';
After disabling alerts o/p should be
SQL> Select distinct enabled_flag from alr_alerts;
E
-
N
Update concurrent requests after clone
create table applsys.fnd_concurrent_requests_bak as
select * from applsys.fnd_concurrent_requests
where concurrent_program_id in
(select concurrent_program_id from fnd_concurrent_programs_tl
where user_concurrent_program_name
in ('Purge Concurrent Request and/or Manager Data','Purge Signon Audit data',
'Purge Obsolete Workflow Runtime Data',
'Workflow Background Process','Gather Schema Statistics'))
and
phase_code != 'C' and
status_code != 'C';
select count(*) from applsys.fnd_concurrent_requests_bak;
create table applsys.fnd_concurrent_requests_ORIG as select * from applsys.fnd_concurrent_requests ;
truncate table applsys.fnd_concurrent_requests ;
insert into applsys.fnd_concurrent_requests select * from applsys.fnd_concurrent_requests_bak;
commit;
UPDATE applsys.fnd_concurrent_requests
SET phase_code = 'C', status_code = 'X'
WHERE phase_code = 'R' and status_code ='R';
commit;
Cancel scheduled concurrent Request “Gather Schema Statistics”
sqlplus apps/apps
sql>
update fnd_concurrent_requests
set phase_code='C',status_code='D'
WHERE phase_code = 'P'
AND status_code in ('Q','I') and concurrent_program_id=38121;
Commit;
Purge the concurrent requests with some user submitted exceptions
Cancel all scheduled and pending concurrent requests with the following user exceptions. OMAMUS, OMAME, WFMGR, INVMGR
sqlplus connect apps/apps
create table fnd_concurrent_requests_bak_1 as select * from fnd_concurrent_requests where concurrent_program_id in (select concurrent_program_id from fnd_concurrent_programs_tl where user_concurrent_program_name
in ('Purge Concurrent Request and/or Manager Data','Purge Signon Audit data', 'Purge Obsolete Workflow Runtime Data','Workflow Background Process')) and phase_code!='C' and status_code!='C';
create table fnd_concurrent_requests_bak_2 as select * from fnd_concurrent_requests where
phase_code ='P' and status_code='I' and requested_by in (select user_id from fnd_user where user_name in ('OMAMUS','OMAME','WFMGR','INVMGR'));
- select count(*) from fnd_concurrent_requests_bak_2;
- select count(*) from fnd_concurrent_requests_bak_1;
connect applsys/apps
- truncate table fnd_concurrent_requests;
connect applsys/apps
- insert into fnd_concurrent_requests select * from fnd_concurrent_requests_bak_2;
- commit;
- select count(*) from fnd_concurrent_requests;
- drop table fnd_concurrent_requests_bak_2; & drop table fnd_concurrent_requests_bak_1;
None of the profiles should point to /tmp, /usr/tmp, /var/tmp etc. Query to check.
set head off
column PROFILE_OPTION_NAME format a30
column PROFILE_OPTION_VALUE format a40
select a.PROFILE_OPTION_ID,a.profile_option_name,PROFILE_OPTION_VALUE
from applsys.fnd_profile_option_values b , fnd_profile_options a
where a.PROFILE_OPTION_ID=b.PROFILE_OPTION_ID
and b.PROFILE_OPTION_VALUE like '%tmp%'
Backup the database links for the TARGET instance. , EXP /IMP
login as oratrn
Su – oratrn
Export the db links
$ exp file=link.dmp tables='sys.link$'
Export: Release 10.2.0.3.0 - Production on Tue May 6 19:56:03 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
Export done in UTF8 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table LINK$ 6 rows exported
Export terminated successfully without warnings.
$ imp file=/refresh_files_June2008/link.dmp full=y ignore=y
Import: Release 10.2.0.3.0 - Production on Tue Jun 3 09:06:35 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
Export file created by EXPORT:V10.02.01 via conventional path
import done in UTF8 character set and AL16UTF16 NCHAR character set
. importing SYS's objects into SYS
. importing SYS's objects into SYS
. . importing table "LINK$" 6 rows imported
Import terminated successfully without warnings.
To put an end date to a resposibility
update fnd_responsibility a
set a.end_date = sysdate
where a.responsibility_id =
(select t.responsibility_id
from fnd_responsibility_tl t
where t.responsibility_name = 'VIS Legacy MIS');
Clearing node information in fnd_nodes
exec fnd_conc_clone.setup_clean as apps user will delete all node information then run autoconfig to create node information in fnd_nodes;
To drop redo-logfile group
The database shuold be in Mounted state.
Select group#,status from v$log;
Alter database drop logfile group num ; (the group # which is Inactive-status);
In case of RAC :alter database disable thread 2;
To get front end url
select home_url from icx_parameters;
To check Guest user and password
SELECT fnd_web_sec.validate_login('GUEST','ORACLE') from dual;
Changing the SITENAME
sqlplus apps/
update fnd_profile_option_values set PROFILE_OPTION_VALUE= ‘VIS clone from PROD on
"Update of Logfiles Names and Nodenames
update fnd_concurrent_queues set node_name='VCOSXAOR0P' where node_name is not null and node_name in ('VMDSXAAH1I','VMDSXAAH1M','VMDSXAAH1L');
The following script will show the logfiles names and node names
select LOGFILE_NAME, LOGFILE_NODE_NAME, OUTFILE_NAME, OUTFILE_NODE_NAME from fnd_concurrent_requests;
The following script will update the logfiles names and node names
update fnd_concurrent_requests set logfile_name='/d01/oravis/viscomn/admin/log/VIS_hostanme/'||substr(logfile_name,48),
LOGFILE_NODE_NAME='hostname',
OUTFILE_NAME='/d01/oravis/viscomn/admin/log/VIS_hostname/'||substr(outfile_name,48),
OUTFILE_NODE_NAME='hostname';
Commit;
Pre-clone merge commands
For Multi-Tier clone, run the following command on each node:
cd
perl adpreclone.pl appsTier
For Single-tier clone,
On Admin Node
cd
perl adpreclone.pl appsTier merge
On Forms Node,
cd
perl adpreclone.pl appltop merge
Copy the required files for merging from source snap:
directory
- to -
directory
Copy following directories from source snap:
<806 ORACLE_HOME>
Copy XXDHM_TOP/forms
Also copy au_top/forms amd au_top/resource from source web node to target node
Steps for multi to single node clone
Copy following directories from admin snap:
•
•
•
•
•
•
Copy following directories from web snap:
• <806 ORACLE_HOME>
•
• Copy XXDHM_TOP/forms from web snap
Copy the required files for merging from web snap
directory
- to -
directory
NOTE: Please make sure to copy all the directories under
Log in to the Target node as the APPLMGR user and execute the following commands:
$ cd
$ perl adcfgclone.pl appsTier
Finishing tasks
• Check the xml file and set the login_page parameter (if it is not set already) as follows and Run AutoConfig (if changing the value):
• Log in to the target system application tier node as the APPLMGR user and run the following tasks in adadmin for all products:
o generate JAR files
o generate message files
o relink executables
o copy files to destination
• Run adadmin to verify files required at runtime. If any files are listed as missing files, you must manually copy them to the merged APPL_TOP.
• Remove the temporary directory
• Follow the post-clone steps
Following are the steps for implementing shared appl_top
Have sysadmin team rename the existing /d01 on hostname2 to /d01-old and mount the /d01 of hostname1 on to hostname2.
clone context on hostname2
cd $AD_TOP/bin
perl adclonectx.pl sharedappltop contextfile=/d01/oratst/testappl/admin/PREPROD_hostname1.xml
Note: Enable only web and forms services when the script prompts
Take the bake up of the context file on hostname1 and edit the xml file to disable the forms and web, which are enabled for single node clone.
on hostname1 go to FND_TOP/patch/115/bin and run perl -I $AU_TOP/perl/txkSOHM.pl
On hostname2 go to $FND_TOP/patch/115/bin and run perl -I $AU_TOP/perl/txkSOHM.pl
When the txkSOHM.pl scripts prompts for values, please make a note of below given entries:
On hostname1
Type of Instance [primary/secondary] : primary
Absolute path of config top : /d01/oratst/tstcomn/conf/PREPROD_hostname1
On hostname2
Type of Instance [primary/secondary] : secondary
Absolute path of config top : /d01/oratst/tstcomn/conf/PREPROD_hostname2
one more eg:
Shared file system configuration:
Running Rapid clone on target primary node
perl /d01/appltest/testcomn/clone/bin/adcfgclone.pl appsTier
(Define admin,concurrent,reports on this node)
a) On Primary Node: hostname1 as appltest
(Configure concurrent,admin,reports services on this node).
$ cd /d01/appltest/testappl/fnd/11.5.0/patch/115/bin
$ perl -I /d01/appltest/testappl/au/11.5.0/perl txkSOHM.pl
(Check for any errors before proceed)
For config_top use value: /d01/appltest/testcomn/conf/TEST_hostname1
b) On Secondary Node: hostname2 as appltest
(Configure Forms, Apache and Discoverer on this node).
$cd $AD_TOP/bin
$perl adclonectx.pl sharedappltop contextfile=/d01/appltest/testappl/admin/TEST_hostname1.xml
(Note: Enable only web and forms services when the script prompts)
$ cd /d01/appltest/testappl/fnd/11.5.0/patch/115/bin
$ perl -I /d01/appltest/testappl/au/11.5.0/perl txkSOHM.pl
For config_top use value: /d01/appltest/testcomn/conf/TEST_hostname2
The script prompts for the following information:
Prompt Response Comment
Absolute path of Applications context file
Type of Instance Secondary For all additional nodes mounting up a shared file system make sure to use secondary
Absolute path of 8.0.6 shared Oracle home <8.0.6 ORACLE_HOME location> Location of the 8.0.6 Oracle home
Absolute path of iAS shared Oracle home
Absolute path of config top
Oracle Applications APPS schema password
Example:
$ cd /d01/appltest/testappl/fnd/11.5.0/patch/115/bin
$ perl -I /d01/appltest/testappl/au/11.5.0/perl txkSOHM.pl
Absolute path of Application's Context XML file : /d01/appltest/testappl/admin/TEST_hostname2.xml
Type of Instance [primary/secondary] : secondary
Absolute path of 8.0.6 Shared Oracle Home : /d01/appltest/testora/8.0.6
Absolute path of iAS Shared Oracle Home : /d01/appltest/testora/iAS
Absolute path of config top : /d01/appltest/testcomn/conf/TEST_hostname2
Oracle Application apps schema password : ****
This command will also execute autoconfig. Make sure autoconfig completes without any errors. Also, check log file and make sure there are no errors
You can rerun the oui registration with the following script:
/d21/oravis/viscomn/admin/out/VIS_hostname/regOUI_APPSIAS_PTCH.sh
Log file located at /var/opt/oracle/oraInventory/logs/OracleHomeCloner_06150953.log
TO submit GSS
Submit ""Gather Schema Statistics"" Job in Cloned/Target Environment after Clone is completed.
1.Login to the Target Instance Application via the URL as SYSADMIN user
2.System Administrator > Concurrent > Requests > Submit a New Request > Gather Schema Statistics
3.Use parameters ALL, 90, NOBACKUP
4.Make sure the request starts running in RUNNING NORMAL
Ensure that ""Purge concurrent Manager Requests/Data"" is scheduled to run daily with AGE=7 and MODE=ALL and to be informed to client / PM.
Ensure that ""Purge Obsolete Workflow data"" is scheduled to run daily with AGE=7 and WORKFLOW_TYPE=TEMP.
Ensure that ""Purge Signon Audit data"" is scheduled to remove 2 days old data on daily basis. "
How to change Apps 11i form color change after cloning
1. Set the Java Look and Feel profile option value to oracle. If you set it to generic, it will take the default values and you wont be able to set your own color.
2. Then set Java Color Scheme profile option value to any of the LOVs as per your choice."
"It won't get effect immediately, you have to log out from
application and again you have to relogin to view color change effect
To set colour scheme
titanium
Profile Option to change = %Java%Color%Scheme%
TO generate env files for environment using autoconfig without sourceing ens since if envs are not present
Run AutoConfig setup phase on the Target System
Execute the INSTE8_SETUP phase of AutoConfig with the new context file. This will create the environment files required for the AutoPatch session:
cd
./adconfig.sh run=INSTE8_SETUP contextfile=
Note: This command does not require the environment to be sourced
Verifying the environment variables.
Now log out f the environment and re-login and make sure the env variables are all set correctly.
Run AutoConfig setup phase on the target system
Run the script $AD_TOP/bin/autoconfig.sh in the setup mode as follows: $ADTOP/bin/adconfig.sh run=INSTE8_SETUP
Create shared applications tier directories:
ls -l $APPL_TOP/admin/*xml
cd $FND_TOP/patch/115/bin
perl -I $AU_TOP/perl txkSOHM.pl
Clone the AutoConfig XML context file on the Target System
Create the target context file using the script $AD_TOP/bin/adconectx.pl.
For the URL to be prod.lenovo.com the following variables in the context file should be like this
configuration would bring the URL as http://hostname.domainname:8000 but the customer needs the URL to be like http://prod.lenovo.com:8000
So we need to update the following parameter values in the XML file and then autoconfig.
login_page oa_var="s_login_page">http://prod.lenovo.com:8000/oa_servlets/AppsLogin
webentryhost oa_var="s_webentryhost">prod
webentrydomain oa_var="s_webentrydomain">lenovo.com
add responsibility from backend
begin
fnd_user_pkg.addresp(
'&User_Name',
'&Responsablity_Application_Short_Name',
'&Responsibility_Key',
'&Security_Group',
'&Description',
'&Start_Date',
'&End_Date' );
commit;
end;
EX:
exec fnd_user_pkg.addresp('
Purge Signon Audit Data
executes $FND_TOP/sql/FNDSCPRG.sql
Check the profile option "Sign-on: Audit level" and make sure it is set to ""Form"". This is required in order to monitor forms sessions.
In case your curious, there are 4 valid values for this profile option: None, User, Reponsibility and Form. As you progress from None to Form additional information is collected and stored.
None - Obvious, nothing is audited.
User - FND_LOGINS table gets updated with one record per user session.
Reponsibility - Same as User, as well, FND_LOGIN_RESPONSIBILITIES will be updated with 1 record for each responsibility used during the session.
Form - Same as user and responsibility, plus FND_LOGIN_RESP_FORMS will be updated with one record for each form selected during the session.
As with any audting, you should determine how long you require the data and purge/archive it.
In order to purge data in the above tables you need to schedule
the concurrent program, ""Purge Signon Audit Data"". This program requires one parameter, Audit Date. All data older than this date will be deleted.
Note: this program deletes data in the above tables as well as FND_UNSUCCESSFUL_LOGINS.
Beware: Enabling auditing does have a slight impact on performance.
Script for hot backup
cr_hot_backup.sql
set lines 999 pages 999
set verify off
set feedback off
set heading off
spool begin_backup.sql
select 'alter tablespace ' || tablespace_name || ' begin backup;' tsbb
from dba_tablespaces
where contents != 'TEMPORARY'
order by tablespace_name
/
spool off
spool end_backup.sql
select 'alter tablespace ' || tablespace_name || ' end backup;' tseb
from dba_tablespaces
where contents != 'TEMPORARY'
order by tablespace_name
/
spool off
1 comment:
Thanks For Sharing. We are Providing clone app developmentservices.
Post a Comment