Creating Online-Patching-Compliant Table in Oracle E-Business Suite R12.2

Online patching introduced in Oracle E-Business Suite R12.2 fundamentally changed the way custom objects must be created and maintained. To ensure zero-downtime patching, every custom table must support Edition-Based Redefinition (EBR). This requires a base table, an editioning view, and an APPS synonym—created in a specific sequence using Oracle’s AD_ZD utilities.

This article provides a clear, step-by-step guide to creating an online-patching-compliant table along with its editioning view (EV) in R12.2. It also outlines how to manage future structural changes through XDF metadata or AD_ZD utilities.

1. Create the Base Table in the Owning Schema

Begin in the Run edition, logged in as the appropriate product schema (for example, APPLSYS or a custom application schema).
At this stage, only the base database objects are created.

Typical actions include:

  • Creating the table using standard DDL.

  • Defining supporting indexes.

  • Using APPS_TS_* tablespaces depending on the object type.

  • Preferring unique indexes instead of primary key constraints, in line with R12.2 object standards.

At this point, no editioning view exists. The table is still non-compliant with online patching.

2. Upgrade the Table to Create the Editioning View and APPS Synonym

Once the base table is ready, convert it into an online-patching-aware object using Oracle’s AD_ZD package:

EXEC AD_ZD_TABLE.UPGRADE('<OWNER_SCHEMA>', '<TABLE_NAME>');

This action generates two critical components:

  • Editioning View (EV):
    Created in the owning schema with the name <TABLE_NAME>#.
    This view becomes the layer through which the application interacts with the table.

  • APPS Synonym:
    A synonym named <TABLE_NAME> is created in the APPS schema, pointing to the EV.

From this point forward, all application components—Forms, OAF, PL/SQL APIs, reports—must reference the APPS synonym. This ensures that future table changes are transparently managed through the EV without breaking online patching rules.

3. Generate and Deploy the XDF Metadata

To package this custom table for deployment across environments, Oracle requires an XDF (XML Definition File) representation.

Steps:

  1. Insert at least one row into the new table (mandatory for XDF generation).

  2. Run xdfgen.pl from the Run edition to produce the .xdf file containing the metadata for the table, indexes, and associated objects.

  3. Include this .xdf in your custom application patch.

  4. During patch application, xdfcmp.pl automatically creates the base table and invokes AD_ZD_TABLE.UPGRADE, ensuring that the EV and APPS synonym are generated in all target instances.

This makes your object fully compliant with the R12.2 adoption and deployment model.

4. Managing Future Structural Changes

When enhancements or structural modifications are required—such as adding new columns—you must preserve online patching compliance.

Two methods are supported:

a. Preferred Method: Update via XDF

Modify the XDF file and apply it using xdfcmp.pl.
This ensures consistent behavior across environments and adheres to Oracle's standards.

b. Direct DDL in Development

If a table is altered manually in a development instance:

EXEC AD_ZD_TABLE.PATCH('<OWNER_SCHEMA>', '<TABLE_NAME>');

This regenerates the EV mapping to align it with the updated table structure.

Conclusion

Building online-patching-compliant objects is essential for long-term maintainability in Oracle E-Business Suite R12.2. By creating the base table, generating the editioning view through AD_ZD utilities, and managing future changes via XDF or AD_ZD_TABLE.PATCH, you ensure seamless behavior during both Run and Patch editions.


at No comments:

Understanding Oracle Unified Auditing: Quick Checks for DBAs


Oracle Unified Auditing consolidates all audit records into a single, unified framework, simplifying how auditing is configured, managed, and reviewed. As more environments move toward stricter compliance and security standards, DBAs increasingly rely on Unified Auditing to track database activity efficiently.

This short guide highlights how to quickly check whether Unified Auditing is enabled and how to review the audit policies configured in your database.

✅ How to Check if Unified Auditing Is Enabled

Unified Auditing can run in two modes:

  • Mixed Mode (default)

  • Pure Unified Auditing Mode

To verify the status, check the database options:

SELECT VALUE FROM V$OPTION WHERE PARAMETER = 'Unified Auditing';

  • TRUE → Unified Auditing is enabled

  • FALSE → Unified Auditing is disabled

If the database is running in pure mode, it was enabled during installation or via relinking.

View Enabled Unified Audit Policies

To see which audit policies are currently active:

SELECT DISTINCT policy_name 
FROM audit_unified_enabled_policies;

This lists all enabled policies, including Oracle-supplied and user-defined ones.

View All Available Unified Audit Policies

To list every policy defined in the system:

SELECT DISTINCT policy_name 
FROM audit_unified_policies;

This helps you understand what policies exist, even if they’re not currently enabled.

Check Audit Options Associated with Each Policy

To see which audit options belong to each policy:

SELECT audit_option, policy_name 
FROM audit_unified_policies 
GROUP BY policy_name, audit_option;

This provides insight into what actions are being audited under each policy.

Summary

Oracle Unified Auditing centralizes and simplifies auditing. With just a few queries, DBAs can quickly validate:

  • Whether Unified Auditing is active

  • Which policies are enabled

  • What audit actions are tied to each policy

These checks are essential for maintaining security, ensuring compliance, and understanding the audit footprint of your Oracle environment.

at No comments:
Subscribe to: Comments (Atom)