Tag Archives: SQL

Oracle

How To Get Those Customizations to Work Correctly with Oracle EBS R12.2 Editioning (EBR)

February 16, 2017 jhlui1 2 Comments

Oracle e-Business Suite R12.2 Overview of Edition-Based Redefinition (EBR) in 11gR2 databases.

Scenario:
Creating a new view based upon the AP.AP_SUPPLIERS table to reside in a new XXCUSTOMSCHEMA.

If you attempt the simple:

create or replace force view XXCUSTOMSCHEMA.AP_SUPPLIER_NEW_V
as
SELECT *
FROM ap.ap_suppliers ;

It works, but your data and table definition may change whenever patching editions are in-play.

If you attempt to just reference the editioned object instead:

create or replace force view XXCUSTOMSCHEMA.AP_SUPPLIER_NEW_V
as
SELECT *
FROM ap.ap_suppliers# /* or the preferred apps.ap_suppliers synonym */

You will receive the ominous ORA-38818: illegal reference to editioned object error message.

What’s missing? The XXCUSTOMSCHEMA isn’t recognized as an edition-friendly schema by Oracle’s e-Business Suite (nor the database), yet.

Create your XXCUSTOMSCHEMA in the usual way:

create user XXCUSTOMSCHEMA
identified by (password)
default tablespace USERS
temporary tablepace TEMP
profile DEFAULT
account UNLOCK;

/* as APPS user */

exec FND_ORACLE_USER_PKG.LOAD_ROW(‘XXCUSTOMSCHEMA’, ‘CUSTOM’, ‘INVALID’, NULL, ‘N’, ‘B’);

/* as SYSTEM user */

alter session set current_schema=APPS;
exec AD_ZD_PREP.ENABLE_CUSTOM_USER(‘XXCUSTOMSCHEMA’);

/* Create your SELECT grants to the editioned objects as APPS user */

grant select on ap.ap_suppliers# to XXCUSTOMSCHEMA;

/* OR if you’re running newer than 12.2.5…the editioning security will cause hundreds of invalid editioned object stubs that need re-compilation unless you instead use */

exec AD_ZD.grant_privs(‘SELECT’, ‘AP_SUPPLIERS’, ‘XXCUSTOMSCHEMA’,X_GRANT_TO_TABLE=>TRUE)

PL/SQL procedure successfully completed.

SQL> exec AD_ZD.revoke_privs(‘SELECT’,’AP_SUPPLIERS’,’XXCUSTOMSCHEMA’);

/* Create your custom view referencing the APPS synonym – which points to the editioned object */

create or replace force view XXCUSTOMSCHEMA.AP_SUPPLIER_NEW_V
as
SELECT *
FROM apps.ap_suppliers ;

This is the same process when creating procedures and functions with the usual caveat that unless it’s a 12c database, you cannot create materialized views on the editioned objects.

For the 11gR2 EBS users, you would stick with the existing non-editioned table object reference:

CREATE MATERIALIZED VIEW APPS.XXAP_SUPPLIER_NEW_MV
(
vendor_id,
vendor_name
)
AS
SELECT aps.vendor_id, aps.vendor_name
FROM ap.ap_suppliers aps;

/* The owning schema for the materialized view must have CREATE TABLE, VIEW AND INDEX privileges on the destination tablespace */

Oracle

TRCSESS – Trace Information for Various Oracle Sessions or Modules – Quick & Dirty Tip

June 9, 2016 jhlui1 Leave a comment

Database Sessions view from Oracle SQL Developer

— Find your session

SQL> /* a Dell TOAD type query */

