Friday, December 25, 2020

Function not available to this responsibility. Change responsibilities or contact your System Administrator Error in EBS

 Getting Function not available to this responsibility. Change responsibilities or contact your System Administrator Error while selecting responsibilities in EBS Home page.

Picture

Solution :

Step 1. Check if the issue only happens on custom responsibility.

If so, review your Custom responsibility and corresponding menus  against the seeded menu.
System Administrator (R) > Security (N) > Responsibility: Query your Responsibility; The menu used will display.

You can query the same on the seeded responsibility and compare.


Step 2. The issue also occurs on the seeded responsibility.
 
Is it occurring on All functions or just one particular function?

     2. a. All/Many functions:  

            Set the value of the following profile options :
              - Hide Diagnostics Menu entry (set to No )
              - "FND:Diagnostics" (set to Yes ).
              - Utilities: Diagnostics ( set to Yes ).

           Ref:(Note 947656.1 Note 1200743.1) .

Bounce mid-tier with clear cache as necessary, log out and test again.

     2. b. The issue only happen to one function:  (Case of "Email Quote")
         e.g. Service Contracts (R) : Launch Contract (N) : Open a contract :

             - Tools (N) > Email Quote
             OR - Summary (T) >  Renewal  sub-tab : Email Quote (B)

Error: "Function not available to this responsibility. Change responsibilities or contact your System Administrator."  

         i. lookout for the parameters attached to the button during the process.

          In this particular case, On an environment where the issue does not occur,  the button opens a page:
 
http......./OA_HTML/RF.jsp?function_id=1032467&resp_id=21708&resp_appl_id=515&security_group_id=0&lang_code=US&params=Sc...
 

               Here are the parameters
                          function_id=1032467  ----->this is the function to check
                          resp_id=21708
                          resp_appl_id=515
                          security_group_id=0

An FND log will also show these if you do not have a working environment.

         ii.Track the Function

              - Track confirm the responsibility:

 Run the following SQL: select * from FND_RESPONSIBILITY_TL where                               responsibility_id ='21708'  -- Service Contracts Manager, Vision Enterprises

              - Query the Responsibility:

 There are chances that the function has not been granted or the menus are not                               recompiled.

System Administrator (R) Security (N) > Responsibility: Query your Responsibility

The menu used will display

           - Query the Menu            
                   Menu (N) : Query the Menu  OKS_MANAGER_TOP_LEVEL_MENU_R12  (This is the OKS   Manager Menu  for R12 Service Contract)

          - This Function belongs to a Menu or sub-menu of OKS_MANAGER_TOP_LEVEL_MENU_R12.

             Under  the Tree display (B) scan for your function_id  OR in this case,

                  look for Contract Administration Menu>  the Sub-Menu name is:                                                   OKS_REN_WB_PAGE_MENU > The function name is  OKS_EMAIL_FUNC (Email customer                   Page)

You can also navigate as follow:

OKS_MANAGER_TOPLEVEL_MENU_R12
> OKS_CONTRACTS_ADMIN_R12
> Submenu: OKS Renewal Workbench Home Menu
> Function: Email Customer Page

      2. c. Grant the function to the sub-menu,  Save. Retest.

Step 1 and 2 did not resolve my issue when I Recompiled the Menu then it worked for me .

Step 3.  (Optional) Reset the Menu.

If Steps 1 and 2 did not resolve the issue then,

- Recompile the Menu,
- Bounce Apache with Clear Cache option,
- Log out and retest. 
                                                                        OR 

Responsibility: System Administrator
Navigation: Profile > System


1. Set the following two profiles at User level only for the user attempting the trace:

Utilities:Diagnostics
Utilities:SQL Trace


To access Examine without a password, have your DBA set profile Utilities: Diagnostics = Yes To generate a trace file without supplying the APPS password, have your DBA set profile Utilities:SQL Trace = Yes Log back in as that user and attempt to set the trace
                                                                            OR
2. Include submenu 'FND Diagnostics Support' to the responsibilities menu.
User might have  attempt to set the trace.



Doc ID 1498737.1 for your reference .

Saturday, December 5, 2020

Cleanup in STANDARD mode is failing in EBS

 Error Info :

Running cleanup in STANDARD mode.
Log: @ADZDSHOWLOG.sql "2020/11/25 14:14:50"

