AD Admin

               Adadmin is a utility that performs a number of tasks required from maintaining and

administering an Oracle Applications instance. It performs two types of works one is performed at

the database level and other is performed at the file system level. The user is required to provides all the informations at the adadmin prompt.

To start the adadmin login to the APPL_TOP as the owner of the application file system.

..........................................................................

[applmgr@apps ~]$ adadmin

                    
             Copyright (c) 2002 Oracle Corporation
                        Redwood Shores, California, USA

                     Oracle Applications AD Administration

                                 Version 11.5.0

NOTE: You may not use this utility for custom development
      unless you have written permission from Oracle Corporation.

Your default directory is '/apps/appsappl'.
Is this the correct APPL_TOP [Yes] ?

AD Administration records your AD Administration session in a text file
you specify.  Enter your AD Administration log file name or press [Return]
to accept the default file name shown in brackets.

Filename [adadmin.log] :

You can be notified by email if a failure occurs.
Do you wish to activate this feature [No] ?

Please enter the batchsize [1000] :


Please enter the name of the Oracle Applications System that this
APPL_TOP belongs to.

The Applications System name must be unique across all Oracle
Applications Systems at your site, must be from 1 to 30 characters
long, may only contain alphanumeric and underscore characters,
and must start with a letter.

Sample Applications System names are: "prod", "test", "demo" and
"Development_2".

Applications System Name [apps] : apps *


Do you currently have files used for installing or upgrading the database
installed in this APPL_TOP [YES] ? YES *


Do you currently have Java and HTML files for HTML-based functionality
installed in this APPL_TOP [YES] ? YES *


Do you currently have Oracle Applications forms files installed
in this APPL_TOP [YES] ? YES *


Do you currently have concurrent program files installed
in this APPL_TOP [YES] ? YES *


You are about to use or modify Oracle Applications product tables
in your ORACLE database 'apps'
using ORACLE executables in '/apps/appsora/8.0.6'.

Is this the correct database [Yes] ?

AD Administration needs the password for your 'SYSTEM' ORACLE schema
in order to determine your installation configuration.

Enter the password for your 'SYSTEM' ORACLE schema: manager


The ORACLE username specified below for Application Object Library
uniquely identifies your existing product group: APPLSYS

Enter the ORACLE password of Application Object Library [APPS] : apps

AD Administration is verifying your username/password.
...........................................................................

Once all the answers are given, the adadmin main menu appears.
In this menu there are Six main options. The first two deals with maintaining application file system
eg: re-linking the application, generating the form, regenerating the JAR files etc.

The next two deals with maintaining database objects eg: compiling and validating the schema, compiling the flexfield etc. The fifth option changes the maintenance mode, and sixth is for exist





1: Generate Applications File Menu:

1. Generate message file:

This option generates all the Oracle message files. Oracle Application uses this file to display message.This task generate message binary files (extension .msb) from Oracle Application Library tables.

2. Generating Forms files:

This options generates form files (extension .fmx) from binary forms file (extension .fmb). These files are normally located at AU_TOP, and the executable file are stored under each product directory.

3. Generate report files:

This option generates the Oracle report file (extension .rdf). You can generate report files of a specific products or you can generate reports files of all products.

4. Generate graphic files:

This option generates Oracle Graphic files (extension .ogd) from the graphic definition files.

5. Generate product JAR (Java Archive) files:
This the last option in the menu. It generates JAR files. It sings the JAR files and regenerate the product JAR files in JAVA_TOP and copies them to APPL_TOP.


2 : Maintain Application Files menu:
This menu takes care of the maintenance task required to keep your application files up-to-date.

1. Relink Application programs:

This option relinks Oracle Application programs with Oracle server libraries. This option should be use only when asked for specifically by Oracle.

2. Create Applications environment files:

This option create an environment file that defines your system configuration.

3. Copy files to destinations:

This option copies files from each product are to central location, where they can be easily referenced

by non-Application Programs. The file types copied to the respective destinations are

JAVA file to $JAVA_TOP
HTML file to $OAH_TOP
Media files to $OAM_TOP


4: Convert Character set:

This task converts the character set of all translatable files in the APPL_TOP. You should select this

task if you want to change the base language or add additional language to Oracle Applications.

This task has a sub menu.

It scans the APPL_TOP and creates files in $APPL_TOP/admin/$TWO_TASK/out directory

admanifest_excp.lst: lists files that will not be converted because of lossy conversion admanifest.lst: lists files that can be converted.

admainfest_lossy.lst: lists files with loss conversion , including line by line details.

Review the files listed in admanifest_excp.lst. Fix the files that report lossy conversion before you
convert the character set.

5: Maintain snapshot information:

Basically there are two types of snapshots

APPL_TOP: It lists patches and version of files in the APPL_TOP.