SELECT ROUND (BITAND (s.ownerid, 65535)) parent_session_sid,
ROUND (BITAND (s.ownerid, 16711680) / 65536) parent_session_instid,
RAWTOHEX (SADDR) AS saddr,
s.SID,
s.SERIAL#,
s.AUDSID,
RAWTOHEX (PADDR) AS paddr,
s.USER#,
s.USERNAME,
s.COMMAND,
s.OWNERID,
s.TADDR,
s.LOCKWAIT,
s.STATUS,
s.SERVER,
s.SCHEMA#,
s.SCHEMANAME,
s.OSUSER,
s.PROCESS,
s.MACHINE,
s.PORT,
s.TERMINAL,
UPPER (s.PROGRAM) PROGRAM,
s.TYPE,
s.SQL_ADDRESS,
s.SQL_HASH_VALUE,
s.SQL_ID,
s.SQL_CHILD_NUMBER,
s.SQL_EXEC_START,
s.SQL_EXEC_ID,
s.PREV_SQL_ADDR,
s.PREV_HASH_VALUE,
s.PREV_SQL_ID,
s.PREV_CHILD_NUMBER,
s.PREV_EXEC_START,
s.PREV_EXEC_ID,
s.PLSQL_ENTRY_OBJECT_ID,
s.PLSQL_ENTRY_SUBPROGRAM_ID,
s.PLSQL_OBJECT_ID,
s.PLSQL_SUBPROGRAM_ID,
s.MODULE,
s.MODULE_HASH,
s.ACTION,
s.ACTION_HASH,
s.CLIENT_INFO,
s.FIXED_TABLE_SEQUENCE,
s.ROW_WAIT_OBJ#,
s.ROW_WAIT_FILE#,
s.ROW_WAIT_BLOCK#,
s.ROW_WAIT_ROW#,
s.TOP_LEVEL_CALL#,
s.LOGON_TIME,
s.LAST_CALL_ET,
s.PDML_ENABLED,
s.FAILOVER_TYPE,
s.FAILOVER_METHOD,
s.FAILED_OVER,
s.RESOURCE_CONSUMER_GROUP,
s.PDML_STATUS,
s.PDDL_STATUS,
s.PQ_STATUS,
s.CURRENT_QUEUE_DURATION,
s.CLIENT_IDENTIFIER,
s.BLOCKING_SESSION_STATUS,
s.BLOCKING_INSTANCE,
s.BLOCKING_SESSION,
s.FINAL_BLOCKING_SESSION_STATUS,
s.FINAL_BLOCKING_INSTANCE,
s.FINAL_BLOCKING_SESSION,
s.SEQ#,
s.EVENT#,
s.EVENT,
s.P1TEXT,
s.P1,
s.P1RAW,
s.P2TEXT,
s.P2,
s.P2RAW,
s.P3TEXT,
s.P3,
s.P3RAW,
s.WAIT_CLASS_ID,
s.WAIT_CLASS#,
s.WAIT_CLASS,
s.WAIT_TIME,
s.SECONDS_IN_WAIT,
s.STATE,
s.WAIT_TIME_MICRO,
s.TIME_REMAINING_MICRO,
s.TIME_SINCE_LAST_WAIT_MICRO,
s.SERVICE_NAME,
s.SQL_TRACE,
s.SQL_TRACE_WAITS,
s.SQL_TRACE_BINDS,
s.SQL_TRACE_PLAN_STATS,
s.SESSION_EDITION_ID,
s.CREATOR_ADDR,
s.CREATOR_SERIAL#,
s.ECID,
stat.cpu – stat.CPU_this_call_start cpu_this_call,
stat.CPU,
stat.UGA_memory,
stat.PGA_memory,
stat.Commits,
stat.Rollbacks,
si.Block_Gets,
si.Consistent_Gets,
si.Physical_Reads,
si.Block_Changes,
si.Consistent_Changes,
p.program “OSProgram”,
p.spid,
p.pid
FROM V$SESSION S,
V$SESS_IO si,
V$PROCESS p,
( SELECT ss.sid stat_sid,
SUM (
DECODE (sn.name, ‘CPU used when call started’, ss.VALUE, 0))
CPU_this_call_start,
SUM (
DECODE (sn.name, ‘CPU used by this session’, ss.VALUE, 0))
CPU,
SUM (DECODE (sn.name, ‘session uga memory’, ss.VALUE, 0))
uga_memory,
SUM (DECODE (sn.name, ‘session pga memory’, ss.VALUE, 0))
pga_memory,
SUM (DECODE (sn.name, ‘user commits’, ss.VALUE, 0)) commits,
SUM (DECODE (sn.name, ‘user rollbacks’, ss.VALUE, 0))
rollbacks
FROM v$sesstat ss, v$statname sn
WHERE     ss.STATISTIC# = sn.STATISTIC#
AND (   sn.name = ‘CPU used when call started’
OR sn.name = ‘CPU used by this session’
OR sn.name = ‘session uga memory’
OR sn.name = ‘session pga memory’
OR sn.name = ‘user commits’
OR sn.name = ‘user rollbacks’)
GROUP BY ss.sid) stat
WHERE     (    (s.USERNAME IS NOT NULL)
AND (NVL (s.osuser, ‘x’) <> ‘SYSTEM’)
AND (s.TYPE <> ‘BACKGROUND’))
AND si.sid(+) = s.sid
AND p.addr(+) = s.paddr
AND stat.stat_sid = s.sid