Statement Handler Error Code: 600
Statement Handler Error Message: ORA-00600: internal error code, arguments: [15239], [], [], [], [], [], [], [], [], [], [], []

ORA-06512: at "SYS.DBMS_SQL", line 1163

ORA-06512: at "SYS.AD_ZD_SYS", line 263

ORA-06512: at "APPS.AD_ZD", line 447

ORA-06512: at "APPS.AD_ZD", line 960

ORA-06512: at line 3 (DBD ERROR: OCIStmtExecute)

Reference of statement-handler is valid, not attempting database reconnect.

Failed to execute SQL statement:
begin

execute immediate 'alter session set DDL_LOCK_TIMEOUT = 600';
ad_zd.cleanup('STANDARD');

end;

Error Message:

ORA-00600: internal error code, arguments: [15239], [], [], [], [], [], [], [], [], [], [], []

ORA-06512: at "SYS.DBMS_SQL", line 1163

ORA-06512: at "SYS.AD_ZD_SYS", line 263

ORA-06512: at "APPS.AD_ZD", line 447

ORA-06512: at "APPS.AD_ZD", line 960

ORA-06512: at line 3 (DBD ERROR: OCIStmtEx
Diagnosis: Select the 'Next' button to proceed with the creation of the SR.

Solution: 

Steps to patch the data dictionary (trigger$):

1) It is recommended to take a full backup or enable flashback in case it is needed to restore the database to a point in time before modifying the data dictionary. ----> You have already taken the backup and now you are doing the patching on the Non-Prod (Test) server.

2) SHUTDOWN IMMEDIATE and STARTUP UPGRADE.

SQL> shutdown immediate;

SQL> startup upgrade;

3) Create a new user called PATCH_USER, the user# assigned will be used to update TRIGGER$:

SQL> create user PATCH_USER identified by <password>;

4) Update TRIGGER$ with the user id of PATCH_USER

SQL> update trigger$ set baseobject = (select user# from sys.user$ where name = 'PATCH_USER') where obj# = &obj;

In the example above the obj value to be provided as input is OBJ#= 7132051 & 7132052.

SQL> update trigger$ set baseobject = (select user# from sys.user$ where name = 'PATCH_USER') where obj# = 7132051;

SQL> update trigger$ set baseobject = (select user# from sys.user$ where name = 'PATCH_USER') where obj# = 7132052;

5) COMMIT;

6) SHUTDOWN ABORT -- It must be shutdown abort
7) STARTUP

8) Drop the trigger:

SQL> drop trigger SCOTT.LOGON_TRG; ----> Here mention the Trigger which is causing an issue in your environment.  Verify alert log and trace log file to get the Trigger name at the time of error .

9) Drop user created in step 3:
SQL> drop user PATCH_USER;

Retest the issue now ,It will resolve .

Friday, November 27, 2020

Error 404 --Not Found while Opening EBS Forms

 Issue :

We are unable to access the Oracle Applications Forms after cloning of DEV Environment from PRODUCTION. Error Screenshot as below.

Picture

Solution:​

1. Verify HOSTNAME in the file /etc/sysconfig/network and Update if accordingly on both the nodes.

2. Generated the Product JAR Files from adadmin.

3. Reloaded the jar files to database from adadmin.

4. Compiled the apps schema from adadmin.

5. Stopped the Application Services.

6. Navigate to $OA_HTML/cabo/images/cache and take a backup of contents of the folder cache and then clear all the contents inside that cache folder.

7. Navigate to $OA_HTML/cabo/styles/cache and take a backup of contents of the folder cache and then clear all the contents inside that cache folder.

8. Navigate to $FND_TOP/patch/115/bin, compiled the jar files -> perl ojspCompile.pl --compile --flush -p 2

9. Start the Application Services

10. Cleared the browser cache and post login, we are able to access the Oracle Applications forms.


                                Hope you found this helpful ,Thank you 

Sunday, October 25, 2020

1.3 Oracle E-Business Suite Release 12.2 Technology Stack Documentation Roadmap

 In This Document



The various books and My Oracle Support knowledge documents listed herein are written and maintained by EBS Development, and categorized as follows.


Section 1: Release Notes, Readmes, and Related Documents

1.1 All 12.2 Releases

1.2 AD-TXK Updates

