In R12 you can set your SQL session context for a single OU with the following

BEGIN

  execute mo_global.set_policy_context('S',2);

END;

•The ‘S’ means Single Org Context

•

•2 is the ORG_ID I want set

 

 ============================= 

ERP Cloning 11i Procedure

 Cloning 11i Procedure

Preclone in the source machine while users are application is running without allowing users to access i.e. to run adpreclone.pl

1. Preclone must be run in the DBTier first then in AppsTier

i)             #su  - oracle

ii)           $ cd

/oracle/proddb/9.2.0/appsutil/scripts/<context_name>

iii)          $perl adpreclone.pl dbTier

iv)          Exit

v)            # su – applmgr

vi)          $ cd /oracle

vii)         $cd COMMON_TOP

viii)       cd admin/scripts/<context_name>

ix)          $perl adpreclone.pl appTier

2. After running adpreclone.pl, SHUTDOWN the applications and then DB properly
3. Take a cold backup of /oracle
1. Ensure new machine OS has been properly installed and configured with new IP and domain name.
4. Restore the cold backup in the target machine
5. Run the adcfgclone.pl in the target machine in the appropriate directory

While Configuration follow the given set of instructions:

# su – oracle

$ cd /oracle

$ cd $ORACLE_HOME

$ cd appsutil/clone/bin

$ perl adcfgclone.pl dbTier

1. DBTIER
1. Do you want to use a virtual hostname for the target node (y/n) [n]?Y
2. Target hostname [linux3]:linux5
3. Target instance is a real Application Cluster (RAC) instance (y/n) [n]: N
4. Target system database name[prod]:Clone

[default value found for S_contextname:Clone_linux5

5. Target system RDBMS_ORACLE_HOME directory

[/oracle/visdb/9.2.0] – press ENTER key

6. Target system utl-file accessible directories list [/usr/tmp]:

                        Press ENTER

7. Number of DATA_TOP’s on the target system [1]:1

Since this is a single node

8. Target system DATA_TOP [/oracle/visdata]: Press ENTER

Message would appearing as follows this is just for knowledge.. creating /oracle/visdb/9.2.0/appsutil/clone/bin/../data/stage/addbhometgt.xml which will contain target system database mount points

9.  Do you want to preserve the Display set to linux3:0.0(y/n) [y]

Type ‘N’ and press ENTER

10. Target system display [Linux5:0.0] press ENTER

**** In case of error message

Error message would appears as: File oratab already exists at /etc/oratab

File oraInst.loc already exists at /etc/oraInst.loc

Solutions follows –

Go to /tmp and run oraInstroot.sh

May be for twice from within the same window from where you executed this perl adcfglone.pl

2. APPTIER

# su – applmgr

$ cd /oracle/prodcomm/clone/bin

$  perl adcfgclone.pl appsTier

a)    Do you want to use a virtual hostname for the target node (y/n) [n]:Y

b)    Target hostname [linux3]:linux5

c)    Target system database SID [PROD]: CLONE

(Default value found for S_Contextname:Clone_linux5)

d)    Target system database server node [linux3]:linux5

e)    Target system database domain name:com

f)     Does the target system have more than one application tier server node (y/n):[n] N

g)    Is the target APPL_TOP divided into multiple mount points (y/n) [n]:N

h)    Target system APPL_TOP mount point [/oracle/prodcomm] press ENTER

i)     Target system COMMON_TOP mount point [/oracle/prodappl] press ENTER

j)     Target system 8.0.6 ORACLE_HOME directory /oracle/prodora/8.0.6? Press ENTER

k)    Target system iAS ORACLE_HOME directory [/oracle/prodora/iAS]: Press ENTER

l)     Do you want to preserve the display set to linux3:0.0 (y/n) [y]N

m)  Target system display[linux5:0.0]:Press ENTER

n)    Do you want to preserve the port values from the source system on the target system (y/n) [y]Y

o)    UTL_FILE_DIR on database tier consists of following directories

1)    /usr/tmp

2)    /oracle/proddb/9.2.0/appsutil/outbound/CLONE_linux5

Choose a value which will be set as APPLPTMP value on the target node [1] default

Change the directory (path) of oraInventory as /etc/

Remove one entry

Create manually the directory oraInventory under /etc by the following this command

# mkdir oraInventory

And then run the command orainstRoot.sh

DB level clone directory /oracle/9.2.0/appsutil/clone

Application level directory /oracle/viscomm/clone

