Understanding the EBS_SYSTEM Schema in Oracle E-Business Suite R12

EBS_SYSTEM Schema in Oracle E-Business Suite R12

Introduction

Oracle E-Business Suite (EBS) R12 has introduced several innovations to improve system performance and availability. One of the critical components in this version is the EBS_SYSTEM schema, which plays a pivotal role in the online patching process. In this blog, we'll explore what the EBS_SYSTEM schema is, its functions, and why it's essential for maintaining an efficient and secure EBS environment.

What is the EBS_SYSTEM Schema?

The EBS_SYSTEM schema is a dedicated database user introduced in EBS R12.2 to support the online patching functionality. It isolates patching operations from the main application users, ensuring that patches can be applied with minimal downtime.

Key Roles and Responsibilities

1. Online Patching (ADOP)

The primary role of the EBS_SYSTEM schema is to manage and execute online patching processes. This includes creating and managing patch editions, synchronizing data between editions, and ensuring that changes are applied without affecting the live environment.

2. Security and Isolation

By using a separate schema for patching operations, EBS_SYSTEM enhances the security and stability of the EBS environment. This segregation ensures that only authorized operations can be performed during the patching process.

3. Patch Edition Management

The EBS_SYSTEM schema is responsible for handling patch editions. When a patch cycle is initiated, EBS_SYSTEM manages the creation of a new patch edition and synchronizes it with the running edition.

4. Data Synchronization

It ensures that all data changes made in the patch edition are correctly propagated to the run edition during the finalization phase, maintaining data consistency and integrity.

5. Object Editioning

EBS_SYSTEM manages editioned objects, allowing different definitions of the same object in different editions. This capability is crucial for applying patches without disrupting the current production environment.

How the EBS_SYSTEM Schema Works

Prepare Phase

During the adop phase=prepare, the EBS_SYSTEM schema sets up the patch edition and synchronizes it with the run edition, preparing the environment for patch application.

Apply Phase

In the adop phase=apply, the schema applies the patches to the patch edition. This ensures that all changes are made in a controlled environment, separate from the live production system.

Finalize and Cutover Phases

During the adop phase=finalize, EBS_SYSTEM checks and prepares the system for switching to the new edition. The adop phase=cutover then makes the patch edition the new run edition, minimizing downtime.

Cleanup Phase

Finally, the adop phase=cleanup removes any obsolete objects and data from previous editions, ensuring that the system remains clean and efficient.

Security Considerations

Given its critical role, the EBS_SYSTEM schema must be secured properly. Ensure strong password policies, monitor its activities regularly, and restrict excessive privileges to prevent unauthorized access and potential security breaches.

Benefits of Using the EBS_SYSTEM Schema

1. Reduced Downtime: By managing patches in a separate edition, the schema allows patches to be applied with minimal disruption to users.
2. Enhanced Security: Isolating patch operations enhances the security and integrity of the EBS environment.
3. Improved Efficiency: Automating patch management tasks reduces the manual effort required from DBAs, leading to faster and more efficient patching processes.

Conclusion

The EBS_SYSTEM schema is a vital component of Oracle E-Business Suite R12, facilitating seamless and secure online patching. Understanding its roles and responsibilities helps DBAs and system administrators maintain a robust and efficient EBS environment. By leveraging the capabilities of EBS_SYSTEM, organizations can ensure minimal downtime, enhanced security, and optimized performance during patching operations.

ASM QUERIES

ASM DISK SPACE REPORT

set lines 255

col path for a35

col Diskgroup for a15

col DiskName for a25

col disk# for 999

col total_mb for 999,999,999

col free_mb for 999,999,999

compute sum of total_mb on DiskGroup

compute sum of free_mb on DiskGroup

break on DiskGroup skip 1 on report -

 set pages 255

select a.name DiskGroup, b.disk_number Disk#, b.name DiskName, 

   b.total_mb, b.free_mb, 

   -- b.path, 

   b.header_status

from v$asm_disk b, v$asm_diskgroup a

where a.group_number (+) =b.group_number

order by b.group_number, b.disk_number, b.name;

DISK GROUP USAGE

set lines 120

col "Redundancy" for a15

col "Diskgroup" for a12

select a.name "Diskgroup" , round(sum(b.total_mb)/1024,1) "Size GB",  

       round(sum(b.free_mb)/1024,1) "Free GB", 

       decode (a.type, 'EXTERN',round(sum(b.free_mb)/1024,1),'NORMAL',round(sum(b.free_mb)/1024/2,1),'HIGH',round(sum(b.free_mb)/1024/3,1))  "Usable GB", 

       decode (a.type, 'EXTERN',round(sum(b.free_mb)/1024/1024,1),'NORMAL',round(sum(b.free_mb)/1024/1024/2,1),'HIGH',round(sum(b.free_mb)/1024/1024/3,1))  "Usable TB", 

       round((sum(b.total_mb)-sum(b.free_mb))/sum(b.total_mb)*1000)/10 "Use%",

       a.type "Redundancy"

from v$asm_disk b, v$asm_diskgroup a 

where  a.group_number (+) =b.group_number 

group by a.name, a.type order by 1;

GROUP BY USAGE

set pages 100

col database for a15

SELECT NVL(dbname, '-- TOTAL') database, round(SUM(space)/1024/1024) mb_used, 

       round(SUM(space) / AVG(total_mb * 1024 * 1024) * 100, 2) pct_used

FROM (

  SELECT gname, file_type, space, aname, system_created, alias_directory,

         regexp_substr(full_alias_path, '[[:alnum:]_]*',1,4) dbname, total_mb

    FROM (

      SELECT system_created, alias_directory, file_type,space, level, gname, aname,

          concat('+'||gname, sys_connect_by_path(aname, '/')) full_alias_path, total_mb

        FROM (

          SELECT b.name gname, b.total_mb, a.parent_index pindex, a.name aname,

                 a.reference_index rindex, a.system_created, a.alias_directory,

                 c.type file_type, c.space

            FROM v$asm_alias a 

            JOIN v$asm_diskgroup b ON a.group_number = b.group_number

       LEFT JOIN v$asm_file c ON a.group_number = c.group_number

             AND a.file_number = c.file_number

             AND a.file_incarnation = c.incarnation

        ) START WITH (mod(pindex, power(2, 24))) = 0 AND rindex IN (

          SELECT a.reference_index FROM v$asm_alias a, v$asm_diskgroup b

           WHERE a.group_number = b.group_number

             AND (mod(a.parent_index, power(2, 24))) = 0

        ) CONNECT BY prior rindex = pindex

    ) WHERE NOT file_type IS NULL AND system_created = 'Y' )

GROUP BY ROLLUP (dbname)

/

                                                    

Oracle ASM USAGE

select name, total_mb, free_mb, 

       round(100*(total_mb-free_mb)/greatest(1,total_mb),0) as used_pct 

  from v$asm_diskgroup;