Core And Application DBA Blog: Clonning

MGT Oracle Applications R12 Clonning

Plan Ports, Mountpoints, Users to be used, and edit related docs

Open the following spreadsheet to get port number settings for existing instances:

M:\Information Systems\Database_Admin\PhysDBA\Procedure\ports for mgt instances.xls

If creating a new instance, add the new instance information to the spreadsheet.

Also, follow the guidelines in the Port Number Management for Unix Servers best practice document:

http://itbestpractices/opscolbestpractices/274/

Port%20Number%20Management%20for%20Unix%20Servers%20BP.doc

create the mountrequest<SID>.doc to be used for requesting mountpoints, ports, and user accounts.

Use the template file

M:\Information Systems\Database_Admin\PhysDBA\Procedure\mountrequestTEMPLATE.xls

Identify source and target hosts

(Refer to the "ports_for_mgt_instances.xls" )

Source Systems : Hostname /Environment for DB/Admin Tier Server,Web Tier Server,

170systems/Markview Web App Server,170systems/Markview Doc Server

Target Systems : Systems where new instance will be created.

Hostname /Environment for DB/Admin Tier Server,Web Tier Server,

170systems/Markview Web App Server,170systems/Markview Doc Server

IMPORTANT: About two weeks before creating a new instance, send a request for new mount points,

opened ports and accounts to the Data Management supervisor.

Create a Help Desk Ticket for the designated DM supervisor asking the Unix Admin team to implement the changes in the

mountrequest<SID>.doc (attach this doc).

One week prior to a refresh, verify source v/s target environment disk space

For database : both source and target, compare

df -k |grep -i <sid>

For applications : both source and target compare

df -k |grep -i <sid>

For any additional space request, raise a case with Unix team in GCC Remedy and

follow-up until space is allocated prior to outage.

Ensure to have an approved CR prior to start of activity.

Ensure Target system has proper file systems and users configured

(right owner ship, permission and file system space required have to be in place).

CLONE DATABASE TIER

tar/zip homes from the source system

NOTE :

1. /var/orastage is an NFS mount mapped accross all servers.

Use this mount to copy the source (tar) filesystem.

Issue not more than 2 commands at the same time, as /var/orastage may not have sufficient space.

Untar and then delete the copied files before starting tar for others

Verify /var/orastage to have minimum of 40Gb available space.

df -k /var/orastage/

BINARIES COPY :-

ON SOURCE DATABASE NODE :

use "tar -cvfp <tar_file_name.tar> <dir_to_be_tar>"

ON TARGET DATABASE NODE :

use "tar -xvfp <tar_file_name.tar>"

As oramgr relink oracle home

This steps is required as we are setting Oracle home at session level

Verify 11.2.0 environment variables are set correctly to target env.

Relink 11.2.0 home

cd $ORACLE_HOME/bin

relink all

Backup verification and Instance startup :-

Source (GOLD)

Connect sqlplus rman_gold/password@ r11es

alter session set nls_date_format='DD-MM-YYYY HH24:MI:SS';

set linesize 120

col operation for a20

col object_type for a20

col status for a10

SELECT operation,object_type,start_time,end_time,status FROM rc_rman_status WHERE db_name='GOLD' --AND status='COMPLETED'

and operation='BACKUP' --and object_type LIKE 'DB%'

and start_time>=(sysdate-2)

order by start_time;

Target (INT1)

- Create orapwd file

- Change the instance parameters in listener.ora, tnsnames.ora and sqlnet.ora

- Start the listener

Update init.ora with new instance parameters .If required create pfile from spfile

sqlplus /as sysdba

SQL> create spfile from pfile;

SQL> startup nomount;

Restore script creation :-

Take the current backup timing from previous steps and update in rman_gold_<target_DB>.cmd .

Copy this script from napdci26 or napdci28 common location .

ex :

cd /opt/oracle/admin/<target_db>/rman_restore/

cp –ip rman_gold_<target_db>.cmd rman_gold_<target_db>.cmd.dd/mm/yyyy

vi rman_gold_<target_db>.cmd

set until time "to_date('Dec 12 2015 17:57:22','Mon DD YYYY HH24:MI:SS')";

edit the date and end time.

Compare file to make sure only one line is updated in script

Diff rman_gold_<target_db>.cmd rman_gold_<target_db>.cmd.dd/mm/yyyy

Initiate Restore and Monitor :-

/opt/oracle/admin/INT1/rman_restore (oramgr@napdci28)[INT1]$ nohup restore.sh &

[1] 36308180

/opt/oracle/admin/INT1/rman_restore (oramgr@napdci28)[INT1]$ Sending output to nohup.out

/opt/oracle/admin/INT1/rman_restore (oramgr@napdci28)[INT1]$ jobs

[1] + Running nohup restore.sh &

To check restore status run below script.

select user, name from v$database;

select sum(SOFAR),sum(TOTALWORK),(ROUND(sum(SOFAR)/sum(TOTALWORK)*100,2))

"%_COMPLETE" FROM V$SESSION_LONGOPS WHERE OPNAME LIKE 'RMAN%'