Cloning step – Copy all the folders from /oracle/*.* to the target machine /Oracle/

Autoconfig in R12

Autoconfig is tool/utility to reconfigure your Oracle Application configuration files using context (XML file) and template files. 
This post covers steps to run Autoconfig in Oracle Apps R12 (steps are similar to 11i, only change is location of autoconfig script and log files)
.
A) Running Autoconfig on R12 environment Application Tier
1. Login as user owning application tierfor R12 (usually applmgr)
2. Set environment variable by executing env file $INSTALL_BASE/ apps/ apps_st/ appl/ APPL[$SID]_[$hostname].env
3. cd $ADMIN_SCRIPTS_HOME  (or $INSTALL_BASE/apps/$CONTEXT_NAME/ admin/ scripts)
4. run adautocfg.sh (Autoconfig script)
./adautocfg.sh5. Supply apps password when prompted.
.
B) Running Autoconfig on R12 environment Database Tier
1.Login as user owning database tier for R12 (usually oracle)
2. Set environment variable by executing env file $INSTALL_BASE/ db/ tech_st/ [11.1.0 or 10.2.0]/ [$SID]_[$hostname].env
3. cd $ORACLE_HOME/ appsutil/ scripts/ $CONTEXT_NAME
4. run adautocfg.sh (Autoconfig script)
./adautocfg.sh
5. Supply apps password when prompted

Things good to know about Autoconfig in R12
1. Autoconfig logs for R12 application tier are at
$INSTALL_BASE/inst/apps/$CONTEXT_NAME/admin/log/$MMDDHHMM/adconfig.log ($INST_TOP/admin/log/[$MMDDhhmm])
2. Autoconfig logs for R12 Database tier are at
[$RDBMS_ORACLE_HOME]/appsutil/log/[$CONTEXT_NAME]/[$MMDDhhmm]/adconfig.log
3. R12 system is autoconfig enabled and uses context file stored in [INST_TOP]/appl/admin/[CONTEXT_NAME].xml (Application Tier)
and
[$DATABASR_ORACLE_HOME]/appsutil/[$CONTEXT_NAME].xml (Database Tier)

What is Autoconfig ? Autoconfig is tool in Apps to configure oracle Applications 11i , Application Tier as well Database Tier.
How to run Autoconfig ?
If you want to configure database tier then you have to execute autoconfig script adautocfg.sh on database tier else if you want to configure on application tier (Middle Tier ) then you have to execute one on middle tier .
Here is the script location
For Database tier its $ORACLE_HOME/appsutil/scripts/$CONTEXT_NAME
For Application Tier its $OAD_TOP/admin/scripts/$CONTEXT_NAME
adautocfg.sh calls adconfig.sh from $AD_TOP/bin which inturn calls adconfig.pl from $AD_TOP/bin
Don’t forget to take backup before executing autoconfig.
What source of information for Autoconfig to update configuration files ?
If you are wondering where is configuration information stored about your apps 11i instance based on which it updates your Instance then this file is xml file also called as Context file and its in $APPL_TOP/admin for Application Tier or $ORACLE_HOME/appsutil in Database Tier
I am going to unwrap this xml / context file which is most important file . If you get a chance open this file & go through it so wait for my next post on Context File Contents with explanation & how to put your customization so that Autoconfig can’t overwrite them ..

SQL Queries for checking Profile Option Values

SQL Queries for checking Profile Option Values

The following queries are useful to get the profile option values of a profile option at site, application, responsibility and user level

1) Obtain Profile Option values for Profile Option name like ‘%Ledger%’ and  Responsibility name like ‘%General%Ledger%’

SELECT substr(pro1.user_profile_option_name,1,35) Profile, decode(pov.level_id, 10001,'Site', 10002,'Application', 10003,'Resp', 10004,'User') Option_Level, decode(pov.level_id, 10001,'Site', 10002,appl.application_short_name, 10003,resp.responsibility_name, 10004,u.user_name) Level_Value, nvl(pov.profile_option_value,'Is Null') Profile_option_Value FROM  fnd_profile_option_values pov, fnd_responsibility_tl resp, fnd_application appl, fnd_user u, fnd_profile_options pro, fnd_profile_options_tl pro1 WHERE pro1.user_profile_option_name like ('%Ledger%') and  pro.profile_option_name = pro1.profile_option_name and  pro.profile_option_id = pov.profile_option_id and  resp.responsibility_name like '%General%Ledger%' /* comment this line  if you need to check profiles for all responsibilities */ and  pov.level_value = resp.responsibility_id (+) and  pov.level_value = appl.application_id (+) and  pov.level_value = u.user_id (+) order by 1,2;

2) Obtain all Profile Option values setup for a particular responsibility. Replace the responsibility name as per your requirement.

SELECT substr(pro1.user_profile_option_name,1,35) Profile, decode(pov.level_id, 10001,'Site', 10002,'Application', 10003,'Resp', 10004,'User') Option_Level, decode(pov.level_id, 10001,'Site', 10002,appl.application_short_name, 10003,resp.responsibility_name, 10004,u.user_name) Level_Value, nvl(pov.profile_option_value,'Is Null') Profile_option_Value FROM  fnd_profile_option_values pov, fnd_responsibility_tl resp, fnd_application appl, fnd_user u, fnd_profile_options pro, fnd_profile_options_tl pro1 WHERE pro.profile_option_name = pro1.profile_option_name and  pro.profile_option_id = pov.profile_option_id and  resp.responsibility_name like '%General%Ledger%' and  pov.level_value = resp.responsibility_id (+) and  pov.level_value = appl.application_id (+) and  pov.level_value = u.user_id (+) order by 1,2;