Steps to clone Oracle Applications database from PROD to TEST

Steps to clone Oracle Applications database from PROD to TEST
Steps to clone a database from PROD to TEST

The following is a brief Guideline on steps to follow to clone you Oracle Applications from PROD to TEST

1) log In to PROD Server as applprod user ; go to /applprod/prodappl ; run APPSORA.env

2) sqlplus system/manager
SQL> alter database backup controlfile to trace;
This will generate a trace file in $ORACLE_HOME//admin/_/udump/*.trc
Go to above directory

Run ls - alt
Find out latest trace file and edit the trace file
SID to desired SID (PROD to TEST)

3) Change dbf path accordingly.
Shutdown database (Shutdown normal both production & test server by using prod_stop.sh , your PROD stop script, and test_stop.sh your TEST stop script, respectively )

4) Now ftp to your server as applprod user and download the file from PROD server to Test server in /appltest directory .

5) Rename /appltest/testdata directory to /appltest/testdatabk
Create a new testdata directory in /appltest
Copy all datafiles, control file etc.of to new /appltest/testdata from PROD
(E.g rcp applprod @ ebsnode2:/applprod/proddata/*.dbf) and rename cntrl*.dbf to cntrl*.bak on test server

6) Run TEST instance .env file from /appltest/testdb/9.2.0 .
Now run the edited trace file (this will recreate the TEST database with PROD configuration) (sqlplus /nolog @edited script.sql)
This will start up TEST oracle database

7) Change profile_option_values from FND_profile_option_values table

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

Select * from fnd_concurrent_queues
Where control_code=’N’

Update fnd_concurrent_queues
set target_node=NULL

Table ICX_Parameters set session_cookie= null

8) Clear concurrent Manager table from toad by using the following commands:

==================================================================================
UPDATE fnd_concurrent_processes
SET process_status_code = 'K'
WHERE process_status_code not in ('K', 'S');

UPDATE fnd_concurrent_queues
SET running_processes = 0, max_processes = 0;

UPDATE fnd_concurrent_queues
SET control_code = NULL
WHERE control_code not in ('E', 'R', 'X')
AND control_code IS NOT NULL;

UPDATE fnd_concurrent_queues
SET target_node = null;

UPDATE fnd_concurrent_requests
SET phase_code = 'C', status_code = 'E'
WHERE status_code ='T' OR phase_code = 'R'

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


9) Copy the Server_ID from $FND_TOP/secure/ebsnode1_test.dbc and update the server_id column of FND_NODES table with that. Also change the NODE_NAME = EBSNODE1
Run the . APPSORA.env from /appltest/testappl directory

10) Now run following Java commands from command prompt:


=================================================================================
java oracle.apps.fnd.security.AdminAppServer apps/apps AUTHENTICATION OFF DBC=/appltest/testappl/fnd/11.5.0/secure/TEST_ebsnode1/test.dbc

java oracle.apps.fnd.security.AdminAppServer apps/apps AUTHENTICATION OFF DBC=/appltest/testappl/fnd/11.5.0/secure/ebsnode1.<>_test.dbc

java oracle.apps.fnd.security.AdminAppServer apps/apps AUTHENTICATION OFF DBC=/appltest/testappl/fnd/11.5.0/secure/ebsnode1_test.dbc

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

11)Shutdown the Oracle database. ; Start application by using test_start.sh from /appltest dir
If anything goes wrong check at http://:8000/OA_HTML/jsp/fnd/aoljtest.jsp

12) Login to Oracle Applications
Change Profile options from Application –
System Administrator(Responsibility) Profile-> System-> concurrent: GSM enabled = ‘N’

Thats's all folks

Reorg Database - General action plan

1:-List of Fragmented Tables/Interface tables(Size more than 1 GB)

Owner Table_name
----- ----------
APPLSYS WF_ITEM_ACTIVITY_STATUSES
APPLSYS FND_LOG_MESSAGES
AR RA_CUSTOMER_TRX_LINES_ALL
AR RA_CUST_TRX_LINE_GL_DIST_ALL
INV MTL_TRANSACTION_LOT_NUMBERS
INV MTL_TXN_REQUEST_LINES
INV MTL_TRANSACTIONS_INTERFACE
MRP MRP_ATP_SCHEDULE_TEMP
ONT OE_PRICE_ADJUSTMENTS
ONT OE_ORDER_LINES_ALL
ONT OE_ORDER_LINES_HISTORY
WSH WSH_DELIVERY_DETAILS
WSH WSH_EXCEPTIONS


How to Re-Org Fragmented and Interface Tables

1. Create a new tablespace
create tablespace add datafile size xxx MB;
2. Grant resouce access to the new tablespace
alter user quota unlimited on ;
3. Move the tables in question to the new tablespace by doing the following:
alter table table_name move ;
4. Move the tables back to the original tablespace
alter table move tablespace ;
5. Re-build indexes (need to provide the index list)

sql>spool index_rebuild.sql

select 'ALTER INDEX '||owner||'.'||INDEX_NAME||' rebuild; ' from dba_indexes
where table_name in ('WF_ITEM_ACTIVITY_STATUSES','FND_LOG_MESSAGES','RA_CUSTOMER_TRX_LINES_ALL','RA_CUST_TRX_LINE_GL_DIST_ALL',
'MTL_TRANSACTION_LOT_NUMBERS','MTL_TXN_REQUEST_LINES','MTL_TRANSACTIONS_INTERFACE','MRP_ATP_SCHEDULE_TEMP','OE_PRICE_ADJUSTMENTS',
'OE_PRICE_ADJUSTMENTS','OE_ORDER_LINES_ALL','OE_ORDER_LINES_HISTORY','WSH_DELIVERY_DETAILS','WSH_EXCEPTIONS')
and owner in ('APPLSYS','AR','INV','MRP','ONT','WSH')
order by table_name,index_name
/

sql>spool off
sql>spool index_rebld.lst
sql>@ index_rebuild.sql
sql>spool off
6. Drop the new tablespace

drop tablespace including contents and datafiles;
Note: This action has to be done when there are no users logged on to applications.


2:-Fixing Intermedia Tables

A) SELECT 'exec ctx_ddl.sync_index(''' || u.username || '.' || i.idx_name || '''); 'from
ctxsys.dr$index i, dba_users u where u.user_id=i.idx_owner# and idx_id in (select pnd_cid from ctxsys.dr$pending);
(list of indexes for cross reference)
exec ctx_ddl.sync_index('AR.HZ_CUST_ACCT_SITES_ALL_T1');
exec ctx_ddl.sync_index('IBC.IBC_ATTRIBUTE_BUNDLES_CTX');
exec ctx_ddl.sync_index('ICX.ICX_QUES_CTX');
exec ctx_ddl.sync_index('JTF.JTF_AMV_ITEMS_TEXT_CTX');
exec ctx_ddl.sync_index('JTF.JTF_AMV_ITEMS_NAME_CTX');
exec ctx_ddl.sync_index('JTF.JTF_AMV_ITEMS_DESC_CTX');
exec ctx_ddl.sync_index('APPLSYS.FND_LOBS_CTX');

Synchronize the indexes manually

a)Login as ctxsys/ctxsys
b)Execute the scripts of the output of #A

Remove any rows remaining in DR$PENDING and DR$WAITING by executing the following.

a)delete from ctxsys.DR$PENDING;
b)delete from ctxsys.DR$WAITING;


3:-Schdule the Below given Concurrent programs.

a:-Name of the Concurrent / DBMS job: Gather Schema Statistics
Executable Name/short Name: FNDGSCST
Schedule / Submit Interval: Every Saturday, Start at 1:00 AM, From start of prior run.
Parameter Reference:
Schema Name=ALL
Estimate Percent=25
Degree=3
Backup Flag=NoBackup
History Mode=None
Gather Option=Gather
Keep other parameters as defaulted.




b:-Name of the Concurrent / DBMS job: Purge Obsolete Workflow Runtime Data
Executable Name/short Name: FNDWFPR
Schedule / Submit Interval: Every 12 hours, Off peak hours. From start of prior run.
Parameter Reference:
Age=30
Persistence Type=Temporary
Keep other parameters as defaulted.



c:-Name of the Concurrent / DBMS job: Purge Signon Audit data
Executable Name/short Name: FNDSCPRG
Schedule / Submit Interval: Every Day, Off Peak hours, From start of prior run. Select the Check Box for Increment Date parameter each run.
Parameter Reference:
Audit Date: give date 31 days prior to current date

Re-org of Database Objects

Re-org of Database Objects
Re-org of Database Objects


1) Table
- Normal
- IOT Tables starting with SYS_IOT_OVER
- Long Datatye column

2) Table Partition
3) Table Subpartition
4) Index
- Normal
- IOT Indexes starting with SYS_IOT_TOP
5) Index Partition
6) Index Subpartition

Reorg Lobsegment/LobIndex

7) Lobsegment/LobIndex

alter table move lob ()
store as (tablespace );

To find the table_name and lob_column

select OWNER,TABLE_NAME,COLUMN_NAME,SEGMENT_NAME,TABLESPACE_NAME from dba_lobs where SEGMENT_NAME like 'lobsegment_name'

SYS_LOB0000195332C00032$$ LOBSEGMENT

OWNER TABLE_NAME
------------------------------ ------------------------------
COLUMN_NAME
----------------------------------------------------------
SEGMENT_NAME INDEX_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
IEO IEO_ICSM_QUEUE_TBL_1
USER_PROP
SYS_LOB0000195332C00032$$ SYS_IL0000195332C00032$$ APPS_TS_QUEUES


So from above output , following will be the command to move lobsegment


alter table IEO.IEO_ICSM_QUEUE_TBL_1 move lob (USER_PROP) store as (tablespace APPS_TS_QUEUES_NEW);

Note that , once the LOBSEGMENT is moved ,corresponding LOBINDEX will
Also move. You don't need to run the command seperately for LOBINDEX.

Here is the dynamic query which will give the command to move LOBSEGMENT/INDEX

select 'alter table ' ||owner || '.' || table_name||' move lob ('||column_name||') store as (tablespace APPS_TS_QUEUES_NEW);'
from dba_lobs where SEGMENT_NAME in ( select segment_name from dba_segments where tablespace_name like '' and segment_type like '%LOB%');



Follow this only for lobsegment on AQ$_WF_CONTROL_D.

SQL> select segment_name, segment_Type,owner from dba_segments WHERE tablespace_name
='APPS_TS_QUEUES';

SEGMENT_NAME
SEGMENT_TYPE OWNER
---------------------------------------------------------------------------------
------------------ ------------------------------
SYS_IL0000583185C00006$$
LOBINDEX APPLSYS
SYS_LOB0000583185C00006$$
LOBSEGMENT APPLSYS


SQL> alter table APPLSYS.AQ$_WF_CONTROL_D move lob (RSUBS) store as
2 lobsegment (tablespace APPS_TS_QUEUES_NEW);
alter table APPLSYS.AQ$_WF_CONTROL_D move lob (RSUBS) store as
*
ERROR at line 1:
ORA-08108: may not build or rebuild this type of index online


Found Note 418238.1

1> First stop WF related services like WF Mailer Agent, WF listener, etc.

2> Then run following SQL :
sqlplus / @$FND_TOP/patch/115/sql/wfctqrec.sql

Reorg Index

4) Index

- Normal

Alter index rebuild tablespace ;

- IOT Indexes starting with SYS_IOT_TOP

If we move the IOT indexes with normal commnad, we get the following error

ERROR at line 1:
ORA-28650: Primary index on an IOT cannot be rebuilt

To move the IOT indexes , use the following method

a) Find the table name on which index is created

SQL> select table_name,table_owner from dba_indexes where index_name like 'SYS_IOT_TOP_375805';

TABLE_NAME TABLE_OWNER
------------------------------ ------------------------------
AQ$_WF_WS_SAMPLE_G APPLSYS


b) If the table name is starting with AQ$

1)Find the queue table, in the same way as was done above for tables.


SQL> select QUEUE_TABLE,OWNER from dba_queue_tables where QUEUE_TABLE like '%WF_WS_SAMPLE%';

QUEUE_TABLE OWNER
------------------------------ ------------------------------
WF_WS_SAMPLE APPLSYS

We have removed the characters before first underscore and after last underscore
When searching for queue table.

2) exec move_aqt.move('APPLSYS',' WF_WS_SAMPLE' ,'DUMMY');

Package move_aqt needs to be installed as mentioned above.
3) Index SYS_IOT_TOP_375805 will be moved to dummy tablespace.

If the table name is not starting with AQ$

1) Move the table_name , from step (a), this will move the index.


5) Index Partition

Alter index rebuild partition
tablespace ;

dba_ind_partitions -- to get the partition name

6) Index Subpartition

Alter index rebuild subpartition
tablespace ;

Dba_ind_subpartitions - to get the subpartition name

Reorg Table Partiton

2) Table Partiton

ALTER TABLE
MOVE PARTITION
TABLESPACE ;

Dba_tab_partitions -- to get the partition name

3) Table Subpartition

ALTER TABLE
MOVE SUBPARTITION
TABLESPACE ;

Dba_tab_subpartitions - to get the subpartition name

- Reorg IOT Tables starting with SYS_IOT_OVER

- IOT Tables starting with SYS_IOT_OVER

If we move the above table with normal command, we get the following error

ERROR at line 1:
ORA-25191: cannot reference overflow table of an index-organized table

To move the above table , use following method

a) Find the Iot_name

select OWNER,TABLE_NAME,TABLESPACE_NAME,IOT_NAME,IOT_TYPE
from dba_tables
where TABLE_NAME like 'SYS_IOT_OVER_375778';
OWNER TABLE_NAME TABLESPACE_NAME IOT_NAME IOT_TYPE
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------
APPLSYS SYS_IOT_OVER_375778 APPS_TS_QUEUES AQ$_WF_CONTROL_G IOT_OVERFLOW


b) Find the queue table

Queue_table - remove the characters before the first underscore and last underscore of IOT_NAME
And then search for the queue table

So in our example IOT_NAME is AQ$_WF_CONTROL_G
So we are searching queue_table like '%WF_CONTROL%'

SQL> select QUEUE_TABLE,OWNER from dba_queue_tables where QUEUE_TABLE like '%WF_CONTROL%';

QUEUE_TABLE OWNER
------------------------------ ------------------------------
WF_CONTROL APPLSYS


c) Install move_aqt package using Note 394713.1
d) exec move_aqt.move('APPLSYS',WF_CONTROL,'DUMMY');
e) Once this is done table SYS_IOT_OVER_375778 will be moved to DUMMY tablespace.




- Long Datatye column

While moving the tables with column of long datatype, we get the following error

ERROR at line 1:
ORA-00997: illegal use of LONG datatype

These tables cannot be moved using normal move command.
We need to export, drop and import the tables.

Reorg Normal Table

- Create tablespace "TEST" for reorg the below tables:

CREATE TABLESPACE TEST NOLOGGING
DATAFILE '/test/oradata/data02/test01.dbf' SIZE 100M
AUTOEXTEND ON NEXT  100M MAXSIZE 5000M EXTENT MANAGEMENT LOCAL UNIFORM SEGMENT SPACE MANAGEMENT
MANUAL;

ALTER TABLESPACE TEST  ADD DATAFILE '/test/oradata/data02/test02.dbf' SIZE 100M AUTOEXTEND ON
NEXT 100M MAXSIZE 5000M;

(Note: Please add more space if needed accordingly)

- grant unlimited access

alter user APPLSYS quota unlimited on TEST;
alter user WSH quota unlimited on TEST;
alter user ASO quota unlimited on TEST;
alter user ECX quota unlimited on TEST;

- move tables to TEST tablespace and back to original tablespace

alter table ECX.ECX_DOCLOGS move tablespace TEST;
alter table ECX.ECX_DOCLOGS move Tablespace APPS_TS_TX_DATA;

alter table APPLSYS.WF_ITEM_ACTIVITY_STATUSES move tablespace TEST;
alter table APPLSYS.WF_ITEM_ACTIVITY_STATUSES move Tablespace APPS_TS_TX_DATA;

alter table APPLSYS.FND_LOG_MESSAGES move tablespace TEST;
alter table APPLSYS.FND_LOG_MESSAGES move Tablespace APPS_TS_TX_DATA;

alter table WSH.WSH_EXCEPTIONS move tablespace TEST;
alter table WSH.WSH_EXCEPTIONS move Tablespace APPS_TS_TX_DATA;

alter table APPLSYS.WF_ITEM_ACTIVITY_STATUSES_H move tablespace TEST;
alter table APPLSYS.WF_ITEM_ACTIVITY_STATUSES_H move Tablespace APPS_TS_TX_DATA;

alter table ASO.ASO_ORDER_FEEDBACK_T move tablespace TEST;
alter table ASO.ASO_ORDER_FEEDBACK_T move Tablespace APPS_TS_QUEUES;

- select count(*) from dba_segments where tablespace_name='TEST';
output should be 0

- Re-build indexes (need to provide the index list)

alter INDEX OWNER.INDEX_NAME rebuild;


- Check for  UNUSABLE Index and rebuild them

select 'alter  INDEX ' ||owner||'.'||index_name|| ' rebuild;'  from dba_indexes where status='UNUSABLE';