Core And Application DBA Blog: tjt clone

Db side:

1.select name from v$database;

TJTG

2.Source env file.

Cd $ORACLE_HOME

. /orahome/oracle/product/TJTG/db/tech_st/11.2.0/TJTG_tjpsb004.env

3.Run adpreclone.pl script on db tier.

cd $ORACLE_HOME/appsutil/scripts/TJTG_tjpsb004

cd $ORACLE_HOME:

/orahome/oracle/product/TJTG/db/tech_st/11.2.0/appsutil/scripts/ TJTG_tjpsb004

perl adpreclone.pl dbTier

tech_st]$ du –sh

/orahome/oracle/product/TJTG/db/tech_st

20G 11.2.0

3.Make Tar zip file.

cd /orahome/oracle/product/TJTG/db/tech_st/11.2.0

DedRspu5e

Tar –cvf /audit_trail/11.2.0_10May2018.tar 11.2.0/

scp -r 11.2.0_10May18.tar root@172.16.2.65:/uatdb/

4. Run adpreclone.pl script on Apps tier.

Source the .Env file:

cat .bash_profile

. /oraapps/oracle/product/TJTG/apps/apps_st/appl/APPSTJTG_tjpsb005.env

export FORMS_PATH=/oraapps/oracle/product/TJTG/apps/apps_st/appl/au/12.0.0/resource:/oraapps/oracle/product/TJTG/apps/apps_st/appl/au/12.0.0/forms/US

Cd $APPL_TOP

.bash_profile value:

. /ASCPPROD_APP/oracle/product/ASCPPROD/apps/apps_st/appl/APPSASCPPROD_tjtgb006.env

###export LDEMULATION=elf_i386

Go to cd $ADMIN_SCRIPTS_HOME

/oraapps/oracle/product/TJTG/inst/apps/TJTG_tjpsb005/admin/scripts

perl adpreclone.pl appsTier

LOGFILE:

/oraapps/oracle/product/TJTG/inst/apps/TJTG_tjpsb005/admin/log/StageAppsTier_05101133.log

Clear it

take only apps and comn tar ,INST_TOP not required .

/oraapps/oracle/product/TJTG

59G apps

121G inst

/uatapp

DedRspu5e

scp -r apps10may.tar root@172.16.2.66:/uatapp/

mkdir –p /ORAAPPS/app/appltest/apps/

Tar –xvf apps.tar.gz

Tar –xvf tech.tar.gz

5.Create user as per requirement:

Create apps and db user

Groupadd dba

Groupadd oinstall

Useradd –g dba –G oinstall orauat

Useradd –g dba –G oinstall appluat

[root@TJPSB007 app]# mkdir -p /ORADB/app/orauat

[root@TJPSB007 app]# chown -R oraascp:dba /ORADB/app/orauat

[root@TJPSB007 app]# chmod -R 775 /ORADB/app/orauat

DedRspu5e

After extraction new folder structure would be like.

/ORADB/app/orauat/11.2.0

Mkdir –p /ORADB/oradata/UAT

mkdir -p ORADB/oradata/UAT

chown -R oraascp:dba ORADB/oradata/UAT

chmod -R 775 ORADB/oradata/UAT

Transfer File:

scp -r 11.2.0_10May18.tar@172.16.2.65:/ORADB/app/orauat

/ORADB/app/orauat --- for extracting dbhome.tar.gz

Db ---extract db home here

/ORADB/oradata/rman_prod_copy –keep all backup here

/TJPSB004_BACKUP/TJTG1213/DB/

scp –r <Take particular days backup datewise> root@172.16.2.65:/ORADB/app/orauat/rman_prod_backups

mkdir –p :/ORADB/app/orauat/rman_prod_backups/

utl_file_dir--

6.
Take Full Rman backup with archivelog.

00 03 * * * sh /TJPSB004_BACKUP/rman_scripts/TJTGfullbk.sh