1.2.1 Application Instructions

  • Document 1583092.1, Oracle E-Business Suite Release 12.2: Suite-Wide Rollup and AD/TXK                                              Delta Information
  • Document 1617461.1, Applying the Latest AD and TXK Release Update Packs to Oracle E-                                                Business Suite Release 12.2
  • Document 1903052.1, Applying A Non-Current Version of the AD and TXK Release Update Packs                                        to Oracle E-Business Suite Release 12.2
1.2.2 Release Notes

  • Document 2649885.1, Oracle E-Business Suite Applications DBA and Technology Stack Release                                        Notes for R12.AD.C.Delta.12 and R12.TXK.C.Delta.12
  • Document 2527995.1, Oracle E-Business Suite Applications DBA and Technology Stack Release                                        Notes for R12.AD.C.Delta.11 and R12.TXK.C.Delta.11
  • Document 2295390.1, Oracle E-Business Suite Applications DBA and Technology Stack Release                                        Notes for R12.AD.C.Delta.10 and R12.TXK.C.Delta.10
  • Document 2233485.1, Oracle E-Business Suite Applications DBA and Technology Stack Release                                        Notes for R12.AD.C.Delta.9 and R12.TXK.C.Delta.9
  • Document 2159750.1, Oracle E-Business Suite Applications DBA and Technology Stack Release                                        Notes for R12.AD.C.Delta.8 and R12.TXK.C.Delta.8
  • Document 2033780.1, Oracle E-Business Suite Applications DBA and Technology Stack Release                                        Notes for R12.AD.C.Delta.7 and R12.TXK.C.Delta.7
  • Document 1983782.1, Oracle E-Business Suite Applications DBA and Technology Stack Release                                        Notes for R12.AD.C.Delta.6 and R12.TXK.C.Delta.6
  • Document 1934471.1, Oracle E-Business Suite Release 12.2: Now Available - Essential Bundle F                                     Fixes for AD Delta 5 and TXK Delta 5
  • Document 1644660.1, Oracle E-Business Suite Technology Stack and Applications DBA Release                                        Notes for R12.TXK.C.Delta.5 and R12.AD.C.Delta.5
  • Document 1674784.1, Oracle E-Business Suite Release 12.2: Now Available - Essential                                                      Consolidated Rollup Patches for AD Delta 4 and TXK Delta 4
  • Document 1624423.1, Oracle E-Business Suite Technology Stack and Applications DBA Release                                        Notes for R12.TXK.C.Delta.4 and R12.AD.C.Delta.4

1.3 Applications Technology Product Family Updates

1.4 Release 12.2.10
  • Document 2666934.1, Oracle E-Business Suite Release 12.2.10 Readme
  • Document 2693485.1, Oracle E-Business Suite Applications DBA and Technology Stack Release                                        Notes for Release 12.2.10
1.5 Release 12.2.9

  • Document 2495027.1, Oracle E-Business Suite Release 12.2.9 Readme
  • Document 2559770.1, Oracle E-Business Suite Applications DBA and Technology Stack Release                                        Notes for Release 12.2.9
1.6 Release 12.2.8

  • Document 2393248.1, Oracle E-Business Suite Release 12.2.8 Readme
  • Document 2292750.1, Oracle E-Business Suite Applications DBA and Technology Stack Release                                        Notes for Release 12.2.7 and 12.2.8
1.7 Release 12.2.7

  • Document 2230783.1, Oracle E-Business Suite Release 12.2.7 Readme
  • Document 2292750.1, Oracle E-Business Suite Applications DBA and Technology Stack Release                                        Notes for Release 12.2.7 and 12.2.8
1.8 Release 12.2.6

  • Document 2114016.1, Oracle E-Business Suite Release 12.2.6 Readme
  • Document 2149555.1, Oracle E-Business Suite Applications DBA and Technology Stack Release                                        Notes for Release 12.2.6
1.9 Release 12.2.5

  • Document 1983050.1, Oracle E-Business Suite Release 12.2.5 Readme
  • Document 2050998.1, Oracle E-Business Suite Applications DBA and Technology Stack Release                                        Notes for Release 12.2.5
1.10 Release 12.2.4

  • Document 1617458.1, Oracle E-Business Suite Release 12.2.4 Readme
  • Document 1911660.1, Oracle E-Business Suite Technology Stack and Applications DBA Release                                        Notes for Release 12.2.4
