Recent Apps DBA Interview Questions..

1. What are .lct and .ldt files in Patch Directory?

Ans:

The patch metadata LDT files (also called datafiles) are FNDLOAD data files included in the top-level directory of all recent patches. The LDT files contain prerequisite patch information and a manifest of all files in the patch with their version numbers. The Patch Information Bundle metadata also include information about the relationships between patches, such as which minipacks are contained in the recommended.

LCT files (also called configfiles) are the configuration files which are used to download/upload data. Without configfiles, datafiles are useless.

2. If Ad worker fails during Ad patching, How many times by default adpatch automatically tries to resume the patching?


Ans:

Three Times

3. While trying to change one profile option at site level, the option is not editable mode? How to make it editable mode?


Ans:

Goto Application Developer -> Profiles -> Give Profile Options Names -> Check updatable

4. How do you check Compatibility of Oracle Applications with Any operating System?

Ans:

Metalink -> Certify Tab -> View Certification by platform

5. How do you check Latest CPU Patch for Oracle Server and Applications?

Ans:

Metalink -> Patches & Updates Tab

6. In RAC env, Each node contains How many IPs?

Ans:

Three

7. What are the tables updated when you apply application patch?

Ans:

ad_applied_patches and ad_bugs

8. Can you apply Opatch without inventory?

Ans:

No

9. If there is no inventory, How do you apply a opatch?

Ans:

Create inventory using runInstaller

10. What are the tables get created during Apps Patching?

Ans:

ad_deferred_jobs and fnd_installed_processes

11. Default environment variable to be set for Forms Config files?

Ans:

FORMS60_WEB_CONFIG_FILE

12. Profile option to determine which dbc file to use?

Ans:

Application Database ID

13. How do you hide apps password during adpatching?

Ans:

adpatch flags=hidepw

14. What is inter operability patch?

Ans:

OS compatibility patch, mostly applied during upgradation

15. How do you compile jsp files?

Ans:

Using adadmin or ojspCompile(perl -x $JTF_TOP/admin/scripts/ojspCompile.pl)

16. What is cache in Concurrent Managers Definition?

Ans:

No of concurrent requets that have to be cached from fnd_concurrent_requests while reading fnd_concurrent_requests

17. Types of profile options?

Ans:

1. Site level
2. User level
3. Responsibility Level
4. Server Level
5. Application Level

18. Opatch log file location ?

Ans:

$ORACLE_HOME/.patch_storage/patch_number/*.log

19. Different levels of SQL Tracing?

Ans:

Regular (Level 1 – standard/default level)
Level 4 (standard + binds)
Level 8 (standard + waits)
Level 12 (standard + binds and waits)

20. If you lost all redo logs files during DB is up and running? What will happen how do you recover it?

Ans:

DB will get crashed immediately

Solution;

1. You have to go for Incomplete Recovery
2. One way: Take previous backup, recover up to last archive and open the database
3. Second way: open the database in no mount state, create control file with rest logs and open the database with rest logs.

21. DB is up and running fine? you lost one data file? DB is in archive log mode? How do you recover it?

Ans:

1. If you have a backup of datafile, restore it and apply archives.
2. If you don't have backup of datafile, create datafile in database and apply archives.

22. How do you clone a context file or how do you change existing port pool?

Ans:

Using adclonectx.pl, you can clone next context file, during cloning you can give new port pool, and run autoconfig

23. How do you run autoconfig in test mode?

Ans:

adchkcfg.sh (AD_TOP/bin)

24. If you lost dbc file, How will you recover it?

Ans:

Using adgendbc($AD_TOP/bin) or run Autoconfig

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