run {

#delete noprompt obsolete redundancy 3 device type disk;

#delete noprompt obsolete device type disk;

allocate channel c1 device type disk;

allocate channel c2 device type disk;

allocate channel c3 device type disk;

allocate channel c4 device type disk;

backup as COMPRESSED BACKUPSET format '/TJPSB004_BACKUP/TJTG1213/DB/%d_%t_%s_%p' tag 'TJTG_full_backup' database;

SQL 'alter system switch logfile';

SQL 'alter system checkpoint';

crosscheck archivelog all;

backup AS COMPRESSED BACKUPSET archivelog all format /TJPSB004_BACKUP/TJTG1213/DB/%d_Arch_Log%t_%s_%p';

backup tag = cf1 format '/TJPSB004_BACKUP/TJTG1213/DB/%d_Control_file%t_%s_%p' current controlfile;

release channel c1;

release channel c2;

release channel c3;

release channel c4;

#allocate channel for maintenance type disk;

#delete obsolete noprompt device type disk;

}

EOF

7.Extracting db.tar and run adcfgclone script.

Set New Location of Orainventory in Oracle _Home:

Cd /ORADB/app/orauat/11.2.0

[orauat@TJPSB007 11.2.0]$ mkdir -p oraInventory

vi oraInst.loc

inventory_loc=/ORADB/app/orauat/11.2.0/oraInventory

inst_group=dba

Execute perl:

Export PATH=/ORADB/app/orauat/11.2.0/perl/bin:$PATH

Cd /ORADB/app/orauat/11.2.0/appsutil/clone/bin

perl adcfgclone.pl dbTechStack

Log file located at /uatdb/app/oraascp/db/11.2.0/appsutil/clone/bin/CloneContext_ 0510135739.log

Provide the values required for creation of the new Database Context file.

Target System Hostname (virtual or normal) [tjpsb007] :

Target Instance is RAC (y/n) [n] : n

Target System Database SID : UAT

Target System Base Directory : /ORADB/app/orauat

Target System utl_file_dir Directory List : /usr/tmp

Number of DATA_TOP's on the Target System [4] : 1

Target System DATA_TOP Directory 1 : /ORADB/oradata/UAT

Target System RDBMS ORACLE_HOME Directory [/ORADB/app/orauat/db/tech_st/11.1.0] : /ORADB/app/orauat/11.2.0

Do you want to preserve the Display [tjpsb004:1.0] (y/n) : n

Target System Display [tjpsb007:0.0] :

Do you want the the target system to have the same port values as the source system (y/n) [y] ? : n

Target System Port Pool [0-99] : 10

Instance "UAT", status UNKNOWN, has 1 handler(s) for this service...

The command completed successfully

Step 1> set in .bash_profile,

. /ORADB/app/orauat/11.2.0/UAT_tjpsb007.env
ORACLE_SID=TJTG
export $ORACLE_SID
#Original Value is TJTG, replaced for restore and recover of rman db

> mv initUAT.ora initTJTG.ora
vi initTJTG.ora
db_name = TJTG (Changed from UAT to TJTG , as TJTG is source db)

vi initTJTG.ora

db name:TJTG(CHANGED FROM UAT)

log_archive_dest_1 = 'LOCATION=/ORADB/oradata/UAT/archive’

11.start db,

Sqlplus / as sysdba
> startup nomount pfile='/ORADB/app/orauat/11.2.0/dbs/initTJTG.ora';
Total System Global Area 1068937216 bytes
Fixed Size 2166536 bytes
Variable Size 427819256 bytes
Database Buffers 624951296 bytes
Redo Buffers 14000128 bytes

> rman target / nocatalog
connected to target database: TJTG (not mounted)
go to rman and check controlfile .
cd /GSTDB/rmanbkp

RMAN> restore controlfile from ' /GSTDB/rmanbkp/TJTG_Control_file976084344_43451_1';

RMAN> alter database mount;

cd /GSTDB/rmanbkp

10.

12.Catalog all rman and archiver files.
rman]$ rman target / nocatalog

connected to target database:

RMAN> catalog start with '/GSTDB/rmanbkp';

13.

Set linesize 120

Set pagesize 100

Spool a.txt

