Wednesday, April 01, 2009

MRC Implementaion on Existing Environment???

What is MRC?

MRC allows Oracle Applications to support organizations that are transitioning from their national currency (Functional Currency) to other Currencies Supported by Oracle.

Is the process of converting functional currency into other currency during reporting.

Modules Impacted With MRC

General Ledger
Account Payables
Account Receivables
Fixed Assets etc.

How do I Enable MRC
STEPS TO BE FOLLOWED:

Installing MRC with Invokers Rights On( This is for New Installation).
Set Up Environment and Database.
Compile and Validate APPS Schemas.
Convert to Multiple Reporting Currencies.
Verify the Installation.
Perform Post–Installation Steps.
Maintain MRC Schema Objects

Running adadmin

Login to FE Node (elephant)
Run adadmin
Filename [adadmin.log] : adadmin_dv1_mrc.log
Please enter the batchsize [1000] : 100000
Enter the password for your 'SYSTEM' ORACLE schema:
Enter the ORACLE password of Application Object Library [APPS]
4. Maintain Applications Database Entities menu
1. Validate APPS schema
This will generate APPS.lst @ $APPL_TOP/admin/SID/out/APPS.lst

Sample Summary of Issues and Proposed Fixes Reported by APPS.lst

Objects with the same name as schema
Proposed Fix: Dropping all objects
Invalid Synonyms in APPS
Proposed Fix: Drop & Recreate all ,drop which are still invalid as reported by validate APPS.
missing or incorrect synonyms in "APPS"
Proposed Fix : Repointed the missing and incorrect synonyms
Missing Grants
Proposed Fix: Grant will be given to the reported objects.
Missing Privileges/ synonyms to objects not in "APPS" base schemas - 2586
Proposed Fix: Grants will be given
Invalid Objects
Proposed Fix: If Close to production. The objects will be documented and the results verified post MRC Installation.

Code Objects in the Base Schema - (Recommended but not mandatory)
Proposed Fix: No Action
Tables exist both in APPS & Base Schema
Proposed Fix: Compare in production & dropped.
Checking for packages in "APPS" with lines > 255 characters
Proposed Fix: Breaking the lines having more than 255 chars.
Checking for missing/invalid APPS_DDL or APPS_ARRAY_DDL packages
Proposed Fix: DBA will address this.

some Issue while running adadmin