— Setup a Client Trace:

SQL> exec dbms_monitor.client_id_trace_enable (client_id=>'<terminal or client string>’);
PL/SQL procedure successfully completed.

— Setup a trace for all matching session threads for a module:
SQL> exec dbms_monitor.serv_mod_act_trace_enable (service_name=>'<ORACLE_SID or SERVICE_NAME>’, module_name=>'<MODULE from V$SESSION>’);
PL/SQL procedure successfully completed.

Visit your trace directory (in 11gR2 and up, you can consolidate the various dump locations using the init.ora parameter diagnostic_dest='<filepath>’)

# *.trc looks through every trace file in the directory for the matching module string
# Can also use any of the other options to search for specific sessions:
# [action=action_name]
# [clientid=client_id]
# [session=session_id]

oracle > trcsess output=mytrace.trc module=”<MODULE from V$SESSION>” *.trc

# Then just execute your normal tkprof command to format the consolidated output

oracle > tkprof mytrace.trc myformattedtrace.txt sys=no sort=exeela explain=<user/pwd>

# Voila! All the traces for a specific session, module, or action at a specific time.

— Don’t forget to disable your tracing afterwards!

SQL> exec dbms_monitor.client_id_trace_disable (client_id=>'<terminal or client string>’);
PL/SQL procedure successfully completed.

— Setup a trace for all matching session threads for a module:
SQL> exec dbms_monitor.serv_mod_act_trace_disable (service_name=>'<ORACLE_SID or SERVICE_NAME>’, module_name=>'<MODULE from V$SESSION>’);
PL/SQL procedure successfully completed.

Oracle

R12.2 Overview for Developers (Oracle e-Business Suite)

May 9, 2014 jhlui1 Leave a comment

We are upgrading our Oracle e-Business Suite from 12.1.3.6 to 12.2.3.

This is a preparatory session prepared for our Development Team and may be useful to others. All copyrights reserved; no reproduction without permission.

AUS_R12_2_for_Developers_@jhlui1 (PDF Link)

Contents Outline:

R12.2 Overview For Developers
May 5, 2014
James H. Lui, OCP
Sr. Oracle Applications DBA
@jhlui1

What’s Covered

What’s an Edition?
What’s an Online Patch?
How is Development Affected?
Examples
What Can I Do?

What’s an Edition? (Doc ID 1489116.1)

An edition is like a workspace or private environment where database objects are redefined. When we are satisfied with the change that we have made, those changes in the edition can be then rolled out to all the application users.
With Edition-Based Redefinition you can have two objects with the same name, as long as they are in different Editions.
An edition is effectively a version label that can be assigned to all editionable objects in a schema.

–When a new edition is used by a schema, all editionable objects are inherited by the new edition from the previous edition.

–These objects can subsequently then be altered or dropped as desired, but doing so will stop the inheritance of that object.

–From Oracle database 11gR2 onwards, each database has at least one edition, the default being ORA$BASE.

–The default edition can be displayed using the DATABASE_PROPERTIES view.

What Objects Can Be Editioned?

(Doc ID 1489116.1)
<illustration>

The following object types are editionable:

FUNCTION
LIBRARY
PACKAGE and PACKAGE BODY
PROCEDURE
TRIGGER
TYPE and TYPE BODY
SYNONYM
VIEW

Review contents of the APPS versus APPS_NE schemas for detai

What are The Rules for Editioning?

(Doc ID 1489116.1 – there are more…)

A non-editionedobject cannot depend on an editioned object.

Some examples:

– A public synonym cannot refer to an editioned object.
(why APPS-owned synonyms take precedence)

– A function-based index cannot depend on an editioned function.

– A materialized view cannot depend on an editioned view.
(why a lot of MV’s are invalidated in 12.2)

– A table cannot have a column of a user-defined data type (collection or Abstract Data Type/ADT) whose owner is editions-enabled (APPS is, APPS_NE isn’t.)

– A noneditioned subprogram cannot have a static reference to a subprogram whose owner is editions-enabled.
(APPS_NE.<package> cannot reference APPS.<function>)

Editioning Example: PTCHARMK

(adop phase=prepare in-progress)
SQL Statement which produced this data:
SELECT * FROM dba_editions;
<illustration>
What’s an Edition? (Doc ID 1489116.1)
SELECT property_value
FROM database_properties
WHERE property_name = ‘DEFAULT_EDITION’;
<illustration>
ALTER SESSION SET edition=V_20140428_1435;

SELECT SYS_CONTEXT(‘USERENV’, ‘SESSION_EDITION_NAME’) AS edition FROM dual;

What’s an Online Patch?(Doc ID 1583902.1)

Online patching is a new patching mechanism that allows the application of patches while the system is up and running, and the users are working as normal (…while ensuring object version consistency.)
What are the phases that make up the Online Patching cycle?

–Prepare a virtual copy (patch edition) of the running application (run edition).

–Apply patches to the patch edition of the application.

–Finalize the system in readiness for the cutover phase.

–Cutover to the patch edition and make it the new run edition.

–Cleanup obsolete definitions or data to recover space.

When a patch is applied, adop will:

–Synchronizethe contents of the run file system to the patch file system. (phase=prepare)

–Apply patching actions on the patch file system. (phase=apply)

–During the cutover phase, the adoputility (phase=cutover) :

Restartsthe application tier services.
Swap [Patch FS] <-> [Run FS]

Online Patching: Our Filesystem (note: we moved the $INST_TOP down a level from /u01 to facilitate preservation as a separate NFS mount during refreshes)

/u01

–fs1/

–fs2/

–fs_ne/

–inst/

–oraInventory/

Online Patching: The Environment

Online Patching: Switching Envs(Doc ID 1545584.1 – Requires 12.2.2 minimum)

Change directory to the Base directory and run script EBSapps.env giving “run” or “patch” as argument, eg:

cd /u01/oracle/EBS122

. ./EBSapps.env run

E-Business Suite Environment Information

—————————————-

RUN File System : <EBS base dir>/fs1/EBSapps/appl

PATCH File System : <EBS base dir>/fs2/EBSapps/appl

Non-Editioned File System : <EBS base dir>/fs_ne

DB Host: <hostname.domain name> Service/SID: <SID>

Sourcing the RUN File System …

Online Patching: Switching Envs(Doc ID 1545584.1 – for PTCHARMK at 12.2.0)

Use fsauto<SID>.sh(already included in the applptch .profile)

cd ~

. ./fsauto${ORACLE_SID}.sh

Running fsauto 1.0 :

Context Name (<SID_host>) : PTCHARMK_aburpaofinm01

RUNEdition APPL_TOP : /u01/fs1

PATCHEdition APPL_TOP : /u01/fs2

APPL_TOP environment file : /u01/fs1/EBSapps/appl/APPSPTCHARMK_aburpaofinm01.env

Non-Editioned File System : /u01/fs_ne/EBSapps/appl

Instance Top Directory : /u01/inst/fs1/inst/apps/PTCHARMK_aburpaofinm01

Admin Scripts Home : /u01/inst/fs1/inst/apps/PTCHARMK_aburpaofinm01/admin/scripts

Setting RUN Edition APPL_TOP environment for /u01/fs1 …

Current PATCH_TOP: /mnt/nfs/ebs/122postinstall

applptch@aburpaofinm01 $>

What’s an Online Patch?

(Checking for Active Patching Sessions)
SELECT ADOP_SESSION_ID,
PREPARE_STATUS,
APPLY_STATUS,
FINALIZE_STATUS,
CUTOVER_STATUS,
CLEANUP_STATUS,
ABORT_STATUS,
STATUS,
ABANDON_FLAG,
NODE_NAME
FROM AD_ADOP_SESSIONS
ORDER BY ADOP_SESSION_ID;

What’s an Online Patch?

(Why should I care?)

Q: Based on the below information querying the AD_ADOP_SESSIONS table in PTCHARMK:

Which patching session will go first, and when?
Which filesystem (fs1 or fs2) and edition is Run versus Patch?
Where should my custom object/code/form/report go to test in this instance?
<illustration>

What’s an Online Patch?
(Why should I care?)

Q: Based on the below information querying the AD_ADOP_SESSIONS table in PTCHARMK:

Which patching session will go first, and when?
Which filesystem and edition is Run versus Patch?
Where should my custom object/code/form/report go to test in this instance?
<illustration>

Examples: Editioned View
(Why we use APPS.AR_DEBUGinstead of AR.AR_DEBUG)

/* ObjName | ObjType | ObjParent | ObjOwner
AR_DEBUG | TABLE | | AR
AR_DEBUG# |VIEW | | AR
AR_DEBUG | SYNONYM | | APPS

Synonym APPS.AR_DEBUG */
/* Formatted on 5/1/2014 3:26:22 PM (QP5 v5.256.13226.35510) */
CREATE OR REPLACE FORCE EDITIONING VIEW AR.AR_DEBUG#
(
DEBUG_TYPE,
ITEM_TYPE,
ITEM_KEY,
ACTIVITY_NAME,
EXECUTION_DATE,
DEBUG_MESSAGE
)
AS
SELECT DEBUG_TYPE DEBUG_TYPE,
ITEM_TYPE ITEM_TYPE,
ITEM_KEY ITEM_KEY,
ACTIVITY_NAME ACTIVITY_NAME,
EXECUTION_DATE EXECUTION_DATE,
DEBUG_MESSAGE DEBUG_MESSAGE
FROM “AR”.”AR_DEBUG”;
CREATE OR REPLACE SYNONYMAPPS.AR_DEBUGFOR AR.AR_DEBUG#;

GRANT DELETE, INSERT, SELECT, UPDATE, DEBUG ON AR.AR_DEBUG#TO APPS WITH GRANT OPTION;

GRANT SELECT ON AR.AR_DEBUG# TO XXUSR_SELECT_R;

Examples: PL/SQL Package
(APPS.ACCOUNT_MGR)

Examples: PL/SQL Package
(APPS.ACCOUNT_MGR)

<illustration>
How is Development Affected?
(Doc ID 1489116.1)
Not using an APPS.<editioned object synonym>

–Views: disappearing/appearing columns, indexes, even data

–PL/SQL: dropped/altered/changed API’s, usage, references

–Triggers: disappearing/appearing constraints, functional changes

–Types: disappearing/appearing columns, indexes, even data

–Synonyms: pointers change: ViewsóTables, all the above

Invalid Objects Appear After Patching
Functional Code Behavior Changes After Patching
If another Developer is using an Edition:The DEFAULT_EDITION can change without warning

What to Do?

Always Be Aware of other Patching or Development activity going on in the instance
Stage Filesystem-based Code in both RUN_BASE and PATCH_BASE
Scheduling of Data Changes is Critical (changes can vanish)
Announce to all other Users if Creating/Cutting-Over a New Edition
Flag all Base-Object Customizations for Detection during PPA

How to Flag a Customization

(OAM -> Site Map -> [Maintenance] -> Register Flagged Files)
<illustration>

How to Flag a Customization

(OAM -> Site Map -> [Maintenance] -> Register Flagged Files)
<illustration>

MOS References

(support.oracle.com – CSI: 19086017)
Deploying Customizations in Oracle E-Business Suite Release 12.2 (Doc ID 1577661.1)
Master Note: Overview of Oracle Edition-Based Redefinition (EBR) (Doc ID 1489116.1)
Oracle E-Business Suite Release 12.2: Online Patching FAQ
(Doc ID 1583902.1)
Oracle Application Framework Release Notes for Release 12.2.3 (Doc ID 1593782.1)
Where to Deploy Custom Java [default: $JAVA_TOP/*]
(Doc ID 1609939.1)
Oracle E-Business Suite Release 12.2 Information Center
(Doc ID 1583153.1)

An in-depth white paper prepared for the NorCalOAUG by John Peters (2/2014):

http://jrpjr.com/paper_archive/12_2_development.pdf

Oracle

Shutdown/Startup vs. Alter Statement – 12c Edition

July 22, 2013 jhlui1 Leave a comment

And most of these new commands are available via a simple right-click in the current release of Oracle’s SQLDeveloper (DBA Menu).

When everyone finally moves to Oracle pluggable databases, one thing to remember is that stopping and starting a database is not going to be as simple as it use to be. Shutting down and starting up a database is done simply with the following commands:

shutdown normal

shutdown immediate

shutdown abort

startup

startup nomount

startup mount

If we use one of these traditional shutdown methods at the container database (CDB) layer, we will bring down the CDB and the associated PDBs under it. This is something to be aware of when looking to reboot a container databases. Lets take a look at bringing down a CDB.

[oracle@oel ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Sun Jul 21 21:42:53 2013

Copyright (c) 1982, 2013, Oracle. All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real…

View original post 687 more words

Oracle

Changing ORACLE_BASE in Oracle 11gR2 (11.2.0.3.4) Database

July 18, 2013 jhlui1 Leave a comment

During a routine installation, I managed to mis-type the filesystem path for the ORACLE_BASE during execution of the Oracle Universal Installer (./runInstaller).

[nb: ORACLE_BASE by normal definition should be a standard starting filesystem mountpoint under which all other instance-specific data/files for this ORACLE_HOME would be found. Typically contains your diagnostic logs, Fast Recovery Area files, all configuration tool logs, and any Flashback checkpoint saves.

$ORACLE_BASE/diag/.

./asm
./clients
./crs
./diagtool
./lsnrctl
./netcman
./ofm
./rdbms
./tnslsnr

$ORACLE_BASE/admin/<SID>/.

./adump # Audit data./dpdump # Data Pump related
./pfile # init.ora files generated by config tools

–end of note]

Since the service account (oracle) had filesystem permissions to do so, it went ahead and put all of the flashback recovery, diagnostics, and audit trail files (and my backup controlfiles) into the incorrectly named directory. The database itself was running, but how to correct this without starting all over again?

Start with backing up your init.ora configuration from spfile to pfile format:

SQL> create pfile from spfile;

File created.

(this defaults to $ORACLE_HOME/dbs/init.ora)

Then shutdown services:

SQL> shutdown immediate;

Database closed.

SQL> exit;

#oracle> lsnrctl stop

Now go find all the places you’ll need to modify/correct the ORACLE_BASE filepath declarations:

$ORACLE_HOME/dbs/init<SID>.ora

$ORACLE_HOME/network/admin (or $TNS_ADMIN)

listener.ora: ADR_BASE_LISTENER
sqlnet.ora: ADR_BASE
(Note: these two are auto-generated using netca – the Oracle Net Configuration Assistant, if you execute that after making the database and environment-side changes.

$ORACLE_HOME/inventory/ContentsXML/oraclehomeproperties.xml : ORACLE_BASE

All finished editing? Start everything back up:

#oracle> lsnrctl start

SQL> startup pfile=?/dbs/init<SID>.ora — to use revised settings

SQL> create spfile from pfile; — copy new settings to spfile

SQL> shutdown immediate;

SQL> startup; — uses spfile instead

Now future installs using dbca, dbua and netca should recognize your defined/modified/corrected ORACLE_BASE.