(select 'set newname for datafile '||FILE_ID||' to '''||file_name||''';'from dba_data_files);

rman target / nocatalog

Give Full Permission to rman files:

[orauat@TJPSB007 rman_prod_copy]$ chmod a+x *

TJTG_976071611_43440_1 TJTG_976071613_43443_1 TJTG_976078589_43446_1

rman target / nocatalog

RMAN>@/GSTDB/rmanbkp/restore_rman.sql

14.
RMAN>list backup of archivelog all;
1 67988 12403567111100 10-MAY-18 12403567111120 10-MAY-18

RUN {
set until sequence 67988 thread 1;
recover database;
}

15.
> - Rename online LOGFILES:

sqlplus / as sysdba

select member from v$logfile;

MEMBER

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

/oradb/oracle/product/TJTG/db/apps_st/data/log1a.dbf

/oradb_1/oracle/product/TJTG/db/apps_st/data/log1b.dbf

/oradb_2/oracle/product/TJTG/db/apps_st/data/log1c.dbf

/oradb/oracle/product/TJTG/db/apps_st/data/log2a.dbf

/oradb_1/oracle/product/TJTG/db/apps_st/data/log2b.dbf

/oradb_2/oracle/product/TJTG/db/apps_st/data/log2c.dbf

ALTER COMMAND:

alter database rename file '/oradb/oracle/product/TJTG/db/apps_st/data/log1a.dbf' to '/ORADB/oradata/UAT/log1a.dbf';

alter database rename file '/oradb_1/oracle/product/TJTG/db/apps_st/data/log1b.dbf' to '/ORADB/oradata/UAT/log1b.dbf';

alter database rename file '/oradb_2/oracle/product/TJTG/db/apps_st/data/log1c.dbf' to '/ORADB/oradata/UAT/log1c.dbf';

alter database rename file '/oradb/oracle/product/TJTG/db/apps_st/data/log2a.dbf' to '/ORADB/oradata/UAT/log2a.dbf';

alter database rename file '/oradb_1/oracle/product/TJTG/db/apps_st/data/log2b.dbf' to '/ORADB/oradata/UAT/log2b.dbf';

alter database rename file '/oradb_2/oracle/product/TJTG/db/apps_st/data/log2c.dbf' to '/ORADB/oradata/UAT/log2c.dbf';

> - Open database using resetlogs:

SQL> alter database open resetlogs;

> - Create and drop TEMP tablspace

SQL> select TS#,NAME from v$tablespace;

TS# NAME

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

0 SYSTEM

1 CTXD

2 OWAPUB

388 APPS_TS_QUEUES

390 ODM

391 OLAP

392 SYSAUX

393 APPS_TS_TOOLS

381 APPS_TS_TX_DATA

382 APPS_TS_TX_IDX

383 APPS_TS_SEED

TS# NAME

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

384 APPS_TS_INTERFACE

396 TJTG

368 APPS_UNDOTS1

398 USERS

307 PORTAL

385 APPS_TS_SUMMARY

386 APPS_TS_NOLOGGING

387 APPS_TS_ARCHIVE

389 APPS_TS_MEDIA

284 TEMP1

21 rows selected.

create TEMPORARY TABLESPACE TEMP3 TEMPFILE '/ORADB/oradata/UAT/temp001.dbf' size 5000M;

Tablespace created.

ALTER TABLESPACE TEMP3 ADD TEMPFILE '/ORADB/oradata/UAT/temp002.dbf' SIZE 5000m;

Tablespace created.

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp3;

drop tablespace temp1 including contents and datafiles;

16.

16.
> - Change database name using "nid" command

SQL> shut immediate
SQL> startup nomount pfile='/ORADB/app/orauat/11.2.0/dbs/initTJTG.ora';

SQL> alter database mount;

SQL> select open_mode, name from v$database;

OPEN_MODE NAME

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

MOUNTED TJTG

$ nid TARGET=sys DBNAME=UAT
Password: as sysdba

[orauat@TJPSB007 rmanbkp]$ nid TARGET=sys DBNAME=UAT

DBNEWID: Release 11.2.0.4.0 - Production on Fri May 11 09:12:31 2018

Password:

Connected to database TJTG (DBID=3810413154)

Connected to server version 11.2.0

Control Files in database:

/ORADB/oradata/UAT/cntrl01.dbf

/ORADB/oradata/UAT/cntrl02.dbf

/ORADB/oradata/UAT/cntrl03.dbf

Change database ID and database name TJTG to UAT? (Y/[N]) => Y

Proceeding with operation

Changing database ID from 3810413154 to 3000684806

Changing database name from TJTG to UAT

Control File /ORADB/oradata/UAT/cntrl01.dbf - modified

Control File /ORADB/oradata/UAT/cntrl02.dbf - modified

Control File /ORADB/oradata/UAT/cntrl03.dbf - modified

Datafile /ORADB/oradata/UAT/system01.db - dbid changed, wrote new name

Datafile /ORADB/oradata/UAT/system02.db - dbid changed, wrote new name

Datafile /ORADB/oradata/UAT/system03.db - dbid changed, wrote new name

Control File /ORADB/oradata/UAT/cntrl01.dbf - dbid changed, wrote new name

Control File /ORADB/oradata/UAT/cntrl02.dbf - dbid changed, wrote new name

Control File /ORADB/oradata/UAT/cntrl03.dbf - dbid changed, wrote new name

Instance shut down

Database name changed to UAT.

Modify parameter file and generate a new password file before restarting.

Database ID for database UAT changed to 3000684806.

All previous backups and archived redo logs for this database are unusable.

Database has been shutdown, open database with RESETLOGS option.

Succesfully changed database name and ID.

DBNEWID - Completed succesfully.

17.- Change ORACLE_SID in environment file and start database with reset logs:

And bash_profile.

ORACLE_SID=UAT

export $ORACLE_SID

cd /ORADB/app/orauat/11.2.0/

Oracle_Home

[orauat@TJPSB007 11.2.0]$ grep ORACLE_SID UAT_tjpsb007.env

ORACLE_SID="UAT"

export ORACLE_SID

[orauat@TJPSB007 11.2.0]$

[orauat@TJPSB007 dbs]$ vi initTJTG.ora

(change db name from TJTG TO new named as UAT)
db_name = UAT

[orauat@TJPSB007 dbs]$ mv initTJTG.ora initUAT.ora
Now start with pfile initUAT.ora

Source bash profile with Oracle_Sid as new SID.

ORACLE_SID=UAT

export $ORACLE_SID

SQL> startup nomount pfile='/ORADB/app/orauat/11.2.0/dbs/initUAT.ora';

ORA-48108: invalid value given for the diagnostic_dest init.ora parameter

ORA-48140: the specified ADR Base directory does not exist [/ASCPPROD_DB/oracle/adr]

ORA-48187: specified directory does not exist

Linux-x86_64 Error: 2: No such file or directory

Additional information: 1

SQL> alter database mount;
alter database open resetlogs;

SQL> select open_mode, name from v$database;

OPEN_MODE NAME

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

READ WRITE UAT

18.
> - Run autoconfig on database Tier:
cd $ORACLE_HOME/appsutil/scripts/UAT_tjpsb007
/ORADB/app/orauat/11.2.0/appsutil/scripts/UAT_tjpsb007
sh adautocfg.sh
AutoConfig completed successfully.

CREATE SPFILE :

show parameter spfile;

pfile in use.

> create spfile from pfile;

>shut immediate

[orauat@TJPSB007 dbs]$ mv initUAT.ora initUAT.ora_bkp15May

>startup

Success!!!!!!

TNS_DETIALS:

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=TJPSB007.ebs.in)(PORT=1531)))

19.

Conn apps/apps

update WF_NOTIFICATIONS set mail_status = 'SENT' where mail_status = 'MAIL';

select * from WF_NOTIFICATIONS where mail_status = 'MAIL';

50000

update fnd_concurrent_requests

set phase_code = 'C', status_code = 'X'

where status_code in ('Q','I')

and requested_start_date > SYSDATE

and hold_flag = 'N';

Update applsys.fnd_concurrent_requests set hold_flag='Y' where phase_code in ('R','P','I');

update fnd_user set email_address = null where email_address is not null;

update per_all_people_f set email_address = null where email_address is not null;

select * from per_all_people_f;

select OUTFILE_NAME from FND_CONCURRENT_REQUESTS;

select COUNT(*) from WF_NOTIFICATIONS where mail_status ='SENT';

438104

select count(*) from WF_NOTIFICATIONS where mail_status ='MAIL';

select SESSION_COOKIE_DOMAIN from ICX_PARAMETERS;

select home_url from ICX_PARAMETERS;

http://tjpsb005.ebs.in:8000/OA_HTML/AppsLogin

select * from dba_db_links;

drop public database link EBS_TO_ASCP.JSW_IN;

select NODE_NAME SUPPORT_CP,SUPPORT_FORMS,SUPPORT_WEB,SUPPORT_ADMIN,SERVER_ADDRESS from FND_NODES;

SUPPORT_CP S S S SERVER_ADDRESS

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

AUTHENTICATION N N N *

TJPSB007 N N N

TJPSB004 N N N

TJPSB005 Y Y Y 172.16.2.33

EXEC FND_CONC_CLONE.SETUP_CLEAN;

10.

Run /ORADB/cmclean.sql as APPS user

11.

select profile_option_value from apps.fnd_profile_option_values where PROFILE_OPTION_ID=125;

EDIT fnd_profile_option_values where PROFILE_OPTION_ID=125;

UAT_15MAY18

UPDATE fnd_profile_option_values

Set profile_option_value='UAT_15MAY18'

Where PROFILE_OPTION_ID=125;

12.

update XDO_CONFIG_VALUES set value='&applptmp_dir' where PROPERTY_CODE='SYSTEM_TEMP_DIR';

select * from XDO_CONFIG_VALUES;

13.

select execution_options from apps.fnd_CONCURRENT_PROGRAMS where concurrent_program_name like '%TJTG%';

select execution_options,concurrent_program_name from apps.fnd_CONCURRENT_PROGRAMS where concurrent_program_name is not null;

run sh adautocfg.sh script

cd $ORACLE_HOME/appsutil/scripts/PROD_tos01
/uatdb/app/oraascp/db/11.2.0/appsutil/scripts/ASR_tjpsb007
sh adautocfg.sh

APPLICATION SIDE:

/ GSTAPP

mkdir -p /GSTAPP/app/appluat/

chown -R appluat:dba /GSTAPP/app/appluat/

chmod -R 775 /GSTAPP/app/appluat/

DedRspu5e

/GSTAPP

APPLICATION

> Run clone Configuration script on application Tier(applascp)
/GSTAPP/app/appluat/apps_st/comn/clone/bin

Run Perl Command :

/uatapp/app/applascp/tech_st/10.1.3/perl/bin

export PATH=/uatapp/app/applascp/tech_st/10.1.3/perl/bin:$PATH

Note:No Need to run perl command as PATh Set.Its throws error.

Run Normally with /usr/bin perl.

perl adcfgclone.pl appsTier

Log file located at /GSTAPP/app/appluat/apps_st/comn/clone/bin/CloneContext_051509 5014.log

Provide the values required for creation of the new APPL_TOP Context file.

Target System Hostname (virtual or normal) [tjpsb008] :

Target System Database SID : UAT

Target System Database Server Node [tjpsb008] : TJPSB007

Target System Database Domain Name [ebs.in] :

Target System Base Directory : /GSTAPP/app/appluat

Target System Tools ORACLE_HOME Directory [/GSTAPP/app/appluat/apps/tech_st/10.1.2] : /GSTAPP/app/appluat/tech_st/10.1.2

Target System Web ORACLE_HOME Directory [/GSTAPP/app/appluat/apps/tech_st/10.1.3] : /GSTAPP/app/appluat/tech_st/10.1.3

Target System APPL_TOP Directory [/GSTAPP/app/appluat/apps/apps_st/appl] : /GSTAPP/app/appluat/apps_st/appl

Target System COMMON_TOP Directory [/GSTAPP/app/appluat/apps/apps_st/comn] : /GSTAPP/app/appluat/apps_st/comn

[appluat@TJPSB008 appluat]$ mkdir -p inst

Target System Instance Home Directory [/GSTAPP/app/appluat/inst] : /GSTAPP/app/appluat/inst

Target System Root Service [enabled] :

Target System Web Entry Point Services [enabled] :

Target System Web Application Services [enabled] :

Target System Batch Processing Services [enabled] :

Target System Other Services [enabled] :

Do you want to preserve the Display [tjpsb005:1.0] (y/n) : n

Target System Display [tjpsb008:0.0] :

Do you want the the target system to have the same port values as the source system (y/n) [y] ? : n

Target System Port Pool [0-99] : 10

http://tjpsb008.ebs.in:8010/OA_HTML/AppsLogin

welcome123

sysadmin

BASH PROFILE Setup:

. /GSTAPP/app/appluat/apps_st/appl/APPSUAT_tjpsb008.env

SYSADMIN PASSWORD application side

CHECK WF FROM LOGIN APPLICATION

SERVICE AGENT

4. Verify the APPLCSF variable setting
s_applcsf variable in the context file and then run AutoConfig.

Set the custom path:

1)In R12 go to $INST_TOP/ora/10.1.2/forms/server

2)Make sure your CUSTOM_TOP is defined in default.env

/uatapp/app/applascp/apps_st/custom/TJTG/12.0.0

###Begin Customization

TJTG_TOP=/GSTAPP/app/appluat/apps_st/custom/TJTG/12.0.0

###End Customization

FNDCPASS apps/Jzav 0 Y system/tjpsebs11 SYSTEM APPLSYS apps

Running autoconfig on database Tier:
/ORADB/app/orauat/11.2.0/appsutil/scripts/UAT_tjpsb007
sh adautocfg.sh

- Running autoconfig on application Tier:
/GSTAPP/app/appluat/inst/apps/UAT_tjpsb008/admin/scripts

<---Inst_top---------------------------------------------à
sh adautocfg.sh

NODE NAME UPDATED OR NOT:

select NODE_NAME SUPPORT_CP,SUPPORT_FORMS,SUPPORT_WEB,SUPPORT_ADMIN,SERVER_ADDRESS from FND_NODES;

WORKFLOW ACTIVITY POST CLONING:

Post Clone Steps In Oracle Applications

The cloning process has traditionally been a manual process performed by DBAs. This process involves copying over the database files – preferably using RMAN DB duplicate, the tech stack, and the application tier to the non-production servers, converting them to DEV or TEST, and then performing a number of post clone steps such as disabling workflow emails from going to end users, scrambling financial information, updating the clone date and the instance name within the apps heading, editing the custom top, changing passwords, and others. Depending upon the size and configuration the cloning process can take from 12 hours to 48 hours on average.

The number of post clone steps can get to be quite numerous and even with the best of documented procedures; one or two steps can easily be missed.

Note:- the following is a list of steps in one particular cloning environment, It may vary based on environment.

1. Once the cloned database is renamed, add all the temp datafiles to the TEMP tablespace.

2. All key passwords need to be updated from their production values to the appropriate values for the DEV environment. (ie: sys,system, sysadmin,apps)

3. Custom database directories need to be re-created with the correct paths for the DEV server.

4. Specific Profile options need to be re-defined – like the apps heading naming the instance and the date stamp of the clone.

5. End-dating specific users so that only the desired developers have access to the instance.

6. Removing/scrambling specific private HR data.

7. Nulling out specific email addresses to prevent workflow from emailing end-users from the DEV instance.

8. Putting a hold on all regularly scheduled production concurrent requests in the DEV instance.

9. Updating all profile option values, swapping out the prod instance name for the DEV instance name. Additional updates for the server names also run.

10. Updates for all custom environment code paths and settings.

11. Updates for any and all printer setting changes.

https://orazer.wordpress.com/2015/09/08/cloning-of-oracle-ebs-r12-step-by-step-procedure-part-2/

Putting all concurrent jobs on hold:
Update applsys.fnd_concurrent_requests set hold_flag='Y' where phase_code in ('R','P','I');

update fnd_concurrent_requests

set phase_code = 'C', status_code = 'X'

where status_code in ('Q','I')

and requested_start_date > SYSDATE

and hold_flag = 'N';

1.update WF_NOTIFICATIONS set mail_status = 'SENT' where mail_status = 'MAIL';

select TEXT_VALUE from WF_NOTIFICATION_ATTRIBUTES;

select TEXT_VALUE from WF_ITEM_ATTRIBUTE_VALUES;

select GUID from WF_SYSTEMS;

select NAME from WF_SYSTEMS;

select ADDRESS from WF_AGENTS;

select WEB_HOST_NAME from FND_FORM_FUNCTIONS;

select WEB_AGENT_NAME from FND_FORM_FUNCTIONS;

select LOGFILE_NAME from FND_CONCURRENT_REQUESTS;

update fnd_user set email_address = null

where email_address is not null;

update per_all_people_f set email_address = null

where email_address is not null;

update WF_NOTIFICATIONS set mail_status = 'SENT' where mail_status = 'MAIL';

select OUTFILE_NAME from FND_CONCURRENT_REQUESTS;

update WF_NOTIFICATIONS set mail_status = 'SENT' where mail_status = 'MAIL';

select COUNT(*) from WF_NOTIFICATIONS where mail_status ='SENT';

2140

select * from WF_NOTIFICATIONS where mail_status ='MAIL';

status=open,closed

update WF_NOTIFICATIONS set mail_status = 'SENT' where mail_status = 'MAIL';

commit;

select distinct(mail_status) from WF_NOTIFICATIONS;

MAIL

SENT

select SESSION_COOKIE_DOMAIN from ICX_PARAMETERS;

select home_url from ICX_PARAMETERS;

select * from dba_db_links;

drop public database link ASCP_TO_EBS.JSW_IN;

drop public database link JSWHRP_TO_STEELP.JSW.IN;

drop public database link APPS_TO_APPS.JSW.IN;

drop public database link EDW_APPS_TO_WH.JSW.IN;

drop public database link ASCP_TO_EBS.JSW.IN;

drop public database link EBS_TO_ASCP.JSW_IN;

SQL> conn apps

Enter password:

Connected.

EXEC FND_CONC_CLONE.SETUP_CLEAN;

select NODE_NAME SUPPORT_CP,SUPPORT_FORMS,SUPPORT_WEB,SUPPORT_ADMIN,SERVER_ADDRESS from FND_NODES;

EXEC FND_CONC_CLONE.SETUP_CLEAN;

select NODE_NAME SUPPORT_CP,SUPPORT_FORMS,SUPPORT_WEB,SUPPORT_ADMIN,SERVER_ADDRESS from FND_NODES;

No rows selected.

SQL> alter user system identified by manager;

FNDCPASS apps/apps 0 Y system/manager ORACLE GL GL1

FNDCPASS apps/apps 0 Y system/manager USER VISION WELCOME

FNDCPASS apps/kyapwd4h1 0 Y system/manager SYSTEM APPLSYS apps

FNDCPASS apps/JzavetOVDr6LKua 0 Y system/tjpsebs11 SYSTEM APPLSYS apps

--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.

cd /GSTDB/app/oraascp/db/11.2.0

cd $ORACLE_HOME

cat oraInst.loc

inventory_loc=/GSTDB/app/oraascp/db/11.2.0/admin/oui/ASC_tjpsb007/oraInventory

10.

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

rename source <SID>_hostname.xml to TARGET_SID_hostname.xml

11.

update FND_NEW_MESSAGES set message_text = &InstanceNameemailaddress

where message_name like '%IBY_FD_SRA_EMAIL_FROM%' and application_id=673;

Commit;

select message_text from FND_NEW_MESSAGES where application_id=673;

select bepid ,BASEURL from iby_bepinfo where BEPID=10020 ;

http://tjtgb006.toshiba-tjps.in:8000/OA_HTML

update iby_bepinfo set BASEURL='http://tjtgb007.toshiba-tjps.in:8060/OA_HTML' where BEPID=10020 ;

commit;

12.

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

select profile_option_value from apps.fnd_profile_option_values where PROFILE_OPTION_ID=125;

UAT_15MAY18

update apps.fnd_profile_option_values

set profile_option_value ='ASC : Refreshed from ASCPPROD on 27-APR-2018'

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;

13.

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;

select execution_options from apps.fnd_CONCURRENT_PROGRAMS where concurrent_program_name like 'IBYSCHEDULER%';

no row selected.

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;

select execution_options from apps.fnd_CONCURRENT_PROGRAMS where concurrent_program_name like 'IBYSCHEDULER%';

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';

select * from iby_remit_advice_setup where remittance_advice_format_code is not null and remit_advice_delivery_method is not null and automatic_sra_submit_flag='Y';

14.

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>;

15.

WORKFLOW STEPS

15.

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

select profile_option_value from apps.fnd_profile_option_values where profile_option_value like 'http://tjtgb006.toshiba-tjps.in:8000%';

PROFILE_OPTION_VALUE

http://tjtgb006.toshiba-tjps.in:8000/pls/ASCPPROD

http://tjtgb006.toshiba-tjps.in:8000/forms/frmservlet

http://tjtgb006.toshiba-tjps.in:8000/OA_HTML/configurator/UiServlet

http://tjtgb006.toshiba-tjps.in:8000/OA_HTML

http://tjtgb006.toshiba-tjps.in:8000

http://tjtgb006.toshiba-tjps.in:8000/mapviewer/omserver

http://tjtgb006.toshiba-tjps.in:8000/OA_HTML/ibyecapp

http://tjtgb006.toshiba-tjps.in:8000

http://tjtgb006.toshiba-tjps.in:8000/OA_HTML/ibyecapp

http://tjtgb006.toshiba-tjps.in:8000/OA_HTML/pricing

http://tjtgb006.toshiba-tjps.in:8000/OA_HTML/

UPDATE apps.fnd_profile_option_values

SET profile_option_value = 'http://tjtgb007.toshiba-tjps.in:8060'

WHERE profile_option_value like 'http://tjtgb006.toshiba-tjps.in:8000%';

select profile_option_value from apps.fnd_profile_option_values where profile_option_value like 'http://tjtgb007.toshiba-tjps.in:8060%';

PROFILE_OPTION_VALUE

http://tjtgb007.toshiba-tjps.in:8060

2) select PROFILE_OPTION_ID, profile_option_value from apps.fnd_profile_option_values where profile_option_value like '%tjtgb%';

PROFILE_OPTION_ID PROFILE_OPTION_VALUE

9619 0614141800001

6613 180000

source string:http://tjtgb006.toshiba-tjps.in:8000

3) select distinct profile_option_value from apps.fnd_profile_option_values where profile_option_value like '%8000%';

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

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

where profile_option_value like '%GOLD%';

update apps.fnd_profile_option_values set profile_option_value=replace(profile_option_value,'ASCPPROD','ASC')

where profile_option_value like '%ASCPPROD%';

12 ROWS UPDATED

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

where profile_option_value like '%gold%';

update apps.fnd_profile_option_values set profile_option_value=replace(profile_option_value,'gold','asc')

where profile_option_value like '%ascpprod%';

0 rows updated.

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%';

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%';

16.

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

PARAMETER_VALUE

120

WFMAIL:MORE_INFO_ANSWERED

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;

17.

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' );

select parameter_value from fnd_svc_comp_param_vals;

133.199.206.60

IMAP

select * from fnd_svc_comp_param_vals;

18.

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%';

select LOG_FILE from apps.ad_applied_patches;

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%';

select * from M_INTERFACE_SETUPS;

20.

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%';

select HTML_BODY,type from wf_messages_tl where type = 'WFMAIL';

21.

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> @Oracle_Home/rdbms/admin/utlrp.sql

22.

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);

PROFILE_OPTION_ID PROFILE_OPTION_VALUE

2353 http://tjtgb007.toshiba-tjps.in:8060

7331 25

3804 http://tjtgb007.toshiba-tjps.in:8060

7324 0.5

7326 1.0

7325 Y

PROFILE_OPTION_ID PROFILE_OPTION_VALUE

2353 http://tjpsb005.ebs.in:8000/pls/TJTG

7331 25

3804 http://tjpsb005.ebs.in:8000/OA_HTML

7324 0.5

7326 1.0

7325 Y

Core And Application DBA Blog

Tuesday, September 25, 2018

tjt clone

No comments:

Post a Comment

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