1.11 Release 12.2.3

  • Document 1586214.1, Oracle E-Business Suite Release 12.2.3 Readme
  • Document 1606170.1, Oracle E-Business Suite Technology Stack and Applications DBA Release                                        Notes for Release 12.2.3

Section 2: Installation and Configuration

2.1 Administration and Management

  • Document 1378579.1, Troubleshooting Rapid Install for E-Business Suite Release 12.2
  • Document 1375769.1, Sharing The Application Tier File System in Oracle E-Business Suite                                                  Release 12.2
  • Document 1375686.1, Using Load-Balancers with Oracle E-Business Suite Release 12.2
  • Document 393861.1,   Globalization Guide for Oracle E-Business Suite Release 12.0 to 12.2
  • Document 393320.1,   Internationalization Update Notes for Release 12.2
  • Document 437878.1,   Upgrading OracleAS 10g Forms and Reports in Oracle E-Business Suite                                          Release 12
  • Document 807393.1,   Hijrah and Thai Calendar Support in Oracle E-Business Suite R12.1 and 12.2
  • Document 1581549.1, Best Practices for Minimizing Oracle E-Business Suite Release 12.2.n                                                Upgrade Downtime
  • Document 1583752.1  Express Diagnosis of Oracle E-Business Suite Release 12.2.n Upgrade                                              Performance Issues
  • Document 1597531.1, Oracle Applications Release 12.2: Upgrade Sizing and Best Practices
  • Document 1906545.1, Resolving Domain Deployment Issues When Installing Oracle E-Business                                        Suite Release                                        12.2
  • Document 567015.1,   Using IPv6 with Oracle E-Business Suite Releases 11i and 12
  • Document 1077709.1, Using Lightweight MLS With Oracle E-Business Suite Release 12.1.3 and                                          Higher
  • Document 563019.1,   Complying with Daylight Saving Time (DST) and Time Zone Rule Changes                                        in  E-Business  Suite Release 12
  • Document 384241.1,   Using Forms Socket Mode with Oracle E-Business Suite Release 12
  • Document 414992.1,   Using Cygwin to Maintain Oracle E-Business Suite Release 12 on Windows
  • Document 1460742.1, Deploying Oracle E-Business Suite on Oracle Exalogic Elastic Cloud and                                          Oracle Exadata Database Machine
  • Document 2025309.1, Using Oracle Database In-Memory with Oracle E-Business Suite
  • Document 2045552.1,  Getting Started with Oracle Application Management Pack (AMP) for                                               Oracle E-Business Suite Release 13.1.1.1.0
  • Document 2676355.1,  Getting Started with Oracle Application Management Pack for Oracle E-                                         Business Suite (AMP), Release 13.4.1.0.0
  • Document 2528000.1,  Oracle E-Business Suite Performance Best Practices
  • Document 2736939.1,  Oracle E-Business Suite Electronic Technical Reference Manual (eTRM),                                             Release 12.1 and 12.2
  • Document 2749775.1,  EBS 12.2 Upgrade Readiness Checker - Database Tier (EURC-DT)


2.2 Installation and Upgrade

  • Document 1330701.1, Oracle E-Business Suite Installation and Upgrade Notes Release 12 (12.2)                                      for Linux x86-64
  • Document 1330702.1, Oracle E-Business Suite Installation and Upgrade Notes Release 12 (12.2)                                      for Oracle Solaris on SPARC (64-bit)

    • This document applies to on-premises environments only
  • Document 1330703.1, Oracle E-Business Suite Installation and Upgrade Notes Release 12 (12.2) f                                    or IBM AIX on Power Systems (64-bit)

    • This document applies to on-premises environments only
  • Document 1330704.1, Oracle E-Business Suite Installation and Upgrade Notes Release 12 (12.2)                                    or HP-UX Itanium

    • This document applies to on-premises environments only
  • Document 1330706.1, Oracle E-Business Suite Installation and Upgrade Notes Release 12 (12.2) f                                   or Microsoft Windows x64 (64-bit)
    • This document applies to on-premises environments only



2.3 Cloning

  • Document 1383621.1, Cloning Oracle E-Business Suite Release 12.2 with Rapid Clone
  • Document 1614793.1, Cloning Oracle E-Business Suite Release 12.2 Environments Integrated with Oracle Access Manager 11gR2 (11.1.2) and Oracle E-Business Suite AccessGate
  • Document 1679270.1, Cloning Oracle E-Business Suite Release 12.2 RAC Enabled Systems with Rapid Clone
  • Document 2047809.1, Cloning Oracle E-Business Suite Release 12.2 (AD-TXK Delta 6 or Lower Codelevel) with Rapid Clone
  • Document 2552208.1. Cloning Oracle E-Business Suite Release 12.2 with Multitenant Database using Rapid Clone