Global snapshot: It lists patches and latest versions of files in the entire Application system. (i.e. across all APPL_TOP's)

Both APPL_TOP and global snapshots may be either

Current view snapshot: A Current view snapshot is created once and updated when appropriate to maintain a consistent view.

named view snapshots: It is a copy of the current view snapshot at a particular time (not necessarily the latest) and it is not updated.

This menu has a sub menu.

6: Check for missing files:

This option verifies all the file required to run Oracle Applications for the present configuration are there in APPL_TOP, and checks if any are missing. The information about the missing files is written in admvrf.lst file and its location is $APPL_TOP/admin$TWO_TASK/out.


 3: Compiled/Reload Application Database entities:




1: Compile APPS schema:

This option compiles invalid objects in the APPS schema. Invalid objects in other schema as sys and system are not necessarily compiled. You can use multiple workers for parallel processing.

2: Compile menu information:

It compiles the menu data structure. It needs to be done if compile security concurrent requests submitted from the menu form fail or if you have uploaded menu entries to the FND_MENU_ENTRIES table.

3: Compile flex fields:

It compiles flex field data structure in Applications Object Library (AOL) tables. this needs to be done if the patch application changes the setup of flex field.

4: Reload JAR files to database:

It reloads all the Oracle Application JAR files to the database, choose this option if the patch readme says so or if all Oracle Application Java classes are removed from your database.


4: Maintain Application Database Entities:

1: Validate APPS schema:

Validating APPS schema means verifying the integrity of the APPS schema. It checks whether the APPS schema has proper roles and privileges or not. This task produces a report named 
<APPS schema name>.lst, which is located at $APPL_TOP/admin/$TWO_TASK/out.

2: Re-create grants and synonyms for APPS schema:

It recreates grants and synonyms for APPLSYSPUB, for APPS schema and for recreating grants on some packages from system to APPS. Each product's data objects are created in its own schema (such as AP schema) but the user accesses all data objects through the APPS schema. therefore the APPS schema must have the appropriate grants and synonyms for those objects.

3: Maintain multi-lingual tables:

This task need to be done after you add a new language. It maintains multilingual tables for Oracle Applications by adding missing, untranslated rows.

4:Check dual table:

It ensures that the dual table exists and has exactly one row. The dual table is created automatically by Oracle along with the data dictionary. This table has one column named "Dummy" of type Varchar2 and contains one row with a value of 'X'.

5: Maintain multiple reporting currencies schema:

It maintains multiple reporting currencies schema (MRC). If MRC functionality is not implements in your database, the option will read 'Convert to Multiple Reporting Currencies schema'.

6: Convert to multi-org:

This option appears as a menu choice only if multi-org is not installed in your database. Using this you can convert to multiple org architecture.


5: Changing Maintenance Mode:

This is the last option of adadmin menu. In Maintenance mode Oracle Application system is made accessible only for patching activities. This provides optimal performance for Auto Patch sessions. Maintenance mode is only needed for Auto Patch session.

To enable Maintenance mode enter 1
To disable Maintenance mode enter 2

RMAN Catalog Configuration

                    There are couple of ways, we can setup the RMAN. We can use control file to store backup catalog information or we can have separate database to store catalog information. The CONTROL_FILE_RECORD_KEEP_TIME initialization parameter controls how long backup records are kept in the control file before those records are re-used to hold information about more recent backups. By default this parameter set to 7 days.


Benefits of using RMAN:

•     Minimize the possibility of human error

•     Simple command interface

•     Manage the complexity of backup and recovery operations

•     Unused block compression lets you skip unused data blocks, thus saving space and time.

•     RMAN can be fully automated

•     Make backups scalable and reliable

•     Supports high-speed incremental backups

•     Can perform error checking when backing up or during recovery

•     Can perform image copies which are similar to operating system backup

•     Can be used with 3rd party backup management software like Veritas Netbackup

•     It is well integrated into OEM, so you can make use of Oracle's scheduler

   
RMAN terminology:

Backup piece: operating system file containing the backup of a data file, controlfile, etc
Backup set: logical structure that contains one or more backup pieces, all relevant backup pieces are contained in a backup set
Image copy: similar to operating system copies like cp or dd, they will contain all block if not used
Channel allocation: Channel allocation is a method of connecting rman and the target database while also specifying the type of backup i.e. disk or tape, they can created manually or automatically.


The Recovery Catalog:

RMAN will use the controlfile on the target database to store repository information regarding any backups for that server, this information can also be stored in a recovery catalog (optional) which resides on a rman server its own database which is dedicated to RMAN, information is still written to controlfile even if a recovery catalog is used.


RMAN Catalog Configuration:
To configure RMAN catalog follow the below steps



1: Source Database Must be in Archivelog mod:
            If source database is not in Archivelog mod. Configure the database in archivelog mod.
To check wether your database is in archivelog or no archivelog mod you can run following commands.

SQL> SELECT log_mode FROM v$database;

or you can confirm it by runnig following command.

SQL> ARCHIVE LOG LIST



2: Create a new database for the catalog : catdb



3: Configure listener on Source database:


4: Configure tns entery on catalog database: catdb



5: create rman "rman_user" user on source database:


6: create tablespace on catalog database: catdb

CREATE TABLESPACE rman
DATAFILE '/u02/oradata/rman/rman01.dbf' size 150m;

7: Create user and give default tablespace the one we created above. catdb

CREATE USER rman IDENTIFIED BY rman
DEFAULT TABLESPACE rman
QUOTA UNLIMITED ON rman;


7: Grant connect, resource, recovery_catalog_owner to the catalog user:

SQL> GRANT connect, resource, recovery_catalog_owner TO rman;

Here the role "RECOVERY_CATALOG_OWNER" provides the user with all privileges required to maintain and query the recovery catalog.


8: create password file on Source database:

9: connect to rman on catalog database: catdb

 $rman target /


10: create catalog: catdb

rman>create catalog;


11: connect to target database: catdb

$rman target rman_user/passwd @tns(tns filename) catalog rman/rman


12: then register the database: catdb

rman>register database



Make sure that the registration was successful by running REPORT SCHEMA:
RMAN> REPORT SCHEMA;

The following command will show you all the configuration of rman.

 rman>show all;

How to use CRONTAB

               Linux has a great program called cron. The crontab (cron derives from chronos, Greek for time, tab stands for table) command. Its a daemon/service that executes shell commands periodically on a given schedule. We could also use it to automatically create backups, synchronize files, schedule updates, and much more. Lets Know about crontab.

 
To see what crontabs are currently running on our system, we can open a terminal and run:

crontab –l

The above command displays the entries in crontab if any.

 
View Other Linux User’s Crontabs entries:

To view crontab entries of other Linux users, login to root and use -u {username} -l as shown below.

crontab -u oracle -l
@monthly /home/oracle/monthly-backup
00 09-18 * * * /home/oracle/check-db-status


To edit the list of cronjobs you can run:

crontab –e

This will open a default editor eg vi to let us edit the crontab. If you save and exit the editor,
all our cronjobs are saved into crontab. Cronjobs are written in the following format:

* * * * * /bin/testscript.sh


Edit Other Linux User’s Crontab File entries:

To edit crontab entries of other Linux users, login to root and use -u {username} -e as shown below.

crontab -u oracle -e
@monthly /home/oracle/monthly-backup
00 09-18 * * * /home/oracle/check-db-status
~
~
~
"/tmp/crontab.XXXXyjWkHw" 2L, 83C2

 
Removing a crontab file:

To remove your crontab file simply enter the following terminal command:

crontab -r


* * * * * /bin/testscript.sh

As we can see there are 5 stars. The stars represent different date parts in the following order:

1. minute (from 0 to 59)
2. hour (from 0 to 23)
3. day of month (from 1 to 31)
4. month (from 1 to 12)
5. day of week (from 0 to 6) (0=Sunday)


Execute something every minute:

If we want to execute the file “testscript.sh” every minute, then we need to do the following crontab entry.

* * * * * /home/oracle/testscript.sh

Please Note: If we leave the star (*) it means every. There are all star. So this means execute  /home/oracle/testscript.sh every minute.

1.every minute
2.every hour
3.every day of the month
4.every month
5.every day in the week.
So the above script will be executed every minute without exception.


Execute every Friday 2AM:

So if we want to schedule the script to run at 2AM every Friday, we would need to do the following cronjob entry:

0 2 * * 5 /bin/testscript.sh
                    or
0 0 * * Fri /bin/testscript.sh

Note: Get into the habit of using Fri instead of 5. Please note that the number starts with 0 (not with 1), and 0 is for Sun (not Mon).

So, the script will be executed when the system clock hits the following:

1. minute: 0
2. hour: 2
3. day: * (every day of month)
4. month: * (every month)
5. weekday: 5 (=Friday)


Execute on workdays 1AM:

If we want to schedule the script to Monday till Friday at 1 AM, we would need the following cronjob entry:

0 1 * * 1-5 /home/oracle/testscript.sh

The script will be executed when the system clock hits the following:

1. minute: 0
2. hour: 2
3. day: * (every day of month)
4. month: * (every month)
5. weekday: 1-5 (=Monday till Friday)


Execute a job every 5 months:

There is no direct way of saying ‘every 5 months’, instead you have to specify what specific months you want to run the job. Probably you may want to run the job on 5th month (May), and 10th month (Oct).

The fourth field is for Months. If you specify * in this field, it runs every month. To run for the specific month, you have to specify the number that corresponds to the month. For example, to run the job on May and Oct, you should specify 5,10 (or) you can simply use the 3 letter acronym of the month and specify May,Oct.

The third field is for DOM (Day of the Month). If you specify * in this field, it will run every day of the month. If you specify 1 in this month, it will run on 1st of the month.

The following example runs the testscript.sh twice a year.
i.e 1st May and 1st Oct.

0 0 1 5,10 * /home/ramesh/backup.sh
                          (or)
0 0 1 May,Oct * /home/ramesh/backup.sh


Schedule a Job Twice a Day:

The following script will run twice every day at 11:00 and 16:00 (4pm). The comma separated value in a field specifies that the command needs to be executed in all the mentioned time.

00 11,16 * * * /home/oracle/testscript.sh

00 – 0th Minute (Top of the hour)
11,16 – 11 AM and 4 PM
* – Every day
* – Every month
* – Every day of the week


Multiple commands:

A double-ampersand “&&” can be used to run multiple commands consecutively. The following example would run command1 and then command2 once a day:

@daily <command1> && <command2>

 
Execute a Linux Command After Every Reboot using @reboot:

Using the @reboot cron keyword, this will execute the specified command once after the machine got booted every time.

@reboot /home/oracle/testscript.sh


Crontab examples for practice:

Runs every minute:
* * * * * <command>

Runs at 40 minutes past the hour
40 * * * * <command>

Runs at 7:30 am every day
30 7 * * * <command>

Runs at 7:30 pm every day
30 18 * * * <command>

Runs at 1:00 am every Sunday
00 1 * * 0 <command>

Runs at 9:30 am on the first day of every month
30 9 1 * * <command>

 
There are also special strings that can be used:

Runs at boot
@reboot <command>

Runs once a year
@yearly <command>

Runs once a year
@annually <command>

Runs once a month
@monthly <command>

Runs once a week
@weekly <command>

Runs once a day
@daily <command>

Runs once a day
@midnight <command>

Runs once a hour
 @hourly <command>

How To Generate ASH (Active Session History) Report

         Oracle 10g introduced the Active Session History (ASH) as part of the Diagnostics and Tuning Pack. ASH stands for Active Session History. An ASH report details statistics from the in-memory performance monitoring tables.

The report provides:

Top User Events.
Details to the wait events
Top Queries
Top Sessions
Top Blocking Sessions
Top DB Objects
       The simplest way to generate this report is through the Oracle Enterprise Manager tool (OEM); however, there are times when this tool is not available. On those occasions, the DBA user can generate the report manually.

To generate the ASH report:

 Log in to the database.

At a command prompt issue the following command

@$ORACLE_HOME/rdbms/admin/ashrpt.sql

 The script prompts you for the following details:

Report Type: [ html | text ] (Specify a format for the report).

Enter value for begin_time: Specify the begin time for the report and press Enter (e.g., -1:30 is one hour and 30 minutes before from the present time).

Enter value for duration: Specify the duration for the report and press Enter (for example, a value of 30 indicates that the report should cover from the begin time to 30 minutes after that time;
leaving the duration empty specifies that the report should cover from the begin time to the current time).

Enter value for report_name: Name the report and press Enter.

The report is generated at the path where the SQLPlus command was issued.
The script produces either text or HTML output as requested.

Step by Step linux installation on VMware with Screenshots

This is a step-by-step installation procedure for Linux, specifically RedHat Linux 4. This is only for beginners
who are doing this for the first time. I hope this will be useful. I am trying to explain the installation procedure of Linux on VMware.

1: open your VMware machine and select "New Virtual Machine".

2: Select "Typical (recommended) "and press "Next".

3: Click on "I will install the operation system later" You can select "Installer disc" if you have Operating systems boot able disk.
 or you can select "Installer disc image file (iso)".
 But we will first create a machine then install operating system.

4:  Type your "Virtual Machine Name" and the location.
     Note: change the location and select the Drive which has more empty space because your machine will create its hard disk on the same location and it will grow after.
 

5: For changing the location click on Brows Button and select the drive and Folder. 

6: Type the disk space you want and click next.
You can add extra hard disk latter if you need.

7: click on finish

8: You have just created a machine you can say a CPU. You can see it on your VMware with your machine name. It will list all your Hardware’s like Memory, Processors, Hard disk etc.
  You can change your hardware settings by clicking on it
eg: if you want to change your (Memory) RAM then click on it and change it.

9: Now its time to select OS DISK or iso file.
   for this click on "CD/DVD"
   Next window: select "Use Physical drive" if you have disk or Image of OS boot able.
   if you have a image you have to mount it first with software like daemons tool or etc then
   select a mounted drive
   Click on "Use ISO image file" if you have an iso file of OS.
   click on "Browse" and select the iso file.  

10: After completing these steps its time to in install LINUX.
   Click on "Power on this Virtual Machine" to start the installation.


Press enter

11: select skip and press enter.

12: select the language.

13: The next window is for Partitioning of your hard disk.
There are 2 options available in Linux
Automatically Partition: Linux will automatically partition your hard disk.
Manually partition with Disk Druid: In this option you can create desired partitions, extra mount points etc. I am doing it by Manual Partition.

Select "Manually partition with Disk Druid" and click Next.
       
Click Yes for all warnings and click Next.

14: You will see /dev/sda its your hard disk.

15: For creating mount points

Click on new 


Mount Point: "/"

File system Type: ext3

Size (MB): 14000

Click ok

16: For "swap" space.

Click on new

file System Type: swap

17: Size (MB): 2048 (always double of RAM)

18: For extra mount points

Click on New


 Mount Point: "/oradata" (or whatever you want to name it just start with "/" mine is oradata)

    
 File system Type: ext3

Size (MB): 2048

If you have free disk space (/dev/sda) you can create more mount points.

Click next when you done.

 19:  Click next because we don’t have to change Boot Loader etc.

20: It’s time to configure the network device.

    Select the device (eth0) click on edit
   
    Uncheck "Configure using DHCP"


    ip address : 192.168.0.5 (or whatever you want)


    Netmask : 255.255.255.0

   Click ok

   

21: In Hostname section

manually : (provide host name eg: test.com etc.)

Click next

22: Disable Firewall and SELinux.

23: Select Time-Zone and Click Next.

24: Provide the password for the root user and click next.

25: Next window is for Package selection. You have to Options
Install default software packages: Linux install mostly (not all) package automatically.
Customize software packages to be installed: you can select packages you want to install or you can simply select everything.

select: Customize software packages to be installed

Click next

26: select Everything click Next.

27: Click next to start the installation.

28: Congratulations, the installation is complete.

Click on Reboot.

29: After restarts.

 Click Next

30: Select "tell me why i need to register and provide a Red Hat login"

   Click next.

31: You don’t need to create a user so click Next.

Click continue.

32: enter your username password

    username: root
    password: (password)

 

                                                                       .....Thank You.....

Oracle Application Interview Questions and Answers-3

Page-3

31: What are the problems u have faced while shutting down applications?
A : While shutting down application generally concurrent manager won’t go down because some or the other request may be running. We will see what are the concurrent requests running by querying fnd_concurrent_requests, fnd_concurrent_program_vl, v$session, v$process and v$sqltext.
If that request is only doing some select statement then we will kill those requests, otherwise we will check what time it will take to complete by querying the previous runs of that request and then we will decide what to do.

32: Where is HTML Cache stored in Oracle Apps Server?
A : Oracle HTML Cache is available at $COMMON_TOP/_pages,
$IAS_ORACLE_HOME/Apache/modplsql/cache

33: How do you know if a specific Oracle patch has been applied in apps to your environment?
A : Use table ad_bugs in which column bug_number is the patch number.

34: For a PL/SQL based concurrent program do you have to issue a commit at the end?
A : The concurrent program runs within its own new session. In APPS, the default database setting enforces a commit at the end of each session. Hence no explicit COMMIT is required.

35: What is a Jinitiator?
A : Jinitiator is the Oracle JVM used to run the applet instead of using browsers own JVM.
When we access forms applet first time, oracle jinitiator will be installed automatically.

36: Which adpatch drivers should be run first? If they are run in the wrong order can they be run again?
A : You should run them in alphabetical order c, d, then g.
you can run them again in the correct order.

37: Where (on which tier) do you run the three different patch drivers?
A : The "c" and "g" drivers are run on all tiers.
The "d" driver is only run on the database tier.

38: What are various modes of applying a patch?
A : adpatch can apply patches in
Test mode : In test mode, AutoPatch does not apply the patch. Instead, it lists each file it would have copied, relinked, executed, or generated and shows exactly what actions it would have performed had it applied the patch.
Pre-install mode : Pre-install mode is generally used during the upgrade process to update AD utilities, apply pre-upgrade patches, or work around other patching issues.
Non-interactive mode : Non-interactive patching is a way to avoid some of the prompts and automate the patching process. For this you have have to create defaults file

39: Can we invoke more than one adadmin session simultaneously? If so when? If not Why?
A : Yes & No you can run adadmin if FND_INSTALL_PROCESSES is NOT locked by any session. If this table is in use by any session then you can’t run adadmin at same time.

40: I am applying a patch, can I open another session in another node and run adpatch?
A : No

41: How to determine Oracle Apps 11i Version?
A : select RELEASE_NAME from fnd_product_groups;
You should see output like
RELEASE_NAME-----------------------11.5.10.2

42: How to find opatch Version?
A : opatch is utility to apply database patch, In order to find opatch version execute "$ORACLE_HOME/OPatch/opatch version"

43: How to find out invalid objects in the database?
A : select count(*) from dba_objects where status ='INVALID'

44: What are the different types of patches?
A : oneoff, mini packs, family packs, maintanance packs, rollup pathches, colsolidated patches.

45: What is a oneoff patch?
A : An oneoff patch is a small patch of (20-90K size) without any pre-req’s

<< page-2 >>                                                                                                                 << page-4>>

Oracle Application Interview Questions and Answers-2

Page-2

16: What is the other script by which u can start apache other than adapcctl.sh?
A : apachectl @IAS_ORACLE_HOME/Apache/bin

17: What is the configuration file for PL/SQL listener?
A : httpd_pls.conf @IAS_ORACLE_HOME/Apache/Apache/conf

18: How to skip copy portion while applying a patch?
A : adpatch options=nocopyportion

19: How to merge patches and what type of patches can be merged?
A : admrgpch We can merge any kind of application patches, if any of the patch contain a u-driver then merged patch will contain u_merged.drv otherwise c_merged.drv, d_merged.drv and g_merged.drv

20: What is the Tiered architecture of your instance?
A : Two Tier: Web and Forms on one node and Conc, admin and report on other node.

21: How to find formserver version?
A : f60gen and press enter, it will tell u the formserver version or we can find out from the front end using help menu.

21: How to find out what are the languages enabled in u r applications?
A : Query fnd_languages

23: How to retrieve SYSADMIN password ?
A : If forgot password link is enabled and sysadmin account is configured with mail id user forget password link else you can reset sysadmin password via FNDCPASS.

24: What are the problems u have faced while starting up applications?
A : Most of the time we will encounter problem with starting up concurrent managers. Reasons , database listener may be down or FNDSM entries are wrong in tnsnames.ora of 806_ORACLE_HOME.

25: What is RRA?
A : RRA stands for Report Review Agent. RRA is nothing but FNDFS which is part of apps listener. RRA job is to pick the log/out file from the file system and show on the editor when u press view log/out button in ‘View concurrent request form’.

26: What is GSM?
A : GSM stands for Generic service Manager, which will monitor application processes like web, forms etc and restarts any of this processes if goes down.

27: What is apps listener?
A : Apps lintener is the combination of FNDFS and FNDSM. FNDSM is service manager which will monitor application services on that node when GSM:enable profile value is ‘Y’.

28: How to find the application version like 11.5.8/11.5.9….?
A : select release_name from fnd_product_groups;

29: How to confirm if Report Server is Up & Running ?
A : Report Server is started by executable rwmts60 on concurrent manager Node & this file is under
$ORACLE_HOME/bin .execute command on your server like
$ps -ef | grep rwmts60
You should get output like
applmgr ……. rwmts60 name=REP60_apps
where apps is your Instance name.
Else you can submit a request like “Active Users” with display set to PDF, check output & log file to
see if report server can display PDF files.

30: How to find the database/sqlplus version?
Ans : select banner from v$version;

<<Page-3>>

Oracle DBA Interview Questions and Answers-3

Page-3

31: Give some examples of the types of database constraints you may find in Oracle and indicate their purpose.
A : A Primary or Unique Key can be used to enforce uniqueness on one or more columns.
A Referential Integrity Constraint can be used to enforce a Foreign Key relationship between two tables.
A Not Null constraint - to ensure a value is entered in a column
A Value Constraint - to check a column value against a specific set of values.

32: A table is classified as a parent table and you want to drop and re-create it. How would you do this without affecting the children tables?
A : Disable the foreign key constraint to the parent, drop the table, re-create the table, enable the foreign key constraint.

33: Explain the difference between ARCHIVELOG mode and NOARCHIVELOG mode and the benefits and disadvantages to each.
A : ARCHIVELOG mode is a mode that you can put the database in for creating a backup of all transactions that have occurred in the database so that you can recover to any point in time. NOARCHIVELOG mode is basically the absence of ARCHIVELOG mode and has the disadvantage of not being able to recover to any point in time. NOARCHIVELOG mode does have the advantage of not having to write transactions to an archive log and thus increases the performance of the database slightly.

34: What command would you use to create a backup control file?
A : Alter database backup control file to trace.

35: Give the stages of instance startup to a usable state where normal users may access it.
A: STARTUP NOMOUNT - Instance startup
STARTUP MOUNT - The database is mounted
STARTUP OPEN - The database is opened

36: What column differentiates the V$ views to the GV$ views and how?
A : The INST_ID column which indicates the instance in a RAC environment the information came from.

37: How would you go about generating an EXPLAIN plan?
A : Create a plan table with utlxplan.sql.
Use the explain plan set statement_id = 'tst1' into plan_table for a SQL statement
Look at the explain plan with utlxplp.sql or utlxpls.sql

38: How would you go about increasing the buffer cache hit ratio?
A : Use the buffer cache advisory over a given workload and then query the v$db_cache_advice table. If a change was necessary then I would use the alter system set db_cache_size command.

39: Explain an ORA-01555.
A : You get this error when you get a snapshot too old within rollback. It can usually be solved by increasing the undo retention or increasing the size of rollbacks. You should also look at the logic involved in the application getting the error message.

40: Explain the difference between $ORACLE_HOME and $ORACLE_BASE.
A : ORACLE_BASE is the root directory for oracle. ORACLE_HOME located beneath ORACLE_BASE is where the oracle products reside.


41: Explain the use of setting GLOBAL_NAMES equal to TRUE.
A : It ensure the use of consistent naming conventions for databases and links in a networked environment.

42: Explain the difference between a FUNCTION, PROCEDURE and PACKAGE.
A : They are all named PL/SQL blocks.
Function must return a value. Can be called inside a query.
Procedure may or may not return value.
Package is the collection of functions, procedures, variables which can be logically grouped together.

43: What background process refreshes materialized views?
A : Job Queue Process (CJQ)

44: How would you determine what sessions are connected and what resources they are waiting for?
A : v$session,v$session_wait

45: How would you force a log switch?
A : alter system switch logfile;

Oracle Application Interview Questions and Answers

page -1

1:  What is *.dbc file and what is location of dbc file ?
A:  dbc as name stands for is database connect descriptor file used to connect to database.
DBC file is quite important as whenever Java or any other program like forms want to connect to
database it uses DBC file.
This file by default located in $FND_TOP/secure directory also called as $FND_SECURE directory.
Typical entry in DBC file is:
GUEST_USER_PWD
APPS_JDBC_URL
DB_HOST
2: Whats is location of access_log file ?
A: access_log file by default is located in $IAS_ORACLE_HOME/ Apache/Apache/logs. Location of
this file is defined in httpd.conf by patameter CustomLog or TransferLog.

3: Where is appsweb.cfg or appsweb_$CONTEXT.cfg stored and why it’s used?
A: This file is defined by environment variable FORMS60_WEB_CONFIG_FILE This is usually in
directory $OA_HTML/bin on forms tier.
This file is used by any forms client session. When a user try to access forms, f60webmx picks up this
file and based on this configuration file creates a forms session to user/client.

4: How to compile an Oracle Reports file ?
A: Utility adrepgen is used to compile Reports. Synatx is given below
adrepgen userid=apps\ source = $PRODUCT_TOP\srw\filename.rdf dest=$PRODUCT_TOP\srw
\filename.rdf stype=rdffile dtype=rdffile logfile=x.log overwrite=yes batch=yes dunit=character

5: What is dev60cgi & f60cgi ?
A: cgi stands for Common Gateway Interface and these are Script Alias in Oracle Apps used to access
forms server. Usually Form Server access directly via http://hostname:port/dev60cgi/f60cgi
(http://hostname:port/dev60cgi/f60cgi)

6:  How to check number of forms users at any time ?
A: Forms Connections initiate f60webmx connections so you can use
ps -ef | grep f60webmx | wc –l

7: Can you clone from multi node system to single node system & vice versa ?
A: Yes.

8. Whats things you do to reduce patch timing?
A: * Merging patches via admrgpch
* Use various adpatch options like nocompiledb or nocompilejsp
* Use defaults file
* Staged APPL_TOP during upgrades
* Increase batch size (Might result into negative )

9: adident utility is used for what?
A: adident utility in oracle apps is used to find version of any file. AD Identification.

10: Why appsutil directory under Database ORACLE_HOME used for?
A:  All the template files, startup scripts , XML files are maintained here.

11: How to create User in Oracle Applications 11i? Can you delete a User?
A: New User can be created using security-->Define-->User menu. No,
user cannot be deleted but can be end-dated.

12: What is wdbsvr.app file used for? What's full path of this file? What's significance of this file?
A : The wdbsvr.app is used by mod_plsql component of Apache to connect to database. The File is located at $IAS_ORACLE_HOME/Apache/modplsql/cfg .

13: Where would i find .rf9 file, and what execatly it does?
A : These files are used during restart of patch in case of patch failure because of some reason.

14: How can you licence a product after installation?
A : By using adutility adlicmgr to licence product in Oracle Apps.

15: What is MRC? What you do to enable MRC in Apps?
A : MRC also called as "Multiple Reporting Currency" in oracle Apps.
Default you have currency in US Dollars but if your organization operating books are in other currency then you as apps dba need to enable MRC in Apps.

<<Page-2>> 

Oracle DBA Interview Questions and Answers -2

Page-2

16:  In linux, how can we change which databases are started during a reboot?
A:   Edit /etc/oratab

17:  When a user process fails, what Oracle background process will clean after it?
A :   PMON

18: How can you reduce the space of TEMP datafile?
A: Prior to Oracle 11g, you had to recreate the datafile. In Oracle 11g a new feature was introduced, and you can shrink the TEMP tablespace.

19: How can you view all the current users connected in your database in this moment?
A: SELECT COUNT(*) USERNAME FROM V$SESSION GROUP BY USERNAME;

20: Explain the differences between  shutdown immediate, shutdown normal, shutdown abort?
A:  SHUTDOWN NORMAL: It waits for all sessions to end, without allowing new connections.
      SHUTDOWN IMMEDIATE : Rollback current transactions and terminates every session.
      SHUTDOWN ABORT : Aborts all the sessions, leaving the database in an inconsistent state. It’s the   fastest method, but can lead to database corruption.

21: Is it possible to backup your database without the use of an RMAN database to store the catalog?
A:  Yes, but the catalog would be stored in the controlfile.

22: What command will you use to navigate through ASM files?
A: asmcmd

23: Which one is faster: D2ELETE or TRUNCATE?
A: TRUNCATE

24: Are passwords in oracle case sensitive?
A: Only since Oracle 11g.

25: Explain the difference between a hot backup and a cold backup and the benefits associated with each.
A : hot backup is basically taking a backup of the database while it is still up and running and it must be in   archive log mode. A cold backup is taking a backup of the database while it is shut down and does not require being in archive logmode. The benefit of taking a hot backup is that the database is still available for use while the backup is occurring and you can recover the database to any point in time. The benefit of taking a cold backup is that it is typically easier to administer the backup and recovery process. In addition, since you are taking cold backups the database does not require being in archive log mode and thus there will be a slight performance gain as the database is not cutting archive logs to disk.

26: You have just had to restore from backup and do not have any control files. How would you go about  bringing up this database?
A: I would create a text based backup control file, stipulating where on disk all the data files were and then issue the recover command with the using backup control file clause.

27: How do you switch from an init.ora file to a spfile?
A: Issue the create spfile from pfile command.

28: Explain the difference between a data block, an extent and a segment.
A: data block is the smallest unit of logical storage for a database object. As objects grow they take chunks of additional storage that are composed of contiguous data blocks. These groupings of contiguous data blocks are called extents. All the extents that an object takes when grouped together are considered the segment of the database object.

29: Compare and contrast TRUNCATE and DELETE for a table.
A : Both the truncate and delete command have the desired outcome of getting rid of all the rows in a table. The difference between the two is that the truncate command is a DDL operation and just moves the high water mark and produces few rollback data. The delete command, on the other hand, is a DML operation, which will produce rollback data and thus take longer to complete.

30: Give the reasoning behind using an index.
A : Faster access to data blocks in a table.

<<Page -3>> 

Oracle DBA Interview Questions and Answers-1

                                      Page-1

1:  Which are the default passwords of SYSTEM/SYS?
A: MANAGER / CHANGE_ON_INSTALL

2 : How can you execute a script file in SQLPLUS?
A: To execute a script file in SQLPlus, type @ and then the file name.

3: Where can you find official Oracle documentation?
A: tahiti.oracle.com

4: What is the address of the Official Oracle Support?
A: metalink.oracle.com or support.oracle.com

5: What file will you use to establish Oracle connections from a remote client?
A: tnsnames.ora

6: How can you check if the database is accepting connections?
A: lsnrctl status or lsnrctl services

7: Which log would you check if a database has a problem?
A: Alert log

8:  Name three clients to connect with Oracle, for example, SQL Developer:
A: SQL Developer, SQL-Plus, TOAD, dbvisualizer, PL/SQL Developer… There are several, but an
     experienced dba should know at least three clients.

9: How can you check the structure of a table from sqlplus?
A: DESCRIBE or DESC

10: What command will you start to run the installation of Oracle software on Linux?
A:  runInstaller


11: Explain the differences between PFILE and SPFILE
A: A PFILE is a Static, text file that initialices the database parameter in the moment that it’s started. If you
     want to modify parameters in PFILE, you have to restart the database.
    A SPFILE is a dynamic, binary file that allows you to overwrite parameters while the database is already
    started (with some exceptions)

12: In which Oracle version was Data Pump introduced?
A:  Oracle 10g

13: Say two examples of DML, two of DCL and two of DDL
A: DML: SELECT, INSERT, UPDATE, DELETE, MERGE, CALL, EXPLAIN PLAN, LOCK TABLE
     DDL: CREATE, ALTER, DROP, TRUNCATE, COMMENT, RENAME
     DCL: GRANT, REVOKE

14: You want to save the output of an Oracle script from sqlplus. How would you do it?
A: spool script_name.txt
     select * from your_oracle_operations;
     spool off;

15: How can you view all the users account in the database?
A: SELECT USERNAME FROM DBA_USERS;

<<< Page-2 >>> 

Creating an AWR (Automatic Workload Repository) Report

AWR Features

The AWR is used to collect performance statistics including:
Wait events used to identify performance problems.

Time model statistics indicating the amount of DB time associated with a process from the V$SESS_TIME_MODEL and V$SYS_TIME_MODEL views.
Active Session History (ASH) statistics from the V$ACTIVE_SESSION_HISTORY view.
Some system and session statistics from the V$SYSSTAT and V$SESSTAT views.
Object usage statistics.
Resource intensive SQL statements.

Workload Repository Views

The following workload repository views are available:

V$ACTIVE_SESSION_HISTORY - Displays the active session history (ASH) sampled every second.

V$METRIC - Displays metric information.

V$METRICNAME - Displays the metrics associated with each metric group.

V$METRIC_HISTORY - Displays historical metrics.

V$METRICGROUP - Displays all metrics groups.

DBA_HIST_ACTIVE_SESS_HISTORY - Displays the history contents of the active session history.

DBA_HIST_BASELINE - Displays baseline information.

DBA_HIST_DATABASE_INSTANCE - Displays database environment information.

DBA_HIST_SNAPSHOT - Displays snapshot information.

DBA_HIST_SQL_PLAN - Displays SQL execution plans.

DBA_HIST_WR_CONTROL - Displays AWR settings.


               Oracle provide two scripts to produce workload repository reports (awrrpt.sql and awrrpti.sql). They are similar in format to the statspack reports and give the option of HTML or plain text formats. The two reports give essential the same output but the awrrpti.sql allows you to select a single instance. The reports can be generated as follows.


@$ORACLE_HOME/rdbms/admin/awrrpt.sql
@$ORACLE_HOME/rdbms/admin/awrrpti.sql



          The scripts prompt you to enter the report format (html or text), the start snapshot id, the end snapshot id and the report filename. The resulting report can be opend in a browser or text editor accordingly.



SQL> @$ORACLE_HOME/rdbms/admin/awrrpt.sql

First, you need to specify whether you want an HTML or a text report.

Enter value for report_type: text

You can enter the number of days (n) will result in the most recent (n) days of snapshots being listed.
 Or you can press <return> without specifying a number it will lists all completed snapshots.


Enter value for num_days:

Listing all Completed Snapshots                                                                                             

 

        After the list displays, you are prompted for the beginning and ending snapshot Id for the workload repository report.

Enter value for begin_snap: 46

Enter value for end_snap: 47

Next, accept the default report name or enter a report name. The default name is accepted in the following example:

Enter value for report_name: test_awr_rpt

Using the report name test_awr_rpt

The workload repository report is generated.


awrrpt.sql
The awrrpt.sql SQL script generates an HTML or text report that displays statistics for a range of snapshot Ids.

awrrpti.sql
The awrrpti.sql SQL script generates an HTML or text report that displays statistics for a range of snapshot Ids on a specified database and instance.

awrsqrpt.sql
The awrsqrpt.sql SQL script generates an HTML or text report that displays statistics of a particular SQL statement for a range of snapshot Ids. Run this report to inspect or debug the performance of a SQL statement.

awrsqrpi.sql
The awrsqrpi.sql SQL script generates an HTML or text report that displays statistics of a particular SQL statement for a range of snapshot Ids on a specified database and instance.
Run this report to inspect or debug the performance of a SQL statement on a specific database and instance.

awrddrpt.sql
The awrddrpt.sql SQL script generates an HTML or text report that compares detailed performance attributes and configuration settings between two selected time periods.

awrddrpi.sql
The awrddrpi.sql SQL script generates an HTML or text report that compares detailed performance attributes and configuration settings between two selected time periods on a specific database and instance.