Category: Uncategorized

Basic Interview Questions

1. What is Oracle Database Architecture?

• Follow-up: Can you briefly explain the components involved?

2. What are the main components of the Oracle Database Architecture?

• Hint: Mention SGA, background processes, data files, control files, etc.

3. What is the System Global Area (SGA)?

• Follow-up: What are the different parts of SGA?

4. What are background processes in Oracle?

• Can you name a few important background processes?

5. What is the role of the Process Monitor (PMON) and System Monitor (SMON)?

6. What is a tablespace in Oracle?

• Follow-up: What is the difference between SYSTEM and SYSAUX tablespaces?

7. What is a control file and why is it important?

8. What are data files and how are they related to tablespaces?

9. What is the difference between a physical and a logical structure in Oracle?

10. What is the role of the redo log files in Oracle?

• Follow-up: What happens if the redo log is full?

---

🔹 Intermediate-Level Questions

11. Explain the difference between PGA and SGA.

12. What is an Oracle instance?

• Follow-up: How is it different from a database?

13. What happens during the Oracle database startup process?

• Stages: NOMOUNT, MOUNT, OPEN

14. What is the use of the control file during database startup?

15. What is the purpose of the listener in Oracle?

16. What is the difference between a dedicated and shared server architecture in Oracle?

17. What are data dictionary views?

• Example: USER_TABLES, ALL_TABLES, DBA_TABLES

---

🔹 Scenario-Based Questions

18. If a user reports slow performance, which components of Oracle architecture would you investigate?

19. How would you check if the Oracle instance is up and running?

20. If the SMON process fails, what impact does it have on the database?

The Script below gives the details of all Concurrent Programs submitted on a given date


==
select
f.request_id ,
pt.user_concurrent_program_name user_conc_program_name,
to_char(f.actual_start_date,’DD-MON-YY HH24:MI:SS’) start_on,
to_char(f.actual_completion_date,’DD-MON-YY HH24:MI:SS’) end_on,
floor(((f.actual_completion_date-f.actual_start_date)
*24*60*60)/3600)
|| ‘ HOURS ‘ ||
floor((((f.actual_completion_date-f.actual_start_date)
*24*60*60) –
floor(((f.actual_completion_date-f.actual_start_date)
*24*60*60)/3600)*3600)/60)
|| ‘ MINUTES ‘ ||
round((((f.actual_completion_date-f.actual_start_date)
*24*60*60) –
floor(((f.actual_completion_date-f.actual_start_date)
*24*60*60)/3600)*3600 –
(floor((((f.actual_completion_date-f.actual_start_date)
*24*60*60) –
floor(((f.actual_completion_date-f.actual_start_date)
*24*60*60)/3600)*3600)/60)*60) ))
|| ‘ SECS ‘ time_difference,
p.concurrent_program_name concurrent_program_name,
decode(f.phase_code,’R’,’Running’,’C’,’Complete’,f.phase_code) Phase,
f.status_code
from  apps.fnd_concurrent_programs p,
apps.fnd_concurrent_programs_tl pt,
apps.fnd_concurrent_requests f
where f.concurrent_program_id = p.concurrent_program_id
and f.program_application_id = p.application_id
and f.concurrent_program_id = pt.concurrent_program_id
and f.program_application_id = pt.application_id
AND pt.language = USERENV(‘Lang’)
and f.actual_start_date like ’24-JUN-15%’
order by
f.actual_start_date desc;

Check Used Space on  a Disk/ Mount Point
———————
The du command summarizes your disk usage. The sort command sorts the files by size. The command shown below sorts from smallest to largest all files in the current directory and all directories contained below the starting directory. Because the sort starts with the smallest file, the end of the list shows the largest files.

Deleting a large file is usually more efficient than deleting many small files. Often the largest files are core files or cache files, both of which you can delete.

du -h –max-depth=1
du -a | sort -k 1n,1|tail -30
du -sk * | sort -n | tail

Files more than 5mb
——————-

find . -mount -size +50000000c -print | ls -lh
find . -size +100000000c -print
find . -size +50000000c -print | xargs ls -l
find . -size +10000000c -print
find . -size +10000 -print

Files more than 30 day old.
—————————

find . -name “*.aud” -mtime +7

find . -name *.req -mtime +30|xargs gzip

find . -name “TEST01_*.out” -mtime +2 |xargs ls -l

find . -name “*.log” -mtime -1|xargs ls -l

Files modified in the last 6hrs ago
————————————
find . -name “*.log” -mmin 360|xargs ls -l
find . -mmin 360|xargs ls -l

find . -mtime +1|xargs ls -l

?+++++++++++++++++++++++++++++++++++++++++++++++++
find . -size +30000000c -xdev -exec ls -l {} ;

?+++++++++++++++++++++++++++++++++++++++++++++++++

Issue:

All the custom concurrent requests failing to run.
Logfile shows the following error:

APP-FND-00362: Routine afpbep cannot execute request &REQUEST for program &PROGRAM,
because the environment variable &BASEPATH is not set for the application to which
the concurrent program executable &EXECUTABLE belongs.

Cause: BASEPATH dir not set as environment variable.

Solution:

Find the application for which concurrent program is defined..

Go to Application Developer–> Concurrent Program –> Define
and query for the concurrent request(which is failing)

(For e.g. If you get the Application : Business Online)

Now go to Application Developer–>Register Application

Query for the Application to get BASEPATH.

(e.g XX_TOP is the basedirectory for Custom Application)

At the application tier check if XX_TOP is not set..
$echo $XX_TOP

Add the env variable XX_TOP=/../../..
 to applTop env or custom env and restart concurrent manager

Resubmit the request which have failed.