Section 3: Patching

3.1 Administration and Management

  • Document 1267768.1, Oracle E-Business Suite Releases 11i and 12.x: Required Updates for Patch Wizard
  • Document 1531121.1, Using the Online Patching Readiness Report in Oracle E-Business Suite Release 12.2
  • Document 1677498.1, How to Restore An Abandoned Node in Oracle E-Business Suite Release 12.2
  • Document 244040.1, Oracle E-Business Suite Recommended Performance Patches
  • Document 1583902.1, Oracle E-Business Suite Release 12.2: Online Patching FAQ
  • Document 1584097.1, Oracle E-Business Suite Release 12.2: Backup and Recovery Guidelines For Online Patching Cutover
  • Document 1594274.1, Oracle E-Business Suite Release 12.2: Consolidated List of Patches and Technology Bug Fixes
  • Document 1678355.1, Oracle E-Business Suite Release 12.2: Validations Performed By the adop Online Patching Utility
  • Document 1901242.1, Script to Diagnose adop and Other AD-TXK Issues in Oracle E-Business Suite Release 12.2
  • Document 2749774.1, EBS Technology Patch Automation Tool for Application Tier (ETPAT-AT)

Thursday, October 22, 2020

adident -AD Identification

 We can know the version and translation level of Oracle Application file in Three ways,


   1. AD utility - adident.
   2.Strings Utility.
   3.By using SQL query.



 1.AD utility - adident.

   Executable -adident .
              
To find out a file version, oracle have provided ‘ADIDENT’ utility (AD Identification). This is  an oracle  apps based utility.

    Executable Location : $AD_TOP/bin

      Action :
           
         $ adident Header <Filename> 
          Ex: adident Header adconfig.sh


2.Strings Utility.


       Strings is a UNIX based utility which is used to  print the strings of printable characters in files. We can use this utility to extract header information from the string content of a file.

Action:

   strings -a <file_name>|grep Header
     Eg:- strings -a OEXWFASG.rdf|grep Header


These two methods can be used to find out file versions for oracle seeded files like rfd, fmx, fmb, xml, txt, pls, etc.

Note:- Letter ‘H’ must be capital in key word ‘Header’


 
3.By using SQL query.

You can also use the following SQL query which shows the file version number and also the patch number which brought the file to the current version


select sub.filename, sub.version , sub.last_update_date, sub.app_short_name, sub.subdir
from (
select adf.filename filename,
afv.version version,afv.LAST_UPDATE_DATE,adf.app_short_name, adf.subdir, 
rank()over(partition by adf.filename
order by afv.version_segment1 desc,
afv.version_segment2 desc,afv.version_segment3 desc,
afv.version_segment4 desc,afv.version_segment5 desc,
afv.version_segment6 desc,afv.version_segment7 desc,
afv.version_segment8 desc,afv.version_segment9 desc,
afv.version_segment10 desc,
afv.translation_level desc) as rank1 
from ad_file_versions afv,
(
select filename, app_short_name, subdir, file_id
from ad_files
where upper(filename) like upper('%&filename%')
) adf
where adf.file_id = afv.file_id
) sub
where rank1 = 1
order by 1;


Saturday, September 26, 2020

Oracle Database Architecture

      A database is a collection of data treated as a unit. The purpose of a database is to store and retrieve related information. A database server is the key to information management. In general, a  server reliably manages a large amount of data in a multiuser environment so that many users can concurrently access the same data. A database server also prevents unauthorized access and provides efficient solutions for failure recovery.

   Oracle Database is the first database designed for enterprise grid computing, the most flexible and cost effective way to manage information and applications. Enterprise grid computing creates large pools of industry-standard, modular storage and servers. With this architecture, each new system can be rapidly provisioned from the pool of components. There is no need for peak workloads, because capacity can be easily added or reallocated from the resource pools as needed.

As the DBA perspective Oracle is a Two tier architecture. As the Applications point of view Oracle is a Three Tier Architecture .