AND TOTALWORK != 0 AND SOFAR <> TOTALWORK;

Application file copy and .profile settings

On TARGET APPS TIER

copy application file system from <Source_host(gold)> to <target_host> machine.

only the changed one will be copied in rysnc command.

Run below commands in VNC server. Perform below step on each application node.

nohup /usr/bin/rsync -e /usr/bin/ssh --rsync-path=/usr/bin/rsync -r -p -v -c -g -a --progress applgold@napdcl30d:/opt/applgold/apps /opt/appl<target_sid>/

nohup /usr/bin/rsync -e /usr/bin/ssh --rsync-path=/usr/bin/rsync -r -p -v -c -g -a --progress custgold@napdcl30d:/opt/custgold/apps /opt/cust<target_sid>/

RELINK and LINK CREATION:-

Copy the file proglinkfix and change the APPLNAME to appl<Target SID> in this file.

cp /var/orastage/cloning/proglinkfix /opt/cust<sid>/

Check for links with Source SID.

find ./apps -type l -exec ls -l {} \;

cd /opt/cust<sid>

find ./apps -name "*prog" -exec /opt/cust<sid>/proglinkfix {} \;

Note: proglinkfix create soft links to $FND_TOP/bin/fndcpesr

Verify the links are recreated.

find ./apps -name "*prog" -exec ls -l {} \; #List *.prog files

Relink all Oracle Homes and recreate soft links

As oramgr on Database node :

Source target 11.2.0 environment.

. /opt/oracle/local/bin/orasid <TARGET_SID>

Create the orapwd file pointing to target database in $ORACLE_HOME/dbs.

orapwd file=orapw<DBNAME> password=<sys passwd> entries=32

Modify the soft links before relinking Oracle home. Check to see if they are pointing to source

cd /opt/oracle/product/appl<tgt_sid>/11.2.0

find . -type l -exec ls -l {} \;

# To recreate the softlinks pointing to prod (output of the find command)

rm <soft link name>

ln -s <link source > <link name>

A script is available to help with this process.

Copy from mdlux4:/opt/oracle/local/dbclone/ALL/rebuild_soft_links.ksh

Relink oracle home and IAS oracle home :-

Before relink make sure below files are updated. And has no source DB entries in it.

cd $ORACLE_HOME/lib/stubs

grep –i gold *

cross verify libc.so ,env_sysman.mk and libc.so.tmp for word gold in these files. Replace it with target_sid if found .

cd $IAS_ORACLE_HOME/lib/stubs

grep –i gold *

cross verify libc.so ,env_sysman.mk and libc.so.tmp for word gold in these files. Replace it with target_sid if found .

export ORACLE_HOME=/opt/appl<target_sid>/apps/tech_st/10.1.2/

$ORACLE_HOME/bin/relink all

Relink 10.1.3

Logoff from appl<target_sid> and Login again.

export ORACLE_HOME=/opt/appl<target_sid>/apps/tech_st/101.3/

$ORACLE_HOME/bin/relink all

Break Markview DBA_JOBS in target DB

IMPORTANT: Break the DBA_JOBS owned by Markview on the target db immediately

after the DB clone

As oramgr, copy mv_break_dba_jobs.sql to the home directory/cloning

cp /var/orastage/cloning/mv_setups/mv_break_dba_jobs.sql $HOME//cloning/$ORACLE_SID

Execute

cd $HOME/cloning/

sqlplus /nolog @mv_break_dba_jobs.sql

## NOTE: Enter source markview password for markview_password

enter TARGET SID for sid

enter "y" when prompted.

Enter value for break_jobs_y_or_n: y

create dirctory: mkdir /opt/oracommon/<SID>/out/<SID>_<hostname>

As apps user in the TARGET DB, clean out old nodes

Connect apps/&apps_pwd

EXEC FND_CONC_CLONE.SETUP_CLEAN;

COMMIT;

Clean out Concurrent Managers

Update fnd_concurrent_requests table

Update applsys.fnd_concurrent_requests

set PHASE_CODE='C', STATUS_CODE='D'

where phase_code in ('P','R','I');

commit;

Run /var/orastage/cloning/cmclean.sql as APPS user

Update /etc/oratab with the new database.

On the database node as oramgr,add an entry similar to the one below with the

new cloned database details.

<TGT_SID>:<Path to 11.2.0 HOME>:Y:N :R:Y:D:appl<tgt_sid>:cust<tgt_sid>:ERP DB - ERP

(Clone of <SRC_SID> DD/MM/YY)

AutoConfig ON DB node :-

Change the passwords for sys/system

alter user &username identified by &password;

Run $ORACLE_HOME/appsutil/bin/adconfig.sh on DB Tier

Before running adconfig,verify TNS_ADMIN location in contextfile.Path should be as below

/opt/oracle/product/appl<sid>/11.2.0.4/network/admin/<SID>_<hostname>

echo $TNS_ADMIN

. .$ORACLE_HOME/<SID>_<hostname>.env

cd $ORACLE_HOME/appsutil/bin

./adconfig.sh

Replace/Modify the CONTEXT file ( <SID>_hostname.xml )