sqlplus -s SYSTEM/***** @/apps/SID/appl_top/ad/11.5.0/admin/sql/advrfapp.sql APPS APPLSYS
declare
*
ERROR at line 1:
ORA-20000: ORA-01403: no data found : This procedure cannot continue because
the "SELECT ANY DICTIONARY" privilege for the SYSTEM schema is missing.
Please rerun the procedure as the privilege has now been granted.
ORA-06512: at line 26

Re-run adamin or grant select any dictionary to system;

ORA-04021: timeout occurred while waiting to lock object SYS.DBMS_LOCK.
Identify the session holding DBMS_LOCK
Workaround
Set job queue process to 0

sqlplus -s SYSTEM/***** @/apps/sid/appl_top/ad/11.5.0/admin/sql/advrfapp.sql APPS APPLSYS
ERROR at line 1:

ORA-20000: ORA-20000: ORU-10027: buffer overflow, limit of 50000 bytes :

Fix : Changed serveroutput from 50000 to 1000000

Different issues

Incorrect synonym:
APPS.MSD_APP_INSTANCE_ORGS - should point to MRP.MSD_APP_INSTANCE_ORGS. Incorrect

Synonym Pointing to Remote Objects with DB Link

Synomyms Pointing to Objects which is Invalid.

Synomyms pointing to Objects which does not exist.

Application Schema not having standard packages like APPS_ARRAY_DDL and APPS_DDL

Similar table object exists in two different schemas.

Necessary grants are not given from Custom Apps schema to APPS.

Package line length more than 255 chars

Invalid Objects

Enabling MRC through adadmin

Run adadmin
4. Maintain Applications Database Entities menu
5. Convert to Multiple Reporting Currencies
Enter SYSTEM password
Enter the number of workers [64] : 24

Converting to Multiple Reporting Currencies (MRC) will create
one extra schema per APPS schema in your database.

Each MRC schema requires about 600 Megabytes of free space in your
SYSTEM tablespace and takes from 6 to 18 hours to create.

You must not perform any DDL operations on your Oracle Applications
database while converting to MRC, but you may change data.

Issue and Fixes

declare
*
ERROR at line 1:
ORA-20005: ORA-02021: DDL operations are not allowed on a remote database
[create_mc_schema(APPLSYS, TRUE, FALSE, none): do_create_mc_schema(1, APPLSYS,
APPS, APPS_MRC, none, TRUE): create_synonyms_for_mc(APPS,APPS_MRC,APPS):
ad_apps_private.create_gs(APPS,APPS_MRC,CCA_ACK_HEADER, FALSE,ALL,APPS):
create_grant(APPS,APPS_MRC,CCA_ACK_HEADER,ALL,FALSE,): do_apps_ddl(APPS,GRANT
ALL ON "CCA_ACK_HEADER" TO APPS_MRC): do_apps_ddl(APPS,GRANT ALL ON
"AMV_MATCHING_QUEUE_TBL" TO APPS_MRC): do_apps_ddl(APPS,GRANT ALL ON
"AK_LOADER_TEMP" TO APPS_MRC): Start Time: 2009-03-17;00:19:25 Failure Time:
2009-03-17;00:23:24 Elapsed: 00;00:03:59]
ORA-06512: at line 25

Fix : Drop the object pointing to remote object and re-create after MRC is over

ERROR at line 1:
ORA-20005: ORA-01403: no data found
ORA-01720: grant option does not exist for 'APPLSYS.FND_LOBS'
[create_mc_schema(APPLSYS, TRUE, FALSE, none): do_create_mc_schema(1, APPLSYS,
APPS, APPS_MRC, none, TRUE): create_synonyms_for_mc(APPS,APPS_MRC,APPS):
ad_apps_private.create_gs(APPS,APPS_MRC,FCOA_ATTACHMENTS_V, FALSE,ALL,APPS):
create_grant(FCOA_ATTACHMENTS_V does not exist in ): do_apps_ddl(APPS,GRANT ALL
ON "FCOA_ATTACHMENTS_V" TO APPS_MRC): Start Time: 2009-03-05;08:56:58 Failure
Time: 2009-03-05;08:58:33 Elapsed: 00;00:01:35]
ORA-06512: at line 25

Fix :select owner,object_type from dba_objects where object_name='FCOA_ATTACHMENTS_V';

CONNECT APPLSYS/xxxx

grant ALL on APPLSYS.FND_LOBS TO XXFAXMGR with grant option;
grant ALL on APPLSYS.FND_ATTACHED_DOCUMENTS TO XXFAXMGR with grant option;

CONNECT APPS/xxxxxx
grant ALL on APPS.FND_ATTACHED_DOCS_FORM_VL TO XXFAXMGR with grant option;

CONNECT XXFAXMGR/xxxxx
grant ALL on XXFAXMGR.FCOA_ATTACHMENTS_V TO APPS with grant option;


ERROR at line 1:
ORA-20005: ORA-00980: synonym translation is no longer valid
[create_mc_schema(APPLSYS, TRUE, FALSE, none): do_create_mc_schema(1, APPLSYS,
APPS, APPS_MRC, none, TRUE): create_synonyms_for_mc(APPS,APPS_MRC,APPS):
ad_apps_private.create_gs(APPS,APPS_MRC,AVL_LOOKUPS, FALSE,ALL,APPS):
create_grant(APPS,APPS_MRC,AVL_LOOKUPS,ALL,FALSE,): do_apps_ddl(APPS,GRANT ALL
ON "AVL_LOOKUPS" TO APPS_MRC): Start Time: 2009-03-10;03:27:40 Failure Time:
2009-03-10;03:27:43 Elapsed: 00;00:00:03]
ORA-06512: at line 25

conn /as sysdba
col object_name for a40
select owner,object_name, object_type from dba_objects where OBJECT_NAME ='AVL_LOOKUPS';
connect apps/xxxxx
grant all on AVL_LOOKUPS to APPS_MRC;

ERROR at line 1:
ORA-20005: ORA-20000: PACKAGE BODY APPS.CMF_CREATE_ITEMS_PKG: Line 28 longer
than 255 characters. [create_mc_schema(APPLSYS, TRUE, FALSE, none):
do_create_mc_schema(1, APPLSYS, APPS, APPS_MRC, none, TRUE):
invoker_mrc_grants(APPS, APPS_MRC): grant_a_package(APPS, CMF_CREATE_ITEMS_PKG,
PACKAGE, APPS_MRC, Y, D, FALSE):
ad_apps_private.copy_code(CMF_CREATE_ITEMS_PKG,PACKAGE BODY,APPS,APPS_MRC):
Start Time: 2009-03-09;03:50:50 Failure Time: 2009-03-09;03:55:23 Elapsed:
00;00:04:33]
ORA-06512: at line 25

Compile package
===============

ALTER PACKAGE APPS.CMF_CREATE_ITEMS_PKG COMPILE BODY;

ALTER PACKAGE APPS.CMF_CREATE_ITEMS_PKG COMPILE;

Action : Line length fix by Maynak and complied the package
Status : Restarted adadmin and Fixed

ERROR at line 1:
ORA-20005: ORA-04063: has errors
ORA-04063: view "CMS.CMS_R_SO_HEADERS_V" has errors [create_mc_schema(APPLSYS,
TRUE, FALSE, none): do_create_mc_schema(1, APPLSYS, APPS, APPS_MRC, none,
TRUE): create_synonyms_for_mc(APPS,APPS_MRC,APPS):
ad_apps_private.create_gs(APPS,APPS_MRC,CMS_R_SO_HEADERS_V, FALSE,ALL,APPS):
create_grant(APPS,APPS_MRC,CMS_R_SO_HEADERS_V,ALL,FALSE,):
do_apps_ddl(APPS,GRANT ALL ON "CMS_R_SO_HEADERS_V" TO APPS_MRC): Start Time:
2009-03-17;03:56:51 Failure Time: 2009-03-17;03:56:53 Elapsed: 00;00:00:02]
ORA-06512: at line 25

Fix:

.Take bacup of source of that view using dbms_metadata
Drop that view after consulting appl team.

Maintaining MRC after Patching and addition of objects to APPS/Custom schema impacting MRC
Increase of Patching Window time
Synonym Objects exists in APPS which is pointing to remote DB through DB link
Non Standard APPS Custom schema creation without Standard packages.
New custom Package creation with Line > 255.
Full grant of SYS, APPS , APPLSYS and other Standard APPS schema objects to Custom Apps Schema Owners

Existence of Code objects in base schemas

Reference

Metalink IDS

135756.1
135773.1

Pre Requisites to Enable MRC

1. 1486355 or 1512489 patches to be applied
2. Requirement 2000m in SYSTEM/TEMP and UNDO each.
3. Modify Validation Script to increase buffer size as below:

cd $AD_TOP/admin/sql/
Modify advrfapp.sql

From :
begin
dbms_output.enable(50000);
end;

To :
begin
dbms_output.enable(1000000);
end;

No comments:

Enabling SSL or TLS in Oracle E-Business Suite Release 12.2

Enabling TLS in Oracle Apps R12.2 Here  we would be looking at the detailed steps for Enabling TLS in Oracle Apps R12.2 Introduction: ...