Example: Gmail can not open by sql prompt, we have to open through application .That's why it is 3 -Tier Architecture .


An Oracle Database consists of a Instance and Database.

An instance, or database instance, is the combination of memory and processes that are a part of a running installation and a database is a set of files that store data.

The database has logical structures and physical structures. Because the physical and logical structures are separate, the physical storage of data can be managed without affecting the access to logical storage structures.



Oracle database software is designed to support the architecture of instances and databases.


The following pictures illustrates the Oracle Database server architecture.



Overview :
​​
Picture
Complete Architecture of Oracle Database Server.
Picture

As we discussed ,An Oracle Database consists of a Instance and Database.


1. Instance 


Database Instance is an interface between client applications (users) and the database. An Oracle instance consists of Two main parts:



1.1  Oracle Memory Structures.
1.2  Oracle Background Processes.




1.1 Oracle Memory Structure .


We Will Focus first on the memory components of the Oracle instance. This set of memory components represents a "living" version of Oracle that is available only when the instance is running. There are two basic memory structures on the Oracle instance.

  • System Global Area or SGA
  • Program Global Area or PGA.

This discussion will explain the components of the SGA and the PGA, and also cover the factors that determine the storage of information about users connected to the Oracle instance.

  • System Global Area or SGA.

The SGA consists of  different items, listed here.

The buffer cache
The shared pool
The redo log buffer


And we have other memory components Like 

Large Pool
Streams Poll
Java Pool



The Buffer Cache :

 The largest component of the SGA is usually the database buffer cache, which is the part of the SGA that holds copies of blocks of data read from the Oracle datafiles on disk. The size of the database buffer cache is controlled by the INIT.ORA parameter DB_BLOCK_BUFFERS, which specifies the number of database blocks that will be contained in the database buffer cache. Since this is expressed as database blocks, the size of the database buffer cache is the value of DB_BLOCK_BUFFERS multiplied by the DB_BLOCK_SIZE. For example, if a database has been created with a DB_BLOCK_SIZE of 8192 (8K) and DB_BLOCK_BUFFERS is set to 1000, then the database buffer cache component of the SGA would be 8192 × 1000 or 8,192,000 bytes.

 When a user process needs data from the database, Oracle first checks to see if the required block is already in the database buffer cache. If it is, it is retrieved from the cache and a disk I/O operation is avoided. Oracle maintains an LRU (least recently used) list of blocks in the cache; when a block is read, its identifier is moved to the end of the list, making it the last block to be purged from the cache. One exception to this rule is that blocks read as the result of a full table scan are placed at the top of the LRU list; the assumption is that they are unlikely to be requested again soon. This behavior may be overridden by using the CACHE clause when creating or updating a table.



The Shared Pool :

The Shared Pool contains the Library Cache and the Dictionary Cache as well as a few other items, which are not in the scope of this section.

The Library Cache holds all users’ SQL statements, Functions, Procedures, and Packages. It stores the parsed SQL statement with its execution plan for reuse.
The Dictionary Cache, sometimes also referred to as the Row Cache, holds the Oracle repository data information such as tables, indexes, and columns definitions, usernames, passwords, synonyms, views, procedures, functions, packages, and privileges information.

The Redo Log Buffer:

The Redo Log Buffer holds users’ entries such as INSERT, UPDATE, DELETE, etc (DML) and CREATE TABLE, DROP TABLE (DDL).
The Redo Entries are information that will be used to reconstruct or redo, changes made to a database. The Log Writer writes the entries into the Online Redo Log files when a COMMIT occurs, every 3 seconds, or when one-third of the Redo Log Buffer is full.
That will guarantee a database recovery to a point of failure if an Oracle database failure occurred.

The Large Pool :

Optional area that provides large memory allocations for certain large processes, such as Oracle backup and recovery operations, and I/O server processes

The Java Pool:

Used for all session-specific Java code and data in the Java Virtual Machine (JVM).

The Streams Pool: 

Used by Oracle Streams to store information required by capture and apply.


Program Global Area or PGA.

The PGA is an area in memory that helps user processes execute, such as bind variable information, sort areas, and other aspects of cursor handling. From the prior discussion of the shared pool, the DBA should know that the database already stores parse trees for recently executed SQL statements in a shared area called the library cache. So, why do the users need their own area to execute? The reason users need their own area in memory to execute is that, even though the parse information for SQL or PL/SQL may already be available, the values that the user wants to execute the search or update upon cannot be shared. The PGA is used to store real values in place of bind variables for executing SQL statements.