For a clone, rename source <SID>_hostname.xml to TARGET_SID_hostname.xml and

update any references to the following:

HOSTNAME/hostname (Both DB/Web Host) – hostnames should be in lower case.

SID and sid .

Ports – The DBA requesting the Clone will provide a list with the port pools.

Display for the host and port of the VFB server.

Copy and rename any non-prod xml file to target and update any references to the following:

Hostname

DB name lower/UPPER case

Port numbers – need to check and update all the port numbers in xml file.

All these port numbers should be free, if not adconfig will fail.

Env Setting before autoconfig

Edit the $APPL_TOP/*.env files, replacing references to the SOURCE_SID with TARGET_SID,

and update any host references.

Create "custom<SID>_<hostname>.env" under $APPL_TOP/ with the below entries

export PATH=$PATH:/opt/appl<sid>/apps/tech_st/10.1.3/appsutil/jdk/jre/bin

export SF_TOP=/opt/cust<sid>/apps/sf/12.0.0

export M_UTIL_TOP=/opt/cust<sid>/apps/util/11.5.0

export ORACLE_SID=<sid>

. /opt/cust<sid>/apps/CUSTSYS.env

export TMPDIR=/var/tmp

export IBM_JAVACOREDIR=/var/tmp

Apps Tier AutoConfig :-

Make sure xml file is updated with correct application node and Instance name

· Copied TMGT context file and modified the ports, SID etc..

· Run the adconfig.sh from AD_TOP/bin.

· Need to check the tnsnames.ora file on db node and application node, do the required changes if any.

Copy and rename any non-prod xml file to target and update any references to the following:

Hostname

DB name lower/UPPER case

Port numbers – need to check and update all the port numbers in xml file.

All these port numbers should be free, if not adconfig will fail.

Run adcfgclone.pl as "appl<tgt_sid>" on ALL TARGET APPL_TOP

Add ENV file in $HOME/.profile

. /opt/appl<sid>/apps/apps_st/appl/APPS<SID>_<HOSTNAME>.env

Set inventory location in /etc/oraInst.loc

Create directory /opt/appl<sid>/oraInventory and set inventory_loc to this directory.

Also ensure ONLY 2 lines are uncommented in this file as below

inventory_loc=/opt/appl<sid>/oraInventory

inst_group=dba

Ensure that directory /opt/appl<sid>/inst exists if not create it

Replace all entries pointing source to target in file

/opt/applint1/apps/tech_st/10.1.2/bin/OracleAS_Relink_Patch.sh

Change apps/all other schemas

FNDCPASS apps/<old appspwd> 0 Y system/<pwd> ALLORACLE <new pwd>

FNDCPASS apps/<oldpwd> 0 Y system/<pwd> SYSTEM APPLSYS <new appspwd>

Change SYSADMIN password

FNDCPASS apps/<new appspwd> 0 Y system/<pwd> USER SYSADMIN <sysadmin pwd>

Run Autoconfig as appl<tgt_sid> to reflect apps password change

Run $ADMIN_SCRIPTS_HOME/adautocfg.sh

Run Autoconfig Sequence:

1) Admin node

2) Second node

3) Admin node

VERIFIY AND SET ENVIRONMENT :-

Set paths for Perl program

export PATH=/opt/appl<sid>/apps/tech_st/10.1.3/perl/bin:$PATH

ADPERLPRG=/opt/appl<sid>/apps/tech_st/10.1.3/perl/bin/perl

PERL5LIB=/opt/appl<sid>/apps/tech_st/10.1.3/perl/lib/5.8.3:

/opt/appl<sid>/apps/tech_st/10.1.3/perl/lib/site_perl/5.8.3:

/opt/appl<sid>/apps/apps_st/appl/au/12.0.0/perl:

/opt/appl<sid>/apps/tech_st/10.1.3/Apache/Apache/mod_perl/lib/site_perl/5.8.3

/i686-linux-thread-multi

export ADPERLPRG PERL5LIB

Edit the $APPL_TOP/*.env files, replacing references to the SOURCE_SID with TARGET_SID,

and update any host references.

Create "custom<SID>_<hostname>.env" under $APPL_TOP/ with the below entries

export PATH=$PATH:/opt/appl<sid>/apps/tech_st/10.1.3/appsutil/jdk/jre/bin

export SF_TOP=/opt/cust<sid>/apps/sf/12.0.0

export M_UTIL_TOP=/opt/cust<sid>/apps/util/11.5.0

export ORACLE_SID=<sid>

. /opt/cust<sid>/apps/CUSTSYS.env

export TMPDIR=/var/tmp

export IBM_JAVACOREDIR=/var/tmp

Update the /opt/cust<tgt_sid>/apps/CUSTSYS.env file to reflect target SID as cust<SID> user.

Add Customtop entries to $ORACLE_CONFIG_HOME/forms/server/default.env. Also correct value for

c_MARKVIEW_TOP in the same file

grep ^M_ /opt/cust<sid>/apps/CUSTSYS.env >> $ORACLE_CONFIG_HOME/forms/server/default.env

Crosscheck below entries :-

M_AP_TOP="$M_TOP/ap/11.5.0"

M_AR_TOP="$M_TOP/ar/11.5.0"

M_CE_TOP="$M_TOP/ce/11.5.0"

M_FA_TOP="$M_TOP/fa/11.5.0"

M_GL_TOP="$M_TOP/gl/11.5.0"

M_PA_TOP="$M_TOP/pa/11.5.0"

M_PER_TOP="$M_TOP/per/11.5.0"

M_PO_TOP="$M_TOP/po/11.5.0"

M_UTIL_TOP="$M_TOP/util/11.5.0"

M_IEX_TOP="$M_TOP/iex/11.5.0"

M_MATFIN_TOP="$M_TOP/matfin/11.5.0"

M_AX_TOP="$M_TOP/ax/11.5.0"

M_XLA_TOP="$M_TOP/xla/11.5.0"

M_JAVA_TOP="/opt/custdev1/java"

#SF_TOP=/opt/appldev1/apps/apps_st/appl/sf/12.0.0 --- Should be commneted

SF_TOP=/opt/custdev1/apps/sf/11.5.0 --- SF_TOP entry should point as here

QV_TOP=/opt/appldev1/apps/apps_st/appl/qv/1.1.1 – QV_TOP entry should point as here

Check if custom java path is added to AF_CLASSPATH in adovars_<SID>_<HOSTNAME>.env in

$INST_TOP/appl/admin.

check full permessions are given to below files:

/opt/appl<sid>/apps/tech_st/10.1.2/bin/rwconverter.sh/opt/appl<sid>/apps/tech_st/10.1.2/bin/rwconverter..

Check all the env's are restored correctly.

check APPSORA.env in APPL_TOP and CUSTSYS.env in CUST_TOP.

v)Check the Context file in $INST_TOP/appl/admin.

check if DNS is set in context file .For TMGT its mgttest and for DMGT its mgtdev.

check if $AU_TOP/forms/US is added to FORMS_PATH

Verify if Java executables are in PATH for cust<sid> user

which javac

Add Customtop entries to $ORACLE_CONFIG_HOME/forms/server/default.env.

Also correct value for c_MARKVIEW_TOP in the same file

grep ^M_ /opt/cust<sid>/apps/CUSTSYS.env >> $ORACLE_CONFIG_HOME/forms/server/default.env

eg: /opt/applcrpx/inst/apps/CRPX_napdcl31d/ora/10.1.2/forms/server/default.env

Post Clone Steps on DB Tier

Ensure /var/spool/erp/<TARGET_SID> and its subdirectories are created as the SOURCE_SID directory.

Ensure /var/oraarch1/<SID> is created for the TARGET

Ensure /opt/oracle/admin/<SID> is created.

Ensure that the DB is running in ARCHIVE MODE and that a level 0 backup is running.

RMAN Backup Setup :-

1. Create RMAN User in RMAN Catalog

2. R11ES Database should be used on napdci80b

3. Connect to R11ES via SQL Plus as a user with DBA rights.

4. Create the catalog user for the new Target Instance.

CREATE USER RMAN_INT1

IDENTIFIED BY RMAN_INT1

DEFAULT TABLESPACE RMAN_CATALOGD

TEMPORARY TABLESPACE TMP01

PROFILE DEFAULT

ACCOUNT UNLOCK;

GRANT RECOVERY_CATALOG_OWNER TO RMAN_INT1;

ALTER USER RMAN_INT1 DEFAULT ROLE ALL;

ALTER USER RMAN_INT1

QUOTA UNLIMITED ON RMAN_CATALOGD;

5. Verify the netbackup link as below.

cd $ORACLE_HOME/lib

ls -ltr libobk*

lrwxrwxrwx 1 oramgr dba 35 Aug 02 08:13 libobk.a -> /usr/openv/netbackup/bin/libobk.a64

6. Create catalog

rman

RMAN> connect target /

RMAN> connect rcvcat "RMAN_INT1/ RMAN_INT1@r11es"

RMAN> create catalog;

RMAN> register database

7. Update RMAN Backup Schedule to include TARGET_SID

cd /opt/oracle/local/admin/obackup

vi pc_rman_backup_sched.dat == config.file ==

8. Ensure cron includes backups of database

SOFT LINK Creation :-

QSVISTAE.pll,QSVISTAE.plx in $AU_TOP/resource

cd $AU_TOP/resource

ls -l QS*

rm QSVISTAE.pll QSVISTAE.plx

ln -s /opt/appl<sid>/apps/qv/1.1.1/resource/QSVISTAE.pll QSVISTAE.pll

ln -s /opt/appl<sid>/apps/qv/1.1.1/resource/QSVISTAE.plx QSVISTAE.plx

ls -l QS*

Add/Verify that the following line exists in file $HOME/.profile for appl<targetsid> user on all nodes:

. /opt/appl<sid>/apps/apps_st/appl/APPS<SID>_<hostname>.env

$APPL_TOP/custom<sid>.env to reflect target SID and hostnames.

cross check custom<SID>.env for correct entries .

Ensure that the sid is pointing to target in the file M_UTIL_SQL_LOAD.prog in the below path

/opt/cust<sid>/apps/util/11.5.0/bin

SABRIX SETUP :-

Coordinate Sabrix Solution Clone timing with El Segundo team

The Sabrix solution has it’s own Database and application Server.To ensure consistency with the Audit database an agreed-upon point in time should be selected to which to restore the supporting database.

"Open a ticket with the EL segundo DBA team.If this is a Full Sabrix Clone (TMGT, PST1 or as directed in the clone request), Include the date/time that the clone was recovered to and the jdbc connection string. The sabrix password will be in the /opt/oracle/pass/<SID>/sabrix.

The password should be preserved between clones-

please update the sabrix password to the value found in this directory.

jdbc:oracle:thin:@//<DB server name>:<TNS port>/SID>

sabrix password: <Sabrix password as will be in new ERP clone>

Connection Pool Name/ JNDI location: ConnAP_<SID>

eg.

jdbc:oracle:thin:@//napdci28.corp.mattel.com:1541/TMGT

sabrix pw: sabr1x#

Connection Pool Name/ JNDI location: ConnAP_TMGT"

For a full sabrix clone, El Segundo team provides connection Server URL’, and a Sabrix Tax Calc URL.

These will be used in "register the sabrix connector" step

"There are currently 4 static URLs used for registering the Sabrix Connection.

GOLD:

Connection Server URL : http://sabrixprod:7778/sabrixoraclecs

Sabrix Calc URL : http://sabrixprod:7778/sabrix/xmlinvoice

TMGT:

Connection Server URL : http://sabrixtest:7781/sabrixoraclecs

Sabrix Calc URL : http://sabrixtest:7781/sabrix/xmlinvoice

PST1:

Connection Server URL : http://<TBD>:7789/sabrixoraclecs

Sabrix Calc URL : http://<TBD>:7788/sabrix/xmlinvoice

All other instances (DMGT etc), use the following, if a full sabrix clone is not specified in the request

Connection Server URL : http://sabrixdev:7777/sabrixoraclecs

Calc URL : http://sabrixdev:7777/sabrix/xmlinvoice

These values will point the sabrix connector to a catch all application server.

Generally only TMGT, PST1, and GOLD will point to a different application server.

"Change SABRIX database user password :

sqlplus ""/as sysdba""

SQL> Alter user sabrix identified by <password>;

** Password available in target /opt/oracle/pass/<targetsid>/sabrix file."

"Register the sabrix connector :

** Make sure the connection server URL and tax calc URL are correct as given above.

** Check the attached file for sample run.

cd /var/orastage/cloning/sabrix/install

sqlplus apps/<appspwd>

@sabrix_register_connection.sql_<SID>

Execute the script M_UTIL_INTERFACE as APPS user:-

1) @/var/orastage/cloning/post_cloning/M_UTIL_INTERFACE_SETUPS_UPDATE.sql

Executing the M_INTERFACE_SETUPS_UPDATE script.

Enter value for source_database_sid: GOLD

2) @/var/orastage/cloning/post_cloning/M_UTIL_INTERFACE_SETUPS_UPDATE_FOR_PROJECTS.sql

Executing the M_INTERFACE_SETUPS_UPDATE script.

Enter value for source_database_sid: <TARGET_SID> Like TMGT,DMGT etc.

Update the Distribution_Point_Address in the MATFIN.M_ORGANIZATION_ROLES table:-

1) FOR TMGT/DMGT:
create table MATFIN.m_organization_roles_backup as
select * from MATFIN.m_organization_roles;
update MATFIN.m_organization_roles set distribution_point_address='MGTPTMGT@Mattel.com';
commit;
FOR OTHERS:
create table MATFIN.m_organization_roles_backup as
select * from MATFIN.m_organization_roles;
update MATFIN.m_organization_roles set distribution_point_address='MGTPCRP@Mattel.com';
commit;

IBY Update :- Part of markview scripts

1) drop table iby_external_payees_bkp;
create table iby_external_payees_bkp as
SELECT * FROM iby_external_payees_all
where remit_advice_Email is not null;

UPDATE iby_external_payees_all
SET remit_advice_Email ='mgtpstsa@mattel.com'
where payee_party_id in
(select payee_party_id from iby_external_payees_bkp)
and remit_advice_Email is not null;

commit;

2) update FND_NEW_MESSAGES set message_text = &InstanceNameemailaddress
where message_name like '%IBY_FD_SRA_EMAIL_FROM%' and application_id=673;
Commit;

Input Examples for respective instance- >
MGTPINT1@MATTEL.COM -> DMGT instance
MGTPTMGT1@MATTEL.COM -> TMGT instance
MGTPPST1@MATTEL.COM -> PST1 instance

Update IBY BEPINFO :-

1) SQL> select bepid ,BASEURL from iby_bepinfo where BEPID=10020 ;
BEPID BASEURL
http://napdcl30d.corp.mattel.com:8020/servlets

Use output from pre-requisite queries taken before activity start:-

2) update iby_bepinfo set BASEURL='http://<webserver>.corp.mattel.com:<port>/servlets' where BEPID=10020 ;
commit;

Update site name profile option & XML PUBLISHER TEMP DIR

1) Make sure below directory exist. SID_HOSTNAME is target sid and hostname

/opt/appl<sid>/inst/apps/<SID>_<hostname>/appltmp

2) SQL>update apps.fnd_profile_option_values
set profile_option_value ='<TARGET_SID> : Refreshed from <Source> on <DD-MON-YY>'
where PROFILE_OPTION_ID=125;

3) SQL> update XDO_CONFIG_VALUES set value='&applptmp_dir' where PROPERTY_CODE='SYSTEM_TEMP_DIR';
-- Eg. APPLTMP value to be passed as /opt/appl<sid>/inst/apps/<SID>_<hostname>/appltmp
SQL> commit;

Update the Vista reference in Target DB

1) Query to get the existing values:
select QV_TOP ,QV_vista_sid,QV_description from qv.qsvp_env;

Provide target sid and source sid to the below sql .

2) update qv.qsvp_env

set QV_TOP = '/opt/appl&tgt_sid/apps/apps_st/appl/qv/1.1.1/',

QV_vista_sid = '&tgt_sid',

QV_description= upper('&TGT_SID');

Update DEBUG LOG Location for concurrent program – IBYSCHEDULER

Note – Make sure to update SID and Hostname values

1) update apps.fnd_CONCURRENT_PROGRAMS set execution_options=replace(execution_options,upper('&SRC_SID'),upper('&TGT_SID'))
where concurrent_program_name like 'IBYSCHEDULER%';
commit;

2) update apps.fnd_CONCURRENT_PROGRAMS set execution_options=replace(execution_options,'&SRC_DB_HOST','&TGT_DB_HOST')
where concurrent_program_name like 'IBYSCHEDULER%';
commit;

3) update iby_remit_advice_setup set remittance_advice_format_code='NULL',remit_advice_delivery_method='NULL',automatic_sra_submit_flag='N' where remittance_advice_format_code is not null and remit_advice_delivery_method is not null and automatic_sra_submit_flag='Y';

Update PPP directory paths :-

Input Source SID,Target SID and Source primary web server name to the below scripts

1) UPDATE IBY_SYS_PMT_PROFILES_B
SET OUTBOUND_PMT_FILE_DIRECTORY = replace(OUTBOUND_PMT_FILE_DIRECTORY,upper('&src_sid'),upper('&tgt_sid'))
WHERE OUTBOUND_PMT_FILE_DIRECTORY like upper('%&src_sid%');

2) UPDATE IBY_SYS_PMT_PROFILES_B
SET OUTBOUND_PMT_FILE_DIRECTORY = replace(OUTBOUND_PMT_FILE_DIRECTORY,lower('&src_sid'),lower('&tgt_sid'))
WHERE OUTBOUND_PMT_FILE_DIRECTORY like lower('%&src_sid%');

3) UPDATE IBY_SYS_PMT_PROFILES_B
SET OUTBOUND_PMT_FILE_DIRECTORY = replace(OUTBOUND_PMT_FILE_DIRECTORY,lower('&src_web_host'),lower('&tgt_web_host'))
WHERE OUTBOUND_PMT_FILE_DIRECTORY like lower('%&src_web_host%');
commit;

Change the necessary passwords per target environment :-

Change password for M_J2EE_USER and markview and MATFIN USERS:
alter profile special limit PASSWORD_VERIFY_FUNCTION null;
alter user M_J2EE_USER identified by M_J2EE_USER;
alter user markview identified by &passwd;
alter profile special limit PASSWORD_VERIFY_FUNCTION default;
alter user MATFIN identified by <APPS password of the instance>;

SFW schema Update ( It is application installed for synchro) :-

1) alter user sfw identified by <Pass: check in location>;

conn sfw/<sfw_pass>@<SID>

drop database link DBL_SFW_SOLFIS;

CREATE DATABASE LINK DBL_SFW_SOLFIS CONNECT TO synchro IDENTIFIED BY <password> USING '<SID>';

2) Change below database link for test archive database.(Not applicable for Dev)

drop public database link ARCHIVE.WORLD;

CREATE PUBLIC DATABASE LINK ARCHIVE.WORLD CONNECT TO AJINNI IDENTIFIED BY <passwd> USING '<ARCH_SID>';

Note <ARCH_SID> eg TARC

Verify ajinni password by connecting to TARC.

WORKFLOW STEPS

Update Expense Report Verification Email Content (Owner = APPS)

1) UPDATE apps.fnd_profile_option_values

SET profile_option_value = 'http://<target webserver>.corp.mattel.com:<port>'

WHERE profile_option_value = 'http://<source webserver>.corp.mattel.com:<Source web port>';

Eg :- GOLD :- http://mgtprod.corp.mattel.com:8020

DMGT:- http://mgtdev.corp.mattel.com:8020

2) select PROFILE_OPTION_ID, profile_option_value from apps.fnd_profile_option_values where profile_option_value like '%&Source_String%'

3) select distinct profile_option_value from apps.fnd_profile_option_values where profile_option_value like '%&Source_String%'

4) select distinct profile_option_value from apps.fnd_profile_option_values where profile_option_value like '%mgtprod%'

5) update apps.fnd_profile_option_values set profile_option_value=replace(profile_option_value,'GOLD','<TARGET_SID>')

where profile_option_value like '%GOLD%';

6) update apps.fnd_profile_option_values set profile_option_value=replace(profile_option_value,'gold','<target_sid')

where profile_option_value like '%gold%';

7) update apps.fnd_profile_option_values set profile_option_value=replace(profile_option_value,'mgtprod','mgt<target_url_value>')

where profile_option_value like '%mgtprod%';

8) update apps.fnd_profile_option_values set profile_option_value=replace(profile_option_value,'napdcs32','napdcs25')

where profile_option_value like '%napdcs32%';

9) update apps.fnd_profile_option_values set profile_option_value=replace(profile_option_value,'napdcs33','napdcs25')

where profile_option_value like '%napdcs33%';

10) update apps.fnd_profile_option_values set profile_option_value=replace(profile_option_value,'napdcs34','napdcs25')

where profile_option_value like '%napdcs34%';

Update FND_SVC_COMP_PARAM_VALS

1) select PARAMETER_VALUE from FND_SVC_COMP_PARAM_VALS where COMPONENT_PARAMETER_ID in ( 10220 , 10240 , 10273);

PARAMETER_VALUE

----------------------------------------------

mgtpgold

http://mgtprod.corp.mattel.com:8020/pls/GOLD

mgtpgold@mattel.com

2) Update FND_SVC_COMP_PARAM_VALS set PARAMETER_VALUE = replace(PARAMETER_VALUE,'&source_sid','&target_sid') where COMPONENT_PARAMETER_ID in ( 10220 , 10273);

3) Update FND_SVC_COMP_PARAM_VALS set PARAMETER_VALUE = 'http://<webserver name>:<portno>/pls/<SID>' where COMPONENT_PARAMETER_ID=10240;

Commit;

Update the test email address :

For all TMGT/DMGT/PST1 use mgtptmgt@mattel.com

For CRP/INT instances use mgtpcrp@mattel.com

1) UPDATE applsys.fnd_svc_comp_param_vals

SET parameter_value = '&TEST_ADDRESS'

WHERE parameter_id IN

(SELECT cpv.parameter_id

FROM fnd_svc_comp_param_vals cpv,

fnd_svc_comp_params_b cpb

WHERE cpv.parameter_id = cpb.parameter_id

AND cpb.parameter_name = 'TEST_ADDRESS'

AND cpv.customization_level = 'L' );

Change Permissions for Custom Tops on Applications/Web Tier

1) Login to all the nodes as "cust<sid>"
cd $M_TOP
chmod -R u+rwx,g+rwx ./*

Modify paths to India Localization Patches

1) update jai_applied_patches
set LOG_FILE = replace(LOG_FILE,upper('&Source_instance_name'),upper('&Target_Instance_name'))
where LOG_FILE like '%&Source_instance_name%';

2) update jai_applied_patches
set LOG_FILE = replace(LOG_FILE,upper('&Source_instance_name'),upper('&Target_Instance_name'))
where LOG_FILE like '%&Source_instance_name%';

3) (Specify Server Name)

update jai_applied_patches
set LOG_FILE = replace(LOG_FILE,'&Source_server_name','&Target_server_name')
where LOG_FILE like '%&Source_server_name%';

GLS Interface Remote Directory Change (As per SR# 4468419):

1) Loging as APPS user and execute below stmt

DMGT & Others -Post Cloning Process

--------------------------

Update M_INTERFACE_SETUPS

set REMOTE_DIRECTORY='glsapps/Pelican_Dev/Cash_Import_Files'

where INTERFACE_VENDOR_NAME='GLS'

and DATA_FLOW_DIRECTION_NAME='OUTBOUND'

and INTERFACE_SETUP_ID=798;

TMGT --Post Cloning Process;

---------------------------

Update M_INTERFACE_SETUPS

set REMOTE_DIRECTORY='glsapps/Pelican_Test/Cash_Import_Files'

where INTERFACE_VENDOR_NAME='GLS'

and DATA_FLOW_DIRECTION_NAME='OUTBOUND'

and INTERFACE_SETUP_ID=798;

workflow updates :-

Wf mailer address :

TMGT - mgtptmgt1

DMGT - mgtpdmgt1

PST1 - mgtppst1

1) update wf_messages_tl

set HTML_BODY =replace(HTML_BODY, 'mgtpgold','&wf_mailer_address')

where type = 'WFMAIL'

and name = 'MAT_OPEN_MAIL_CUSTOM_IPROC' and HTML_BODY like '%mgtpgold%';

Update webserver and http port

TMGT - mgttest

DMGT - mgtdev

PST1 - napdcl31b - Port 8002 for PST1

1) update wf_messages_tl

set HTML_BODY =replace(HTML_BODY, 'mgtprod','&webserver')

where type = 'WFMAIL'

and name = 'MAT_OPEN_MAIL_CUSTOM_IPROC' and HTML_BODY like '%mgtprod%';

2) update wf_messages_tl

set HTML_BODY =replace(HTML_BODY, '8020','&webserver')

where type = 'WFMAIL'

and name = 'MAT_OPEN_MAIL_CUSTOM_IPROC' and HTML_BODY like '%8020%';

Update File Directory Path After Cloning :-

Put appropriate values for SID and Hostname

1) Update IBY_TRANSMIT_VALUES

set transmit_varchar2_value='/opt/appltmgt/inst/apps/TMGT_napdcl31d/logs/appl/conc/out'

where transmit_configuration_id = 21

and transmit_parameter_code='FILE_DIR';

2) Update IBY_TRANSMIT_VALUES

set transmit_varchar2_value='/opt/appltmgt/inst/apps/TMGT_napdcl31d/logs/appl/conc/ipayment/outbound'

where transmit_configuration_id=43

and transmit_parameter_code='FILE_DIR';

Commit;

3) update m_interface_setups

set remote_url = 'OracleEssTestGLPL'

where remote_url = 'OracleEssProdGLPL';

4) update m_interface_setups

set remote_url = 'OracleEssTestGLBS'

where remote_url = 'OracleEssProdGLBS';

5) update m_interface_setups

set remote_url = 'OracleEssTestGLPA'

where remote_url = 'OracleEssProdGLPA';

6) update m_interface_setups

set remote_url = 'OracleEssTestGLRev'

where remote_url = 'OracleEssProdGLRev';

Commit;

Update Interface Setup :-

1) update M_INTERFACE_SETUPS set ACCOUNT_NAME='corp\\ftphyptest',REMOTE_IDENTIFICATION='amiko#121',REMOTE_DIRECTORY='ftphypappstest'

where INTERFACE_VENDOR_NAME like 'HYP%' and DATA_FLOW_DIRECTION_NAME='OUTBOUND' and REMOTE_URL='napdcmftp001pv';

2) update m_interface_setups set remote_url = 'nandciapp001t' where remote_url = 'nandciapp001p';

External Document Steps

Follow steps from below document.

Follow steps starting from below document.

Make sure below steps are completed from Markview 170

Update DBA_JOBS settings for Markview schema

Start Markview OC4J's

Verify that the new OC4J urls work for the new instance.

Verifying Markview Components

Un-Break Markview DBA_JOBS (in Target DB)
## NOTE: enter "n" when prompted.

Post Clone Steps:-

1) Recompile all objects on Target Database

Query existing invalid objects
   SELECT OWNER, OBJECT_TYPE, COUNT(*)
   FROM DBA_OBJECTS
   WHERE STATUS = 'INVALID'
   GROUP BY OWNER, OBJECT_TYPE;

2) Run the following as a SYSDBA user to recompile all invalid PL/SQL objects:
SQL> @?/rdbms/admin/utlrp.sql

3) Post clone Step in for markview filecapture (run as markview user)

4) Update XML publisher Temp Location:-

Enable XML Publisher Temp location: (Important)
1- Login as Sysadmin
2- Go to the XML publisher Responsibility
3- On The Admin tab
Expand the Properties and search for the Temp property
4- edit the full path of the temp directory (Ex: /opt/appldmgt/inst/apps/DMGT_napdcl32c/appltmp
5- Ensure the Path exist and has full permission also it should have enough free space at least 5 GB

5) Provide SYSADMIN Responsibility to below USERS:
Brian Klein (KLEINBRI)
Labrini Stathonikou (STATHONL)
Paula Bilski (BILSKIPA)
Tan, Sook Teng (Mandy) (TANSOOKT)

6) Ensure Profile ID's 7326, 7331, 7324, 7325, 4532, 2353, 3804 point to correct target environment path :
select profile_option_id, profile_option_value
from apps.fnd_profile_option_values
where profile_option_id in (7326, 7331, 7324, 7325, 4532, 2353, 3804);

7) Review Workflow Notification Mailer Configuration
Ensure allow forwarded responses check and proper test e-mail address
Ensure proper accounts are used for reply to address and inbound account
Run upd_iproc_email.sql script to update the headers with proper configuration of workflow.
Schedule the below request in Workflow manager configuration in OAM to run daily
1. Background Engines
2. Purge
3. Control Queue cleanup

Send an email to requestor and list of contacts related to the instance creation/refresh advising that the task was completed and include the information from the previous step in the email message.

Close the CR related to this task.

Markview Configuration Overview

Runtime Data Clear-down (on Target DB server) :

RUN THIS TASK ONLY WHEN SPECIFICALLY REQUESTED!!

Create Directory Structure in Document Server for TARGET

Create and configure new OC4J’s = TST01

Modify tnsnames.ora file to include new database information

(on both MarkView App and Doc/Express servers)

Create New DAD in Markview Application server – Verify dads.conf

Create & configure SQL*Flow Manager Logs on target database server utlfile and render manger

Core And Application DBA Blog

Tuesday, September 25, 2018

Clonning_Document Nides

No comments:

Post a Comment

[ERROR]: The value of s_patch_service_name is not set correctly in atleast one of the context files.