1.2  Oracle Background Processes.

A good deal of the discussion around users thus far speaks of processes--user processes doing this or that. In any Oracle instance, there will be user processes accessing information. . Likewise, the Oracle instance will be doing some things behind the scenes, using background processes. There are several background processes in the Oracle instance. It was mentioned in the discussion of the SGA that no user process ever interfaces directly with I/O. This setup is allowed because the Oracle instance has its own background processes that handle everything from writing changed data blocks onto disk to securing locks on remote databases for record changes in situations where the Oracle instance is set up to run in a distributed environment. The following list presents each background process and its role in the Oracle instance.

DBWR 

The database writer process. This background process handles all data block writes to disk. It works in conjunction with the Oracle database buffer cache memory structure. It prevents users from ever accessing a disk to perform a data change such as updateinsert, or delete.

LGWR 

The log writer process. This background process handles the writing of redo log entries from the redo log buffer to online redo log files on disk. This process also writes the log sequence number of the current online redo log to the datafile headers and to the control file. Finally, LGWR handles initiating the process of clearing the dirty buffer write queue. At various times, depending on database configuration, those updated blocks are written to disk by DBWR. These events are called checkpoints. LGWR handles telling DBWR to write the changes.

SMON

The system monitor process. The usage and function of this Oracle background process is twofold. First, in the event of an instance failure—when the memory structures and processes that comprise the Oracle instance cannot continue to run—the SMON process handles recovery from that instance failure. Second, the SMON process handles disk space management issues on the database by taking smaller fragments of space and "coalescing" them, or piecing them together.

PMON

The process monitor process. PMON watches the user processes on the database to make sure that they work correctly. If for any reason a user process fails during its connection to Oracle, PMON will clean up the remnants of its activities and make sure that any changes it may have made to the system are "rolled back," or backed out of the database and reverted to their original form.

RECO 

(optional) The recoverer process. In Oracle databases using the distributed option, this background process handles the resolution of distributed transactions against the database.

ARCH

(optional) The archiver process. In Oracle databases that archive their online redo logs, the ARCH process handles automatically moving a copy of the online redo log to a log archive destination.

CKPT

(optional) The checkpoint process. In high-activity databases, CKPT can be used to handle writing log sequence numbers to the datafile headers and control file, alleviating LGWR of that responsibility.

2. Database 


Database is a combination of Physical and Logical structure .
he following section briefly describes the  physical  and logical storage structure of an Oracle Database.


​Physical storage structures

The physical storage structures are simply files that store data. When you execute a CREATE DATABASE statement to create a new database,

Oracle creates the following files:


  • Data files:
  • data files contain real data, e.g., sales order and customer data. The data of logical database structures such as tables and indexes are physically stored in the data files.
  • Control files:
  • every database has a control file that contains metadata. The metadata describes the physical structure of the database including the database name and the locations of data files.
  • Online redo log files:
  • every database has an online redo log that consists of two or more online redo log files. An online redo log is made up of redo entries that record all changes made to the data.
Besides these files, an Oracle database includes other important files such as parameter files, network files, backup files, and archived redo log files for backup and recovery.

Picture
Logical Storage Structures


Oracle Database uses a logical storage structure for fine-grained control of disk space usage. The following are logical storage structures in an Oracle Database:

  • Data blocks:
  • a data block corresponds to a number of bytes on the disk. Oracle stores data in data blocks. Data blocks are also referred to as logical blocks, Oracle blocks or pages.
  • Extents:
  • An extent is a specific number of logically contiguous data blocks used to store the particular type of information.
  • Segments:
  • A segment is a set of extents allocated for storing database objects, e.g., a table or an index.
  • Tablespaces:
  • A database is divided into logical storage units called tablespaces. A tablespace is a logical container for a segment. Each tablespace consists of at least one data file.
The following picture illustrates segments, extents and data blocks within a tablespace:

Picture
And the next figure shows the relationship between logical and physical storage structures:
Picture
​Now, you should have a good overview of the Oracle Database architecture and its components.

ORACLE DATABASE CONTENT

ORACLE DATABASE 11gR2 & 12C  CONTENT Pre-Requisite: UNIX, SQL Basics Introduction to Oracle Database §   Introduction of Database ...